PostgreSQL rejects INSERT ... ON CONFLICT ... DO UPDATE on any table
that has a conditional (WHERE) DO/DO ALSO rule or a non-NOTHING DO
INSTEAD rule, erroring with 'INSERT with ON CONFLICT clause cannot be
used with table that has INSERT or UPDATE rules'. Prosody 13's
mod_storage_sql uses ON CONFLICT upserts against the prosody kv table
whenever prosody_unique_index exists (created by these scripts), so the
five inherited conditional DO rules on prosody (cache_group_avatarids,
update_profile_queue_from_insert/_update, update_muc_remote_name,
update_room_nick_jid_map_remote) broke every kv upsert (vcard, vcard_muc,
muc_remote, config, fcmtoken, ...).
Replace those rules with AFTER INSERT / AFTER INSERT OR UPDATE
row-level triggers, which do not block ON CONFLICT. The two profile-queue
rules merge into one AFTER INSERT OR UPDATE trigger so the UPDATE branch
of an ON CONFLICT upsert is also covered (it fires AFTER UPDATE triggers,
not AFTER INSERT, when the conflict is taken).
Also drop the legacy 0.11.6 update_group_owners rule. It is logically
dead under 13.0.6 (fires on key='_affiliations', which is never written)
but PostgreSQL checks rule existence at plan time, so even a dead
conditional rule blocks ON CONFLICT. Dropping it is mandatory, not
optional as the README previously claimed.
Conversion is added to both prosody-13-new-deployment.sql (fresh
deployments, and the run_new_deployment branch of migrate.sh) and
prosody-13-rules-triggers.sql (the run_rules_triggers branch for
0.11.6->13.0 upgrades), so every helm pre/post-upgrade hook path
reaches the fix. Derived-table INSTEAD upsert rules are unchanged.
Verified against postgres:16: both scripts apply cleanly, pg_rewrite
for prosody returns 0 rows, ON CONFLICT upserts succeed, and triggers
fire on both INSERT and conflict-UPDATE branches; reproduced the
production error by re-adding the legacy rules, then confirmed the
incremental script resolves it. Idempotent re-run safe.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/10>
The pre-upgrade hook failed with "could not create unique index
prosody_unique_index" because the prosody table contained duplicate
rows (same host, user, store, key). Without the unique index,
mod_storage_sql falls back to SELECT-then-INSERT instead of ON
CONFLICT upsert, which races under concurrent writes and inserts
duplicates — most commonly in the fcmtoken map store.
Add a DELETE that removes duplicate rows (keeping the last-written
row per group via ctid ordering) immediately before the CREATE UNIQUE
INDEX in both prosody-13-new-deployment.sql and
prosody-13-migration-once.sql. The DELETE is a no-op when no
duplicates exist, preserving idempotency.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/8>
Prosody 13's mod_storage_sql upgrade path only warns when the index is
missing (it is created only on fresh tables). On DBs upgraded from 0.11.6
the table pre-existed, so the index was never created and has_upsert_index
stayed false, disabling ON CONFLICT upserts and degrading write performance.
Add an idempotent CREATE UNIQUE INDEX IF NOT EXISTS to both the migration
and new-deployment scripts.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/4>
The pre-upgrade hook failed with 'permission denied for table
pg_ts_config_map' because the migration runs as prosodyDBuser (a
non-superuser), and pg_ts_config_map is a PostgreSQL system catalog that
only superusers can DELETE from.
Replace the direct 'delete from pg_ts_config_map where ...' with the
equivalent non-superuser DDL 'ALTER TEXT SEARCH CONFIGURATION ... DROP
MAPPING IF EXISTS FOR <token types>', enumerating every token type the
block re-adds. Verified against postgres:15 as a non-superuser role: the
DELETE reproduces the exact error, DROP MAPPING IF EXISTS succeeds, and
the drop+re-add round-trip is idempotent across repeated runs.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/3>
Dockerfile: add postgresql-client and bake db-customization/ SQL scripts
into the image at /vnc/db-customization/.
config/migrate.sh: detection + migration script with two modes:
- pre-upgrade: runs idempotent prosody-13-new-deployment.sql on 13.0.x
databases; skips 0.11.6 (unsafe pre-upgrade) and new deployments.
- post-upgrade: waits for Prosody table, then runs the appropriate
scripts — full 0.11.6->13.0.6 migration (rules-triggers + migration-once
+ new-deployment) or idempotent drift correction for 13.0.x.
Helm chart: two Job templates (db-migration-pre-upgrade.yaml,
db-migration-post-upgrade.yaml) gated by dbMigration.enabled (default
true). Both reuse the Prosody image and DB credentials from existing
values. backoffLimit: 0, hook-delete-policy: hook-succeeded.
Also tracks the db-customization SQL files (previously untracked, now
referenced by the Dockerfile ADD).
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/3>
Adds a detection query set and decision tree to determine from the
database itself whether it is a new deployment, an unmigrated 0.11.6
database, an upgraded 0.11.6 database, or a clean 13.0 state — and which
script to run in each case.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/3>
Documents which SQL script to run for new deployments vs existing 0.11.6
database upgrades, ordering prerequisites, and what changed in the MUC
storage layout between 0.11.6 and 13.0.6.
Part-of: <http://gitlab.vnc.biz/uxf/vnctalk-prosody/-/merge_requests/3>
Stefan-Sanger