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