Subject: pg_publication_tables: return NULL attnames when no column list is specified

From: Roberto Mello <[email-masked]>
Date: Wed, 25 Mar 2026 13:50:45 -0600

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

---

From: Roberto Mello <[email-masked]>
Date: Wed, 25 Mar 2026 18:48:34 -0600

On Wed, Mar 25, 2026 at 5:32 PM Peter Smith <[email-masked]> 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

---

From: Ajin Cherian <[email-masked]>
Date: Fri, 27 Mar 2026 15:06:54 +1100

> 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

---

From: Roberto Mello <[email-masked]>
Date: Fri, 27 Mar 2026 11:27:16 -0600

> Tested the patch and the patch fixes the bug as described. My tests below:

Thank you for the review.

<snip>

> expected behavior change for users in that configuration.

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.

---

From: Roberto Mello <[email-masked]>
Date: Fri, 27 Mar 2026 13:53:45 -0600

On Fri, Mar 27, 2026 at 11:27 AM Roberto Mello <[email-masked]>
wrote:

> Good point. I know I worked on that, so I must have accidentally dropped
> it from the patch.
>

Here's v3 of the patch with the missing test.

Roberto Mello
Snowflake

---

From: Ajin Cherian <[email-masked]>
Date: Mon, 30 Mar 2026 15:42:48 +1100

On Sat, Mar 28, 2026 at 6:54 AM Roberto Mello <[email-masked]> wrote:
>
> On Fri, Mar 27, 2026 at 11:27 AM Roberto Mello <[email-masked]> wrote:
>>
>> Good point. I know I worked on that, so I must have accidentally dropped it from the patch.
>>

LGTM

regards,
Ajin Cherian
Fujitsu Australia

---

From: Amit Kapila <[email-masked]>
Date: Mon, 30 Mar 2026 12:50:47 +0530

On Thu, Mar 26, 2026 at 1:21 AM Roberto Mello <[email-masked]> wrote:
> 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"
> 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
> By making them indistinguishable, the synthesis hid a real conflict from users
> same subscription. I am proposing a fix that restores the distinction and correctly

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.

---

From: Roberto Mello <[email-masked]>
Date: Mon, 30 Mar 2026 12:32:17 -0600

> 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

---

From: Amit Kapila <[email-masked]>
Date: Tue, 31 Mar 2026 14:08:41 +0530

On Tue, Mar 31, 2026 at 12:02 AM Roberto Mello <[email-masked]> wrote:
>
> The conflict exists because the two publications have different contracts about future
> 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}

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.

> decode time (in pgoutput.c) still treated them differently. So the conflict was real
> either drop the explicit column list from pub_b (so both mean "all columns"),

Or alter the subscription to drop the publication which in your case
would be pub_b.

--
With Regards,
Amit Kapila.

---

From: Roberto Mello <[email-masked]>
Date: Tue, 31 Mar 2026 15:54:51 -0600

> 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.

> 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?

> 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.

> give a WARNING at the time subscriber DDLs when they lead to such

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

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 31 Mar 2026 15:55:48 -0700

On Thursday, March 26, 2026, Álvaro Herrera <[email-masked]> 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.

---

From: Roberto Mello <[email-masked]>
Date: Tue, 31 Mar 2026 17:23:11 -0600

> “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.

> 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

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