Re: [Proposal] Global temporary tables

pá 25. 10. 2019 v 17:01 odesílatel Robert Haas napsal: > On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik > 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

Re: [Proposal] Global temporary tables

On Fri, Oct 11, 2019 at 9:50 AM Konstantin Knizhnik 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.

Re: [Proposal] Global temporary tables

> 2019年10月11日 下午9:50，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 table

Re: [Proposal] Global temporary tables

> 2019年10月12日 下午1:16，Pavel Stehule 写道： > > > > pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik > mailto:k.knizh...@postgrespro.ru>> napsal: > > > On 11.10.2019 15:15, 曾文旌(义从) wrote: >> Dear Hackers, >> >> This propose a w

Re: [Proposal] Global temporary tables

pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> 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 "Al

Re: [Proposal] Global temporary tables

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. https://wiki.postgresql.org

[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. https://wiki.postgresql.org/wiki/Todo <https://wiki.postgresql.

Re: Global temporary tables

@ -192,6 +192,7 @@ typedef struct TransactionStateData int parallelModeLevel; /* Enter/ExitParallelMode counter */ bool chain; /* start a new block after this one */ struct TransactionStateData *parent; /* back link to parent */ + TransactionId replicaTransactionId;/* pseudo XID fo

Re: Global temporary tables

This broke recently. Can you please rebase? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Global temporary tables

ore if all users used temporary tables, and you should to calculate with it - it is one reason for global temp tables, then you need multiply size by max_connection. hard to say what is best from implementation perspective, but it can be unhappy if global temporary tables has different performance

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

Re: Global temporary tables

+ b/src/backend/access/transam/xact.c @@ -192,6 +192,7 @@ typedef struct TransactionStateData int parallelModeLevel; /* Enter/ExitParallelMode counter */ bool chain; /* start a new block after this one */ struct TransactionStateData *parent; /* back link to parent */ + TransactionId replica

Re: Global temporary tables

ransactionStateData *parent; /* back link to parent */ + TransactionId replicaTransactionId;/* pseudo XID for inserting data in global temp tables at replica */ } TransactionStateData; typedef TransactionStateData *TransactionState; @@ -286,6 +287,12 @@ typedef struct XactCallback

Re: Global temporary tables

On 20.08.2019 20:01, Pavel Stehule wrote: Another solution is wait on ZHeap storage and replica can to have own UNDO log. I thought about implementation of special table access method for temporary tables. +1 Unfortunately implementing special table access method for temporary

Re: Global temporary tables

út 20. 8. 2019 v 18:42 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 20.08.2019 19:06, Pavel Stehule wrote: > > > > As I wrote at the beginning of this thread, one of the problems with >> temporary table sis that it is not possible to use them at replica. >> Global

Re: Global temporary tables

On 20.08.2019 19:06, Pavel Stehule wrote: As I wrote at the beginning of this thread, one of the problems with temporary table sis that it is not possible to use them at replica. Global temp tables allows to share metadata between master and replica. I am not sure if I

Re: Global temporary tables

eed to ask OS to allocate more memory. > maybe, but shared buffers you have a overhead with searching free buffers and some overhead with synchronization processes. > > > Using local buffers for global temporary tables can be interesting from > another reason - it uses temporary files, and t

Re: Global temporary tables

. It may be caused by system memory allocator - in case of using shared buffers we do not need to ask OS to allocate more memory. Using local buffers for global temporary tables can be interesting from another reason - it uses temporary files, and temporary files can be forwarded on ephemeral IO

Re: Global temporary tables

s to smaller dedicated caches versus access to bigger shared cache. But it is hard to imagine so access to local cache is 10% slower than access to shared cache. Maybe there is some bottle neck - maybe our implementation of local buffers are suboptimal. Using local buffers for global temporar

Re: Global temporary tables

On 19.08.2019 14:25, Pavel Stehule wrote: po 19. 8. 2019 v 13:16 odesílatel Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> napsal: On 19.08.2019 11:51, Konstantin Knizhnik wrote: On 18.08.2019 11:28, Pavel Stehule wrote: ne 18. 8. 2019 v 9:02 odesílatel

Re: Global temporary tables

po 19. 8. 2019 v 13:16 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 19.08.2019 11:51, Konstantin Knizhnik wrote: > > > > On 18.08.2019 11:28, Pavel Stehule wrote: > > > > ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik < > k.knizh...@postgrespro.ru> napsal: >

Re: Global temporary tables

On 19.08.2019 11:51, Konstantin Knizhnik wrote: On 18.08.2019 11:28, Pavel Stehule wrote: ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> napsal: On 16.08.2019 20:17, Pavel Stehule wrote: pá 16. 8. 2019 v 16:12 odesílatel Konstantin

Re: Global temporary tables

On 18.08.2019 11:28, Pavel Stehule wrote: ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> napsal: On 16.08.2019 20:17, Pavel Stehule wrote: pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>>

Re: Global temporary tables

ne 18. 8. 2019 v 9:02 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 16.08.2019 20:17, Pavel Stehule wrote: > > > > pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik < > k.knizh...@postgrespro.ru> napsal: > >> I did more investigations of performance of global

Re: Global temporary tables

On 16.08.2019 20:17, Pavel Stehule wrote: pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> napsal: I did more investigations of performance of global temp tables with shared buffers vs. vanilla (local) temp tables. 1. Combination of

Re: Global temporary tables

pá 16. 8. 2019 v 16:12 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > I did more investigations of performance of global temp tables with shared > buffers vs. vanilla (local) temp tables. > > 1. Combination of persistent and temporary tables in the same query. > >

Re: Global temporary tables

I did more investigations of performance of global temp tables with shared buffers vs. vanilla (local) temp tables. 1. Combination of persistent and temporary tables in the same query. Preparation: create table big(pk bigint primary key, val bigint); insert into big values

Re: Global temporary tables

On 16.08.2019 11:32, Craig Ringer wrote: You ignore the costs of evicting non-temporary data from shared_buffers, i.e. contention for space. Also increased chance of backends being forced to do direct write-out due to lack of s_b space for dirty buffers. > In case of pulling all content of

Re: Global temporary tables

On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik wrote: > > 1. Statistic for global temporary tables (including number of tuples, > pages and all visible flag). > My position is the following: while in most cases it should not be a > problem, because users rarely create indexe

Re: Global temporary tables

open issues: 1. Statistic for global temporary tables (including number of tuples, pages and all visible flag). My position is the following: while in most cases it should not be a problem, because users rarely create indexes or do analyze for temporary tables, there can be situations when differen

Re: Global temporary tables

On Tue, 13 Aug 2019 at 21:50, Konstantin Knizhnik wrote: > As far as I understand relpages and reltuples are set only when you >> perform "analyze" of the table. >> > > Also autovacuum's autoanalyze. > > > When it happen? > I have created normal table, populated it with some data and then wait >

Re: Global temporary tables

newdatatuple, InvalidBuffer); } /* process OWNED BY if given */ @@ -1178,6 +1183,17 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple) LockBuffer(*buf, BUFFER_LOCK_EXCLUSIVE); page = BufferGetPage(*buf); + if (GlobalTempRelationPageIsNotInitialized(rel, page)) + { + /* Initialize sequence for glo

Re: Global temporary tables

On 13.08.2019 11:21, Craig Ringer wrote: On Fri, 9 Aug 2019 at 22:07, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: Ok, here it is: global_private_temp-1.patch Initial pass review follows. Relation name "SESSION" is odd. I guess you're avoiding "global" because the

Re: Global temporary tables

On Tue, 13 Aug 2019 at 16:19, Konstantin Knizhnik wrote: > > > On 13.08.2019 8:34, Craig Ringer wrote: > > On Tue, 13 Aug 2019 at 00:47, Pavel Stehule > wrote: > > >> But Postgres is not storing this information now anywhere else except >>> statistic, isn't it? >>> >> >> not only - critical

Re: Global temporary tables

On Fri, 9 Aug 2019 at 22:07, Konstantin Knizhnik wrote: > > > Ok, here it is: global_private_temp-1.patch > Initial pass review follows. Relation name "SESSION" is odd. I guess you're avoiding "global" because the data is session-scoped, not globally temporary. But I'm not sure "session" fits

Re: Global temporary tables

On 13.08.2019 8:34, Craig Ringer wrote: On Tue, 13 Aug 2019 at 00:47, Pavel Stehule > wrote: But Postgres is not storing this information now anywhere else except statistic, isn't it? not only - critical numbers are reltuples, relpages

Re: Global temporary tables

use it to store local information about >> global temporary tables. >> So if 99% of users do not perform analyze for temporary tables, then them >> will not be faced with this problem, right? >> > > they use default statistics based on relpages. But for 1% of

Re: Global temporary tables

po 12. 8. 2019 v 18:19 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > Hi, > > On 11.08.2019 10:14, Pavel Stehule wrote: > > > Hi > > >> There is one more problem with global temporary tables for which I do not >> know good sol

Re: Global temporary tables

Hi, On 11.08.2019 10:14, Pavel Stehule wrote: Hi There is one more problem with global temporary tables for which I do not know good solution now: collecting statistic. As far as each backend has its own data, generally them may need different query plans. Right now

Re: Global temporary tables

Hi > There is one more problem with global temporary tables for which I do not > know good solution now: collecting statistic. > As far as each backend has its own data, generally them may need different > query plans. > Right now if you perform "analyze t

Re: Global temporary tables

ut subsequent accesses to global shared table are faster (because it completely fits in large shared buffer cache). There is one more problem with global temporary tables for which I do not know good solution now: collecting statistic. As far as each backend has its own data, generally them may need

Re: Global temporary tables

On Fri, 9 Aug 2019 at 22:07, Konstantin Knizhnik wrote: > > > Ok, here it is: global_private_temp-1.patch > Fantastic. I'll put that high on my queue. I'd love to see something like this get in. Doubly so if it brings us closer to being able to use temp tables on physical read replicas,

Re: Global temporary tables

newdatatuple); + fill_seq_with_data(seqrel, newdatatuple, InvalidBuffer); } /* process OWNED BY if given */ @@ -1178,6 +1183,17 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple) LockBuffer(*buf, BUFFER_LOCK_EXCLUSIVE); page = BufferGetPage(*buf); + if (rel->rd_rel->

Re: Global temporary tables

On Thu, 8 Aug 2019 at 15:03, Konstantin Knizhnik wrote: > > > On 08.08.2019 5:40, Craig Ringer wrote: > > On Tue, 6 Aug 2019 at 16:32, Konstantin Knizhnik < > k.knizh...@postgrespro.ru> wrote: > >> New version of the patch with several fixes is attached. >> Many thanks to Roman Zharkov for

Re: Global temporary tables

On 08.08.2019 5:40, Craig Ringer wrote: On Tue, 6 Aug 2019 at 16:32, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: New version of the patch with several fixes is attached. Many thanks to Roman Zharkov for testing. FWIW I still don't understand your argument with

Re: Global temporary tables

On Tue, 6 Aug 2019 at 16:32, Konstantin Knizhnik wrote: > New version of the patch with several fixes is attached. > Many thanks to Roman Zharkov for testing. > FWIW I still don't understand your argument with regards to using shared_buffers for temp tables having connection pooling benefits.

Re: Global temporary tables

lation rel, Buffer *buf, HeapTuple seqdatatuple) LockBuffer(*buf, BUFFER_LOCK_EXCLUSIVE); page = BufferGetPage(*buf); + if (rel->rd_rel->relpersistence == RELPERSISTENCE_SESSION && PageIsNew(page)) + { + /* Initialize sequence for global temporary tables */ + Datum value[SEQ_C

Re: Global temporary tables

On 01.08.2019 6:10, Craig Ringer wrote: 3. It is not possible to use temporary tables at replica. For physical replicas, yes. Yes, definitely logical replicas (for example our PgPro-EE multimaster based on logical replication) do not suffer from this problem. But in case of

Re: Global temporary tables

ssing relfilenodes on write so we don't even have to pre-create the actual file. We'd register the relfilenode as a tempfile and use existing tempfile cleanup mechanisms, and we'd use the temp tablespace to store it. I must be missing something important because it doesn't seem hard. Glob

Global temporary tables

) To solve this problems I propose notion of "global temporary" tables, similar with ones in Oracle. Definition of this table (metadata) is shared by all backends but data is private to the backend. After session termination data is obviously lost. Suggested syntax for creation of glo

<    1   2   3   4