Re: Global temporary tables - Mailing list pgsql-hackers

From Phil Florent
Subject Re: Global temporary tables
Date
Msg-id PR3P192MB054061BF72887F0AFE031735BA1B0@PR3P192MB0540.EURP192.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: Global temporary tables  (Konstantin Knizhnik <k.knizhnik@postgrespro.ru>)
List pgsql-hackers

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

Version 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


From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Sent: Monday, February 10, 2020 5:48:29 PM
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; Philippe BEAUDOIN <phb07@apra.asso.fr>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; Konstantin Knizhnik <knizhnik@garret.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

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: In PG12, query with float calculations is slower than PG11
Next
From: Fujii Masao
Date:
Subject: Wait event that should be reported while waiting for WAL archiving tofinish