Re: [Proposal] Global temporary tables

On 27.01.2020 22:44, Pavel Stehule wrote:
>
> I don't think so compatibility with Oracle is valid point in this
> case. We need GTT, but the mechanism of index building should be
> designed for Postgres, and for users.
>
> Maybe the method proposed by you can be activated by some option like
> CREATE INDEX IMMEDIATELY FOR ALL SESSION. When you use GTT without
> index, then
> it should to work some time more, and if you use short life sessions,
> then index build can be last or almost last operation over table and
> can be suboptimal.
>
> Anyway, this behave can be changed later without bigger complications
> - and now I am have strong opinion to prefer don't allow to any DDL
> (with index creation) on any active GTT in other sessions.
> Probably your proposal - build indexes on other sessions when GTT is
> touched can share code with just modify metadata and wait on session
> reset or GTT reset
>
Well, compatibility with Oracle was never treated as important argument
in this group:)
But I hope that you agree that it real argument against your proposal.
Much more important argument is incompatibility with behavior of regular
table.
If you propose such incompatibility, then you should have some very
strong arguments for such behavior which will definitely confuse users.

But I heard only two arguments:

1. Concurrent building of indexes by all backends may consume much
memory (n_backends * maintenance_work_mem) and consume a lot of disk/CPU
resources.

First of all it is not completely true. Indexes will be created on
demand when GTT will be accessed and chance that all sessions will
become building indexes simultaneously is very small.

But what will happen if we prohibit access to this index for existed
sessions? If we need index for GTT, then most likely it is used for joins.
If there is no index, then optimizer has to choose some other plan to
perform this join. For example use hash join. Hash join also requires
memory,
so if all backends will perform such join simultaneously, then them
consume (n_backends * work_mem) memory.
Yes, work_mem is used to be smaller than maintenance_work_mem. But in
any case DBA has a choice to adjust this parameters to avoid this problem.
And in case of your proposal (prohibit access to this index) you give
him no choice to optimize query execution in existed sessions.

Also if all sessions will simultaneously perform sequential scan of GTT
instead of building index for it, then them will read the same amount of
data and consume comparable CPU time.
So prohibiting access to the indexes will not save us from high
resources consumption if all existed sessions are really actively
working with this GTT.

2. GTT in one session can contains large amount of data and we need
index for it, but small amount of data in another session and we do not
need index for it.

Such situation definitely can happen. But it contradicts to the main
assumption of GTT use case (that it is accessed in the same way by all
sessions).
Also I may be agree with this argument if you propose to create indexes
locally for each sessions.
But your proposal is to prohibit access to the index to the sessions
which already have populated GTT with data but allow it for sessions
which have not accessed this GTT yet.
So if some session stores some data in GTT after index was created, then
it will build index for it, doesn't matter whether size of table is
small or large.
Why do we make an exception for sessions which already have data in GTT
in this case?

So from my point of view both arguments are doubtful and can not explain
why rules of index usability for GTT should be different from regular
tables.

> Usually it is not hard problem to refresh sessions, and what I know
> when you update plpgsql code, it is best practice to refresh session
> early.
>

I know may systems where session is established once client is connected
to the system and not closed until client is disconnected.
And any attempt to force termination of the session will cause
application errors which are not expected by the client.

Sorry, I think that it is principle point in discussion concerning GTT
design.
Implementation of GTT can be changed in future, but it is bad if
behavior of GTT will be changed.
It is not clear for me why from the very beginning we should provide
inconsistent behavior which is even more difficult to implement than
behavior compatible with regular tables.
And say that in the future it can be changed...

Sorry, but I do not consider proposals to create indexes locally for
each session (i.e. global tables but private indexes) or use some
special complicated SQL syntax constructions like
CREATE INDEX IMMEDIATELY FOR ALL SESSION as some real alternatives which
have to be discussed.

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