liquid_feedback_core
changeset 67:72e5356b5454 v1.2.5
Improved locking to avoid unneccessary delays and possible deadlocks
| author | jbe |
|---|---|
| date | Mon Aug 16 14:45:21 2010 +0200 (2010-08-16) |
| parents | bdee8dc73a63 |
| children | 28c1af992cd3 |
| files | core.sql update/core-update.v1.2.4-v1.2.5.sql |
line diff
1.1 --- a/core.sql Sun Aug 15 17:11:02 2010 +0200 1.2 +++ b/core.sql Mon Aug 16 14:45:21 2010 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.2.4', 1, 2, 4)) 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 @@ -1917,39 +1917,123 @@ 1.13 -- Locking for snapshots and voting procedure -- 1.14 ------------------------------------------------ 1.15 1.16 -CREATE FUNCTION "global_lock"() RETURNS VOID 1.17 + 1.18 +CREATE FUNCTION "share_row_lock_issue_trigger"() 1.19 + RETURNS TRIGGER 1.20 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.21 + BEGIN 1.22 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.23 + PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE; 1.24 + END IF; 1.25 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.26 + PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE; 1.27 + RETURN NEW; 1.28 + ELSE 1.29 + RETURN OLD; 1.30 + END IF; 1.31 + END; 1.32 + $$; 1.33 + 1.34 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables'; 1.35 + 1.36 + 1.37 +CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"() 1.38 + RETURNS TRIGGER 1.39 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.40 BEGIN 1.41 - -- NOTE: PostgreSQL allows reading, while tables are locked in 1.42 - -- exclusive move. Transactions should be kept short anyway! 1.43 - LOCK TABLE "member" IN EXCLUSIVE MODE; 1.44 - LOCK TABLE "area" IN EXCLUSIVE MODE; 1.45 - LOCK TABLE "membership" IN EXCLUSIVE MODE; 1.46 - -- NOTE: "member", "area" and "membership" are locked first to 1.47 - -- prevent deadlocks in combination with "calculate_member_counts"() 1.48 - LOCK TABLE "policy" IN EXCLUSIVE MODE; 1.49 - LOCK TABLE "issue" IN EXCLUSIVE MODE; 1.50 - LOCK TABLE "initiative" IN EXCLUSIVE MODE; 1.51 - LOCK TABLE "draft" IN EXCLUSIVE MODE; 1.52 - LOCK TABLE "suggestion" IN EXCLUSIVE MODE; 1.53 - LOCK TABLE "interest" IN EXCLUSIVE MODE; 1.54 - LOCK TABLE "initiator" IN EXCLUSIVE MODE; 1.55 - LOCK TABLE "supporter" IN EXCLUSIVE MODE; 1.56 - LOCK TABLE "opinion" IN EXCLUSIVE MODE; 1.57 - LOCK TABLE "delegation" IN EXCLUSIVE MODE; 1.58 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.59 + PERFORM NULL FROM "issue" 1.60 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.61 + WHERE "initiative"."id" = OLD."initiative_id" 1.62 + FOR SHARE OF "issue"; 1.63 + END IF; 1.64 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.65 + PERFORM NULL FROM "issue" 1.66 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.67 + WHERE "initiative"."id" = NEW."initiative_id" 1.68 + FOR SHARE OF "issue"; 1.69 + RETURN NEW; 1.70 + ELSE 1.71 + RETURN OLD; 1.72 + END IF; 1.73 + END; 1.74 + $$; 1.75 + 1.76 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"'; 1.77 + 1.78 + 1.79 +CREATE TRIGGER "share_row_lock_issue" 1.80 + BEFORE INSERT OR UPDATE OR DELETE ON "initiative" 1.81 + FOR EACH ROW EXECUTE PROCEDURE 1.82 + "share_row_lock_issue_trigger"(); 1.83 + 1.84 +CREATE TRIGGER "share_row_lock_issue" 1.85 + BEFORE INSERT OR UPDATE OR DELETE ON "interest" 1.86 + FOR EACH ROW EXECUTE PROCEDURE 1.87 + "share_row_lock_issue_trigger"(); 1.88 + 1.89 +CREATE TRIGGER "share_row_lock_issue" 1.90 + BEFORE INSERT OR UPDATE OR DELETE ON "supporter" 1.91 + FOR EACH ROW EXECUTE PROCEDURE 1.92 + "share_row_lock_issue_trigger"(); 1.93 + 1.94 +CREATE TRIGGER "share_row_lock_issue_via_initiative" 1.95 + BEFORE INSERT OR UPDATE OR DELETE ON "opinion" 1.96 + FOR EACH ROW EXECUTE PROCEDURE 1.97 + "share_row_lock_issue_via_initiative_trigger"(); 1.98 + 1.99 +CREATE TRIGGER "share_row_lock_issue" 1.100 + BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter" 1.101 + FOR EACH ROW EXECUTE PROCEDURE 1.102 + "share_row_lock_issue_trigger"(); 1.103 + 1.104 +CREATE TRIGGER "share_row_lock_issue" 1.105 + BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter" 1.106 + FOR EACH ROW EXECUTE PROCEDURE 1.107 + "share_row_lock_issue_trigger"(); 1.108 + 1.109 +CREATE TRIGGER "share_row_lock_issue" 1.110 + BEFORE INSERT OR UPDATE OR DELETE ON "vote" 1.111 + FOR EACH ROW EXECUTE PROCEDURE 1.112 + "share_row_lock_issue_trigger"(); 1.113 + 1.114 +COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function'; 1.115 +COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function'; 1.116 +COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function'; 1.117 +COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function'; 1.118 +COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function'; 1.119 +COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function'; 1.120 +COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function'; 1.121 + 1.122 + 1.123 +CREATE FUNCTION "lock_issue" 1.124 + ( "issue_id_p" "issue"."id"%TYPE ) 1.125 + RETURNS VOID 1.126 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.127 + BEGIN 1.128 + LOCK TABLE "member" IN SHARE MODE; 1.129 + LOCK TABLE "membership" IN SHARE MODE; 1.130 + LOCK TABLE "policy" IN SHARE MODE; 1.131 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.132 + -- NOTE: The row-level exclusive lock in combination with the 1.133 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 1.134 + -- acquire a row-level share lock on the issue) ensure that no data 1.135 + -- is changed, which could affect calculation of snapshots or 1.136 + -- counting of votes. Table "delegation" must be table-level-locked, 1.137 + -- as it also contains issue- and global-scope delegations. 1.138 + LOCK TABLE "delegation" IN SHARE MODE; 1.139 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 1.140 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 1.141 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 1.142 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 1.143 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 1.144 - LOCK TABLE "direct_voter" IN EXCLUSIVE MODE; 1.145 - LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE; 1.146 - LOCK TABLE "vote" IN EXCLUSIVE MODE; 1.147 RETURN; 1.148 END; 1.149 $$; 1.150 1.151 -COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though'; 1.152 +COMMENT ON FUNCTION "lock_issue" 1.153 + ( "issue"."id"%TYPE ) 1.154 + IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.'; 1.155 1.156 1.157 1.158 @@ -1961,9 +2045,10 @@ 1.159 RETURNS VOID 1.160 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.161 BEGIN 1.162 - LOCK TABLE "member" IN EXCLUSIVE MODE; 1.163 - LOCK TABLE "area" IN EXCLUSIVE MODE; 1.164 - LOCK TABLE "membership" IN EXCLUSIVE MODE; 1.165 + LOCK TABLE "member" IN SHARE MODE; 1.166 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.167 + LOCK TABLE "area" IN EXCLUSIVE MODE; 1.168 + LOCK TABLE "membership" IN SHARE MODE; 1.169 DELETE FROM "member_count"; 1.170 INSERT INTO "member_count" ("total_count") 1.171 SELECT "total_count" FROM "member_count_view"; 1.172 @@ -2109,7 +2194,7 @@ 1.173 $$; 1.174 1.175 COMMENT ON FUNCTION "create_population_snapshot" 1.176 - ( "issue_id_p" "issue"."id"%TYPE ) 1.177 + ( "issue"."id"%TYPE ) 1.178 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.'; 1.179 1.180 1.181 @@ -2269,7 +2354,7 @@ 1.182 "initiative_id_v" "initiative"."id"%TYPE; 1.183 "suggestion_id_v" "suggestion"."id"%TYPE; 1.184 BEGIN 1.185 - PERFORM "global_lock"(); 1.186 + PERFORM "lock_issue"("issue_id_p"); 1.187 PERFORM "create_population_snapshot"("issue_id_p"); 1.188 PERFORM "create_interest_snapshot"("issue_id_p"); 1.189 UPDATE "issue" SET 1.190 @@ -2668,7 +2753,7 @@ 1.191 "issue_row" "issue"%ROWTYPE; 1.192 "member_id_v" "member"."id"%TYPE; 1.193 BEGIN 1.194 - PERFORM "global_lock"(); 1.195 + PERFORM "lock_issue"("issue_id_p"); 1.196 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.197 DELETE FROM "delegating_voter" 1.198 WHERE "issue_id" = "issue_id_p"; 1.199 @@ -3068,7 +3153,7 @@ 1.200 "policy_row" "policy"%ROWTYPE; 1.201 "voting_requested_v" BOOLEAN; 1.202 BEGIN 1.203 - PERFORM "global_lock"(); 1.204 + PERFORM "lock_issue"("issue_id_p"); 1.205 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.206 -- only process open issues: 1.207 IF "issue_row"."closed" ISNULL THEN
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.2.4-v1.2.5.sql Mon Aug 16 14:45:21 2010 +0200 2.3 @@ -0,0 +1,596 @@ 2.4 +BEGIN; 2.5 + 2.6 + 2.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.8 + SELECT * FROM (VALUES ('1.2.5', 1, 2, 5)) 2.9 + AS "subquery"("string", "major", "minor", "revision"); 2.10 + 2.11 + 2.12 +CREATE FUNCTION "share_row_lock_issue_trigger"() 2.13 + RETURNS TRIGGER 2.14 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.15 + BEGIN 2.16 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 2.17 + PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE; 2.18 + END IF; 2.19 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 2.20 + PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE; 2.21 + RETURN NEW; 2.22 + ELSE 2.23 + RETURN OLD; 2.24 + END IF; 2.25 + END; 2.26 + $$; 2.27 + 2.28 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables'; 2.29 + 2.30 + 2.31 +CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"() 2.32 + RETURNS TRIGGER 2.33 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.34 + BEGIN 2.35 + IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 2.36 + PERFORM NULL FROM "issue" 2.37 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 2.38 + WHERE "initiative"."id" = OLD."initiative_id" 2.39 + FOR SHARE OF "issue"; 2.40 + END IF; 2.41 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 2.42 + PERFORM NULL FROM "issue" 2.43 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 2.44 + WHERE "initiative"."id" = NEW."initiative_id" 2.45 + FOR SHARE OF "issue"; 2.46 + RETURN NEW; 2.47 + ELSE 2.48 + RETURN OLD; 2.49 + END IF; 2.50 + END; 2.51 + $$; 2.52 + 2.53 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"'; 2.54 + 2.55 + 2.56 +CREATE TRIGGER "share_row_lock_issue" 2.57 + BEFORE INSERT OR UPDATE OR DELETE ON "initiative" 2.58 + FOR EACH ROW EXECUTE PROCEDURE 2.59 + "share_row_lock_issue_trigger"(); 2.60 + 2.61 +CREATE TRIGGER "share_row_lock_issue" 2.62 + BEFORE INSERT OR UPDATE OR DELETE ON "interest" 2.63 + FOR EACH ROW EXECUTE PROCEDURE 2.64 + "share_row_lock_issue_trigger"(); 2.65 + 2.66 +CREATE TRIGGER "share_row_lock_issue" 2.67 + BEFORE INSERT OR UPDATE OR DELETE ON "supporter" 2.68 + FOR EACH ROW EXECUTE PROCEDURE 2.69 + "share_row_lock_issue_trigger"(); 2.70 + 2.71 +CREATE TRIGGER "share_row_lock_issue_via_initiative" 2.72 + BEFORE INSERT OR UPDATE OR DELETE ON "opinion" 2.73 + FOR EACH ROW EXECUTE PROCEDURE 2.74 + "share_row_lock_issue_via_initiative_trigger"(); 2.75 + 2.76 +CREATE TRIGGER "share_row_lock_issue" 2.77 + BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter" 2.78 + FOR EACH ROW EXECUTE PROCEDURE 2.79 + "share_row_lock_issue_trigger"(); 2.80 + 2.81 +CREATE TRIGGER "share_row_lock_issue" 2.82 + BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter" 2.83 + FOR EACH ROW EXECUTE PROCEDURE 2.84 + "share_row_lock_issue_trigger"(); 2.85 + 2.86 +CREATE TRIGGER "share_row_lock_issue" 2.87 + BEFORE INSERT OR UPDATE OR DELETE ON "vote" 2.88 + FOR EACH ROW EXECUTE PROCEDURE 2.89 + "share_row_lock_issue_trigger"(); 2.90 + 2.91 +COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function'; 2.92 +COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function'; 2.93 +COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function'; 2.94 +COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function'; 2.95 +COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function'; 2.96 +COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function'; 2.97 +COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function'; 2.98 + 2.99 + 2.100 +CREATE FUNCTION "lock_issue" 2.101 + ( "issue_id_p" "issue"."id"%TYPE ) 2.102 + RETURNS VOID 2.103 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.104 + BEGIN 2.105 + LOCK TABLE "member" IN SHARE MODE; 2.106 + LOCK TABLE "membership" IN SHARE MODE; 2.107 + LOCK TABLE "policy" IN SHARE MODE; 2.108 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 2.109 + -- NOTE: The row-level exclusive lock in combination with the 2.110 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 2.111 + -- acquire a row-level share lock on the issue) ensure that no data 2.112 + -- is changed, which could affect calculation of snapshots or 2.113 + -- counting of votes. Table "delegation" must be table-level-locked, 2.114 + -- as it also contains issue- and global-scope delegations. 2.115 + LOCK TABLE "delegation" IN SHARE MODE; 2.116 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 2.117 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 2.118 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 2.119 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 2.120 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 2.121 + RETURN; 2.122 + END; 2.123 + $$; 2.124 + 2.125 +COMMENT ON FUNCTION "lock_issue" 2.126 + ( "issue"."id"%TYPE ) 2.127 + IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.'; 2.128 + 2.129 + 2.130 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 2.131 + RETURNS VOID 2.132 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.133 + BEGIN 2.134 + LOCK TABLE "member" IN SHARE MODE; 2.135 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 2.136 + LOCK TABLE "area" IN EXCLUSIVE MODE; 2.137 + LOCK TABLE "membership" IN SHARE MODE; 2.138 + DELETE FROM "member_count"; 2.139 + INSERT INTO "member_count" ("total_count") 2.140 + SELECT "total_count" FROM "member_count_view"; 2.141 + UPDATE "area" SET 2.142 + "direct_member_count" = "view"."direct_member_count", 2.143 + "member_weight" = "view"."member_weight", 2.144 + "autoreject_weight" = "view"."autoreject_weight" 2.145 + FROM "area_member_count" AS "view" 2.146 + WHERE "view"."area_id" = "area"."id"; 2.147 + RETURN; 2.148 + END; 2.149 + $$; 2.150 + 2.151 +CREATE OR REPLACE FUNCTION "create_snapshot" 2.152 + ( "issue_id_p" "issue"."id"%TYPE ) 2.153 + RETURNS VOID 2.154 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.155 + DECLARE 2.156 + "initiative_id_v" "initiative"."id"%TYPE; 2.157 + "suggestion_id_v" "suggestion"."id"%TYPE; 2.158 + BEGIN 2.159 + PERFORM "lock_issue"("issue_id_p"); 2.160 + PERFORM "create_population_snapshot"("issue_id_p"); 2.161 + PERFORM "create_interest_snapshot"("issue_id_p"); 2.162 + UPDATE "issue" SET 2.163 + "snapshot" = now(), 2.164 + "latest_snapshot_event" = 'periodic', 2.165 + "population" = ( 2.166 + SELECT coalesce(sum("weight"), 0) 2.167 + FROM "direct_population_snapshot" 2.168 + WHERE "issue_id" = "issue_id_p" 2.169 + AND "event" = 'periodic' 2.170 + ), 2.171 + "vote_now" = ( 2.172 + SELECT coalesce(sum("weight"), 0) 2.173 + FROM "direct_interest_snapshot" 2.174 + WHERE "issue_id" = "issue_id_p" 2.175 + AND "event" = 'periodic' 2.176 + AND "voting_requested" = TRUE 2.177 + ), 2.178 + "vote_later" = ( 2.179 + SELECT coalesce(sum("weight"), 0) 2.180 + FROM "direct_interest_snapshot" 2.181 + WHERE "issue_id" = "issue_id_p" 2.182 + AND "event" = 'periodic' 2.183 + AND "voting_requested" = FALSE 2.184 + ) 2.185 + WHERE "id" = "issue_id_p"; 2.186 + FOR "initiative_id_v" IN 2.187 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 2.188 + LOOP 2.189 + UPDATE "initiative" SET 2.190 + "supporter_count" = ( 2.191 + SELECT coalesce(sum("di"."weight"), 0) 2.192 + FROM "direct_interest_snapshot" AS "di" 2.193 + JOIN "direct_supporter_snapshot" AS "ds" 2.194 + ON "di"."member_id" = "ds"."member_id" 2.195 + WHERE "di"."issue_id" = "issue_id_p" 2.196 + AND "di"."event" = 'periodic' 2.197 + AND "ds"."initiative_id" = "initiative_id_v" 2.198 + AND "ds"."event" = 'periodic' 2.199 + ), 2.200 + "informed_supporter_count" = ( 2.201 + SELECT coalesce(sum("di"."weight"), 0) 2.202 + FROM "direct_interest_snapshot" AS "di" 2.203 + JOIN "direct_supporter_snapshot" AS "ds" 2.204 + ON "di"."member_id" = "ds"."member_id" 2.205 + WHERE "di"."issue_id" = "issue_id_p" 2.206 + AND "di"."event" = 'periodic' 2.207 + AND "ds"."initiative_id" = "initiative_id_v" 2.208 + AND "ds"."event" = 'periodic' 2.209 + AND "ds"."informed" 2.210 + ), 2.211 + "satisfied_supporter_count" = ( 2.212 + SELECT coalesce(sum("di"."weight"), 0) 2.213 + FROM "direct_interest_snapshot" AS "di" 2.214 + JOIN "direct_supporter_snapshot" AS "ds" 2.215 + ON "di"."member_id" = "ds"."member_id" 2.216 + WHERE "di"."issue_id" = "issue_id_p" 2.217 + AND "di"."event" = 'periodic' 2.218 + AND "ds"."initiative_id" = "initiative_id_v" 2.219 + AND "ds"."event" = 'periodic' 2.220 + AND "ds"."satisfied" 2.221 + ), 2.222 + "satisfied_informed_supporter_count" = ( 2.223 + SELECT coalesce(sum("di"."weight"), 0) 2.224 + FROM "direct_interest_snapshot" AS "di" 2.225 + JOIN "direct_supporter_snapshot" AS "ds" 2.226 + ON "di"."member_id" = "ds"."member_id" 2.227 + WHERE "di"."issue_id" = "issue_id_p" 2.228 + AND "di"."event" = 'periodic' 2.229 + AND "ds"."initiative_id" = "initiative_id_v" 2.230 + AND "ds"."event" = 'periodic' 2.231 + AND "ds"."informed" 2.232 + AND "ds"."satisfied" 2.233 + ) 2.234 + WHERE "id" = "initiative_id_v"; 2.235 + FOR "suggestion_id_v" IN 2.236 + SELECT "id" FROM "suggestion" 2.237 + WHERE "initiative_id" = "initiative_id_v" 2.238 + LOOP 2.239 + UPDATE "suggestion" SET 2.240 + "minus2_unfulfilled_count" = ( 2.241 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.242 + FROM "issue" CROSS JOIN "opinion" 2.243 + JOIN "direct_interest_snapshot" AS "snapshot" 2.244 + ON "snapshot"."issue_id" = "issue"."id" 2.245 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.246 + AND "snapshot"."member_id" = "opinion"."member_id" 2.247 + WHERE "issue"."id" = "issue_id_p" 2.248 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.249 + AND "opinion"."degree" = -2 2.250 + AND "opinion"."fulfilled" = FALSE 2.251 + ), 2.252 + "minus2_fulfilled_count" = ( 2.253 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.254 + FROM "issue" CROSS JOIN "opinion" 2.255 + JOIN "direct_interest_snapshot" AS "snapshot" 2.256 + ON "snapshot"."issue_id" = "issue"."id" 2.257 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.258 + AND "snapshot"."member_id" = "opinion"."member_id" 2.259 + WHERE "issue"."id" = "issue_id_p" 2.260 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.261 + AND "opinion"."degree" = -2 2.262 + AND "opinion"."fulfilled" = TRUE 2.263 + ), 2.264 + "minus1_unfulfilled_count" = ( 2.265 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.266 + FROM "issue" CROSS JOIN "opinion" 2.267 + JOIN "direct_interest_snapshot" AS "snapshot" 2.268 + ON "snapshot"."issue_id" = "issue"."id" 2.269 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.270 + AND "snapshot"."member_id" = "opinion"."member_id" 2.271 + WHERE "issue"."id" = "issue_id_p" 2.272 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.273 + AND "opinion"."degree" = -1 2.274 + AND "opinion"."fulfilled" = FALSE 2.275 + ), 2.276 + "minus1_fulfilled_count" = ( 2.277 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.278 + FROM "issue" CROSS JOIN "opinion" 2.279 + JOIN "direct_interest_snapshot" AS "snapshot" 2.280 + ON "snapshot"."issue_id" = "issue"."id" 2.281 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.282 + AND "snapshot"."member_id" = "opinion"."member_id" 2.283 + WHERE "issue"."id" = "issue_id_p" 2.284 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.285 + AND "opinion"."degree" = -1 2.286 + AND "opinion"."fulfilled" = TRUE 2.287 + ), 2.288 + "plus1_unfulfilled_count" = ( 2.289 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.290 + FROM "issue" CROSS JOIN "opinion" 2.291 + JOIN "direct_interest_snapshot" AS "snapshot" 2.292 + ON "snapshot"."issue_id" = "issue"."id" 2.293 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.294 + AND "snapshot"."member_id" = "opinion"."member_id" 2.295 + WHERE "issue"."id" = "issue_id_p" 2.296 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.297 + AND "opinion"."degree" = 1 2.298 + AND "opinion"."fulfilled" = FALSE 2.299 + ), 2.300 + "plus1_fulfilled_count" = ( 2.301 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.302 + FROM "issue" CROSS JOIN "opinion" 2.303 + JOIN "direct_interest_snapshot" AS "snapshot" 2.304 + ON "snapshot"."issue_id" = "issue"."id" 2.305 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.306 + AND "snapshot"."member_id" = "opinion"."member_id" 2.307 + WHERE "issue"."id" = "issue_id_p" 2.308 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.309 + AND "opinion"."degree" = 1 2.310 + AND "opinion"."fulfilled" = TRUE 2.311 + ), 2.312 + "plus2_unfulfilled_count" = ( 2.313 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.314 + FROM "issue" CROSS JOIN "opinion" 2.315 + JOIN "direct_interest_snapshot" AS "snapshot" 2.316 + ON "snapshot"."issue_id" = "issue"."id" 2.317 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.318 + AND "snapshot"."member_id" = "opinion"."member_id" 2.319 + WHERE "issue"."id" = "issue_id_p" 2.320 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.321 + AND "opinion"."degree" = 2 2.322 + AND "opinion"."fulfilled" = FALSE 2.323 + ), 2.324 + "plus2_fulfilled_count" = ( 2.325 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.326 + FROM "issue" CROSS JOIN "opinion" 2.327 + JOIN "direct_interest_snapshot" AS "snapshot" 2.328 + ON "snapshot"."issue_id" = "issue"."id" 2.329 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.330 + AND "snapshot"."member_id" = "opinion"."member_id" 2.331 + WHERE "issue"."id" = "issue_id_p" 2.332 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.333 + AND "opinion"."degree" = 2 2.334 + AND "opinion"."fulfilled" = TRUE 2.335 + ) 2.336 + WHERE "suggestion"."id" = "suggestion_id_v"; 2.337 + END LOOP; 2.338 + END LOOP; 2.339 + RETURN; 2.340 + END; 2.341 + $$; 2.342 + 2.343 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.344 + RETURNS VOID 2.345 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.346 + DECLARE 2.347 + "issue_row" "issue"%ROWTYPE; 2.348 + "member_id_v" "member"."id"%TYPE; 2.349 + BEGIN 2.350 + PERFORM "lock_issue"("issue_id_p"); 2.351 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.352 + DELETE FROM "delegating_voter" 2.353 + WHERE "issue_id" = "issue_id_p"; 2.354 + DELETE FROM "direct_voter" 2.355 + WHERE "issue_id" = "issue_id_p" 2.356 + AND "autoreject" = TRUE; 2.357 + DELETE FROM "direct_voter" USING "member" 2.358 + WHERE "direct_voter"."member_id" = "member"."id" 2.359 + AND "direct_voter"."issue_id" = "issue_id_p" 2.360 + AND "member"."active" = FALSE; 2.361 + UPDATE "direct_voter" SET "weight" = 1 2.362 + WHERE "issue_id" = "issue_id_p"; 2.363 + PERFORM "add_vote_delegations"("issue_id_p"); 2.364 + FOR "member_id_v" IN 2.365 + SELECT "interest"."member_id" 2.366 + FROM "interest" 2.367 + LEFT JOIN "direct_voter" 2.368 + ON "interest"."member_id" = "direct_voter"."member_id" 2.369 + AND "interest"."issue_id" = "direct_voter"."issue_id" 2.370 + LEFT JOIN "delegating_voter" 2.371 + ON "interest"."member_id" = "delegating_voter"."member_id" 2.372 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 2.373 + WHERE "interest"."issue_id" = "issue_id_p" 2.374 + AND "interest"."autoreject" = TRUE 2.375 + AND "direct_voter"."member_id" ISNULL 2.376 + AND "delegating_voter"."member_id" ISNULL 2.377 + UNION SELECT "membership"."member_id" 2.378 + FROM "membership" 2.379 + LEFT JOIN "interest" 2.380 + ON "membership"."member_id" = "interest"."member_id" 2.381 + AND "interest"."issue_id" = "issue_id_p" 2.382 + LEFT JOIN "direct_voter" 2.383 + ON "membership"."member_id" = "direct_voter"."member_id" 2.384 + AND "direct_voter"."issue_id" = "issue_id_p" 2.385 + LEFT JOIN "delegating_voter" 2.386 + ON "membership"."member_id" = "delegating_voter"."member_id" 2.387 + AND "delegating_voter"."issue_id" = "issue_id_p" 2.388 + WHERE "membership"."area_id" = "issue_row"."area_id" 2.389 + AND "membership"."autoreject" = TRUE 2.390 + AND "interest"."autoreject" ISNULL 2.391 + AND "direct_voter"."member_id" ISNULL 2.392 + AND "delegating_voter"."member_id" ISNULL 2.393 + LOOP 2.394 + INSERT INTO "direct_voter" 2.395 + ("member_id", "issue_id", "weight", "autoreject") VALUES 2.396 + ("member_id_v", "issue_id_p", 1, TRUE); 2.397 + INSERT INTO "vote" ( 2.398 + "member_id", 2.399 + "issue_id", 2.400 + "initiative_id", 2.401 + "grade" 2.402 + ) SELECT 2.403 + "member_id_v" AS "member_id", 2.404 + "issue_id_p" AS "issue_id", 2.405 + "id" AS "initiative_id", 2.406 + -1 AS "grade" 2.407 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.408 + END LOOP; 2.409 + PERFORM "add_vote_delegations"("issue_id_p"); 2.410 + UPDATE "issue" SET 2.411 + "closed" = now(), 2.412 + "voter_count" = ( 2.413 + SELECT coalesce(sum("weight"), 0) 2.414 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.415 + ) 2.416 + WHERE "id" = "issue_id_p"; 2.417 + UPDATE "initiative" SET 2.418 + "positive_votes" = "vote_counts"."positive_votes", 2.419 + "negative_votes" = "vote_counts"."negative_votes", 2.420 + "agreed" = CASE WHEN "majority_strict" THEN 2.421 + "vote_counts"."positive_votes" * "majority_den" > 2.422 + "majority_num" * 2.423 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.424 + ELSE 2.425 + "vote_counts"."positive_votes" * "majority_den" >= 2.426 + "majority_num" * 2.427 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.428 + END 2.429 + FROM 2.430 + ( SELECT 2.431 + "initiative"."id" AS "initiative_id", 2.432 + coalesce( 2.433 + sum( 2.434 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 2.435 + ), 2.436 + 0 2.437 + ) AS "positive_votes", 2.438 + coalesce( 2.439 + sum( 2.440 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 2.441 + ), 2.442 + 0 2.443 + ) AS "negative_votes" 2.444 + FROM "initiative" 2.445 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 2.446 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 2.447 + LEFT JOIN "direct_voter" 2.448 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 2.449 + LEFT JOIN "vote" 2.450 + ON "vote"."initiative_id" = "initiative"."id" 2.451 + AND "vote"."member_id" = "direct_voter"."member_id" 2.452 + WHERE "initiative"."issue_id" = "issue_id_p" 2.453 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 2.454 + GROUP BY "initiative"."id" 2.455 + ) AS "vote_counts", 2.456 + "issue", 2.457 + "policy" 2.458 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 2.459 + AND "issue"."id" = "initiative"."issue_id" 2.460 + AND "policy"."id" = "issue"."policy_id"; 2.461 + -- NOTE: "closed" column of issue must be set at this point 2.462 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.463 + INSERT INTO "battle" ( 2.464 + "issue_id", 2.465 + "winning_initiative_id", "losing_initiative_id", 2.466 + "count" 2.467 + ) SELECT 2.468 + "issue_id", 2.469 + "winning_initiative_id", "losing_initiative_id", 2.470 + "count" 2.471 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.472 + END; 2.473 + $$; 2.474 + 2.475 +CREATE OR REPLACE FUNCTION "check_issue" 2.476 + ( "issue_id_p" "issue"."id"%TYPE ) 2.477 + RETURNS VOID 2.478 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.479 + DECLARE 2.480 + "issue_row" "issue"%ROWTYPE; 2.481 + "policy_row" "policy"%ROWTYPE; 2.482 + "voting_requested_v" BOOLEAN; 2.483 + BEGIN 2.484 + PERFORM "lock_issue"("issue_id_p"); 2.485 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.486 + -- only process open issues: 2.487 + IF "issue_row"."closed" ISNULL THEN 2.488 + SELECT * INTO "policy_row" FROM "policy" 2.489 + WHERE "id" = "issue_row"."policy_id"; 2.490 + -- create a snapshot, unless issue is already fully frozen: 2.491 + IF "issue_row"."fully_frozen" ISNULL THEN 2.492 + PERFORM "create_snapshot"("issue_id_p"); 2.493 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.494 + END IF; 2.495 + -- eventually close or accept issues, which have not been accepted: 2.496 + IF "issue_row"."accepted" ISNULL THEN 2.497 + IF EXISTS ( 2.498 + SELECT NULL FROM "initiative" 2.499 + WHERE "issue_id" = "issue_id_p" 2.500 + AND "supporter_count" > 0 2.501 + AND "supporter_count" * "policy_row"."issue_quorum_den" 2.502 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.503 + ) THEN 2.504 + -- accept issues, if supporter count is high enough 2.505 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.506 + "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later 2.507 + UPDATE "issue" SET "accepted" = "issue_row"."accepted" 2.508 + WHERE "id" = "issue_row"."id"; 2.509 + ELSIF 2.510 + now() >= "issue_row"."created" + "issue_row"."admission_time" 2.511 + THEN 2.512 + -- close issues, if admission time has expired 2.513 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.514 + UPDATE "issue" SET "closed" = now() 2.515 + WHERE "id" = "issue_row"."id"; 2.516 + END IF; 2.517 + END IF; 2.518 + -- eventually half freeze issues: 2.519 + IF 2.520 + -- NOTE: issue can't be closed at this point, if it has been accepted 2.521 + "issue_row"."accepted" NOTNULL AND 2.522 + "issue_row"."half_frozen" ISNULL 2.523 + THEN 2.524 + SELECT 2.525 + CASE 2.526 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 2.527 + TRUE 2.528 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 2.529 + FALSE 2.530 + ELSE NULL 2.531 + END 2.532 + INTO "voting_requested_v" 2.533 + FROM "issue" WHERE "id" = "issue_id_p"; 2.534 + IF 2.535 + "voting_requested_v" OR ( 2.536 + "voting_requested_v" ISNULL AND 2.537 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.538 + ) 2.539 + THEN 2.540 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.541 + "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later 2.542 + UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen" 2.543 + WHERE "id" = "issue_row"."id"; 2.544 + END IF; 2.545 + END IF; 2.546 + -- close issues after some time, if all initiatives have been revoked: 2.547 + IF 2.548 + "issue_row"."closed" ISNULL AND 2.549 + NOT EXISTS ( 2.550 + -- all initiatives are revoked 2.551 + SELECT NULL FROM "initiative" 2.552 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.553 + ) AND ( 2.554 + NOT EXISTS ( 2.555 + -- and no initiatives have been revoked lately 2.556 + SELECT NULL FROM "initiative" 2.557 + WHERE "issue_id" = "issue_id_p" 2.558 + AND now() < "revoked" + "issue_row"."verification_time" 2.559 + ) OR ( 2.560 + -- or verification time has elapsed 2.561 + "issue_row"."half_frozen" NOTNULL AND 2.562 + "issue_row"."fully_frozen" ISNULL AND 2.563 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.564 + ) 2.565 + ) 2.566 + THEN 2.567 + "issue_row"."closed" = now(); -- NOTE: "issue_row" used later 2.568 + UPDATE "issue" SET "closed" = "issue_row"."closed" 2.569 + WHERE "id" = "issue_row"."id"; 2.570 + END IF; 2.571 + -- fully freeze issue after verification time: 2.572 + IF 2.573 + "issue_row"."half_frozen" NOTNULL AND 2.574 + "issue_row"."fully_frozen" ISNULL AND 2.575 + "issue_row"."closed" ISNULL AND 2.576 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.577 + THEN 2.578 + PERFORM "freeze_after_snapshot"("issue_id_p"); 2.579 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.580 + END IF; 2.581 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.582 + -- close issue by calling close_voting(...) after voting time: 2.583 + IF 2.584 + "issue_row"."closed" ISNULL AND 2.585 + "issue_row"."fully_frozen" NOTNULL AND 2.586 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.587 + THEN 2.588 + PERFORM "close_voting"("issue_id_p"); 2.589 + END IF; 2.590 + END IF; 2.591 + RETURN; 2.592 + END; 2.593 + $$; 2.594 + 2.595 + 2.596 +DROP FUNCTION "global_lock"(); 2.597 + 2.598 + 2.599 +COMMIT;