Re: [Proposal] Global temporary tables

pá 24. 1. 2020 v 10:43 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 24.01.2020 12:09, Pavel Stehule wrote:
>
>
>
> pá 24. 1. 2020 v 9:39 odesílatel Konstantin Knizhnik <
> k(dot)knizhnik(at)postgrespro(dot)ru> napsal:
>
>>
>>
>> On 23.01.2020 23:47, Robert Haas wrote:
>> > On Sat, Jan 11, 2020 at 8:51 PM Tomas Vondra
>> > <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>> >> I proposed just ignoring those new indexes because it seems much
>> simpler
>> >> than alternative solutions that I can think of, and it's not like those
>> >> other solutions don't have other issues.
>> > +1.
>> >
>> >> For example, I've looked at the "on demand" building as implemented in
>> >> global_private_temp-8.patch, I kinda doubt adding a bunch of index
>> build
>> >> calls into various places in index code seems somewht suspicious.
>> > +1. I can't imagine that's a safe or sane thing to do.
>> >
>>
>> As far as you know there are two versions of GTT implementations now.
>> And we are going to merge them into single patch.
>> But there are some principle question concerning provided functionality
>> which has to be be discussed:
>> should we prohibit DDL on GTT if there are more than one sessions using
>> it. It includes creation/dropping indexes, dropping table, altering
>> table...
>>
>> If the answer is "yes", then the question whether to populate new
>> indexes with data is no relevant at all, because such situation will not
>> be possible.
>> But in this case we will get incompatible behavior with normal
>> (permanent) tables and it seems to be very inconvenient from DBA point
>> of view:
>> it will be necessary to enforce all clients to close their sessions to
>> perform some DDL manipulations with GTT.
>> Some DDLs will be very difficult to implement if GTT is used by more
>> than one backend, for example altering table schema.
>>
>> My current solution is to allow creation/droping index on GTT and
>> dropping table itself, while prohibit alter schema at all for GTT.
>> Wenjing's approach is to prohibit any DDL if GTT is used by more than
>> one backend.
>>
>
> When I create index on GTT in one session, then I don't expect creating
> same index in all other sessions that uses same GTT.
>
> But I can imagine to creating index on GTT enforces index in current
> session, and for other sessions this index will be invalid to end of
> session.
>
>
> So there are three possible alternatives:
>
> 1. Prohibit index creation of GTT when it used by more than once session.
> 2. Create index and populate them with data in all sessions using this GTT.
> 3. Create index only in current session and do not allow to use it in all
> other sessions already using this GTT (but definitely allow to use it in
> new sessions).
>
> 1 is Wenjing's approach, 2 - is my approach, 3 - is your suggestion :)
>
> I can construct the following table with pro/cons of each approach:
>
> Approach
> Compatibility with normal table
> User (DBA) friendly
> Complexity of implementation
> Consistency
> 1
> -
> 1: requires restart of all sessions to perform operation
> 2: requires global cache of GTT
> 3*: *no man, no problem
> 2
> +
> 3: if index is created then it is actually needed, isn't it? 1: use
> existed functionality to create index
> 2: if alter schema is prohibited
> 3
> -
> 2: requires restart of all sessions to use created index
> 3: requires some mechanism for prohibiting index created after first
> session access to GTT
> 1: can perform DDL but do no see effect of it
>
>
You will see a effect of DDL in current session (where you did the change),
all other sessions should to live without any any change do reconnect or to
RESET connect

I don't like 2 - when I do index on global temp table, I don't would to
wait on indexing on all other sessions. These operations should be
maximally independent.

Regards

Pavel

>
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>