liquid_feedback_core
view update/core-update.v2.0.5-v2.0.6.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 | f022016f6748 |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.0.6', 2, 0, 6))
5 AS "subquery"("string", "major", "minor", "revision");
7 -- add column "invite_code_expiry" to table "member":
8 ALTER TABLE "member" ADD COLUMN "invite_code_expiry" TIMESTAMPTZ;
9 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
11 -- write member history only for activated accounts:
12 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
13 RETURNS TRIGGER
14 LANGUAGE 'plpgsql' VOLATILE AS $$
15 BEGIN
16 IF
17 ( NEW."active" != OLD."active" OR
18 NEW."name" != OLD."name" ) AND
19 OLD."activated" NOTNULL
20 THEN
21 INSERT INTO "member_history"
22 ("member_id", "active", "name")
23 VALUES (NEW."id", OLD."active", OLD."name");
24 END IF;
25 RETURN NULL;
26 END;
27 $$;
29 -- set "draft_id" in "event" table on event 'initiative_revoked':
30 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
31 RETURNS TRIGGER
32 LANGUAGE 'plpgsql' VOLATILE AS $$
33 DECLARE
34 "issue_row" "issue"%ROWTYPE;
35 "draft_id_v" "draft"."id"%TYPE;
36 BEGIN
37 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
38 SELECT * INTO "issue_row" FROM "issue"
39 WHERE "id" = NEW."issue_id";
40 SELECT "id" INTO "draft_id_v" FROM "current_draft"
41 WHERE "initiative_id" = NEW."id";
42 INSERT INTO "event" (
43 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
44 ) VALUES (
45 'initiative_revoked',
46 NEW."revoked_by_member_id",
47 NEW."issue_id",
48 "issue_row"."state",
49 NEW."id",
50 "draft_id_v");
51 END IF;
52 RETURN NULL;
53 END;
54 $$;
56 -- delete column "invite_code_expiry" in function "delete_private_data":
57 CREATE OR REPLACE FUNCTION "delete_private_data"()
58 RETURNS VOID
59 LANGUAGE 'plpgsql' VOLATILE AS $$
60 BEGIN
61 DELETE FROM "member" WHERE "activated" ISNULL;
62 UPDATE "member" SET
63 "invite_code" = NULL,
64 "invite_code_expiry" = NULL,
65 "admin_comment" = NULL,
66 "last_login" = NULL,
67 "login" = NULL,
68 "password" = NULL,
69 "notify_email" = NULL,
70 "notify_email_unconfirmed" = NULL,
71 "notify_email_secret" = NULL,
72 "notify_email_secret_expiry" = NULL,
73 "notify_email_lock_expiry" = NULL,
74 "password_reset_secret" = NULL,
75 "password_reset_secret_expiry" = NULL,
76 "organizational_unit" = NULL,
77 "internal_posts" = NULL,
78 "realname" = NULL,
79 "birthday" = NULL,
80 "address" = NULL,
81 "email" = NULL,
82 "xmpp_address" = NULL,
83 "website" = NULL,
84 "phone" = NULL,
85 "mobile_phone" = NULL,
86 "profession" = NULL,
87 "external_memberships" = NULL,
88 "external_posts" = NULL,
89 "statement" = NULL;
90 -- "text_search_data" is updated by triggers
91 DELETE FROM "setting";
92 DELETE FROM "setting_map";
93 DELETE FROM "member_relation_setting";
94 DELETE FROM "member_image";
95 DELETE FROM "contact";
96 DELETE FROM "ignored_member";
97 DELETE FROM "area_setting";
98 DELETE FROM "issue_setting";
99 DELETE FROM "ignored_initiative";
100 DELETE FROM "initiative_setting";
101 DELETE FROM "suggestion_setting";
102 DELETE FROM "non_voter";
103 DELETE FROM "direct_voter" USING "issue"
104 WHERE "direct_voter"."issue_id" = "issue"."id"
105 AND "issue"."closed" ISNULL;
106 RETURN;
107 END;
108 $$;
110 COMMIT;