Re: [Proposal] Global temporary tables

ne 9. 2. 2020 v 13:05 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

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

Thank you. I respect your opinion.

> 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 know nothing about this area - I expect so you and Wenjing will find
good solution.

We have to start with something what is simple, usable, and if it possible
it is well placed to Postgres's architecture.

at @1 .. when all tables are empty for other sessions, then I don't see any
problem. From practical reason, I think so requirement to don't use table
in other sessions is too hard, and I can be nice (maybe it is) if creating
index should not be blocked, but if I create index too late, then index is
for other session (when the table is used) invalid (again it can be done in
future).

I am sure, so there are not end of all days - and there is a space for
future enhancing and testing other variants. I can imagine more different
variations with different advantages/disadvantages. Just for begin I prefer
design that has concept closer to current Postgres.

Regards

Pavel