# HG changeset patch # User Goffi # Date 1610643563 -3600 # Node ID 9204402005701b3b2165b5c6bd15a2258bdb92bf # Parent d9745fe5db46eb931adae1702f3a992693e6ad00 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore /!\ pgsql schema needs to be updated /!\ /!\ Minimal PostgreSQL required version is back to 9.5 /!\ `regconfig` is using system table, and `pg_upgrade` can't handle that, causing trouble when moving to a new major version of PostgreSQL. To work around this, the `data_fts_cfg` column type in `items` has been changed from `regconfig` to `text`. GENERATED column can't be used with type casting to `regconfig`, so the data_fts column is now generated with a trigger. As a result, the minimal requirement of PostgreSQL 12 is not necessary anymore. diff -r d9745fe5db46 -r 920440200570 db/pubsub.sql --- a/db/pubsub.sql Fri Dec 11 17:19:00 2020 +0100 +++ b/db/pubsub.sql Thu Jan 14 17:59:23 2021 +0100 @@ -66,8 +66,8 @@ item text NOT NULL, publisher text NOT NULL, data xml NOT NULL, - data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED, - data_fts_cfg regconfig NOT NULL DEFAULT 'simple', + data_fts tsvector, + data_fts_cfg text NOT NULL DEFAULT 'simple', access_model text NOT NULL DEFAULT 'open' CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), created timestamp with time zone NOT NULL DEFAULT now(), @@ -106,10 +106,10 @@ /* Full Text Search */ CREATE INDEX items_data_fts ON items USING GIN (data_fts); -CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS +CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS $$ BEGIN - UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig + UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') WHERE items.node_id=new.node_id AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); RETURN new; @@ -120,11 +120,26 @@ CREATE TRIGGER nodes_fts_language_update AFTER UPDATE OF fts_language ON nodes FOR EACH ROW + EXECUTE PROCEDURE update_data_fts_cfg(); + +CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS +$$ +BEGIN + new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); + RETURN new; +END +$$ +language plpgsql; + +CREATE TRIGGER items_fts_tsvector_update + BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items + FOR EACH ROW EXECUTE PROCEDURE update_data_fts(); + CREATE TABLE metadata ( key text PRIMARY KEY, value text ); -INSERT INTO metadata VALUES ('version', '5'); +INSERT INTO metadata VALUES ('version', '7'); diff -r d9745fe5db46 -r 920440200570 db/sat_pubsub_update_5_6.sql --- a/db/sat_pubsub_update_5_6.sql Fri Dec 11 17:19:00 2020 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,104 +0,0 @@ --- we check version of the database before doing anything --- and stop execution if not good -\set ON_ERROR_STOP -DO $$ -DECLARE ver text; -BEGIN - SELECT value INTO ver FROM metadata WHERE key='version'; - IF NOT FOUND OR ver!='5' THEN - RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver; - END IF; -END$$; -\unset ON_ERROR_STOP --- end of version check - -/* NOT NULL constraint was not applied to items.data */ -ALTER TABLE items ALTER COLUMN data SET NOT NULL; - -/* Full Text Search */ -ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic'; -ALTER TABLE items ADD COLUMN data_fts_cfg regconfig NOT NULL DEFAULT 'simple'; -ALTER TABLE items ADD COLUMN data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED; -CREATE INDEX items_data_fts ON items USING GIN (data_fts); - -CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS -$$ -BEGIN - UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig - WHERE items.node_id=new.node_id - AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); - RETURN new; -END; -$$ -language plpgsql; - -CREATE TRIGGER nodes_fts_language_update - AFTER UPDATE OF fts_language ON nodes - FOR EACH ROW - EXECUTE PROCEDURE update_data_fts(); - -/* we update nodes with schema to prepare for XEP-0346 implementation */ - -INSERT INTO nodes(node, pep, persist_items, publish_model, max_items) - SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1 - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL - ) AS s; - -INSERT INTO affiliations(entity_id, node_id, affiliation) - SELECT aff.entity_id, tpl.node_id, 'owner' - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NOT NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep - LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; - -/* we need to do a similar request for non PEP nodes */ -INSERT INTO affiliations(entity_id, node_id, affiliation) - SELECT aff.entity_id, tpl.node_id, 'owner' - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL - LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; - -INSERT INTO items(node_id, item, publisher, data) - SELECT - tpl.node_id, - 'current', - e.jid||'/generated', - xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NOT NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep - LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' - LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; - -/* once again for non PEP nodes */ -INSERT INTO items(node_id, item, publisher, data) - SELECT - tpl.node_id, - 'current', - e.jid||'/generated', - xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) - FROM ( - SELECT node_id, node, pep, publish_model, schema - FROM nodes - WHERE schema IS NOT NULL AND pep IS NULL - ) AS s - LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL - LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' - LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; - -UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL; - -UPDATE metadata SET value='6' WHERE key='version'; diff -r d9745fe5db46 -r 920440200570 db/sat_pubsub_update_5_7.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/sat_pubsub_update_5_7.sql Thu Jan 14 17:59:23 2021 +0100 @@ -0,0 +1,121 @@ +-- we check version of the database before doing anything +-- and stop execution if not good +\set ON_ERROR_STOP +DO $$ +DECLARE ver text; +BEGIN + SELECT value INTO ver FROM metadata WHERE key='version'; + IF NOT FOUND OR ver!='5' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* NOT NULL constraint was not applied to items.data */ +ALTER TABLE items ALTER COLUMN data SET NOT NULL; + +/* Full Text Search */ +ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic'; +ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple'; +ALTER TABLE items ADD COLUMN data_fts tsvector; +CREATE INDEX items_data_fts ON items USING GIN (data_fts); + +CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS +$$ +BEGIN + UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') + WHERE items.node_id=new.node_id + AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); + RETURN new; +END; +$$ +language plpgsql; + +CREATE TRIGGER nodes_fts_language_update + AFTER UPDATE OF fts_language ON nodes + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts_cfg(); + +CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS +$$ +BEGIN + new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); + RETURN new; +END +$$ +language plpgsql; + +CREATE TRIGGER items_fts_tsvector_update + BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts(); + +/* We do the update to trigger the data_fts generation */ +UPDATE items SET data_fts_cfg='simple'; + +/* we update nodes with schema to prepare for XEP-0346 implementation */ + +INSERT INTO nodes(node, pep, persist_items, publish_model, max_items) + SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1 + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL + ) AS s; + +INSERT INTO affiliations(entity_id, node_id, affiliation) + SELECT aff.entity_id, tpl.node_id, 'owner' + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NOT NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep + LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; + +/* we need to do a similar request for non PEP nodes */ +INSERT INTO affiliations(entity_id, node_id, affiliation) + SELECT aff.entity_id, tpl.node_id, 'owner' + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL + LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner'; + +INSERT INTO items(node_id, item, publisher, data) + SELECT + tpl.node_id, + 'current', + e.jid||'/generated', + xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NOT NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep + LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' + LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; + +/* once again for non PEP nodes */ +INSERT INTO items(node_id, item, publisher, data) + SELECT + tpl.node_id, + 'current', + e.jid||'/generated', + xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema) + FROM ( + SELECT node_id, node, pep, publish_model, schema + FROM nodes + WHERE schema IS NOT NULL AND pep IS NULL + ) AS s + LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL + LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner' + LEFT JOIN entities AS e ON e.entity_id = aff.entity_id; + +UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL; + +UPDATE metadata SET value='7' WHERE key='version'; diff -r d9745fe5db46 -r 920440200570 db/sat_pubsub_update_6_7.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db/sat_pubsub_update_6_7.sql Thu Jan 14 17:59:23 2021 +0100 @@ -0,0 +1,58 @@ +-- NOTE: this update is to be used only by people which have been installing the +-- 6th version of the schema. It is has been replaced because regconfig prevent +-- proper update (the GENERATED column with regconfig has been replaced by a +-- trigger). People which haven't installed 6th version can directly use the +-- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been +-- deleted has it's not needed anymore and can lead to troubles. + +-- we check version of the database before doing anything +-- and stop execution if not good +\set ON_ERROR_STOP +DO $$ +DECLARE ver text; +BEGIN + SELECT value INTO ver FROM metadata WHERE key='version'; + IF NOT FOUND OR ver!='6' THEN + RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver; + END IF; +END$$; +\unset ON_ERROR_STOP +-- end of version check + +/* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */ +ALTER TABLE items DROP COLUMN data_fts; +ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text; +ALTER TABLE items ADD COLUMN data_fts tsvector; +CREATE INDEX items_data_fts ON items USING GIN (data_fts); + +ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg; +/* We don't use regconfig anymore in this method */ +CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS +$$ +BEGIN + UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple') + WHERE items.node_id=new.node_id + AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); + RETURN new; +END; +$$ +language plpgsql; + +CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS +$$ +BEGIN + new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text); + RETURN new; +END +$$ +language plpgsql; + +CREATE TRIGGER items_fts_tsvector_update + BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items + FOR EACH ROW + EXECUTE PROCEDURE update_data_fts(); + +/* We do the update to trigger the data_fts generation */ +UPDATE items SET data_fts_cfg='simple'; + +UPDATE metadata SET value='7' WHERE key='version'; diff -r d9745fe5db46 -r 920440200570 sat_pubsub/pgsql_storage.py --- a/sat_pubsub/pgsql_storage.py Fri Dec 11 17:19:00 2020 +0100 +++ b/sat_pubsub/pgsql_storage.py Thu Jan 14 17:59:23 2021 +0100 @@ -79,7 +79,7 @@ parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8')) ITEMS_SEQ_NAME = 'node_{node_id}_seq' PEP_COL_NAME = 'pep' -CURRENT_VERSION = '6' +CURRENT_VERSION = '7' # retrieve the maximum integer item id + 1 NEXT_ITEM_ID_QUERY = r"SELECT COALESCE(max(item::integer)+1,1) as val from items where node_id={node_id} and item ~ E'^\\d+$'"