Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Wed, Aug 31, 2016 at 6:07 PM, Andres Freund wrote: > > In my experience pg attribute is usually the worst affected. Many tech > takes won't even have stays entries... > > Mine too. One database currently has a 400GB pg_attribute table, because we chew through temp tables

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On August 31, 2016 3:00:15 PM PDT, Tomas Vondra wrote: > > >On 08/31/2016 11:43 PM, Andres Freund wrote: >> On 2016-08-31 23:40:46 +0200, Tomas Vondra wrote: >>> It's an improvement (and it's pretty much exactly what I proposed >>> upthread). But it does not solve

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/31/2016 11:43 PM, Andres Freund wrote: > On 2016-08-31 23:40:46 +0200, Tomas Vondra wrote: >> It's an improvement (and it's pretty much exactly what I proposed >> upthread). But it does not solve the problems with pg_statistic for >> example (each backend needs it's own statistics. So we'd

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-31 23:40:46 +0200, Tomas Vondra wrote: > It's an improvement (and it's pretty much exactly what I proposed > upthread). But it does not solve the problems with pg_statistic for > example (each backend needs it's own statistics. So we'd either bloat > the pg_statistic (if we manage to

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/31/2016 09:20 PM, Vik Fearing wrote: > On 08/24/2016 06:16 PM, Robert Haas wrote: >> On Tue, Aug 23, 2016 at 6:11 PM, Tomas Vondra >> wrote: >>> Could someone please explain how the unlogged tables are supposed to fix the >>> catalog bloat problem, as stated

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/24/2016 06:16 PM, Robert Haas wrote: > On Tue, Aug 23, 2016 at 6:11 PM, Tomas Vondra > wrote: >> Could someone please explain how the unlogged tables are supposed to fix the >> catalog bloat problem, as stated in the initial patch submission? We'd still >> need

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Wed, Aug 24, 2016 at 12:39 PM, Andres Freund wrote: > > > On August 24, 2016 9:32:48 AM PDT, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote: > > > > > >On 08/24/2016 12:20 AM, Andres Freund wrote: > >> On 2016-08-23 19:18:04 -0300, Claudio Freire wrote: > >>> On Tue,

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On August 24, 2016 9:32:48 AM PDT, Tomas Vondra wrote: > > >On 08/24/2016 12:20 AM, Andres Freund wrote: >> On 2016-08-23 19:18:04 -0300, Claudio Freire wrote: >>> On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra >>> wrote: Could

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/24/2016 12:20 AM, Andres Freund wrote: On 2016-08-23 19:18:04 -0300, Claudio Freire wrote: On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra wrote: Could someone please explain how the unlogged tables are supposed to fix the catalog bloat problem, as stated

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 23, 2016 at 6:11 PM, Tomas Vondra wrote: > Could someone please explain how the unlogged tables are supposed to fix the > catalog bloat problem, as stated in the initial patch submission? We'd still > need to insert/delete the catalog rows when

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Wed, Aug 24, 2016 at 2:04 AM, Alvaro Herrera wrote: > Claudio Freire wrote: > >> After looking at it from a birdseye view, I agree it's conceptually >> complex (reading HeapTupleSatisfiesSelf already makes one dizzy). >> >> But other than that, the implementation

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Claudio Freire wrote: > After looking at it from a birdseye view, I agree it's conceptually > complex (reading HeapTupleSatisfiesSelf already makes one dizzy). > > But other than that, the implementation seems rather simple. It seems > to me, if one figures out that it is safe to do so

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 23, 2016 at 9:12 PM, Tomas Vondra wrote: > On 08/24/2016 12:38 AM, Claudio Freire wrote: >> >> On Tue, Aug 23, 2016 at 7:25 PM, Tomas Vondra >> wrote: > > Could someone please explain how the unlogged tables are

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/24/2016 12:38 AM, Claudio Freire wrote: On Tue, Aug 23, 2016 at 7:25 PM, Tomas Vondra wrote: Could someone please explain how the unlogged tables are supposed to fix the catalog bloat problem, as stated in the initial patch submission? We'd still need to

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 23, 2016 at 7:25 PM, Tomas Vondra wrote: >>> Could someone please explain how the unlogged tables are supposed to fix >>> the >>> catalog bloat problem, as stated in the initial patch submission? We'd >>> still >>> need to insert/delete the catalog rows

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-23 19:33:33 -0300, Claudio Freire wrote: > On Tue, Aug 23, 2016 at 7:20 PM, Andres Freund wrote: > >> Wouldn't more aggressive vacuuming of catalog tables fix the bloat? > > > > Not really in my experience, at least not without more drastic vacuum > > changes. The

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 23, 2016 at 7:20 PM, Andres Freund wrote: >> Wouldn't more aggressive vacuuming of catalog tables fix the bloat? > > Not really in my experience, at least not without more drastic vacuum > changes. The issue is that if you have a single "long running" > transaction

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/24/2016 12:18 AM, Claudio Freire wrote: On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra wrote: On 08/22/2016 10:32 PM, Robert Haas wrote: ... 1. The number of tables for which we would need to add a duplicate, unlogged table is formidable. You need

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-23 19:18:04 -0300, Claudio Freire wrote: > On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra > wrote: > > Could someone please explain how the unlogged tables are supposed to fix the > > catalog bloat problem, as stated in the initial patch submission? We'd

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 23, 2016 at 7:11 PM, Tomas Vondra wrote: > On 08/22/2016 10:32 PM, Robert Haas wrote: >> >> >> ... >> >> 1. The number of tables for which we would need to add a duplicate, >> unlogged table is formidable. You need pg_attribute, pg_attrdef, >>

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/22/2016 10:32 PM, Robert Haas wrote: ... 1. The number of tables for which we would need to add a duplicate, unlogged table is formidable. You need pg_attribute, pg_attrdef, pg_constraint, pg_description, pg_type, pg_trigger, pg_rewrite, etc. And the backend changes needed so that we

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Thank you everyone for great comments! > have a permanent pg_class which holds the records for permanent tables > and an *unlogged* table, say pg_class_unlogged, which holds records > for temporary tables. Now everybody can see everybody else's data, > yet we don't have to create permanent

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-22 17:50:11 -0300, Alvaro Herrera wrote: > > 2. You can't write to unlogged tables on standby servers, so this > > doesn't help solve the problem of wanting to use temporary tables on > > standbys. > > Check. We could think about relaxing this restriction, which would > enable the

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Robert Haas wrote: > However: > > 1. The number of tables for which we would need to add a duplicate, > unlogged table is formidable. You need pg_attribute, pg_attrdef, > pg_constraint, pg_description, pg_type, pg_trigger, pg_rewrite, etc. > And the backend changes needed so that we used the

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Tue, Aug 16, 2016 at 8:03 PM, Jim Nasby wrote: > On 8/16/16 11:59 AM, Robert Haas wrote: > ... >> >> That doesn't really solve the problem, because OTHER backends won't be >> able to see them. So, if I create a fast temporary table in one >> session that depends on a

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

2016-08-18 16:33 GMT+02:00 Tomas Vondra : > On 08/15/2016 12:23 PM, Pavel Stehule wrote: > >> >> >> 2016-08-15 12:18 GMT+02:00 Aleksander Alekseev >> >: >> >> > The global temporary tables has

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/15/2016 12:23 PM, Pavel Stehule wrote: 2016-08-15 12:18 GMT+02:00 Aleksander Alekseev >: > The global temporary tables has persistent rows in the catalogue. The > mapping to files can be marked as special and real

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/17/2016 11:50 AM, Aleksander Alekseev wrote: That doesn't really solve the problem, because OTHER backends won't be able to see them. So, if I create a fast temporary table in one session that depends on a permanent object, some other session can drop the permanent object. If there were

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

> That doesn't really solve the problem, because OTHER backends won't be > able to see them. So, if I create a fast temporary table in one > session that depends on a permanent object, some other session can > drop the permanent object. If there were REAL catalog entries, that > wouldn't work,

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 8/16/16 11:59 AM, Robert Haas wrote: ... That doesn't really solve the problem, because OTHER backends won't be able to see them. So, if I create a fast temporary table in one session that depends on a permanent object, some other session can drop the permanent object. If there were REAL

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Mon, Aug 15, 2016 at 5:12 AM, Aleksander Alekseev wrote: > Just to keep things sane I would like to remind that in this concrete > patch there _are_ catalog entries: > > ``` > [...] > This file contents imlementation of special type of temporary tables --- > fast

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

> Why are you sending this off-list? Please let's keep the discussion > on the mailing list. I suggest resending this there. Sorry for that. I accidentally removed pgsql-hackers@ from CC list or maybe my email client somehow did it for me. Short after that I realized my mistake and sent a copy

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Why are you sending this off-list? Please let's keep the discussion on the mailing list. I suggest resending this there. On Mon, Aug 15, 2016 at 5:01 AM, Aleksander Alekseev wrote: >> > >>> I think the whole idea of a fast temporary table is that there >> > >>> are

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Re: To Tom Lane 2016-08-15 <20160815111057.v2mqqjp4aabvw...@msg.df7cb.de> > Re: Tom Lane 2016-07-30 <1184.1469890...@sss.pgh.pa.us> > > In short, I think that the way to make something like this work is to > > figure out how to have "virtual" catalog rows describing a temp table. > > Or maybe to

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Re: Tom Lane 2016-07-30 <1184.1469890...@sss.pgh.pa.us> > In short, I think that the way to make something like this work is to > figure out how to have "virtual" catalog rows describing a temp table. > Or maybe to partition the catalogs so that vacuuming away temp-table > rows is easier/cheaper

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

2016-08-15 12:18 GMT+02:00 Aleksander Alekseev : > > The global temporary tables has persistent rows in the catalogue. The > > mapping to files can be marked as special and real mapping should be > > only in memory. > > > > So the changes in catalogue related to global

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

> The global temporary tables has persistent rows in the catalogue. The > mapping to files can be marked as special and real mapping should be > only in memory. > > So the changes in catalogue related to global temporary tables are > pretty less frequently. I'm afraid I still don't get it. Let

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

2016-08-15 12:00 GMT+02:00 Aleksander Alekseev : > > But we can change this discussion little bit different. I believe so > > solution should be *global temporary tables*. These tables has > > persistent catalogue entries. Data are joined with session. These > > tables

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

> But we can change this discussion little bit different. I believe so > solution should be *global temporary tables*. These tables has > persistent catalogue entries. Data are joined with session. These > tables can be effective solution of problem with temporary tables, > can be strong benefit

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

2016-08-15 11:01 GMT+02:00 Aleksander Alekseev : > > > >>> I think the whole idea of a fast temporary table is that there > > > >>> are no catalog entries. If there are no catalog entries, then > > > >>> dependencies are not visible. If there ARE catalog entries, to >

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

> > >>> I think the whole idea of a fast temporary table is that there > > >>> are no catalog entries. If there are no catalog entries, then > > >>> dependencies are not visible. If there ARE catalog entries, to > > >>> what do they refer? Without a pg_class entry for the table, > > >>> there's

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-14 21:04:57 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2016-08-07 14:46:06 -0400, Tom Lane wrote: > >> Robert Haas writes: > >>> I think the whole idea of a fast temporary table is that there are no > >>> catalog entries. If there

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Andres Freund writes: > On 2016-08-07 14:46:06 -0400, Tom Lane wrote: >> Robert Haas writes: >>> I think the whole idea of a fast temporary table is that there are no >>> catalog entries. If there are no catalog entries, then dependencies >>> are not

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 2016-08-07 14:46:06 -0400, Tom Lane wrote: > Robert Haas writes: > > I think the whole idea of a fast temporary table is that there are no > > catalog entries. If there are no catalog entries, then dependencies > > are not visible. If there ARE catalog entries, to what

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

05.08.2016 19:41, Robert Haas: 2. This inserts additional code in a bunch of really low-level places like heap_hot_search_buffer, heap_update, heap_delete, etc. I think what you've basically done here is create a new, in-memory heap AM and then, because we don't have an API for adding new

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Robert Haas writes: > I think the whole idea of a fast temporary table is that there are no > catalog entries. If there are no catalog entries, then dependencies > are not visible. If there ARE catalog entries, to what do they refer? > Without a pg_class entry for the

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Sat, Aug 6, 2016 at 4:05 AM, Dmitry Dolgov <9erthali...@gmail.com> wrote: >> For example, suppose I create a fast temporary table and then I create a >> functional index on the fast temporary table that uses some SQL function >> defined in pg_proc. > Just to clarify, did you mean something like

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

​Hi I tried to dig into this patch (which seems pretty interesting) to help bring it in good shape. Here are few random notes, I hope they can be helpful: > I think we actually need a real API here. Definitely, there are plenty places in the new code with the same pattern: * figure out if it's

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On Thu, Aug 4, 2016 at 8:14 AM, Aleksander Alekseev wrote: > Thanks everyone for your remarks and comments! > > Here is an improved version of a patch. > > Main changes: > * Patch passes `make installcheck` > * Code is fully commented, also no more TODO's > > I wish I

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 30 July 2016 at 13:42, Tomas Vondra wrote: > I'd argue that if you mess with catalogs directly, you're on your own. Interesting. What would you suggest people use instead? Geoff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Thanks everyone for your remarks and comments! Here is an improved version of a patch. Main changes: * Patch passes `make installcheck` * Code is fully commented, also no more TODO's I wish I sent this version of a patch last time. Now I realize it was really hard to read and understand. Hope I

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Tomas Vondra writes: > What is needed is an overview of the approach, so that the reviewers can > read that first, instead of assembling the knowledge from pieces > scattered over comments in many pieces. But I see the fasttab.c contains > this: > /* TODO TODO

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 08/01/2016 11:45 AM, Aleksander Alekseev wrote: Hello. Thanks everyone for great comments! Well, jokes aside, that's a pretty lousy excuse for not writing any docs I think maybe I put it in a wrong way. There are currently a lot of comments in a code, more then enough to understand how

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Hello. Thanks everyone for great comments! > Well, jokes aside, that's a pretty lousy excuse for not writing any > docs I think maybe I put it in a wrong way. There are currently a lot of comments in a code, more then enough to understand how this feature works. What I meant is that this is

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 07/30/2016 04:47 PM, Tom Lane wrote: Pavel Stehule writes: But there are some patterns used with work with temp tables,that should not working, and we would to decide if we prepare workaround or not. -- problematic pattern (old code) IF NOT EXISTS(SELECT * FROM

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 7/30/16 10:47 AM, Tom Lane wrote: > Pavel Stehule writes: >> But there are some patterns used with work with temp tables,that should not >> working, and we would to decide if we prepare workaround or not. > >> -- problematic pattern (old code) >> IF NOT EXISTS(SELECT

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Pavel Stehule writes: > But there are some patterns used with work with temp tables,that should not > working, and we would to decide if we prepare workaround or not. > -- problematic pattern (old code) > IF NOT EXISTS(SELECT * FROM pg_class WHERE ) THEN > CREATE

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 07/30/2016 06:49 AM, Pavel Stehule wrote: 1) I wonder whether the FAST makes sense - does this really change the performance significantly? IMHO you only move the catalog rows to memory, so why should the tables be any faster? I also believe this conflicts with SQL standard

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

2016-07-30 1:46 GMT+02:00 Tomas Vondra : > Hi, > > On 07/29/2016 01:15 PM, Aleksander Alekseev wrote: > >> Hello >> >> Some time ago we discussed an idea of "fast temporary tables": >> >> https://www.postgresql.org/message-id/20160301182500.2c81c3dc%40fujitsu >> >>

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

Hi, On 07/29/2016 01:15 PM, Aleksander Alekseev wrote: Hello Some time ago we discussed an idea of "fast temporary tables": https://www.postgresql.org/message-id/20160301182500.2c81c3dc%40fujitsu In two words the idea is following. PostgreSQL stores information about all relations in