liquid_feedback_core

changeset 331:c705309b5727

Work on new system to allow the background job (lf_update) run without intensive locking (incomplete yet)
author jbe
date Thu Feb 14 23:50:50 2013 +0100 (2013-02-14)
parents 29ca4c6e2e78
children f17ee916711a
files core.sql
line diff
     1.1 --- a/core.sql	Mon Feb 11 02:39:02 2013 +0100
     1.2 +++ b/core.sql	Thu Feb 14 23:50:50 2013 +0100
     1.3 @@ -1578,16 +1578,21 @@
     1.4    RETURNS TRIGGER
     1.5    LANGUAGE 'plpgsql' VOLATILE AS $$
     1.6      DECLARE
     1.7 -      "issue_id_v" "issue"."id"%TYPE;
     1.8 -      "issue_row"  "issue"%ROWTYPE;
     1.9 +      "issue_id_v"            "issue"."id"%TYPE;
    1.10 +      "issue_row"             "issue"%ROWTYPE;
    1.11 +      "direct_voter_update_v" BOOLEAN;
    1.12      BEGIN
    1.13 +      "direct_voter_update_v" := FALSE;
    1.14        IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
    1.15          IF
    1.16            OLD."issue_id"  = NEW."issue_id"  AND
    1.17            OLD."member_id" = NEW."member_id" AND
    1.18            OLD."weight"    = NEW."weight"
    1.19          THEN
    1.20 -          RETURN NULL;  -- allows changing of voter comment
    1.21 +          IF OLD."weight" = NEW."weight" THEN
    1.22 +            RETURN NULL;  -- allows changing of voter comment
    1.23 +          END IF;
    1.24 +          "direct_voter_update_v" := TRUE;
    1.25          END IF;
    1.26        END IF;
    1.27        IF TG_OP = 'DELETE' THEN
    1.28 @@ -1599,6 +1604,12 @@
    1.29          WHERE "id" = "issue_id_v" FOR SHARE;
    1.30        IF "issue_row"."closed" NOTNULL THEN
    1.31          RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
    1.32 +      ELSIF
    1.33 +        "issue_row"."state" = 'voting' AND
    1.34 +        "issue_row"."phase_finished" NOTNULL AND
    1.35 +        "direct_voter_update_v" = FALSE  -- allow change of weight during calculation
    1.36 +      THEN
    1.37 +        RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
    1.38        END IF;
    1.39        RETURN NULL;
    1.40      END;
    1.41 @@ -2054,7 +2065,6 @@
    1.42      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
    1.43    WHERE "issue"."state" = 'voting'
    1.44    AND "issue"."phase_finished" NOTNULL
    1.45 -  AND "issue"."cleaned" ISNULL
    1.46    AND (
    1.47      "winning_initiative"."id" != "losing_initiative"."id" OR
    1.48      ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
    1.49 @@ -2860,8 +2870,8 @@
    1.50        "system_setting_row" "system_setting"%ROWTYPE;
    1.51      BEGIN
    1.52        SELECT * INTO "system_setting_row" FROM "system_setting";
    1.53 -      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.54        IF "system_setting_row"."member_ttl" NOTNULL THEN
    1.55 +        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.56          UPDATE "member" SET "active" = FALSE
    1.57            WHERE "active" = TRUE
    1.58            AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
    1.59 @@ -3342,11 +3352,10 @@
    1.60        "initiative_id_v"    "initiative"."id"%TYPE;
    1.61        "suggestion_id_v"    "suggestion"."id"%TYPE;
    1.62      BEGIN
    1.63 -      PERFORM "lock_issue"("issue_id_p");
    1.64        PERFORM "create_population_snapshot"("issue_id_p");
    1.65        PERFORM "create_interest_snapshot"("issue_id_p");
    1.66        UPDATE "issue" SET
    1.67 -        "snapshot" = now(),
    1.68 +        "snapshot" = coalesce("phase_finished", now()),
    1.69          "latest_snapshot_event" = 'periodic',
    1.70          "population" = (
    1.71            SELECT coalesce(sum("weight"), 0)
    1.72 @@ -3557,7 +3566,48 @@
    1.73  -- Freezing issues --
    1.74  ---------------------
    1.75  
    1.76 -CREATE FUNCTION "freeze_after_snapshot"
    1.77 +
    1.78 +CREATE FUNCTION "issue_admission"
    1.79 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.80 +  RETURNS VOID
    1.81 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.82 +    DECLARE
    1.83 +      "issue_row"  "issue"%ROWTYPE;
    1.84 +      "policy_row" "policy"%ROWTYPE;
    1.85 +    BEGIN
    1.86 +      SELECT * INTO "issue_row" FROM "issue"
    1.87 +        WHERE "id" = "issue_id_p" FOR UPDATE;
    1.88 +      SELECT * INTO "policy_row"
    1.89 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    1.90 +      IF EXISTS (
    1.91 +        SELECT NULL FROM "initiative"
    1.92 +        WHERE "issue_id" = "issue_id_p"
    1.93 +        AND "supporter_count" > 0
    1.94 +        AND "supporter_count" * "policy_row"."issue_quorum_den"
    1.95 +        >= "issue_row"."population" * "policy_row"."issue_quorum_num"
    1.96 +      ) THEN
    1.97 +        UPDATE "issue" SET
    1.98 +          "state"          = 'discussion',
    1.99 +          "accepted"       = coalesce("phase_finished", now()),
   1.100 +          "phase_finished" = NULL
   1.101 +          WHERE "id" = "issue_id_p";
   1.102 +      ELSIF "issue_row"."phase_finished" NOTNULL THEN
   1.103 +        UPDATE "issue" SET
   1.104 +          "state"          = 'canceled_issue_not_accepted',
   1.105 +          "closed"         = "phase_finished",
   1.106 +          "phase_finished" = NULL
   1.107 +          WHERE "id" = "issue_id_p";
   1.108 +      END IF;
   1.109 +      RETURN;
   1.110 +    END;
   1.111 +  $$;
   1.112 +
   1.113 +COMMENT ON FUNCTION "issue_admission"
   1.114 +  ( "issue"."id"%TYPE )
   1.115 +  IS 'Checks admission of an issue, and either sets "accepted" to TRUE, or cancels the issue if "phase_finished" is set';
   1.116 +
   1.117 +
   1.118 +CREATE FUNCTION "initiative_admission"
   1.119    ( "issue_id_p" "issue"."id"%TYPE )
   1.120    RETURNS VOID
   1.121    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.122 @@ -3566,13 +3616,14 @@
   1.123        "policy_row"     "policy"%ROWTYPE;
   1.124        "initiative_row" "initiative"%ROWTYPE;
   1.125      BEGIN
   1.126 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.127 -      SELECT * INTO "policy_row"
   1.128 -        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.129 -      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.130 +      SELECT * INTO "issue_row" FROM "issue"
   1.131 +        WHERE "id" = "issue_id_p" FOR SHARE;
   1.132 +      SELECT * INTO "policy_row" FROM "policy"
   1.133 +        WHERE "id" = "issue_row"."policy_id";
   1.134        FOR "initiative_row" IN
   1.135          SELECT * FROM "initiative"
   1.136          WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.137 +        FOR UPDATE
   1.138        LOOP
   1.139          IF
   1.140            "initiative_row"."polling" OR (
   1.141 @@ -3589,23 +3640,45 @@
   1.142              WHERE "id" = "initiative_row"."id";
   1.143          END IF;
   1.144        END LOOP;
   1.145 +      RETURN;
   1.146 +    END;
   1.147 +  $$;
   1.148 +
   1.149 +COMMENT ON FUNCTION "initiative_admission"
   1.150 +  ( "issue"."id"%TYPE )
   1.151 +  IS 'Sets the "admitted" flag of all initiatives in an issue, according to their supporter count fulfilling the necessary "initiative_quorum"';
   1.152 +
   1.153 +
   1.154 +CREATE FUNCTION "freeze_after_snapshot"
   1.155 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.156 +  RETURNS VOID
   1.157 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.158 +    DECLARE
   1.159 +      "issue_row"      "issue"%ROWTYPE;
   1.160 +      "policy_row"     "policy"%ROWTYPE;
   1.161 +      "initiative_row" "initiative"%ROWTYPE;
   1.162 +    BEGIN
   1.163 +      SELECT * INTO "issue_row" FROM "issue"
   1.164 +        WHERE "id" = "issue_id_p" FOR UPDATE;
   1.165 +      SELECT * INTO "policy_row" FROM "policy"
   1.166 +        WHERE "id" = "issue_row"."policy_id";
   1.167        IF EXISTS (
   1.168          SELECT NULL FROM "initiative"
   1.169          WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.170        ) THEN
   1.171          UPDATE "issue" SET
   1.172            "state"        = 'voting',
   1.173 -          "accepted"     = coalesce("accepted", now()),
   1.174 -          "half_frozen"  = coalesce("half_frozen", now()),
   1.175 -          "fully_frozen" = now()
   1.176 +          "accepted"     = coalesce("accepted", "phase_finished"),
   1.177 +          "half_frozen"  = coalesce("half_frozen", "phase_finished"),
   1.178 +          "fully_frozen" = "phase_finished"
   1.179            WHERE "id" = "issue_id_p";
   1.180        ELSE
   1.181          UPDATE "issue" SET
   1.182            "state"           = 'canceled_no_initiative_admitted',
   1.183 -          "accepted"        = coalesce("accepted", now()),
   1.184 -          "half_frozen"     = coalesce("half_frozen", now()),
   1.185 -          "fully_frozen"    = now(),
   1.186 -          "closed"          = now(),
   1.187 +          "accepted"        = coalesce("accepted", "phase_finished"),
   1.188 +          "half_frozen"     = coalesce("half_frozen", "phase_finished"),
   1.189 +          "fully_frozen"    = "phase_finished",
   1.190 +          "closed"          = "phase_finished",
   1.191            "ranks_available" = TRUE
   1.192            WHERE "id" = "issue_id_p";
   1.193          -- NOTE: The following DELETE statements have effect only when
   1.194 @@ -3620,7 +3693,7 @@
   1.195  
   1.196  COMMENT ON FUNCTION "freeze_after_snapshot"
   1.197    ( "issue"."id"%TYPE )
   1.198 -  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
   1.199 +  IS 'This function freezes an issue (fully) and starts voting, but must only be called after all other preparations have been made.';
   1.200  
   1.201  
   1.202  CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
   1.203 @@ -3630,8 +3703,10 @@
   1.204        "issue_row" "issue"%ROWTYPE;
   1.205      BEGIN
   1.206        PERFORM "create_snapshot"("issue_id_p");
   1.207 +      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.208 +      PERFORM "initiative_admission"("issue_id_p");
   1.209 +      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.210        PERFORM "freeze_after_snapshot"("issue_id_p");
   1.211 -      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.212        RETURN;
   1.213      END;
   1.214    $$;
   1.215 @@ -3748,7 +3823,6 @@
   1.216        "unit_id_v"   "unit"."id"%TYPE;
   1.217        "member_id_v" "member"."id"%TYPE;
   1.218      BEGIN
   1.219 -      PERFORM "lock_issue"("issue_id_p");
   1.220        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.221        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.222        -- delete timestamp of voting comment:
   1.223 @@ -3779,15 +3853,6 @@
   1.224        UPDATE "direct_voter" SET "weight" = 1
   1.225          WHERE "issue_id" = "issue_id_p";
   1.226        PERFORM "add_vote_delegations"("issue_id_p");
   1.227 -      -- set voter count and mark issue as being calculated:
   1.228 -      UPDATE "issue" SET
   1.229 -        "state"  = 'calculation',
   1.230 -        "closed" = now(),
   1.231 -        "voter_count" = (
   1.232 -          SELECT coalesce(sum("weight"), 0)
   1.233 -          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.234 -        )
   1.235 -        WHERE "id" = "issue_id_p";
   1.236        -- materialize battle_view:
   1.237        -- NOTE: "closed" column of issue must be set at this point
   1.238        DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.239 @@ -3800,6 +3865,13 @@
   1.240          "winning_initiative_id", "losing_initiative_id",
   1.241          "count"
   1.242          FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.243 +      -- set voter count:
   1.244 +      UPDATE "issue" SET
   1.245 +        "voter_count" = (
   1.246 +          SELECT coalesce(sum("weight"), 0)
   1.247 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.248 +        )
   1.249 +        WHERE "id" = "issue_id_p";
   1.250        -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.251        UPDATE "initiative" SET
   1.252          "positive_votes" = "battle_win"."count",
   1.253 @@ -3858,8 +3930,7 @@
   1.254        "initiative_id_v"   "initiative"."id"%TYPE;
   1.255      BEGIN
   1.256        SELECT * INTO "issue_row"
   1.257 -        FROM "issue" WHERE "id" = "issue_id_p"
   1.258 -        FOR UPDATE;
   1.259 +        FROM "issue" WHERE "id" = "issue_id_p";
   1.260        SELECT * INTO "policy_row"
   1.261          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.262        SELECT count(1) INTO "dimension_v"
   1.263 @@ -4134,7 +4205,8 @@
   1.264            ELSE
   1.265              'finished_without_winner'::"issue_state"
   1.266            END,
   1.267 -        "ranks_available" = TRUE
   1.268 +        "closed" = "phase_finished",
   1.269 +        "phase_finished" = NULL
   1.270          WHERE "id" = "issue_id_p";
   1.271        RETURN;
   1.272      END;
   1.273 @@ -4151,146 +4223,152 @@
   1.274  -----------------------------
   1.275  
   1.276  
   1.277 +CREATE TYPE "check_issue_persistence" AS (
   1.278 +        "state"                 "issue_state",
   1.279 +        "phase_finished"        BOOLEAN,
   1.280 +        "issue_revoked"         BOOLEAN,
   1.281 +        "snapshot_created"      BOOLEAN,
   1.282 +        "harmonic_weights_set"  BOOLEAN,
   1.283 +        "closed_voting"         BOOLEAN );
   1.284 +
   1.285  CREATE FUNCTION "check_issue"
   1.286 -  ( "issue_id_p" "issue"."id"%TYPE )
   1.287 -  RETURNS VOID
   1.288 +  ( "issue_id_p" "issue"."id"%TYPE,
   1.289 +    "persist"    "check_issue_persistence" )
   1.290 +  RETURNS "check_issue_persistence"
   1.291    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.292      DECLARE
   1.293 -      "issue_row"      "issue"%ROWTYPE;
   1.294 -      "policy_row"     "policy"%ROWTYPE;
   1.295 -      "new_snapshot_v" BOOLEAN;
   1.296 +      "issue_row" "issue"%ROWTYPE;
   1.297 +      "state_v"   "issue_state";
   1.298      BEGIN
   1.299 -      PERFORM "lock_issue"("issue_id_p");
   1.300 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.301 -      -- only process open issues:
   1.302 -      IF "issue_row"."closed" ISNULL THEN
   1.303 -        SELECT * INTO "policy_row" FROM "policy"
   1.304 -          WHERE "id" = "issue_row"."policy_id";
   1.305 -        -- create a snapshot, unless issue is already fully frozen:
   1.306 -        IF "issue_row"."fully_frozen" ISNULL THEN
   1.307 -          PERFORM "create_snapshot"("issue_id_p");
   1.308 -          "new_snapshot_v" := TRUE;
   1.309 -          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.310 -        ELSE
   1.311 -          "new_snapshot_v" := FALSE;
   1.312 +      IF "persist" ISNULL THEN
   1.313 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.314 +          FOR UPDATE;
   1.315 +        IF "issue_row"."closed" NOTNULL THEN
   1.316 +          RETURN NULL;
   1.317          END IF;
   1.318 -        -- eventually close or accept issues, which have not been accepted:
   1.319 -        IF "issue_row"."accepted" ISNULL THEN
   1.320 -          IF EXISTS (
   1.321 -            SELECT NULL FROM "initiative"
   1.322 -            WHERE "issue_id" = "issue_id_p"
   1.323 -            AND "supporter_count" > 0
   1.324 -            AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.325 -            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.326 -          ) THEN
   1.327 -            -- accept issues, if supporter count is high enough
   1.328 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.329 -            -- NOTE: "issue_row" used later
   1.330 -            "issue_row"."state" := 'discussion';
   1.331 -            "issue_row"."accepted" := now();
   1.332 -            UPDATE "issue" SET
   1.333 -              "state"    = "issue_row"."state",
   1.334 -              "accepted" = "issue_row"."accepted"
   1.335 -              WHERE "id" = "issue_row"."id";
   1.336 -          ELSIF
   1.337 -            now() >= "issue_row"."created" + "issue_row"."admission_time"
   1.338 -          THEN
   1.339 -            -- close issues, if admission time has expired
   1.340 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.341 -            UPDATE "issue" SET
   1.342 -              "state" = 'canceled_issue_not_accepted',
   1.343 -              "closed" = now()
   1.344 -              WHERE "id" = "issue_row"."id";
   1.345 -          END IF;
   1.346 +        "persist"."state" := "issue_row"."state";
   1.347 +        IF
   1.348 +          ( "issue_row"."state" = 'admission' AND now() >=
   1.349 +            "issue_row"."created" + "issue_row"."admission_time" ) OR
   1.350 +          ( "issue_row"."state" = 'discussion' AND now() >=
   1.351 +            "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
   1.352 +          ( "issue_row"."state" = 'verification' AND now() >=
   1.353 +            "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
   1.354 +          ( "issue_row"."state" = 'voting' AND now() >=
   1.355 +            "issue_row"."fully_frozen" + "issue_row"."voting_time" )
   1.356 +        THEN
   1.357 +          "persist"."phase_finished" := TRUE;
   1.358 +        ELSE
   1.359 +          "persist"."phase_finished" := FALSE;
   1.360          END IF;
   1.361 -        -- eventually half freeze issues:
   1.362          IF
   1.363 -          -- NOTE: issue can't be closed at this point, if it has been accepted
   1.364 -          "issue_row"."accepted" NOTNULL AND
   1.365 -          "issue_row"."half_frozen" ISNULL
   1.366 -        THEN
   1.367 -          IF
   1.368 -            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
   1.369 -          THEN
   1.370 -            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.371 -            -- NOTE: "issue_row" used later
   1.372 -            "issue_row"."state" := 'verification';
   1.373 -            "issue_row"."half_frozen" := now();
   1.374 -            UPDATE "issue" SET
   1.375 -              "state"       = "issue_row"."state",
   1.376 -              "half_frozen" = "issue_row"."half_frozen"
   1.377 -              WHERE "id" = "issue_row"."id";
   1.378 -          END IF;
   1.379 -        END IF;
   1.380 -        -- close issues after some time, if all initiatives have been revoked:
   1.381 -        IF
   1.382 -          "issue_row"."closed" ISNULL AND
   1.383            NOT EXISTS (
   1.384              -- all initiatives are revoked
   1.385              SELECT NULL FROM "initiative"
   1.386              WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.387            ) AND (
   1.388              -- and issue has not been accepted yet
   1.389 -            "issue_row"."accepted" ISNULL OR
   1.390 +            "persist"."state" = 'admission' OR
   1.391 +            -- or verification time has elapsed
   1.392 +            ( "persist"."state" = 'verification' AND
   1.393 +              "persist"."phase_finished" ) OR
   1.394 +            -- or no initiatives have been revoked lately
   1.395              NOT EXISTS (
   1.396 -              -- or no initiatives have been revoked lately
   1.397                SELECT NULL FROM "initiative"
   1.398                WHERE "issue_id" = "issue_id_p"
   1.399                AND now() < "revoked" + "issue_row"."verification_time"
   1.400 -            ) OR (
   1.401 -              -- or verification time has elapsed
   1.402 -              "issue_row"."half_frozen" NOTNULL AND
   1.403 -              "issue_row"."fully_frozen" ISNULL AND
   1.404 -              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
   1.405              )
   1.406            )
   1.407          THEN
   1.408 -          -- NOTE: "issue_row" used later
   1.409 -          IF "issue_row"."accepted" ISNULL THEN
   1.410 -            "issue_row"."state" := 'canceled_revoked_before_accepted';
   1.411 -          ELSIF "issue_row"."half_frozen" ISNULL THEN
   1.412 -            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
   1.413 -          ELSE
   1.414 -            "issue_row"."state" := 'canceled_after_revocation_during_verification';
   1.415 -          END IF;
   1.416 -          "issue_row"."closed" := now();
   1.417 -          UPDATE "issue" SET
   1.418 -            "state"  = "issue_row"."state",
   1.419 -            "closed" = "issue_row"."closed"
   1.420 -            WHERE "id" = "issue_row"."id";
   1.421 +          "persist"."issue_revoked" := TRUE;
   1.422 +        ELSE
   1.423 +          "persist"."issue_revoked" := FALSE;
   1.424          END IF;
   1.425 -        -- fully freeze issue after verification time:
   1.426 -        IF
   1.427 -          "issue_row"."half_frozen" NOTNULL AND
   1.428 -          "issue_row"."fully_frozen" ISNULL AND
   1.429 -          "issue_row"."closed" ISNULL AND
   1.430 -          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
   1.431 +        IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
   1.432 +          UPDATE "issue" SET "phase_finished" = now()
   1.433 +            WHERE "id" = "issue_row"."id";
   1.434 +          RETURN "persist";
   1.435 +        ELSIF
   1.436 +          "persist"."state" IN ('admission', 'discussion', 'verification')
   1.437          THEN
   1.438 -          PERFORM "freeze_after_snapshot"("issue_id_p");
   1.439 -          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
   1.440 -        END IF;
   1.441 -        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.442 -        -- close issue by calling close_voting(...) after voting time:
   1.443 -        IF
   1.444 -          "issue_row"."closed" ISNULL AND
   1.445 -          "issue_row"."fully_frozen" NOTNULL AND
   1.446 -          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
   1.447 -        THEN
   1.448 -          PERFORM "close_voting"("issue_id_p");
   1.449 -          -- calculate ranks will not consume much time and can be done now
   1.450 -          PERFORM "calculate_ranks"("issue_id_p");
   1.451 -        END IF;
   1.452 -        -- if a new shapshot has been created, then recalculate harmonic weights:
   1.453 -        IF "new_snapshot_v" THEN
   1.454 -          PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.455 +          RETURN "persist";
   1.456 +        ELSE
   1.457 +          RETURN NULL;
   1.458          END IF;
   1.459        END IF;
   1.460 -      RETURN;
   1.461 +      IF
   1.462 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
   1.463 +        coalesce("persist"."snapshot_created", FALSE) = FALSE
   1.464 +      THEN
   1.465 +        PERFORM "create_snapshot"("issue_id_p");
   1.466 +        "persist"."snapshot_created" = TRUE;
   1.467 +        IF "persist"."phase_finished" THEN
   1.468 +          IF "persist"."state" = 'admission' THEN
   1.469 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.470 +          ELSIF "persist"."state" = 'discussion' THEN
   1.471 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.472 +          ELSIF "persist"."state" = 'verification' THEN
   1.473 +            PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.474 +            PERFORM "initiative_admission"("issue_id_p");
   1.475 +          END IF;
   1.476 +        END IF;
   1.477 +        RETURN "persist";
   1.478 +      END IF;
   1.479 +      IF
   1.480 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
   1.481 +        coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
   1.482 +      THEN
   1.483 +        PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.484 +        "persist"."harmonic_weights_set" = TRUE;
   1.485 +        IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
   1.486 +          RETURN "persist";
   1.487 +        ELSE
   1.488 +          RETURN NULL;
   1.489 +        END IF;
   1.490 +      END IF;
   1.491 +      IF "persist"."state" = 'verification' AND "persist"."phase_finished" THEN
   1.492 +        PERFORM "freeze_after_snapshot"("issue_id_p");
   1.493 +        RETURN NULL;
   1.494 +      END IF;
   1.495 +      IF "persist"."issue_revoked" THEN
   1.496 +        IF "persist"."state" = 'admission' THEN
   1.497 +          "state_v" := 'canceled_revoked_before_accepted';
   1.498 +        ELSIF "persist"."state" = 'discussion' THEN
   1.499 +          "state_v" := 'canceled_after_revocation_during_discussion';
   1.500 +        ELSIF "persist"."state" = 'verification' THEN
   1.501 +          "state_v" := 'canceled_after_revocation_during_verification';
   1.502 +        END IF;
   1.503 +        UPDATE "issue" SET
   1.504 +          "state"          = "state_v",
   1.505 +          "closed"         = "phase_finished",
   1.506 +          "phase_finished" = NULL
   1.507 +          WHERE "id" = "issue_row"."id";
   1.508 +        RETURN NULL;
   1.509 +      END IF;
   1.510 +      IF "persist"."state" = 'admission' THEN
   1.511 +        PERFORM issue_admission("issue_id_p");
   1.512 +        RETURN NULL;
   1.513 +      END IF;
   1.514 +      IF
   1.515 +        "persist"."state" = 'voting' AND "persist"."phase_finished" AND
   1.516 +        coalesce("persist"."closed_voting", FALSE) = FALSE
   1.517 +      THEN
   1.518 +        PERFORM "close_voting"("issue_id_p");
   1.519 +        "persist"."closed_voting" = TRUE;
   1.520 +        RETURN "persist";
   1.521 +      END IF;
   1.522 +      IF "persist"."state" = 'voting' AND "persist"."phase_finished" THEN
   1.523 +        PERFORM "calculate_ranks"("issue_id_p");
   1.524 +        RETURN NULL;
   1.525 +      END IF;
   1.526 +      RAISE WARNING 'should not happen';
   1.527 +      RETURN NULL;
   1.528      END;
   1.529    $$;
   1.530  
   1.531  COMMENT ON FUNCTION "check_issue"
   1.532 -  ( "issue"."id"%TYPE )
   1.533 +  ( "issue"."id"%TYPE,
   1.534 +    "check_issue_persistence" )
   1.535    IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
   1.536  
   1.537  
   1.538 @@ -4299,15 +4377,17 @@
   1.539    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.540      DECLARE
   1.541        "issue_id_v" "issue"."id"%TYPE;
   1.542 +      "persist_v"  "check_issue_persistence";
   1.543      BEGIN
   1.544        DELETE FROM "expired_session";
   1.545        PERFORM "check_activity"();
   1.546        PERFORM "calculate_member_counts"();
   1.547        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
   1.548 -        PERFORM "check_issue"("issue_id_v");
   1.549 -      END LOOP;
   1.550 -      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
   1.551 -        PERFORM "calculate_ranks"("issue_id_v");
   1.552 +        "persist_v" := NULL;
   1.553 +        LOOP
   1.554 +          "persist_v" := "check_issue"("issue_id_v", "persist_v");
   1.555 +          EXIT WHEN "persist_v" ISNULL;
   1.556 +        END LOOP;
   1.557        END LOOP;
   1.558        RETURN;
   1.559      END;

Impressum / About Us