Horton

Re: Skipping schema changes in publication

Shlok Kyal <shlok.kyal.oss@gmail.com>

View thread
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
To: vignesh C <vignesh21@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, YeXiu <1518981153@qq.com>, Ian Lawrence Barwick <barwick@gmail.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-09-26T18:53:58Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix miscellaneous issues in EXCEPT publication clause.

  2. Change syntax of EXCEPT TABLE clause in publication commands.

  3. Add support for EXCEPT TABLE in ALTER PUBLICATION.

  4. Allow table exclusions in publications via EXCEPT TABLE.

  5. Add wait_for_subscription_sync for TAP tests.

On Thu, 25 Sept 2025 at 14:18, vignesh C <vignesh21@gmail.com> wrote:
>
> On Fri, 5 Sept 2025 at 11:57, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
> >
> > On Mon, 25 Aug 2025 at 13:38, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
> > >
> > > On Thu, 21 Aug 2025 at 05:33, Peter Smith <smithpb2250@gmail.com> wrote:
> > > >
> > > > Hi Shlok,
> > > >
> > > > I reviewed your latest v20-0003 patch and have no more comments at
> > > > this time; I only found one trivial typo.
> > > >
> > > > ======
> > > > src/bin/psql/describe.c
> > > >
> > > > 1.
> > > > + /*
> > > > + * Footers entries for a publication description or a table
> > > > + * description
> > > > + */
> > > >
> > > > Typo. /Footers entries/Footer entries/
> > > >
> > >
> > > I have fixed it and attached the updated patches
> > >
> > The patches were not applying on HEAD and needed a Rebase. Here is the
> > rebased patches
>
> Consider the following scenario:
> create table t1(c1 int, c2 int);
> create publication pub1 for table t1 except (c1, c2);
>
> In this case, the publication is created in such a way that no columns
> are included, so effectively no data will be replicated to the
> subscriber.
> However, when attempting an UPDATE, the following error occurs:
> postgres=# update t1 set c1 = 2;
> ERROR:  cannot update table "t1" because it does not have a replica
> identity and publishes updates
> HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
>
> Is this behavior expected?

Hi Vignesh,

I think this behaviour is same as other similar cases like:

1. publication on empty table:
CREATE TABLE t1();
CREATE PUBLICATION pub1 FOR TABLE t1;

postgres=# DELETE FROM t1;
ERROR:  cannot delete from table "t1" because it does not have a
replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using
ALTER TABLE.

2. All the columns in a table is a generated column:
 CREATE TABLE t2(a int GENERATED ALWAYS AS (2*2) STORED);
CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish_generated_columns='none');

In this case since "publish_generated_columns=none", should not
publish changes for table t2. But we get following:
postgres=# DELETE FROM t2;
ERROR:  cannot delete from table "t2" because it does not have a
replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using
ALTER TABLE.

In above cases as well no columns are published but we have the similar error.
Given these behaviours in HEAD I think it is okay for EXCEPT
column_list to have the similar behaviour when all columns are
excluded. Thought?

Thanks,
Shlok Kyal