liquid_feedback_core
diff update/core-update.v1.2.4-v1.2.5.sql @ 67:72e5356b5454
Improved locking to avoid unneccessary delays and possible deadlocks
| author | jbe |
|---|---|
| date | Mon Aug 16 14:45:21 2010 +0200 (2010-08-16) |
| parents | |
| children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.2.4-v1.2.5.sql Mon Aug 16 14:45:21 2010 +0200 1.3 @@ -0,0 +1,596 @@ 1.4 +BEGIN; 1.5 + 1.6 + 1.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.8 + SELECT * FROM (VALUES ('1.2.5', 1, 2, 5)) 1.9 + AS "subquery"("string", "major", "minor", "revision"); 1.10 + 1.11 + 1.12 +CREATE FUNCTION "share_row_lock_issue_trigger"() 1.13 + RETURNS TRIGGER 1.14 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.15 + BEGIN 1.16 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.17 + PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE; 1.18 + END IF; 1.19 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.20 + PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE; 1.21 + RETURN NEW; 1.22 + ELSE 1.23 + RETURN OLD; 1.24 + END IF; 1.25 + END; 1.26 + $$; 1.27 + 1.28 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables'; 1.29 + 1.30 + 1.31 +CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"() 1.32 + RETURNS TRIGGER 1.33 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.34 + BEGIN 1.35 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.36 + PERFORM NULL FROM "issue" 1.37 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.38 + WHERE "initiative"."id" = OLD."initiative_id" 1.39 + FOR SHARE OF "issue"; 1.40 + END IF; 1.41 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.42 + PERFORM NULL FROM "issue" 1.43 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.44 + WHERE "initiative"."id" = NEW."initiative_id" 1.45 + FOR SHARE OF "issue"; 1.46 + RETURN NEW; 1.47 + ELSE 1.48 + RETURN OLD; 1.49 + END IF; 1.50 + END; 1.51 + $$; 1.52 + 1.53 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"'; 1.54 + 1.55 + 1.56 +CREATE TRIGGER "share_row_lock_issue" 1.57 + BEFORE INSERT OR UPDATE OR DELETE ON "initiative" 1.58 + FOR EACH ROW EXECUTE PROCEDURE 1.59 + "share_row_lock_issue_trigger"(); 1.60 + 1.61 +CREATE TRIGGER "share_row_lock_issue" 1.62 + BEFORE INSERT OR UPDATE OR DELETE ON "interest" 1.63 + FOR EACH ROW EXECUTE PROCEDURE 1.64 + "share_row_lock_issue_trigger"(); 1.65 + 1.66 +CREATE TRIGGER "share_row_lock_issue" 1.67 + BEFORE INSERT OR UPDATE OR DELETE ON "supporter" 1.68 + FOR EACH ROW EXECUTE PROCEDURE 1.69 + "share_row_lock_issue_trigger"(); 1.70 + 1.71 +CREATE TRIGGER "share_row_lock_issue_via_initiative" 1.72 + BEFORE INSERT OR UPDATE OR DELETE ON "opinion" 1.73 + FOR EACH ROW EXECUTE PROCEDURE 1.74 + "share_row_lock_issue_via_initiative_trigger"(); 1.75 + 1.76 +CREATE TRIGGER "share_row_lock_issue" 1.77 + BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter" 1.78 + FOR EACH ROW EXECUTE PROCEDURE 1.79 + "share_row_lock_issue_trigger"(); 1.80 + 1.81 +CREATE TRIGGER "share_row_lock_issue" 1.82 + BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter" 1.83 + FOR EACH ROW EXECUTE PROCEDURE 1.84 + "share_row_lock_issue_trigger"(); 1.85 + 1.86 +CREATE TRIGGER "share_row_lock_issue" 1.87 + BEFORE INSERT OR UPDATE OR DELETE ON "vote" 1.88 + FOR EACH ROW EXECUTE PROCEDURE 1.89 + "share_row_lock_issue_trigger"(); 1.90 + 1.91 +COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function'; 1.92 +COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function'; 1.93 +COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function'; 1.94 +COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function'; 1.95 +COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function'; 1.96 +COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function'; 1.97 +COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function'; 1.98 + 1.99 + 1.100 +CREATE FUNCTION "lock_issue" 1.101 + ( "issue_id_p" "issue"."id"%TYPE ) 1.102 + RETURNS VOID 1.103 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.104 + BEGIN 1.105 + LOCK TABLE "member" IN SHARE MODE; 1.106 + LOCK TABLE "membership" IN SHARE MODE; 1.107 + LOCK TABLE "policy" IN SHARE MODE; 1.108 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.109 + -- NOTE: The row-level exclusive lock in combination with the 1.110 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 1.111 + -- acquire a row-level share lock on the issue) ensure that no data 1.112 + -- is changed, which could affect calculation of snapshots or 1.113 + -- counting of votes. Table "delegation" must be table-level-locked, 1.114 + -- as it also contains issue- and global-scope delegations. 1.115 + LOCK TABLE "delegation" IN SHARE MODE; 1.116 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 1.117 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 1.118 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 1.119 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 1.120 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 1.121 + RETURN; 1.122 + END; 1.123 + $$; 1.124 + 1.125 +COMMENT ON FUNCTION "lock_issue" 1.126 + ( "issue"."id"%TYPE ) 1.127 + IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.'; 1.128 + 1.129 + 1.130 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.131 + RETURNS VOID 1.132 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.133 + BEGIN 1.134 + LOCK TABLE "member" IN SHARE MODE; 1.135 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.136 + LOCK TABLE "area" IN EXCLUSIVE MODE; 1.137 + LOCK TABLE "membership" IN SHARE MODE; 1.138 + DELETE FROM "member_count"; 1.139 + INSERT INTO "member_count" ("total_count") 1.140 + SELECT "total_count" FROM "member_count_view"; 1.141 + UPDATE "area" SET 1.142 + "direct_member_count" = "view"."direct_member_count", 1.143 + "member_weight" = "view"."member_weight", 1.144 + "autoreject_weight" = "view"."autoreject_weight" 1.145 + FROM "area_member_count" AS "view" 1.146 + WHERE "view"."area_id" = "area"."id"; 1.147 + RETURN; 1.148 + END; 1.149 + $$; 1.150 + 1.151 +CREATE OR REPLACE FUNCTION "create_snapshot" 1.152 + ( "issue_id_p" "issue"."id"%TYPE ) 1.153 + RETURNS VOID 1.154 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.155 + DECLARE 1.156 + "initiative_id_v" "initiative"."id"%TYPE; 1.157 + "suggestion_id_v" "suggestion"."id"%TYPE; 1.158 + BEGIN 1.159 + PERFORM "lock_issue"("issue_id_p"); 1.160 + PERFORM "create_population_snapshot"("issue_id_p"); 1.161 + PERFORM "create_interest_snapshot"("issue_id_p"); 1.162 + UPDATE "issue" SET 1.163 + "snapshot" = now(), 1.164 + "latest_snapshot_event" = 'periodic', 1.165 + "population" = ( 1.166 + SELECT coalesce(sum("weight"), 0) 1.167 + FROM "direct_population_snapshot" 1.168 + WHERE "issue_id" = "issue_id_p" 1.169 + AND "event" = 'periodic' 1.170 + ), 1.171 + "vote_now" = ( 1.172 + SELECT coalesce(sum("weight"), 0) 1.173 + FROM "direct_interest_snapshot" 1.174 + WHERE "issue_id" = "issue_id_p" 1.175 + AND "event" = 'periodic' 1.176 + AND "voting_requested" = TRUE 1.177 + ), 1.178 + "vote_later" = ( 1.179 + SELECT coalesce(sum("weight"), 0) 1.180 + FROM "direct_interest_snapshot" 1.181 + WHERE "issue_id" = "issue_id_p" 1.182 + AND "event" = 'periodic' 1.183 + AND "voting_requested" = FALSE 1.184 + ) 1.185 + WHERE "id" = "issue_id_p"; 1.186 + FOR "initiative_id_v" IN 1.187 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.188 + LOOP 1.189 + UPDATE "initiative" SET 1.190 + "supporter_count" = ( 1.191 + SELECT coalesce(sum("di"."weight"), 0) 1.192 + FROM "direct_interest_snapshot" AS "di" 1.193 + JOIN "direct_supporter_snapshot" AS "ds" 1.194 + ON "di"."member_id" = "ds"."member_id" 1.195 + WHERE "di"."issue_id" = "issue_id_p" 1.196 + AND "di"."event" = 'periodic' 1.197 + AND "ds"."initiative_id" = "initiative_id_v" 1.198 + AND "ds"."event" = 'periodic' 1.199 + ), 1.200 + "informed_supporter_count" = ( 1.201 + SELECT coalesce(sum("di"."weight"), 0) 1.202 + FROM "direct_interest_snapshot" AS "di" 1.203 + JOIN "direct_supporter_snapshot" AS "ds" 1.204 + ON "di"."member_id" = "ds"."member_id" 1.205 + WHERE "di"."issue_id" = "issue_id_p" 1.206 + AND "di"."event" = 'periodic' 1.207 + AND "ds"."initiative_id" = "initiative_id_v" 1.208 + AND "ds"."event" = 'periodic' 1.209 + AND "ds"."informed" 1.210 + ), 1.211 + "satisfied_supporter_count" = ( 1.212 + SELECT coalesce(sum("di"."weight"), 0) 1.213 + FROM "direct_interest_snapshot" AS "di" 1.214 + JOIN "direct_supporter_snapshot" AS "ds" 1.215 + ON "di"."member_id" = "ds"."member_id" 1.216 + WHERE "di"."issue_id" = "issue_id_p" 1.217 + AND "di"."event" = 'periodic' 1.218 + AND "ds"."initiative_id" = "initiative_id_v" 1.219 + AND "ds"."event" = 'periodic' 1.220 + AND "ds"."satisfied" 1.221 + ), 1.222 + "satisfied_informed_supporter_count" = ( 1.223 + SELECT coalesce(sum("di"."weight"), 0) 1.224 + FROM "direct_interest_snapshot" AS "di" 1.225 + JOIN "direct_supporter_snapshot" AS "ds" 1.226 + ON "di"."member_id" = "ds"."member_id" 1.227 + WHERE "di"."issue_id" = "issue_id_p" 1.228 + AND "di"."event" = 'periodic' 1.229 + AND "ds"."initiative_id" = "initiative_id_v" 1.230 + AND "ds"."event" = 'periodic' 1.231 + AND "ds"."informed" 1.232 + AND "ds"."satisfied" 1.233 + ) 1.234 + WHERE "id" = "initiative_id_v"; 1.235 + FOR "suggestion_id_v" IN 1.236 + SELECT "id" FROM "suggestion" 1.237 + WHERE "initiative_id" = "initiative_id_v" 1.238 + LOOP 1.239 + UPDATE "suggestion" SET 1.240 + "minus2_unfulfilled_count" = ( 1.241 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.242 + FROM "issue" CROSS JOIN "opinion" 1.243 + JOIN "direct_interest_snapshot" AS "snapshot" 1.244 + ON "snapshot"."issue_id" = "issue"."id" 1.245 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.246 + AND "snapshot"."member_id" = "opinion"."member_id" 1.247 + WHERE "issue"."id" = "issue_id_p" 1.248 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.249 + AND "opinion"."degree" = -2 1.250 + AND "opinion"."fulfilled" = FALSE 1.251 + ), 1.252 + "minus2_fulfilled_count" = ( 1.253 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.254 + FROM "issue" CROSS JOIN "opinion" 1.255 + JOIN "direct_interest_snapshot" AS "snapshot" 1.256 + ON "snapshot"."issue_id" = "issue"."id" 1.257 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.258 + AND "snapshot"."member_id" = "opinion"."member_id" 1.259 + WHERE "issue"."id" = "issue_id_p" 1.260 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.261 + AND "opinion"."degree" = -2 1.262 + AND "opinion"."fulfilled" = TRUE 1.263 + ), 1.264 + "minus1_unfulfilled_count" = ( 1.265 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.266 + FROM "issue" CROSS JOIN "opinion" 1.267 + JOIN "direct_interest_snapshot" AS "snapshot" 1.268 + ON "snapshot"."issue_id" = "issue"."id" 1.269 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.270 + AND "snapshot"."member_id" = "opinion"."member_id" 1.271 + WHERE "issue"."id" = "issue_id_p" 1.272 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.273 + AND "opinion"."degree" = -1 1.274 + AND "opinion"."fulfilled" = FALSE 1.275 + ), 1.276 + "minus1_fulfilled_count" = ( 1.277 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.278 + FROM "issue" CROSS JOIN "opinion" 1.279 + JOIN "direct_interest_snapshot" AS "snapshot" 1.280 + ON "snapshot"."issue_id" = "issue"."id" 1.281 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.282 + AND "snapshot"."member_id" = "opinion"."member_id" 1.283 + WHERE "issue"."id" = "issue_id_p" 1.284 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.285 + AND "opinion"."degree" = -1 1.286 + AND "opinion"."fulfilled" = TRUE 1.287 + ), 1.288 + "plus1_unfulfilled_count" = ( 1.289 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.290 + FROM "issue" CROSS JOIN "opinion" 1.291 + JOIN "direct_interest_snapshot" AS "snapshot" 1.292 + ON "snapshot"."issue_id" = "issue"."id" 1.293 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.294 + AND "snapshot"."member_id" = "opinion"."member_id" 1.295 + WHERE "issue"."id" = "issue_id_p" 1.296 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.297 + AND "opinion"."degree" = 1 1.298 + AND "opinion"."fulfilled" = FALSE 1.299 + ), 1.300 + "plus1_fulfilled_count" = ( 1.301 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.302 + FROM "issue" CROSS JOIN "opinion" 1.303 + JOIN "direct_interest_snapshot" AS "snapshot" 1.304 + ON "snapshot"."issue_id" = "issue"."id" 1.305 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.306 + AND "snapshot"."member_id" = "opinion"."member_id" 1.307 + WHERE "issue"."id" = "issue_id_p" 1.308 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.309 + AND "opinion"."degree" = 1 1.310 + AND "opinion"."fulfilled" = TRUE 1.311 + ), 1.312 + "plus2_unfulfilled_count" = ( 1.313 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.314 + FROM "issue" CROSS JOIN "opinion" 1.315 + JOIN "direct_interest_snapshot" AS "snapshot" 1.316 + ON "snapshot"."issue_id" = "issue"."id" 1.317 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.318 + AND "snapshot"."member_id" = "opinion"."member_id" 1.319 + WHERE "issue"."id" = "issue_id_p" 1.320 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.321 + AND "opinion"."degree" = 2 1.322 + AND "opinion"."fulfilled" = FALSE 1.323 + ), 1.324 + "plus2_fulfilled_count" = ( 1.325 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.326 + FROM "issue" CROSS JOIN "opinion" 1.327 + JOIN "direct_interest_snapshot" AS "snapshot" 1.328 + ON "snapshot"."issue_id" = "issue"."id" 1.329 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.330 + AND "snapshot"."member_id" = "opinion"."member_id" 1.331 + WHERE "issue"."id" = "issue_id_p" 1.332 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.333 + AND "opinion"."degree" = 2 1.334 + AND "opinion"."fulfilled" = TRUE 1.335 + ) 1.336 + WHERE "suggestion"."id" = "suggestion_id_v"; 1.337 + END LOOP; 1.338 + END LOOP; 1.339 + RETURN; 1.340 + END; 1.341 + $$; 1.342 + 1.343 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.344 + RETURNS VOID 1.345 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.346 + DECLARE 1.347 + "issue_row" "issue"%ROWTYPE; 1.348 + "member_id_v" "member"."id"%TYPE; 1.349 + BEGIN 1.350 + PERFORM "lock_issue"("issue_id_p"); 1.351 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.352 + DELETE FROM "delegating_voter" 1.353 + WHERE "issue_id" = "issue_id_p"; 1.354 + DELETE FROM "direct_voter" 1.355 + WHERE "issue_id" = "issue_id_p" 1.356 + AND "autoreject" = TRUE; 1.357 + DELETE FROM "direct_voter" USING "member" 1.358 + WHERE "direct_voter"."member_id" = "member"."id" 1.359 + AND "direct_voter"."issue_id" = "issue_id_p" 1.360 + AND "member"."active" = FALSE; 1.361 + UPDATE "direct_voter" SET "weight" = 1 1.362 + WHERE "issue_id" = "issue_id_p"; 1.363 + PERFORM "add_vote_delegations"("issue_id_p"); 1.364 + FOR "member_id_v" IN 1.365 + SELECT "interest"."member_id" 1.366 + FROM "interest" 1.367 + LEFT JOIN "direct_voter" 1.368 + ON "interest"."member_id" = "direct_voter"."member_id" 1.369 + AND "interest"."issue_id" = "direct_voter"."issue_id" 1.370 + LEFT JOIN "delegating_voter" 1.371 + ON "interest"."member_id" = "delegating_voter"."member_id" 1.372 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 1.373 + WHERE "interest"."issue_id" = "issue_id_p" 1.374 + AND "interest"."autoreject" = TRUE 1.375 + AND "direct_voter"."member_id" ISNULL 1.376 + AND "delegating_voter"."member_id" ISNULL 1.377 + UNION SELECT "membership"."member_id" 1.378 + FROM "membership" 1.379 + LEFT JOIN "interest" 1.380 + ON "membership"."member_id" = "interest"."member_id" 1.381 + AND "interest"."issue_id" = "issue_id_p" 1.382 + LEFT JOIN "direct_voter" 1.383 + ON "membership"."member_id" = "direct_voter"."member_id" 1.384 + AND "direct_voter"."issue_id" = "issue_id_p" 1.385 + LEFT JOIN "delegating_voter" 1.386 + ON "membership"."member_id" = "delegating_voter"."member_id" 1.387 + AND "delegating_voter"."issue_id" = "issue_id_p" 1.388 + WHERE "membership"."area_id" = "issue_row"."area_id" 1.389 + AND "membership"."autoreject" = TRUE 1.390 + AND "interest"."autoreject" ISNULL 1.391 + AND "direct_voter"."member_id" ISNULL 1.392 + AND "delegating_voter"."member_id" ISNULL 1.393 + LOOP 1.394 + INSERT INTO "direct_voter" 1.395 + ("member_id", "issue_id", "weight", "autoreject") VALUES 1.396 + ("member_id_v", "issue_id_p", 1, TRUE); 1.397 + INSERT INTO "vote" ( 1.398 + "member_id", 1.399 + "issue_id", 1.400 + "initiative_id", 1.401 + "grade" 1.402 + ) SELECT 1.403 + "member_id_v" AS "member_id", 1.404 + "issue_id_p" AS "issue_id", 1.405 + "id" AS "initiative_id", 1.406 + -1 AS "grade" 1.407 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.408 + END LOOP; 1.409 + PERFORM "add_vote_delegations"("issue_id_p"); 1.410 + UPDATE "issue" SET 1.411 + "closed" = now(), 1.412 + "voter_count" = ( 1.413 + SELECT coalesce(sum("weight"), 0) 1.414 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.415 + ) 1.416 + WHERE "id" = "issue_id_p"; 1.417 + UPDATE "initiative" SET 1.418 + "positive_votes" = "vote_counts"."positive_votes", 1.419 + "negative_votes" = "vote_counts"."negative_votes", 1.420 + "agreed" = CASE WHEN "majority_strict" THEN 1.421 + "vote_counts"."positive_votes" * "majority_den" > 1.422 + "majority_num" * 1.423 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.424 + ELSE 1.425 + "vote_counts"."positive_votes" * "majority_den" >= 1.426 + "majority_num" * 1.427 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.428 + END 1.429 + FROM 1.430 + ( SELECT 1.431 + "initiative"."id" AS "initiative_id", 1.432 + coalesce( 1.433 + sum( 1.434 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.435 + ), 1.436 + 0 1.437 + ) AS "positive_votes", 1.438 + coalesce( 1.439 + sum( 1.440 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 1.441 + ), 1.442 + 0 1.443 + ) AS "negative_votes" 1.444 + FROM "initiative" 1.445 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.446 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.447 + LEFT JOIN "direct_voter" 1.448 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.449 + LEFT JOIN "vote" 1.450 + ON "vote"."initiative_id" = "initiative"."id" 1.451 + AND "vote"."member_id" = "direct_voter"."member_id" 1.452 + WHERE "initiative"."issue_id" = "issue_id_p" 1.453 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 1.454 + GROUP BY "initiative"."id" 1.455 + ) AS "vote_counts", 1.456 + "issue", 1.457 + "policy" 1.458 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.459 + AND "issue"."id" = "initiative"."issue_id" 1.460 + AND "policy"."id" = "issue"."policy_id"; 1.461 + -- NOTE: "closed" column of issue must be set at this point 1.462 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.463 + INSERT INTO "battle" ( 1.464 + "issue_id", 1.465 + "winning_initiative_id", "losing_initiative_id", 1.466 + "count" 1.467 + ) SELECT 1.468 + "issue_id", 1.469 + "winning_initiative_id", "losing_initiative_id", 1.470 + "count" 1.471 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.472 + END; 1.473 + $$; 1.474 + 1.475 +CREATE OR REPLACE FUNCTION "check_issue" 1.476 + ( "issue_id_p" "issue"."id"%TYPE ) 1.477 + RETURNS VOID 1.478 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.479 + DECLARE 1.480 + "issue_row" "issue"%ROWTYPE; 1.481 + "policy_row" "policy"%ROWTYPE; 1.482 + "voting_requested_v" BOOLEAN; 1.483 + BEGIN 1.484 + PERFORM "lock_issue"("issue_id_p"); 1.485 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.486 + -- only process open issues: 1.487 + IF "issue_row"."closed" ISNULL THEN 1.488 + SELECT * INTO "policy_row" FROM "policy" 1.489 + WHERE "id" = "issue_row"."policy_id"; 1.490 + -- create a snapshot, unless issue is already fully frozen: 1.491 + IF "issue_row"."fully_frozen" ISNULL THEN 1.492 + PERFORM "create_snapshot"("issue_id_p"); 1.493 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.494 + END IF; 1.495 + -- eventually close or accept issues, which have not been accepted: 1.496 + IF "issue_row"."accepted" ISNULL THEN 1.497 + IF EXISTS ( 1.498 + SELECT NULL FROM "initiative" 1.499 + WHERE "issue_id" = "issue_id_p" 1.500 + AND "supporter_count" > 0 1.501 + AND "supporter_count" * "policy_row"."issue_quorum_den" 1.502 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.503 + ) THEN 1.504 + -- accept issues, if supporter count is high enough 1.505 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.506 + "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later 1.507 + UPDATE "issue" SET "accepted" = "issue_row"."accepted" 1.508 + WHERE "id" = "issue_row"."id"; 1.509 + ELSIF 1.510 + now() >= "issue_row"."created" + "issue_row"."admission_time" 1.511 + THEN 1.512 + -- close issues, if admission time has expired 1.513 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.514 + UPDATE "issue" SET "closed" = now() 1.515 + WHERE "id" = "issue_row"."id"; 1.516 + END IF; 1.517 + END IF; 1.518 + -- eventually half freeze issues: 1.519 + IF 1.520 + -- NOTE: issue can't be closed at this point, if it has been accepted 1.521 + "issue_row"."accepted" NOTNULL AND 1.522 + "issue_row"."half_frozen" ISNULL 1.523 + THEN 1.524 + SELECT 1.525 + CASE 1.526 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 1.527 + TRUE 1.528 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 1.529 + FALSE 1.530 + ELSE NULL 1.531 + END 1.532 + INTO "voting_requested_v" 1.533 + FROM "issue" WHERE "id" = "issue_id_p"; 1.534 + IF 1.535 + "voting_requested_v" OR ( 1.536 + "voting_requested_v" ISNULL AND 1.537 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.538 + ) 1.539 + THEN 1.540 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.541 + "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later 1.542 + UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen" 1.543 + WHERE "id" = "issue_row"."id"; 1.544 + END IF; 1.545 + END IF; 1.546 + -- close issues after some time, if all initiatives have been revoked: 1.547 + IF 1.548 + "issue_row"."closed" ISNULL AND 1.549 + NOT EXISTS ( 1.550 + -- all initiatives are revoked 1.551 + SELECT NULL FROM "initiative" 1.552 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.553 + ) AND ( 1.554 + NOT EXISTS ( 1.555 + -- and no initiatives have been revoked lately 1.556 + SELECT NULL FROM "initiative" 1.557 + WHERE "issue_id" = "issue_id_p" 1.558 + AND now() < "revoked" + "issue_row"."verification_time" 1.559 + ) OR ( 1.560 + -- or verification time has elapsed 1.561 + "issue_row"."half_frozen" NOTNULL AND 1.562 + "issue_row"."fully_frozen" ISNULL AND 1.563 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.564 + ) 1.565 + ) 1.566 + THEN 1.567 + "issue_row"."closed" = now(); -- NOTE: "issue_row" used later 1.568 + UPDATE "issue" SET "closed" = "issue_row"."closed" 1.569 + WHERE "id" = "issue_row"."id"; 1.570 + END IF; 1.571 + -- fully freeze issue after verification time: 1.572 + IF 1.573 + "issue_row"."half_frozen" NOTNULL AND 1.574 + "issue_row"."fully_frozen" ISNULL AND 1.575 + "issue_row"."closed" ISNULL AND 1.576 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.577 + THEN 1.578 + PERFORM "freeze_after_snapshot"("issue_id_p"); 1.579 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 1.580 + END IF; 1.581 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.582 + -- close issue by calling close_voting(...) after voting time: 1.583 + IF 1.584 + "issue_row"."closed" ISNULL AND 1.585 + "issue_row"."fully_frozen" NOTNULL AND 1.586 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.587 + THEN 1.588 + PERFORM "close_voting"("issue_id_p"); 1.589 + END IF; 1.590 + END IF; 1.591 + RETURN; 1.592 + END; 1.593 + $$; 1.594 + 1.595 + 1.596 +DROP FUNCTION "global_lock"(); 1.597 + 1.598 + 1.599 +COMMIT;