Two issues with version checks in CREATE SUBSCRIPTION

Started by Fujii Masao21 days ago6 messages

masao.fujii@gmail.com

21 days ago

2 attachment(s)

Hi,

While looking at subscription-related code, I noticed two issues related to
version checks.

if (walrcv_server_version(wrconn) < 19000)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot enable retain_dead_tuples if the
publisher is running a version earlier than PostgreSQL 19"));

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

Second, CREATE SUBSCRIPTION with copy_data=true and origin='none' currently
fails when the publisher is running a version earlier than v19, although
this combination should be supported. The failure occurs because the command
issues a query calling pg_get_publication_sequences on the publisher,
which does not exist before v19. The attached 0002 patch fixes this
by skipping that query when the publisher runs an older version.

Thoughts?

Regards,

--
Fujii Masao

Attachments:

v1-0001-Fix-version-check-for-retain_dead_tuples-subscrip.patchapplication/octet-stream; name=v1-0001-Fix-version-check-for-retain_dead_tuples-subscrip.patchDownload

From 7b2869702af0cde3182a58bf2677b156bab1ec76 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 23 Dec 2025 01:25:41 +0900
Subject: [PATCH v1 1/2] Fix version check for retain_dead_tuples subscription
 option.

The retain_dead_tuples subscription option is supported only when
the publisher runs PostgreSQL 19 or later. However, it could previously
be enabled even when the publisher was running an earlier version.

This was caused by check_pub_dead_tuple_retention() comparing
the publisher server version against 19000 instead of 190000.

Fix this typo so that the version check correctly enforces the PG19+
requirement.
---
 src/backend/commands/subscriptioncmds.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index abbcaff0838..f023dcbd6ad 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -2753,7 +2753,7 @@ check_pub_dead_tuple_retention(WalReceiverConn *wrconn)
 	bool		isnull;
 	bool		remote_in_recovery;
 
-	if (walrcv_server_version(wrconn) < 19000)
+	if (walrcv_server_version(wrconn) < 190000)
 		ereport(ERROR,
 				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
 				errmsg("cannot enable retain_dead_tuples if the publisher is running a version earlier than PostgreSQL 19"));
-- 
2.51.2

v1-0002-Avoid-calling-pg_get_publication_sequences-on-pre.patchapplication/octet-stream; name=v1-0002-Avoid-calling-pg_get_publication_sequences-on-pre.patchDownload

From 4fb942f85b24cd8c70541855c3cddc4b62ea91c1 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Tue, 23 Dec 2025 00:58:22 +0900
Subject: [PATCH v1 2/2] Avoid calling pg_get_publication_sequences on pre-PG19
 publishers.

CREATE SUBSCRIPTION with copy_data=true and origin='none' previously
failed when the publisher was running a version earlier than PostgreSQL 19,
although this combination should be supported.

The failure occurred because the command issued a query calling
pg_get_publication_sequences on the publisher, which is not available
before PG19.

Fix this by skipping that query when the publisher runs a version earlier
than PostgreSQL 19.
---
 src/backend/commands/subscriptioncmds.c | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index f023dcbd6ad..4e80eda0a25 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -2649,9 +2649,11 @@ check_publications_origin_sequences(WalReceiverConn *wrconn, List *publications,
 	/*
 	 * Enable sequence synchronization checks only when origin is 'none' , to
 	 * ensure that sequence data from other origins is not inadvertently
-	 * copied.
+	 * copied. This check is necessary if the publisher is running PG19 or
+	 * later, where logical replication sequence synchronization is supported.
 	 */
-	if (!copydata || pg_strcasecmp(origin, LOGICALREP_ORIGIN_NONE) != 0)
+	if (!copydata || pg_strcasecmp(origin, LOGICALREP_ORIGIN_NONE) != 0 ||
+		walrcv_server_version(wrconn) < 190000)
 		return;
 
 	initStringInfo(&cmd);
-- 
2.51.2

Amit Kapila

amit.kapila16@gmail.com

20 days ago

In reply to: Fujii Masao (#1)

Re: Two issues with version checks in CREATE SUBSCRIPTION

On Mon, Dec 22, 2025 at 10:57 PM Fujii Masao <masao.fujii@gmail.com> wrote:

While looking at subscription-related code, I noticed two issues related to
version checks.

if (walrcv_server_version(wrconn) < 19000)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot enable retain_dead_tuples if the
publisher is running a version earlier than PostgreSQL 19"));

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

Second, CREATE SUBSCRIPTION with copy_data=true and origin='none' currently
fails when the publisher is running a version earlier than v19, although
this combination should be supported. The failure occurs because the command
issues a query calling pg_get_publication_sequences on the publisher,
which does not exist before v19. The attached 0002 patch fixes this
by skipping that query when the publisher runs an older version.

The changes look good to me though I haven't tested the patches. So,
please feel free to push after the relevant test. Thanks for catching
and fixing these issues.

--
With Regards,
Amit Kapila.

Hayato Kuroda (Fujitsu)

kuroda.hayato@fujitsu.com

20 days ago

In reply to: Fujii Masao (#1)

RE: Two issues with version checks in CREATE SUBSCRIPTION

Dear Fujii-san,

Thanks for the patch! They basically look good to me.

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

One idea is to introduce a constant like RETAIN_DEAD_TUPLES_MIN_VERSION_NUM,
which avoids similar typo. Is it overengineering?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Fujii Masao

masao.fujii@gmail.com

20 days ago

In reply to: Hayato Kuroda (Fujitsu) (#3)

Re: Two issues with version checks in CREATE SUBSCRIPTION

On Tue, Dec 23, 2025 at 2:55 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:

Dear Fujii-san,

Thanks for the patch! They basically look good to me.

Thanks to Amit and Hayato for the review! I'll commit the patches.

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

One idea is to introduce a constant like RETAIN_DEAD_TUPLES_MIN_VERSION_NUM,
which avoids similar typo. Is it overengineering?

I don't think this would be a sufficient guard against typos. Even with
this approach, we could still introduce a mistake like the following,
for example:

#define RETAIN_DEAD_TUPLES_MIN_VERSION_NUM 19000

Also, there are many existing checks that compare against version constants
like 190000, so changing the code to use such a macro everywhere feels
like overkill to me.

Regards,

--
Fujii Masao

Shlok Kyal

shlok.kyal.oss@gmail.com

20 days ago

In reply to: Fujii Masao (#1)

Re: Two issues with version checks in CREATE SUBSCRIPTION

Hi Fujii-san,

On Mon, 22 Dec 2025 at 22:57, Fujii Masao <masao.fujii@gmail.com> wrote:

Hi,

While looking at subscription-related code, I noticed two issues related to
version checks.

if (walrcv_server_version(wrconn) < 19000)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot enable retain_dead_tuples if the
publisher is running a version earlier than PostgreSQL 19"));

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

I tested this. I created a publisher in PG17 and tried creating
subscriber in PG19.

Without Patch, I am able to create the subscription with
retain_dead_tuples = true.
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH (retain
_dead_tuples=true);
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

With 0001 Patch, this issue is resolved.
postgres=# CREATE SUBSCRIPTION sub2 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH
(retain_dead_tuples=true);
ERROR: cannot enable retain_dead_tuples if the publisher is running a
version earlier than PostgreSQL 19

Second, CREATE SUBSCRIPTION with copy_data=true and origin='none' currently
fails when the publisher is running a version earlier than v19, although
this combination should be supported. The failure occurs because the command
issues a query calling pg_get_publication_sequences on the publisher,
which does not exist before v19. The attached 0002 patch fixes this
by skipping that query when the publisher runs an older version.

I am able to reproduce this scenario.
I created a publisher in PG17 and tried creating subscriber in PG19.

Without Patch we are hitting the following error.
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH (origin='none');
ERROR: could not receive list of replicated sequences from the
publisher: ERROR: function pg_get_publication_sequences(name) does
not exist
LINE 3: LATERAL pg_get_publication_sequences(P.pubname) GPS
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

And I confirm that the 0002 patch resolves this.

Thanks,
Shlok Kyal

Fujii Masao

masao.fujii@gmail.com

19 days ago

In reply to: Shlok Kyal (#5)

Re: Two issues with version checks in CREATE SUBSCRIPTION

On Tue, Dec 23, 2025 at 7:18 PM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:

Hi Fujii-san,

On Mon, 22 Dec 2025 at 22:57, Fujii Masao <masao.fujii@gmail.com> wrote:

Hi,

While looking at subscription-related code, I noticed two issues related to
version checks.

if (walrcv_server_version(wrconn) < 19000)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot enable retain_dead_tuples if the
publisher is running a version earlier than PostgreSQL 19"));

First, in subscriptioncmds.c this check rejects enabling retain_dead_tuples
when the publisher is running an older version. However, the comparison uses
19000 as v19 value. Since server versions are encoded as 190000 for v19,
this appears to be a typo and allows the option to be enabled unexpectedly
on pre-v19 publishers. The attached 0001 patch fixes this by correcting
the version constant.

I tested this. I created a publisher in PG17 and tried creating
subscriber in PG19.

Without Patch, I am able to create the subscription with
retain_dead_tuples = true.
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH (retain
_dead_tuples=true);
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

With 0001 Patch, this issue is resolved.
postgres=# CREATE SUBSCRIPTION sub2 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH
(retain_dead_tuples=true);
ERROR: cannot enable retain_dead_tuples if the publisher is running a
version earlier than PostgreSQL 19

Second, CREATE SUBSCRIPTION with copy_data=true and origin='none' currently
fails when the publisher is running a version earlier than v19, although
this combination should be supported. The failure occurs because the command
issues a query calling pg_get_publication_sequences on the publisher,
which does not exist before v19. The attached 0002 patch fixes this
by skipping that query when the publisher runs an older version.

I am able to reproduce this scenario.
I created a publisher in PG17 and tried creating subscriber in PG19.

Without Patch we are hitting the following error.
postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
port=5432 dbname=postgres' PUBLICATION pub1 WITH (origin='none');
ERROR: could not receive list of replicated sequences from the
publisher: ERROR: function pg_get_publication_sequences(name) does
not exist
LINE 3: LATERAL pg_get_publication_sequences(P.pubname) GPS
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

And I confirm that the 0002 patch resolves this.

Thanks for the review and test! I've pushed the patches.

Regards,

--
Fujii Masao