public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: Peter Smith <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: YeXiu <[email protected]>
Cc: Ian Lawrence Barwick <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Skipping schema changes in publication
Date: Tue, 11 Nov 2025 15:54:29 +0530
Message-ID: <CANhcyEUmEpYXgizXd0BPehPKSzQg3n4zkiy5rbb5+hx4dbjybw@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
References: <CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com>
<CALj2ACVOzhs+BD+abFV2x4oKJdsDNd6SgsE7r8UjnZDCKGEckA@mail.gmail.com>
<CAA4eK1K6Kr88d2S0zFdHRMyuoaZeNh+ktU+oigmCuD09_x_-+g@mail.gmail.com>
<CAHut+PsvC-NezO3MJkdyEz=G1QRje2LntjwhQiEeVbmhOQuBMA@mail.gmail.com>
<CALDaNm18VH2j8cTqfELHQ=0ZNognbGBhbHPteJenWQC6C2dueQ@mail.gmail.com>
<CALDaNm0k_0Ccj47wzJzzPFwgQB7w=R5+Q2_nSqYrmMmjhmcRUw@mail.gmail.com>
<CAHut+Pv_0DwyWoGQNMF+G2AGqMuJTzWQKRtmxaC+=zLTPL-Zkw@mail.gmail.com>
<CALDaNm2-GJt2HsYTkLqQ=ecm=R-vOBw1=aM_d2EiYbz39x_cTQ@mail.gmail.com>
<TYCPR01MB8373C3120C2B3112001ED6F1EDCF9@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0iZZDB300Dez_97S8G6_RW5QpQ8ef6X3wq8tyK-8wnXQ@mail.gmail.com>
<CAHut+PtiomM+iyAZHvb2dzfsPvRru266KuBe49hKy2n2h+m_zA@mail.gmail.com>
<CALDaNm30KDnwX4Czi29fqLb8JBkuwqjbpj9ixwNXXox574NZqQ@mail.gmail.com>
<CALDaNm1PfKRJsEzbKpyt=v4p3bw+_SzE+LFPsMhR5X+qs+0pPw@mail.gmail.com>
<TYCPR01MB83730A2F1D6A5303E9C1416AEDD99@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0sAU4s1KTLOEWv=rYo5dQK6uFTJn_0FKj3XG1Nv4D-qw@mail.gmail.com>
<CALDaNm3CLRa95tpas6tEj8x58MUNDShxBNoYS+P8Uq5cryoAOw@mail.gmail.com>
<CALDaNm0EKC3o=v+F7GneGibuCULGKkBWXmNaVB4GR9HoqD066A@mail.gmail.com>
<CALDaNm1Z1Rmqj9s6P9ZzmrVA9F_vZ_DwwhYAJmsjqmY6dS3-hA@mail.gmail.com>
<CAB8KJ=jJGuW=ozKmXZzKDUHZ_-J2ZYGOtJo=i2cnNbSu6=KuYg@mail.gmail.com>
<CALDaNm1mbFP8fxHU_H1Ex4cT2Aq3n8FE79tq0TO5ThvFnDUYMA@mail.gmail.com>
<CAB8KJ=jq4RwTs8K7pokmXQwQppP2ChVJLMSAdXaxAX+c1r+mdg@mail.gmail.com>
<CALDaNm1mJvLni8GODebKBmyegXuZ18bLoG-Pz6H1MCX=vphCYA@mail.gmail.com>
<CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com>
<CALDaNm1AQZYgT0tALRrkvpP1Q+8+e7vkGCUjQ-jim1C0q3e=zA@mail.gmail.com>
<CAA4eK1KRdAPC=5=7tQ1GW0cRwD=zaDMi+T4u_k4GxPhPY6e8BQ@mail.gmail.com>
<OS3PR01MB5718C8BE84B862E7E0CEC29B94BD2@OS3PR01MB5718.jpnprd01.prod.outlook.com>
<CAA4eK1KYQz7cf46_D=6VkZ4J6Y8vJ88MMi=6zm2TJXDP+V1mLg@mail.gmail.com>
<CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@mail.gmail.com>
<CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com>
<CAHut+PuiaLOCkiAx9nPnjk6wTbPFvnm9T5svTuKbgwJwTdea8w@mail.gmail.com>
<CANhcyEV_MePxgftHY65et1WdOAk70M0C7PZ1STPUO8PXHVB1YA@mail.gmail.com>
<CAHut+Ps0hSNqrjv_jT1AuXxO-CrZue3ixE0jKsxVhtArMrkujQ@mail.gmail.com>
<CANhcyEXX3viVpYcGHD_fzhf_f6CDQWr2+VBywrJf5zm_XiB4tg@mail.gmail.com>
<CAHut+PsXP_61ZXuVOx5u9FZGK3oH4taaA59oOzgqyygZx8ezWw@mail.gmail.com>
<CANhcyEU+aPu6iAH2cTA0cDtn3pd6c_njBONCt3FubYZoEEnm8Q@mail.gmail.com>
<CAHut+Pv2P6dJ7hZj_fmzN+=xzjvpOpgkAJvDZg3TD2xpvmY1NQ@mail.gmail.com>
<CANhcyEW2LK4diNeCG862DE40yQoV3VAgf59kXUq2TuR8fnw5vQ@mail.gmail.com>
<CAHut+PuSHScrODVGCM7P53Mv1HE2N6ThzkH4+gQ1eFXVeD-OCA@mail.gmail.com>
<CANhcyEUtYV-9ujtxLasnxN_peT+3LuZjcRx1xUECh1CCmANB8w@mail.gmail.com>
<CAHut+PuviFA6C7qps=+kDYfe3P99as8NCjbR=SYxoi0o96ipoA@mail.gmail.com>
<CANhcyEXkeg3sjkS3DS9yU1ckz4ozUBNZ+RmrWaRNSSVCR8RquA@mail.gmail.com>
<CAHut+PsHavMy_KJ0MwR9J6q0BTTty54TxS-KBZc7X6tb4u7rfA@mail.gmail.com>
<CANhcyEU=k4+0BqOu25N76g738XKUwfLGGdf8e+ssGiRKHC4RwQ@mail.gmail.com>
<CAHut+Ptdi8txJbMc+5Sp2uB4QLGOp-rZdkBbXyhAkxim0iAhBQ@mail.gmail.com>
<CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com>
<CAHut+Ptq0-tMYUOvG3yR34AvuEzR9vUH=muqV_=uEO3zCuA6rA@mail.gmail.com>
<CANhcyEUEMWSkTfGc7Q3B+UiOzSiOmOGLgK-+C5DXwtCGOnDBhg@mail.gmail.com>
<CAHut+PuAgSDr3kbSxPMYEyCeGiJ5hgaT1JUvuiYPRT=Q+--O-Q@mail.gmail.com>
<CANhcyEU_uuiKMRrd_E_DeYsyCvwY_u995E-Do3d66J7tQnzdzw@mail.gmail.com>
<CANhcyEWK9sj9UY4uaV36Q8qxUv=8Joch0o98RCN5U3xLjUxAag@mail.gmail.com>
<CALDaNm32XQDR4qsOhPQeophVbZ8r+ShJSSssoVfdPcwG6joPHQ@mail.gmail.com>
<CANhcyEVt2CBnG7MOktaPPV4rYapHR-VHe5=qoziTZh1L9SVc6w@mail.gmail.com>
<CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
On Thu, 30 Oct 2025 at 11:34, Peter Smith <[email protected]> wrote:
>
> Hi Vignesh
>
> Here are some review comments for the patch v24-0002.
>
> These comments are just for the SGML docs. The patch needs a rebase so
> I was unable to review the code.
>
> ======
> Commit message
>
> 1.
> A new column "prexcept" is added to table "pg_publication_rel", to maintain
> the relations that the user wants to exclude from the publications.
>
> ~
>
> /to maintain/to flag/
>
> ======
> doc/src/sgml/logical-replication.sgml
>
> 2.
> <para>
> - To add tables to a publication, the user must have ownership rights on the
> - table. To add all tables in schema to a publication, the user must be a
> - superuser. To create a publication that publishes all tables or
> all tables in
> - schema automatically, the user must be a superuser.
> + To create a publication using FOR ALL TABLES or FOR ALL TABLES IN SCHEMA,
> + the user must be a superuser. To add ALL TABLES or ALL TABLES IN SCHEMA to a
> + publication, the user must be a superuser. To add tables to a publication,
> + the user must have ownership rights on the table.
> </para>
>
> Those "FOR ALL TABLES" etc are missing SGML markup.
>
> ======
> doc/src/sgml/ref/alter_publication.sgml
>
> 3.
> +ALTER PUBLICATION <replaceable class="parameter">name</replaceable>
> ADD ALL TABLES [ EXCEPT [ TABLE ] <replaceable
> class="parameter">exception_object</replaceable> [, ... ] ]
>
> and
>
> +
> +<phrase>where <replaceable
> class="parameter">exception_object</replaceable> is:</phrase>
> +
> + [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
> +
>
> It is not clear from the syntax which of these is possible.
>
> ... ADD ALL TABLES EXCEPT TABLE t1,t2,t3
> ... ADD ALL TABLES EXCEPT TABLE t1, TABLE t2, TABLES t3
>
> IMO it is best put the "[TABLE]" within the exception_object:
> [ TABLE ] [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> Then both are possible, which is consistent with how "FOR TABLE" syntax works.
>
> Furthermore, you might want later to say EXCLUDE SEQUENCE, so doing it
> this way makes that possible.
>
Recently a commit was pushed which allowed use of ALL SEQUENCES
syntax. Due to it, I have updated the syntax to
CREATE PUBLICATION ... ALL TABLES EXCEPT TABLE(t1, t2, t3);
For ALTER PUBLICATION to have a similar syntax. I have updated it to
have syntax like:
ALTER PUBLICATION ... ADD ALL TABLES EXCEPT TABLE(t1, t2, t3);
I think in the future we can extend the syntax for sequences like:
ALL SEQUENCES EXCEPT(s1, s2, s3).
See [1] for more info.
> ~~~
>
> 4.
> - Adding a table to a publication additionally requires owning that table.
> - The <literal>ADD TABLES IN SCHEMA</literal>,
> + Adding a table to or excluding a table from a publication additionally
> + requires owning that table. The <literal>ADD ALL TABLES</literal>,
>
> This wording seems a bit awkward. How are re-phrasing like:
>
> SUGGESTION
> Adding or excluding a table from a publication requires ownership of that table.
>
> ~~~
>
> 5.
> - name to explicitly indicate that descendant tables are included.
> + name to explicitly indicate that descendant tables are affected. For
> + partitioned tables, <literal>ONLY</literal> donot have any effect.
>
> typo: /donot/does not/
>
> ======
> doc/src/sgml/ref/create_publication.sgml
>
> 6.
> - [ FOR ALL TABLES
> + [ FOR ALL TABLES [ EXCEPT [ TABLE ] <replaceable
> class="parameter">exception_object</replaceable> [, ... ] ]
> | FOR <replaceable
> class="parameter">publication_object</replaceable> [, ... ] ]
> [ WITH ( <replaceable
> class="parameter">publication_parameter</replaceable> [= <replaceable
> class="parameter">value</replaceable>] [, ... ] ) ]
>
> @@ -30,6 +30,10 @@ CREATE PUBLICATION <replaceable
> class="parameter">name</replaceable>
>
> TABLE [ ONLY ] <replaceable
> class="parameter">table_name</replaceable> [ * ] [ ( <replaceable
> class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE (
> <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
> TABLES IN SCHEMA { <replaceable
> class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...
> ]
> +
> +<phrase>where <replaceable
> class="parameter">exception_object</replaceable> is:</phrase>
> +
> + [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> Same review comment as #3 before.
>
> I think it is clearer (and more flexible) to change the
> exception_object to include [TABLE].
> [ TABLE ] [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
>
> It also helps pave the way for any future EXCLUDE SEQUENCE feature.
>
See #3 before
.
> ~~~
>
> 7.
> + <para>
> + This clause specifies a list of tables to be excluded from the
> + publication. It can only be used with <literal>FOR ALL TABLES</literal>.
> + If <literal>ONLY</literal> is specified before the table name, only
> + that table is excluded from the publication. If
> <literal>ONLY</literal> is
> + not specified, the table and all its descendant tables (if any) are
> + excluded. Optionally, <literal>*</literal> can be specified after the
> + table name to explicitly indicate that descendant tables are excluded.
> + This does not apply to a partitioned table, however. The partitioned
> + table or its partitions are excluded from the publication based on the
> + parameter <literal>publish_via_partition_root</literal>.
> + </para>
> + <para>
> + When <literal>publish_via_partition_root</literal> is set to
> + <literal>true</literal>, specifying a root partitioned table in
> + <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
> + replication. Specifying a leaf partition has no effect, as its
> changes are
> + still replicated via the root partitioned table. When
> + <literal>publish_via_partition_root</literal> is set to
> + <literal>false</literal>, specifying a partitioned table or non-leaf
> + partition has no effect, as changes are replicated via the leaf
> + partitions. Specifying a leaf partition excludes only that partition from
> + replication.
> + </para>
>
> I felt that the second paragraph should be started with the sentence
> "The partitioned table or its partitions are excluded...", so then
> everything related to "publish_via_partition_root" is kept together.
>
> ~~~
>
> 8.
> + <para>
> + Create a publication that publishes all changes in all the tables except for
> + the changes of <structname>users</structname> and
> + <structname>departments</structname>:
> +<programlisting>
> +CREATE PUBLICATION mypublication FOR ALL TABLES EXCEPT users, departments;
> +</programlisting>
> + </para>
>
> The words "the changes of" are not needed, and you did not use that
> wording in the ALTER PUBLICATION example.
>
> ======
> doc/src/sgml/ref/psql-ref.sgml
>
> 9.
> If <literal>x</literal> is appended to the command name, the results
> are displayed in expanded mode.
> - If <literal>+</literal> is appended to the command name, the tables and
> - schemas associated with each publication are shown as well.
> + If <literal>+</literal> is appended to the command name, the tables,
> + excluded tables and schemas associated with each publication
> are shown as
> + well.
> </para>
>
> /excluded tables and schemas/excluded tables, and schemas/
>
I addressed the remaining comments in the latest v26 patch [1].
[1]:https://www.postgresql.org/message-id/CANhcyEWGiWwGt1-v6d9bCAae9Np7cNPt%2BiRV9PXBZ0z%3D75XEVw%40mail...
Thanks,
Shlok Kyal
view thread (377+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CANhcyEUmEpYXgizXd0BPehPKSzQg3n4zkiy5rbb5+hx4dbjybw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox