Horton
Menu

Re: Pgoutput not capturing the generated columns

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: Shubham Khanna <khannashubham1197@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Rajendra Kumar Dangwal <dangwalrajendra888@gmail.com>, pgsql-hackers@lists.postgresql.org, euler@eulerto.com
Date: 2024-09-16T21:01:32Z
Lists: pgsql-hackers 
On Wed, Sep 11, 2024 at 10:30 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Because this feature is now being implemented as a PUBLICATION option,
> there is another scenario that might need consideration; I am thinking
> about where the same table is published by multiple PUBLICATIONS (with
> different option settings) that are subscribed by a single
> SUBSCRIPTION.
>
> e.g.1
> -----
> CREATE PUBLICATION pub1 FOR TABLE t1 WITH (publish_generated_columns = true);
> CREATE PUBLICATION pub2 FOR TABLE t1 WITH (publish_generated_columns = false);
> CREATE SUBSCRIPTION sub ... PUBLICATIONS pub1,pub2;
> -----
>
> e.g.2
> -----
> CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = true);
> CREATE PUBLICATION pub2 FOR TABLE t1 WITH (publish_generated_columns = false);
> CREATE SUBSCRIPTION sub ... PUBLICATIONS pub1,pub2;
> -----
>
> Do you know if this case is supported? If yes, then which publication
> option value wins?

I would expect these option values are processed with OR. That is, we
publish changes of the generated columns if at least one publication
sets publish_generated_columns to true. It seems to me that we treat
multiple row filters in the same way.

>
> The CREATE SUBSCRIPTION docs [1] only says "Subscriptions having
> several publications in which the same table has been published with
> different column lists are not supported."
>
> Perhaps the user is supposed to deduce that the example above would
> work OK if table 't1' has no generated cols. OTOH, if it did have
> generated cols then the PUBLICATION column lists must be different and
> therefore it is "not supported" (??).

With the patch, how should this feature work when users specify a
generated column to the column list and set publish_generated_column =
false, in the first place? raise an error (as we do today)? or always
send NULL?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Commits

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

  1. Doc: Generated column replication.

  2. Rename pubgencols_type to pubgencols in pg_publication.

  3. Doc: Fix column name in pg_publication catalog.

  4. Fix buildfarm failure introduced by commit e65dbc9927.

  5. Change publication's publish_generated_columns option type to enum.

  6. Fix \dRp+ output when describing publications with a lower server version.

  7. Replicate generated columns when 'publish_generated_columns' is set.

  8. Doc: Update the behavior of generated columns in Logical Replication.

  9. Replicate generated columns when specified in the column list.

  10. Doc: Generated columns are skipped for logical replication.