The Fujitsu PostgreSQL OSS team has been developing with the community a new feature for PG18 to allow logical replication of (STORED) generated columns.
This article explains a bit more about the new feature.
Background
Typically, when using logical replication, the table at the subscriber will be defined the same as at the publisher:
test_pub=# CREATE TABLE tab1(a int, b int); test_sub=# CREATE TABLE tab1(a int, b int);
Here’s another example where b is a generated column:
test_pub=# -- "b" is a stored generated column test_pub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED); test_pub=# INSERT INTO tab_gen_to_gen VALUES (1),(2),(3); test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen; test_pub=# SELECT * from tab_gen_to_gen; a | b ---+--- 1 | 2 2 | 4 3 | 6 (3 rows) test_sub=# -- "b" is stored generated col, but calculation differs from col "b" on publisher test_sub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1; test_sub=# SELECT * from tab_gen_to_gen; a | b ---+---- 1 | 22 * 2 | 44 3 | 66 (3 rows)
* Notice the b values come from the subscriber side column calculation (a * 22)
The publisher side generated column is not used at all.
Because the publisher side generated column values are not even used, the PG17 (and prior) logical replication implementation optimizes the replication by not replicating the generated columns at all. They are simply skipped over.
The broken scenario
Everything works fine as described in the Background section above, but in fact, there is no strict requirement for the subscriber table definition to be identical to the publisher table.
Specifically, it is allowed for the publisher table column to be generated, even when the equivalent subscriber side table column is not generated:
test_pub=# -- "b" is a stored generated column test_pub=# CREATE TABLE tab_gen_to_nogen (a int, b int GENERATED ALWAYS AS (a * 2) STORED); test_sub=# -- "b" at the subscriber is NOT a generated column test_sub=# CREATE TABLE tab_gen_to_nogen (a int, b int);
This highlights a use case where it would be desirable to replicate the generated value directly from the publisher to the regular (non-generated) column on the subscriber. PostgreSQL 17 and earlier do not support this, because as mentioned already, they skip generated columns during logical replication.
The ability to avoid skipping the replication of generated columns may also be useful when replicating data to a non-PostgreSQL database, especially if the target database does not support generated columns.
Method #1. Publication parameter publish_generated_columns
A new enum parameter (publish_generated_columns) is added in PG18 to the CREATE PUBLICATION command syntax.
Possible values:
- none -- (default) Generated columns are skipped during replication. This behaviour is the same as PG17.
- stored -- STORED generated columns are published in the same way as regular (non-generated) columns.
Examples
test_pub=# -- "b" is a stored generated column test_pub=# CREATE TABLE tab_gencol_b (a int, b int GENERATED ALWAYS AS (a * 2) STORED); test_pub=# -- col "b" will NOT be published, because publish_generated_columns defaults to none test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gencol_b; test_pub=# -- col "b" will NOT be published, because publish_generated_columns is none test_pub=# CREATE PUBLICATION pub2 FOR TABLE tab_gencol_b WITH (publish_generated_columns=none); test_pub=# -- col "b" WILL be published, because publish_generated_columns is stored test_pub=# CREATE PUBLICATION pub3 FOR TABLE tab_gencol_b WITH (publish_generated_columns=stored);
Method #2. Publication column lists
A PUBLICATION column list defines what columns of a particular table will be published.
Previously, there was a restriction disallowing generated columns from being members of a column list. This restriction has been lifted in PG18.
Examples
test_pub=# -- "b" is a stored generated column test_pub=# CREATE TABLE tab_gencol_b (a int, b int GENERATED ALWAYS AS (a * 2) STORED); test_pub=# -- column "b" will NOT be published, because "b" is not in the column list test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gencol_b(a); test_pub=# -- column "b" WILL be published, because "b" is in the column list test_pub=# CREATE PUBLICATION pub2 FOR TABLE tab_gencol_b(a, b);
Publication column lists take precedence
If a publication column list is defined, it takes precedence over the publish_generated_columns parameter.
Examples
test_pub=# -- "b" is a stored generated column test_pub=# CREATE TABLE tab_gencol_b (a int, b int GENERATED ALWAYS AS (a * 2) STORED); test_pub=# -- col "b" is not in col list so will NOT be published, even when publish_generated_columns is stored test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gencol_b (a) WITH (publish_generated_columns=stored); test_pub=# -- col "b" is in the col list so it WILL be published, even when publish_generated_columns is none test_pub=# CREATE PUBLICATION pub2 FOR TABLE tab_gencol_b(a, b) WITH (publish_generated_columns=none);
Effect
Let’s see what the replication behaviors are when the equivalent table column on one or both of Publisher/Subscriber sides is a (STORED) GENERATED column.
The Replicate generated field in the sections below is Yes/No according to how the user defined the PUBLICATION:
Yes
- There is a publication column list and there is a generated column named in it
- There is no publication column list, but the parameter publish_generated_columns is stored
No
- There is a publication column list, but the generated column is not named in it
- There is no publication column list, and the parameter publish_generated_columns is none (default)
- This is the same as PG17 behavior
Behaviors that differ due to the new PG18 feature are marked with the 
icon below.
| # | Publisher column |
Subscriber column |
Replace generated |
Subscriber colum result |
|
| 1 | Generated | Generated | No | Subscriber-generated column value | |
| 2 | |
Generated | Generated | Yes | ERROR – incompatible generated column |
| 3 | Generated | Regular | No | Subscriber regular column default | |
| 4 | |
Generated | Regular | Yes | Publisher-generated column value |
| 5 | Generated | (missing) | No | - | |
| 6 | |
Generated | (missing) | Yes | ERROR – missing replicated column |
| 7 | |
Regular | Generated | - | PG17: ERROR – missing column PG18: ERROR – incompatible generated column |
| 8 | (missing) | Generated | - | Subscriber-generated column value |
Behavior 1
| Publisher column | Generated |
| Subscriber column | Generated |
| Replicate generated | No |
| Subscriber col result | Subscriber-generated column value |
| Notes | Generated columns are not replicated, so the result is just the subscriber-generated column value |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
test_sub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 100) STORED);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
test_sub=# SELECT * FROM t1;
a | b
---+-----
1 | 101
(1 row)
Behavior 2This behavior is different due to the new PG18 feature
| Publisher column | Generated |
| Subscriber column | Generated |
| Replicate generated | Yes |
| Subscriber col result | ERROR – incompatible generated column |
| Notes | Because we are requesting the publisher-generated column to be replicated, now the CREATE SUBSCRIPTION fails reporting that subscriber side has an incompatible generated column. |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1 WITH (publish_generated_columns=stored);
test_sub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 100) STORED);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
ERROR: logical replication target relation "public.t1" has incompatible generated column: "b"
Behavior 3
| Publisher column | Generated |
| Subscriber column | Regular |
| Replicate generated | No |
| Subscriber col result | Subscriber regular column default |
| Notes | Generated columns are not replicated, so the result is just the subscriber regular column default value. |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
test_sub=# CREATE TABLE t1 (a int, b int DEFAULT 99);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
test_sub=# SELECT * FROM t1;
a | b
---+----
1 | 99
(1 row)
Behavior 4This behavior is different due to the new PG18 feature
| Publisher column | Generated |
| Subscriber column | Regular |
| Replicate generated | Yes |
| Subscriber col result | Publisher-generated column value |
| Notes | The generated value from the publisher table is replicated. Permitting this scenario was the reason for introducing this new feature in the first place. |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1 WITH (publish_generated_columns=stored;
test_sub=# CREATE TABLE t1 (a int, b int DEFAULT 99);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
test_sub=# SELECT * FROM t1;
a | b
---+---
1 | 2
(1 row)
Behavior 5
| Publisher column | Generated |
| Subscriber column | (missing) |
| Replicate generated | No |
| Subscriber col result | - |
| Notes | Generated columns are not replicated, so nothing happens. |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
test_sub=# CREATE TABLE t1 (a int);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
test_sub=# SELECT * FROM t1;
a
---
1
(1 row)
Behavior 6This behavior is different due to the new PG18 feature
| Publisher column | Generated |
| Subscriber column | (missing) |
| Replicate generated | Yes |
| Subscriber col result | ERROR – missing replicated column |
| Notes | Because we are requesting the publisher-generated column to be replicated, now the CREATE SUBSCRIPTION fails, reporting the subscriber side column as a missing replicated column. |
test_pub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1 WITH (publish_generated_columns=stored);
test_sub=# CREATE TABLE t1 (a int);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
ERROR: logical replication target relation "public.t1" is missing replicated column: "b"
Behavior 7This behavior is different due to the new PG18 feature
| Publisher column | Regular |
| Subscriber column | Generated |
| Replicate generated | - |
| Subscriber col result |
PG17: ERROR – missing column PG18: ERROR – incompatible generated column |
| Notes |
PG17: CREATE SUBSCRIPTION fails, reporting that the subscriber side has a missing column. PG18: CREATE SUBSCRIPTION fails, reporting that replication to a subscriber-side generated column is not allowed. |
test_pub=# CREATE TABLE t1 (a int, b int DEFAULT 99);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
test_sub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 100) STORED);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
ERROR: logical replication target relation "public.t1" has incompatible generated column: "b"
Behavior 8
| Publisher column | (missing) |
| Subscriber column | Generated |
| Replicate generated | - |
| Subscriber col result |
Subscriber-generated column value |
| Notes |
This is a scenario where the subscriber table has more columns than the publisher table.There is nothing to be replicated. The extra column at the subscriber side was a generated one, so the result is just that subscriber-generated column value. |
test_pub=# CREATE TABLE t1 (a int);
test_pub=# INSERT INTO t1 VALUES (1);
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
test_sub=# CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a + 100) STORED);
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
test_sub=# SELECT * FROM t1;
a | b
---+-----
1 | 101
(1 row)
Unsupported scenarios
- VIRTUAL generated columns
Logical replication supports only replication of STORED generated columns.
- Multiple publications to one subscription
There is no support for subscriptions comprising several publications, when the same table is published simultaneously with conflicting settings for the publication of generated columns.
- Initial Table Synchronization to older subscribers
If the subscriber is from a release prior to PG18, then initial table synchronization won't copy the generated columns even if they are defined in the publisher.
For the future
The new PG18 the feature currently supports only STORED generated columns, however the use of an enumerated value for publish_generated_columns parameter was intentional, allowing for future enhancements – such as potential support for VIRTUAL generated columns.
References
- PostgreSQL online documentation
- Discussion threads on pgsql-hackers
- Git source code commits
