Re: Skipping schema changes in publication

On Wed, 18 Jun 2025 at 06:34, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Tue, Jun 17, 2025 at 5:41 PM Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> wrote:
> ...
> > I have attached a patch support excluding columns for publication.
> >
> > I have added a syntax: "FOR TABLE table_name EXCEPT (c1, c2, ..)"
> > It can be used with CREATE or ALTER PUBLICATION.
> >
> > v12-0003 patch contains the changes for the same.
> >
>
> Hi Shlok,
>
> I was interested in your new EXCEPT (col-list) so I had a quick look
> at your patch v12-0003 (only looked at the documentation).
>
> Below are some comments:
>
> ======
>
> 1. Chapter 29.5 "Column Lists".
>
> I think new EXCEPT syntax needs a mention here as well.
>
Added

> ======
>
> doc/src/sgml/catalogs.sgml
>
> 2.
> + <para>
> + This is an array of values that indicates which table columns are
> + excluded from the publication. For example, a value of
> + <literal>1 3</literal> would mean that the columns except the first and
> + the third columns are published.
> + A null value indicates that no columns are excluded from being
> published.
> + </para></entry>
>
> The sentence "A null value indicates that no columns are excluded from
> being published" seems kind of confusing, because if the user has a
> "normal" column-list although nothing was being *explicitly* excluded
> (using EXCEPT), any columns not named are *implicitly* excluded from
> being published.
>
I have removed this line.

> ~
>
> 3.
> TBH, I was wondering why a new catalog attribute was necessary...
>
> Can't you simply re-use the existing attribute "prattrs" attribute.
> e.g. let's just define negative means exclude.
>
> e.g. a value of 1 3 means only the 1st and 3rd columns are published
> e.g. a value of -1 -3 means all columns except 1st and 3rd columns are published
> e.g. a value of null mean all columns are published
>
> (mixes of negative and positive will not be possible)
>

Currently I have added a new attribute 'prexcludeattrs' in
pg_publication_rel table. I used this approach because it will be
easier for user to get the exclude column list, in code no extra
processing is required to get the exclude column list.

For an approach to use negative numbers for exclude columns. I see an
advantage that we do not need to introduce a new column for
pg_publication_rel. But in code, each time we want to get a column
list or exclude column list we need an extra processing of 'prattrs'
columns. Also I don't see any existing catalog table using a negative
attribute for column list.

Based on above observations, I feel that the current is better.

Please correct me if I missed an advantage for the approach you suggested.

> ======
>
> doc/src/sgml/ref/alter_publication.sgml
>
> 4. ALTER PUBLICATION syntax
>
> The syntax is currently written as:
> TABLE [ ONLY ] table_name [ * ] { [ [ ( column_name [, ... ] ) ] | [
> EXCEPT ( column_name [, ... ] ) ] ] } [ WHERE ( expression ) ] [, ...
> ]
>
> Can't this be more simply written as:
> TABLE [ ONLY ] table_name [ * ] [ [ EXCEPT ] ( column_name [, ... ] )
> ] [ WHERE ( expression ) ] [, ... ]
>
> ~~~
Fixed

>
> 5.
> + <para>
> + Alter publication <structname>mypublication</structname> to add table
> + <structname>users</structname> except column
> + <structname>security_pin</structname>:
> +<programlisting>
> +ALTER PUBLICATION production_publication ADD TABLE users EXCEPT (security_pin);
>
> Those tags don't seem correct. e.g. "users" and "security_pin" are not
> <structname> (???).
>
> Perhaps, every other example here is wrong too and you just copied
> them? Anyway, something here looks wrong to me.
>
I saw different documents and usage of tags seems not well defined.
For example for table we are using tags in document
create_publication.sgml, update.sgml <structname> is used, in document
table.sgml, advanced.sgml <classname> is used, and in
logical-replication.sgml <literal> is used. Similarly for column
names <structname>, <structfield> or <literal> are used in different
parts of the document.

I kept the changed tag to <structfield> for the column for this patch.
Do you have any suggestions?

> ======
> doc/src/sgml/ref/create_publication.sgml
>
> 6. CREATE PUBLICATION syntax
>
> The syntax is currently written as:
> TABLE [ ONLY ] table_name [ * ] { [ [ ( column_name [, ... ] ) ] | [
> EXCEPT ( column_name [, ... ] ) ] ] } [ WHERE ( expression ) ] [, ...
> ]
>
> Can't this be more simply written as:
> TABLE [ ONLY ] table_name [ * ] [ [ EXCEPT ] ( column_name [, ... ] )
> ] [ WHERE ( expression ) ] [, ... ]
>
> ~~~
Fixed

>
> 7.
> + <para>
> + When a column list is specified with EXCEPT, the named columns are not
> + replicated. The excluded column list cannot contain generated
> columns. The
> + column list and excluded column list cannot be specified together.
> + Specifying a column list has no effect on <literal>TRUNCATE</literal>
> + commands.
> + </para>
>
> IMO you don't need to say "The column list and excluded column list
> cannot be specified together." because AFAIK the syntax makes that
> impossible to do anyhow.
>
Removed this line

> ~~~
>
> 8.
> + <para>
> + Create a publication that publishes all changes for table
> <structname>users</structname>
> + except changes for columns <structname>security_pin</structname>:
> +<programlisting>
> +CREATE PUBLICATION users_safe FOR TABLE users EXCEPT (security_pin);
> +</programlisting>
> + </para>
>
> 8a.
> Same review comment as previously -- Those tags don't seem correct.
> e.g. "users" and "security_pin" are not <structname> (???).
> Again, are all the other existing tags also wrong? Maybe a new thread
> needed to address these?
>
> ~
Same as point 5.
I also feel this should be addressed in a new thread.

> 8b.
> Plural? /except changes for columns/except changes for column/
Fixed

Also in this patch I added displaying "EXCEPT (column_list)" for \dRp+
and \d table_name psql commands.

Thanks and Regards,
Shlok Kyal