Re: [PATCH] Skip unpublishable child tables when adding parent to publication
| От | Arunprasad Rajkumar |
|---|---|
| Тема | Re: [PATCH] Skip unpublishable child tables when adding parent to publication |
| Дата | |
| Msg-id | CACDxuFwGv92kaMs4e8k_yFUuKMhCcn-rCfZgEm4TuiN8J-H1Zg@mail.gmail.com обсуждение исходный текст |
| Ответ на | RE: [PATCH] Skip unpublishable child tables when adding parent to publication ("Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>) |
| Список | pgsql-hackers |
Thank you, Zhijie and Euler, for your thoughts.
I’d like to compare the behavior of UNLOGGED, TEMP, and FOREIGN tables with PostgreSQL streaming (physical) replication. PostgreSQL sets a clear expectation there: these relations are not made available on the replica.
I think we could follow a similar approach for logical replication. Instead of disallowing users from indirectly adding unsupported relations (for example, via partitions or FOR ALL TABLES), we could emit a WARNING wherever possible and remove inconsistent behavior (e.g., performing an initial table sync for UNLOGGED tables) [1].
IMHO, disallowing users from adding an UNLOGGED or FOREIGN table to an existing partitioned table that is already part of a publication may break their business goals. In contrast, excluding such relations from replication—with clear warnings and documentation—would be less disruptive while still setting correct expectations.
BTW, trying to access a partitioned table with UNLOGGED table on read replica throws the following error!
I’d like to compare the behavior of UNLOGGED, TEMP, and FOREIGN tables with PostgreSQL streaming (physical) replication. PostgreSQL sets a clear expectation there: these relations are not made available on the replica.
I think we could follow a similar approach for logical replication. Instead of disallowing users from indirectly adding unsupported relations (for example, via partitions or FOR ALL TABLES), we could emit a WARNING wherever possible and remove inconsistent behavior (e.g., performing an initial table sync for UNLOGGED tables) [1].
IMHO, disallowing users from adding an UNLOGGED or FOREIGN table to an existing partitioned table that is already part of a publication may break their business goals. In contrast, excluding such relations from replication—with clear warnings and documentation—would be less disruptive while still setting correct expectations.
BTW, trying to access a partitioned table with UNLOGGED table on read replica throws the following error!
tsdb=> SELECT * FROM test_parted;
ERROR: cannot access temporary or unlogged relations during recovery
ERROR: cannot access temporary or unlogged relations during recovery
Regards,
Arun
On Tue, 16 Dec 2025 at 09:21, Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> wrote:
On Tuesday, December 16, 2025 7:28 AM Euler Taveira <euler@eulerto.com> wrote:
>
> On Mon, Dec 15, 2025, at 3:57 AM, Amit Kapila wrote:
> >
> > I think the unlogged table is afterwards silently ignored during
> > replication.
> >
>
> There is also the FOR ALL TABLES case. The manual says
>
> Marks the publication as one that replicates changes for all tables in the
> database, including tables created in the future.
>
> It says nothing about relation kind. This is an oversight. FOR TABLE and FOR
> TABLES IN SCHEMA mention about the unsupported relations. One suggestion
> is to
> avoid repeating the same sentence in each clause and add it to the command
> description. Maybe using a <note>...</note>.
>
> Regarding the FOR ALL TABLES behavior, should it prohibit creating/attaching
> a
> partition for an unsupported relation? Different from the FOR TABLE clause
> that
> you have a specified relation, in this case you don't one. It means you could
> have an error for regular commands (CREATE TABLE or ALTER TABLE ... SET
> UNLOGGED) if you simply have a publication with FOR ALL TABLES. This
> change
> might break routines that are working today and I think that is a bad idea. A
> reasonable solution is to ignore the unsupported objects. It means a
> partitioned table that has a single unlogged table as a partition will be
> ignored. It changes the current behavior to have "all or nothing" instead of
> "some". IMO it is easier to detect an issue if the partitioned table is empty
> then if there is just partial data in it.
>
> In summary, I think we should prohibit adding a partitioned table to a
> publication if there is any unsupported relation that is a partition of it. The
> FOR ALL TABLES ignores the partitioned table if there is any unsupported
> relation. Opinions?
I thought about implementing a rule within publication DDLs to prevent adding
partitioned tables with unsupported partitions to a publication. However, users
can still create problematic partitioned tables later using commands like ATTACH
PARTITION, CREATE PARTITION OF, or ALTER TABLE SET UNLOGGED. These commands are
similar to those that you identified in the FOR ALL TABLES scenario. This raises
uncertainty about how we should address these commands in the FOR single TABLE
scenario. Should we permit these user commands but restrict only adding
unsupported relation to publication, or should we apply restrictions across all
such commands? The former might lead to inconsistency with the FOR ALL TABLES
setting, where unsupported relations are silently ignored.
Best Regards,
Hou zj
В списке pgsql-hackers по дате отправления: