public inbox for [email protected]
help / color / mirror / Atom feedpg_publication_tables: return NULL attnames when no column list is specified
12+ messages / 4 participants
[nested] [flat]
* pg_publication_tables: return NULL attnames when no column list is specified
@ 2026-03-25 19:50 Roberto Mello <[email protected]>
2026-03-26 00:48 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-30 07:20 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
2026-03-31 22:55 ` Re: pg_publication_tables: return NULL attnames when no column list is specified David G. Johnston <[email protected]>
0 siblings, 3 replies; 12+ messages in thread
From: Roberto Mello @ 2026-03-25 19:50 UTC (permalink / raw)
To: PostgreSQL Developers <[email protected]>
Hi,
When a table is added to a publication without a column list:
ALTER PUBLICATION my_pub ADD TABLE my_table;
Any new column added to my_table will automatically be replicated. The
underlying catalog (pg_publication_rel.prattrs) stores NULL for this case.
When a table is added with an explicit column list:
ALTER PUBLICATION my_pub ADD TABLE my_table (id, name, status);
New columns will NOT be replicated until the publication is explicitly
altered.
The catalog stores an int2vector of the specified attribute numbers.
The problem is that pg_get_publication_tables() in pg_publication.c (the
set-returning
function backing the pg_publication_tables view) erases this distinction.
When prattrs is NULL, it opens the table, iterates all eligible attributes,
and builds a synthetic int2vector of all current columns. The view then
shows
the same attnames output for both cases.
| Scenario | prattrs in catalog | attrs from SRF |
attnames in view |
|--------------------------|--------------------|---------------------|------------------|
| column list (a, b) | {1, 2 } | {1,2} | {a,
b} |
| No column list | NULL | {1,2,3,...} (synth) | {a,
b, c, ...} |
| FOR ALL TABLES/IN SCHEMA | no catalog row | {1,2,3,...} (synth) | {a,
b, c, ...} |
This is a problem for operations: schema migrations that add columns to
published
tables may or may not replicate depending on how the publication was
originally
defined, and the only way to check is querying pg_publication_rel directly.
Additionally, tablesync.c has a workaround heuristic that tries to reverse
this synthesis by comparing array_length(gpt.attrs, 1) against c.relnatts.
This heuristic is buggy: relnatts counts all user attributes including
dropped
columns, but the synthesized list excludes dropped columns and conditionally
excludes generated columns. So for any table with a dropped column, the
heuristic incorrectly treats "no column list" as if an explicit list were
specified.
The problematic code is the result of commits that incrementally
built on each other, with later ones introducing workarounds for side
effects
of earlier ones:
1. fd0b9dcebda (06/2022) Amit Kapila (author: Hou Zhijie)
Prohibit combining publications with different column lists
Added the subscriber-side check that prevents combining publications with
different column lists for the same table. The subscriber runs
SELECT DISTINCT gpt.attrs and expects at most one row. To handle the fact
that pg_get_publication_tables() returned NULL for no-column-list
publications (which would always be distinct from any explicit list), this
commit introduced the relnatts heuristic in tablesync.c:
CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)
THEN NULL ELSE gpt.attrs END
The idea was: if the SRF returns a synthesized list whose length equals
relnatts, it must be the "all columns" case, so collapse it back to NULL.
But at this point the SRF didn't synthesize anything yet, so the heuristic
was anticipating the next commit.
2. b7ae0395369 (01/2023) Amit Kapila (author: Shi yu)
Ignore dropped and generated columns from the column list
This is the commit that introduced the synthesis loop. It "solved" a problem
where a table in two publications (one with a column list naming all
columns,
one without a column list) was erroring with "cannot use different column
lists"
because one returned NULL and the other returned an int2vector. The fix was
to synthesize a column list when none was specified, filtering out dropped
and generated columns, so that both publications would produce identical
int2vectors and DISTINCT would collapse them to one row.
But this synthesis made it impossible to distinguish "all columns" from
"explicit list of all columns" in the view. And the relnatts heuristic from
fd0b9dcebda - which was supposed to reverse the synthesis - was broken from
the start because relnatts includes dropped columns while the synthesized
list
excludes them.
The synthesis in b7ae0395369 tried to make "no column list" and "explicit
list
of all columns" look identical. But they have genuinely different
semantics:
- No column list (NULL): all current and future columns are replicated.
ALTER TABLE ADD COLUMN automatically replicates the new column.
- Explicit full list: only the named columns are replicated. New columns
are NOT replicated until the publication is explicitly altered.
By making them indistinguishable, the synthesis hid a real conflict from
users
who had a table in two publications with different column semantics on the
same subscription. I am proposing a fix that restores the distinction and
correctly
(IMO) surfaces this conflict.
The fix is to stop synthesizing the full column list in
pg_get_publication_tables().
When prattrs is NULL in the catalog, let attrs remain NULL in the SRF
output.
Remove the buggy CASE WHEN heuristic in tablesync.c since it is no longer
needed.
There is one scenario where there is a change for users: one pub no list +
one pub explicit all columns. Anyone with that specific configuration will
see a new error on the
next tablesync or subscription refresh after upgrading. The fix for those
users is to
either remove the explicit column list from the second publication (making
both "all
columns"), or keep the difference and use separate subscriptions.
AFAICT this is safe except for the change in behavior I describe above.
psql and pg_dump query pg_publication_rel directly and not the affected
view. New subscriber
talking to old publisher still works (old pub synthesizes a list, new sub
handles it).
Old subscriber talking to new publisher also works (the old CASE WHEN
evaluates
array_length(NULL, 1) which returns NULL, falling through to the ELSE branch
returning NULL).
Attached is a patch with the fix, including updates to documentation, 7
updated
regression tests where existing expected outputs where attnames changes from
{a} to NULL, and added new test cases that verify both cases are
distinguishable in
the same query.
Roberto Mello
Snowflake
Attachments:
[application/octet-stream] v1-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch (12.6K, 3-v1-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch)
download | inline diff:
From 7f2581395567b76efd85975d2133ee2c484f18ff Mon Sep 17 00:00:00 2001
From: Roberto Mello <[email protected]>
Date: Tue, 24 Mar 2026 19:20:10 -0600
Subject: [PATCH v1] Fix pg_publication_tables to return NULL attnames for
all-column publications
Previously pg_get_publication_tables() synthesized a column list even
when no explicit column list was specified in the publication. This made
it impossible for consumers of pg_publication_tables to distinguish
between "all columns are published" (new columns will automatically be
replicated) and an explicit column list (only listed columns are
replicated).
Worse, the subscriber-side workaround in fetch_remote_table_info()
compared array_length(gpt.attrs, 1) against pg_class.relnatts to detect
the "all columns" case, but relnatts includes dropped columns while the
synthesized list excluded them, so tables with dropped columns were
misidentified as having an explicit column list.
Fix by simply leaving attrs as NULL when no column list was specified
(prattrs is NULL) in the publication catalog. This is consistent with
how prattrs itself is stored and removes the need for the relnatts
heuristic in tablesync.c.
The real replication column filtering (which must account for dropped
columns, generated columns, and pub->pubgencols_type) is performed
downstream in pgoutput.c by pub_form_cols_map() and
check_and_fetch_column_list(), both of which are unchanged by this
patch.
---
doc/src/sgml/system-views.sgml | 9 ++--
src/backend/catalog/pg_publication.c | 52 ++++---------------
src/backend/replication/logical/tablesync.c | 9 ++--
src/test/regress/expected/publication.out | 57 ++++++++++++++++++---
src/test/regress/sql/publication.sql | 34 ++++++++++++
5 files changed, 102 insertions(+), 59 deletions(-)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 9ee1a2bfc6a..6c926860e41 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2712,9 +2712,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
(references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attname</structfield>)
</para>
<para>
- Names of table columns included in the publication. This contains all
- the columns of the table when the user didn't specify the column list
- for the table.
+ Names of the table columns included in the publication, or NULL if
+ no explicit column list was specified for the table. When NULL, all
+ current and future columns of the table are published; new columns
+ added to the table will automatically be replicated. When non-NULL,
+ only the listed columns are replicated, and newly added columns will
+ not appear until the publication is explicitly altered.
</para></entry>
</row>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index c92ff3f51c3..a2fa4906f08 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1439,49 +1439,15 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
nulls[3] = true;
}
- /* Show all columns when the column list is not specified. */
- if (nulls[2])
- {
- Relation rel = table_open(relid, AccessShareLock);
- int nattnums = 0;
- int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
- int i;
-
- attnums = palloc_array(int16, desc->natts);
-
- for (i = 0; i < desc->natts; i++)
- {
- Form_pg_attribute att = TupleDescAttr(desc, i);
-
- if (att->attisdropped)
- continue;
-
- if (att->attgenerated)
- {
- /* We only support replication of STORED generated cols. */
- if (att->attgenerated != ATTRIBUTE_GENERATED_STORED)
- continue;
-
- /*
- * User hasn't requested to replicate STORED generated
- * cols.
- */
- if (pub->pubgencols_type != PUBLISH_GENCOLS_STORED)
- continue;
- }
-
- attnums[nattnums++] = att->attnum;
- }
-
- if (nattnums > 0)
- {
- values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
- nulls[2] = false;
- }
-
- table_close(rel, AccessShareLock);
- }
+ /*
+ * When no column list is specified (prattrs is NULL), we leave
+ * attrs as NULL rather than synthesizing a list of all current
+ * columns. This allows consumers of pg_publication_tables to
+ * distinguish between "all columns are published" (attrs IS
+ * NULL - new columns will automatically be replicated) and an
+ * explicit column list (attrs IS NOT NULL - only listed columns
+ * are replicated).
+ */
rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index f49a4852ecb..5f6d892e595 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -799,13 +799,10 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
*/
resetStringInfo(&cmd);
appendStringInfo(&cmd,
- "SELECT DISTINCT"
- " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
- " THEN NULL ELSE gpt.attrs END)"
+ "SELECT DISTINCT gpt.attrs"
" FROM pg_publication p,"
- " LATERAL pg_get_publication_tables(p.pubname) gpt,"
- " pg_class c"
- " WHERE gpt.relid = %u AND c.oid = gpt.relid"
+ " LATERAL pg_get_publication_tables(p.pubname) gpt"
+ " WHERE gpt.relid = %u"
" AND p.pubname IN ( %s )",
lrel->remoteid,
pub_names->data);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index a220f48b285..011c6026816 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2072,7 +2072,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2081,7 +2081,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2089,7 +2089,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2098,7 +2098,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2107,7 +2107,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2115,7 +2115,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2130,7 +2130,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
RESET client_min_messages;
@@ -2139,6 +2139,49 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+ pubname | all_columns
+------------+-------------
+ pub_cols | f
+ pub_nocols | t
+(2 rows)
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+ pubname | attnames
+----------+-----------
+ pub_cols | {id,name}
+(1 row)
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+ pubname | all_columns
+-------------+-------------
+ pub_dropped | t
+(1 row)
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+-- ======================================================
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 22e0a30b5c7..0f3c443a180 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1327,6 +1327,40 @@ DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+-- ======================================================
+
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
base-commit: 7baa39c468fa1d703d50cd697e9dd05f6831ae38
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-26 00:48 ` Roberto Mello <[email protected]>
2026-03-27 04:06 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Ajin Cherian <[email protected]>
2 siblings, 1 reply; 12+ messages in thread
From: Roberto Mello @ 2026-03-26 00:48 UTC (permalink / raw)
To: Peter Smith <[email protected]>; pgsql-hackers
On Wed, Mar 25, 2026 at 5:32 PM Peter Smith <[email protected]> wrote:
> FYI - the patch failed to apply for me because of publication.out issues.
>
> There appear to be some missing trailing spaces in the result table headers
>
Apologies.
Generated a v2 patch. Applied cleanly and tests passed.
Roberto Mello
Snowflake
Attachments:
[application/octet-stream] v2-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch (12.6K, 3-v2-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch)
download | inline diff:
From 395b793b1929e8559f37916aa5b5b7320befb615 Mon Sep 17 00:00:00 2001
From: Roberto Mello <[email protected]>
Date: Tue, 24 Mar 2026 19:20:10 -0600
Subject: [PATCH v2] Fix pg_publication_tables to return NULL attnames for
all-column publications
Previously, pg_get_publication_tables() synthesized a column list even
when no explicit column list was specified in the publication. This made
it impossible for consumers of pg_publication_tables to distinguish
between "all columns are published" (new columns will automatically be
replicated) and an explicit column list (only listed columns are
replicated).
Worse, the subscriber-side workaround in fetch_remote_table_info()
compared array_length(gpt.attrs, 1) against pg_class.relnatts to detect
the "all columns" case, but relnatts includes dropped columns while the
synthesized list excluded them, so tables with dropped columns were
misidentified as having an explicit column list.
Fix by simply leaving attrs as NULL when no column list was specified
(prattrs is NULL) in the publication catalog. This is consistent with
how prattrs itself is stored and removes the need for the relnatts
heuristic in tablesync.c.
The real replication column filtering (which must account for dropped
columns, generated columns, and pub->pubgencols_type) is performed
downstream in pgoutput.c by pub_form_cols_map() and
check_and_fetch_column_list(), both of which are unchanged by this
patch.
---
doc/src/sgml/system-views.sgml | 9 ++--
src/backend/catalog/pg_publication.c | 52 ++++---------------
src/backend/replication/logical/tablesync.c | 9 ++--
src/test/regress/expected/publication.out | 57 ++++++++++++++++++---
src/test/regress/sql/publication.sql | 34 ++++++++++++
5 files changed, 102 insertions(+), 59 deletions(-)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 9ee1a2bfc6a..6c926860e41 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2712,9 +2712,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
(references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attname</structfield>)
</para>
<para>
- Names of table columns included in the publication. This contains all
- the columns of the table when the user didn't specify the column list
- for the table.
+ Names of the table columns included in the publication, or NULL if
+ no explicit column list was specified for the table. When NULL, all
+ current and future columns of the table are published; new columns
+ added to the table will automatically be replicated. When non-NULL,
+ only the listed columns are replicated, and newly added columns will
+ not appear until the publication is explicitly altered.
</para></entry>
</row>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index c92ff3f51c3..a2fa4906f08 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1439,49 +1439,15 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
nulls[3] = true;
}
- /* Show all columns when the column list is not specified. */
- if (nulls[2])
- {
- Relation rel = table_open(relid, AccessShareLock);
- int nattnums = 0;
- int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
- int i;
-
- attnums = palloc_array(int16, desc->natts);
-
- for (i = 0; i < desc->natts; i++)
- {
- Form_pg_attribute att = TupleDescAttr(desc, i);
-
- if (att->attisdropped)
- continue;
-
- if (att->attgenerated)
- {
- /* We only support replication of STORED generated cols. */
- if (att->attgenerated != ATTRIBUTE_GENERATED_STORED)
- continue;
-
- /*
- * User hasn't requested to replicate STORED generated
- * cols.
- */
- if (pub->pubgencols_type != PUBLISH_GENCOLS_STORED)
- continue;
- }
-
- attnums[nattnums++] = att->attnum;
- }
-
- if (nattnums > 0)
- {
- values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
- nulls[2] = false;
- }
-
- table_close(rel, AccessShareLock);
- }
+ /*
+ * When no column list is specified (prattrs is NULL), we leave
+ * attrs as NULL rather than synthesizing a list of all current
+ * columns. This allows consumers of pg_publication_tables to
+ * distinguish between "all columns are published" (attrs IS
+ * NULL -- new columns will automatically be replicated) and an
+ * explicit column list (attrs IS NOT NULL -- only listed columns
+ * are replicated).
+ */
rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index f49a4852ecb..5f6d892e595 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -799,13 +799,10 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
*/
resetStringInfo(&cmd);
appendStringInfo(&cmd,
- "SELECT DISTINCT"
- " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
- " THEN NULL ELSE gpt.attrs END)"
+ "SELECT DISTINCT gpt.attrs"
" FROM pg_publication p,"
- " LATERAL pg_get_publication_tables(p.pubname) gpt,"
- " pg_class c"
- " WHERE gpt.relid = %u AND c.oid = gpt.relid"
+ " LATERAL pg_get_publication_tables(p.pubname) gpt"
+ " WHERE gpt.relid = %u"
" AND p.pubname IN ( %s )",
lrel->remoteid,
pub_names->data);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index a220f48b285..011c6026816 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2072,7 +2072,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2081,7 +2081,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2089,7 +2089,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2098,7 +2098,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2107,7 +2107,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2115,7 +2115,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2130,7 +2130,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
RESET client_min_messages;
@@ -2139,6 +2139,49 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+ pubname | all_columns
+------------+-------------
+ pub_cols | f
+ pub_nocols | t
+(2 rows)
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+ pubname | attnames
+----------+-----------
+ pub_cols | {id,name}
+(1 row)
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+ pubname | all_columns
+-------------+-------------
+ pub_dropped | t
+(1 row)
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+-- ======================================================
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 22e0a30b5c7..0f3c443a180 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1327,6 +1327,40 @@ DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+-- ======================================================
+
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-26 00:48 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-27 04:06 ` Ajin Cherian <[email protected]>
2026-03-27 17:27 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Ajin Cherian @ 2026-03-27 04:06 UTC (permalink / raw)
To: Roberto Mello <[email protected]>; +Cc: Peter Smith <[email protected]>; pgsql-hackers
On Thu, Mar 26, 2026 at 11:49 AM Roberto Mello <[email protected]> wrote:
>
> On Wed, Mar 25, 2026 at 5:32 PM Peter Smith <[email protected]> wrote:
>>
>> FYI - the patch failed to apply for me because of publication.out issues.
>>
>> There appear to be some missing trailing spaces in the result table headers
>
>
> Apologies.
>
> Generated a v2 patch. Applied cleanly and tests passed.
Hello,
Tested the patch and the patch fixes the bug as described. My tests below:
postgres=# CREATE TABLE my_table (id int PRIMARY KEY, name text, status text);
CREATE TABLE
postgres=# ALTER TABLE my_table ADD COLUMN old_col text;
ALTER TABLE
postgres=# ALTER TABLE my_table DROP COLUMN old_col;
ALTER TABLE
postgres=# CREATE PUBLICATION pub_no_list FOR TABLE my_table;
CREATE PUBLICATION pub_explicit_all FOR TABLE my_table (id, name, status);
CREATE PUBLICATION
CREATE PUBLICATION
=========================
On head - without patch:
=========================
postgres=# SELECT pubname, attnames
FROM pg_publication_tables
WHERE tablename = 'my_table'
ORDER BY pubname;
pubname | attnames
------------------+------------------
pub_explicit_all | {id,name,status}
pub_no_list | {id,name,status}
(2 rows)
postgres=# SELECT p.pubname, pr.prattrs
FROM pg_publication p
LEFT JOIN pg_publication_rel pr
ON pr.prpubid = p.oid
JOIN pg_class c ON c.oid = pr.prrelid
WHERE c.relname = 'my_table'
ORDER BY p.pubname;
pubname | prattrs
------------------+---------
pub_explicit_all | 1 2 3
pub_no_list |
(2 rows)
postgres=# SELECT
p.pubname,
pr.prattrs AS raw_catalog_attrs,
gpt.attnames AS view_attnames,
c.relnatts,
array_length(gpt.attnames, 1) AS synth_len,
-- Replicating the buggy CASE WHEN heuristic from tablesync.c
CASE WHEN array_length(gpt.attnames, 1) = c.relnatts
THEN NULL
ELSE gpt.attnames
END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c ON c.relname = gpt.tablename
JOIN pg_publication p ON p.pubname = gpt.pubname
LEFT JOIN pg_publication_rel pr
ON pr.prrelid = c.oid
AND pr.prpubid = p.oid
WHERE gpt.tablename = 'my_table'
ORDER BY p.pubname;
pubname | raw_catalog_attrs | view_attnames | relnatts |
synth_len | heuristic_result
------------------+-------------------+------------------+----------+-----------+------------------
pub_explicit_all | 1 2 3 | {id,name,status} | 4 |
3 | {id,name,status}
pub_no_list | | {id,name,status} | 4 |
3 | {id,name,status}
(2 rows)
---------------------------------------------------------------------
============
After patch:
============
postgres=# SELECT pubname, attnames
FROM pg_publication_tables
WHERE tablename = 'my_table'
ORDER BY pubname;
pubname | attnames
------------------+------------------
pub_explicit_all | {id,name,status}
pub_no_list |
(2 rows)
postgres=# SELECT p.pubname, pr.prattrs
FROM pg_publication p
LEFT JOIN pg_publication_rel pr ON pr.prpubid = p.oid
JOIN pg_class c ON c.oid = pr.prrelid
WHERE c.relname = 'my_table'
ORDER BY p.pubname;
pubname | prattrs
------------------+---------
pub_explicit_all | 1 2 3
pub_no_list |
(2 rows)
postgres=# SELECT
p.pubname,
pr.prattrs AS raw_catalog_attrs,
gpt.attnames AS view_attnames,
c.relnatts,
array_length(gpt.attnames, 1) AS synth_len,
-- Replicating the buggy CASE WHEN heuristic from tablesync.c
CASE WHEN array_length(gpt.attnames, 1) = c.relnatts
THEN NULL
ELSE gpt.attnames
END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c ON c.relname = gpt.tablename
JOIN pg_publication p ON p.pubname = gpt.pubname
LEFT JOIN pg_publication_rel pr
ON pr.prrelid = c.oid
AND pr.prpubid = p.oid
WHERE gpt.tablename = 'my_table'
ORDER BY p.pubname;
pubname | raw_catalog_attrs | view_attnames | relnatts |
synth_len | heuristic_result
------------------+-------------------+------------------+----------+-----------+------------------
pub_explicit_all | 1 2 3 | {id,name,status} | 4 |
3 | {id,name,status}
pub_no_list | | | 4 |
|
(2 rows)
One observation from reviewing the patch: the test suite covers the
partial column list and dropped column cases well, but is missing a
test for the two-publication conflict scenario (one pub with no list +
one pub with an explicit list of all columns on the same table). That
is the breaking change called out in the original report and probably
deserves its own regression test with a comment explaining the
expected behavior change for users in that configuration.
Patch LGTM otherwise.
regards,
Ajin Cherian
Fujitsu Australia
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-26 00:48 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-27 04:06 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Ajin Cherian <[email protected]>
@ 2026-03-27 17:27 ` Roberto Mello <[email protected]>
2026-03-27 19:53 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Roberto Mello @ 2026-03-27 17:27 UTC (permalink / raw)
To: Ajin Cherian <[email protected]>; +Cc: Peter Smith <[email protected]>; pgsql-hackers
On Thu, Mar 26, 2026 at 10:07 PM Ajin Cherian <[email protected]> wrote:
>
> Tested the patch and the patch fixes the bug as described. My tests below:
>
Thank you for the review.
<snip>
One observation from reviewing the patch: the test suite covers the
> partial column list and dropped column cases well, but is missing a
> test for the two-publication conflict scenario (one pub with no list +
> one pub with an explicit list of all columns on the same table). That
> is the breaking change called out in the original report and probably
> deserves its own regression test with a comment explaining the
> expected behavior change for users in that configuration.
> Patch LGTM otherwise.
>
Good point. I know I worked on that, so I must have accidentally dropped it
from the patch.
Will prepare a revision and submit.
Thanks again.
Roberto Mello
Snowflake
P.S.: I forgot to acknowledge and thank Greg Mullane for the initial review
on the patch, so doing that now.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-26 00:48 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-27 04:06 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Ajin Cherian <[email protected]>
2026-03-27 17:27 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-27 19:53 ` Roberto Mello <[email protected]>
2026-03-30 04:42 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Ajin Cherian <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Roberto Mello @ 2026-03-27 19:53 UTC (permalink / raw)
To: Ajin Cherian <[email protected]>; +Cc: Peter Smith <[email protected]>; pgsql-hackers
On Fri, Mar 27, 2026 at 11:27 AM Roberto Mello <[email protected]>
wrote:
> On Thu, Mar 26, 2026 at 10:07 PM Ajin Cherian <[email protected]> wrote:
>
>>
>> One observation from reviewing the patch: the test suite covers the
>> partial column list and dropped column cases well, but is missing a
>> test for the two-publication conflict scenario (one pub with no list +
>> one pub with an explicit list of all columns on the same table). That
>> is the breaking change called out in the original report and probably
>> deserves its own regression test with a comment explaining the
>> expected behavior change for users in that configuration.
>> Patch LGTM otherwise.
>>
>
> Good point. I know I worked on that, so I must have accidentally dropped
> it from the patch.
>
> Will prepare a revision and submit.
>
Here's v3 of the patch with the missing test.
Roberto Mello
Snowflake
Attachments:
[application/octet-stream] v3-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch (15.1K, 3-v3-0001-Fix-pg_publication_tables-to-return-NULL-attnames.patch)
download | inline diff:
From eb099b2b0c341995da51ff584aa2394cad37971c Mon Sep 17 00:00:00 2001
From: Roberto Mello <[email protected]>
Date: Tue, 24 Mar 2026 19:20:10 -0600
Subject: [PATCH v3] Fix pg_publication_tables to return NULL attnames for
all-column publications
Previously, pg_get_publication_tables() synthesized a column list even
when no explicit column list was specified in the publication. This made
it impossible for consumers of pg_publication_tables to distinguish
between "all columns are published" (new columns will automatically be
replicated) and an explicit column list (only listed columns are
replicated).
Worse, the subscriber-side workaround in fetch_remote_table_info()
compared array_length(gpt.attrs, 1) against pg_class.relnatts to detect
the "all columns" case, but relnatts includes dropped columns while the
synthesized list excluded them, so tables with dropped columns were
misidentified as having an explicit column list.
Fix by simply leaving attrs as NULL when no column list was specified
(prattrs is NULL) in the publication catalog. This is consistent with
how prattrs itself is stored and removes the need for the relnatts
heuristic in tablesync.c.
The real replication column filtering (which must account for dropped
columns, generated columns, and pub->pubgencols_type) is performed
downstream in pgoutput.c by pub_form_cols_map() and
check_and_fetch_column_list(), both of which are unchanged by this
patch.
---
doc/src/sgml/system-views.sgml | 9 ++-
src/backend/catalog/pg_publication.c | 52 +++---------
src/backend/replication/logical/tablesync.c | 9 +--
src/test/regress/expected/publication.out | 90 +++++++++++++++++++--
src/test/regress/sql/publication.sql | 59 ++++++++++++++
5 files changed, 160 insertions(+), 59 deletions(-)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 9ee1a2bfc6a..6c926860e41 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -2712,9 +2712,12 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
(references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attname</structfield>)
</para>
<para>
- Names of table columns included in the publication. This contains all
- the columns of the table when the user didn't specify the column list
- for the table.
+ Names of the table columns included in the publication, or NULL if
+ no explicit column list was specified for the table. When NULL, all
+ current and future columns of the table are published; new columns
+ added to the table will automatically be replicated. When non-NULL,
+ only the listed columns are replicated, and newly added columns will
+ not appear until the publication is explicitly altered.
</para></entry>
</row>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index c92ff3f51c3..a2fa4906f08 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1439,49 +1439,15 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
nulls[3] = true;
}
- /* Show all columns when the column list is not specified. */
- if (nulls[2])
- {
- Relation rel = table_open(relid, AccessShareLock);
- int nattnums = 0;
- int16 *attnums;
- TupleDesc desc = RelationGetDescr(rel);
- int i;
-
- attnums = palloc_array(int16, desc->natts);
-
- for (i = 0; i < desc->natts; i++)
- {
- Form_pg_attribute att = TupleDescAttr(desc, i);
-
- if (att->attisdropped)
- continue;
-
- if (att->attgenerated)
- {
- /* We only support replication of STORED generated cols. */
- if (att->attgenerated != ATTRIBUTE_GENERATED_STORED)
- continue;
-
- /*
- * User hasn't requested to replicate STORED generated
- * cols.
- */
- if (pub->pubgencols_type != PUBLISH_GENCOLS_STORED)
- continue;
- }
-
- attnums[nattnums++] = att->attnum;
- }
-
- if (nattnums > 0)
- {
- values[2] = PointerGetDatum(buildint2vector(attnums, nattnums));
- nulls[2] = false;
- }
-
- table_close(rel, AccessShareLock);
- }
+ /*
+ * When no column list is specified (prattrs is NULL), we leave
+ * attrs as NULL rather than synthesizing a list of all current
+ * columns. This allows consumers of pg_publication_tables to
+ * distinguish between "all columns are published" (attrs IS
+ * NULL -- new columns will automatically be replicated) and an
+ * explicit column list (attrs IS NOT NULL -- only listed columns
+ * are replicated).
+ */
rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index f49a4852ecb..5f6d892e595 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -799,13 +799,10 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
*/
resetStringInfo(&cmd);
appendStringInfo(&cmd,
- "SELECT DISTINCT"
- " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
- " THEN NULL ELSE gpt.attrs END)"
+ "SELECT DISTINCT gpt.attrs"
" FROM pg_publication p,"
- " LATERAL pg_get_publication_tables(p.pubname) gpt,"
- " pg_class c"
- " WHERE gpt.relid = %u AND c.oid = gpt.relid"
+ " LATERAL pg_get_publication_tables(p.pubname) gpt"
+ " WHERE gpt.relid = %u"
" AND p.pubname IN ( %s )",
lrel->remoteid,
pub_names->data);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index a220f48b285..4a25e9654f3 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -2072,7 +2072,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2081,7 +2081,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2089,7 +2089,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2098,7 +2098,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch2 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2107,7 +2107,7 @@ CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
-- Table publication that includes both the parent table and the child table
@@ -2115,7 +2115,7 @@ ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+------------+----------+-----------
- pub | sch2 | tbl1_part1 | {a} |
+ pub | sch2 | tbl1_part1 | |
(1 row)
DROP PUBLICATION pub;
@@ -2130,7 +2130,7 @@ CREATE PUBLICATION pub FOR TABLES IN SCHEMA sch1 WITH (PUBLISH_VIA_PARTITION_ROO
SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
- pub | sch1 | tbl1 | {a} |
+ pub | sch1 | tbl1 | |
(1 row)
RESET client_min_messages;
@@ -2139,6 +2139,82 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+ pubname | all_columns
+------------+-------------
+ pub_cols | f
+ pub_nocols | t
+(2 rows)
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+ pubname | attnames
+----------+-----------
+ pub_cols | {id,name}
+(1 row)
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+ pubname | all_columns
+-------------+-------------
+ pub_dropped | t
+(1 row)
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+-- Test that two publications on the same table, one without a column list
+-- and one with an explicit list of all columns, produce distinguishable
+-- rows in pg_publication_tables. On the subscriber side this difference
+-- would now correctly trigger "cannot use different column lists" during
+-- tablesync.
+CREATE TABLE pub_conflict_test (id int, name text);
+CREATE PUBLICATION pub_all FOR TABLE pub_conflict_test;
+CREATE PUBLICATION pub_explicit FOR TABLE pub_conflict_test (id, name);
+-- The two publications should produce different attnames values:
+-- pub_all -> NULL (all current and future columns)
+-- pub_explicit -> {id,name} (only these columns)
+SELECT pubname, attnames, attnames IS NULL AS is_all_columns
+ FROM pg_publication_tables
+ WHERE tablename = 'pub_conflict_test' ORDER BY pubname;
+ pubname | attnames | is_all_columns
+--------------+-----------+----------------
+ pub_all | | t
+ pub_explicit | {id,name} | f
+(2 rows)
+
+-- Confirm they are DISTINCT (2 rows, not 1), which is what causes the
+-- subscriber-side conflict check to fire.
+SELECT COUNT(DISTINCT attnames IS NULL) AS distinct_attnames_nullability
+ FROM pg_publication_tables
+ WHERE tablename = 'pub_conflict_test';
+ distinct_attnames_nullability
+-------------------------------
+ 2
+(1 row)
+
+DROP PUBLICATION pub_all;
+DROP PUBLICATION pub_explicit;
+DROP TABLE pub_conflict_test;
+-- ======================================================
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 22e0a30b5c7..b89cd416b17 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -1327,6 +1327,65 @@ DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
-- ======================================================
+-- Test that pg_publication_tables distinguishes between tables with
+-- an explicit column list and tables without one (attnames should be
+-- NULL when no column list was specified).
+CREATE TABLE pub_col_test (id int, name text, status text);
+CREATE PUBLICATION pub_nocols FOR TABLE pub_col_test;
+CREATE PUBLICATION pub_cols FOR TABLE pub_col_test (id, name);
+
+-- Without column list: attnames should be NULL
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' ORDER BY pubname;
+
+-- With column list: attnames should list specific columns
+SELECT pubname, attnames FROM pg_publication_tables
+ WHERE tablename = 'pub_col_test' AND attnames IS NOT NULL ORDER BY pubname;
+
+DROP PUBLICATION pub_nocols;
+DROP PUBLICATION pub_cols;
+DROP TABLE pub_col_test;
+
+-- Test that a table with a dropped column still shows attnames as NULL
+-- when no explicit column list was specified. The old implementation
+-- compared the synthesized column count against relnatts, but relnatts
+-- includes dropped columns, so the heuristic was wrong for this case.
+CREATE TABLE pub_dropped_test (id int, dropped_col text, name text);
+ALTER TABLE pub_dropped_test DROP COLUMN dropped_col;
+CREATE PUBLICATION pub_dropped FOR TABLE pub_dropped_test;
+
+SELECT pubname, attnames IS NULL AS all_columns FROM pg_publication_tables
+ WHERE tablename = 'pub_dropped_test';
+
+DROP PUBLICATION pub_dropped;
+DROP TABLE pub_dropped_test;
+
+-- Test that two publications on the same table, one without a column list
+-- and one with an explicit list of all columns, produce distinguishable
+-- rows in pg_publication_tables. On the subscriber side this difference would
+-- now correctly trigger "cannot use different column lists" during tablesync.
+CREATE TABLE pub_conflict_test (id int, name text);
+CREATE PUBLICATION pub_all FOR TABLE pub_conflict_test;
+CREATE PUBLICATION pub_explicit FOR TABLE pub_conflict_test (id, name);
+
+-- The two publications should produce different attnames values:
+-- pub_all -> NULL (all current and future columns)
+-- pub_explicit -> {id,name} (only these columns)
+SELECT pubname, attnames, attnames IS NULL AS is_all_columns
+ FROM pg_publication_tables
+ WHERE tablename = 'pub_conflict_test' ORDER BY pubname;
+
+-- Confirm they are DISTINCT (2 rows, not 1), which is what causes the
+-- subscriber-side conflict check to fire.
+SELECT COUNT(DISTINCT attnames IS NULL) AS distinct_attnames_nullability
+ FROM pg_publication_tables
+ WHERE tablename = 'pub_conflict_test';
+
+DROP PUBLICATION pub_all;
+DROP PUBLICATION pub_explicit;
+DROP TABLE pub_conflict_test;
+-- ======================================================
+
-- Test the 'publish_generated_columns' parameter with the following values:
-- 'stored', 'none'.
SET client_min_messages = 'ERROR';
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-26 00:48 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-27 04:06 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Ajin Cherian <[email protected]>
2026-03-27 17:27 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-27 19:53 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-30 04:42 ` Ajin Cherian <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Ajin Cherian @ 2026-03-30 04:42 UTC (permalink / raw)
To: Roberto Mello <[email protected]>; +Cc: Peter Smith <[email protected]>; pgsql-hackers
On Sat, Mar 28, 2026 at 6:54 AM Roberto Mello <[email protected]> wrote:
>
> On Fri, Mar 27, 2026 at 11:27 AM Roberto Mello <[email protected]> wrote:
>>
>> On Thu, Mar 26, 2026 at 10:07 PM Ajin Cherian <[email protected]> wrote:
>>>
>>>
>>> One observation from reviewing the patch: the test suite covers the
>>> partial column list and dropped column cases well, but is missing a
>>> test for the two-publication conflict scenario (one pub with no list +
>>> one pub with an explicit list of all columns on the same table). That
>>> is the breaking change called out in the original report and probably
>>> deserves its own regression test with a comment explaining the
>>> expected behavior change for users in that configuration.
>>> Patch LGTM otherwise.
>>
>>
>> Good point. I know I worked on that, so I must have accidentally dropped it from the patch.
>>
>> Will prepare a revision and submit.
>
>
> Here's v3 of the patch with the missing test.
LGTM
regards,
Ajin Cherian
Fujitsu Australia
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-30 07:20 ` Amit Kapila <[email protected]>
2026-03-30 18:32 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2 siblings, 1 reply; 12+ messages in thread
From: Amit Kapila @ 2026-03-30 07:20 UTC (permalink / raw)
To: Roberto Mello <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>
On Thu, Mar 26, 2026 at 1:21 AM Roberto Mello <[email protected]> wrote:
>
> 2. b7ae0395369 (01/2023) Amit Kapila (author: Shi yu)
> Ignore dropped and generated columns from the column list
>
> This is the commit that introduced the synthesis loop. It "solved" a problem
> where a table in two publications (one with a column list naming all columns,
> one without a column list) was erroring with "cannot use different column lists"
> because one returned NULL and the other returned an int2vector. The fix was
> to synthesize a column list when none was specified, filtering out dropped
> and generated columns, so that both publications would produce identical
> int2vectors and DISTINCT would collapse them to one row.
>
> But this synthesis made it impossible to distinguish "all columns" from
> "explicit list of all columns" in the view. And the relnatts heuristic from
> fd0b9dcebda - which was supposed to reverse the synthesis - was broken from
> the start because relnatts includes dropped columns while the synthesized list
> excludes them.
>
> The synthesis in b7ae0395369 tried to make "no column list" and "explicit list
> of all columns" look identical. But they have genuinely different
> semantics:
>
> - No column list (NULL): all current and future columns are replicated.
> ALTER TABLE ADD COLUMN automatically replicates the new column.
> - Explicit full list: only the named columns are replicated. New columns
> are NOT replicated until the publication is explicitly altered.
>
> By making them indistinguishable, the synthesis hid a real conflict from users
> who had a table in two publications with different column semantics on the
> same subscription. I am proposing a fix that restores the distinction and correctly
> (IMO) surfaces this conflict.
>
I would like to understand why shall we consider this as a conflict?
IIRC, we tried to ensure that if in future new columns get added to
the relation and the same is not updated in the explicit column list
then it will result in error.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-30 07:20 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
@ 2026-03-30 18:32 ` Roberto Mello <[email protected]>
2026-03-31 08:38 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Roberto Mello @ 2026-03-30 18:32 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>
On Mon, Mar 30, 2026 at 1:21 AM Amit Kapila <[email protected]> wrote:
> On Thu, Mar 26, 2026 at 1:21 AM Roberto Mello <[email protected]>
> wrote:
>
> > By making them indistinguishable, the synthesis hid a real conflict from
> users
> > who had a table in two publications with different column semantics on
> the
> > same subscription. I am proposing a fix that restores the distinction
> and correctly
> > (IMO) surfaces this conflict.
> >
>
> I would like to understand why shall we consider this as a conflict?
> IIRC, we tried to ensure that if in future new columns get added to
> the relation and the same is not updated in the explicit column list
> then it will result in error.
>
Hi Amit,
The conflict exists because the two publications have different contracts
about future
schema changes, and the subscriber has no way to honor both simultaneously.
For example:
CREATE TABLE t (id int, name text);
CREATE PUBLICATION pub_a FOR TABLE t; -- no column list
CREATE PUBLICATION pub_b FOR TABLE t (id, name); -- explicit list
CREATE SUBSCRIPTION sub CONNECTION '...' PUBLICATION pub_a, pub_b;
At this point both publications replicate the same columns. But after:
ALTER TABLE t ADD COLUMN email text;
pub_a now replicates {id, name, email} (automatically, because no column
list
means all current and future columns), while pub_b still replicates {id,
name}
(the explicit list hasn't been altered).
The subscriber receives WAL from both publications for the same table.
Which column set should it apply? It cannot apply both as they disagree on
whether email is included. This is exactly the situation the "cannot use
different column lists" check was designed to prevent.
The current code suppresses this error by making the two cases look
identical at query time. But the underlying catalog still stored NULL
for pub_a and {1,2} for pub_b, and the actual replication behavior at WAL
decode time (in pgoutput.c) still treated them differently. So the conflict
was real
but hidden from the check that was supposed to detect it.
To put it another way: the check's purpose is to ensure a single consistent
column set for each table across all publications on a subscription. Two
publications that will diverge on the next ALTER TABLE ADD COLUMN
do not provide that guarantee. Surfacing the conflict at subscription
creation / refresh time - before the schema change happens - is better than
discovering it after, when the subscriber receives incompatible column sets
and replication breaks.
For users who currently have this configuration, the fix is straightforward:
either drop the explicit column list from pub_b (so both mean "all
columns"),
or keep the explicit list and use a separate subscription.
Roberto Mello
Snowflake
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-30 07:20 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
2026-03-30 18:32 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-31 08:38 ` Amit Kapila <[email protected]>
2026-03-31 21:54 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Amit Kapila @ 2026-03-31 08:38 UTC (permalink / raw)
To: Roberto Mello <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>
On Tue, Mar 31, 2026 at 12:02 AM Roberto Mello <[email protected]> wrote:
>
> The conflict exists because the two publications have different contracts about future
> schema changes, and the subscriber has no way to honor both simultaneously.
>
> For example:
>
> CREATE TABLE t (id int, name text);
> CREATE PUBLICATION pub_a FOR TABLE t; -- no column list
> CREATE PUBLICATION pub_b FOR TABLE t (id, name); -- explicit list
> CREATE SUBSCRIPTION sub CONNECTION '...' PUBLICATION pub_a, pub_b;
>
> At this point both publications replicate the same columns. But after:
>
> ALTER TABLE t ADD COLUMN email text;
>
> pub_a now replicates {id, name, email} (automatically, because no column list
> means all current and future columns), while pub_b still replicates {id, name}
> (the explicit list hasn't been altered).
>
> The subscriber receives WAL from both publications for the same table.
> Which column set should it apply? It cannot apply both as they disagree on
> whether email is included. This is exactly the situation the "cannot use
> different column lists" check was designed to prevent.
>
I think we need to consider the cases where current permissive
behavior helps. For example, consider the cases where the schema is
static. Now let us consider another case where a user would actually
need to define such publication combinations for a subscription. One
of the more common ways this conflict happens is accidental: User has
pub_1 for TABLE t (col1, col2). User later decides to replicate the
entire database to a new subscription and creates pub_2 FOR ALL
TABLES. User adds pub_2 to the subscription. Currently, the user can
add pub_2 and then later realize they need to drop pub_1 to clean
things up. If ALTER SUBSCRIPTION blocks this, the user is stuck in a
Catch-22 where they can't add the "All Tables" publication because a
single specific table has a column list. They would have to drop the
specific publication first, potentially losing replication coverage
for that table during the gap.
Now, considering the other cases where replication later ERRORs out
(like the one you mentioned) when we allow such combinations, we can
give a WARNING at the time subscriber DDLs when they lead to such
combinations.
> The current code suppresses this error by making the two cases look
> identical at query time. But the underlying catalog still stored NULL
> for pub_a and {1,2} for pub_b, and the actual replication behavior at WAL
> decode time (in pgoutput.c) still treated them differently. So the conflict was real
> but hidden from the check that was supposed to detect it.
>
> To put it another way: the check's purpose is to ensure a single consistent
> column set for each table across all publications on a subscription. Two
> publications that will diverge on the next ALTER TABLE ADD COLUMN
> do not provide that guarantee. Surfacing the conflict at subscription
> creation / refresh time - before the schema change happens - is better than
> discovering it after, when the subscriber receives incompatible column sets
> and replication breaks.
>
> For users who currently have this configuration, the fix is straightforward:
> either drop the explicit column list from pub_b (so both mean "all columns"),
> or keep the explicit list and use a separate subscription.
>
Or alter the subscription to drop the publication which in your case
would be pub_b.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-30 07:20 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
2026-03-30 18:32 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-31 08:38 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Amit Kapila <[email protected]>
@ 2026-03-31 21:54 ` Roberto Mello <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Roberto Mello @ 2026-03-31 21:54 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>
On Tue, Mar 31, 2026 at 2:38 AM Amit Kapila <[email protected]> wrote:
> On Tue, Mar 31, 2026 at 12:02 AM Roberto Mello <[email protected]>
> wrote:
> >
> > The subscriber receives WAL from both publications for the same table.
> > Which column set should it apply? It cannot apply both as they disagree
> on
> > whether email is included. This is exactly the situation the "cannot use
> > different column lists" check was designed to prevent.
> >
>
> I think we need to consider the cases where current permissive
> behavior helps. For example, consider the cases where the schema is
>
I'm sorry but you completely dismissed the points that I brought up,
addressing
none of the concerns, and went on a tangent about "permissive behavior".
Permissive behavior is fine and dandy until it bites you in the @$$, and
there's
a line where permissive is wrong, bad, or dangerous. I pointed out why I
think
the current behavior is wrong, potentially dangerous. MySQL is king of
permissive
behavior but I don't think that's much to brag about. PostgreSQL
historically
favored correctness over permissiveness.
The deeper issue is that the "permissive" behavior isn't actually
permissive: it's
silently inconsistent. The two publications have different replication
contracts stored
in the catalog (prattrs = NULL vs prattrs = {1,2}), and pgoutput.c honors
that difference
at WAL decode time. The old code just hid the difference from the check
that was
supposed to detect it.
static. Now let us consider another case where a user would actually
> need to define such publication combinations for a subscription. One
> of the more common ways this conflict happens is accidental: User has
> pub_1 for TABLE t (col1, col2). User later decides to replicate the
> entire database to a new subscription and creates pub_2 FOR ALL
> TABLES. User adds pub_2 to the subscription. Currently, the user can
> add pub_2 and then later realize they need to drop pub_1 to clean
>
How would they "realize" that if the view is showing them the exact
same data for the two publications?
> things up. If ALTER SUBSCRIPTION blocks this, the user is stuck in a
> Catch-22 where they can't add the "All Tables" publication because a
> single specific table has a column list. They would have to drop the
> specific publication first, potentially losing replication coverage
> for that table during the gap.
>
I don't think it actually traps the user. The sequence that works today and
would continue to work:
-- Starting from sub has pub_1 with explicit column list
ALTER PUBLICATION pub_1 DROP TABLE t; -- on publisher
ALTER SUBSCRIPTION sub REFRESH PUBLICATION; -- on subscriber
-- Now add pub_2 FOR ALL TABLES
ALTER SUBSCRIPTION sub ADD PUBLICATION pub_2;
Or even simpler, just drop the column list from pub_1 before adding pub_2:
ALTER PUBLICATION pub_1 SET TABLE t; -- removes the column
list
ALTER SUBSCRIPTION sub ADD PUBLICATION pub_2;
There's no gap in replication coverage in either case because the
subscription is still
active throughout. The table remains subscribed via pub_1 until pub_2 takes
over.
For the static schema case: if the schema never changes, the two
publications do produce
identical column sets and there's no practical problem. But the publication
system doesn't
know the schema is static. It has to handle the general case. And the
general case is that
NULL means "all current and future columns" while an explicit list means
"exactly these,
nothing more." Those are different things (contracts) and being
"permissive" with interpretation
of what those contracts mean in my head versus someone else's could be
dangerous.
> Now, considering the other cases where replication later ERRORs out
> (like the one you mentioned) when we allow such combinations, we can
> give a WARNING at the time subscriber DDLs when they lead to such
> combinations.
>
A WARNING at ALTER SUBSCRIPTION time would be better than silence, but it
still
allows the subscriber into a state where a future ALTER TABLE ADD COLUMN on
the
publisher will cause replication to break. At that point the user gets the
ERROR anyway,
but now it's during replication rather than at setup time, which is harder
to diagnose and
recover from. Erroring early, when the user is actively making the change
and can fix it,
is the safer default.
That said, if the consensus is that the migration path is too disruptive, a
middle ground
could be to ERROR by default but provide a subscription-level option to
downgrade it to
WARNING. For the reasons I pointed out, I think the right call is to keep
the ERROR as
the default though, since it catches a real inconsistency.
Roberto Mello
Snowflake
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
@ 2026-03-31 22:55 ` David G. Johnston <[email protected]>
2026-03-31 23:23 ` Re: pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2026-03-31 22:55 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Roberto Mello <[email protected]>; PostgreSQL Developers <[email protected]>
On Thursday, March 26, 2026, Álvaro Herrera <[email protected]> wrote:
> On 2026-Mar-25, Roberto Mello wrote:
>
> > The problem is that pg_get_publication_tables() in pg_publication.c
> > (the set-returning function backing the pg_publication_tables view)
> > erases this distinction. When prattrs is NULL, it opens the table,
> > iterates all eligible attributes, and builds a synthetic int2vector of
> > all current columns. The view then shows the same attnames output for
> > both cases.
>
> I agree that this is wrong. This distinction was explicitly discussed
> when the column-list feature was developed. I don't think we can
> backpatch the fix though, out of fear that we would break something for
> existing users; but we should definitely fix it for pg19.
>
IIUC the wording for v18 and earlier should read more like:
“Subscriptions having several publications in which the same table has
different sets of columns published are not supported.”
The claim that this defacto behavior is a bug needing to be fixed is now
before us (there is no disagreement that the physical column lists are
different - null vs non-null). My cursory take at this leads me to believe
we should accept what actually got implemented and not call this a bug to
be fixed (aside from the docs).
That the catalog is the only official source of truth regarding the
physical column list distinction, and the function represents the logical
“set of columns actually seen”, makes sense seen in that light.
I haven’t dived deep enough to understand whether there is C code issue
that needs to be resolved. Or whether we can make dealing with this more
user-friendly given this constraint.
Removing the limitation would seem more appealing if we are going to make a
change. The obvious answer of “union all sets of columns published for a
table and replicate those” would be the simplest to document though I
suspect the current implementation basically chooses one of the
publications to pull from which makes that difficult in the general case.
I do kinda wonder why we need to enforce any kind of error so long as one
of the publications for a given table includes all columns though. Or even
is a proper superset to be a tiny bit more flexible. A technically
uninformed wondering but still.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_publication_tables: return NULL attnames when no column list is specified
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-31 22:55 ` Re: pg_publication_tables: return NULL attnames when no column list is specified David G. Johnston <[email protected]>
@ 2026-03-31 23:23 ` Roberto Mello <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Roberto Mello @ 2026-03-31 23:23 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; PostgreSQL Developers <[email protected]>
On Tue, Mar 31, 2026 at 4:55 PM David G. Johnston <
[email protected]> wrote:
>
> IIUC the wording for v18 and earlier should read more like:
>
> “Subscriptions having several publications in which the same table has
> different sets of columns published are not supported.”
>
> The claim that this defacto behavior is a bug needing to be fixed is now
> before us (there is no disagreement that the physical column lists are
> different - null vs non-null). My cursory take at this leads me to believe
> we should accept what actually got implemented and not call this a bug to
> be fixed (aside from the docs).
>
> That the catalog is the only official source of truth regarding the
> physical column list distinction, and the function represents the logical
> “set of columns actually seen”, makes sense seen in that light.
>
The internal code was designed around the NULL/non-NULL distinction. The
SRF
pg_get_publication_tables() is the one place that erased it, and the CASE
WHEN relnatts heuristic
in tablesync was an attempt to reverse that erasure, but it's demonstrably
broken for
tables with dropped columns. That seems like a bug to me regardless of how
we feel about the
behavioral question, but I have no objections to not calling it a bug. I'm
confident the
best thing was intended when the code was committed and hindsight is always
20/20.
I haven’t dived deep enough to understand whether there is C code issue
> that needs to be resolved. Or whether we can make dealing with this more
> user-friendly given this constraint.
>
> Removing the limitation would seem more appealing if we are going to make
> a change. The obvious answer of “union all sets of columns published for a
> table and replicate those” would be the simplest to document though I
> suspect the current implementation basically chooses one of the
> publications to pull from which makes that difficult in the general case.
> I do kinda wonder why we need to enforce any kind of error so long as one
> of the publications for a given table includes all columns though. Or even
> is a proper superset to be a tiny bit more flexible. A technically
> uninformed wondering but still.
>
The superset idea would be a significant change to how the WAL output
plugin works. pgoutput.c
doesn't have a concept of "this publication contributes columns X and that
publication contributes
columns Y, send the union."
This would be an interesting improvement but it's a larger project... it
would touch pgoutput, tablesync,
and the subscriber's relation mapping. My patch is trying to fix the
immediate inconsistency (the view
lying about the catalog state, and the broken relnatts heuristic) without
changing the replication protocol
or column merging behavior.
If the view shows {id, name} for both publications, a DBA planning a schema
migration has no way to
know that ALTER TABLE ADD COLUMN email will be replicated for one
publication but not the
other. The catalog stores the information needed to make this
determination, the view actively hides it.
NULL in the view would tell the DBA "this publication replicates
everything, including future columns"
which is actionable information.
Roberto Mello
Snowflake
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-03-31 23:23 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-25 19:50 pg_publication_tables: return NULL attnames when no column list is specified Roberto Mello <[email protected]>
2026-03-26 00:48 ` Roberto Mello <[email protected]>
2026-03-27 04:06 ` Ajin Cherian <[email protected]>
2026-03-27 17:27 ` Roberto Mello <[email protected]>
2026-03-27 19:53 ` Roberto Mello <[email protected]>
2026-03-30 04:42 ` Ajin Cherian <[email protected]>
2026-03-30 07:20 ` Amit Kapila <[email protected]>
2026-03-30 18:32 ` Roberto Mello <[email protected]>
2026-03-31 08:38 ` Amit Kapila <[email protected]>
2026-03-31 21:54 ` Roberto Mello <[email protected]>
2026-03-31 22:55 ` David G. Johnston <[email protected]>
2026-03-31 23:23 ` Roberto Mello <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox