# HG changeset patch # User jbe # Date 1364040343 -3600 # Node ID e474e9e1240a04eb79da135c8a03c592b2a4b529 # Parent 1a5c4cc28d098888f6a7359a8b005b6fb3fcc181 Code cleanup regarding temporary storage of session/transaction data to disable protection triggers diff -r 1a5c4cc28d09 -r e474e9e1240a core.sql --- a/core.sql Fri Mar 22 22:46:51 2013 +0100 +++ b/core.sql Sat Mar 23 13:05:43 2013 +0100 @@ -7,7 +7,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) + SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) AS "subquery"("string", "major", "minor", "revision"); @@ -54,15 +54,15 @@ ------------------------- -CREATE TABLE "internal_session_store" ( - PRIMARY KEY ("backend_pid", "key"), - "backend_pid" INT4, +CREATE TABLE "temporary_transaction_data" ( + PRIMARY KEY ("txid", "key"), + "txid" INT8 DEFAULT txid_current(), "key" TEXT, "value" TEXT NOT NULL ); -COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; - -COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; +COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed'; + +COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table'; CREATE TABLE "system_setting" ( @@ -1599,8 +1599,8 @@ "issue_row" "issue"%ROWTYPE; BEGIN IF EXISTS ( - SELECT NULL FROM "internal_session_store" - WHERE "backend_pid" = pg_backend_pid() + SELECT NULL FROM "temporary_transaction_data" + WHERE "txid" = txid_current() AND "key" = 'override_protection_triggers' AND "value" = TRUE::TEXT ) THEN @@ -3671,9 +3671,8 @@ 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"; -- override protection triggers: - DELETE FROM "internal_session_store"; - INSERT INTO "internal_session_store" ("backend_pid", "key", "value") - VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); + INSERT INTO "temporary_transaction_data" ("key", "value") + VALUES ('override_protection_triggers', TRUE::TEXT); -- delete timestamp of voting comment: UPDATE "direct_voter" SET "comment_changed" = NULL WHERE "issue_id" = "issue_id_p"; @@ -3702,8 +3701,9 @@ UPDATE "direct_voter" SET "weight" = 1 WHERE "issue_id" = "issue_id_p"; PERFORM "add_vote_delegations"("issue_id_p"); - -- finish overriding protection triggers (mandatory, as pids may be reused): - DELETE FROM "internal_session_store"; + -- finish overriding protection triggers (avoids garbage): + DELETE FROM "temporary_transaction_data" + WHERE "key" = 'override_protection_triggers'; -- materialize battle_view: -- NOTE: "closed" column of issue must be set at this point DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; @@ -4344,17 +4344,14 @@ CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) RETURNS VOID LANGUAGE 'plpgsql' VOLATILE AS $$ - DECLARE - "issue_row" "issue"%ROWTYPE; BEGIN - SELECT * INTO "issue_row" - FROM "issue" WHERE "id" = "issue_id_p" - FOR UPDATE; - IF "issue_row"."cleaned" ISNULL THEN - UPDATE "issue" SET - "state" = 'voting', - "closed" = NULL - WHERE "id" = "issue_id_p"; + IF EXISTS ( + SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL + ) THEN + -- override protection triggers: + INSERT INTO "temporary_transaction_data" ("key", "value") + VALUES ('override_protection_triggers', TRUE::TEXT); + -- clean data: DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; DELETE FROM "direct_voter" @@ -4375,11 +4372,11 @@ USING "initiative" -- NOTE: due to missing index on issue_id WHERE "initiative"."issue_id" = "issue_id_p" AND "supporter"."initiative_id" = "initiative_id"; - UPDATE "issue" SET - "state" = "issue_row"."state", - "closed" = "issue_row"."closed", - "cleaned" = now() - WHERE "id" = "issue_id_p"; + -- mark issue as cleaned: + UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p"; + -- finish overriding protection triggers (avoids garbage): + DELETE FROM "temporary_transaction_data" + WHERE "key" = 'override_protection_triggers'; END IF; RETURN; END; @@ -4451,6 +4448,7 @@ RETURNS VOID LANGUAGE 'plpgsql' VOLATILE AS $$ BEGIN + DELETE FROM "temporary_transaction_data"; DELETE FROM "member" WHERE "activated" ISNULL; UPDATE "member" SET "invite_code" = NULL, diff -r 1a5c4cc28d09 -r e474e9e1240a update/core-update.v2.2.2-v2.2.3.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v2.2.2-v2.2.3.sql Sat Mar 23 13:05:43 2013 +0100 @@ -0,0 +1,243 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) + AS "subquery"("string", "major", "minor", "revision"); + +DROP TABLE "internal_session_store"; + +CREATE TABLE "temporary_transaction_data" ( + PRIMARY KEY ("txid", "key"), + "txid" INT8 DEFAULT txid_current(), + "key" TEXT, + "value" TEXT NOT NULL ); + +COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed'; + +COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table'; + +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() + RETURNS TRIGGER + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_id_v" "issue"."id"%TYPE; + "issue_row" "issue"%ROWTYPE; + BEGIN + IF EXISTS ( + SELECT NULL FROM "temporary_transaction_data" + WHERE "txid" = txid_current() + AND "key" = 'override_protection_triggers' + AND "value" = TRUE::TEXT + ) THEN + RETURN NULL; + END IF; + IF TG_OP = 'DELETE' THEN + "issue_id_v" := OLD."issue_id"; + ELSE + "issue_id_v" := NEW."issue_id"; + END IF; + SELECT INTO "issue_row" * FROM "issue" + WHERE "id" = "issue_id_v" FOR SHARE; + IF ( + "issue_row"."closed" NOTNULL OR ( + "issue_row"."state" = 'voting' AND + "issue_row"."phase_finished" NOTNULL + ) + ) THEN + IF + TG_RELID = 'direct_voter'::regclass AND + TG_OP = 'UPDATE' + THEN + IF + OLD."issue_id" = NEW."issue_id" AND + OLD."member_id" = NEW."member_id" AND + OLD."weight" = NEW."weight" + THEN + RETURN NULL; -- allows changing of voter comment + END IF; + END IF; + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; + END IF; + RETURN NULL; + END; + $$; + +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "area_id_v" "area"."id"%TYPE; + "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"; + -- override protection triggers: + INSERT INTO "temporary_transaction_data" ("key", "value") + VALUES ('override_protection_triggers', TRUE::TEXT); + -- delete timestamp of voting comment: + UPDATE "direct_voter" SET "comment_changed" = NULL + WHERE "issue_id" = "issue_id_p"; + -- delete delegating votes (in cases of manual reset of issue state): + DELETE FROM "delegating_voter" + WHERE "issue_id" = "issue_id_p"; + -- delete votes from non-privileged voters: + DELETE FROM "direct_voter" + USING ( + SELECT + "direct_voter"."member_id" + FROM "direct_voter" + JOIN "member" ON "direct_voter"."member_id" = "member"."id" + LEFT JOIN "privilege" + ON "privilege"."unit_id" = "unit_id_v" + AND "privilege"."member_id" = "direct_voter"."member_id" + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( + "member"."active" = FALSE OR + "privilege"."voting_right" ISNULL OR + "privilege"."voting_right" = FALSE + ) + ) AS "subquery" + WHERE "direct_voter"."issue_id" = "issue_id_p" + AND "direct_voter"."member_id" = "subquery"."member_id"; + -- consider delegations: + UPDATE "direct_voter" SET "weight" = 1 + WHERE "issue_id" = "issue_id_p"; + PERFORM "add_vote_delegations"("issue_id_p"); + -- finish overriding protection triggers (avoids garbage): + DELETE FROM "temporary_transaction_data" + WHERE "key" = 'override_protection_triggers'; + -- materialize battle_view: + -- NOTE: "closed" column of issue must be set at this point + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; + INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + ) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; + -- set voter count: + UPDATE "issue" SET + "voter_count" = ( + SELECT coalesce(sum("weight"), 0) + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" + ) + WHERE "id" = "issue_id_p"; + -- copy "positive_votes" and "negative_votes" from "battle" table: + UPDATE "initiative" SET + "positive_votes" = "battle_win"."count", + "negative_votes" = "battle_lose"."count" + FROM "battle" AS "battle_win", "battle" AS "battle_lose" + WHERE + "battle_win"."issue_id" = "issue_id_p" AND + "battle_win"."winning_initiative_id" = "initiative"."id" AND + "battle_win"."losing_initiative_id" ISNULL AND + "battle_lose"."issue_id" = "issue_id_p" AND + "battle_lose"."losing_initiative_id" = "initiative"."id" AND + "battle_lose"."winning_initiative_id" ISNULL; + END; + $$; + +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + IF EXISTS ( + SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL + ) THEN + -- override protection triggers: + INSERT INTO "temporary_transaction_data" ("key", "value") + VALUES ('override_protection_triggers', TRUE::TEXT); + -- clean data: + DELETE FROM "delegating_voter" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "direct_voter" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "delegating_interest_snapshot" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "direct_interest_snapshot" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "delegating_population_snapshot" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "direct_population_snapshot" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "non_voter" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "delegation" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "supporter" + USING "initiative" -- NOTE: due to missing index on issue_id + WHERE "initiative"."issue_id" = "issue_id_p" + AND "supporter"."initiative_id" = "initiative_id"; + -- mark issue as cleaned: + UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p"; + -- finish overriding protection triggers (avoids garbage): + DELETE FROM "temporary_transaction_data" + WHERE "key" = 'override_protection_triggers'; + END IF; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "delete_private_data"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + DELETE FROM "temporary_transaction_data"; + DELETE FROM "member" WHERE "activated" ISNULL; + UPDATE "member" SET + "invite_code" = NULL, + "invite_code_expiry" = NULL, + "admin_comment" = NULL, + "last_login" = NULL, + "login" = NULL, + "password" = NULL, + "lang" = NULL, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "notify_level" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "formatting_engine" = NULL, + "statement" = NULL; + -- "text_search_data" is updated by triggers + DELETE FROM "setting"; + DELETE FROM "setting_map"; + DELETE FROM "member_relation_setting"; + DELETE FROM "member_image"; + DELETE FROM "contact"; + DELETE FROM "ignored_member"; + DELETE FROM "session"; + DELETE FROM "area_setting"; + DELETE FROM "issue_setting"; + DELETE FROM "ignored_initiative"; + DELETE FROM "initiative_setting"; + DELETE FROM "suggestion_setting"; + DELETE FROM "non_voter"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL; + RETURN; + END; + $$; + +COMMIT;