1.1 --- a/core.sql	Sun Aug 15 17:11:02 2010 +0200
     1.2 +++ b/core.sql	Mon Aug 16 14:45:21 2010 +0200
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.2.4', 1, 2, 4))
     1.8 +  SELECT * FROM (VALUES ('1.2.5', 1, 2, 5))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -1917,39 +1917,123 @@
    1.13  -- Locking for snapshots and voting procedure --
    1.14  ------------------------------------------------
    1.15  
    1.16 -CREATE FUNCTION "global_lock"() RETURNS VOID
    1.17 +
    1.18 +CREATE FUNCTION "share_row_lock_issue_trigger"()
    1.19 +  RETURNS TRIGGER
    1.20 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.21 +    BEGIN
    1.22 +      IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
    1.23 +        PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
    1.24 +      END IF;
    1.25 +      IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    1.26 +        PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
    1.27 +        RETURN NEW;
    1.28 +      ELSE
    1.29 +        RETURN OLD;
    1.30 +      END IF;
    1.31 +    END;
    1.32 +  $$;
    1.33 +
    1.34 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
    1.35 +
    1.36 +
    1.37 +CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
    1.38 +  RETURNS TRIGGER
    1.39    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.40      BEGIN
    1.41 -      -- NOTE: PostgreSQL allows reading, while tables are locked in
    1.42 -      -- exclusive move. Transactions should be kept short anyway!
    1.43 -      LOCK TABLE "member"     IN EXCLUSIVE MODE;
    1.44 -      LOCK TABLE "area"       IN EXCLUSIVE MODE;
    1.45 -      LOCK TABLE "membership" IN EXCLUSIVE MODE;
    1.46 -      -- NOTE: "member", "area" and "membership" are locked first to
    1.47 -      -- prevent deadlocks in combination with "calculate_member_counts"()
    1.48 -      LOCK TABLE "policy"     IN EXCLUSIVE MODE;
    1.49 -      LOCK TABLE "issue"      IN EXCLUSIVE MODE;
    1.50 -      LOCK TABLE "initiative" IN EXCLUSIVE MODE;
    1.51 -      LOCK TABLE "draft"      IN EXCLUSIVE MODE;
    1.52 -      LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
    1.53 -      LOCK TABLE "interest"   IN EXCLUSIVE MODE;
    1.54 -      LOCK TABLE "initiator"  IN EXCLUSIVE MODE;
    1.55 -      LOCK TABLE "supporter"  IN EXCLUSIVE MODE;
    1.56 -      LOCK TABLE "opinion"    IN EXCLUSIVE MODE;
    1.57 -      LOCK TABLE "delegation" IN EXCLUSIVE MODE;
    1.58 +      IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
    1.59 +        PERFORM NULL FROM "issue"
    1.60 +          JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
    1.61 +          WHERE "initiative"."id" = OLD."initiative_id"
    1.62 +          FOR SHARE OF "issue";
    1.63 +      END IF;
    1.64 +      IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    1.65 +        PERFORM NULL FROM "issue"
    1.66 +          JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
    1.67 +          WHERE "initiative"."id" = NEW."initiative_id"
    1.68 +          FOR SHARE OF "issue";
    1.69 +        RETURN NEW;
    1.70 +      ELSE
    1.71 +        RETURN OLD;
    1.72 +      END IF;
    1.73 +    END;
    1.74 +  $$;
    1.75 +
    1.76 +COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
    1.77 +
    1.78 +
    1.79 +CREATE TRIGGER "share_row_lock_issue"
    1.80 +  BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
    1.81 +  FOR EACH ROW EXECUTE PROCEDURE
    1.82 +  "share_row_lock_issue_trigger"();
    1.83 +
    1.84 +CREATE TRIGGER "share_row_lock_issue"
    1.85 +  BEFORE INSERT OR UPDATE OR DELETE ON "interest"
    1.86 +  FOR EACH ROW EXECUTE PROCEDURE
    1.87 +  "share_row_lock_issue_trigger"();
    1.88 +
    1.89 +CREATE TRIGGER "share_row_lock_issue"
    1.90 +  BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
    1.91 +  FOR EACH ROW EXECUTE PROCEDURE
    1.92 +  "share_row_lock_issue_trigger"();
    1.93 +
    1.94 +CREATE TRIGGER "share_row_lock_issue_via_initiative"
    1.95 +  BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
    1.96 +  FOR EACH ROW EXECUTE PROCEDURE
    1.97 +  "share_row_lock_issue_via_initiative_trigger"();
    1.98 +
    1.99 +CREATE TRIGGER "share_row_lock_issue"
   1.100 +  BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
   1.101 +  FOR EACH ROW EXECUTE PROCEDURE
   1.102 +  "share_row_lock_issue_trigger"();
   1.103 +
   1.104 +CREATE TRIGGER "share_row_lock_issue"
   1.105 +  BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
   1.106 +  FOR EACH ROW EXECUTE PROCEDURE
   1.107 +  "share_row_lock_issue_trigger"();
   1.108 +
   1.109 +CREATE TRIGGER "share_row_lock_issue"
   1.110 +  BEFORE INSERT OR UPDATE OR DELETE ON "vote"
   1.111 +  FOR EACH ROW EXECUTE PROCEDURE
   1.112 +  "share_row_lock_issue_trigger"();
   1.113 +
   1.114 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "initiative"       IS 'See "lock_issue" function';
   1.115 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "interest"         IS 'See "lock_issue" function';
   1.116 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "supporter"        IS 'See "lock_issue" function';
   1.117 +COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion"          IS 'See "lock_issue" function';
   1.118 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "direct_voter"     IS 'See "lock_issue" function';
   1.119 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "delegating_voter" IS 'See "lock_issue" function';
   1.120 +COMMENT ON TRIGGER "share_row_lock_issue"                ON "vote"             IS 'See "lock_issue" function';
   1.121 +
   1.122 +
   1.123 +CREATE FUNCTION "lock_issue"
   1.124 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.125 +  RETURNS VOID
   1.126 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.127 +    BEGIN
   1.128 +      LOCK TABLE "member"     IN SHARE MODE;
   1.129 +      LOCK TABLE "membership" IN SHARE MODE;
   1.130 +      LOCK TABLE "policy"     IN SHARE MODE;
   1.131 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   1.132 +      -- NOTE: The row-level exclusive lock in combination with the
   1.133 +      -- share_row_lock_issue(_via_initiative)_trigger functions (which
   1.134 +      -- acquire a row-level share lock on the issue) ensure that no data
   1.135 +      -- is changed, which could affect calculation of snapshots or
   1.136 +      -- counting of votes. Table "delegation" must be table-level-locked,
   1.137 +      -- as it also contains issue- and global-scope delegations.
   1.138 +      LOCK TABLE "delegation" IN SHARE MODE;
   1.139        LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
   1.140        LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
   1.141        LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
   1.142        LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
   1.143        LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
   1.144 -      LOCK TABLE "direct_voter"     IN EXCLUSIVE MODE;
   1.145 -      LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
   1.146 -      LOCK TABLE "vote"             IN EXCLUSIVE MODE;
   1.147        RETURN;
   1.148      END;
   1.149    $$;
   1.150  
   1.151 -COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
   1.152 +COMMENT ON FUNCTION "lock_issue"
   1.153 +  ( "issue"."id"%TYPE )
   1.154 +  IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
   1.155  
   1.156  
   1.157  
   1.158 @@ -1961,9 +2045,10 @@
   1.159    RETURNS VOID
   1.160    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.161      BEGIN
   1.162 -      LOCK TABLE "member"     IN EXCLUSIVE MODE;
   1.163 -      LOCK TABLE "area"       IN EXCLUSIVE MODE;
   1.164 -      LOCK TABLE "membership" IN EXCLUSIVE MODE;
   1.165 +      LOCK TABLE "member"       IN SHARE MODE;
   1.166 +      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
   1.167 +      LOCK TABLE "area"         IN EXCLUSIVE MODE;
   1.168 +      LOCK TABLE "membership"   IN SHARE MODE;
   1.169        DELETE FROM "member_count";
   1.170        INSERT INTO "member_count" ("total_count")
   1.171          SELECT "total_count" FROM "member_count_view";
   1.172 @@ -2109,7 +2194,7 @@
   1.173    $$;
   1.174  
   1.175  COMMENT ON FUNCTION "create_population_snapshot"
   1.176 -  ( "issue_id_p" "issue"."id"%TYPE )
   1.177 +  ( "issue"."id"%TYPE )
   1.178    IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
   1.179  
   1.180  
   1.181 @@ -2269,7 +2354,7 @@
   1.182        "initiative_id_v"    "initiative"."id"%TYPE;
   1.183        "suggestion_id_v"    "suggestion"."id"%TYPE;
   1.184      BEGIN
   1.185 -      PERFORM "global_lock"();
   1.186 +      PERFORM "lock_issue"("issue_id_p");
   1.187        PERFORM "create_population_snapshot"("issue_id_p");
   1.188        PERFORM "create_interest_snapshot"("issue_id_p");
   1.189        UPDATE "issue" SET
   1.190 @@ -2668,7 +2753,7 @@
   1.191        "issue_row"   "issue"%ROWTYPE;
   1.192        "member_id_v" "member"."id"%TYPE;
   1.193      BEGIN
   1.194 -      PERFORM "global_lock"();
   1.195 +      PERFORM "lock_issue"("issue_id_p");
   1.196        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.197        DELETE FROM "delegating_voter"
   1.198          WHERE "issue_id" = "issue_id_p";
   1.199 @@ -3068,7 +3153,7 @@
   1.200        "policy_row"        "policy"%ROWTYPE;
   1.201        "voting_requested_v" BOOLEAN;
   1.202      BEGIN
   1.203 -      PERFORM "global_lock"();
   1.204 +      PERFORM "lock_issue"("issue_id_p");
   1.205        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.206        -- only process open issues:
   1.207        IF "issue_row"."closed" ISNULL THEN