liquid_feedback_core
annotate update/core-update.v1.2.8-v1.2.9.sql @ 295:69d6fba0f84c
Use EXCLUSIVE MODE table locks in function "lock_issue"("issue"."id")
Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.
Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.
| author | jbe |
|---|---|
| date | Thu Sep 13 17:02:22 2012 +0200 (2012-09-13) |
| parents | e588fdf1676e |
| children |
| rev | line source |
|---|---|
| jbe@87 | 1 BEGIN; |
| jbe@87 | 2 |
| jbe@87 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
| jbe@87 | 4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) |
| jbe@87 | 5 AS "subquery"("string", "major", "minor", "revision"); |
| jbe@87 | 6 |
| jbe@87 | 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
| jbe@87 | 8 RETURNS VOID |
| jbe@87 | 9 LANGUAGE 'plpgsql' VOLATILE AS $$ |
| jbe@87 | 10 DECLARE |
| jbe@87 | 11 "issue_row" "issue"%ROWTYPE; |
| jbe@87 | 12 "member_id_v" "member"."id"%TYPE; |
| jbe@87 | 13 BEGIN |
| jbe@87 | 14 PERFORM "lock_issue"("issue_id_p"); |
| jbe@87 | 15 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
| jbe@87 | 16 DELETE FROM "delegating_voter" |
| jbe@87 | 17 WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 18 DELETE FROM "direct_voter" |
| jbe@87 | 19 WHERE "issue_id" = "issue_id_p" |
| jbe@87 | 20 AND "autoreject" = TRUE; |
| jbe@87 | 21 DELETE FROM "direct_voter" USING "member" |
| jbe@87 | 22 WHERE "direct_voter"."member_id" = "member"."id" |
| jbe@87 | 23 AND "direct_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 24 AND "member"."active" = FALSE; |
| jbe@87 | 25 UPDATE "direct_voter" SET "weight" = 1 |
| jbe@87 | 26 WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 27 PERFORM "add_vote_delegations"("issue_id_p"); |
| jbe@87 | 28 FOR "member_id_v" IN |
| jbe@87 | 29 SELECT "interest"."member_id" |
| jbe@87 | 30 FROM "interest" |
| jbe@87 | 31 JOIN "member" |
| jbe@87 | 32 ON "interest"."member_id" = "member"."id" |
| jbe@87 | 33 LEFT JOIN "direct_voter" |
| jbe@87 | 34 ON "interest"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 35 AND "interest"."issue_id" = "direct_voter"."issue_id" |
| jbe@87 | 36 LEFT JOIN "delegating_voter" |
| jbe@87 | 37 ON "interest"."member_id" = "delegating_voter"."member_id" |
| jbe@87 | 38 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
| jbe@87 | 39 WHERE "interest"."issue_id" = "issue_id_p" |
| jbe@87 | 40 AND "interest"."autoreject" = TRUE |
| jbe@87 | 41 AND "member"."active" |
| jbe@87 | 42 AND "direct_voter"."member_id" ISNULL |
| jbe@87 | 43 AND "delegating_voter"."member_id" ISNULL |
| jbe@87 | 44 UNION SELECT "membership"."member_id" |
| jbe@87 | 45 FROM "membership" |
| jbe@87 | 46 JOIN "member" |
| jbe@87 | 47 ON "membership"."member_id" = "member"."id" |
| jbe@87 | 48 LEFT JOIN "interest" |
| jbe@87 | 49 ON "membership"."member_id" = "interest"."member_id" |
| jbe@87 | 50 AND "interest"."issue_id" = "issue_id_p" |
| jbe@87 | 51 LEFT JOIN "direct_voter" |
| jbe@87 | 52 ON "membership"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 53 AND "direct_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 54 LEFT JOIN "delegating_voter" |
| jbe@87 | 55 ON "membership"."member_id" = "delegating_voter"."member_id" |
| jbe@87 | 56 AND "delegating_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 57 WHERE "membership"."area_id" = "issue_row"."area_id" |
| jbe@87 | 58 AND "membership"."autoreject" = TRUE |
| jbe@87 | 59 AND "member"."active" |
| jbe@87 | 60 AND "interest"."autoreject" ISNULL |
| jbe@87 | 61 AND "direct_voter"."member_id" ISNULL |
| jbe@87 | 62 AND "delegating_voter"."member_id" ISNULL |
| jbe@87 | 63 LOOP |
| jbe@87 | 64 INSERT INTO "direct_voter" |
| jbe@87 | 65 ("member_id", "issue_id", "weight", "autoreject") VALUES |
| jbe@87 | 66 ("member_id_v", "issue_id_p", 1, TRUE); |
| jbe@87 | 67 INSERT INTO "vote" ( |
| jbe@87 | 68 "member_id", |
| jbe@87 | 69 "issue_id", |
| jbe@87 | 70 "initiative_id", |
| jbe@87 | 71 "grade" |
| jbe@87 | 72 ) SELECT |
| jbe@87 | 73 "member_id_v" AS "member_id", |
| jbe@87 | 74 "issue_id_p" AS "issue_id", |
| jbe@87 | 75 "id" AS "initiative_id", |
| jbe@87 | 76 -1 AS "grade" |
| jbe@87 | 77 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 78 END LOOP; |
| jbe@87 | 79 PERFORM "add_vote_delegations"("issue_id_p"); |
| jbe@87 | 80 UPDATE "issue" SET |
| jbe@87 | 81 "closed" = now(), |
| jbe@87 | 82 "voter_count" = ( |
| jbe@87 | 83 SELECT coalesce(sum("weight"), 0) |
| jbe@87 | 84 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
| jbe@87 | 85 ) |
| jbe@87 | 86 WHERE "id" = "issue_id_p"; |
| jbe@87 | 87 UPDATE "initiative" SET |
| jbe@87 | 88 "positive_votes" = "vote_counts"."positive_votes", |
| jbe@87 | 89 "negative_votes" = "vote_counts"."negative_votes", |
| jbe@87 | 90 "agreed" = CASE WHEN "majority_strict" THEN |
| jbe@87 | 91 "vote_counts"."positive_votes" * "majority_den" > |
| jbe@87 | 92 "majority_num" * |
| jbe@87 | 93 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
| jbe@87 | 94 ELSE |
| jbe@87 | 95 "vote_counts"."positive_votes" * "majority_den" >= |
| jbe@87 | 96 "majority_num" * |
| jbe@87 | 97 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
| jbe@87 | 98 END |
| jbe@87 | 99 FROM |
| jbe@87 | 100 ( SELECT |
| jbe@87 | 101 "initiative"."id" AS "initiative_id", |
| jbe@87 | 102 coalesce( |
| jbe@87 | 103 sum( |
| jbe@87 | 104 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
| jbe@87 | 105 ), |
| jbe@87 | 106 0 |
| jbe@87 | 107 ) AS "positive_votes", |
| jbe@87 | 108 coalesce( |
| jbe@87 | 109 sum( |
| jbe@87 | 110 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
| jbe@87 | 111 ), |
| jbe@87 | 112 0 |
| jbe@87 | 113 ) AS "negative_votes" |
| jbe@87 | 114 FROM "initiative" |
| jbe@87 | 115 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
| jbe@87 | 116 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
| jbe@87 | 117 LEFT JOIN "direct_voter" |
| jbe@87 | 118 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
| jbe@87 | 119 LEFT JOIN "vote" |
| jbe@87 | 120 ON "vote"."initiative_id" = "initiative"."id" |
| jbe@87 | 121 AND "vote"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 122 WHERE "initiative"."issue_id" = "issue_id_p" |
| jbe@87 | 123 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
| jbe@87 | 124 GROUP BY "initiative"."id" |
| jbe@87 | 125 ) AS "vote_counts", |
| jbe@87 | 126 "issue", |
| jbe@87 | 127 "policy" |
| jbe@87 | 128 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
| jbe@87 | 129 AND "issue"."id" = "initiative"."issue_id" |
| jbe@87 | 130 AND "policy"."id" = "issue"."policy_id"; |
| jbe@87 | 131 -- NOTE: "closed" column of issue must be set at this point |
| jbe@87 | 132 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 133 INSERT INTO "battle" ( |
| jbe@87 | 134 "issue_id", |
| jbe@87 | 135 "winning_initiative_id", "losing_initiative_id", |
| jbe@87 | 136 "count" |
| jbe@87 | 137 ) SELECT |
| jbe@87 | 138 "issue_id", |
| jbe@87 | 139 "winning_initiative_id", "losing_initiative_id", |
| jbe@87 | 140 "count" |
| jbe@87 | 141 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 142 END; |
| jbe@87 | 143 $$; |
| jbe@87 | 144 |
| jbe@87 | 145 COMMIT; |