Thread
-
Re: Proposal: Conflict log history table for Logical Replication
shveta malik <shveta.malik@gmail.com> — 2026-05-07T04:31:37Z
On Thu, May 7, 2026 at 8:26 AM Dilip Kumar <dilipbalaut@gmail.com> wrote: > > On Wed, May 6, 2026 at 7:34 PM Dilip Kumar <dilipbalaut@gmail.com> wrote: > > > > On Wed, May 6, 2026 at 6:28 PM Dilip Kumar <dilipbalaut@gmail.com> wrote: > > > > > > On Wed, May 6, 2026 at 4:31 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > > > > > On Wed, May 6, 2026 at 3:01 PM shveta malik <shveta.malik@gmail.com> wrote: > > > > > > > > > > On Wed, May 6, 2026 at 9:24 AM vignesh C <vignesh21@gmail.com> wrote: > > > > > > > > > > > > Few comments: > > > > > > 1) Currently we allow renaming of pg_conflict schema, this might be ok > > > > > > as we allow other sysem schema like pg_catalog and pg_toast also. > > > > > > postgres=# alter schema pg_conflict rename to test_conflict; > > > > > > ALTER SCHEMA > > > > > > > > > > > > > > > > I agree that we allow renaming other schemas including pg_toast, but I > > > > > am not sure if this is consciously made decision, see BUG #18281 ast > > > > > [1]. I don't favour allowing renaming pg_conflict for 2 reasons: > > > > > > > > > > 1) Because Postgres explicitly blocks renaming schemas to a name > > > > > starting with 'pg_'. If an admin accidentally renames 'pg_conflict' to > > > > > something else, they are permanently locked out from renaming it back. > > > > > > > > > > 2) While the core worker might survive a rename via OID lookups; > > > > > external scripts, extensions, and monitoring tools will likely > > > > > hardcode the 'pg_conflict' string. If the schema is renamed, these > > > > > tools will fail. > > > > > > > > > > > > > I think we shouldn't go out of our way to disallow superusers to > > > > rename pg_conflict schema similar to other cases. We can try to > > > > prevent hard-coding schema names where possible but not sure we can > > > > guarantee that nothing related to pg_conflict schema won't break as > > > > shown by you in the following similar case for pg_conflict. > > > > > > > > > One such example of scripts breaking is present event in Postgres. I > > > > > did the following, and most of psql commands started failing after > > > > > that due to hard-coded pg_catalog name in them. > > > > > > > > > > postgres=# alter schema pg_catalog rename to catalog_new; > > > > > ALTER SCHEMA > > > > > > > > > > postgres=# \d catalog_new.* > > > > > ERROR: relation "pg_catalog.pg_class" does not exist > > > > > LINE 5: FROM pg_catalog.pg_class c > > > > > > > > > > [1]: https://www.postgresql.org/message-id/flat/18281-5b1b6c5991d345aa%40postgresql.org > > > > > > I can see pg_toast and pg_catalog schema also hard coded in couple of > > > places e.g. > > > > > > listPartitionedTables() > > > { > > > if (!pattern) > > > appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" > > > " AND n.nspname !~ '^pg_toast'\n" > > > " AND n.nspname <> 'information_schema'\n"); > > > } > > > > > > I will analyze which all places we are hardcoding, I think on server > > > side code we can easily avoid but from client side e.g. describe we > > > might need to invent a way to identify the schema name, or we might > > > have to store it somewhere in pg_subscription etc, I don't think we > > > should go that route. > > > > Here is updated patch set > > > > Open comments: > > 1. Analyze and avoid hardcoding the 'pg_conflict' schema name wherever possible > > 2. change the way we display clt in \dRs+ > > 3. Transfer the clt ownership when subscription ownership has change > > (Note: I have coded a poc for this but still checking whether it works > > in all cases) > > > > I will send the revised version by end of this week after fixing these > > open comments as well. > > So for the ownership change, this simple change[1] is working fine, > but there is another issue that currently we can assign subscription > nownership to any user even that doesn't have pg_create_subscription > maybe that should be fine as it is not creating the subscription but > now question is how to manage the permission on the conflict log table > see below test[2] > > > [1[] > diff --git a/src/backend/commands/subscriptioncmds.c > b/src/backend/commands/subscriptioncmds.c > index a2de57e17b4..c9fac56714e 100644 > --- a/src/backend/commands/subscriptioncmds.c > +++ b/src/backend/commands/subscriptioncmds.c > @@ -2718,6 +2718,10 @@ AlterSubscriptionOwner_internal(Relation rel, > HeapTuple tup, Oid newOwnerId) > form->subowner = newOwnerId; > CatalogTupleUpdate(rel, &tup->t_self, tup); > + /* Update owner of the conflict log table if it exists */ > + if (OidIsValid(form->subconflictlogrelid)) > + ATExecChangeOwner(form->subconflictlogrelid, > newOwnerId, true, AccessExclusiveLock); > + > /* Update owner dependency reference */ > changeDependencyOnOwner(SubscriptionRelationId, > form->oid, > > [2] > -- test to show the ownership is getting changed for the table, but > now this user will have access issue on the pg_conflict_log table as > this user do not have pg_create_subscription role, I haven't yet > checked whether the problems are only related to clt access or there > would be issue for other subcription management as well. > > postgres[557253]=# SELECT relname, relowner FROM pg_class WHERE > relname = 'pg_conflict_log_16406'; > relname | relowner > -----------------------+---------- > pg_conflict_log_16406 | 10 > (1 row) > > postgres[557253]=# CREATE USER test; > CREATE ROLE > postgres[557253]=# ALTER SUBSCRIPTION sub OWNER TO test; > ALTER SUBSCRIPTION > postgres[557253]=# SELECT relname, relowner FROM pg_class WHERE > relname = 'pg_conflict_log_16406'; > relname | relowner > -----------------------+---------- > pg_conflict_log_16406 | 16410 > (1 row) > During my testing, I initally found it strange that user without pg_create_subscription is allowed to perform ALTER Sub. But that is base/head behaviour. Now coming to our use-case around it. postgres=# create user user1; CREATE ROLE postgres=# ALTER SUBSCRIPTION sub1 OWNER TO user1; ALTER SUBSCRIPTION postgres=# SELECT relowner::regrole FROM pg_class WHERE relname = 'pg_conflict_log_16392'; relowner ---------- user1 As Dilip stated, user1 owns the table but cannot access or truncate it. postgres=> select * from pg_conflict.pg_conflict_log_16392; ERROR: permission denied for schema pg_conflict postgres=> truncate pg_conflict.pg_conflict_log_16392; ERROR: permission denied for schema pg_conflict It looks weird at first, but I think we have exact same beahviour for toast table: --as superuser: postgres=# CREATE TABLE user_data (id int, big_text text); CREATE TABLE postgres=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'user_data'; reltoastrelid ------------------------- pg_toast.pg_toast_16399 postgres=# SELECT * FROM pg_toast.pg_toast_16399; chunk_id | chunk_seq | chunk_data ----------+-----------+------------ (0 rows) postgres=# alter table user_data owner to user1; ALTER TABLE --toast table ownership got changed: postgres=# \dt+ pg_toast.pg_toast_16399 Schema | Name | Type | Owner | ----------+----------------+-------------+-------+- pg_toast | pg_toast_16399 | TOAST table | user1 | As user1: postgres=> SELECT * FROM pg_toast.pg_toast_16399; ERROR: permission denied for schema pg_toast So behaviour is similar to our case. IMO, at best we can document it well, something like: Note: Conflict log tables reside in the restricted pg_conflict schema. To query or truncate these logs, a user must be a superuser or have the pg_create_subscription privilege. A subscription owner lacking these privileges will not be able to access or purge conflict log tables. Thoughts? thanks Shveta