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