Thread: Forbid to DROP temp tables of other sessions
Hi,
I noticed that TRUNCATE and ALTER commands on temporary tables of other sessions produce an error "cannot truncate/alter temporary tables of other sessions". But are there any reasons to allow us to DROP such tables?
It seems to me that the only case when we may need it is the removal of orphan tables. But the autovacuum is responsible for this and it uses a different functionality. I'm wondering if there are any other cases. If not, can we just handle it for example in ExecDropStmt and produce an error like "cannot drop temporary tables of other sessions"?
--
Best regards,
Daniil Davydov
On Fri, 25 Oct 2024 at 11:02, Daniil Davydov <3danissimo@gmail.com> wrote:
But are there any reasons to allow us to DROP such tables?
Hi!
This topic has already been discussed in [0], I believe. I'm not sure how it all ended and if there were any changes made in the end. But from the user's perspective, temporary tables are expected to be isolated within sessions, I think. This is an ideal solution, but does it feasible or not is a question.
BTW, if we can "isolate" temp relations, we'll be one step close to get rid of temp relations locking [1].
[0] https://www.postgresql.org/message-id/flat/d4a68c6d-d6c4-d52a-56cb-babb8177b5fe%40oss.nttdata.com
Best regards,
Maxim Orlov.
Daniil Davydov <3danissimo@gmail.com> writes:
> I noticed that TRUNCATE and ALTER commands on temporary tables of other
> sessions produce an error "cannot truncate/alter temporary tables of other
> sessions". But are there any reasons to allow us to DROP such tables?
> It seems to me that the only case when we may need it is the removal of
> orphan tables. But the autovacuum is responsible for this and it uses a
> different functionality. I'm wondering if there are any other cases. If
> not, can we just handle it for example in ExecDropStmt and produce an error
> like "cannot drop temporary tables of other sessions"?
If autovacuum can do it, I don't see a reason to prevent superusers
from doing it manually.
regards, tom lane
Hi, looks good for me, but please fix formatting in temp_tbl_fix.patch!
On Tue, 29 Oct 2024 at 07:22, Daniil Davydov <3danissimo@gmail.com> wrote:
Hi,
Thanks for your comments, I appreciate them.
As I continued to deal with the topic of working with temp tables of
other sessions, I noticed something like a bug. For example
(REL_17_STABLE):
Session 1:
=# CREATE TEMP TABLE test(id int);
Session 2:
=# INSERT INTO pg_temp_0.test VALUES (1);
=# INSERT INTO pg_temp_0.test VALUES (2);
Second INSERT command will end with an error "cannot access temporary
tables of other sessions". I checked why this is happening and found
errors in several places.
Good catch. I agree with this being an unwarranted behaviour.
A minor comment from my end is the wording of the error message.
Based on the Postgresql error message style huide, something like this could be better,
"could not access temporary relations of other sessions".
So, I attach two files to this email :
1) Isolation test, that shows an error in REL_17_STABLE (iso_1.patch)
2) Patch that fixes code that mistakenly considered temporary tables
to be permanent (I will be glad to receive feedback on these fixes) +
isolation test, which shows that now any action with temp table of
other session leads to error (temp_tbl_fix.patch)
Tests look kinda ugly, but I think it's inevitable, given that we
don't know exactly what the name of the temporary schema of other
session will be.
--
Best regards,
Daniil Davydov
Regards,
Rafia Sabih
Rafia Sabih
CYBERTEC PostgreSQL International GmbH