Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Bug in ALTER SUBSCRIPTION ... SERVER / ... CONNECTION with broken
 old server
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <OS9PR01MB12149B54DEA148108C6FA5667F52D2@OS9PR01MB12149.jpnprd01.prod.outlook.com>
Date: Thu, 23 Apr 2026 11:37:42 +0800
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <DFBCC95A-8B02-47E0-8825-9E00B51FB786@gmail.com>
References: <D908370F-2695-4231-851D-17179A6A6F2A@gmail.com>
 <OS9PR01MB12149B54DEA148108C6FA5667F52D2@OS9PR01MB12149.jpnprd01.prod.outlook.com>
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DFBCC95A-8B02-47E0-8825-9E00B51FB786%40gmail.com>
Precedence: bulk


> On Apr 22, 2026, at 20:35, Hayato Kuroda (Fujitsu) =
<kuroda.hayato@fujitsu.com> wrote:
>=20
> Dear Chao,
>=20
>> I tested the v19 new feature CREATE SUBSCRIPTION ... SERVER =
yesterday, and
>> found an issue: once the old server becomes broken, the subscription =
cannot be
>> recovered by switching it to a good server.
>=20
> Thanks for testing. I could reproduce the same issue. In addition to =
yours, I found
> DROP SUBSCRIPTION cannot be done anymore. To switch the connection or =
drop it,
> I had to create the same user mapping must be created again.
>=20
> ```
> postgres=3D# DROP SUBSCRIPTION sub_bug ;
> ERROR:  user mapping not found for user "postgres", server "old_srv"
> postgres=3D# CREATE USER MAPPING FOR CURRENT_USER SERVER old_srv
> OPTIONS (user 'dummy', password 'dummy');
> CREATE USER MAPPING
> postgres=3D# DROP SUBSCRIPTION sub_bug ;
> DROP SUBSCRIPTION
> ```
>=20
> Before deep dive to your fix, I'm unclear why dropping the active USER =
MAPPING is
> allowed. Personally, it should be avoided anyway. Do you know why it's =
not restricted?
>=20
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>=20

Hi Hayato-san,

There is an existing test case in subscription.sql:
```
-- fail, must connect but lacks USAGE on server, as well as user mapping
DROP SUBSCRIPTION regress_testsub6;
```

So, I guess that=E2=80=99s an intentional behavior. You have to fix the =
broken server or switch to a good one before dropping the subscription. =
That=E2=80=99s my understanding from the test cases.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/