liquid_feedback_core

changeset 336:a7537038640d

Cleanup of new code for allowing "lf_update" to run without extensive locking

- Removed unnecessary variable "direct_voter_update_v" in function "forbid_changes_on_closed_issue_trigger"()
- Added PERFORM "require_transaction_isolation"() in more functions
- Integrated functions "issue_admission", "initiative_admission" and "freeze_after_snapshot" into "check_issue" function
- Removed function "manual_freeze" (replacement: set "issue" timings accordingly, when needed)
- Added/modified some comments
author jbe
date Wed Feb 20 02:56:49 2013 +0100 (2013-02-20)
parents ab370f3b9892
children c8289a674ef2
files core.sql
line diff
     1.1 --- a/core.sql	Tue Feb 19 14:44:54 2013 +0100
     1.2 +++ b/core.sql	Wed Feb 20 02:56:49 2013 +0100
     1.3 @@ -1578,9 +1578,8 @@
     1.4    RETURNS TRIGGER
     1.5    LANGUAGE 'plpgsql' VOLATILE AS $$
     1.6      DECLARE
     1.7 -      "issue_id_v"            "issue"."id"%TYPE;
     1.8 -      "issue_row"             "issue"%ROWTYPE;
     1.9 -      "direct_voter_update_v" BOOLEAN;
    1.10 +      "issue_id_v" "issue"."id"%TYPE;
    1.11 +      "issue_row"  "issue"%ROWTYPE;
    1.12      BEGIN
    1.13        IF TG_OP = 'DELETE' THEN
    1.14          "issue_id_v" := OLD."issue_id";
    1.15 @@ -3101,6 +3100,7 @@
    1.16        "weight_v"              INT4;
    1.17        "sub_weight_v"          INT4;
    1.18      BEGIN
    1.19 +      PERFORM "require_transaction_isolation"();
    1.20        "weight_v" := 0;
    1.21        FOR "issue_delegation_row" IN
    1.22          SELECT * FROM "issue_delegation"
    1.23 @@ -3165,6 +3165,7 @@
    1.24      DECLARE
    1.25        "member_id_v" "member"."id"%TYPE;
    1.26      BEGIN
    1.27 +      PERFORM "require_transaction_isolation"();
    1.28        DELETE FROM "direct_population_snapshot"
    1.29          WHERE "issue_id" = "issue_id_p"
    1.30          AND "event" = 'periodic';
    1.31 @@ -3237,6 +3238,7 @@
    1.32        "weight_v"              INT4;
    1.33        "sub_weight_v"          INT4;
    1.34      BEGIN
    1.35 +      PERFORM "require_transaction_isolation"();
    1.36        "weight_v" := 0;
    1.37        FOR "issue_delegation_row" IN
    1.38          SELECT * FROM "issue_delegation"
    1.39 @@ -3301,6 +3303,7 @@
    1.40      DECLARE
    1.41        "member_id_v" "member"."id"%TYPE;
    1.42      BEGIN
    1.43 +      PERFORM "require_transaction_isolation"();
    1.44        DELETE FROM "direct_interest_snapshot"
    1.45          WHERE "issue_id" = "issue_id_p"
    1.46          AND "event" = 'periodic';
    1.47 @@ -3597,165 +3600,6 @@
    1.48  
    1.49  
    1.50  
    1.51 ----------------------
    1.52 --- Freezing issues --
    1.53 ----------------------
    1.54 -
    1.55 -
    1.56 -CREATE FUNCTION "issue_admission"
    1.57 -  ( "issue_id_p" "issue"."id"%TYPE )
    1.58 -  RETURNS VOID
    1.59 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.60 -    DECLARE
    1.61 -      "issue_row"  "issue"%ROWTYPE;
    1.62 -      "policy_row" "policy"%ROWTYPE;
    1.63 -    BEGIN
    1.64 -      PERFORM "require_transaction_isolation"();
    1.65 -      SELECT * INTO "issue_row" FROM "issue"
    1.66 -        WHERE "id" = "issue_id_p" FOR UPDATE;
    1.67 -      SELECT * INTO "policy_row"
    1.68 -        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    1.69 -      IF EXISTS (
    1.70 -        SELECT NULL FROM "initiative"
    1.71 -        WHERE "issue_id" = "issue_id_p"
    1.72 -        AND "supporter_count" > 0
    1.73 -        AND "supporter_count" * "policy_row"."issue_quorum_den"
    1.74 -        >= "issue_row"."population" * "policy_row"."issue_quorum_num"
    1.75 -      ) THEN
    1.76 -        UPDATE "issue" SET
    1.77 -          "state"          = 'discussion',
    1.78 -          "accepted"       = coalesce("phase_finished", now()),
    1.79 -          "phase_finished" = NULL
    1.80 -          WHERE "id" = "issue_id_p";
    1.81 -      ELSIF "issue_row"."phase_finished" NOTNULL THEN
    1.82 -        UPDATE "issue" SET
    1.83 -          "state"          = 'canceled_issue_not_accepted',
    1.84 -          "closed"         = "phase_finished",
    1.85 -          "phase_finished" = NULL
    1.86 -          WHERE "id" = "issue_id_p";
    1.87 -      END IF;
    1.88 -      RETURN;
    1.89 -    END;
    1.90 -  $$;
    1.91 -
    1.92 -COMMENT ON FUNCTION "issue_admission"
    1.93 -  ( "issue"."id"%TYPE )
    1.94 -  IS 'Checks admission of an issue, and either sets "accepted" to TRUE, or cancels the issue if "phase_finished" is set';
    1.95 -
    1.96 -
    1.97 -CREATE FUNCTION "initiative_admission"
    1.98 -  ( "issue_id_p" "issue"."id"%TYPE )
    1.99 -  RETURNS VOID
   1.100 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.101 -    DECLARE
   1.102 -      "issue_row"      "issue"%ROWTYPE;
   1.103 -      "policy_row"     "policy"%ROWTYPE;
   1.104 -      "initiative_row" "initiative"%ROWTYPE;
   1.105 -    BEGIN
   1.106 -      PERFORM "require_transaction_isolation"();
   1.107 -      SELECT * INTO "issue_row" FROM "issue"
   1.108 -        WHERE "id" = "issue_id_p";
   1.109 -      SELECT * INTO "policy_row" FROM "policy"
   1.110 -        WHERE "id" = "issue_row"."policy_id";
   1.111 -      FOR "initiative_row" IN
   1.112 -        SELECT * FROM "initiative"
   1.113 -        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.114 -        FOR UPDATE
   1.115 -      LOOP
   1.116 -        IF
   1.117 -          "initiative_row"."polling" OR (
   1.118 -            "initiative_row"."satisfied_supporter_count" > 0 AND
   1.119 -            "initiative_row"."satisfied_supporter_count" *
   1.120 -            "policy_row"."initiative_quorum_den" >=
   1.121 -            "issue_row"."population" * "policy_row"."initiative_quorum_num"
   1.122 -          )
   1.123 -        THEN
   1.124 -          UPDATE "initiative" SET "admitted" = TRUE
   1.125 -            WHERE "id" = "initiative_row"."id";
   1.126 -        ELSE
   1.127 -          UPDATE "initiative" SET "admitted" = FALSE
   1.128 -            WHERE "id" = "initiative_row"."id";
   1.129 -        END IF;
   1.130 -      END LOOP;
   1.131 -      RETURN;
   1.132 -    END;
   1.133 -  $$;
   1.134 -
   1.135 -COMMENT ON FUNCTION "initiative_admission"
   1.136 -  ( "issue"."id"%TYPE )
   1.137 -  IS 'Sets the "admitted" flag of all initiatives in an issue, according to their supporter count fulfilling the necessary "initiative_quorum"';
   1.138 -
   1.139 -
   1.140 -CREATE FUNCTION "freeze_after_snapshot"
   1.141 -  ( "issue_id_p" "issue"."id"%TYPE )
   1.142 -  RETURNS VOID
   1.143 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.144 -    DECLARE
   1.145 -      "issue_row"      "issue"%ROWTYPE;
   1.146 -      "policy_row"     "policy"%ROWTYPE;
   1.147 -      "initiative_row" "initiative"%ROWTYPE;
   1.148 -    BEGIN
   1.149 -      PERFORM "require_transaction_isolation"();
   1.150 -      SELECT * INTO "issue_row" FROM "issue"
   1.151 -        WHERE "id" = "issue_id_p" FOR UPDATE;
   1.152 -      SELECT * INTO "policy_row" FROM "policy"
   1.153 -        WHERE "id" = "issue_row"."policy_id";
   1.154 -      IF EXISTS (
   1.155 -        SELECT NULL FROM "initiative"
   1.156 -        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.157 -      ) THEN
   1.158 -        UPDATE "issue" SET
   1.159 -          "state"         = 'voting',
   1.160 -          "accepted"      = coalesce("accepted", "phase_finished"),
   1.161 -          "half_frozen"   = coalesce("half_frozen", "phase_finished"),
   1.162 -          "fully_frozen"  = "phase_finished",
   1.163 -          "phase_finished" = NULL
   1.164 -          WHERE "id" = "issue_id_p";
   1.165 -      ELSE
   1.166 -        UPDATE "issue" SET
   1.167 -          "state"           = 'canceled_no_initiative_admitted',
   1.168 -          "accepted"        = coalesce("accepted", "phase_finished"),
   1.169 -          "half_frozen"     = coalesce("half_frozen", "phase_finished"),
   1.170 -          "fully_frozen"    = "phase_finished",
   1.171 -          "closed"          = "phase_finished",
   1.172 -          "phase_finished"  = NULL
   1.173 -          WHERE "id" = "issue_id_p";
   1.174 -        -- NOTE: The following DELETE statements have effect only when
   1.175 -        --       issue state has been manipulated
   1.176 -        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
   1.177 -        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
   1.178 -        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
   1.179 -      END IF;
   1.180 -      RETURN;
   1.181 -    END;
   1.182 -  $$;
   1.183 -
   1.184 -COMMENT ON FUNCTION "freeze_after_snapshot"
   1.185 -  ( "issue"."id"%TYPE )
   1.186 -  IS 'This function freezes an issue (fully) and starts voting, but must only be called after all other preparations have been made.';
   1.187 -
   1.188 -
   1.189 -CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
   1.190 -  RETURNS VOID
   1.191 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.192 -    DECLARE
   1.193 -      "issue_row" "issue"%ROWTYPE;
   1.194 -    BEGIN
   1.195 -      PERFORM "create_snapshot"("issue_id_p");
   1.196 -      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.197 -      PERFORM "initiative_admission"("issue_id_p");
   1.198 -      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.199 -      PERFORM "freeze_after_snapshot"("issue_id_p");
   1.200 -      RETURN;
   1.201 -    END;
   1.202 -  $$;
   1.203 -
   1.204 -COMMENT ON FUNCTION "manual_freeze"
   1.205 -  ( "issue"."id"%TYPE )
   1.206 -  IS 'Freeze an issue manually (fully) and start voting';
   1.207 -
   1.208 -
   1.209 -
   1.210  -----------------------
   1.211  -- Counting of votes --
   1.212  -----------------------
   1.213 @@ -3773,6 +3617,7 @@
   1.214        "weight_v"              INT4;
   1.215        "sub_weight_v"          INT4;
   1.216      BEGIN
   1.217 +      PERFORM "require_transaction_isolation"();
   1.218        "weight_v" := 0;
   1.219        FOR "issue_delegation_row" IN
   1.220          SELECT * FROM "issue_delegation"
   1.221 @@ -3832,6 +3677,7 @@
   1.222      DECLARE
   1.223        "member_id_v" "member"."id"%TYPE;
   1.224      BEGIN
   1.225 +      PERFORM "require_transaction_isolation"();
   1.226        FOR "member_id_v" IN
   1.227          SELECT "member_id" FROM "direct_voter"
   1.228          WHERE "issue_id" = "issue_id_p"
   1.229 @@ -4272,14 +4118,19 @@
   1.230          "harmonic_weights_set"  BOOLEAN,
   1.231          "closed_voting"         BOOLEAN );
   1.232  
   1.233 +COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
   1.234 +
   1.235 +
   1.236  CREATE FUNCTION "check_issue"
   1.237    ( "issue_id_p" "issue"."id"%TYPE,
   1.238      "persist"    "check_issue_persistence" )
   1.239    RETURNS "check_issue_persistence"
   1.240    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.241      DECLARE
   1.242 -      "issue_row" "issue"%ROWTYPE;
   1.243 -      "state_v"   "issue_state";
   1.244 +      "issue_row"      "issue"%ROWTYPE;
   1.245 +      "policy_row"     "policy"%ROWTYPE;
   1.246 +      "initiative_row" "initiative"%ROWTYPE;
   1.247 +      "state_v"        "issue_state";
   1.248      BEGIN
   1.249        PERFORM "require_transaction_isolation"();
   1.250        IF "persist" ISNULL THEN
   1.251 @@ -4351,7 +4202,29 @@
   1.252              PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.253            ELSIF "persist"."state" = 'verification' THEN
   1.254              PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.255 -            PERFORM "initiative_admission"("issue_id_p");
   1.256 +            SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.257 +            SELECT * INTO "policy_row" FROM "policy"
   1.258 +              WHERE "id" = "issue_row"."policy_id";
   1.259 +            FOR "initiative_row" IN
   1.260 +              SELECT * FROM "initiative"
   1.261 +              WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.262 +              FOR UPDATE
   1.263 +            LOOP
   1.264 +              IF
   1.265 +                "initiative_row"."polling" OR (
   1.266 +                  "initiative_row"."satisfied_supporter_count" > 0 AND
   1.267 +                  "initiative_row"."satisfied_supporter_count" *
   1.268 +                  "policy_row"."initiative_quorum_den" >=
   1.269 +                  "issue_row"."population" * "policy_row"."initiative_quorum_num"
   1.270 +                )
   1.271 +              THEN
   1.272 +                UPDATE "initiative" SET "admitted" = TRUE
   1.273 +                  WHERE "id" = "initiative_row"."id";
   1.274 +              ELSE
   1.275 +                UPDATE "initiative" SET "admitted" = FALSE
   1.276 +                  WHERE "id" = "initiative_row"."id";
   1.277 +              END IF;
   1.278 +            END LOOP;
   1.279            END IF;
   1.280          END IF;
   1.281          RETURN "persist";
   1.282 @@ -4388,7 +4261,29 @@
   1.283          RETURN NULL;
   1.284        END IF;
   1.285        IF "persist"."state" = 'admission' THEN
   1.286 -        PERFORM issue_admission("issue_id_p");
   1.287 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.288 +          FOR UPDATE;
   1.289 +        SELECT * INTO "policy_row"
   1.290 +          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.291 +        IF EXISTS (
   1.292 +          SELECT NULL FROM "initiative"
   1.293 +          WHERE "issue_id" = "issue_id_p"
   1.294 +          AND "supporter_count" > 0
   1.295 +          AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.296 +          >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.297 +        ) THEN
   1.298 +          UPDATE "issue" SET
   1.299 +            "state"          = 'discussion',
   1.300 +            "accepted"       = coalesce("phase_finished", now()),
   1.301 +            "phase_finished" = NULL
   1.302 +            WHERE "id" = "issue_id_p";
   1.303 +        ELSIF "issue_row"."phase_finished" NOTNULL THEN
   1.304 +          UPDATE "issue" SET
   1.305 +            "state"          = 'canceled_issue_not_accepted',
   1.306 +            "closed"         = "phase_finished",
   1.307 +            "phase_finished" = NULL
   1.308 +            WHERE "id" = "issue_id_p";
   1.309 +        END IF;
   1.310          RETURN NULL;
   1.311        END IF;
   1.312        IF "persist"."phase_finished" THEN
   1.313 @@ -4401,7 +4296,36 @@
   1.314            RETURN NULL;
   1.315          END IF;
   1.316          IF "persist"."state" = 'verification' THEN
   1.317 -          PERFORM "freeze_after_snapshot"("issue_id_p");
   1.318 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.319 +            FOR UPDATE;
   1.320 +          SELECT * INTO "policy_row" FROM "policy"
   1.321 +            WHERE "id" = "issue_row"."policy_id";
   1.322 +          IF EXISTS (
   1.323 +            SELECT NULL FROM "initiative"
   1.324 +            WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.325 +          ) THEN
   1.326 +            UPDATE "issue" SET
   1.327 +              "state"         = 'voting',
   1.328 +              "accepted"      = coalesce("accepted", "phase_finished"),
   1.329 +              "half_frozen"   = coalesce("half_frozen", "phase_finished"),
   1.330 +              "fully_frozen"  = "phase_finished",
   1.331 +              "phase_finished" = NULL
   1.332 +              WHERE "id" = "issue_id_p";
   1.333 +          ELSE
   1.334 +            UPDATE "issue" SET
   1.335 +              "state"           = 'canceled_no_initiative_admitted',
   1.336 +              "accepted"        = coalesce("accepted", "phase_finished"),
   1.337 +              "half_frozen"     = coalesce("half_frozen", "phase_finished"),
   1.338 +              "fully_frozen"    = "phase_finished",
   1.339 +              "closed"          = "phase_finished",
   1.340 +              "phase_finished"  = NULL
   1.341 +              WHERE "id" = "issue_id_p";
   1.342 +            -- NOTE: The following DELETE statements have effect only when
   1.343 +            --       issue state has been manipulated
   1.344 +            DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
   1.345 +            DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
   1.346 +            DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
   1.347 +          END IF;
   1.348            RETURN NULL;
   1.349          END IF;
   1.350          IF "persist"."state" = 'voting' THEN
   1.351 @@ -4422,7 +4346,7 @@
   1.352  COMMENT ON FUNCTION "check_issue"
   1.353    ( "issue"."id"%TYPE,
   1.354      "check_issue_persistence" )
   1.355 -  IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
   1.356 +  IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
   1.357  
   1.358  
   1.359  CREATE FUNCTION "check_everything"()
   1.360 @@ -4447,7 +4371,7 @@
   1.361      END;
   1.362    $$;
   1.363  
   1.364 -COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
   1.365 +COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
   1.366  
   1.367  
   1.368  

Impressum / About Us