Re: Proposal: Conflict log history table for Logical Replication

On Wed, Sep 10, 2025 at 4:32 PM Alastair Turner <minion(at)decodable(dot)me> wrote:
>
>> Here we will have to create a built-in type of type table which is I
>> think typcategory => 'C' and if we create this type it should be
>> supplied with the "typrelid" that means there should be a backing
>> catalog table. At least thats what I think.
>
> A compound type can be used for building a table, it's not necessary to create a table when creating the type. In user SQL:
>
> CREATE TYPE conflict_log_type AS (
> conflictid UUID,
> subid OID,
> tableid OID,
> conflicttype TEXT,
> operationtype TEXT,
> replication_origin TEXT,
> remote_commit_ts TIMESTAMPTZ,
> local_commit_ts TIMESTAMPTZ,
> ri_key JSON,
> remote_tuple JSON,
> local_tuple JSON
> );
>
> CREATE TABLE my_subscription_conflicts OF conflict_log_type;

Problem is if you CREATE TYPE just before creating the table that
means subscription owners get full control over the type as well it
means they can alter the type itself. So logically this TYPE should
be a built-in type so that subscription owners do not have control to
ALTER the type but they have permission to create a table from this
type. But the problem is whenever you create a type it needs to have
corresponding relid in pg_class in fact you can just create a type as
per your example and see[1] it will get corresponding entry in
pg_class.

So the problem is if you create a user defined type it will be created
under the subscription owner and it defeats the purpose of not
allowing to alter the type OTOH if we create a built-in type it needs
to have a corresponding entry in pg_class.

So what's your proposal, create this type while creating a
subscription or as a built-in type, or anything else?

[1]
postgres[1948123]=# CREATE TYPE conflict_log_type AS (conflictid UUID);
postgres[1948123]=# select oid, typrelid, typcategory from pg_type
where typname='conflict_log_type';

oid | typrelid | typcategory
-------+----------+-------------
16386 | 16384 | C
(1 row)

postgres[1948123]=# select relname from pg_class where oid=16384;
relname
-------------------
conflict_log_type

--
Regards,
Dilip Kumar
Google