# HG changeset patch
# User jbe
# Date 1360893116 -3600
# Node ID b83ab26828a884bd3c7b733391e4f21e08d39f4b
# Parent  f17ee916711a9e8443c2810299a9d1418db2f162
Completely removed table locking in favor of transaction isolation requirements

diff -r f17ee916711a -r b83ab26828a8 core.sql
--- a/core.sql	Fri Feb 15 02:13:09 2013 +0100
+++ b/core.sql	Fri Feb 15 02:51:56 2013 +0100
@@ -1567,7 +1567,7 @@
 
 
 ---------------------------------------------------------------
--- Ensure that votes are not modified when issues are frozen --
+-- Ensure that votes are not modified when issues are closed --
 ---------------------------------------------------------------
 
 -- NOTE: Frontends should ensure this anyway, but in case of programming
@@ -2859,19 +2859,56 @@
 
 
 
+---------------------------
+-- Transaction isolation --
+---------------------------
+
+CREATE FUNCTION "require_transaction_isolation"()
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    BEGIN
+      IF
+        current_setting('transaction_isolation') NOT IN
+        ('repeatable read', 'serializable')
+      THEN
+        RAISE EXCEPTION 'Insufficient transaction isolation level';
+      END IF;
+      RETURN;
+    END;
+  $$;
+
+
+CREATE FUNCTION "dont_require_transaction_isolation"()
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    BEGIN
+      IF
+        current_setting('transaction_isolation') IN
+        ('repeatable read', 'serializable')
+      THEN
+        RAISE WARNING 'Unneccessary transaction isolation level: %',
+          current_setting('transaction_isolation');
+      END IF;
+      RETURN;
+    END;
+  $$;
+
+
+
 ------------------------------------------------------------------------
 -- Regular tasks, except calculcation of snapshots and voting results --
 ------------------------------------------------------------------------
 
+
 CREATE FUNCTION "check_activity"()
   RETURNS VOID
   LANGUAGE 'plpgsql' VOLATILE AS $$
     DECLARE
       "system_setting_row" "system_setting"%ROWTYPE;
     BEGIN
+      PERFORM "dont_require_transaction_isolation"();
       SELECT * INTO "system_setting_row" FROM "system_setting";
       IF "system_setting_row"."member_ttl" NOTNULL THEN
-        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
         UPDATE "member" SET "active" = FALSE
           WHERE "active" = TRUE
           AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
@@ -2887,12 +2924,7 @@
   RETURNS VOID
   LANGUAGE 'plpgsql' VOLATILE AS $$
     BEGIN
-      LOCK TABLE "member"       IN SHARE MODE;
-      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
-      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
-      LOCK TABLE "area"         IN EXCLUSIVE MODE;
-      LOCK TABLE "privilege"    IN SHARE MODE;
-      LOCK TABLE "membership"   IN SHARE MODE;
+      PERFORM "require_transaction_isolation"();
       DELETE FROM "member_count";
       INSERT INTO "member_count" ("total_count")
         SELECT "total_count" FROM "member_count_view";
@@ -2989,6 +3021,7 @@
       "weight_ary"   FLOAT[];
       "min_weight_v" FLOAT;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       UPDATE "initiative" SET "harmonic_weight" = NULL
         WHERE "issue_id" = "issue_id_p";
       LOOP
@@ -3352,6 +3385,7 @@
       "initiative_id_v"    "initiative"."id"%TYPE;
       "suggestion_id_v"    "suggestion"."id"%TYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       PERFORM "create_population_snapshot"("issue_id_p");
       PERFORM "create_interest_snapshot"("issue_id_p");
       UPDATE "issue" SET
@@ -3534,6 +3568,7 @@
     DECLARE
       "event_v" "issue"."latest_snapshot_event"%TYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
         WHERE "id" = "issue_id_p" FOR UPDATE;
       UPDATE "issue" SET "latest_snapshot_event" = "event_p"
@@ -3575,6 +3610,7 @@
       "issue_row"  "issue"%ROWTYPE;
       "policy_row" "policy"%ROWTYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT * INTO "issue_row" FROM "issue"
         WHERE "id" = "issue_id_p" FOR UPDATE;
       SELECT * INTO "policy_row"
@@ -3616,8 +3652,9 @@
       "policy_row"     "policy"%ROWTYPE;
       "initiative_row" "initiative"%ROWTYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT * INTO "issue_row" FROM "issue"
-        WHERE "id" = "issue_id_p" FOR SHARE;
+        WHERE "id" = "issue_id_p";
       SELECT * INTO "policy_row" FROM "policy"
         WHERE "id" = "issue_row"."policy_id";
       FOR "initiative_row" IN
@@ -3658,6 +3695,7 @@
       "policy_row"     "policy"%ROWTYPE;
       "initiative_row" "initiative"%ROWTYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT * INTO "issue_row" FROM "issue"
         WHERE "id" = "issue_id_p" FOR UPDATE;
       SELECT * INTO "policy_row" FROM "policy"
@@ -3824,6 +3862,7 @@
       "unit_id_v"   "unit"."id"%TYPE;
       "member_id_v" "member"."id"%TYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
       -- delete timestamp of voting comment:
@@ -3930,6 +3969,7 @@
       "winners_ary"       INTEGER[];
       "initiative_id_v"   "initiative"."id"%TYPE;
     BEGIN
+      PERFORM "require_transaction_isolation"();
       SELECT * INTO "issue_row"
         FROM "issue" WHERE "id" = "issue_id_p";
       SELECT * INTO "policy_row"
@@ -4241,6 +4281,7 @@
       "issue_row" "issue"%ROWTYPE;
       "state_v"   "issue_state";
     BEGIN
+      PERFORM "require_transaction_isolation"();
       IF "persist" ISNULL THEN
         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
           FOR UPDATE;
@@ -4391,6 +4432,7 @@
       "issue_id_v" "issue"."id"%TYPE;
       "persist_v"  "check_issue_persistence";
     BEGIN
+      RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
       DELETE FROM "expired_session";
       PERFORM "check_activity"();
       PERFORM "calculate_member_counts"();
diff -r f17ee916711a -r b83ab26828a8 test.sql
--- a/test.sql	Fri Feb 15 02:13:09 2013 +0100
+++ b/test.sql	Fri Feb 15 02:51:56 2013 +0100
@@ -3,6 +3,9 @@
 
 BEGIN;
 
+-- set transaction isolation level to be able to call "check_everything"() function
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
 INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES
   ('now', 'now', TRUE, 'user1',  'User #1'),   -- id  1
   ('now', 'now', TRUE, 'user2',  'User #2'),   -- id  2