liquid_feedback_core
changeset 398:806561cce3b1
New table "issue_order" for ordering fields to avoid a bottleneck with locking
author | jbe |
---|---|
date | Fri Oct 11 20:07:41 2013 +0200 (2013-10-11) |
parents | 1e4fcb7f0eac |
children | db863178d98b |
files | core.sql lf_update_issue_order.c update/core-update.v2.2.5-v2.2.6.sql |
line diff
1.1 --- a/core.sql Fri Oct 11 13:42:16 2013 +0200 1.2 +++ b/core.sql Fri Oct 11 20:07:41 2013 +0200 1.3 @@ -539,8 +539,6 @@ 1.4 "population" INT4, 1.5 "voter_count" INT4, 1.6 "status_quo_schulze_rank" INT4, 1.7 - "order_in_admission_state" INT4, 1.8 - "order_in_open_states" INT4, 1.9 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK ( 1.10 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ), 1.11 CONSTRAINT "valid_state" CHECK ( 1.12 @@ -590,24 +588,33 @@ 1.13 1.14 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 1.15 1.16 -COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; 1.17 -COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set'; 1.18 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.19 -COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.20 -COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.21 -COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.22 -COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.23 -COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.24 -COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.25 -COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.26 -COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.27 -COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.28 -COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.29 -COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.30 -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.31 -COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.32 -COMMENT ON COLUMN "issue"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; 1.33 -COMMENT ON COLUMN "issue"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"'; 1.34 +COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; 1.35 +COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set'; 1.36 +COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.37 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.38 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.39 +COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.40 +COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.41 +COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.42 +COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.43 +COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.44 +COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.45 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.46 +COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.47 +COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.48 +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.49 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.50 + 1.51 + 1.52 +CREATE TABLE "issue_order" ( 1.53 + "id" INT8 PRIMARY KEY REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.54 + "order_in_admission_state" INT4, 1.55 + "order_in_open_states" INT4 ); 1.56 + 1.57 +COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once'; 1.58 + 1.59 +COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; 1.60 +COMMENT ON COLUMN "issue_order"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"'; 1.61 1.62 1.63 CREATE TABLE "issue_setting" ( 1.64 @@ -2087,7 +2094,7 @@ 1.65 "area_id", 1.66 "id" AS "issue_id", 1.67 "order_in_admission_state" * 2 - 1 AS "minimum_position" 1.68 - FROM "issue" 1.69 + FROM "issue" NATURAL LEFT JOIN "issue_order" 1.70 WHERE "closed" ISNULL 1.71 ORDER BY 1.72 coalesce(
2.1 --- a/lf_update_issue_order.c Fri Oct 11 13:42:16 2013 +0200 2.2 +++ b/lf_update_issue_order.c Fri Oct 11 20:07:41 2013 +0200 2.3 @@ -178,20 +178,20 @@ 2.4 PGresult *res; 2.5 char *cmd; 2.6 int i; 2.7 - if (asprintf(&cmd, "BEGIN; UPDATE \"issue\" SET \"order_in_admission_state\" = NULL WHERE \"area_id\" = %s AND (\"state\" = 'admission' OR \"order_in_admission_state\" NOTNULL)", escaped_area_id) < 0) { 2.8 + if (asprintf(&cmd, "BEGIN; DELETE FROM \"issue_order\" USING \"issue\" WHERE \"issue_order\".\"id\" = \"issue\".\"id\" AND \"issue\".\"area_id\" = %s", escaped_area_id) < 0) { 2.9 fprintf(stderr, "Could not prepare query string in memory.\n"); 2.10 abort(); 2.11 } 2.12 res = PQexec(db, cmd); 2.13 free(cmd); 2.14 if (!res) { 2.15 - fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue update.\n"); 2.16 + fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue order update.\n"); 2.17 return 1; 2.18 } else if ( 2.19 PQresultStatus(res) != PGRES_COMMAND_OK && 2.20 PQresultStatus(res) != PGRES_TUPLES_OK 2.21 ) { 2.22 - fprintf(stderr, "Error while executing SQL command to initiate issue update:\n%s", PQresultErrorMessage(res)); 2.23 + fprintf(stderr, "Error while executing SQL command to initiate issue order update:\n%s", PQresultErrorMessage(res)); 2.24 PQclear(res); 2.25 return 1; 2.26 } else { 2.27 @@ -204,7 +204,7 @@ 2.28 fprintf(stderr, "Could not escape literal in memory.\n"); 2.29 abort(); 2.30 } 2.31 - if (asprintf(&cmd, "UPDATE \"issue\" SET \"order_in_admission_state\" = %i WHERE \"id\" = %s", candidates[i].seat, escaped_issue_id) < 0) { 2.32 + if (asprintf(&cmd, "INSERT INTO \"issue_order\" (\"id\", \"order_in_admission_state\") VALUES (%i, %s)", candidates[i].seat, escaped_issue_id) < 0) { 2.33 fprintf(stderr, "Could not prepare query string in memory.\n"); 2.34 abort(); 2.35 } 2.36 @@ -212,12 +212,12 @@ 2.37 res = PQexec(db, cmd); 2.38 free(cmd); 2.39 if (!res) { 2.40 - fprintf(stderr, "Error in pqlib while sending SQL command to update issue order.\n"); 2.41 + fprintf(stderr, "Error in pqlib while sending SQL command to insert issue order.\n"); 2.42 } else if ( 2.43 PQresultStatus(res) != PGRES_COMMAND_OK && 2.44 PQresultStatus(res) != PGRES_TUPLES_OK 2.45 ) { 2.46 - fprintf(stderr, "Error while executing SQL command to update issue order:\n%s", PQresultErrorMessage(res)); 2.47 + fprintf(stderr, "Error while executing SQL command to insert issue order:\n%s", PQresultErrorMessage(res)); 2.48 PQclear(res); 2.49 } else { 2.50 PQclear(res);
3.1 --- a/update/core-update.v2.2.5-v2.2.6.sql Fri Oct 11 13:42:16 2013 +0200 3.2 +++ b/update/core-update.v2.2.5-v2.2.6.sql Fri Oct 11 20:07:41 2013 +0200 3.3 @@ -4,11 +4,15 @@ 3.4 SELECT * FROM (VALUES ('2.2.6', 2, 2, 6)) 3.5 AS "subquery"("string", "major", "minor", "revision"); 3.6 3.7 -ALTER TABLE "issue" ADD COLUMN "order_in_admission_state" INT4; 3.8 -ALTER TABLE "issue" ADD COLUMN "order_in_open_states" INT4; 3.9 +CREATE TABLE "issue_order" ( 3.10 + "id" INT8 PRIMARY KEY REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.11 + "order_in_admission_state" INT4, 3.12 + "order_in_open_states" INT4 ); 3.13 3.14 -COMMENT ON COLUMN "issue"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; 3.15 -COMMENT ON COLUMN "issue"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"'; 3.16 +COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once'; 3.17 + 3.18 +COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; 3.19 +COMMENT ON COLUMN "issue_order"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"'; 3.20 3.21 CREATE VIEW "issue_supporter_in_admission_state" AS 3.22 SELECT DISTINCT 3.23 @@ -31,7 +35,7 @@ 3.24 "area_id", 3.25 "id" AS "issue_id", 3.26 "order_in_admission_state" * 2 - 1 AS "minimum_position" 3.27 - FROM "issue" 3.28 + FROM "issue" NATURAL LEFT JOIN "issue_order" 3.29 WHERE "closed" ISNULL 3.30 ORDER BY 3.31 coalesce(