Thread

Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-02T12:08:06Z

Hi hackers,

while playing with the new ALTER SUBSCRIPTION parameter added in a5918fddf10,
I realized that the subscription is not re-read once we acquire the lock in
AlterSubscription().

This pre-existing issue is now more visible after a5918fddf10:

1/ two concurrent ALTER SUBSCRIPTION SET (conflict_log_destination = 'table')
could result in the second session attempting to create an already-existing
conflict log table, producing a confusing "relation already exists" error:

ERROR:  relation "pg_conflict_log_24614" already exists

It's confusing because ALTER SUBSCRIPTION SET (conflict_log_destination = 'table')
would not report an error if the conflict table already exists (and no concurrent
ALTER is running).

2/ a concurrent DROP followed by the ALTER would emit a NOTICE about creating the
conflict log table before failing with "referenced subscription was concurrently
dropped". That sounds like a weird messaging:

NOTICE:  created conflict log table "pg_conflict.pg_conflict_log_24620" for subscription "mysub"
ERROR:  referenced subscription was concurrently dropped

The attached fixes it by:

- Re-reading the subscription tuple after LockSharedObject() and refreshing the
  Subscription struct.
- Moving the local variable assignments to after the re-read.
- Re-checking the password_required privilege restriction after the re-read.

Remarks:

1/ not re-checking password_required after the re-read would still produce a
"tuple concurrently updated" error, but re-checking it allows us to display a
better error message.

2/ the ownership check is intentionally not re-done after the lock because
AlterSubscriptionOwner() does not take AccessExclusiveLock on the subscription
object: it only takes RowExclusiveLock on the pg_subscription catalog table.
This means ownership can change regardless of our lock, making a re-check after
lock acquisition pointless. The existing "tuple concurrently updated" error from
CatalogTupleUpdate() already provides a protection if ownership changes
concurrently.

3/ the "privileges" checks are still also done before the lock acquisition because
we don't want to lock an object we don't have privileges on.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Re-read subscription state after lock in AlterSubscription

Dilip Kumar <dilipbalaut@gmail.com> — 2026-07-02T12:27:09Z

On Thu, Jul 2, 2026 at 5:38 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi hackers,
>
> while playing with the new ALTER SUBSCRIPTION parameter added in a5918fddf10,
> I realized that the subscription is not re-read once we acquire the lock in
> AlterSubscription().
>
> This pre-existing issue is now more visible after a5918fddf10:
>
> 1/ two concurrent ALTER SUBSCRIPTION SET (conflict_log_destination = 'table')
> could result in the second session attempting to create an already-existing
> conflict log table, producing a confusing "relation already exists" error:
>
> ERROR:  relation "pg_conflict_log_24614" already exists
>
> It's confusing because ALTER SUBSCRIPTION SET (conflict_log_destination = 'table')
> would not report an error if the conflict table already exists (and no concurrent
> ALTER is running).
>
> 2/ a concurrent DROP followed by the ALTER would emit a NOTICE about creating the
> conflict log table before failing with "referenced subscription was concurrently
> dropped". That sounds like a weird messaging:
>
> NOTICE:  created conflict log table "pg_conflict.pg_conflict_log_24620" for subscription "mysub"
> ERROR:  referenced subscription was concurrently dropped
>
> The attached fixes it by:
>
> - Re-reading the subscription tuple after LockSharedObject() and refreshing the
>   Subscription struct.
> - Moving the local variable assignments to after the re-read.
> - Re-checking the password_required privilege restriction after the re-read.
>
> Remarks:
>
> 1/ not re-checking password_required after the re-read would still produce a
> "tuple concurrently updated" error, but re-checking it allows us to display a
> better error message.
>
> 2/ the ownership check is intentionally not re-done after the lock because
> AlterSubscriptionOwner() does not take AccessExclusiveLock on the subscription
> object: it only takes RowExclusiveLock on the pg_subscription catalog table.
> This means ownership can change regardless of our lock, making a re-check after
> lock acquisition pointless. The existing "tuple concurrently updated" error from
> CatalogTupleUpdate() already provides a protection if ownership changes
> concurrently.
>
> 3/ the "privileges" checks are still also done before the lock acquisition because
> we don't want to lock an object we don't have privileges on.
>

Thanks Bertrand, yeah this seems like a valid issue, and I agree we
need to reread the subscription after acquiring the object lock.

-- 
Regards,
Dilip Kumar
Google

RE: Re-read subscription state after lock in AlterSubscription

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-07-02T12:48:53Z

Dear Bertrand,

Good catch. Current code allows that old `sub` value is retained, so it sounds
reasonable fix even for me.

BTW, the issue that GetSubscription() is called before the LockSharedObject() looks
the existing issues even on REL_13_STABLE. So does it mean that there were no
cases that concurrent altering can be the unexpected state? At least,
"retain_dead_tuples" can avoid the issue because the launcher manages the
conflict slot.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-02T13:20:45Z

Hi Kuroda-san,

On Thu, Jul 02, 2026 at 12:48:53PM +0000, Hayato Kuroda (Fujitsu) wrote:
> Dear Bertrand,
> 
> Good catch. Current code allows that old `sub` value is retained, so it sounds
> reasonable fix even for me.
> 
> BTW, the issue that GetSubscription() is called before the LockSharedObject() looks
> the existing issues even on REL_13_STABLE. So does it mean that there were no
> cases that concurrent altering can be the unexpected state?

Yeah, but I think they would produce "tuple concurrently updated" error (due to 
CatalogTupleUpdate) so that invalid information could not be used.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

RE: Re-read subscription state after lock in AlterSubscription

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-07-03T03:13:08Z

Dear Bertrand,

> Yeah, but I think they would produce "tuple concurrently updated" error (due to
> CatalogTupleUpdate) so that invalid information could not be used.

I confirmed with PG14 that tuple concurrently updated ERROR can be raised when
ALTER SUBSCRIPTION DISABLE happens concurrently:

```
postgres=# ALTER SUBSCRIPTION sub DISABLE ;
ERROR:  tuple concurrently updated
```

It might be harmless but I think the correct ERROR should be reported: the patch
should be backpatched. Thought?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-03T04:19:00Z

Hi Kuroda-san,

On Fri, Jul 03, 2026 at 03:13:08AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Dear Bertrand,
> 
> > Yeah, but I think they would produce "tuple concurrently updated" error (due to
> > CatalogTupleUpdate) so that invalid information could not be used.
> 
> I confirmed with PG14 that tuple concurrently updated ERROR can be raised when
> ALTER SUBSCRIPTION DISABLE happens concurrently:
> 
> ```
> postgres=# ALTER SUBSCRIPTION sub DISABLE ;
> ERROR:  tuple concurrently updated
> ```

Yeah, reproducible by using a breakpoint just before acquiring the lock for example.

> It might be harmless but I think the correct ERROR should be reported: the patch
> should be backpatched. Thought?

I'm not sure about the back patch part as it would only improve error messages
in a rare race condition (and there is no risk of invalid data being used).

Since a5918fddf10, that's a different story because a table creation is now
involved.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Re-read subscription state after lock in AlterSubscription

Dilip Kumar <dilipbalaut@gmail.com> — 2026-07-03T04:50:32Z

On Fri, Jul 3, 2026 at 9:49 AM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi Kuroda-san,
>
> On Fri, Jul 03, 2026 at 03:13:08AM +0000, Hayato Kuroda (Fujitsu) wrote:
> > Dear Bertrand,
> >
> > > Yeah, but I think they would produce "tuple concurrently updated" error (due to
> > > CatalogTupleUpdate) so that invalid information could not be used.
> >
> > I confirmed with PG14 that tuple concurrently updated ERROR can be raised when
> > ALTER SUBSCRIPTION DISABLE happens concurrently:
> >
> > ```
> > postgres=# ALTER SUBSCRIPTION sub DISABLE ;
> > ERROR:  tuple concurrently updated
> > ```
>
> Yeah, reproducible by using a breakpoint just before acquiring the lock for example.
>
> > It might be harmless but I think the correct ERROR should be reported: the patch
> > should be backpatched. Thought?
>
> I'm not sure about the back patch part as it would only improve error messages
> in a rare race condition (and there is no risk of invalid data being used).

Patch LGTM. IMHO we can backpatch this as it is a small change and
also fixes the bug, without this fix a non-superuser executing  ALTER
SUBSCRIPTION could bypass the password_required=false restriction if a
concurrent transaction
updated that flag.  However, we could argue that this is a corner case
and can be skipped but given the patch's simplicity, I recommend
backpatching.

-- 
Regards,
Dilip Kumar
Google

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-03T05:52:44Z

Hi,

On Fri, Jul 03, 2026 at 10:20:32AM +0530, Dilip Kumar wrote:
> On Fri, Jul 3, 2026 at 9:49 AM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
> >
> > Hi Kuroda-san,
> >
> > On Fri, Jul 03, 2026 at 03:13:08AM +0000, Hayato Kuroda (Fujitsu) wrote:
> > > Dear Bertrand,
> > >
> > > > Yeah, but I think they would produce "tuple concurrently updated" error (due to
> > > > CatalogTupleUpdate) so that invalid information could not be used.
> > >
> > > I confirmed with PG14 that tuple concurrently updated ERROR can be raised when
> > > ALTER SUBSCRIPTION DISABLE happens concurrently:
> > >
> > > ```
> > > postgres=# ALTER SUBSCRIPTION sub DISABLE ;
> > > ERROR:  tuple concurrently updated
> > > ```
> >
> > Yeah, reproducible by using a breakpoint just before acquiring the lock for example.
> >
> > > It might be harmless but I think the correct ERROR should be reported: the patch
> > > should be backpatched. Thought?
> >
> > I'm not sure about the back patch part as it would only improve error messages
> > in a rare race condition (and there is no risk of invalid data being used).
> 
> Patch LGTM.

Thanks for looking at it!

> IMHO we can backpatch this as it is a small change and
> also fixes the bug, without this fix a non-superuser executing  ALTER
> SUBSCRIPTION could bypass the password_required=false restriction if a
> concurrent transaction
> updated that flag.

I don't think that's right. I just tested it with a breakpoint that way:

ALTER SUBSCRIPTION mysub SET (password_required = true);
ALTER SUBSCRIPTION mysub OWNER TO nonsuperuser;

gdb breakpoint at subscriptioncmds.c:1714 on session 1 (nonsuperuser)

session 1 (as nonsuperuser): start ALTER SUBSCRIPTION mysub SET (binary = true);
session 1 is paused by the breakpoint
session 2 (as superuser): ALTER SUBSCRIPTION mysub SET (password_required = false); 
continue session 1, gives:

postgres=> ALTER SUBSCRIPTION mysub SET (binary = true);
ERROR:  tuple concurrently updated

So it's also "protected" by this error.

> but given the patch's simplicity, I recommend
> backpatching.

That's right but that would only improve error messages. That said, looking closer,
they are elog() ones, so "not expected" to occur so yeah backpatch does make sense.

That said, what about also fixing DropSubscription() like in the 0002 attached?
(that would also produce those elog() messages in case of concurrent DROP or ALTER).

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

RE: Re-read subscription state after lock in AlterSubscription

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> — 2026-07-03T08:08:13Z

On Friday, July 3, 2026 1:53 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> 
> > but given the patch's simplicity, I recommend backpatching.
> 
> That's right but that would only improve error messages. That said, looking
> closer, they are elog() ones, so "not expected" to occur so yeah backpatch
> does make sense.

+1 for backpatching, even if it's rare, the "ERROR: tuple concurrently updated"
message seems confusing to me.

> 
> That said, what about also fixing DropSubscription() like in the 0002 attached?
> (that would also produce those elog() messages in case of concurrent DROP or
> ALTER).

For the patch, I'm not sure if we must repeat the checks twice. Could we
simply move the original checks to after we take the lock? At least, the
GetSubscription() call and the password check can be moved there and old codes
can be deleted.

BTW, this may not be strictly related, but I think it's not safe to do the
ownership check before locking the subscription as well. If the subscription is
concurrently dropped, a "tuple concurrently updated" error can still occur.

(Thanks to Kuroda-San for discussing this with me off-list.)

Best Regards,
Hou zj

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-03T09:03:24Z

Hi,

On Fri, Jul 03, 2026 at 08:08:13AM +0000, Zhijie Hou (Fujitsu) wrote:
> On Friday, July 3, 2026 1:53 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> > 
> > That said, what about also fixing DropSubscription() like in the 0002 attached?
> > (that would also produce those elog() messages in case of concurrent DROP or
> > ALTER).
> 
> For the patch, I'm not sure if we must repeat the checks twice.

Thanks for looking at it!

> Could we
> simply move the original checks to after we take the lock? At least, the
> GetSubscription() call and the password check can be moved there and old codes
> can be deleted.

I'm not sure which checks you refer to. The ones that are keep before the lock
acquisition are because we don't want to lock an object we don't have privileges
on (see remark 3 in [1]).

> BTW, this may not be strictly related, but I think it's not safe to do the
> ownership check before locking the subscription as well. If the subscription is
> concurrently dropped, a "tuple concurrently updated" error can still occur.

That's right, I explained why in remark number 2 in [1]:

"
the ownership check is intentionally not re-done after the lock because
AlterSubscriptionOwner() does not take AccessExclusiveLock on the subscription
object: it only takes RowExclusiveLock on the pg_subscription catalog table.
This means ownership can change regardless of our lock, making a re-check after
lock acquisition pointless. The existing "tuple concurrently updated" error from
CatalogTupleUpdate() already provides a protection if ownership changes
concurrently.
"

Does that make sense?

[1]: https://postgr.es/m/akZUpiDa1UfmzYxL%40bdtpg

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

RE: Re-read subscription state after lock in AlterSubscription

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> — 2026-07-03T09:56:13Z

On Friday, July 3, 2026 5:03 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> On Fri, Jul 03, 2026 at 08:08:13AM +0000, Zhijie Hou (Fujitsu) wrote:
> > On Friday, July 3, 2026 1:53 PM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > That said, what about also fixing DropSubscription() like in the 0002
> attached?
> > > (that would also produce those elog() messages in case of concurrent
> > > DROP or ALTER).
> >
> > For the patch, I'm not sure if we must repeat the checks twice.
> 
> Thanks for looking at it!
> 
> > Could we
> > simply move the original checks to after we take the lock? At least,
> > the
> > GetSubscription() call and the password check can be moved there and
> > old codes can be deleted.
> 
> I'm not sure which checks you refer to. The ones that are keep before the lock
> acquisition are because we don't want to lock an object we don't have
> privileges on (see remark 3 in [1]).

I was referring to the password_required check and the GetSubscription() call.

I think failing the password_required check does not necessarily mean we do not
have the permission to lock the subscription, It seems to me we only need to
disallow changing the subscription data in this case. In
DropSubscription, we take a lock on the subscription regardless of
password_required.

Best Regards,
Hou zj

Re: Re-read subscription state after lock in AlterSubscription

Amit Kapila <amit.kapila16@gmail.com> — 2026-07-03T10:15:34Z

On Fri, Jul 3, 2026 at 1:38 PM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:
>
> On Friday, July 3, 2026 1:53 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> >
> > > but given the patch's simplicity, I recommend backpatching.
> >
> > That's right but that would only improve error messages. That said, looking
> > closer, they are elog() ones, so "not expected" to occur so yeah backpatch
> > does make sense.
>
> +1 for backpatching, even if it's rare, the "ERROR: tuple concurrently updated"
> message seems confusing to me.
>

I also think backpatching makes sense. BTW, I have a comment:
+ heap_freetuple(tup);
+ tup = SearchSysCacheCopy2(SUBSCRIPTIONNAME, ObjectIdGetDatum(MyDatabaseId),
+   CStringGetDatum(stmt->subname));

heap_freetuple() could be done before acquiring the lock, is there a
reason to keep it after lock?

> >
> > That said, what about also fixing DropSubscription() like in the 0002 attached?
> > (that would also produce those elog() messages in case of concurrent DROP or
> > ALTER).
>
> For the patch, I'm not sure if we must repeat the checks twice. Could we
> simply move the original checks to after we take the lock? At least, the
> GetSubscription() call and the password check can be moved there and old codes
> can be deleted.
>

Isn't the same true for the AlterSubscription() case as well? Also, I
noticed that AlterPublication() does the same trick but it uses
PUBLICATIONOID cacheid, so shouldn't we use SUBSCRIPTIONOID cacheid
here as well? I think this is to prevent the case where the same name
pub/sub is recreated after lock.

-- 
With Regards,
Amit Kapila.

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-03T15:39:21Z

Hi,

On Fri, Jul 03, 2026 at 03:45:34PM +0530, Amit Kapila wrote:
> On Fri, Jul 3, 2026 at 1:38 PM Zhijie Hou (Fujitsu)
> <houzj.fnst@fujitsu.com> wrote:
> >
> > On Friday, July 3, 2026 1:53 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > > but given the patch's simplicity, I recommend backpatching.
> > >
> > > That's right but that would only improve error messages. That said, looking
> > > closer, they are elog() ones, so "not expected" to occur so yeah backpatch
> > > does make sense.
> >
> > +1 for backpatching, even if it's rare, the "ERROR: tuple concurrently updated"
> > message seems confusing to me.
> >
> 
> I also think backpatching makes sense. BTW, I have a comment:

Thanks for looking at it!

> + heap_freetuple(tup);
> + tup = SearchSysCacheCopy2(SUBSCRIPTIONNAME, ObjectIdGetDatum(MyDatabaseId),
> +   CStringGetDatum(stmt->subname));
> 
> heap_freetuple() could be done before acquiring the lock, is there a
> reason to keep it after lock?

No particular reason, could be done before. Done in 0001 attached.

> 
> > >
> > > That said, what about also fixing DropSubscription() like in the 0002 attached?
> > > (that would also produce those elog() messages in case of concurrent DROP or
> > > ALTER).
> >
> > For the patch, I'm not sure if we must repeat the checks twice. Could we
> > simply move the original checks to after we take the lock? At least, the
> > GetSubscription() call and the password check can be moved there and old codes
> > can be deleted.
> >
> 
> Isn't the same true for the AlterSubscription() case as well?

I think there is no need to lock if we are later going to disallow changing the
subscription data due to the password_required/superuser check.

That said moving it as suggested by Hou-san, does simplify the code and the lock
is not held for long, so done that way in 0001.

> Also, I
> noticed that AlterPublication() does the same trick but it uses
> PUBLICATIONOID cacheid, so shouldn't we use SUBSCRIPTIONOID cacheid
> here as well? I think this is to prevent the case where the same name
> pub/sub is recreated after lock.

Oh right and I did it that way in 0001 and 0002.

But while doing this and looking closely, I'm not sure AlterPublication() does
it right. Indeed, in theory, the OID could have been re-used too (between the
time we did the name resolution and the time we lock the publication). I think
what is needed is something similar to RangeVarGetRelidExtended(), means do the
name resolution, acl check (ownership) and lock acquisition, all in unison.

That's what 0003 is trying to achieve for the subscription and 0004 for the 
publication.

What do you think?

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Re-read subscription state after lock in AlterSubscription

Dilip Kumar <dilipbalaut@gmail.com> — 2026-07-04T08:00:08Z

On Fri, Jul 3, 2026 at 9:09 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi,
>
> On Fri, Jul 03, 2026 at 03:45:34PM +0530, Amit Kapila wrote:
> > On Fri, Jul 3, 2026 at 1:38 PM Zhijie Hou (Fujitsu)
> > <houzj.fnst@fujitsu.com> wrote:
> > >
> > > On Friday, July 3, 2026 1:53 PM Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:
> > > >
> > > > > but given the patch's simplicity, I recommend backpatching.
> > > >
> > > > That's right but that would only improve error messages. That said, looking
> > > > closer, they are elog() ones, so "not expected" to occur so yeah backpatch
> > > > does make sense.
> > >
> > > +1 for backpatching, even if it's rare, the "ERROR: tuple concurrently updated"
> > > message seems confusing to me.
> > >
> >
> > I also think backpatching makes sense. BTW, I have a comment:
>
> Thanks for looking at it!
>
> > + heap_freetuple(tup);
> > + tup = SearchSysCacheCopy2(SUBSCRIPTIONNAME, ObjectIdGetDatum(MyDatabaseId),
> > +   CStringGetDatum(stmt->subname));
> >
> > heap_freetuple() could be done before acquiring the lock, is there a
> > reason to keep it after lock?
>
> No particular reason, could be done before. Done in 0001 attached.
>
> >
> > > >
> > > > That said, what about also fixing DropSubscription() like in the 0002 attached?
> > > > (that would also produce those elog() messages in case of concurrent DROP or
> > > > ALTER).
> > >
> > > For the patch, I'm not sure if we must repeat the checks twice. Could we
> > > simply move the original checks to after we take the lock? At least, the
> > > GetSubscription() call and the password check can be moved there and old codes
> > > can be deleted.
> > >
> >
> > Isn't the same true for the AlterSubscription() case as well?
>
> I think there is no need to lock if we are later going to disallow changing the
> subscription data due to the password_required/superuser check.
>
> That said moving it as suggested by Hou-san, does simplify the code and the lock
> is not held for long, so done that way in 0001.
>
> > Also, I
> > noticed that AlterPublication() does the same trick but it uses
> > PUBLICATIONOID cacheid, so shouldn't we use SUBSCRIPTIONOID cacheid
> > here as well? I think this is to prevent the case where the same name
> > pub/sub is recreated after lock.
>
> Oh right and I did it that way in 0001 and 0002.
>
> But while doing this and looking closely, I'm not sure AlterPublication() does
> it right. Indeed, in theory, the OID could have been re-used too (between the
> time we did the name resolution and the time we lock the publication). I think
> what is needed is something similar to RangeVarGetRelidExtended(), means do the
> name resolution, acl check (ownership) and lock acquisition, all in unison.
>
> That's what 0003 is trying to achieve for the subscription and 0004 for the
> publication.
>
> What do you think?
>
0003:

It looks like the implementation of DROP SUBSCRIPTION IF EXISTS has a
concurrent drop race condition in DropSubscription(). Currently, if
stmt->missing_ok is true, the initial lookup safely handles a missing
subscription. However, once a subscription is found and the code
enters the drop loop, a second internal lookup/refetch happens. If a
concurrent transaction drops the subscription after our initial check
but before this internal refetch, the code throws an error.
Essentially, the loop completely ignores the missing_ok flag during
the refetch phase.  Am I missing something?

-- 
Regards,
Dilip Kumar
Google

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-04T08:19:46Z

Hi,

On Sat, Jul 04, 2026 at 01:30:08PM +0530, Dilip Kumar wrote:
> On Fri, Jul 3, 2026 at 9:09 PM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
> >
> > But while doing this and looking closely, I'm not sure AlterPublication() does
> > it right. Indeed, in theory, the OID could have been re-used too (between the
> > time we did the name resolution and the time we lock the publication). I think
> > what is needed is something similar to RangeVarGetRelidExtended(), means do the
> > name resolution, acl check (ownership) and lock acquisition, all in unison.
> >
> > That's what 0003 is trying to achieve for the subscription and 0004 for the
> > publication.
> >
> > What do you think?
> >
> 0003:
> 
> It looks like the implementation of DROP SUBSCRIPTION IF EXISTS has a
> concurrent drop race condition in DropSubscription(). Currently, if
> stmt->missing_ok is true, the initial lookup safely handles a missing
> subscription. However, once a subscription is found and the code
> enters the drop loop, a second internal lookup/refetch happens. If a
> concurrent transaction drops the subscription after our initial check
> but before this internal refetch, the code throws an error.
> Essentially, the loop completely ignores the missing_ok flag during
> the refetch phase.

Good catch, will fix, thanks! 

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

RE: Re-read subscription state after lock in AlterSubscription

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-07-06T02:43:20Z

Dear Bertrand,

Thanks for updating the patch. I found one issue:

```
	/* DROP hook for the subscription being removed */
	InvokeObjectDropHook(SubscriptionRelationId, subid, 0);

```

I think the reporting should be after the loop, otherwise the wrong subid can be
reported. Am I missing something?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Re: Re-read subscription state after lock in AlterSubscription

Amit Kapila <amit.kapila16@gmail.com> — 2026-07-06T04:54:26Z

On Fri, Jul 3, 2026 at 9:09 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> On Fri, Jul 03, 2026 at 03:45:34PM +0530, Amit Kapila wrote:
>
> But while doing this and looking closely, I'm not sure AlterPublication() does
> it right. Indeed, in theory, the OID could have been re-used too (between the
> time we did the name resolution and the time we lock the publication). I think
> what is needed is something similar to RangeVarGetRelidExtended(), means do the
> name resolution, acl check (ownership) and lock acquisition, all in unison.
>

It seems RangeVarGetRelidExtended() also doesn't do the additional
invalidation handling if the caller already has an appropriate lock,
see comments [1]. Apart from that also, I am not sure it is a good
ideal to add this additional handling in Pub/Sub DDLs as in worst case
scenario even if the OID is re-used the user will face "tuple
concurrently updated" or similar ERRORs, it won't do anything wrong.
So for such rare cases, it doesn't seem worth adding this additional
re-checking machinery. Based on the same theory, I am thinking again
whether it is worth backpatching these patches? I mean these fall into
the category of improving user facing messages during Pub/Sub DDLs, so
isn't it okay to just push this work in HEAD?

[1]:
/*
 * If no lock requested, we assume the caller knows what they're
 * doing.  They should have already acquired a heavyweight lock on
 * this relation earlier in the processing of this same statement, so
 * it wouldn't be appropriate to AcceptInvalidationMessages() here, as
 * that might pull the rug out from under them.
 */
if (lockmode == NoLock)

-- 
With Regards,
Amit Kapila.

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-06T05:01:00Z

Hi Kuroda-san,

On Mon, Jul 06, 2026 at 02:43:20AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Dear Bertrand,
> 
> Thanks for updating the patch. I found one issue:
> 
> ```
> 	/* DROP hook for the subscription being removed */
> 	InvokeObjectDropHook(SubscriptionRelationId, subid, 0);
> 
> ```
> 
> I think the reporting should be after the loop, otherwise the wrong subid can be
> reported.

Yeah, and I think this is an existing behavior not related to the patch. Currently,
InvokeObjectDropHook() is called before we lock the subscription. I think that
makes more sense to do it after the lock is acquired, so this is now changed in
0002.

Also addressing Dilip's comment in the attached.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-06T05:43:28Z

Hi,

On Mon, Jul 06, 2026 at 10:24:26AM +0530, Amit Kapila wrote:
> On Fri, Jul 3, 2026 at 9:09 PM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
> >
> > On Fri, Jul 03, 2026 at 03:45:34PM +0530, Amit Kapila wrote:
> >
> > But while doing this and looking closely, I'm not sure AlterPublication() does
> > it right. Indeed, in theory, the OID could have been re-used too (between the
> > time we did the name resolution and the time we lock the publication). I think
> > what is needed is something similar to RangeVarGetRelidExtended(), means do the
> > name resolution, acl check (ownership) and lock acquisition, all in unison.
> >
> 
> It seems RangeVarGetRelidExtended() also doesn't do the additional
> invalidation handling if the caller already has an appropriate lock,
> see comments [1].

From what I can see, the NoLock callers of RangeVarGetRelidExtended(), are for
callers that don't modify objects (they are "read only" callers). The only
exception is nextval() but there is an XXX that mentions it.

Here we modify the subscription or publication, so I don't think we are in the
NoLock spirit of RangeVarGetRelidExtended().

> Apart from that also, I am not sure it is a good
> ideal to add this additional handling in Pub/Sub DDLs as in worst case
> scenario even if the OID is re-used the user will face "tuple
> concurrently updated" or similar ERRORs, it won't do anything wrong.

That's probably right before a5918fddf10, but with conflict_log_destination='table'
we now perform creating/dropping a table based on the stale data before it ever
reaches CatalogTupleUpdate().

Also, even prior a5918fddf10 I believe there might be situations that could not
produce the tuple concurrently updated" but corrupt the tuple (say if vacuum had 
the time to clean up the tuple and the same ctid is reused). Probably extremely
rare scenario, though.

> So for such rare cases, it doesn't seem worth adding this additional
> re-checking machinery. Based on the same theory, I am thinking again
> whether it is worth backpatching these patches? I mean these fall into
> the category of improving user facing messages during Pub/Sub DDLs, so
> isn't it okay to just push this work in HEAD?

Those are not ereport() but elog() messages so not "expected" to happen.

Based on the above, I'm thinking that backpatching 0001 and 0002 and keep
0003 and 0004 only for HEAD could make sense.

What do you think?

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Re-read subscription state after lock in AlterSubscription

Amit Kapila <amit.kapila16@gmail.com> — 2026-07-06T09:37:24Z

On Mon, Jul 6, 2026 at 11:13 AM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> On Mon, Jul 06, 2026 at 10:24:26AM +0530, Amit Kapila wrote:
> > On Fri, Jul 3, 2026 at 9:09 PM Bertrand Drouvot
> > <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > On Fri, Jul 03, 2026 at 03:45:34PM +0530, Amit Kapila wrote:
> > >
> > > But while doing this and looking closely, I'm not sure AlterPublication() does
> > > it right. Indeed, in theory, the OID could have been re-used too (between the
> > > time we did the name resolution and the time we lock the publication). I think
> > > what is needed is something similar to RangeVarGetRelidExtended(), means do the
> > > name resolution, acl check (ownership) and lock acquisition, all in unison.
> > >
> >
> > It seems RangeVarGetRelidExtended() also doesn't do the additional
> > invalidation handling if the caller already has an appropriate lock,
> > see comments [1].
>
> From what I can see, the NoLock callers of RangeVarGetRelidExtended(), are for
> callers that don't modify objects (they are "read only" callers). The only
> exception is nextval() but there is an XXX that mentions it.
>
> Here we modify the subscription or publication, so I don't think we are in the
> NoLock spirit of RangeVarGetRelidExtended().
>

IIUC, here the risk is that during the first read and before we take
the Lock, if the same OID is reused for a different subscription then
we may end up modifying an unintended subscription. I think that is a
theoretical risk rather than a practical one. We already note similar
risk at other places, like see comments atop GetNewOidWithIndex(Since
the OID is not immediately inserted into the table, there is a race
condition here; but a problem could occur only if someone else managed
to cycle through 2^32 OIDs and generate the same OID before we finish
inserting our row. This seems unlikely to be a problem.). Similarly
comments atop GetNewRelFileNumber( As with GetNewOidWithIndex(), there
is some theoretical risk of a race) made a note of similar risk. I
feel we should note this in comments rather than trying to add
additional code to handle it.

As per my understanding the loop exists in RangeVarGetRelidExtended()
because relation lookup follows the name, and no lock can pin a
name->OID binding, so the binding can be rebound by concurrent DDL
between lookup and lock. Concretely, our lock protects relation X's
OID, but it can't stop someone renaming X away and handing X's old
name to a different relation Y. The lockable thing (the OID) and the
thing that changes (the name binding) are different objects. The loop
detects exactly this: acquiring the lock runs
AcceptInvalidationMessages(), and if any invalidations arrived while
we waited (inval_count == SharedInvalidMessageCounter), it re-resolves
the name. If the name now maps to a different OID than the one we
locked, it releases the old lock and locks the new OID. It repeats
until the name resolves to the same OID across a lock acquisition —
i.e. until the binding is stable while locked. OTOH, the subscription
path follows the locked OID instead, so it needs only a single
re-read.

With Regards,
Amit Kapila.

Re: Re-read subscription state after lock in AlterSubscription

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-07-06T14:53:39Z

Hi,

On Mon, Jul 06, 2026 at 03:07:24PM +0530, Amit Kapila wrote:
> On Mon, Jul 6, 2026 at 11:13 AM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
> >
> > > It seems RangeVarGetRelidExtended() also doesn't do the additional
> > > invalidation handling if the caller already has an appropriate lock,
> > > see comments [1].
> >
> > From what I can see, the NoLock callers of RangeVarGetRelidExtended(), are for
> > callers that don't modify objects (they are "read only" callers). The only
> > exception is nextval() but there is an XXX that mentions it.
> >
> > Here we modify the subscription or publication, so I don't think we are in the
> > NoLock spirit of RangeVarGetRelidExtended().
> >
> 
> IIUC, here the risk is that during the first read and before we take
> the Lock, if the same OID is reused for a different subscription then
> we may end up modifying an unintended subscription. I think that is a
> theoretical risk rather than a practical one.

I agree OID reuse itself is theoretical.

> As per my understanding the loop exists in RangeVarGetRelidExtended()
> because relation lookup follows the name, and no lock can pin a
> name->OID binding, so the binding can be rebound by concurrent DDL
> between lookup and lock. Concretely, our lock protects relation X's
> OID, but it can't stop someone renaming X away and handing X's old
> name to a different relation Y.

Not sure it's only about renaming. The commit message of 4240e429d0c mentions
"This was particularly problematic in the case where a table had been dropped
and recreated". b3ad5d02c9c also used the same logic and reasoning "avoids
needlessly failing when the object of interest is concurrently dropped and
recreated".

Also in 4240e429d0c: "there's nothing at all here to guard against similar race
conditions for non-relations": I think that subscriptions and publications are
among those non-relations cases.

> The lockable thing (the OID) and the
> thing that changes (the name binding) are different objects. The loop
> detects exactly this: acquiring the lock runs
> AcceptInvalidationMessages(), and if any invalidations arrived while
> we waited (inval_count == SharedInvalidMessageCounter), it re-resolves
> the name. If the name now maps to a different OID than the one we
> locked, it releases the old lock and locks the new OID. It repeats
> until the name resolves to the same OID across a lock acquisition —
> i.e. until the binding is stable while locked. OTOH, the subscription
> path follows the locked OID instead, so it needs only a single
> re-read.

I think that's for example what RemoveRelations() was doing before 4240e429d0c
and what get_object_address() was doing before b3ad5d02c9c:

1/ Resolve name to OID
2/ Lock by OID
3/ Check if it still exists
4/ If gone then elog(ERROR..

but has been changed in b3ad5d02c9c with a retry loop.

Also looking at get_object_address(), I can see that it handles publications and
subscriptions:

case OBJECT_PUBLICATION:
case OBJECT_SUBSCRIPTION:
  address = get_object_address_unqualified(objtype,
                                           castNode(String, object), missing_ok);

and that DROP PUBLICATION goes through it, so that it already benefits from the
retry loop in get_object_address().

DROP SUBSCRIPTION however has its own dedicated code path and does not go through
get_object_address(): 0003 adds the retry loop for it. And if DROP already uses
the retry loop then ALTER should probably use it too (also done in 0003 and 0004).

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com