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
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
