Horton
Menu

Re: Pgoutput not capturing the generated columns

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: Shubham Khanna <khannashubham1197@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Rajendra Kumar Dangwal <dangwalrajendra888@gmail.com>, pgsql-hackers@lists.postgresql.org, euler@eulerto.com
Date: 2024-09-12T05:30:23Z
Lists: pgsql-hackers 
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?

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" (??).

I have not tried this to see what happens, but even if it behaves as
expected, there should probably be some comments/docs/tests for this
scenario to clarify it for the user.

Notice that "publish_via_partition_root" has a similar conundrum, but
in that case, the behaviour is documented in the CREATE PUBLICATION
docs [2]. So, maybe  "publish_generated_columns" should be documented
a bit like that.

======
[1] https://www.postgresql.org/docs/devel/sql-createsubscription.html
[2] https://www.postgresql.org/docs/devel/sql-createpublication.html

Kind Regards,
Peter Smith.
Fujitsu Australia

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.