Re: [Proposal] Global temporary tables

pá 7. 2. 2020 v 18:28 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 07.02.2020 18:15, Robert Haas wrote:
> > On Wed, Feb 5, 2020 at 10:48 AM Konstantin Knizhnik
> > <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> > My answer is - yes.
> >> Just because:
> >> - Such behavior is compatible with regular tables. So it will not
> >> confuse users and doesn't require some complex explanations.
> >> - It is compatible with Oracle.
> >> - It is what DBA usually want when creating index.
> >> -
> >> There are several arguments against such behavior:
> >> - Concurrent building of index in multiple sessions can consume a lot of
> >> memory
> >> - Building index can increase query execution time (which can be not
> >> expected by clients)
> > I think those are good arguments, especially the second one. There's
> > no limit on how long building a new index might take, and it could be
> > several minutes. A user who was running a query that could have
> > completed in a few seconds or even milliseconds will be unhappy to
> > suddenly wait a long time for a new index to be built. And that is an
> > entirely realistic scenario, because the new index might be better,
> > but only marginally.
> Yes, I agree that this arguments are important.
> But IMHO less important than incompatible behavior (Pavel doesn't agree
> with word "incompatible" in this context
> since semantic of temp tables is in any case different with semantic of
> regular tables).
>
> Just want to notice that if we have huge GTT (so that creation of index
> takes significant amount of time)
> sequential scan of this table also will not be fast.
>
> But in any case, if we agree that we can control thus behavior using GUC
> or index property,
> then it is ok for me.
>
>
>
> >
> > Also, an important point to which I've already alluded a few times is
> > that creating an index can fail. Now, one way it can fail is that
> > there could be some problem writing to disk, or you could run out of
> > memory, or whatever. However, it can also fail because the new index
> > is UNIQUE and the data this backend has in the table doesn't conform
> > to the associated constraint. It will be confusing if all access to a
> > table suddenly starts complaining about uniqueness violations.
>
> Yes, building index can fail (as any other operation with database).
> What's wring with it?
> If it is fatal error, then backend is terminated and content of its temp
> table is disappeared.
> If it is non-fatal error, then current transaction is aborted:
>
>
> Session1:
> postgres=# create global temp table gtt(x integer);
> CREATE TABLE
> postgres=# insert into gtt values (generate_series(1,100000));
> INSERT 0 100000
>
> Session2:
> postgres=# insert into gtt values (generate_series(1,100000));
> INSERT 0 100000
> postgres=# insert into gtt values (1);
> INSERT 0 1
>

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.

>
> > I don't believe that the feature you are proposing can be correctly
> > implemented in 10 lines of code. I would be pleasantly surprised if it
> > can be done in 1000.
> >
> Right now I do not see any sources of extra complexity.
> Will be pleased if you can point them to me.
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>