Mercurial > libervia-pubsub

comparison db/sat_pubsub_update_6_7.sql @ 433:920440200570

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
432:d9745fe5db46 433:920440200570
1 -- NOTE: this update is to be used only by people which have been installing the
2 -- 6th version of the schema. It is has been replaced because regconfig prevent
3 -- proper update (the GENERATED column with regconfig has been replaced by a
4 -- trigger). People which haven't installed 6th version can directly use the
5 -- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been
6 -- deleted has it's not needed anymore and can lead to troubles.
7
8 -- we check version of the database before doing anything
9 -- and stop execution if not good
10 \set ON_ERROR_STOP
11 DO $$
12 DECLARE ver text;
13 BEGIN
14 SELECT value INTO ver FROM metadata WHERE key='version';
15 IF NOT FOUND OR ver!='6' THEN
16 RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver;
17 END IF;
18 END$$;
19 \unset ON_ERROR_STOP
20 -- end of version check
21
22 /* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */
23 ALTER TABLE items DROP COLUMN data_fts;
24 ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text;
25 ALTER TABLE items ADD COLUMN data_fts tsvector;
26 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
27
28 ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg;
29 /* We don't use regconfig anymore in this method */
30 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
31 $$
32 BEGIN
33 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
34 WHERE items.node_id=new.node_id
35 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
36 RETURN new;
37 END;
38 $$
39 language plpgsql;
40
41 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
42 $$
43 BEGIN
44 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
45 RETURN new;
46 END
47 $$
48 language plpgsql;
49
50 CREATE TRIGGER items_fts_tsvector_update
51 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
52 FOR EACH ROW
53 EXECUTE PROCEDURE update_data_fts();
54
55 /* We do the update to trigger the data_fts generation */
56 UPDATE items SET data_fts_cfg='simple';
57
58 UPDATE metadata SET value='7' WHERE key='version';