libervia-pubsub: db/sat_pubsub_update_5

annotate db/sat_pubsub_update_5_7.sql @ 433:920440200570

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.

author	Goffi <goffi@goffi.org>
date	Thu, 14 Jan 2021 17:59:23 +0100
parents
children

rev	line source
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	1 -- we check version of the database before doing anything
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	2 -- and stop execution if not good
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	3 \set ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	4 DO $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	5 DECLARE ver text;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	6 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	7 SELECT value INTO ver FROM metadata WHERE key='version';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	8 IF NOT FOUND OR ver!='5' THEN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	10 END IF;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	11 END$$;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	12 \unset ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	13 -- end of version check
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	14
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	15 /* NOT NULL constraint was not applied to items.data */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	16 ALTER TABLE items ALTER COLUMN data SET NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	17
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	18 /* Full Text Search */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	19 ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	20 ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	21 ALTER TABLE items ADD COLUMN data_fts tsvector;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	22 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	23
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	24 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	25 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	26 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	27 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	28 WHERE items.node_id=new.node_id
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	29 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	30 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	31 END;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	32 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	33 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	34
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	35 CREATE TRIGGER nodes_fts_language_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	36 AFTER UPDATE OF fts_language ON nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	37 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	38 EXECUTE PROCEDURE update_data_fts_cfg();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	39
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	40 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	41 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	42 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	43 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	44 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	45 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	46 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	47 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	48
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	49 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	50 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	51 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	52 EXECUTE PROCEDURE update_data_fts();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	53
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	54 /* We do the update to trigger the data_fts generation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	55 UPDATE items SET data_fts_cfg='simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	56
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	57 /* we update nodes with schema to prepare for XEP-0346 implementation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	58
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	59 INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	60 SELECT 'fdp/template/'\|\|s.node, s.pep, true, s.publish_model, 1
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	61 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	62 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	63 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	64 WHERE schema IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	65 ) AS s;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	66
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	67 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	68 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	69 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	70 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	71 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	72 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	73 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	74 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	75 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	76
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	77 /* we need to do a similar request for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	78 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	79 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	80 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	81 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	82 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	83 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	84 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	85 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	86 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	87
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	88 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	89 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	90 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	91 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	92 e.jid\|\|'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	93 xmlelement(name item, xmlattributes('current' as id, e.jid\|\|'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	94 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	95 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	96 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	97 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	98 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	99 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	100 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	101 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	102
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	103 /* once again for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	104 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	105 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	106 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	107 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	108 e.jid\|\|'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	109 xmlelement(name item, xmlattributes('current' as id, e.jid\|\|'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	110 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	111 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	112 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	113 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	114 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	115 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	116 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	117 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	118
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	119 UPDATE nodes SET node='fdp/submitted/'\|\|node WHERE schema IS NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	120
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	121 UPDATE metadata SET value='7' WHERE key='version';

Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_5_7.sql @ 433:920440200570