Re: [Proposal] Global temporary tables

On Wed, Feb 5, 2020 at 10:48 AM Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> > I don't understand. A global temporary table, as I understand it, is a
> > table for which each session sees separate contents. So you would
> > never need to populate it with existing data.
> Session 1:
> create global temp table gtt(x integer);
> insert into gtt values (generate_series(1,100000));
>
> Session 2:
> insert into gtt values (generate_series(1,200000));
>
> Session1:
> create index on gtt(x);
> explain select * from gtt where x = 1;
>
> Session2:
> explain select * from gtt where x = 1;
> ??? Should we use index here?

OK, I see where you're coming from now.

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

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.

> That is all - just 10 line of code.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company