liquid_feedback_core

changeset 333:b83ab26828a8

Completely removed table locking in favor of transaction isolation requirements
author jbe
date Fri Feb 15 02:51:56 2013 +0100 (2013-02-15)
parents f17ee916711a
children a2ab4fb1d0c7
files core.sql test.sql
line diff
     1.1 --- a/core.sql	Fri Feb 15 02:13:09 2013 +0100
     1.2 +++ b/core.sql	Fri Feb 15 02:51:56 2013 +0100
     1.3 @@ -1567,7 +1567,7 @@
     1.4  
     1.5  
     1.6  ---------------------------------------------------------------
     1.7 --- Ensure that votes are not modified when issues are frozen --
     1.8 +-- Ensure that votes are not modified when issues are closed --
     1.9  ---------------------------------------------------------------
    1.10  
    1.11  -- NOTE: Frontends should ensure this anyway, but in case of programming
    1.12 @@ -2859,19 +2859,56 @@
    1.13  
    1.14  
    1.15  
    1.16 +---------------------------
    1.17 +-- Transaction isolation --
    1.18 +---------------------------
    1.19 +
    1.20 +CREATE FUNCTION "require_transaction_isolation"()
    1.21 +  RETURNS VOID
    1.22 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.23 +    BEGIN
    1.24 +      IF
    1.25 +        current_setting('transaction_isolation') NOT IN
    1.26 +        ('repeatable read', 'serializable')
    1.27 +      THEN
    1.28 +        RAISE EXCEPTION 'Insufficient transaction isolation level';
    1.29 +      END IF;
    1.30 +      RETURN;
    1.31 +    END;
    1.32 +  $$;
    1.33 +
    1.34 +
    1.35 +CREATE FUNCTION "dont_require_transaction_isolation"()
    1.36 +  RETURNS VOID
    1.37 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.38 +    BEGIN
    1.39 +      IF
    1.40 +        current_setting('transaction_isolation') IN
    1.41 +        ('repeatable read', 'serializable')
    1.42 +      THEN
    1.43 +        RAISE WARNING 'Unneccessary transaction isolation level: %',
    1.44 +          current_setting('transaction_isolation');
    1.45 +      END IF;
    1.46 +      RETURN;
    1.47 +    END;
    1.48 +  $$;
    1.49 +
    1.50 +
    1.51 +
    1.52  ------------------------------------------------------------------------
    1.53  -- Regular tasks, except calculcation of snapshots and voting results --
    1.54  ------------------------------------------------------------------------
    1.55  
    1.56 +
    1.57  CREATE FUNCTION "check_activity"()
    1.58    RETURNS VOID
    1.59    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.60      DECLARE
    1.61        "system_setting_row" "system_setting"%ROWTYPE;
    1.62      BEGIN
    1.63 +      PERFORM "dont_require_transaction_isolation"();
    1.64        SELECT * INTO "system_setting_row" FROM "system_setting";
    1.65        IF "system_setting_row"."member_ttl" NOTNULL THEN
    1.66 -        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.67          UPDATE "member" SET "active" = FALSE
    1.68            WHERE "active" = TRUE
    1.69            AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
    1.70 @@ -2887,12 +2924,7 @@
    1.71    RETURNS VOID
    1.72    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.73      BEGIN
    1.74 -      LOCK TABLE "member"       IN SHARE MODE;
    1.75 -      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
    1.76 -      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
    1.77 -      LOCK TABLE "area"         IN EXCLUSIVE MODE;
    1.78 -      LOCK TABLE "privilege"    IN SHARE MODE;
    1.79 -      LOCK TABLE "membership"   IN SHARE MODE;
    1.80 +      PERFORM "require_transaction_isolation"();
    1.81        DELETE FROM "member_count";
    1.82        INSERT INTO "member_count" ("total_count")
    1.83          SELECT "total_count" FROM "member_count_view";
    1.84 @@ -2989,6 +3021,7 @@
    1.85        "weight_ary"   FLOAT[];
    1.86        "min_weight_v" FLOAT;
    1.87      BEGIN
    1.88 +      PERFORM "require_transaction_isolation"();
    1.89        UPDATE "initiative" SET "harmonic_weight" = NULL
    1.90          WHERE "issue_id" = "issue_id_p";
    1.91        LOOP
    1.92 @@ -3352,6 +3385,7 @@
    1.93        "initiative_id_v"    "initiative"."id"%TYPE;
    1.94        "suggestion_id_v"    "suggestion"."id"%TYPE;
    1.95      BEGIN
    1.96 +      PERFORM "require_transaction_isolation"();
    1.97        PERFORM "create_population_snapshot"("issue_id_p");
    1.98        PERFORM "create_interest_snapshot"("issue_id_p");
    1.99        UPDATE "issue" SET
   1.100 @@ -3534,6 +3568,7 @@
   1.101      DECLARE
   1.102        "event_v" "issue"."latest_snapshot_event"%TYPE;
   1.103      BEGIN
   1.104 +      PERFORM "require_transaction_isolation"();
   1.105        SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
   1.106          WHERE "id" = "issue_id_p" FOR UPDATE;
   1.107        UPDATE "issue" SET "latest_snapshot_event" = "event_p"
   1.108 @@ -3575,6 +3610,7 @@
   1.109        "issue_row"  "issue"%ROWTYPE;
   1.110        "policy_row" "policy"%ROWTYPE;
   1.111      BEGIN
   1.112 +      PERFORM "require_transaction_isolation"();
   1.113        SELECT * INTO "issue_row" FROM "issue"
   1.114          WHERE "id" = "issue_id_p" FOR UPDATE;
   1.115        SELECT * INTO "policy_row"
   1.116 @@ -3616,8 +3652,9 @@
   1.117        "policy_row"     "policy"%ROWTYPE;
   1.118        "initiative_row" "initiative"%ROWTYPE;
   1.119      BEGIN
   1.120 +      PERFORM "require_transaction_isolation"();
   1.121        SELECT * INTO "issue_row" FROM "issue"
   1.122 -        WHERE "id" = "issue_id_p" FOR SHARE;
   1.123 +        WHERE "id" = "issue_id_p";
   1.124        SELECT * INTO "policy_row" FROM "policy"
   1.125          WHERE "id" = "issue_row"."policy_id";
   1.126        FOR "initiative_row" IN
   1.127 @@ -3658,6 +3695,7 @@
   1.128        "policy_row"     "policy"%ROWTYPE;
   1.129        "initiative_row" "initiative"%ROWTYPE;
   1.130      BEGIN
   1.131 +      PERFORM "require_transaction_isolation"();
   1.132        SELECT * INTO "issue_row" FROM "issue"
   1.133          WHERE "id" = "issue_id_p" FOR UPDATE;
   1.134        SELECT * INTO "policy_row" FROM "policy"
   1.135 @@ -3824,6 +3862,7 @@
   1.136        "unit_id_v"   "unit"."id"%TYPE;
   1.137        "member_id_v" "member"."id"%TYPE;
   1.138      BEGIN
   1.139 +      PERFORM "require_transaction_isolation"();
   1.140        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.141        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.142        -- delete timestamp of voting comment:
   1.143 @@ -3930,6 +3969,7 @@
   1.144        "winners_ary"       INTEGER[];
   1.145        "initiative_id_v"   "initiative"."id"%TYPE;
   1.146      BEGIN
   1.147 +      PERFORM "require_transaction_isolation"();
   1.148        SELECT * INTO "issue_row"
   1.149          FROM "issue" WHERE "id" = "issue_id_p";
   1.150        SELECT * INTO "policy_row"
   1.151 @@ -4241,6 +4281,7 @@
   1.152        "issue_row" "issue"%ROWTYPE;
   1.153        "state_v"   "issue_state";
   1.154      BEGIN
   1.155 +      PERFORM "require_transaction_isolation"();
   1.156        IF "persist" ISNULL THEN
   1.157          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.158            FOR UPDATE;
   1.159 @@ -4391,6 +4432,7 @@
   1.160        "issue_id_v" "issue"."id"%TYPE;
   1.161        "persist_v"  "check_issue_persistence";
   1.162      BEGIN
   1.163 +      RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
   1.164        DELETE FROM "expired_session";
   1.165        PERFORM "check_activity"();
   1.166        PERFORM "calculate_member_counts"();
     2.1 --- a/test.sql	Fri Feb 15 02:13:09 2013 +0100
     2.2 +++ b/test.sql	Fri Feb 15 02:51:56 2013 +0100
     2.3 @@ -3,6 +3,9 @@
     2.4  
     2.5  BEGIN;
     2.6  
     2.7 +-- set transaction isolation level to be able to call "check_everything"() function
     2.8 +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
     2.9 +
    2.10  INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES
    2.11    ('now', 'now', TRUE, 'user1',  'User #1'),   -- id  1
    2.12    ('now', 'now', TRUE, 'user2',  'User #2'),   -- id  2

Impressum / About Us