Mercurial > libervia-pubsub

comparison 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
comparison
equal deleted inserted replaced
432:d9745fe5db46 433:920440200570
64 item_id serial PRIMARY KEY, 64 item_id serial PRIMARY KEY,
65 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, 65 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
66 item text NOT NULL, 66 item text NOT NULL,
67 publisher text NOT NULL, 67 publisher text NOT NULL,
68 data xml NOT NULL, 68 data xml NOT NULL,
69 data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED, 69 data_fts tsvector,
70 data_fts_cfg regconfig NOT NULL DEFAULT 'simple', 70 data_fts_cfg text NOT NULL DEFAULT 'simple',
71 access_model text NOT NULL DEFAULT 'open' 71 access_model text NOT NULL DEFAULT 'open'
72 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), 72 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
73 created timestamp with time zone NOT NULL DEFAULT now(), 73 created timestamp with time zone NOT NULL DEFAULT now(),
74 updated timestamp with time zone NOT NULL DEFAULT now(), 74 updated timestamp with time zone NOT NULL DEFAULT now(),
75 UNIQUE (node_id, item) 75 UNIQUE (node_id, item)
104 ); 104 );
105 105
106 /* Full Text Search */ 106 /* Full Text Search */
107 CREATE INDEX items_data_fts ON items USING GIN (data_fts); 107 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
108 108
109 CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS 109 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
110 $$ 110 $$
111 BEGIN 111 BEGIN
112 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig 112 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
113 WHERE items.node_id=new.node_id 113 WHERE items.node_id=new.node_id
114 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id); 114 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
115 RETURN new; 115 RETURN new;
116 END; 116 END;
117 $$ 117 $$
118 language plpgsql; 118 language plpgsql;
119 119
120 CREATE TRIGGER nodes_fts_language_update 120 CREATE TRIGGER nodes_fts_language_update
121 AFTER UPDATE OF fts_language ON nodes 121 AFTER UPDATE OF fts_language ON nodes
122 FOR EACH ROW 122 FOR EACH ROW
123 EXECUTE PROCEDURE update_data_fts_cfg();
124
125 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
126 $$
127 BEGIN
128 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
129 RETURN new;
130 END
131 $$
132 language plpgsql;
133
134 CREATE TRIGGER items_fts_tsvector_update
135 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
136 FOR EACH ROW
123 EXECUTE PROCEDURE update_data_fts(); 137 EXECUTE PROCEDURE update_data_fts();
138
124 139
125 CREATE TABLE metadata ( 140 CREATE TABLE metadata (
126 key text PRIMARY KEY, 141 key text PRIMARY KEY,
127 value text 142 value text
128 ); 143 );
129 144
130 INSERT INTO metadata VALUES ('version', '5'); 145 INSERT INTO metadata VALUES ('version', '7');