public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniil Davydov <[email protected]>
To: Jim Jones <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Soumya S Murali <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Stepan Neretin <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Mohamed Ali <[email protected]>
Cc: Nazneen Jafri <[email protected]>
Cc: Shawn McCoy <[email protected]>
Subject: Re: Fix bug with accessing to temporary tables of other sessions
Date: Sun, 3 May 2026 15:53:39 +0700
Message-ID: <CAJDiXgi_SW3DVvXb4+n04NA1YfeeXmT3gUnSG7h4JwBd+RdJGg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAJDiXghBO_TqvHOSui8MOxiFmwLT20+SAnH5nW1rpWHk7Jwffg@mail.gmail.com>
<[email protected]>
<CAJDiXgi0JFk0f2KWWQkzLBC5P7erX9WP18qqnbi-rjZ-K-P=3w@mail.gmail.com>
<CAMtXxw_hBNuAWQUdSRMpoeRVRYr+5+S7p0bSzuqtHxfpzJPd3w@mail.gmail.com>
<[email protected]>
<CAMtXxw_2VnP5U7U+2ObOCC2voEkAzUqAgH=rPTQBXrb_8K=pyA@mail.gmail.com>
<CAPpHfdtsGSywUExfKMakAuRz8-+61d-4XHkVf=pNQeYTZTVYoA@mail.gmail.com>
<CAMtXxw8J7L0r8aXt9NBFfqqSnaNyU=-_Uq4tW4aO3PHyVeXLAQ@mail.gmail.com>
<CAPpHfdvLCgXgdhSvuPCkek+r=P4E-WiNceWfh7UQztiMzfFcvg@mail.gmail.com>
<[email protected]>
<CAPpHfdsOkkeobCfO9hJvaE2fRcdGmWJoX0umx--DnApmtmefCg@mail.gmail.com>
<CAJDiXgjNySH7Od3nZGjWy30sKv+-12c=kajL7Qfkj3y+CDRwtA@mail.gmail.com>
<CAPpHfds8pwuwF69JFs0SQz58op-U4ddR3KWv0NVEij2mpkzgpw@mail.gmail.com>
<[email protected]>
Hi,
On Sun, May 3, 2026 at 12:32 AM Jim Jones <[email protected]> wrote:
>
> In addition to the DROP TABLE exception:
>
> It is also possible to LOCK temporary tables from other sessions:
>
> postgres=# BEGIN;
> BEGIN
> postgres=*# LOCK TABLE pg_temp_91.t IN ACCESS SHARE MODE ;
> LOCK TABLE
>
> pg_temp_91.t lives as long the transaction is open -- even after the
> origin session closes, which is totally expected. I'd say it falls into
> the same category of DROP TABLE, where the table contents are never
> read, so I'd argue it's ok.
Autovacuum locks orphaned table's oid before deleting it. LOCK TABLE command
also locks the oid of the specified table. If we want to make sure that
autovacuum can acquire the mentioned lock (i.e. cover this behavior using
tests), I suggest testing it using the LOCK TABLE command.
Please, see the attached patch that ensures that cross-session LOCK TABLE works
properly.
--
Best regards,
Daniil Davydov
Attachments:
[text/x-patch] 0001-Test-cross-session-LOCK-TABLE-scenario.patch (2.1K, 2-0001-Test-cross-session-LOCK-TABLE-scenario.patch)
download | inline diff:
From 05aaab6a225195246e70ad465a0995c4c0437e23 Mon Sep 17 00:00:00 2001
From: Daniil Davidov <[email protected]>
Date: Sun, 3 May 2026 15:51:06 +0700
Subject: [PATCH] Test cross-session LOCK TABLE scenario
---
.../test_misc/t/013_temp_obj_multisession.pl | 25 +++++++++++++++++++
1 file changed, 25 insertions(+)
diff --git a/src/test/modules/test_misc/t/013_temp_obj_multisession.pl b/src/test/modules/test_misc/t/013_temp_obj_multisession.pl
index b4442836bef..a7369700580 100644
--- a/src/test/modules/test_misc/t/013_temp_obj_multisession.pl
+++ b/src/test/modules/test_misc/t/013_temp_obj_multisession.pl
@@ -220,14 +220,39 @@ like(
qr/cannot drop table .*\.foo2 because other objects depend on it/,
'DROP TABLE blocked by cross-session dependency');
+my $foo2_oid = $node->safe_psql('postgres',
+ "SELECT oid FROM pg_class WHERE relname='foo2';");
+
+# Cross-session LOCK TABLE scenario. Ensure that LockRelationOid is working
+# properly for other temp tables since this mechanism is also used by
+# autovacuum during orphaned tables cleanup.
+my $psql2 = $node->background_psql('postgres');
+$psql2->query_safe(
+ qq{
+ BEGIN;
+ LOCK TABLE $tempschema.foo2 IN ACCESS SHARE MODE;
+});
+
# When the owner session ends, its temp objects are dropped via the
# normal session-exit cleanup, which cascades through
# DEPENDENCY_NORMAL and also removes the cross-session function that
# depended on the temp row type. This is the same mechanism
# autovacuum relies on to clean up temp relations left behind by a
# crashed backend.
+# Access share lock on the foo2 will block session-exit cleanup, because an
+# owner will try to acquire deletion lock all its temp objects via
+# findDependentObjects.
+my $log_offset = -s $node->logfile;
$psql1->quit;
+# Check whether session-exit cleanup is blocked.
+$node->wait_for_log(qr/waiting for AccessExclusiveLock on relation $foo2_oid/,
+ $log_offset);
+
+# Release lock on foo2 and allow session-exit cleanup to finish.
+$psql2->query_safe(q(COMMIT;));
+$psql2->quit;
+
$node->poll_query_until(
'postgres',
"SELECT NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'cross_session_func')"
--
2.43.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fix bug with accessing to temporary tables of other sessions
In-Reply-To: <CAJDiXgi_SW3DVvXb4+n04NA1YfeeXmT3gUnSG7h4JwBd+RdJGg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox