liquid_feedback_core

annotate update/core-update.v1.2.0-v1.2.1.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 fdd1729d7e2a
children
rev   line source
jbe@59 1 BEGIN;
jbe@59 2
jbe@59 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@59 4 SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
jbe@59 5 AS "subquery"("string", "major", "minor", "revision");
jbe@59 6
jbe@59 7 ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ;
jbe@59 8 ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction"
jbe@59 9 CHECK (
jbe@59 10 "cleaned" ISNULL OR (
jbe@59 11 "closed" NOTNULL AND (
jbe@59 12 "fully_frozen" ISNULL OR "ranks_available"
jbe@59 13 )
jbe@59 14 )
jbe@59 15 );
jbe@59 16
jbe@59 17 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
jbe@59 18
jbe@59 19 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@59 20 RETURNS VOID
jbe@59 21 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@59 22 DECLARE
jbe@59 23 "issue_row" "issue"%ROWTYPE;
jbe@59 24 BEGIN
jbe@59 25 SELECT * INTO "issue_row"
jbe@59 26 FROM "issue" WHERE "id" = "issue_id_p"
jbe@59 27 FOR UPDATE;
jbe@59 28 IF "issue_row"."cleaned" ISNULL THEN
jbe@59 29 UPDATE "issue" SET
jbe@59 30 "closed" = NULL,
jbe@59 31 "ranks_available" = FALSE
jbe@59 32 WHERE "id" = "issue_id_p";
jbe@59 33 DELETE FROM "delegating_voter"
jbe@59 34 WHERE "issue_id" = "issue_id_p";
jbe@59 35 DELETE FROM "direct_voter"
jbe@59 36 WHERE "issue_id" = "issue_id_p";
jbe@59 37 DELETE FROM "delegating_interest_snapshot"
jbe@59 38 WHERE "issue_id" = "issue_id_p";
jbe@59 39 DELETE FROM "direct_interest_snapshot"
jbe@59 40 WHERE "issue_id" = "issue_id_p";
jbe@59 41 DELETE FROM "delegating_population_snapshot"
jbe@59 42 WHERE "issue_id" = "issue_id_p";
jbe@59 43 DELETE FROM "direct_population_snapshot"
jbe@59 44 WHERE "issue_id" = "issue_id_p";
jbe@59 45 DELETE FROM "delegation"
jbe@59 46 WHERE "issue_id" = "issue_id_p";
jbe@59 47 DELETE FROM "supporter"
jbe@59 48 WHERE "issue_id" = "issue_id_p";
jbe@59 49 UPDATE "issue" SET
jbe@59 50 "closed" = "issue_row"."closed",
jbe@59 51 "ranks_available" = "issue_row"."ranks_available",
jbe@59 52 "cleaned" = now()
jbe@59 53 WHERE "id" = "issue_id_p";
jbe@59 54 END IF;
jbe@59 55 RETURN;
jbe@59 56 END;
jbe@59 57 $$;
jbe@59 58
jbe@59 59 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
jbe@59 60
jbe@59 61 COMMIT;

Impressum / About Us