Mercurial > libervia-pubsub
diff db/pubsub.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 | 5a0ada3b61ca |
children | b5e1e8d93dd4 |
line wrap: on
line diff
--- 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');