Horton

Thread

  1. Replace is_publishable_class() with relispublishable column in pg_class

    Dilip Kumar <dilipbalaut@gmail.com> — 2025-12-16T15:49:21Z

    Hello hackers,

I would like to propose an improvement to the way PostgreSQL
determines if a relation is eligible for logical replication.
Currently, this check is performed dynamically via
is_publishable_class(), which relies on hard-coded OID checks and
relkind.  As noted in the existing comments atop
is_publishable_class[1], the long-term solution is to add a boolean
column to pg_class and depend on that instead of OID checks.

Motivation
========
1) The check is performed frequently in the logical decoding path
(e.g., in pgoutput_change and pgoutput_truncate). Moving this to a
cached catalog attribute in pg_class allows for a simple check.
2) As suggested by Amit Kpila [2], for the upcoming Conflict Log Table
feature, we need a clean way to exclude these internal conflict log
tables from publication. A catalog flag allows us to set this property
at relation creation rather than adding more special cases.

Proposed Changes
===============
The attached patch implements the following:
1) Catalog Update: Adds relispublishable (bool) to pg_class.
2) Creation Logic: Input parameter is added to
heap_create_with_catalog() so that caller can pass whether the
relation is publishable or not.
3) Update pgoutput and other relevant places to utilize the new
pg_class column directly.

[1]
The best
* long-term solution may be to add a "relispublishable" bool to pg_class,
* and depend on that instead of OID checks.
*/
static bool
is_publishable_class(Oid relid, Form_pg_class reltuple)
{
..
}

[2] https://www.postgresql.org/message-id/CAA4eK1K8Aqm%2BjP_EMPF8H_3UJSEExdwDCaphq6%3DunZZMdcmD0A%40mail.gmail.com

-- 
Regards,
Dilip Kumar
Google

  2. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Andres Freund <andres@anarazel.de> — 2025-12-16T16:28:11Z

    Hi,

On 2025-12-16 21:19:21 +0530, Dilip Kumar wrote:
> Motivation
> ========
> 1) The check is performed frequently in the logical decoding path
> (e.g., in pgoutput_change and pgoutput_truncate). Moving this to a
> cached catalog attribute in pg_class allows for a simple check.

You could solve this more resource-efficiently by putting the information in
the relcache entry.

> 2) As suggested by Amit Kpila [2], for the upcoming Conflict Log Table
> feature, we need a clean way to exclude these internal conflict log
> tables from publication. A catalog flag allows us to set this property
> at relation creation rather than adding more special cases.

Seems like the issue here also could be addressed the same way?

Greetings,

Andres Freund

  3. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Andres Freund <andres@anarazel.de> — 2025-12-16T17:45:17Z

    Hi,

On 2025-12-16 11:28:11 -0500, Andres Freund wrote:
> On 2025-12-16 21:19:21 +0530, Dilip Kumar wrote:
> > 2) As suggested by Amit Kpila [2], for the upcoming Conflict Log Table
> > feature, we need a clean way to exclude these internal conflict log
> > tables from publication. A catalog flag allows us to set this property
> > at relation creation rather than adding more special cases.
> 
> Seems like the issue here also could be addressed the same way?

Actually, wouldn't a table-level property be completely inappropriate for
that?  Imagine one publication that's used for HA (or major version upgrade)
and doesn't use a conflict table, which replicates all tables (including the
conflict table of another pub/sub). And a subscription doing bi-direction
replication that *does* obviously use the conflict table. In one of those
cases you want to replicate changes to the conflict table, in the other
not. So a table / pg_class property would be inappropriate, no?

Greetings,

Andres Freund

  4. Re: Replace is_publishable_class() with relispublishable column in pg_class

    shveta malik <shveta.malik@gmail.com> — 2025-12-17T03:44:11Z

    On Tue, Dec 16, 2025 at 9:58 PM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2025-12-16 21:19:21 +0530, Dilip Kumar wrote:
> > Motivation
> > ========
> > 1) The check is performed frequently in the logical decoding path
> > (e.g., in pgoutput_change and pgoutput_truncate). Moving this to a
> > cached catalog attribute in pg_class allows for a simple check.
>
> You could solve this more resource-efficiently by putting the information in
> the relcache entry.
>

I feel the  proposed solution (marking in pg_class) is required not
only to improve performance by avoiding OID checks in frequently
executed paths, but also to ensure consistent behavior when publishing
tables.

The existing approach does not provide a foolproof solution. As an
example, tables in information_schema are currently not eligible for
publication; but if information_schema is dropped and recreated, those
tables become eligible because their relid no longer falls under
FirstNormalObjectId (see steps in [1]).  There may be other
existing/future scenarios with similar behavior. In my opinion, the
proposed solution is a good approach to ensure consistency in such
cases.

[1]:
****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

thanks
Shveta

  5. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Dilip Kumar <dilipbalaut@gmail.com> — 2025-12-17T03:49:57Z

    On Tue, Dec 16, 2025 at 11:15 PM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2025-12-16 11:28:11 -0500, Andres Freund wrote:
> > On 2025-12-16 21:19:21 +0530, Dilip Kumar wrote:
> > > 2) As suggested by Amit Kpila [2], for the upcoming Conflict Log Table
> > > feature, we need a clean way to exclude these internal conflict log
> > > tables from publication. A catalog flag allows us to set this property
> > > at relation creation rather than adding more special cases.
> >
> > Seems like the issue here also could be addressed the same way?

Yeah we can do that, in fact the current patch already does that.  The
problem is the conflict log tables are created as regular tables and
in order to identify whether any table is used as a conflict log table
we need to scan the pg_subscription.  So in order to simplify this it
seems the table level property could be a better idea considering the
comments atop is_publishable_class() which says "the long-term
solution is to add a boolean
column to pg_class and depend on that instead of OID checks.".
Another motivation I am seeing is that we can extend this solution to
the SQL level syntax whether users can create tables by marking that
as non-publishable so that even when publication is created with ALL
TABLE options such tables can be excluded.

> Actually, wouldn't a table-level property be completely inappropriate for
> that?  Imagine one publication that's used for HA (or major version upgrade)
> and doesn't use a conflict table, which replicates all tables (including the
> conflict table of another pub/sub). And a subscription doing bi-direction
> replication that *does* obviously use the conflict table. In one of those
> cases you want to replicate changes to the conflict table, in the other
> not. So a table / pg_class property would be inappropriate, no?

Not sure I completely got this point,  but let me explain the
requirement, so the idea is that the conflict log table which are
created for the subscription if the conflict log table option is set
then all the conflict occurred on the node would be inserted into this
table, so you can consider this as a log which we want to use for
better lookup instead of looking into the server logs.  So IMHO this
table stores very node specific conflict information which might not
make any sense for other nodes.

-- 
Regards,
Dilip Kumar
Google

  6. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Andres Freund <andres@anarazel.de> — 2025-12-17T04:22:41Z

    Hi,

On 2025-12-17 09:19:57 +0530, Dilip Kumar wrote:
> On Tue, Dec 16, 2025 at 11:15 PM Andres Freund <andres@anarazel.de> wrote:
> > Actually, wouldn't a table-level property be completely inappropriate for
> > that?  Imagine one publication that's used for HA (or major version upgrade)
> > and doesn't use a conflict table, which replicates all tables (including the
> > conflict table of another pub/sub). And a subscription doing bi-direction
> > replication that *does* obviously use the conflict table. In one of those
> > cases you want to replicate changes to the conflict table, in the other
> > not. So a table / pg_class property would be inappropriate, no?
> 
> Not sure I completely got this point,  but let me explain the
> requirement, so the idea is that the conflict log table which are
> created for the subscription if the conflict log table option is set
> then all the conflict occurred on the node would be inserted into this
> table, so you can consider this as a log which we want to use for
> better lookup instead of looking into the server logs.  So IMHO this
> table stores very node specific conflict information which might not
> make any sense for other nodes.

Imagine you have a bi-directional replication setup between A <-> B. Then you
want to upgrade A to a new major version A'. To minimize downtime, you want to
use logical replication for that. For the logical replication A -> A', you
*would* want to logically replicate the conflict log table, because the
history of logical conflicts is actually important.

Greetings,

Andres Freund

  7. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Amit Kapila <amit.kapila16@gmail.com> — 2025-12-17T06:06:02Z

    On Wed, Dec 17, 2025 at 9:52 AM Andres Freund <andres@anarazel.de> wrote:
>
> On 2025-12-17 09:19:57 +0530, Dilip Kumar wrote:
> > On Tue, Dec 16, 2025 at 11:15 PM Andres Freund <andres@anarazel.de> wrote:
> > > Actually, wouldn't a table-level property be completely inappropriate for
> > > that?  Imagine one publication that's used for HA (or major version upgrade)
> > > and doesn't use a conflict table, which replicates all tables (including the
> > > conflict table of another pub/sub). And a subscription doing bi-direction
> > > replication that *does* obviously use the conflict table. In one of those
> > > cases you want to replicate changes to the conflict table, in the other
> > > not. So a table / pg_class property would be inappropriate, no?
> >
> > Not sure I completely got this point,  but let me explain the
> > requirement, so the idea is that the conflict log table which are
> > created for the subscription if the conflict log table option is set
> > then all the conflict occurred on the node would be inserted into this
> > table, so you can consider this as a log which we want to use for
> > better lookup instead of looking into the server logs.  So IMHO this
> > table stores very node specific conflict information which might not
> > make any sense for other nodes.
>
> Imagine you have a bi-directional replication setup between A <-> B. Then you
> want to upgrade A to a new major version A'. To minimize downtime, you want to
> use logical replication for that. For the logical replication A -> A', you
> *would* want to logically replicate the conflict log table, because the
> history of logical conflicts is actually important.
>

Yes, we need it for this case but still we need a way to distinguish
cases where we won't need to send conflict table changes for FOR ALL
TABLES publication (say a bi-directional replication set up). For
cases where user wants to allow conflict table changes to be
replicated, we want users to explicitly mention conflict_table in a
publication either as a publication option or explicitly as part of
FOR TABLE syntax, otherwise, its changes won't be replicated. So, to
ignore changes for other publications like FOR ALL TABLES, such a flag
(relispublishable) could be useful. The other way to identify whether
to replicate the changes in conflict table is as mentioned by Dilip
that for the first time when replicating a change for a table, we need
to scan pg_subscription to identify whether the current table is a
conflict log table and then cache it as relsync cache entry. We need
to do this because the conflict table is created as a regular table
along with subscriptions. Do you have any better ideas?

-- 
With Regards,
Amit Kapila.

  8. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Peter Smith <smithpb2250@gmail.com> — 2025-12-17T07:07:14Z

    Hi,

Here is a completely different idea. This may solve the immediate
problem re the replication of the Conflict Log Table (CLT) at least...

0. AFAIK, anything named with a "pg_" prefix generally means that the
named thing is intended for PG internal use, right?

1. So, the CLT should be automatically named to have a "pg_" prefix,
indicating that it really is an internal table, albeit not a system
catalog. IIRC, this kind of name was a recent suggestion anyhow.

2. Then, the CREATE PUBLICATION pub1 FOR ALL TABLES should be modified
to automatically *exclude* any tables having a "pg_" prefix.

3. But, if you *really* still want to publish the CLT, then you can do it:

3a) publish it explicitly
CREATE PUBLICATION pub1 FOR TABLE pg_clt_for_sub_1234

3b) invent a new option so that FOR ALL TABLES won't exclude it:
CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_internal_tables=true);

======
Kind Regards,
Peter Smith.
Fujitsu Australia.

  9. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Amit Kapila <amit.kapila16@gmail.com> — 2025-12-17T09:13:42Z

    On Wed, Dec 17, 2025 at 12:37 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Here is a completely different idea. This may solve the immediate
> problem re the replication of the Conflict Log Table (CLT) at least...
>
> 0. AFAIK, anything named with a "pg_" prefix generally means that the
> named thing is intended for PG internal use, right?
>
> 1. So, the CLT should be automatically named to have a "pg_" prefix,
> indicating that it really is an internal table, albeit not a system
> catalog. IIRC, this kind of name was a recent suggestion anyhow.
>

We would also like to give user a provision to provide
conflict_table|conflict_log name. In general also, I am not sure if
assuming this is a good idea as there could be some other internal
table if not now then in future where we would like to allow
replication by default.

-- 
With Regards,
Amit Kapila.

  10. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Amit Kapila <amit.kapila16@gmail.com> — 2025-12-17T12:23:56Z

    On Wed, Dec 17, 2025 at 12:37 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Here is a completely different idea. This may solve the immediate
> problem re the replication of the Conflict Log Table (CLT) at least...
>

Another idea could be that at the startup (pgoutput_startup), we can
form a conflict_table cache and then use it to determine whether to
send the change or not? Along with that we need to consider whether
publication explicitly indicates that a conflict_table changes needs
to be replicated or not.

-- 
With Regards,
Amit Kapila.

  11. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Peter Eisentraut <peter@eisentraut.org> — 2025-12-17T15:29:56Z

    On 17.12.25 08:07, Peter Smith wrote:
> 0. AFAIK, anything named with a "pg_" prefix generally means that the
> named thing is intended for PG internal use, right?

This might be a convention, but it's not enforced by the system, except 
for some object types like schemas and tablespaces.

  12. Re: Replace is_publishable_class() with relispublishable column in pg_class

    Amit Kapila <amit.kapila16@gmail.com> — 2025-12-18T10:40:33Z

    On Wed, Dec 17, 2025 at 5:53 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Dec 17, 2025 at 12:37 PM Peter Smith <smithpb2250@gmail.com> wrote:
> >
> > Here is a completely different idea. This may solve the immediate
> > problem re the replication of the Conflict Log Table (CLT) at least...
> >
>
> Another idea could be that at the startup (pgoutput_startup), we can
> form a conflict_table cache and then use it to determine whether to
> send the change or not? Along with that we need to consider whether
> publication explicitly indicates that a conflict_table changes needs
> to be replicated or not.
>

Yet another idea to cache catalog table information to avoid look up
overhead is to declare unique_index on dbid+cat_relid similar to
existing index SubscriptionNameIndexId. Then after the first time
looking up the entry for the catalog table, keep a corresponding flag
in the RelSyncCache table entry.

-- 
With Regards,
Amit Kapila.