Re: [Proposal] Global temporary tables

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Konstantin Knizhnik <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: 曾文旌(义从) <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: 蔡松露(子嘉) <[email protected]>
Cc: Cai, Le <[email protected]>
Cc: 萧少聪(铁庵) <[email protected]>
Subject: Re: [Proposal] Global temporary tables
Date: Mon, 27 Jan 2020 12:11:29 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRAvegZoQCLw3vjHZ=6_Kr7-1UAA5QgGYOshhXU6j_RuCg@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<20200105200603.wrb4beacwb57hhpk@development>
	<[email protected]>
	<[email protected]>
	<20200109164829.mdl3z5xalmaqxs67@development>
	<[email protected]>
	<20200112015109.caab6qdr2ob6d5jv@development>
	<CA+TgmobRhPA1gVbMsj9YLA-sv0Ub8NpMrkWCKwRiD=bnjVND3Q@mail.gmail.com>
	<[email protected]>
	<CAFj8pRAB4Ni+CL6n9QZ24wdp6n_2TLb2hYm60hs_KVXnDzN8tw@mail.gmail.com>
	<[email protected]>
	<CAFj8pRARb4BiT9Nb-YFg2h+EdbVQt8FY=L0utGM_FL6T-gjc-w@mail.gmail.com>
	<[email protected]>
	<CAFj8pRAvegZoQCLw3vjHZ=6_Kr7-1UAA5QgGYOshhXU6j_RuCg@mail.gmail.com>

On 24.01.2020 22:39, Pavel Stehule wrote:
> I cannot to evaluate your proposal, and I am sure, so you know more 
> about this code.
>
> There is a question if we can allow to build local temp index on 
> global temp table. It is different situation. When I work with global 
> properties personally I prefer total asynchronous implementation of 
> any DDL operations for other than current session. When it is true, 
> then I have not any objection. For me, good enough design of any DDL 
> can be based on catalog change without forcing to living tables.
>

 From my point of view there are two difference uses cases of temp tables:
1. Backend needs some private data source which is specific to this 
session and has no relation with activities of other sessions.
2. We need a table  containing private session data, but which is used 
in the same way by all database users.

In the first case current Postgres temp tables works well (if we forget 
for a moment about all known issues related with temp tables).
Global temp tables are used to address the second scenario.  Assume that 
we write some stored procedure or implement some business logic  outside 
database and
what to perform some complex analtic query which requires tepmp table 
for storing intermediate results. In this case we can create GTT with 
all needed index at the moment of database initialization
and do not perform any extra DDL during query execution. If will prevent 
catalog bloating and makes execution of query more efficient.

I do not see any reasons to allow build local indexes for global table. 
Yes,it can happen that some session will have small amount of data in 
particular GTT and another - small amount of data in this table. But if 
access pattern is the same  (and nature of GTT assumes it), then index 
in either appreciate, either useless in both cases.

> I see following disadvantage of your proposal. See scenario
>
> 1. I have two sessions
>
> A - small GTT with active owner
> B - big GTT with some active application.
>
> session A will do new index - it is fast, but if creating index is 
> forced on B on demand (when B was touched), then this operation have 
> to wait after index will be created.
>
> So I afraid build a index on other sessions on GTT when GTT tables in 
> other sessions will not be empty.

Yes, it is true. But is is not the most realistic scenario from my point 
of view.
As I explained above, GTT should be used when we need temporary storage 
accessed in the same way by all clients.
If (as with normal tables) at some moment of time DBA realizes, that 
efficient execution of some queries needs extra indexes,
then it should be able to do it. It is very inconvenient and unnatural 
to prohibit DBA to do it until all sessions using this GTT are closed 
(it may never happen)
or require all sessions to restart to be able to use this index.

So it is possible to imagine different scenarios of working with GTTs.
But from my point of view the only non-contradictory model of their 
behavior is to make it compatible with normal tables.
And do not forget about compatibility with Oracle. Simplifying of 
porting existed applications from Oracle to Postgres  may be the
main motivation of adding GTT to Postgres. And making them incompatible 
with Oracle will be very strange.

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

view thread (492+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [Proposal] Global temporary tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox