Re: [Proposal] Global temporary tables - Mailing list pgsql-hackers

From 曾文旌(义从)
Subject Re: [Proposal] Global temporary tables
Date
Msg-id 1A44D7F6-FBEC-4DDB-997C-FF05546F4B81@alibaba-inc.com
Whole thread Raw
In response to Re: [Proposal] Global temporary tables  (Konstantin Knizhnik <k.knizhnik@postgrespro.ru>)
List pgsql-hackers


2020年2月9日 下午8:05,Konstantin Knizhnik <k.knizhnik@postgrespro.ru> 写道:



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).
I made some improvements
1 Support for all indexes on GTT (using index_build build empty index).
2 Remove some ugly code in md.c bufmgr.c

Please give me feedback.


Wenjing

Attachment

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: plan cache overhead on plpgsql expression
Next
From: "曾文旌(义从)"
Date:
Subject: Re: [Proposal] Global temporary tables