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