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