Horton

Re: [Proposal] Global temporary tables

Konstantin Knizhnik <k.knizhnik@postgrespro.ru>

View thread
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Pavel Stehule <pavel.stehule@gmail.com>
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-02-09T12:05:04Z
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.


On 07.02.2020 21:37, Pavel Stehule wrote:
>
> What when session 2 has active transaction? Then to be correct, you 
> should to wait with index creation to end of transaction.
>
>
>     Session1:
>     postgres=# create unique index on gtt(x);
>     CREATE INDEX
>
>     Sessin2:
>     postgres=# explain select * from gtt where x=1;
>     ERROR:  could not create unique index "gtt_x_idx"
>     DETAIL:  Key (x)=(1) is duplicated.
>
>
> This is little bit unexpected behave (probably nobody expect so any 
> SELECT fail with error "could not create index" - I understand exactly 
> to reason and context, but this side effect is something what I afraid.
>
The more I thinking creation of indexes for GTT on-demand, the more 
contractions I see.
So looks like there are only two safe alternatives:
1. Allow DDL for GTT (including index creation) only if there are no 
other sessions using this GTT ("using" means that no data was inserted 
in GTT by this session). Things can be even more complicated if we take 
in account inter-table dependencies (like foreign key constraint).
2. Create indexes for GTT locally.

2) seems to be very contradictory (global table metadata, but private 
indexes) and hard to implement because in this case we have to maintain 
some private copy of index catalog to keep information about private 
indexes.

1) is currently implemented by Wenjing. Frankly speaking I still find 
such limitation too restrictive and inconvenient for users. From my 
point of view Oracle developers have implemented better compromise. But 
if I am the only person voting for such solution, then let's stop this 
discussion.
But in any case I think that calling ambuild to construct index for 
empty table is better solution than implementation of all indexes (and 
still not solving the problem with custom indexes).