Mercurial > libervia-pubsub
comparison 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 |
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'); |