Re: Proposal: Conflict log history table for Logical Replication

On Wed, Dec 17, 2025 at 9:59 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Tue, Dec 16, 2025 at 10:33 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> > The OID check may be unreliable, as mentioned in the comment. I tested
> > this by dropping and recreating information_schema, and observed that
> > after recreation it became eligible for publication because its relid
> > no longer falls under FirstNormalObjectId. Steps:
> >
> > ****Pub****:
> > create publication pub1;
> > ALTER PUBLICATION pub1 ADD TABLE information_schema.sql_sizing;
> > select * from information_schema.sql_sizing where sizing_id=97;
> >
> > ****Sub****:
> > create subscription sub1 connection '...' publication pub1 with
> > (copy_data=false);
> > select * from information_schema.sql_sizing where sizing_id=97;
> >
> > ****Pub****:
> > alter table information_schema.sql_sizing replica identity full;
> > --this is not replicated.
> > UPDATE information_schema.sql_sizing set supported_value=12 where sizing_id=97;
> >
> > ****Sub****:
> > postgres=# select supported_value from information_schema.sql_sizing
> > where sizing_id=97;
> > supported_value
> > -----------------
> > 0
> >
> > ~~
> >
> > Then drop and recreate and try to perform the above update again, it
> > gets replicated:
> >
> > drop schema information_schema cascade;
> > ./psql -d postgres -f ./../../src/backend/catalog/information_schema.sql -p 5433
> >
> > ****Pub****:
> > ALTER PUBLICATION pub1 ADD TABLE information_schema.sql_sizing;
> > select * from information_schema.sql_sizing where sizing_id=97;
> > alter table information_schema.sql_sizing replica identity full;
> > --This is replicated
> > UPDATE information_schema.sql_sizing set supported_value=14 where sizing_id=97;
> >
> > ****Sub****:
> > --This shows supported_value as 14
> > postgres=# select supported_value from information_schema.sql_sizing
> > where sizing_id=97;
> > supported_value
> > -----------------
> > 14
>
> Hmm, I might be missing something what why we do not want to publish
> which is in information_shcema, especially when the internally created
> schema is dropped then user can create his own schema with name
> information-schema and create a bunch of tables in that so why do we
> want to block those? I mean the example you showed here is pretty
> much like a user created schema and table no? Or am I missing
> something important?
>

I don’t think a user intentionally dropping information_schema and
creating their own schema (with different definitions and tables) is a
practical scenario. While it isn’t explicitly restricted, I don’t see
a strong need for it. OTOH, there are scenarios where, after fixing
issues that affect the definition of information_schema on stable
branches, users may be asked to reload information_schema to apply the
updated definitions. One such case can be seen in [1].

Additionally, while reviewing the code, I noticed places where the
logic does not rely solely on relid being less than
FirstNormalObjectId. Instead, it performs name-based comparisons,
explicitly accounting for the possibility that information_schema may
have been dropped and reloaded. This further indicates that such
scenarios are considered practical. See [2].
And if such scenarios are possible, it might be worth considering
keeping the publish behavior consistent, both before and after a
reload of information_schema.

[1]:
https://www.postgresql.org/docs/9.1/release-9-1-2.html

[2]:
pg_upgrade has this:
static DataTypesUsageChecks data_types_usage_checks[] =
{
/*
* Look for composite types that were made during initdb *or* belong to
* information_schema; that's important in case information_schema was
* dropped and reloaded.
*
* The cutoff OID here should match the source cluster's value of
* FirstNormalObjectId. We hardcode it rather than using that C #define
* because, if that #define is ever changed, our own version's value is
* NOT what to use. Eventually we may need a test on the
source cluster's
* version to select the correct value.
*/
{
.status = gettext_noop("Checking for system-defined
composite types in user tables"),
.report_filename = "tables_using_composite.txt",
.base_query =
"SELECT t.oid FROM pg_catalog.pg_type t "
"LEFT JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid "
" WHERE typtype = 'c' AND (t.oid < 16384 OR nspname =
'information_schema')",

thanks
Shveta