Re: [Proposal] Global temporary tables

čt 30. 1. 2020 v 9:45 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 29.01.2020 20:37, Pavel Stehule wrote:
>
>
>
> 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.
>
>
> Building regular index requires two kinds of lock:
> 1. You have to lock pg_class to make changes in system catalog.
> 2. You need to lock heap relation to pervent concurrent updates while
> building index.
>
> GTT requires 1) but not 2).
> Once backend inserts information about new index in system catalog, all
> other sessions may use it. pg_class lock prevents any race condition here.
> And building index itself doesn't affect any other backends.
>

It is true. The difference for GTT, so any other sessions have to build
index (in your proposal) as extra operation against original plan.

Pavel

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