Re: Global temporary tables

On 20.09.2019 19:43, Pavel Stehule wrote:
>
>     1. I do not need them at all.
>     2. Eliminate catalog bloating.
>     3. Mostly needed for compatibility with Oracle (simplify porting,...).
>     4. Parallel query execution.
>     5. Can be used at replica.
>     6. More efficient use of resources (first of all memory).
>
>
> There can be other point important for cloud. Inside some cloud 
> usually there are two types of discs - persistent (slow) and ephemeral 
> (fast). We effectively used temp tables there because we moved temp 
> tablespace to ephemeral discs.

Yes, I already heard this argument and agree with it.
I just want to notice two things:
1. My assumption is that in most cases data of temporary table can fit 
in memory (certainly if we are not limiting them by temp_buffers = 8MB, 
but store in shared buffers) and so there is on need to write them to 
the persistent media at all.
2. Global temp tables do not substitute local temp tables, accessed 
through local buffers. So if you want to use temporary storage, you will 
always have a way to do it.
The question is whether we need to support two kinds of global temp 
tables (with shared or private buffers) or just implement one of them.

>
> I missing one point in your list - developer's comfort - using temp 
> tables is just much more comfortable - you don't need create it again, 
> again, .. Due this behave is possible to reduce @2 and @3 can be nice 
> side effect. If you reduce @2 to zero, then @5 should be possible 
> without any other.
>
Sorry, I do not completely understand your point here
You can use normal (permanent) table and you will not have to create 
them again and again. It is also possible to use them for storing 
temporary data - just need to truncate table when data is not needed any 
more.
Certainly you can not use the same table in more than one backend. Here 
is the main advantage of temp tables - you can have storage of 
per-session data and do not worry about possible name conflicts.

 From the other side: there are many cases where format of temporary 
data is not statically known: it is determined dynamically during 
program execution.
In this case local temp table provides the most convenient mechanism for 
working with such data.

This is why I think that ewe need to have both local and global temp tables.

Also I do not agree with your statement "If you reduce @2 to zero, then 
@5 should be possible without any other".
In the solution implemented by Aleksander Alekseev metadata of temporary 
tables is kept in memory and not affecting catalog at all.
But them still can not be used at replica.
There are still some serious problems which need to be fixed to able it:
allow insert/update/delete statements for read-only transactions, 
somehow assign XIDs for them, implement savepoints and rollback of such 
transactions.
All this was done in the last version of my patch.
Yes, it doesn't depend on whether we are using shared or private buffers 
for temporary tables. The same approach can be implemented for both of them.
The question is whether we are really need temp tables at replica and if 
so, do we need full transaction support for them, including rollbacks, 
subtransactions.

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