liquid_feedback_core
view update/core-update.v2.0.6-v2.0.7.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 | 4346d48b1780 |
| children |
line source
1 BEGIN;
3 -- NOTE: "lf_update" needs to be recompiled to complete this update!
5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
6 SELECT * FROM (VALUES ('2.0.7', 2, 0, 7))
7 AS "subquery"("string", "major", "minor", "revision");
9 CREATE VIEW "expired_session" AS
10 SELECT * FROM "session" WHERE now() > "expiry";
12 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
13 DELETE FROM "session" WHERE "ident" = OLD."ident";
15 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
16 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
18 CREATE OR REPLACE FUNCTION "check_everything"()
19 RETURNS VOID
20 LANGUAGE 'plpgsql' VOLATILE AS $$
21 DECLARE
22 "issue_id_v" "issue"."id"%TYPE;
23 BEGIN
24 DELETE FROM "expired_session";
25 PERFORM "check_activity"();
26 PERFORM "calculate_member_counts"();
27 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
28 PERFORM "check_issue"("issue_id_v");
29 END LOOP;
30 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
31 PERFORM "calculate_ranks"("issue_id_v");
32 END LOOP;
33 RETURN;
34 END;
35 $$;
37 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
38 RETURNS VOID
39 LANGUAGE 'plpgsql' VOLATILE AS $$
40 BEGIN
41 UPDATE "member" SET
42 "last_login" = NULL,
43 "login" = NULL,
44 "password" = NULL,
45 "locked" = TRUE,
46 "active" = FALSE,
47 "notify_email" = NULL,
48 "notify_email_unconfirmed" = NULL,
49 "notify_email_secret" = NULL,
50 "notify_email_secret_expiry" = NULL,
51 "notify_email_lock_expiry" = NULL,
52 "password_reset_secret" = NULL,
53 "password_reset_secret_expiry" = NULL,
54 "organizational_unit" = NULL,
55 "internal_posts" = NULL,
56 "realname" = NULL,
57 "birthday" = NULL,
58 "address" = NULL,
59 "email" = NULL,
60 "xmpp_address" = NULL,
61 "website" = NULL,
62 "phone" = NULL,
63 "mobile_phone" = NULL,
64 "profession" = NULL,
65 "external_memberships" = NULL,
66 "external_posts" = NULL,
67 "statement" = NULL
68 WHERE "id" = "member_id_p";
69 -- "text_search_data" is updated by triggers
70 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
71 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
72 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
73 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
74 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
75 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
76 DELETE FROM "session" WHERE "member_id" = "member_id_p";
77 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
78 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
79 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
80 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
81 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
82 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
83 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
84 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
85 DELETE FROM "direct_voter" USING "issue"
86 WHERE "direct_voter"."issue_id" = "issue"."id"
87 AND "issue"."closed" ISNULL
88 AND "member_id" = "member_id_p";
89 RETURN;
90 END;
91 $$;
93 CREATE OR REPLACE FUNCTION "delete_private_data"()
94 RETURNS VOID
95 LANGUAGE 'plpgsql' VOLATILE AS $$
96 BEGIN
97 DELETE FROM "member" WHERE "activated" ISNULL;
98 UPDATE "member" SET
99 "invite_code" = NULL,
100 "invite_code_expiry" = NULL,
101 "admin_comment" = NULL,
102 "last_login" = NULL,
103 "login" = NULL,
104 "password" = NULL,
105 "notify_email" = NULL,
106 "notify_email_unconfirmed" = NULL,
107 "notify_email_secret" = NULL,
108 "notify_email_secret_expiry" = NULL,
109 "notify_email_lock_expiry" = NULL,
110 "password_reset_secret" = NULL,
111 "password_reset_secret_expiry" = NULL,
112 "organizational_unit" = NULL,
113 "internal_posts" = NULL,
114 "realname" = NULL,
115 "birthday" = NULL,
116 "address" = NULL,
117 "email" = NULL,
118 "xmpp_address" = NULL,
119 "website" = NULL,
120 "phone" = NULL,
121 "mobile_phone" = NULL,
122 "profession" = NULL,
123 "external_memberships" = NULL,
124 "external_posts" = NULL,
125 "statement" = NULL;
126 -- "text_search_data" is updated by triggers
127 DELETE FROM "setting";
128 DELETE FROM "setting_map";
129 DELETE FROM "member_relation_setting";
130 DELETE FROM "member_image";
131 DELETE FROM "contact";
132 DELETE FROM "ignored_member";
133 DELETE FROM "session";
134 DELETE FROM "area_setting";
135 DELETE FROM "issue_setting";
136 DELETE FROM "ignored_initiative";
137 DELETE FROM "initiative_setting";
138 DELETE FROM "suggestion_setting";
139 DELETE FROM "non_voter";
140 DELETE FROM "direct_voter" USING "issue"
141 WHERE "direct_voter"."issue_id" = "issue"."id"
142 AND "issue"."closed" ISNULL;
143 RETURN;
144 END;
145 $$;
147 COMMIT;