Mercurial > libervia-pubsub

diff db/pubsub.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 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');