Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w7h3V-005bON-1J for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2026 21:55:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w7h3R-00DO1q-39 for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2026 21:55:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w7h3R-00DO1i-1n for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2026 21:55:22 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w7h3P-000000022FF-27iW for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2026 21:55:20 +0000 Received: by mail-pj1-x1035.google.com with SMTP id 98e67ed59e1d1-35d99bae2ebso3047653a91.3 for ; Tue, 31 Mar 2026 14:55:19 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1774994118; cv=none; d=google.com; s=arc-20240605; b=DMpXDz8F8DKmKHUTG6nAecmRUvA28NleceAovSAdGclaKWWr+m/8kJFairfwEQNF7V ZUdgGl9zmeNEUGN2fOvqK1pOWE9VJjEAxFhiV4ggtuQnvfEqgD2QA0+0C36K0/wHRxzg C/6LdfgNQPZZP6HoNBrCoXWimHUCdTj8pGBu+efp/VuRTlZu6/d/rHQzDTbzCGWZBl+k IqujUTGp0Jy84NDTJIxKEE5zZ6IpxfQEnJksXaApSS2OZ1VhLYuPmFdohQicyiM+T0dz z0y1xnPuIoEaX1o24+BmmIlTIRTzqXJAW3Ovx2F6JYfqdnqCSmuFbJpIhq466Il7Ayx1 qMSw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=7N3CFLOQKls9vMdQls5jvP37Xx+zMmL5XU9SGivTXM0=; fh=/k/uS/3EGigow8/nOxOmNqgJix1B5pRaNgIVDPRBhM4=; b=Rdnlp/Jr5JeB+i4MhhQ4Db3sNjZy4rN/htRlo28pNm84TbJo8j2Xpgrz2dCh7IxGJF suwUUidCW/fd+QHz80GxyofjEqByAjY1E8h0dTK+rm5dffgIufKFw06dSYXnR5mlmb0V ZWoKdcaZxfFoRYO9urUC1UxYaNqX262ndiv67apH39t5S7gB80tywVZ2BWSamRhm3hxI MO0JGppdPHPuaoGhhXd6oB8sATgLovodXiy0wG74lUg3g29BCuLKRtTm75umanJgCHpI kHjhmVw1vegzoTDZkvBg8c/aubO9PHlQDY0kSBWQ4Ta2Yxf/BRvZVyOCsb7ty343lKfO A1qg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1774994118; x=1775598918; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7N3CFLOQKls9vMdQls5jvP37Xx+zMmL5XU9SGivTXM0=; b=a9xCRFbMxrmmHvG9ICIxaweE6Il9fHhNps3M9bQJ5QEoqYX3QCzsilP7vVueGwyNdU ZbPGuLJzwx8wg/pJgwdgJ4z3uK7Lub9H83ISwNQiiSqZBk/3FbMC4YBYax4e62mBinOu 5sdRjhkrWotqiXCzOvBm+R+6YW6mCTxNAYDs3imlAHOnzcpi1VD3HPEK9uX/PGfavcVD YPwvkcyaHaJj4aS29H6lBea7lXb4LjMlxpzuZ8WKWJFmsyhSTECtxWKFndq48kh6PExz Hoqs9xgZ6oaGDuPVnVGHloM2ih+mIVQecGfOwLFxuBbz/aWyr6AmdLdhPF4SQZly6aN1 Ygmw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1774994118; x=1775598918; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=7N3CFLOQKls9vMdQls5jvP37Xx+zMmL5XU9SGivTXM0=; b=APrEQ+/vVadI/PsH97CxHt0HIjqTqaCduY/C9bO3Wy1tRqptHq5WqNj1V/UmnIyPAU hBKY0TLOlAfwgC57CIn7FJKeUIPTZNwgwGrAvWFfJULyaSn7NHaAV6ZJSpeF/rhAf77Q CXorXM7Yz3z5Eer57qWOztSKTer/En3Polr4MpFiimJN5k2GaSDgLrFxSzvqGVWKwqoJ 90IQ7bSuL/DmaFW5N7vxU+De62RbsxpQKeIHLNzsSXZRnyT0Or0jwjqKs2To1Z9ZqGTL IKu7FuIL/yY5JOVD5n6foOZNLl6hPQe5H9FFxiAs+8Nytasz0eS1t3YIkvMGFmCZokvq ZM+A== X-Gm-Message-State: AOJu0YyyPcpyeppYp7rHEj/l1JlUeK9DqPqTdBbJI28LiexrDehp/OZb klGQUDDv8sdV0o2FLvzMlc7RS2jM8CfsmPplUAz3FWIF6nSqASzZsyMY4adqP8F2uwhafWVt6IP jMp9Vf7uqtK/B4b4+HoIgmOxBy2xds/g= X-Gm-Gg: ATEYQzyeijWy32yvfaVtN9CmX02Gkw/iLpWVnw0fXDUt06CTMAU2jeNuRrnnuBo2bk+ V7XSbwBs4u1G7RYtLeuUzsUqHnquMwPx+/eeewzEKtAVsJuy6R47y+m58OF7QhO/p4zLc1dWwoC +lhp5MWZKnXJzoW/FgaI3FV93YT6+Owta5Rh7GWhO4owrMitJTKhFeORndUmWgc080BM7JU/lEy VYed2oAjTBqzWGh0QvhdtbyhRz4FrJLH6mw8EmX8FyYajIJqkTBd0+MSztNzTxepxDPqsEb/AIX S8OV7T2coRQ1RBdI5O0rjbVAzFrAUkyHTVamx2DOf0XFf2EtlHoiLsymo7ecHVU08zmDb7Y= X-Received: by 2002:a17:903:1a88:b0:2b2:539b:d29d with SMTP id d9443c01a7336-2b269ade174mr7451595ad.2.1774994117730; Tue, 31 Mar 2026 14:55:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Roberto Mello Date: Tue, 31 Mar 2026 15:54:51 -0600 X-Gm-Features: AQROBzD3q-wuoK0y-AGec8pWQiy3AIuDDX4Y0DHyvG1DIrPrlSgT7GgEaXomhG0 Message-ID: Subject: Re: pg_publication_tables: return NULL attnames when no column list is specified To: Amit Kapila Cc: PostgreSQL Developers Content-Type: multipart/alternative; boundary="000000000000dda6b7064e5905a5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000dda6b7064e5905a5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Mar 31, 2026 at 2:38=E2=80=AFAM Amit Kapila wrote: > On Tue, Mar 31, 2026 at 12:02=E2=80=AFAM Roberto Mello > 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 us= e > > 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 =3D NULL vs prattrs =3D {1,2}), and pgoutput.c hono= rs 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 --000000000000dda6b7064e5905a5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Mar 31, 2026 at = 2:38=E2=80=AFAM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Tue, Mar= 31, 2026 at 12:02=E2=80=AFAM Roberto Mello <roberto.mello@gmail.com> wrote: >
> The subscriber receives WAL from both publications for the same table.=
> Which column set should it apply? It cannot apply both as they disagre= e on
> whether email is included. This is exactly the situation the "can= not 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 w= ent on a tangent about "permissive behavior".

Permissive behavior is fine and dandy until it bites you in the=C2=A0= @$$, and there's
a line=C2=A0where permissive is wrong, bad, = or dangerous. I pointed out why I think
the current behavior is w= rong, potentially dangerous. MySQL is king of permissive
behavior= but I don't think that's much to brag about. PostgreSQL historical= ly
favored correctness over permissiveness.

<= div>The deeper issue is that the "permissive" behavior isn't = actually permissive: it's
silently inconsistent. The two publication= s have different replication contracts stored
in the catalog (pra= ttrs =3D NULL vs prattrs =3D {1,2}), and pgoutput.c honors that difference<= /div>
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 v= iew is showing them the exact
same data for the two publications?=
=C2=A0
things up. If ALTER SUBSCRIPTION blocks this, the user is stuck in a
Catch-22 where they can't add the "All Tables" publication be= cause 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 t= hink it actually traps the user. The sequence that works today and
would continue to work:

=C2=A0 =C2=A0 -- Starting from sub has pub_1 with explicit column = list
=C2=A0 =C2=A0 ALTER PUBLICATION pub_1 DROP TABLE t; =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0-- on publisher
=C2=A0 =C2=A0 ALTER SUBSCRIPTION sub= REFRESH PUBLICATION; =C2=A0 =C2=A0-- on subscriber
=C2=A0 =C2=A0 -- Now= add pub_2 FOR ALL TABLES
=C2=A0 =C2=A0 ALTER SUBSCRIPTION sub ADD PUBLI= CATION pub_2;

Or even simpler, just drop the column list from pub_1 = before adding pub_2:

=C2=A0 =C2=A0 ALTER PUBLICATION pub_1 SET TABLE= t; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- removes the column list
=C2=A0= =C2=A0 ALTER SUBSCRIPTION sub ADD PUBLICATION pub_2;

There's no= gap in replication coverage in either case because the subscription is sti= ll
active throughout. The table remains sub= scribed via pub_1 until pub_2 takes over.

For the static sche= ma case: if the schema never changes, the two publications do produce
=
identical column sets and there's no practic= al problem. But the publication system doesn't
know the schema is static. It has to handle the general case. And t= he general case is that
NULL means "al= l current and future columns" while an explicit list means "exact= ly these,
nothing more." Those are dif= ferent things (contracts) and being "permissive" with interpretat= ion
of what those contracts mean in my head= versus someone else's could be dangerous.
=C2=A0
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 SUBSCR= IPTION 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 tha= n at setup time, which is harder to diagnose and
recover from. Er= roring 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 o= ption to downgrade it to
WARNING. For the r= easons I pointed out, I think the right call is to keep the ERROR as
<= div class=3D"gmail_quote">the default though, since it catches a real incon= sistency.


Roberto Mello
Snowflake=C2=A0
--000000000000dda6b7064e5905a5--