Subject: Re: [Proposal] Global temporary tables
To: =?UTF-8?B?5pu+5paH5peMKOS5ieS7jik=?= <wenjing.zwj@alibaba-inc.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Pavel Stehule <pavel.stehule@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>, =?UTF-8?B?6JSh5p2+6Zyy?=
 =?UTF-8?B?KOWtkOWYiSk=?= <zijia@taobao.com>, "Cai, Le"
 <le.cai@alibaba-inc.com>, =?UTF-8?B?6JCn5bCR6IGqKOmTgeW6tSk=?=
 <shaocong.xsc@alibaba-inc.com>
References: <1A1A6EDC-D0EC-47B0-BD21-C2ACBAEA65E4@alibaba-inc.com>
 <0ef6f482-11ce-a99f-207e-26fa3f2f56db@postgrespro.ru>
 <CA+Tgmob_Fg9PfNjjOReJtAE4B5gc8+UqHh+tztJ_C+iRN4KHSw@mail.gmail.com>
 <CAFj8pRC6AoBLZgS-Xr-GCXyrJOkfN89eEsidLiAFZis0foF+Dw@mail.gmail.com>
 <CA+TgmoYaiZfD-K7LOweG1FuXr2zPyporfa8XOToOKmBzK+j+fw@mail.gmail.com>
 <8dc812ea-af3f-7a9d-ae9a-d89c25ca89ca@postgrespro.ru>
 <CA+TgmoZPeACc3yeQZeM_PijL8191jOCBzRnz4g1VP+Q5ULAYrA@mail.gmail.com>
 <0FC4E40B-4B4E-4B10-B2AA-F8E8E914F822@alibaba-inc.com>
 <e6fb54c2-bc4f-b8f0-f189-52d43be510ef@postgrespro.ru>
 <66623FEE-5EC8-45A2-8225-AF1B83D7197F@alibaba-inc.com>
 <7a321cef-f9ce-a42e-5860-54fcdf07e77a@postgrespro.ru>
 <F687C0B8-AE94-4A65-9AD9-BB83B874F425@alibaba-inc.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <745ef1fa-5a81-d30d-aa33-bce24a022a3a@postgrespro.ru>
Date: Fri, 8 Nov 2019 15:57:13 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <F687C0B8-AE94-4A65-9AD9-BB83B874F425@alibaba-inc.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 08.11.2019 10:50, 曾文旌(义从) wrote:
> In my opinion, it is not a good idea to trigger a btbuild with a select or DML, the cost of which depends on the amount of data in the GTT.
IMHO it is better than returning error.
Also index will be used only if cost of plan with index will be 
considered better than cost of plan without index. If you do not have 
index, then you have to scan the whole table.
Time of such scan is comparable with time of building index.

Yes, I agree that indexes for GTT are used to be created together with 
table itself before it is used by any application.
But if later DBA recognized that efficient execution of queries requires 
some more indexes,
it will be strange and dangerous to prevent him from adding such index 
until all clients which have accessed this table will drop their 
connections.
Also maintaining in shared memory information about attached backends 
seems to be overkill.

>>
>> This code initializes B-Tree and load data in it when GTT index is access and is not initialized yet.
>> It looks a little bit hacker but it works.
>>
>> I also wonder why you are keeping information about GTT in shared memory. Looks like the only information we really need to share is table's metadata.
>> But it is already shared though catalog. All other GTT related information is private to backend so I do not see reasons to place it in shared memory.
> The shared hash structure tracks which backend has initialized the GTT storage in order to implement the DDL of the GTT.
Sorry, I do not understand this argument.
DDL is performed on shared metadata present in global catalog.
Standard postgres invalidation mechanism is used to notify all backends 
about schema changes.
Why do we need to maintain some extra information in shared memory.
Can you give me example of DLL which does't work without such shared hash?

> As for GTT, there is only one definition(include index on GTT), but each backend may have one data.
> For the implementation of drop GTT, I assume that all data and definitions need to be deleted.

Data of dropped GTT is removed on normal backend termination or cleaned 
up at server restart in case of abnormal shutdown (as it is done for 
local temp tables).
I have not used any shared control structures for GTT in my 
implementation and that is why I wonder why do you need it and what are 
the expected problems with my
implementation?

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