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).
