Horton

Re: [Proposal] Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Robert Haas <robertmhaas@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2020-01-30T09:23:52Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. PageAddItemExtended(): Add LP_UNUSED assertion.

  2. Remove temporary files after backend crash

  3. Fix comment in indexing.c

  4. Fix failure to ignore leftover temp tables after a server crash.

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

>
>
> On 29.01.2020 20:37, Pavel Stehule wrote:
>
>
>
> st 29. 1. 2020 v 18:21 odesílatel Konstantin Knizhnik <
> k.knizhnik@postgrespro.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
>
>