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