MIME-Version: 1.0
References: 
 <CAKz==b+k7OwOib__LgfDadUzW9jFrbQ9FqdERpsufi8d7F=vkQ@mail.gmail.com>
 <CAA4eK1KwFKFYnXqYm22oMdQCpLxaLp4ch=YBVUkvGmiA=AUXjA@mail.gmail.com>
 <CAKz==bJwd79+TMOgaxG2jBCoz8b=Nt3v3RR=fA29A=0NhFhcPQ@mail.gmail.com>
In-Reply-To: 
 <CAKz==bJwd79+TMOgaxG2jBCoz8b=Nt3v3RR=fA29A=0NhFhcPQ@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Tue, 31 Mar 2026 14:08:41 +0530
Message-ID: 
 <CAA4eK1JTnbKyXdjYZQqgouZKULZDOEyr_-7tTxh1eg0quOYUwQ@mail.gmail.com>
Subject: Re: pg_publication_tables: return NULL attnames when no column list
 is specified
To: Roberto Mello <roberto.mello@gmail.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1JTnbKyXdjYZQqgouZKULZDOEyr_-7tTxh1eg0quOYUwQ%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 31, 2026 at 12:02=E2=80=AFAM Roberto Mello <roberto.mello@gmail=
.com> wrote:
>
> The conflict exists because the two publications have different contracts=
 about future
> schema changes, and the subscriber has no way to honor both simultaneousl=
y.
>
> 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 o=
n
> 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 confli=
ct 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 consiste=
nt
> 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 th=
an
> discovering it after, when the subscriber receives incompatible column se=
ts
> and replication breaks.
>
> For users who currently have this configuration, the fix is straightforwa=
rd:
> either drop the explicit column list from pub_b (so both mean "all column=
s"),
> 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.