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
- 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