Mercurial > libervia-pubsub

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- NOTE: this update is to be used only by people which have been installing the
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- 6th version of the schema. It is has been replaced because regconfig prevent
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
3 -- proper update (the GENERATED column with regconfig has been replaced by a
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
4 -- trigger). People which haven't installed 6th version can directly use the
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
5 -- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
6 -- deleted has it's not needed anymore and can lead to troubles.
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
7
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
8 -- 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
9 -- and stop execution if not good
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
10 \set ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
11 DO $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
12 DECLARE ver text;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
13 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
14 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
15 IF NOT FOUND OR ver!='6' THEN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
16 RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
17 END IF;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
18 END$$;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
19 \unset ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
20 -- end of version check
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
21
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
22 /* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
23 ALTER TABLE items DROP COLUMN data_fts;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
24 ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
25 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
26 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
27
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
28 ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
29 /* We don't use regconfig anymore in this method */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
30 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
31 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
32 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
33 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
34 WHERE items.node_id=new.node_id
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
35 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
36 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
37 END;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
38 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
39 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
40
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
41 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
42 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
43 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
44 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
45 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
46 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
47 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
48 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
49
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
50 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
51 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
52 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
53 EXECUTE PROCEDURE update_data_fts();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
54
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
55 /* 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
56 UPDATE items SET data_fts_cfg='simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
57
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents:
diff changeset
58 UPDATE metadata SET value='7' WHERE key='version';