Re: Skipping schema changes in publication

On Mon, 29 Sept 2025 at 08:58, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> Hi Shlok,
>
> I was looking at the recent v24 changes.
>
> ======
> GENERAL.
>
> I saw that you modified the system view to add a new flag:
>
> + <row>
> + <entry role="catalog_table_entry"><para role="column_definition">
> + <structfield>exceptcol</structfield> <type>bool</type>
> + </para>
> + <para>
> + True if a column list with <literal>EXCEPT</literal> clause is specified
> + for the table in the publication.
> + </para></entry>
> + </row>
>
> So output now might look like this:
>
> +CREATE TABLE pub_test_except1 (a int NOT NULL, b int, c int NOT NULL, d int);
> +CREATE PUBLICATION testpub_except FOR TABLE pub_test_except1,
> pub_sch1.pub_test_except2 EXCEPT (b, c);
> +SELECT * FROM pg_publication_tables WHERE pubname = 'testpub_except';
> + pubname | schemaname | tablename | attnames |
> rowfilter | exceptcol
> +----------------+------------+------------------+-----------+-----------+-----------
> + testpub_except | public | pub_test_except1 | {a,b,c,d} | | f
> + testpub_except | pub_sch1 | pub_test_except2 | {a,d} | | t
> +(2 rows)
>
> ~~~
>
> I think this was done in response to a comment from Vignesh [1], but
> it did not get implemented in the way that I had imagined. e.g. I
> imagined the view might be more like this:
>
> + pubname | schemaname | tablename | attnames |
> rowfilter | exceptcols
> +----------------+------------+------------------+-----------+-----------+-----------
> + testpub_except | public | pub_test_except1 | {a,b,c,d} | |
> + testpub_except | pub_sch1 | pub_test_except2 | {a,d} | | {b,c}
>
> I don't know if broadcasting to the user what the unpublished/hidden
> columns' names are is very wise (e.g. "{password,internal_notes,
> salary}", but OTOH just having a boolean flag saying that "something"
> was excluded ddin't seem useful.
>
> ~
>
> Furthermore, having a Boolean seemed strangely incompatible with a
> normal column list. e.g. Lets say there is a table T1 with cols
> c1,c2,c3,c4.
>
> I could publish that as "FOR TABLE T1(c1,c2,c3)"
> Or as "FOR TABLE T1 EXCEPT (c4)"
>
> In the v24 implementation, AFAIK, the view will show those as
> "attnames = {c1,c2,c3}", and except will be both "f" and "t". It
> seemed odd to.
>
> ~
>
> Lastly, I think the EXCEPT (col-list) feature was mostly added just
> to help users with 100s of columns to write their CREATE PUBLICATION
> statement more easily. Since the view already shows all the columns
> that will be published. So, I'm kind of -0.5 on this idea of changing
> the view to show how they typed their statement.

On further consideration, I’m ok with removing this column to avoid
potential confusion.

Regards,
Vignesh