Обсуждение: DOCS: Generated table columns are skipped by logical replication
Hi hackers, While reviewing another thread that proposes to include "generated columns" support for logical replication [1] I was looking for any existing PostgreSQL documentation on this topic. But, I found almost nothing about it at all -- I only saw one aside mention saying that logical replication low-level message information is not sent for generated columns [2]. ~~ IMO there should be some high-level place in the docs where the behaviour for logical replication w.r.t. generated columns is described. There are lots of candidate places which could talk about this topic. * e.g.1 in "Generated Columns" (section 5.4) * e.g.2 in LR "Column-Lists" docs (section 29.5) * e.g.3 in LR "Restrictions" docs (section 29.7) * e.g.4 in the "CREATE PUBLICATION" reference page For now, I have provided just a simple patch for the "Generated Columns" section [3]. Perhaps it is enough. Thoughts? ====== [1] https://www.postgresql.org/message-id/flat/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E%40gmail.com [2] https://www.postgresql.org/docs/devel/protocol-logicalrep-message-formats.html [3] https://www.postgresql.org/docs/devel/ddl-generated-columns.html Kind Regards, Peter Smith. Fujitsu Australia
Вложения
On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote: > > While reviewing another thread that proposes to include "generated > columns" support for logical replication [1] I was looking for any > existing PostgreSQL documentation on this topic. > > But, I found almost nothing about it at all -- I only saw one aside > mention saying that logical replication low-level message information > is not sent for generated columns [2]. > > ~~ > > IMO there should be some high-level place in the docs where the > behaviour for logical replication w.r.t. generated columns is > described. > +1. > There are lots of candidate places which could talk about this topic. > * e.g.1 in "Generated Columns" (section 5.4) > * e.g.2 in LR "Column-Lists" docs (section 29.5) > * e.g.3 in LR "Restrictions" docs (section 29.7) > * e.g.4 in the "CREATE PUBLICATION" reference page > > For now, I have provided just a simple patch for the "Generated > Columns" section [3]. Perhaps it is enough. > Can we try to clarify if their corresponding values are replicated? -- With Regards, Amit Kapila.
On Tue, Jun 18, 2024 at 9:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote: > > > > While reviewing another thread that proposes to include "generated > > columns" support for logical replication [1] I was looking for any > > existing PostgreSQL documentation on this topic. > > > > But, I found almost nothing about it at all -- I only saw one aside > > mention saying that logical replication low-level message information > > is not sent for generated columns [2]. > > > > ~~ > > > > IMO there should be some high-level place in the docs where the > > behaviour for logical replication w.r.t. generated columns is > > described. > > > > +1. > > > There are lots of candidate places which could talk about this topic. > > * e.g.1 in "Generated Columns" (section 5.4) > > * e.g.2 in LR "Column-Lists" docs (section 29.5) > > * e.g.3 in LR "Restrictions" docs (section 29.7) > > * e.g.4 in the "CREATE PUBLICATION" reference page > > > > For now, I have provided just a simple patch for the "Generated > > Columns" section [3]. Perhaps it is enough. > > > > Can we try to clarify if their corresponding values are replicated? > Sure. Here are some current PG17 observed behaviours demonstrating that generated columns are not replicated. ====== Example #1 The generated cols 'b' column is not replicated. Notice the subscriber side 'b' has its own computed value which uses a different calculation. PUB: create table t1 (a int, b int generated always as (a * 2) stored); SUB: create table t1 (a int, b int generated always as (a * 20) stored); PUB: insert into t1 values (1),(2),(3); create publication pub1 for table t1; test_pub=# select * from t1; a | b ---+--- 1 | 2 2 | 4 3 | 6 (3 rows) SUB: create subscription sub1 connection 'dbname=test_pub' publication pub1; test_sub=# select * from t1; a | b ---+---- 1 | 20 2 | 40 3 | 60 (3 rows) ====== Example 2 You cannot specify a generated column in a CREATE PUBLICATION column-list. PUB: create table t2 (a int, b int generated always as (a * 2) stored); create publication pub2 for table t2(b); ERROR: cannot use generated column "b" in publication column list ====== Example 3 Here the subscriber-side table doesn't even have a column 'b'. Normally, a missing column like this would cause subscription errors, but since the publisher-side generated column 'b' is not replicated, this scenario is allowed. PUB: create table t3 (a int, b int generated always as (a * 2) stored); SUB: create table t3 (a int); PUB: create publication pub3 for table t3; insert into t3 values (1),(2),(3); test_pub=# select * from t3; a | b ---+--- 1 | 2 2 | 4 3 | 6 (3 rows) SUB: create subscription sub3 connection 'dbname=test_pub' publication pub3; test_sub=# select * from t3; a --- 1 2 3 (3 rows) ====== Kind Regards, Peter Smith. Fujitsu Australia
On Wed, Jun 19, 2024 at 6:46 AM Peter Smith <smithpb2250@gmail.com> wrote: > > On Tue, Jun 18, 2024 at 9:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > On Tue, Jun 18, 2024 at 12:11 PM Peter Smith <smithpb2250@gmail.com> wrote: > > > > > > While reviewing another thread that proposes to include "generated > > > columns" support for logical replication [1] I was looking for any > > > existing PostgreSQL documentation on this topic. > > > > > > But, I found almost nothing about it at all -- I only saw one aside > > > mention saying that logical replication low-level message information > > > is not sent for generated columns [2]. > > > > > > ~~ > > > > > > IMO there should be some high-level place in the docs where the > > > behaviour for logical replication w.r.t. generated columns is > > > described. > > > > > > > +1. > > > > > There are lots of candidate places which could talk about this topic. > > > * e.g.1 in "Generated Columns" (section 5.4) > > > * e.g.2 in LR "Column-Lists" docs (section 29.5) > > > * e.g.3 in LR "Restrictions" docs (section 29.7) > > > * e.g.4 in the "CREATE PUBLICATION" reference page > > > > > > For now, I have provided just a simple patch for the "Generated > > > Columns" section [3]. Perhaps it is enough. > > > > > > > Can we try to clarify if their corresponding values are replicated? > > > > Sure. Here are some current PG17 observed behaviours demonstrating > that generated columns are not replicated. > Thanks for sharing examples. Your proposed patch as-is looks good to me. We should back-patch this unless you or someone else thinks otherwise. -- With Regards, Amit Kapila.
On 18.06.24 08:40, Peter Smith wrote: > For now, I have provided just a simple patch for the "Generated > Columns" section [3]. Perhaps it is enough. Makes sense. + Generated columns are skipped for logical replication, and cannot be + specified in a <command>CREATE PUBLICATION</command> column-list. Maybe remove the comma, and change "column-list" to "column list".
On Wed, Jun 19, 2024 at 2:21 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > ... > > Thanks for sharing examples. Your proposed patch as-is looks good to > me. We should back-patch this unless you or someone else thinks > otherwise. > Hi Amit. I modified the patch text slightly according to Peter E's suggestion [1]. I also tested the above examples against all older PostgreSQL versions 12,13,14,15,16,17. The logical replication behaviour of skipping generated columns is the same for all of them. Note that CREATE PUBLICATION column lists did not exist until PG15, so a modified patch is needed for the versions before that. ~ The attached "HEAD" patch is appropriate for HEAD, PG17, PG16, PG15 The attached "PG14" patch is appropriate for PG14, PG13, PG12 ====== [1] https://www.postgresql.org/message-id/2b291af9-929f-49ab-b378-5cbc029d348f%40eisentraut.org Kind Regards, Peter Smith. Fujitsu Australia
Вложения
On Thu, Jun 20, 2024 at 6:36 AM Peter Smith <smithpb2250@gmail.com> wrote: > > Hi Amit. > > I modified the patch text slightly according to Peter E's suggestion [1]. > > I also tested the above examples against all older PostgreSQL versions > 12,13,14,15,16,17. The logical replication behaviour of skipping > generated columns is the same for all of them. > > Note that CREATE PUBLICATION column lists did not exist until PG15, so > a modified patch is needed for the versions before that. > Pushed. -- With Regards, Amit Kapila.