Re: [Proposal] Global temporary tables

st 29. 1. 2020 v 18:21 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 29.01.2020 20:08, Pavel Stehule wrote:
>
>
>
>
> 2. Actually I do not propose some completely new approach. I try to
>> provide behavior with is compatible with regular tables.
>> If you create index for regular table, then it can be used in all
>> sessions, right?
>>
>
> I don't understand to this point. Regular tables shares data, shares
> files. You cannot to separate it. More - you have to uses relatively
> aggressive locks to be this operation safe.
>
> Nothing from these points are valid for GTT.
>
>
> GTT shares metadata.
> As far as them are not sharing data, then GTT are safer than regular
> table, aren't them?
> "Safer" means that we need less "aggressive" locks for them: we need to
> protect only metadata, not data itself.
>
> My point is that if we allow other sessions to access created indexes for
> regular tables, then it will be not more complex to support it for GTT.
> Actually "not more complex" in this case means "no extra efforts are
> needed".
>

It is hard to say. I see a significant difference. When I do index on
regular table, then I don't change a context of other processes. I have to
wait for lock, and after I got a lock then other processes waiting.

With GTT, I don't want to wait for others - and other processes should
build indexes inside - without expected sequence of operations. Maybe it
can have positive effect, but it can have negative effect too. In this case
I prefer (in this moment) zero effect on other sessions. So I would to
build index in my session and I don't would to wait for other sessions, and
if it is possible other sessions doesn't need to interact or react on my
action too. It should be independent what is possible. The most simple
solution is request on unique usage. I understand so it can be not too
practical. Better is allow to usage GTT by other tables, but the changes
are invisible in other sessions to session reset. It is minimalistic
strategy. It has not benefits for other sessions, but it has not negative
impacts too.

> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>