Re: [Proposal] Global temporary tables

t 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 ch

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 patch

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. Require

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 tables to

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/wik

[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.org/w

Re: Global temporary tables

ckend/access/transam/xact.c +++ 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 lin

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

nt. More 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 perfor

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 (firs

Re: Global temporary tables

st 18. 9. 2019 v 12:04 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 21.08.2019 11:54, Konstantin Knizhnik wrote: > > > > 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

Re: Global temporary tables

aeee..ab1bef9 100644 --- a/src/backend/access/transam/xact.c +++ 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 TransactionSta

Re: Global temporary tables

bool chain; /* start a new block after this one */ struct TransactionStateData *parent; /* back link to parent */ + TransactionId replicaTransactionId;/* pseudo XID for inserting data in global temp tables at replica */ } TransactionStateData; typedef TransactionStateData *TransactionState;

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 t

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 u

Re: Global temporary tables

o 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

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 temporary t

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 Konstant

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 K

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 tem

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 pers

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. > > Preparatio

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 (generate_series(1,1

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 16.08.2019 11:37, Craig Ringer wrote: On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: I forget or do not notice some of your questions, would you be so kind as to repeat them? Sent early by accident. Repeating question

Re: Global temporary tables

> > > On Fri, 16 Aug 2019 at 15:30, Konstantin Knizhnik < > k.knizh...@postgrespro.ru> wrote: > > >> I forget or do not notice some of your questions, would you be so kind as >> to repeat them? >> > > Sent early by accident. Repeating questions: Why do you need to do all this indirection with c

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

ight now I reread all this thread and see two 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 tab

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

lockBuffer) + UnlockReleaseBuffer(buf); } /* @@ -502,7 +507,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) /* * Insert the modified tuple into the new storage file. */ - fill_seq_with_data(seqrel, newdatatuple); + fill_seq_with_data(seqrel, newdatatuple, InvalidBuffer); } /* proc

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 numb

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 from

Re: Global temporary tables

d yet. >> If such cache exists, then we can 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 de

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 soluti

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 if

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 table" in

Re: Global temporary tables

cache and different eviction policy). But 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

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, thoug

Re: Global temporary tables

q_with_data(seqrel, 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

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 testin

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 rega

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. Ar

Re: Global temporary tables

/* * Insert the modified tuple into the new storage file. */ - fill_seq_with_data(seqrel, newdatatuple); + fill_seq_with_data(seqrel, newdatatuple, InvalidBuffer); } /* process OWNED BY if given */ @@ -1178,6 +1183,16 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple) L

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 multimaste

Re: Global temporary tables

ed to track temp table and index relfilenodes. If no relfilenode is defined for the table, the mapper would allocate one. We already happily create missing relfilenodes on write so we don't even have to pre-create the actual file. We'd register the relfilenode as a tempfile and us

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 o

<    1   2   3   4