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