liquid_feedback_core

changeset 4:6133c0a62378 beta5

Version beta5

Precalculation of certain values to increase performance:
- Count of active members per area
- Count of active members altogether
- Count of people involved in the voting process for an issue

Minor bugfix in function close_voting(...) related to initiatives having no positive or negative votes
author jbe
date Sun Nov 15 12:00:00 2009 +0100 (2009-11-15)
parents 3da35844c874
children 1cbdd3975a61
files core.sql lf_update.c
line diff
     1.1 --- a/core.sql	Sun Nov 15 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Sun Nov 15 12:00:00 2009 +0100
     1.3 @@ -30,6 +30,14 @@
     1.4  COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
     1.5  
     1.6  
     1.7 +CREATE TABLE "member_count" (
     1.8 +        "count"                 INT4            NOT NULL );
     1.9 +
    1.10 +COMMENT ON TABLE "member_count" IS 'Holds a single value (single row, single column) of the total count of active(!) members, as calculated from "member_count_view"';
    1.11 +
    1.12 +COMMENT ON COLUMN "member_count"."count" IS 'Total count of active(!) members';
    1.13 +
    1.14 +
    1.15  CREATE TABLE "contact" (
    1.16          PRIMARY KEY ("member_id", "other_member_id"),
    1.17          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.18 @@ -91,12 +99,14 @@
    1.19          "id"                    SERIAL4         PRIMARY KEY,
    1.20          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.21          "name"                  TEXT            NOT NULL,
    1.22 -        "description"           TEXT            NOT NULL DEFAULT '' );
    1.23 +        "description"           TEXT            NOT NULL DEFAULT '',
    1.24 +        "member_count"          INT4 );
    1.25  CREATE INDEX "area_active_idx" ON "area" ("active");
    1.26  
    1.27  COMMENT ON TABLE "area" IS 'Subject areas';
    1.28  
    1.29 -COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
    1.30 +COMMENT ON COLUMN "area"."active"       IS 'TRUE means new issues can be created in this area';
    1.31 +COMMENT ON COLUMN "area"."member_count" IS 'Number of active members of that area, as calculated from view "area_member_count"';
    1.32  
    1.33  
    1.34  CREATE TABLE "issue" (
    1.35 @@ -113,6 +123,7 @@
    1.36          "population"            INT4,
    1.37          "vote_now"              INT4,
    1.38          "vote_later"            INT4,
    1.39 +        "voter_count"           INT4,
    1.40          CONSTRAINT "valid_state" CHECK (
    1.41            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.42            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.43 @@ -143,6 +154,7 @@
    1.44  COMMENT ON COLUMN "issue"."population"      IS 'Calculated from table "direct_population_snapshot"';
    1.45  COMMENT ON COLUMN "issue"."vote_now"        IS 'Calculated from table "direct_interest_snapshot"';
    1.46  COMMENT ON COLUMN "issue"."vote_later"      IS 'Calculated from table "direct_interest_snapshot"';
    1.47 +COMMENT ON COLUMN "issue"."voter_count"     IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    1.48  
    1.49  
    1.50  CREATE TABLE "initiative" (
    1.51 @@ -656,9 +668,9 @@
    1.52  
    1.53  
    1.54  
    1.55 ------------------------------------------------------------------------
    1.56 --- Automatic copy of autoreject settings from membership to interest --
    1.57 ------------------------------------------------------------------------
    1.58 +-----------------------------------------------------
    1.59 +-- Automatic calculation of certain default values --
    1.60 +-----------------------------------------------------
    1.61  
    1.62  CREATE FUNCTION "copy_autoreject_trigger"()
    1.63    RETURNS TRIGGER
    1.64 @@ -768,6 +780,25 @@
    1.65  -- Views and helper functions for views --
    1.66  ------------------------------------------
    1.67  
    1.68 +CREATE VIEW "member_count_view" AS
    1.69 +  SELECT count(1) FROM "member" WHERE "active";
    1.70 +
    1.71 +COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
    1.72 +
    1.73 +
    1.74 +CREATE VIEW "area_member_count" AS
    1.75 +  SELECT "area"."id" AS "area_id", count("member"."id")
    1.76 +  FROM "area"
    1.77 +  LEFT JOIN "membership"
    1.78 +  ON "area"."id" = "membership"."area_id"
    1.79 +  LEFT JOIN "member"
    1.80 +  ON "membership"."member_id" = "member"."id"
    1.81 +  AND "member"."active"
    1.82 +  GROUP BY "area"."id";
    1.83 +
    1.84 +COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
    1.85 +
    1.86 +
    1.87  CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS
    1.88    SELECT "delegation".*, "issue"."id" AS "resulting_issue_id"
    1.89    FROM "delegation"
    1.90 @@ -930,13 +961,15 @@
    1.91        -- NOTE: PostgreSQL allows reading, while tables are locked in
    1.92        -- exclusive move. Transactions should be kept short anyway!
    1.93        LOCK TABLE "member"     IN EXCLUSIVE MODE;
    1.94 +      LOCK TABLE "area"       IN EXCLUSIVE MODE;
    1.95 +      LOCK TABLE "membership" IN EXCLUSIVE MODE;
    1.96 +      -- NOTE: "member", "area" and "membership" are locked first to
    1.97 +      -- prevent deadlocks in combination with "calculate_member_counts"()
    1.98        LOCK TABLE "policy"     IN EXCLUSIVE MODE;
    1.99 -      LOCK TABLE "area"       IN EXCLUSIVE MODE;
   1.100        LOCK TABLE "issue"      IN EXCLUSIVE MODE;
   1.101        LOCK TABLE "initiative" IN EXCLUSIVE MODE;
   1.102        LOCK TABLE "draft"      IN EXCLUSIVE MODE;
   1.103        LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
   1.104 -      LOCK TABLE "membership" IN EXCLUSIVE MODE;
   1.105        LOCK TABLE "interest"   IN EXCLUSIVE MODE;
   1.106        LOCK TABLE "initiator"  IN EXCLUSIVE MODE;
   1.107        LOCK TABLE "supporter"  IN EXCLUSIVE MODE;
   1.108 @@ -958,6 +991,31 @@
   1.109  
   1.110  
   1.111  
   1.112 +-------------------------------
   1.113 +-- Materialize member counts --
   1.114 +-------------------------------
   1.115 +
   1.116 +CREATE FUNCTION "calculate_member_counts"()
   1.117 +  RETURNS VOID
   1.118 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.119 +    BEGIN
   1.120 +      LOCK TABLE "member"     IN EXCLUSIVE MODE;
   1.121 +      LOCK TABLE "area"       IN EXCLUSIVE MODE;
   1.122 +      LOCK TABLE "membership" IN EXCLUSIVE MODE;
   1.123 +      DELETE FROM "member_count";
   1.124 +      INSERT INTO "member_count" ("count")
   1.125 +        SELECT "count" FROM "member_count_view";
   1.126 +      UPDATE "area" SET "member_count" = "area_member_count"."count"
   1.127 +        FROM "area_member_count"
   1.128 +        WHERE "area_member_count"."area_id" = "area"."id";
   1.129 +      RETURN;
   1.130 +    END;
   1.131 +  $$;
   1.132 +
   1.133 +COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
   1.134 +
   1.135 +
   1.136 +
   1.137  ------------------------------
   1.138  -- Calculation of snapshots --
   1.139  ------------------------------
   1.140 @@ -1440,8 +1498,8 @@
   1.141          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.142        PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
   1.143        UPDATE "issue" SET
   1.144 -        "accepted"     = COALESCE("accepted", now()),
   1.145 -        "half_frozen"  = COALESCE("half_frozen", now()),
   1.146 +        "accepted"     = coalesce("accepted", now()),
   1.147 +        "half_frozen"  = coalesce("half_frozen", now()),
   1.148          "fully_frozen" = now()
   1.149          WHERE "id" = "issue_id_p";
   1.150        FOR "initiative_row" IN
   1.151 @@ -1640,17 +1698,28 @@
   1.152            FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.153        END LOOP;
   1.154        PERFORM "add_vote_delegations"("issue_id_p");
   1.155 +      UPDATE "issue" SET
   1.156 +        "voter_count" = (
   1.157 +          SELECT coalesce(sum("weight"), 0)
   1.158 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.159 +        );
   1.160        UPDATE "initiative" SET
   1.161          "positive_votes" = "subquery"."positive_votes",
   1.162          "negative_votes" = "subquery"."negative_votes"
   1.163          FROM (
   1.164            SELECT
   1.165              "initiative_id",
   1.166 -            sum(
   1.167 -              CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.168 +            coalesce(
   1.169 +              sum(
   1.170 +                CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.171 +              ),
   1.172 +              0
   1.173              ) AS "positive_votes",
   1.174 -            sum (
   1.175 -              CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   1.176 +            coalesce(
   1.177 +              sum(
   1.178 +                CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   1.179 +              ),
   1.180 +              0
   1.181              ) AS "negative_votes"
   1.182            FROM "vote" JOIN "direct_voter"
   1.183            ON "vote"."member_id" = "direct_voter"."member_id"
   1.184 @@ -2012,14 +2081,11 @@
   1.185        "issue_id_v" "issue"."id"%TYPE;
   1.186      BEGIN
   1.187        DELETE FROM "expired_session";
   1.188 -      FOR "issue_id_v" IN
   1.189 -        SELECT "id" FROM "open_issue"
   1.190 -      LOOP
   1.191 +      PERFORM "calculate_member_counts"();
   1.192 +      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
   1.193          PERFORM "check_issue"("issue_id_v");
   1.194        END LOOP;
   1.195 -      FOR "issue_id_v" IN
   1.196 -        SELECT "id" FROM "issue_with_ranks_missing"
   1.197 -      LOOP
   1.198 +      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
   1.199          PERFORM "calculate_ranks"("issue_id_v");
   1.200        END LOOP;
   1.201        RETURN;
     2.1 --- a/lf_update.c	Sun Nov 15 12:00:00 2009 +0100
     2.2 +++ b/lf_update.c	Sun Nov 15 12:00:00 2009 +0100
     2.3 @@ -67,6 +67,20 @@
     2.4      return 1;
     2.5    }
     2.6  
     2.7 +  // calculate member counts:
     2.8 +  status = PQexec(db, "SELECT \"calculate_member_counts\"()");
     2.9 +  if (!status) {
    2.10 +    fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
    2.11 +    return 1;
    2.12 +  }
    2.13 +  if (
    2.14 +    PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.15 +    PQresultStatus(status) != PGRES_TUPLES_OK
    2.16 +  ) {
    2.17 +    fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(status));
    2.18 +    return 1;
    2.19 +  }
    2.20 +
    2.21    // update open issues:
    2.22    list = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
    2.23    if (!list) {

Impressum / About Us