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
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) {