liquid_feedback_core
annotate update/core-update.v1.0.0-v1.0.1.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 | 74c985baf082 |
| children |
| rev | line source |
|---|---|
| jbe@48 | 1 BEGIN; |
| jbe@48 | 2 |
| jbe@48 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
| jbe@48 | 4 SELECT * FROM (VALUES ('1.0.1', 1, 0, 1)) |
| jbe@48 | 5 AS "subquery"("string", "major", "minor", "revision"); |
| jbe@48 | 6 |
| jbe@48 | 7 CREATE OR REPLACE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) |
| jbe@48 | 8 RETURNS VOID |
| jbe@48 | 9 LANGUAGE 'plpgsql' VOLATILE AS $$ |
| jbe@48 | 10 BEGIN |
| jbe@48 | 11 UPDATE "member" SET |
| jbe@48 | 12 "login" = NULL, |
| jbe@48 | 13 "password" = NULL, |
| jbe@48 | 14 "notify_email" = NULL, |
| jbe@48 | 15 "notify_email_unconfirmed" = NULL, |
| jbe@48 | 16 "notify_email_secret" = NULL, |
| jbe@48 | 17 "notify_email_secret_expiry" = NULL, |
| jbe@48 | 18 "password_reset_secret" = NULL, |
| jbe@48 | 19 "password_reset_secret_expiry" = NULL, |
| jbe@48 | 20 "organizational_unit" = NULL, |
| jbe@48 | 21 "internal_posts" = NULL, |
| jbe@48 | 22 "realname" = NULL, |
| jbe@48 | 23 "birthday" = NULL, |
| jbe@48 | 24 "address" = NULL, |
| jbe@48 | 25 "email" = NULL, |
| jbe@48 | 26 "xmpp_address" = NULL, |
| jbe@48 | 27 "website" = NULL, |
| jbe@48 | 28 "phone" = NULL, |
| jbe@48 | 29 "mobile_phone" = NULL, |
| jbe@48 | 30 "profession" = NULL, |
| jbe@48 | 31 "external_memberships" = NULL, |
| jbe@48 | 32 "external_posts" = NULL, |
| jbe@48 | 33 "statement" = NULL |
| jbe@48 | 34 WHERE "id" = "member_id_p"; |
| jbe@48 | 35 -- "text_search_data" is updated by triggers |
| jbe@48 | 36 UPDATE "member_history" SET "login" = NULL |
| jbe@48 | 37 WHERE "member_id" = "member_id_p"; |
| jbe@48 | 38 DELETE FROM "setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 39 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 40 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 41 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 42 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 43 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; |
| jbe@48 | 44 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 45 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 46 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 47 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; |
| jbe@48 | 48 RETURN; |
| jbe@48 | 49 END; |
| jbe@48 | 50 $$; |
| jbe@48 | 51 |
| jbe@48 | 52 COMMIT; |