Re: [Proposal] Global temporary tables

On 24.01.2020 15:15, Pavel Stehule wrote:

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

Why? I found this requirement quit unnatural and contradicting to the behavior of normal tables.
Actually one of motivation for adding global tempo tables to Postgres is to provide compatibility with Oracle.
Although I know that Oracle design decisions were never considered as  axioms by Postgres community,
but ni case of GTT design I think that we should take in account Oracle approach.
And GTT in Oracle behaves exactly as in my implementation:

https://www.oracletutorial.com/oracle-basics/oracle-global-temporary-table/

It is not clear from this documentation whether index created for GTT in one session can be used in another session which already has some data in this GTT.
But I did experiment with install Oracle server and  can confirm that actually works in this way.

So I do not understand why do we need to complicate our GTT implementation in order to prohibit useful functionality and introduce inconsistency between behavior of normal and global temp tables.

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.

Nobody suggest to wait building index in all sessions.
Indexes will be constructed on demand when session access this table.
If session will no access this table at all, then index will never be constructed.

Once again: logic of dealing with indexes in GTT is very simple.
For normal tables, indexes are initialized at the tame when them are created.
For GTT it is not true. We have to initialize index on demand when it is accessed first time in session.

So it has to be handled in any way.
The question is only whether we should allow creation of index for table already populated with some data?
Actually doesn't require some additional efforts. We can use existed build_index function which initialize index and populates it with data.
So the solution proposed for me is most natural, convenient and simplest solution at the same time. And compatible with Oracle.

Regards

Pavel

 

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

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