Re: Global temporary tables

Hi,

I am very interested in this feature that will conform to the SQL standard and I read that :

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?

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.

There is a confusion. Sadly it does not work like that at all with Oracle. Their implementation is buggy in my opinion.

Here is a very simple test case to prove it with the latest version (january 2020) :

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0<http://19.0.0.0>.0 - Production

Version 19.6.0.0<http://19.6.0.0>.0

-- session 1

create global temporary table gtt(x integer);

Table created.

-- session 2

insert into gtt SELECT level FROM dual CONNECT BY LEVEL <= 100000;

100000 rows created.

-- session 1

create index igtt on gtt(x);

Index created.

-- session 2

select * from gtt where x = 9;

no rows selected

select /*+ FULL(gtt) */ * from gtt where x = 9;

X

----------

9

What happened ? The optimizer (planner) knows the new index igtt can be efficient via dynamic sampling. Hence, igtt is used at execution time...but it is NOT populated. By default I obtained no line. If I force a full scan of the table with a hint /*+ FULL */ you can see that I obtain my line 9. Different results with different exec plans it's a WRONG RESULT bug, the worst kind of bugs.

Please don't consider Oracle as a reference for your implementation. I am 100% sure you can implement and document that better than Oracle. E.g index is populated and considered only for transactions that started after the index creation or something like that. It would be far better than this misleading behaviour.

Regards,

Phil

Télécharger Outlook pour Android<https://aka.ms/ghei36>

________________________________
From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Sent: Monday, February 10, 2020 5:48:29 PM
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>; Philippe BEAUDOIN <phb07(at)apra(dot)asso(dot)fr>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org <pgsql-hackers(at)lists(dot)postgresql(dot)org>; Konstantin Knizhnik <knizhnik(at)garret(dot)ru>
Subject: Re: Global temporary tables

Sorry, small typo in the last patch.

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