--- 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');

--- a/db/sat_pubsub_update_5_6.sql	Fri Dec 11 17:19:00 2020 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,104 +0,0 @@
--- we check version of the database before doing anything
--- and stop execution if not good
-\set ON_ERROR_STOP
-DO $$
-DECLARE ver text;
-BEGIN
-    SELECT value INTO ver FROM metadata WHERE key='version';
-    IF NOT FOUND OR ver!='5' THEN
-        RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
-    END IF;
-END$$;
-\unset ON_ERROR_STOP
--- end of version check
-
-/* NOT NULL constraint was not applied to items.data */
-ALTER TABLE items ALTER COLUMN data SET NOT NULL;
-
-/* Full Text Search */
-ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
-ALTER TABLE items ADD COLUMN data_fts_cfg regconfig NOT NULL DEFAULT 'simple';
-ALTER TABLE items ADD COLUMN data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED;
-CREATE INDEX items_data_fts ON items USING GIN (data_fts);
-
-CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS
-$$
-BEGIN
-    UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig
-        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;
-END;
-$$
-language plpgsql;
-
-CREATE TRIGGER nodes_fts_language_update
-     AFTER UPDATE OF fts_language ON nodes
-     FOR EACH ROW
-     EXECUTE PROCEDURE update_data_fts();
-
-/* we update nodes with schema to prepare for XEP-0346 implementation */
-
-INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
-    SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
-    FROM (
-        SELECT node_id, node, pep, publish_model, schema
-        FROM nodes
-        WHERE schema IS NOT NULL
-    ) AS s;
-
-INSERT INTO affiliations(entity_id, node_id, affiliation)
-    SELECT aff.entity_id, tpl.node_id, 'owner'
-    FROM (
-        SELECT node_id, node, pep, publish_model, schema
-        FROM nodes
-        WHERE schema IS NOT NULL AND pep IS NOT NULL
-    ) AS s
-    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
-    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
-
-/* we need to do a similar request for non PEP nodes */
-INSERT INTO affiliations(entity_id, node_id, affiliation)
-    SELECT aff.entity_id, tpl.node_id, 'owner'
-    FROM (
-        SELECT node_id, node, pep, publish_model, schema
-        FROM nodes
-        WHERE schema IS NOT NULL AND pep IS NULL
-    ) AS s
-    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
-    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
-
-INSERT INTO items(node_id, item, publisher, data)
-    SELECT
-        tpl.node_id,
-        'current',
-        e.jid||'/generated',
-        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
-    FROM (
-        SELECT node_id, node, pep, publish_model, schema
-        FROM nodes
-        WHERE schema IS NOT NULL AND pep IS NOT NULL
-    ) AS s
-    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
-    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
-    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
-
-/* once again for non PEP nodes */
-INSERT INTO items(node_id, item, publisher, data)
-    SELECT
-        tpl.node_id,
-        'current',
-        e.jid||'/generated',
-        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
-    FROM (
-        SELECT node_id, node, pep, publish_model, schema
-        FROM nodes
-        WHERE schema IS NOT NULL AND pep IS NULL
-    ) AS s
-    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
-    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
-    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
-
-UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
-
-UPDATE metadata SET value='6' WHERE key='version';

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/sat_pubsub_update_5_7.sql	Thu Jan 14 17:59:23 2021 +0100
@@ -0,0 +1,121 @@
+-- we check version of the database before doing anything
+-- and stop execution if not good
+\set ON_ERROR_STOP
+DO $$
+DECLARE ver text;
+BEGIN
+    SELECT value INTO ver FROM metadata WHERE key='version';
+    IF NOT FOUND OR ver!='5' THEN
+        RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+/* NOT NULL constraint was not applied to items.data */
+ALTER TABLE items ALTER COLUMN data SET NOT NULL;
+
+/* Full Text Search */
+ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
+ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple';
+ALTER TABLE items ADD COLUMN data_fts tsvector;
+CREATE INDEX items_data_fts ON items USING GIN (data_fts);
+
+CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
+$$
+BEGIN
+    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;
+END;
+$$
+language plpgsql;
+
+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();
+
+/* We do the update to trigger the data_fts generation */
+UPDATE items SET data_fts_cfg='simple';
+
+/* we update nodes with schema to prepare for XEP-0346 implementation */
+
+INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
+    SELECT 'fdp/template/'||s.node, s.pep, true, s.publish_model, 1
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL
+    ) AS s;
+
+INSERT INTO affiliations(entity_id, node_id, affiliation)
+    SELECT aff.entity_id, tpl.node_id, 'owner'
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NOT NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
+    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
+
+/* we need to do a similar request for non PEP nodes */
+INSERT INTO affiliations(entity_id, node_id, affiliation)
+    SELECT aff.entity_id, tpl.node_id, 'owner'
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
+    LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
+
+INSERT INTO items(node_id, item, publisher, data)
+    SELECT
+        tpl.node_id,
+        'current',
+        e.jid||'/generated',
+        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NOT NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep=s.pep
+    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
+    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
+
+/* once again for non PEP nodes */
+INSERT INTO items(node_id, item, publisher, data)
+    SELECT
+        tpl.node_id,
+        'current',
+        e.jid||'/generated',
+        xmlelement(name item, xmlattributes('current' as id, e.jid||'/generated' as publisher), s.schema)
+    FROM (
+        SELECT node_id, node, pep, publish_model, schema
+        FROM nodes
+        WHERE schema IS NOT NULL AND pep IS NULL
+    ) AS s
+    LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'||s.node AND tpl.pep IS NULL
+    LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
+    LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
+
+UPDATE nodes SET node='fdp/submitted/'||node WHERE schema IS NOT NULL;
+
+UPDATE metadata SET value='7' WHERE key='version';

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/sat_pubsub_update_6_7.sql	Thu Jan 14 17:59:23 2021 +0100
@@ -0,0 +1,58 @@
+-- NOTE: this update is to be used only by people which have been installing the
+-- 6th version of the schema. It is has been replaced because regconfig prevent
+-- proper update (the GENERATED column with regconfig has been replaced by a
+-- trigger). People which haven't installed 6th version can directly use the
+-- sat_pubsub_update_5_7.sql file. The sat_pubsub_update_5_6.sql has been
+-- deleted has it's not needed anymore and can lead to troubles.
+
+-- we check version of the database before doing anything
+-- and stop execution if not good
+\set ON_ERROR_STOP
+DO $$
+DECLARE ver text;
+BEGIN
+    SELECT value INTO ver FROM metadata WHERE key='version';
+    IF NOT FOUND OR ver!='6' THEN
+        RAISE EXCEPTION 'This update file needs to be applied on database schema version 6, you use version %',ver;
+    END IF;
+END$$;
+\unset ON_ERROR_STOP
+-- end of version check
+
+/* regconfig type is not usable when doing database upgrade (for new PostgreSQL major version) */
+ALTER TABLE items DROP COLUMN data_fts;
+ALTER TABLE items ALTER COLUMN data_fts_cfg TYPE text;
+ALTER TABLE items ADD COLUMN data_fts tsvector;
+CREATE INDEX items_data_fts ON items USING GIN (data_fts);
+
+ALTER FUNCTION update_data_fts() RENAME TO update_data_fts_cfg;
+/* We don't use regconfig anymore in this method */
+CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
+$$
+BEGIN
+    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;
+END;
+$$
+language plpgsql;
+
+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();
+
+/* We do the update to trigger the data_fts generation */
+UPDATE items SET data_fts_cfg='simple';
+
+UPDATE metadata SET value='7' WHERE key='version';

--- a/sat_pubsub/pgsql_storage.py	Fri Dec 11 17:19:00 2020 +0100
+++ b/sat_pubsub/pgsql_storage.py	Thu Jan 14 17:59:23 2021 +0100
@@ -79,7 +79,7 @@
 parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8'))
 ITEMS_SEQ_NAME = 'node_{node_id}_seq'
 PEP_COL_NAME = 'pep'
-CURRENT_VERSION = '6'
+CURRENT_VERSION = '7'
 # retrieve the maximum integer item id + 1
 NEXT_ITEM_ID_QUERY = r"SELECT COALESCE(max(item::integer)+1,1) as val from items where node_id={node_id} and item ~ E'^\\d+$'"