Horton

Re: [Proposal] Global temporary tables

Konstantin Knizhnik <k.knizhnik@postgrespro.ru>

View thread
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, Robert Haas <robertmhaas@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2019-11-06T16:08:12Z
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.

Attachments


On 06.11.2019 16:24, 曾文旌(义从) wrote:
> Dear Hackers
>
>
> I attached the patch of GTT implementationI base on PG12.
> The GTT design came from my first email.
> Some limitations in patch will be eliminated in later versions.
>
> Later, I will comment on Konstantin's patch and make some proposals for cooperation.
> Looking forward to your feedback.
>
> Thanks.
>
> Zeng Wenjing
>

Thank you for this patch.
My first comments:

1.  I have ported you patch to the latest Postgres version (my patch is 
attached).
2. You patch is supporting only B-Tree index for GTT. All other indexes 
(hash, gin, gist, brin,...) are not currently supported.
3. I do not understand the reason for the following limitation:
"We allow to create index on global temp table only this session use it"

First of all it seems to significantly reduce usage of global temp tables.
Why do we need GTT at all? Mostly because we need to access temporary 
data in more than one backend. Otherwise we can just use normal table.
If temp table is expected to be larger enough, so that we need to create 
index for it, then it is hard to believe that it will be needed only in 
one backend.

May be the assumption is that all indexes has to be created before GTT 
start to be used.
But right now this check is not working correctly in any case - if you 
insert some data into the table, then
you can not create index any more:

postgres=# create global temp table gtt(x integer primary key, y integer);
CREATE TABLE
postgres=# insert into gtt values (generate_series(1,100000), 
generate_series(1,100000));
INSERT 0 100000
postgres=# create index on gtt(y);
ERROR:  can not create index when have one or more backend attached this 
global temp table

I wonder why do you need such restriction?


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