Re: Global temporary tables

s@lists.postgresql.org ; Konstantin Knizhnik Subject: Re: Global temporary tables Sorry, small typo in the last patch. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

Re: Global temporary tables

Sorry, small typo in the last patch. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/contrib/pg_prewarm/pg_prewarm.c b/contrib/pg_prewarm/pg_prewarm.c index 33e2d28..93059ef 100644 --- a/contrib/pg_prewarm/pg_prewarm.c +++ b/con

Re: Global temporary tables

Fix GTT index initialization. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/contrib/pg_prewarm/pg_prewarm.c b/contrib/pg_prewarm/pg_prewarm.c index 33e2d28..93059ef 100644 --- a/contrib/pg_prewarm/pg_prewarm.c +++ b/contrib/pg_

Re: Global temporary tables

On 01.02.2020 14:49, Tomas Vondra wrote: Hi, this patch was marked as waiting on author since the beginning of the CF, most likely because it no longer applies (not sure). As there has been very little activity since then, I've marked it as returned with feedback. Feel free to re-submit an upd

Re: Global temporary tables

Hi, this patch was marked as waiting on author since the beginning of the CF, most likely because it no longer applies (not sure). As there has been very little activity since then, I've marked it as returned with feedback. Feel free to re-submit an updated patch for 2020-03. This definitely doe

Re: Global temporary tables

Hi all, I am not aware enough in the Postgres internals to give advice about the implementation. But my feeling is that there is another big interest for this feature: simplify the Oracle to PostgreSQL migration of applications that use global termporary tables. And this is quite common when s

Re: Global temporary tables

On 01.12.2019 4:56, Michael Paquier wrote: On Wed, Nov 20, 2019 at 07:32:14PM +0300, Konstantin Knizhnik wrote: Now pg_gtt_statistic view is provided for global temp tables. Latest patch fails to apply, per Mr Robot's report. Could you please rebase and send an updated version? For now I ha

Re: Global temporary tables

On Wed, Nov 20, 2019 at 07:32:14PM +0300, Konstantin Knizhnik wrote: > Now pg_gtt_statistic view is provided for global temp tables. Latest patch fails to apply, per Mr Robot's report. Could you please rebase and send an updated version? For now I have moved the patch to next CF, waiting on auth

Re: Global temporary tables

Now pg_gtt_statistic view is provided for global temp tables. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index ae7b729..485c068 100644 --- a/src/backend/access/

Re: Global temporary tables

Yet another version of my GTT patch addressing issues reported by 曾文旌(义从) * Bug in TRUNCATE is fixed, * ON COMMIT DELETE ROWS option is supported * ALTER TABLE is correctly handled -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git

Re: Global temporary tables

As far as both Robert and Pavel think that aspects of using GTT in parallel queries and at replica should be considered separately. I have prepared simplest version of the patch for GTT which introduces minimal differences with current (local) temporary table. So GTT are stored in private buffers

Re: Global temporary tables

On 25.09.2019 23:28, Alvaro Herrera wrote: This broke recently. Can you please rebase? Rebased version of the patch is attached. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c

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

po 23. 9. 2019 v 9:57 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > 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 q

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

I have added support of all indexes (brin, btree, gin, gist, hash, spgist) for global temp tables (before only B-Tree index was supported). It will be nice to have some generic mechanism for it, but I do not understand how it can look like. The problem is that normal relations are initialized at

Re: Global temporary tables

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 about implementation of special table access method for temporary tables. +1 Unfortunately imple

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

út 20. 8. 2019 v 16:51 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 19.08.2019 18:53, Pavel Stehule wrote: > > > > >> Certainly, default (small) temp buffer size plays roles. >> But it this IPC host this difference is not so important. >> Result with local temp tabl

Re: Global temporary tables

On 19.08.2019 18:53, Pavel Stehule wrote: Certainly, default (small) temp buffer size plays roles. But it this IPC host this difference is not so important. Result with local temp tables and temp_buffers = 1GB: 859k TPS. It is little bit unexpected result.I understand so it par

Re: Global temporary tables

> Certainly, default (small) temp buffer size plays roles. > But it this IPC host this difference is not so important. > Result with local temp tables and temp_buffers = 1GB: 859k TPS. > It is little bit unexpected result.I understand so it partially it is generic problem access to smaller dedicat

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 indexes or do analyze fo

Re: Global temporary tables

On 16.08.2019 9:25, Craig Ringer wrote: On Tue, 13 Aug 2019 at 21:50, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: As far as I understand relpages and reltuples are set only when you perform "analyze" of the table. Also autovacuum's autoanalyze. W

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

On 13.08.2019 11:27, Craig Ringer wrote: On Tue, 13 Aug 2019 at 16:19, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: On 13.08.2019 8:34, Craig Ringer wrote: On Tue, 13 Aug 2019 at 00:47, Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote: But P

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

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 pg_class > That's a very good point. relallvisible too. How's the global temp table

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 solution now: collecting statistic. >> As far as eac

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 you

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 one backend, then it will > affe

Re: Global temporary tables

On 10.08.2019 5:12, 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 Fantastic. I'll put that high on my queue. I'd love to see something like this get in. Doubly so if it brin

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

On 09.08.2019 8:34, Craig Ringer wrote: On Thu, 8 Aug 2019 at 15:03, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: 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 ver

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

New version of the patch with several fixes is attached. Many thanks to Roman Zharkov for testing. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_bufferca

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

On Wed, 31 Jul 2019 at 23:05, Konstantin Knizhnik wrote: > Current Postgres implementation of temporary table causes number of > problems: > > 1. Catalog bloating: if client creates and deletes too many temporary > tables, then autovacuum get stuck on catalog. > This also upsets logical decoding