liquid_feedback_core

annotate update/core-update.v2.0.5-v2.0.6.sql @ 295:69d6fba0f84c

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

Impressum / About Us