MIME-Version: 1.0
References: 
 <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
 <CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
 <CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
 <CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
 <CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
 <CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
 <CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
 <CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
 <CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
 <CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
 <CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
 <CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
 <CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
 <CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
 <CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
 <CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
 <CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
 <CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
 <CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
In-Reply-To: 
 <CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Mon, 1 Jun 2026 17:24:07 +1000
Message-ID: 
 <CAHut+PtkCS3TuVusC1yf0ZCcAci0NwAj3EwzNXXtnubeTn1+aQ@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPtkCS3TuVusC1yf0ZCcAci0NwAj3EwzNXXtnubeTn1%2BaQ%40mail.gmail.com>
Precedence: bulk

Hi Nisha.

Review comments for v8-0001 and v8-0002.

=3D=3D=3D=3D=3D=3D
git apply gives warnings.

1.
git apply ../patches_misc/v8-0001-Support-EXCEPT-clause-for-schema-level-pu=
blicatio.patch
../patches_misc/v8-0001-Support-EXCEPT-clause-for-schema-level-publicatio.p=
atch:176:
space before tab in indent.
                                         errmsg("relation \"%s\" is
already member of publication \"%s\"",
warning: 1 line adds whitespace errors.

=3D=3D=3D=3D=3D=3D
src/bin/psql/tab-complete.in.c

On Sat, May 30, 2026 at 2:32=E2=80=AFPM Nisha Moond <nisha.moond412@gmail.c=
om> wrote:
>
...
> > 9.
> > BTW, the current code is not able to handle multiple schemas.
> >
> > So, this works:
> > test_pub=3D# CREATE PUBLICATION pub1 for TABLES IN SCHEMA myschema <TAB=
>
> > EXCEPT ( TABLE  WITH (
> >
> > but, this doesn't do anything:
> > test_pub=3D# CREATE PUBLICATION pub1 for TABLES IN SCHEMA public, mysch=
ema <TAB>
> >
>
> I think the above preserves the existing behavior. Currently, we do
> not suggest "WITH (" after the second schema onwards. To support this
> properly, we would also need to handle "WITH (" suggestions for
> subsequent schema entries.
>
> I=E2=80=99ve created a top-up patch (patch-002) for this. I can merge it =
if we
> want to change the current behavior. Let me know your thoughts.

2.
Some scenarios are improved, but others do not work (either newly
broken or maybe they have been?).

TBH, I am unsure if the added complexity of patch 0002 was worth it. I
am going to pass on this for now and wait for other opinions.

e.g.

Good: (suggests schemas to use)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA
CURRENT_SCHEMA      information_schema  myschema            public

Bad: (does not suggest more schema to use)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA public, <TAB HERE=
>

Good: (completes names of known schema)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA my <TAB HERE>
becomes
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA myschema

Bad: (does not complete names of known schema)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA public, my <TAB H=
ERE>

Good: (suggest EXCEPT with single schema)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA myschema
EXCEPT ( TABLE  WITH (

Good: (suggest EXCEPT with multi schema)
test_pub=3D# CREATE PUBLICATION pub1 for TABLES IN SCHEMA public, myschema
EXCEPT ( TABLE  WITH (

Bad: (doesn't work if the FOR TABLE precedes TABLES IN SCHEMA)
test_pub=3D# CREATE PUBLICATION pub1 FOR TABLE mytab, TABLES IN <TAB HERE>

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