Thread: Do not lock temp relations
Hi!
Working with temp relations is some kind of bottleneck in Postgres, in my view.
There are no problems if you want to handle it from time to time, not arguing
that. But if you have to make a massive temp tables creation/deletion, you'll
soon step into a performance degradation.
To the best of my knowledge, there are two obvious problems:
1. We have to add or remove an entry in pg_class when temp table created and
deleted, resulting in "bloating" of pg_class. Thus, auto-vacuum is needed, but
it will acquire a lock, slowing things down.
2. Temp tables almost universally treated as regular tables. And this is 100%
correct and makes code much simpler. But also involve all the locking mechanism.
As for the first issue, I do not see how any significant improvements can be made,
unfortunately.
But for the second one: do we really need any lock for temp relations? AFAICU
they are backend only, apart from pg_class entries.
I do not have any particular solution for now, only some kind of concept: we can
put checks for temp relations in LockAcquire/LockRelease in order to skip
locking.
Do I miss something and idea is doomed or there are no visible obstacles here
and it's worth the effort to make a POC patch?
--
Working with temp relations is some kind of bottleneck in Postgres, in my view.
There are no problems if you want to handle it from time to time, not arguing
that. But if you have to make a massive temp tables creation/deletion, you'll
soon step into a performance degradation.
To the best of my knowledge, there are two obvious problems:
1. We have to add or remove an entry in pg_class when temp table created and
deleted, resulting in "bloating" of pg_class. Thus, auto-vacuum is needed, but
it will acquire a lock, slowing things down.
2. Temp tables almost universally treated as regular tables. And this is 100%
correct and makes code much simpler. But also involve all the locking mechanism.
As for the first issue, I do not see how any significant improvements can be made,
unfortunately.
But for the second one: do we really need any lock for temp relations? AFAICU
they are backend only, apart from pg_class entries.
I do not have any particular solution for now, only some kind of concept: we can
put checks for temp relations in LockAcquire/LockRelease in order to skip
locking.
Do I miss something and idea is doomed or there are no visible obstacles here
and it's worth the effort to make a POC patch?
--
Best regards,
Maxim Orlov.
Maxim Orlov <orlovmg@gmail.com> writes:
> But for the second one: do we really need any lock for temp relations?
Yes. Our implementation restrictions preclude access to the contents
of another session's temp tables, but it is not forbidden to do DDL
on them so long as no content access is required. (Without this,
it'd be problematic for example to clean out a crashed session's temp
tables. See the "orphan temporary tables" logic in autovacuum.c.)
You need fairly realistic locking to ensure that's OK.
regards, tom lane
On Mon, 30 Sept 2024 at 18:05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yes. Our implementation restrictions preclude access to the contents
of another session's temp tables, but it is not forbidden to do DDL
on them so long as no content access is required. (Without this,
it'd be problematic for example to clean out a crashed session's temp
tables. See the "orphan temporary tables" logic in autovacuum.c.)
OK, now I've realized the problem. Thanks a lot!
Best regards,
Maxim Orlov.
> Yes. Our implementation restrictions preclude access to the contents
> of another session's temp tables, but it is not forbidden to do DDL
> on them so long as no content access is required. (Without this,
> it'd be problematic for example to clean out a crashed session's temp
> tables. See the "orphan temporary tables" logic in autovacuum.c.)
Indeed, a potentially dangerous situation may arise when both the autovacuum and client process attempt to delete the contents of a temporary namespace. But there is a patch (6faca9ae2878c8f642a2e5748d2dbb2b91341bec) that protects us from race condition in this case. -- Best regards,
Daniil Davydov.> of another session's temp tables, but it is not forbidden to do DDL
> on them so long as no content access is required. (Without this,
> it'd be problematic for example to clean out a crashed session's temp
> tables. See the "orphan temporary tables" logic in autovacuum.c.)
Indeed, a potentially dangerous situation may arise when both the autovacuum and client process attempt to delete the contents of a temporary namespace. But there is a patch (6faca9ae2878c8f642a2e5748d2dbb2b91341bec) that protects us from race condition in this case. -- Best regards,
On Mon, 30 Sept 2024 at 18:05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yes. Our implementation restrictions preclude access to the contents
> of another session's temp tables, but it is not forbidden to do DDL
> on them so long as no content access is required. (Without this,
> it'd be problematic for example to clean out a crashed session's temp
> Yes. Our implementation restrictions preclude access to the contents
> of another session's temp tables, but it is not forbidden to do DDL
> on them so long as no content access is required. (Without this,
> it'd be problematic for example to clean out a crashed session's temp
> tables. See the "orphan temporary tables" logic in autovacuum.c.)
Indeed, a potentially dangerous situation may arise when both the autovacuum and client process attempt to delete the contents of a temporary namespace. But there is a patch (6faca9ae2878c8f642a2e5748d2dbb2b91341bec) that protects us from race condition in this case.
--
Best regards,
Daniil Davydov
On Tue, Oct 22, 2024 at 5:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Orlov <orlovmg@gmail.com> writes:
> But for the second one: do we really need any lock for temp relations?
Yes. Our implementation restrictions preclude access to the contents
of another session's temp tables, but it is not forbidden to do DDL
on them so long as no content access is required. (Without this,
it'd be problematic for example to clean out a crashed session's temp
tables. See the "orphan temporary tables" logic in autovacuum.c.)
You need fairly realistic locking to ensure that's OK.
regards, tom lane