liquid_feedback_core
changeset 330:29ca4c6e2e78
Removed locking system
author | jbe |
---|---|
date | Mon Feb 11 02:39:02 2013 +0100 (2013-02-11) |
parents | b9e625add823 |
children | c705309b5727 |
files | core.sql |
line diff
1.1 --- a/core.sql Sun Feb 10 23:37:19 2013 +0100 1.2 +++ b/core.sql Mon Feb 11 02:39:02 2013 +0100 1.3 @@ -2849,138 +2849,6 @@ 1.4 1.5 1.6 1.7 ------------------------------------------------- 1.8 --- Locking for snapshots and voting procedure -- 1.9 ------------------------------------------------- 1.10 - 1.11 - 1.12 -CREATE FUNCTION "share_row_lock_issue_trigger"() 1.13 - RETURNS TRIGGER 1.14 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.15 - BEGIN 1.16 - IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.17 - PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE; 1.18 - END IF; 1.19 - IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.20 - PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE; 1.21 - RETURN NEW; 1.22 - ELSE 1.23 - RETURN OLD; 1.24 - END IF; 1.25 - END; 1.26 - $$; 1.27 - 1.28 -COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables'; 1.29 - 1.30 - 1.31 -CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"() 1.32 - RETURNS TRIGGER 1.33 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.34 - BEGIN 1.35 - IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN 1.36 - PERFORM NULL FROM "issue" 1.37 - JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.38 - WHERE "initiative"."id" = OLD."initiative_id" 1.39 - FOR SHARE OF "issue"; 1.40 - END IF; 1.41 - IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.42 - PERFORM NULL FROM "issue" 1.43 - JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.44 - WHERE "initiative"."id" = NEW."initiative_id" 1.45 - FOR SHARE OF "issue"; 1.46 - RETURN NEW; 1.47 - ELSE 1.48 - RETURN OLD; 1.49 - END IF; 1.50 - END; 1.51 - $$; 1.52 - 1.53 -COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"'; 1.54 - 1.55 - 1.56 -CREATE TRIGGER "share_row_lock_issue" 1.57 - BEFORE INSERT OR UPDATE OR DELETE ON "initiative" 1.58 - FOR EACH ROW EXECUTE PROCEDURE 1.59 - "share_row_lock_issue_trigger"(); 1.60 - 1.61 -CREATE TRIGGER "share_row_lock_issue" 1.62 - BEFORE INSERT OR UPDATE OR DELETE ON "interest" 1.63 - FOR EACH ROW EXECUTE PROCEDURE 1.64 - "share_row_lock_issue_trigger"(); 1.65 - 1.66 -CREATE TRIGGER "share_row_lock_issue" 1.67 - BEFORE INSERT OR UPDATE OR DELETE ON "supporter" 1.68 - FOR EACH ROW EXECUTE PROCEDURE 1.69 - "share_row_lock_issue_trigger"(); 1.70 - 1.71 -CREATE TRIGGER "share_row_lock_issue_via_initiative" 1.72 - BEFORE INSERT OR UPDATE OR DELETE ON "opinion" 1.73 - FOR EACH ROW EXECUTE PROCEDURE 1.74 - "share_row_lock_issue_via_initiative_trigger"(); 1.75 - 1.76 -CREATE TRIGGER "share_row_lock_issue" 1.77 - BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter" 1.78 - FOR EACH ROW EXECUTE PROCEDURE 1.79 - "share_row_lock_issue_trigger"(); 1.80 - 1.81 -CREATE TRIGGER "share_row_lock_issue" 1.82 - BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter" 1.83 - FOR EACH ROW EXECUTE PROCEDURE 1.84 - "share_row_lock_issue_trigger"(); 1.85 - 1.86 -CREATE TRIGGER "share_row_lock_issue" 1.87 - BEFORE INSERT OR UPDATE OR DELETE ON "vote" 1.88 - FOR EACH ROW EXECUTE PROCEDURE 1.89 - "share_row_lock_issue_trigger"(); 1.90 - 1.91 -COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function'; 1.92 -COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function'; 1.93 -COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function'; 1.94 -COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function'; 1.95 -COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function'; 1.96 -COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function'; 1.97 -COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function'; 1.98 - 1.99 - 1.100 -CREATE FUNCTION "lock_issue" 1.101 - ( "issue_id_p" "issue"."id"%TYPE ) 1.102 - RETURNS VOID 1.103 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.104 - BEGIN 1.105 - -- The following locking order is used: 1.106 - -- 1st) row-level lock on the issue 1.107 - -- 2nd) table-level locks in order of occurrence in the core.sql file 1.108 - PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.109 - -- NOTE: The row-level exclusive lock in combination with the 1.110 - -- share_row_lock_issue(_via_initiative)_trigger functions (which 1.111 - -- acquire a row-level share lock on the issue) ensure that no data 1.112 - -- is changed, which could affect calculation of snapshots or 1.113 - -- counting of votes. Table "delegation" must be table-level-locked, 1.114 - -- as it also contains issue- and global-scope delegations. 1.115 - PERFORM NULL FROM "member" WHERE "active" FOR SHARE; 1.116 - -- NOTE: As we later cause implicit row-level share locks on many 1.117 - -- active members, we lock them before locking any other table 1.118 - -- to avoid deadlocks 1.119 - LOCK TABLE "member" IN SHARE MODE; 1.120 - LOCK TABLE "privilege" IN SHARE MODE; 1.121 - LOCK TABLE "membership" IN SHARE MODE; 1.122 - LOCK TABLE "policy" IN SHARE MODE; 1.123 - LOCK TABLE "delegation" IN SHARE MODE; 1.124 - LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 1.125 - LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 1.126 - LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 1.127 - LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 1.128 - LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 1.129 - RETURN; 1.130 - END; 1.131 - $$; 1.132 - 1.133 -COMMENT ON FUNCTION "lock_issue" 1.134 - ( "issue"."id"%TYPE ) 1.135 - IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.'; 1.136 - 1.137 - 1.138 - 1.139 ------------------------------------------------------------------------ 1.140 -- Regular tasks, except calculcation of snapshots and voting results -- 1.141 ------------------------------------------------------------------------