[BUG] Cannot flush buffers of temp table deleted from other session
Hi,
Ability to do a DROP temporary table of other session leads to error :
session 1 :
create temp table test (id int);
-- let's assume that 'test' will be placed in schema 'pg_temp_0' and
has relfilepath 'base/5/t0_16390'
insert into test select generate_series(1, 1000); -- make few buffers dirty
session 2 :
drop table pg_temp_0.test; -- drop temp table of session 1
session 1:
create temp table test (id int); -- create another temp table
-- make dirty too many buffers (> temp_buffers), so postgres will try
to flush some of them
insert into test select generate_series(1, 250000);
ERROR: could not open file "base/5/t0_16390": No such file or directory
Thus, we were trying to flush buffers of an already deleted temp
table. Error occurs here : GetLocalVictimBuffer -> FlushLocalBuffer ->
smgropen(BufTagGetRelFileLocator(&bufHdr->tag), MyProcNumber).
To be honest, I don't see a good way to handle this error, because
there may be a lot of reasons why flushing failed (up to the point
that the buffer tag may be corrupted).
I attach a patch (targeted on the master branch) that contains a
sample idea of error handling - don't throw an error if we can ensure
that the relation has been deleted.
Patch contains a very rough assumption, that relation's relfilenode ==
relation's oid.
What do you think about it?
BTW, there are other bugs that can occur during interacting with other
session temp tables. I described them and suggested corrections in
this thread [1]/messages/by-id/CAJDiXgj72Axj0d4ojKdRWG_rnkfs4uWY414NL=15sCvh7-9rwg@mail.gmail.com.
[1]: /messages/by-id/CAJDiXgj72Axj0d4ojKdRWG_rnkfs4uWY414NL=15sCvh7-9rwg@mail.gmail.com
--
Best regards,
Daniil Davydov
Attachments:
0001-Handle-error-with-flushing-dirty-buffer-of-deleted-t.patchtext/x-patch; charset=US-ASCII; name=0001-Handle-error-with-flushing-dirty-buffer-of-deleted-t.patchDownload+84-7
BTW, there are other bugs that can occur during interacting with other
session temp tables. I described them and suggested corrections in
this thread [1].[1] /messages/by-id/CAJDiXgj72Axj0d4ojKdRWG_rnkfs4uWY414NL=15sCvh7-9rwg@mail.gmail.com
Sorry, this is an outdated thread. I planned to continue to discuss
mentioned topic here :
/messages/by-id/CAJDiXghdFcZ8=nh4G69te7iRr3Q0uFyXxb3ZdG09_GTNZXwH0g@mail.gmail.com
--
Best regards,
Daniil Davydov
Daniil Davydov <3danissimo@gmail.com> writes:
Ability to do a DROP temporary table of other session leads to error :
[ shrug... ] Don't do that. A superuser is capable of doing lots
of things that will break the database, and this one hardly seems
like one of the worse ones.
BTW, there are other bugs that can occur during interacting with other
session temp tables. I described them and suggested corrections in
this thread [1].
I doubt I'd agree that any such thing is a bug we need to fix.
You should not be attempting to touch other sessions' temp tables.
(If there's any path by which a non-superuser can do that, we
probably need to tighten some permission check somewhere.)
regards, tom lane
Hi,
On Thu, Jun 12, 2025 at 2:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Daniil Davydov <3danissimo@gmail.com> writes:
Ability to do a DROP temporary table of other session leads to error :
[ shrug... ] Don't do that. A superuser is capable of doing lots
of things that will break the database, and this one hardly seems
like one of the worse ones.
OK, I see your point, thanks.
IMHO, deleting other temporary tables and (for example) deleting rows
from pg_class are not so similar in terms of the obvious occurrence of
bad consequences.
But as far as I understand, we will not specify such details somewhere
in the documentation.
BTW, there are other bugs that can occur during interacting with other
session temp tables. I described them and suggested corrections in
this thread [1].I doubt I'd agree that any such thing is a bug we need to fix.
You should not be attempting to touch other sessions' temp tables.
(If there's any path by which a non-superuser can do that, we
probably need to tighten some permission check somewhere.)
Let's forget about DROP for a while. Postgres design has rules, that
superuser cannot perform commands like
select/update/delete/truncate... with other session's temp tables.
You can find appropriate error messages (for example) in bufmgr.c :
***
if (RELATION_IS_OTHER_TEMP(reln))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
***
But by now the query parser does not recognize other session's temp
tables as temporary, and we can get weird query results instead of
getting the error provided by the postgres design (that even worse
than unexpected error occurrence).
I am sure that this is a bug and we should fix such behavior.
--
Best regards,
Danill Davydov