[Proposal] Global temporary tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

[Proposal] Global temporary tables

曾文旌(义从)
Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist.

In recent years, PG community had many discussions about global temp table (GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(5) A recent implementation and design from Konstantin Knizhnik covered many functions of GTT: https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch

However, as pointed by Konstantin himself, the implementation still needs functions related to CLOG, vacuum, and MVCC visibility.

We developed GTT based on PG 11 and included most needed features, such as how to deal with concurrent DDL and DML operations, how to handle vacuum and too old relfrozenxids, and how to store and access GTT statistics. 

This design followed many suggestions from previous discussion in community. Here are some examples:
“have a separate 'relpersistence' setting for global temp tables…by having the backend id in all filename….   From Andres Freund
Use session memory context to store information related to GTT.   From Pavel Stehule
“extend the relfilenode mapper to support a backend-local non-persistent relfilenode map that's used to track temp table and index relfilenodes…” from Craig Ringer

Our implementation creates one record in pg_class for GTT’s schema definition. When rows are first inserted into the GTT in a session, a session specific file is created to store the GTT’s data. Those files are removed when the session ends. We maintain the GTT’s statistics in session local memory. DDL operations, such as DROP table or CREATE INDEX, can be executed on a GTT only by one session, while no other sessions insert any data into the GTT before or it is already truncated. This also avoids the concurrency of DML and DDL operations on GTT. We maintain a session level oldest relfrozenxids for GTT. This way, autovacuum or vacuum can truncate CLOG and increase global relfrozenxids based on all tables’ relfrozenxids, including GTT’s. 
The follows summarize the main design and implementation: 
Syntax: ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS
Data storage and buffering follows the same way as local temp table with a relfilenode including session id.
A hash table(A) in shared memory is used to track sessions and their usage of GTTs and to serialize DDL and DML operations. 
Another hash table(B) in session memory is introduced to record storage files for GTTs and their indexes. When a session ends, those files are removed. 
The same hash table(B) in session memory is used to record the relfrozenxids of each GTT. The oldest one is stored in myproc so that autovacuum and vacuum may use it to determine global oldest relfrozenxids and truncate clog. 
The same hash table(B) in session memory stores GTT’s session level statistics, It is generated during the operations of vacuum and analyze, and used by SQL optimizer to create execution plan. 
Some utility functions are added for DBA to manage GTTs. 
TRUNCATE command on a GTT behaves differently from that on a normal table. The command deletes the data immediately but keeps relfilenode using lower level table lock, RowExclusiveLock, instead of  AccessExclusiveLock. 
Main limits of this version or future improvement: need suggestions from community: 
1 VACUUM FULL and CLUSTER are not supported; any operations which may change relfilenode are disabled to GTT.
2 Sequence column is not supported in GTT for now.
3 Users defined statistics is not supported.


Details:

Requirement
The features list about global temp table:
1. global temp table (ON COMMIT clause is omitted, SQL specifies that the default behavior is ON COMMIT DELETE ROWS)
2. support with on commit DELETE ROWS
3. support with on commit PRESERVE ROWS
4. not support ON COMMIT DROP

Feature description
Global temp tables are defined just once and automatically exist (starting with empty contents) in every session that needs them.
Global temp table, each session use local buffer, read or write independent data files.
Use on commit DELETE ROWS for a transaction-specific global temp table. This is the default. database will truncate the table (delete all its rows) after each commit.
Use on commit PRESERVE ROWS Specify PRESERVE ROWS for a session-specific global temp table. databse will truncate the table (delete all its rows) when you terminate the session.

design
Global temp tables are designed based on local temp table(buffer and storage files). 
Because the catalog of global temp table is shared between sessions but the data is not shared, we need to build some new mechanisms to manage non-shared data and statistics for those data.

1. catalog
1.1 relpersistence
define RELPERSISTENCEGLOBALTEMP 'g'
Mark global temp table in pg_class relpersistence to 'T'. The relpersistence of the index created on the global temp table is also set to ’T'

1.2 on commit clause
In local temp table on commit DELETE ROWS and on commit PRESERVE ROWS not store in catalog, but GTT need.
Store a bool value oncommitdelete_rows to reloptions only for GTT and share with other session.

2. gram.y
Global temp table already has a syntax tree. jush need to remove the warning message "GLOBAL is deprecated in temporary table creation" and mark relpersistence = RELPERSISTENCEGLOBALTEMP

3. STORAGE
3.1. active_gtt_shared_hash
create a hash table in shared memory to trace the GTT files that are initialized in each session. 
Each hash entry contains a bitmap that records the backendid of the initialized GTT file.
With this hash table, we know which backend/session are using this GTT.
It will be used in GTT's DDL.

3.2. gtt_storage_local_hash
In each backend, create a local hashtable gtt_storage_local_hash for tracks GTT storage file and statistics.
1). GTT storage file track
When one session inserts data into a GTT for the first time, record to local hash.
2). normal clean GTT files
Use beforeshmemexit to ensure that all files for the session GTT are deleted when the session exits.
3). abnormal situation file cleanup
When a backend exits abnormally (such as oom kill), the startup process started to recovery before accept connect. startup process check and remove all GTT files before redo wal.

4 DDL
4.1 DROP GTT
One GTT table is allowed to be deleted when only the current session USES it. After get the AccessExclusiveLock of the GTT table, use active_gtt_shared_hash to check and make sure that.

4.2 ALTER GTT
Same as drop GTT.

4.3 CREATE INDEX ON GTT, DROP INDEX ON GTT
Same as drop GTT.

4.4 TRUNCATE GTT
The truncate GTT use RowExclusiveLock, not AccessExclusiveLock, Because truncate only cleans up local data file and local buffers in this session.
Also, truncate immediately deletes the data file without changing the relfilenode of the GTT table. btw, I'm not sure the implementation will be acceptable to the community.

4.5  create index on GTT
Same as drop GTT.

4.6 OTHERS
Any table operations about GTT that need to change relfilenode are disabled, such as vacuum full/cluster.

5. The statistics of GTT
1 relpages reltuples relallvisible frozenxid minmulti from pg_class
2 The statistics for each column from pg_statistic
All the above information will be stored to gtt_storage_local_hash.
When vacuum or analyze GTT's statistic will update, and the planner will use them. Of course, statistics only contain data within the current session.

5.1. View global temp table statistics
Provide pggttattstatistic get column statistics for GTT. Provide pggtt_relstats to rel statistics for GTT.
These functions are implemented in a plug-in, without add system view or function.

6. autovacuum
Autovacuum skips all GTT.

7. vacuum(frozenxid push, clog truncate)
The GTT data file contains transaction information. Queries for GTT data rely on transaction information such as clog. That's can not be vacuumed automatically by vacuum.
7.1 The session level gtt oldest frozenxid
When one GTT been create or remove, record the session level oldest frozenxid and put it into MyProc. 

7.1 vacuum
When vacuum push the db's frozenxid(vacupdatedatfrozenxid), need to consider the GTT. It needs to calculate the transactions required for the GTT(search all MyPorc), to avoid the clog required by GTT being cleaned.

8. Parallel query
Planner does not produce parallel query plans for SQL related to global temp table.

9. Operability
Provide pggttattachedpid lists all the pids that are using the GTT. Provide pglistgttrelfrozenxids lists the session level oldest frozenxid of using GTT.
These functions are implemented in a plug-in, without add system view or function.
DBA can use the above function and pgterminatebackend to force the cleanup of "too old" GTT tables and sessions.

10. Limitations and todo list
10.1. alter GTT
10.2. pg_statistic_ext
10.3. remove GTT's relfilenode can not change limit.
cluster/vacuum full, optimize truncate gtt.
10.4. SERIAL column type
The GTT from different sessions share a sequence(SERIAL type).
Need each session use the sequence independently.
10.5. Locking optimization for GTT.
10.6 materialized views is not support on GTT.


What do you thinking about this proposal?
Looking forward to your feedback.

Thanks!


regards

--
Zeng Wenjing
Alibaba Group-Database Products Business Unit


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik


On 11.10.2019 15:15, 曾文旌(义从) wrote:
Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist.

In recent years, PG community had many discussions about global temp table (GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(5) A recent implementation and design from Konstantin Knizhnik covered many functions of GTT: https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch

However, as pointed by Konstantin himself, the implementation still needs functions related to CLOG, vacuum, and MVCC visibility.


Just to clarify.
I have now proposed several different solutions for GTT:

Shared vs. private buffers for GTT:
1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

Access to GTT at replica:
1. Access is prohibited (as for original temp tables). No changes at all.
2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
If all of the following conditions are true:

1) GTT are used in joins
2) There are indexes defined for GTT
3) Size and histogram of GTT in different backends can significantly vary.
4) ANALYZE was explicitly called for GTT

then query execution plan built in one backend will be also used for other backends where it can be inefficient.
I also do not consider this problem as "show stopper" for adding GTT to Postgres.

I still do not understand the opinion of community which functionality of GTT is considered to be most important.
But the patch with local buffers and no replica support is small enough to become good starting point.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Pavel Stehule


pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 11.10.2019 15:15, 曾文旌(义从) wrote:
Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist.

In recent years, PG community had many discussions about global temp table (GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(5) A recent implementation and design from Konstantin Knizhnik covered many functions of GTT: https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch

However, as pointed by Konstantin himself, the implementation still needs functions related to CLOG, vacuum, and MVCC visibility.


Just to clarify.
I have now proposed several different solutions for GTT:

Shared vs. private buffers for GTT:
1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

This is important argument for using share buffers. Maybe the best is mix of both - store files in temporal tablespace, but using share buffers. More, it can be accessible for autovacuum.

Access to GTT at replica:
1. Access is prohibited (as for original temp tables). No changes at all.
2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
If all of the following conditions are true:

1) GTT are used in joins
2) There are indexes defined for GTT
3) Size and histogram of GTT in different backends can significantly vary.
4) ANALYZE was explicitly called for GTT

then query execution plan built in one backend will be also used for other backends where it can be inefficient.
I also do not consider this problem as "show stopper" for adding GTT to Postgres.

The last issue is show stopper in my mind. It really depends on usage. There are situation where shared statistics are ok (and maybe good solution), and other situation, where shared statistics are just unusable.

Regards

Pavel



I still do not understand the opinion of community which functionality of GTT is considered to be most important.
But the patch with local buffers and no replica support is small enough to become good starting point.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

曾文旌(义从)


2019年10月12日 下午1:16,Pavel Stehule <[hidden email]> 写道:



pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik <[hidden email]> napsal:


On 11.10.2019 15:15, 曾文旌(义从) wrote:
Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist.

In recent years, PG community had many discussions about global temp table (GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(5) A recent implementation and design from Konstantin Knizhnik covered many functions of GTT: https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch

However, as pointed by Konstantin himself, the implementation still needs functions related to CLOG, vacuum, and MVCC visibility.


Just to clarify.
I have now proposed several different solutions for GTT:

Shared vs. private buffers for GTT:
1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

This is important argument for using share buffers. Maybe the best is mix of both - store files in temporal tablespace, but using share buffers. More, it can be accessible for autovacuum.

Access to GTT at replica:
1. Access is prohibited (as for original temp tables). No changes at all.
2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT. 
If all of the following conditions are true:

1) GTT are used in joins
2) There are indexes defined for GTT
3) Size and histogram of GTT in different backends can significantly vary. 
4) ANALYZE was explicitly called for GTT

then query execution plan built in one backend will be also used for other backends where it can be inefficient.
I also do not consider this problem as "show stopper" for adding GTT to Postgres.

The last issue is show stopper in my mind. It really depends on usage. There are situation where shared statistics are ok (and maybe good solution), and other situation, where shared statistics are just unusable.
This proposal calculates and stores independent statistics(relpages reltuples and histogram of GTT) for the gtt data within each session, ensuring optimizer can get accurate statistics.


Regards

Pavel



I still do not understand the opinion of community which functionality of GTT is considered to be most important.
But the patch with local buffers and no replica support is small enough to become good starting point.


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

Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

曾文旌(义从)
In reply to this post by konstantin knizhnik


2019年10月11日 下午9:50,Konstantin Knizhnik <[hidden email]> 写道:



On 11.10.2019 15:15, 曾文旌(义从) wrote:
Dear Hackers,

This propose a way to develop global temporary tables in PostgreSQL.

I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist.

In recent years, PG community had many discussions about global temp table (GTT) support. Previous discussion covered the following topics: 
(1) The main benefit or function: GTT offers features like “persistent schema, ephemeral data”, which avoids catalog bloat and reduces catalog vacuum. 
(2) Whether follows ANSI concept of temporary tables
(3) How to deal with statistics, single copy of schema definition, relcache
(5) A recent implementation and design from Konstantin Knizhnik covered many functions of GTT: https://www.postgresql.org/message-id/attachment/103265/global_private_temp-1.patch

However, as pointed by Konstantin himself, the implementation still needs functions related to CLOG, vacuum, and MVCC visibility.


Just to clarify.
I have now proposed several different solutions for GTT:

Shared vs. private buffers for GTT:
1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

Access to GTT at replica:
1. Access is prohibited (as for original temp tables). No changes at all.
2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
If all of the following conditions are true:

1) GTT are used in joins
2) There are indexes defined for GTT
3) Size and histogram of GTT in different backends can significantly vary.
4) ANALYZE was explicitly called for GTT

then query execution plan built in one backend will be also used for other backends where it can be inefficient.
I also do not consider this problem as "show stopper" for adding GTT to Postgres.
When session A writes 10000000 rows of data to gtt X, session B also uses X at the same time and it has 100 rows of different data. If B uses analyze to count the statistics of 100000 rows of data and updates it to catalog.
Obviously, session A will get inaccurate query plan based on misaligned statistics when calculating the query plan for X related queries. Session A may think that table X is too small to be worth using index scan, but it is not. Each session needs to get the statistics of the self data to make the query plan.


I still do not understand the opinion of community which functionality of GTT is considered to be most important.
But the patch with local buffers and no replica support is small enough to become good starting point.
Yes ,the first step, we focus on complete basic functions of gtt (dml ddl index on gtt (MVCC visibility rules) storage).
Abnormal statistics can cause problems with index selection on gtt, so index on gtt and accurate statistical information is necessary.



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

Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
In reply to this post by konstantin knizhnik
On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
<[hidden email]> wrote:
> Just to clarify.
> I have now proposed several different solutions for GTT:
>
> Shared vs. private buffers for GTT:
> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

I vote for #1. I think parallel query for temp objects may be a
desirable feature, but I don't think it should be the job of a patch
implementing GTTs to make it happen. In fact, I think it would be an
actively bad idea, because I suspect that if we do eventually support
temp relations for parallel query, we're going to want a solution that
is shared between regular temp tables and global temp tables, not
separate solutions for each.

> Access to GTT at replica:
> 1. Access is prohibited (as for original temp tables). No changes at all.
> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
> 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
> and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

I again vote for #1. A GTT is defined to allow data to be visible only
within one session -- so what does it even mean for the data to be
accessible on a replica?

> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
> If all of the following conditions are true:
>
> 1) GTT are used in joins
> 2) There are indexes defined for GTT
> 3) Size and histogram of GTT in different backends can significantly vary.
> 4) ANALYZE was explicitly called for GTT
>
> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
> I also do not consider this problem as "show stopper" for adding GTT to Postgres.

I think that's *definitely* a show stopper.

> I still do not understand the opinion of community which functionality of GTT is considered to be most important.
> But the patch with local buffers and no replica support is small enough to become good starting point.

Well, it seems we now have two patches for this feature. I guess we
need to figure out which one is better, and whether it's possible for
the two efforts to be merged, rather than having two different teams
hacking on separate code bases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Pavel Stehule


pá 25. 10. 2019 v 17:01 odesílatel Robert Haas <[hidden email]> napsal:
On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
<[hidden email]> wrote:
> Just to clarify.
> I have now proposed several different solutions for GTT:
>
> Shared vs. private buffers for GTT:
> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.

I vote for #1. I think parallel query for temp objects may be a
desirable feature, but I don't think it should be the job of a patch
implementing GTTs to make it happen. In fact, I think it would be an
actively bad idea, because I suspect that if we do eventually support
temp relations for parallel query, we're going to want a solution that
is shared between regular temp tables and global temp tables, not
separate solutions for each.

> Access to GTT at replica:
> 1. Access is prohibited (as for original temp tables). No changes at all.
> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
> 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
> and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).

I again vote for #1. A GTT is defined to allow data to be visible only
within one session -- so what does it even mean for the data to be
accessible on a replica?

why not? there are lot of sessions on replica servers. One usage of temp tables is fixing estimation errors. You can create temp table with partial query result, run ANALYZE and evaluate other steps. Now this case is not possible on replica servers.

One motivation for GTT  is decreasing port costs from Oracle. But other motivations, like do more complex calculations on replica are valid and valuable.



> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
> If all of the following conditions are true:
>
> 1) GTT are used in joins
> 2) There are indexes defined for GTT
> 3) Size and histogram of GTT in different backends can significantly vary.
> 4) ANALYZE was explicitly called for GTT
>
> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
> I also do not consider this problem as "show stopper" for adding GTT to Postgres.

I think that's *definitely* a show stopper.

> I still do not understand the opinion of community which functionality of GTT is considered to be most important.
> But the patch with local buffers and no replica support is small enough to become good starting point.

Well, it seems we now have two patches for this feature. I guess we
need to figure out which one is better, and whether it's possible for
the two efforts to be merged, rather than having two different teams
hacking on separate code bases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik
In reply to this post by Robert Haas


On 25.10.2019 18:01, Robert Haas wrote:

> On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
> <[hidden email]> wrote:
>> Just to clarify.
>> I have now proposed several different solutions for GTT:
>>
>> Shared vs. private buffers for GTT:
>> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
>> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.
> I vote for #1. I think parallel query for temp objects may be a
> desirable feature, but I don't think it should be the job of a patch
> implementing GTTs to make it happen. In fact, I think it would be an
> actively bad idea, because I suspect that if we do eventually support
> temp relations for parallel query, we're going to want a solution that
> is shared between regular temp tables and global temp tables, not
> separate solutions for each.

Sorry, may be I do not not understand you.
It seems to me that there is only one thing preventing usage of
temporary tables in parallel plans: private buffers.
If global temporary tables are accessed as normal tables though shared
buffers then them can be used in parallel queries
and no extra support is required for it.
At least I have checked that parallel queries are correctly worked for
my implementation of GTT with shared buffers.
So I do not understand about which "separate solutions" you are talking
about.

I can agree that private buffers may be  good starting point for GTT
implementation, because it is less invasive and GTT access speed is
exactly the same as of normal temp tables.
But I do not understand your argument why it is "actively bad idea".

>> Access to GTT at replica:
>> 1. Access is prohibited (as for original temp tables). No changes at all.
>> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
>> 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
>> and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).
> I again vote for #1. A GTT is defined to allow data to be visible only
> within one session -- so what does it even mean for the data to be
> accessible on a replica?

There are sessions at replica (in case of hot standby), aren't there?

>
>> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
>> If all of the following conditions are true:
>>
>> 1) GTT are used in joins
>> 2) There are indexes defined for GTT
>> 3) Size and histogram of GTT in different backends can significantly vary.
>> 4) ANALYZE was explicitly called for GTT
>>
>> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
>> I also do not consider this problem as "show stopper" for adding GTT to Postgres.
> I think that's *definitely* a show stopper.
Well, if both you and Pavel think that it is really "show stopper", then
this problem really has to be addressed.
I slightly confused about this opinion, because Pavel has told me
himself that 99% of users never create indexes for temp tables
or run "analyze" for them. And without it, this problem is not a problem
at all.

>> I still do not understand the opinion of community which functionality of GTT is considered to be most important.
>> But the patch with local buffers and no replica support is small enough to become good starting point.
> Well, it seems we now have two patches for this feature. I guess we
> need to figure out which one is better, and whether it's possible for
> the two efforts to be merged, rather than having two different teams
> hacking on separate code bases.

I am open for cooperations.
Source code of all my patches is available.

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



Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Pavel Stehule

>
>> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
>> If all of the following conditions are true:
>>
>> 1) GTT are used in joins
>> 2) There are indexes defined for GTT
>> 3) Size and histogram of GTT in different backends can significantly vary.
>> 4) ANALYZE was explicitly called for GTT
>>
>> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
>> I also do not consider this problem as "show stopper" for adding GTT to Postgres.
> I think that's *definitely* a show stopper.
Well, if both you and Pavel think that it is really "show stopper", then
this problem really has to be addressed.
I slightly confused about this opinion, because Pavel has told me
himself that 99% of users never create indexes for temp tables
or run "analyze" for them. And without it, this problem is not a problem
at all.


Users doesn't do ANALYZE on temp tables in 99%. It's true. But second fact is so users has lot of problems. It's very similar to wrong statistics on persistent tables. When data are small, then it is not problem for users, although from my perspective it's not optimal. When data are not small, then the problem can be brutal. Temporary tables are not a exception. And users and developers are people - we know only about fatal problems. There are lot of unoptimized queries, but because the problem is not fatal, then it is not reason for report it. And lot of people has not any idea how fast the databases can be. The knowledges of  users and app developers are sad book.

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

曾文旌(义从)
In reply to this post by konstantin knizhnik


> 2019年10月26日 上午12:22,Konstantin Knizhnik <[hidden email]> 写道:
>
>
>
> On 25.10.2019 18:01, Robert Haas wrote:
>> On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
>> <[hidden email]> wrote:
>>> Just to clarify.
>>> I have now proposed several different solutions for GTT:
>>>
>>> Shared vs. private buffers for GTT:
>>> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
>>> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.
>> I vote for #1. I think parallel query for temp objects may be a
>> desirable feature, but I don't think it should be the job of a patch
>> implementing GTTs to make it happen. In fact, I think it would be an
>> actively bad idea, because I suspect that if we do eventually support
>> temp relations for parallel query, we're going to want a solution that
>> is shared between regular temp tables and global temp tables, not
>> separate solutions for each.
>
> Sorry, may be I do not not understand you.
> It seems to me that there is only one thing preventing usage of temporary tables in parallel plans: private buffers.
> If global temporary tables are accessed as normal tables though shared buffers then them can be used in parallel queries
> and no extra support is required for it.
> At least I have checked that parallel queries are correctly worked for my implementation of GTT with shared buffers.
> So I do not understand about which "separate solutions" you are talking about.
>
> I can agree that private buffers may be  good starting point for GTT implementation, because it is less invasive and GTT access speed is exactly the same as of normal temp tables.
> But I do not understand your argument why it is "actively bad idea".
>
>>> Access to GTT at replica:
>>> 1. Access is prohibited (as for original temp tables). No changes at all.
>>> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
>>> 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
>>> and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).
>> I again vote for #1. A GTT is defined to allow data to be visible only
>> within one session -- so what does it even mean for the data to be
>> accessible on a replica?
>
> There are sessions at replica (in case of hot standby), aren't there?
>
>>
>>> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
>>> If all of the following conditions are true:
>>>
>>> 1) GTT are used in joins
>>> 2) There are indexes defined for GTT
>>> 3) Size and histogram of GTT in different backends can significantly vary.
>>> 4) ANALYZE was explicitly called for GTT
>>>
>>> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
>>> I also do not consider this problem as "show stopper" for adding GTT to Postgres.
>> I think that's *definitely* a show stopper.
> Well, if both you and Pavel think that it is really "show stopper", then this problem really has to be addressed.
> I slightly confused about this opinion, because Pavel has told me himself that 99% of users never create indexes for temp tables
> or run "analyze" for them. And without it, this problem is not a problem at all.
>
>>> I still do not understand the opinion of community which functionality of GTT is considered to be most important.
>>> But the patch with local buffers and no replica support is small enough to become good starting point.
>> Well, it seems we now have two patches for this feature. I guess we
>> need to figure out which one is better, and whether it's possible for
>> the two efforts to be merged, rather than having two different teams
>> hacking on separate code bases.
>
> I am open for cooperations.
> Source code of all my patches is available.
We are also willing to cooperate to complete this feature.
Let me prepare the code(merge code to pg12) and up to community, then see how we work together.

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



Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
In reply to this post by Pavel Stehule
On Fri, Oct 25, 2019 at 11:14 AM Pavel Stehule <[hidden email]> wrote:

>> > Access to GTT at replica:
>> > 1. Access is prohibited (as for original temp tables). No changes at all.
>> > 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
>> > 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
>> > and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).
>>
>> I again vote for #1. A GTT is defined to allow data to be visible only
>> within one session -- so what does it even mean for the data to be
>> accessible on a replica?
>
> why not? there are lot of sessions on replica servers. One usage of temp tables is fixing estimation errors. You can create temp table with partial query result, run ANALYZE and evaluate other steps. Now this case is not possible on replica servers.
>
> One motivation for GTT  is decreasing port costs from Oracle. But other motivations, like do more complex calculations on replica are valid and valuable.

Hmm, I think I was slightly confused when I wrote my previous
response. I now see that what was under discussion was not making data
from the master visible on the standbys, which really wouldn't make
any sense, but rather allowing standby sessions to also use the GTT,
each with its own local copy of the data. I don't think that's a bad
feature, but look how invasive the required changes are. Not allowing
rollbacks seems dead on arrival; an abort would be able to leave the
table and index mutually inconsistent.  A separate XID space would be
a real solution, perhaps, but it would be *extremely* complicated and
invasive to implement.

One thing that I've learned over and over again as a developer is that
you get a lot more done if you tackle one problem at a time. GTTs are
a sufficiently-large problem all by themselves; a major reworking of
the way XIDs work might be a good project to undertake at some point,
but it doesn't make any sense to incorporate that into the GTT
project, which is otherwise about a mostly-separate set of issues.
Let's not try to solve more problems at once than strictly necessary.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
In reply to this post by konstantin knizhnik
On Fri, Oct 25, 2019 at 12:22 PM Konstantin Knizhnik
<[hidden email]> wrote:

> On 25.10.2019 18:01, Robert Haas wrote:
> > On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
> > <[hidden email]> wrote:
> >> Just to clarify.
> >> I have now proposed several different solutions for GTT:
> >>
> >> Shared vs. private buffers for GTT:
> >> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
> >> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.
> > I vote for #1. I think parallel query for temp objects may be a
> > desirable feature, but I don't think it should be the job of a patch
> > implementing GTTs to make it happen. In fact, I think it would be an
> > actively bad idea, because I suspect that if we do eventually support
> > temp relations for parallel query, we're going to want a solution that
> > is shared between regular temp tables and global temp tables, not
> > separate solutions for each.
>
> Sorry, may be I do not not understand you.
> It seems to me that there is only one thing preventing usage of
> temporary tables in parallel plans: private buffers.
> If global temporary tables are accessed as normal tables though shared
> buffers then them can be used in parallel queries
> and no extra support is required for it.
> At least I have checked that parallel queries are correctly worked for
> my implementation of GTT with shared buffers.
> So I do not understand about which "separate solutions" you are talking
> about.
>
> I can agree that private buffers may be  good starting point for GTT
> implementation, because it is less invasive and GTT access speed is
> exactly the same as of normal temp tables.
> But I do not understand your argument why it is "actively bad idea".

Well, it sounds like you're talking about ending up in a situation
where local temporary tables are still in private buffers, but global
temporary table data is in shared buffers. I think that would be
inconsistent. And it would mean that when somebody wanted to make
local temporary tables accessible in parallel query, they'd have to
write a patch for that.  In other words, I don't support dividing the
patches like this:

Patch #1: Support global temporary tables + allow global temporary
tables to used by parallel query
Patch #2: Allow local temporary tables to be used by parallel query

I support dividing them like this:

Patch #1: Support global temporary tables
Patch #2: Allow (all kinds of) temporary tables to be used by parallel query

The second division looks a lot cleaner to me, although as always I
might be missing something.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik
In reply to this post by Robert Haas


On 28.10.2019 15:07, Robert Haas wrote:

> On Fri, Oct 25, 2019 at 11:14 AM Pavel Stehule <[hidden email]> wrote:
>>>> Access to GTT at replica:
>>>> 1. Access is prohibited (as for original temp tables). No changes at all.
>>>> 2. Tuples of temp tables are marked with forzen XID.  Minimal changes, rollbacks are not possible.
>>>> 3. Providing special XIDs for GTT at replica. No changes in CLOG are required, but special MVCC visibility rules are used for GTT. Current limitation: number of transactions accessing GTT at replica is limited by 2^32
>>>> and bitmap of correspondent size has to be maintained (tuples of GTT are not proceeded by vacuum and not frozen, so XID horizon never moved).
>>> I again vote for #1. A GTT is defined to allow data to be visible only
>>> within one session -- so what does it even mean for the data to be
>>> accessible on a replica?
>> why not? there are lot of sessions on replica servers. One usage of temp tables is fixing estimation errors. You can create temp table with partial query result, run ANALYZE and evaluate other steps. Now this case is not possible on replica servers.
>>
>> One motivation for GTT  is decreasing port costs from Oracle. But other motivations, like do more complex calculations on replica are valid and valuable.
> Hmm, I think I was slightly confused when I wrote my previous
> response. I now see that what was under discussion was not making data
> from the master visible on the standbys, which really wouldn't make
> any sense, but rather allowing standby sessions to also use the GTT,
> each with its own local copy of the data. I don't think that's a bad
> feature, but look how invasive the required changes are. Not allowing
> rollbacks seems dead on arrival; an abort would be able to leave the
> table and index mutually inconsistent.  A separate XID space would be
> a real solution, perhaps, but it would be *extremely* complicated and
> invasive to implement.
Sorry, but both statements are not true.
As I mentioned before, I have implemented both solutions.

I am not sure how vital is lack of aborts for transactions working with
GTT at replica.
Some people said that there is no sense in aborts of read-only
transactions at replica (despite to the fact that them are saving
intermediate results in GTT).
Some people said something similar with your's "dead on arrival".
But inconsistency is not possible: if such transaction is really
aborted, then backend is terminated and nobody can see this inconsistency.

Concerning second alternative: you can check yourself that it is not
*extremely* complicated and invasive.
I extracted changes which are related with handling transactions at
replica and attached them to this mail.
It is just 500 lines (including diff contexts). Certainly there are some
limitation of this implementation: number of  transactions working with
GTT at replica is limited by 2^32
and since GTT tuples are not frozen, analog of GTT CLOG kept in memory
is never truncated.

>
> One thing that I've learned over and over again as a developer is that
> you get a lot more done if you tackle one problem at a time. GTTs are
> a sufficiently-large problem all by themselves; a major reworking of
> the way XIDs work might be a good project to undertake at some point,
> but it doesn't make any sense to incorporate that into the GTT
> project, which is otherwise about a mostly-separate set of issues.
> Let's not try to solve more problems at once than strictly necessary.
>
I agree with it and think that implementation of GTT with private
buffers and no replica access is good starting point.

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


replica-gtt-xact.diff (18K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik
In reply to this post by Robert Haas


On 28.10.2019 15:13, Robert Haas wrote:

> On Fri, Oct 25, 2019 at 12:22 PM Konstantin Knizhnik
> <[hidden email]> wrote:
>> On 25.10.2019 18:01, Robert Haas wrote:
>>> On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik
>>> <[hidden email]> wrote:
>>>> Just to clarify.
>>>> I have now proposed several different solutions for GTT:
>>>>
>>>> Shared vs. private buffers for GTT:
>>>> 1. Private buffers. This is least invasive patch, requiring no changes in relfilenodes.
>>>> 2. Shared buffers. Requires changing relfilenode but supports parallel query execution for GTT.
>>> I vote for #1. I think parallel query for temp objects may be a
>>> desirable feature, but I don't think it should be the job of a patch
>>> implementing GTTs to make it happen. In fact, I think it would be an
>>> actively bad idea, because I suspect that if we do eventually support
>>> temp relations for parallel query, we're going to want a solution that
>>> is shared between regular temp tables and global temp tables, not
>>> separate solutions for each.
>> Sorry, may be I do not not understand you.
>> It seems to me that there is only one thing preventing usage of
>> temporary tables in parallel plans: private buffers.
>> If global temporary tables are accessed as normal tables though shared
>> buffers then them can be used in parallel queries
>> and no extra support is required for it.
>> At least I have checked that parallel queries are correctly worked for
>> my implementation of GTT with shared buffers.
>> So I do not understand about which "separate solutions" you are talking
>> about.
>>
>> I can agree that private buffers may be  good starting point for GTT
>> implementation, because it is less invasive and GTT access speed is
>> exactly the same as of normal temp tables.
>> But I do not understand your argument why it is "actively bad idea".
> Well, it sounds like you're talking about ending up in a situation
> where local temporary tables are still in private buffers, but global
> temporary table data is in shared buffers. I think that would be
> inconsistent. And it would mean that when somebody wanted to make
> local temporary tables accessible in parallel query, they'd have to
> write a patch for that.  In other words, I don't support dividing the
> patches like this:
>
> Patch #1: Support global temporary tables + allow global temporary
> tables to used by parallel query
> Patch #2: Allow local temporary tables to be used by parallel query
>
> I support dividing them like this:
>
> Patch #1: Support global temporary tables
> Patch #2: Allow (all kinds of) temporary tables to be used by parallel query
>
> The second division looks a lot cleaner to me, although as always I
> might be missing something.
>
Logically it may be good decision. But piratically support of parallel
access to GTT requires just accessing their data through shared buffer.
But in case of local temp tables we need also need to some how share
table's metadata between parallel workers. It seems to be much more
complicated if ever possible.

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



Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
On Mon, Oct 28, 2019 at 9:48 AM Konstantin Knizhnik
<[hidden email]> wrote:
> Logically it may be good decision. But piratically support of parallel
> access to GTT requires just accessing their data through shared buffer.
> But in case of local temp tables we need also need to some how share
> table's metadata between parallel workers. It seems to be much more
> complicated if ever possible.

Why? The backends all share a snapshot, and can load whatever they
need from the system catalogs.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
In reply to this post by konstantin knizhnik
On Mon, Oct 28, 2019 at 9:37 AM Konstantin Knizhnik
<[hidden email]> wrote:
> Sorry, but both statements are not true.

Well, I think they are true.

> I am not sure how vital is lack of aborts for transactions working with
> GTT at replica.
> Some people said that there is no sense in aborts of read-only
> transactions at replica (despite to the fact that them are saving
> intermediate results in GTT).
> Some people said something similar with your's "dead on arrival".
> But inconsistency is not possible: if such transaction is really
> aborted, then backend is terminated and nobody can see this inconsistency.

Aborting the current transaction is a very different thing from
terminating the backend.

Also, the idea that there is no sense in aborts of read-only
transactions on a replica seems totally wrong. Suppose that you insert
a row into the table and then you go to insert a row in each index,
but one of the index inserts fails - duplicate key, out of memory
error, I/O error, whatever. Now the table and the index are
inconsistent. Normally, we're protected against this by MVCC, but if
you use a solution that breaks MVCC by using the same XID for all
transactions, then it can happen.

> Concerning second alternative: you can check yourself that it is not
> *extremely* complicated and invasive.
> I extracted changes which are related with handling transactions at
> replica and attached them to this mail.
> It is just 500 lines (including diff contexts). Certainly there are some
> limitation of this implementation: number of  transactions working with
> GTT at replica is limited by 2^32
> and since GTT tuples are not frozen, analog of GTT CLOG kept in memory
> is never truncated.

I admit that this patch is not lengthy, but there remains the question
of whether it is correct. It's possible that the problem isn't as
complicated as I think it is, but I do think there are quite a number
of reasons why this patch wouldn't be considered acceptable...

> I agree with it and think that implementation of GTT with private
> buffers and no replica access is good starting point.

...but given that we seem to agree on this point, perhaps it isn't
necessary to argue about those things right now.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik


On 28.10.2019 19:40, Robert Haas wrote:

> Aborting the current transaction is a very different thing from
> terminating the backend.
>
> Also, the idea that there is no sense in aborts of read-only
> transactions on a replica seems totally wrong. Suppose that you insert
> a row into the table and then you go to insert a row in each index,
> but one of the index inserts fails - duplicate key, out of memory
> error, I/O error, whatever. Now the table and the index are
> inconsistent. Normally, we're protected against this by MVCC, but if
> you use a solution that breaks MVCC by using the same XID for all
> transactions, then it can happen.

Certainly I understand the difference between abort of transaction and
termination of backend.
I do not say that it is good solution. And definitely aborts can happen
for read-only transactions.
I just wanted to express one moment: transaction aborts are caused by
two reasons:
- expected programming errors: deadlocks, conversion errors, unique
constraint violation,...
- unexpected system errors: disk space exhaustion, out of memory, I/O
errors...

Usually at replica with read-only transactions we do not have to deal
with errors of first kind.
So transaction may be aborted, but such abort most likely means that
something is wrong with the system
and restart of backend is not so bad solution in this situation.

In any case, I do not insist on this "frozen XID" approach.
The only advantage of this approach is that it is very simple to
implement: correspondent patch contains just 80 lines of code
and actually it requires just 5 (five) one-line changes.
I didn't agree with your statement just because restart of backend makes
it not possible to observe any inconsistencies in the database.

> ...but given that we seem to agree on this point, perhaps it isn't
> necessary to argue about those things right now.
>
Ok.
I attached new patch for GTT with local (private) buffer and no replica
access.
It provides GTT for all built-in indexes


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


global_private_temp-3.patch (61K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik
In reply to this post by Pavel Stehule


On 25.10.2019 20:00, Pavel Stehule wrote:

>
>> So except the limitation mentioned above (which I do not consider as critical) there is only one problem which was not addressed: maintaining statistics for GTT.
>> If all of the following conditions are true:
>>
>> 1) GTT are used in joins
>> 2) There are indexes defined for GTT
>> 3) Size and histogram of GTT in different backends can significantly vary.
>> 4) ANALYZE was explicitly called for GTT
>>
>> then query execution plan built in one backend will be also used for other backends where it can be inefficient.
>> I also do not consider this problem as "show stopper" for adding GTT to Postgres.
> I think that's *definitely* a show stopper.
Well, if both you and Pavel think that it is really "show stopper", then
this problem really has to be addressed.
I slightly confused about this opinion, because Pavel has told me
himself that 99% of users never create indexes for temp tables
or run "analyze" for them. And without it, this problem is not a problem
at all.


Users doesn't do ANALYZE on temp tables in 99%. It's true. But second fact is so users has lot of problems. It's very similar to wrong statistics on persistent tables. When data are small, then it is not problem for users, although from my perspective it's not optimal. When data are not small, then the problem can be brutal. Temporary tables are not a exception. And users and developers are people - we know only about fatal problems. There are lot of unoptimized queries, but because the problem is not fatal, then it is not reason for report it. And lot of people has not any idea how fast the databases can be. The knowledges of  users and app developers are sad book.

Pavel

It seems to me that I have found quite elegant solution for per-backend statistic for GTT: I just inserting it in backend's catalog cache, but not in pg_statistic table itself.
To do it I have to add InsertSysCache/InsertCatCache functions which insert pinned entry in the correspondent cache.
I wonder if there are some pitfalls of such approach?

New patch for GTT is attached.
-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

global_private_temp-4.patch (69K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

Robert Haas
On Fri, Nov 1, 2019 at 11:15 AM Konstantin Knizhnik
<[hidden email]> wrote:
> It seems to me that I have found quite elegant solution for per-backend statistic for GTT: I just inserting it in backend's catalog cache, but not in pg_statistic table itself.
> To do it I have to add InsertSysCache/InsertCatCache functions which insert pinned entry in the correspondent cache.
> I wonder if there are some pitfalls of such approach?

That sounds pretty hackish. You'd have to be very careful, for
example, that if the tables were dropped or re-analyzed, all of the
old entries got removed -- and then it would still fail if any code
tried to access the statistics directly from the table, rather than
via the caches. My assumption is that the statistics ought to be
stored in some backend-private data structure designed for that
purpose, and that the code that needs the data should be taught to
look for it there when the table is a GTT.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: [Proposal] Global temporary tables

konstantin knizhnik


On 01.11.2019 18:26, Robert Haas wrote:
> On Fri, Nov 1, 2019 at 11:15 AM Konstantin Knizhnik
> <[hidden email]> wrote:
>> It seems to me that I have found quite elegant solution for per-backend statistic for GTT: I just inserting it in backend's catalog cache, but not in pg_statistic table itself.
>> To do it I have to add InsertSysCache/InsertCatCache functions which insert pinned entry in the correspondent cache.
>> I wonder if there are some pitfalls of such approach?
> That sounds pretty hackish. You'd have to be very careful, for
> example, that if the tables were dropped or re-analyzed, all of the
> old entries got removed --

I have checked it:
- when table is reanalyzed, then cache entries are replaced.
- when table is dropped, then cache entries are removed.

> and then it would still fail if any code
> tried to access the statistics directly from the table, rather than
> via the caches. My assumption is that the statistics ought to be
> stored in some backend-private data structure designed for that
> purpose, and that the code that needs the data should be taught to
> look for it there when the table is a GTT.

Yes, if you do "select * from pg_statistic" then you will not see
statistic for GTT in this case.
But I do not think that it is so critical. I do not believe that anybody
is trying to manually interpret values in this table.
And optimizer is retrieving statistic through sys-cache mechanism and so
is able to build correct plan in this case.

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



123