Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog
 (a.k.a fast temp tables)
To: Pavel Stehule <pavel.stehule@gmail.com>
References: <20160729141552.4062e19b@fujitsu>
 <39b0f423-a4df-3564-3b4a-b1de6f9eea80@2ndquadrant.com>
 <CAFj8pRAv+vPxzTQMviCsYqQrfpYei5bTvX80zRUp1Q3xAbTWyg@mail.gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Aleksander Alekseev <a.alekseev@postgrespro.ru>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <aebacde6-2271-7dbf-a6e0-423c391d3efe@2ndquadrant.com>
Date: Sat, 30 Jul 2016 14:42:54 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRAv+vPxzTQMviCsYqQrfpYei5bTvX80zRUp1Q3xAbTWyg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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 specification of CREATE TABLE.
>
>
> Probably has zero value to have slow and fast temp tables (from
> catalogue cost perspective). So the FAST implementation should be used
> everywhere. 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 TEMP TABLE xxx()
> ELSE
>   TRUNCATE TABLE xxx;
> END IF;

I'd argue that if you mess with catalogs directly, you're on your own. 
Not only it's fragile, but this pattern is also prone to race conditions 
(although a concurrent session can't create a conflicting temporary table).

>
> -- modern patter (new code)
> BEGIN
>   TRUNCATE TABLE xxx;
> EXCEPTION WHEN ..... THEN
>   CREATE TEMP TABLE(...)
> END;
>
> In this case we can use GUC, because visible behave should be same.

What GUC?

>
> The benefit of zero catalogue cost temp tables is significant - and for
> some larger applications the temp tables did hard performance issues.

Yeah, catalog bloat is a serious issue in such cases, and it's amplified 
by indexes created on the temporary tables.

>
> Some other random notes:
>
> 1. With this code should not be hard to implement global temp tables -
> shared persistent structure, temp local data - significant help for any
> who have to migrate from Oracle.

The patch moves in pretty much the opposite direction - if anything, 
it'll make it more difficult to implement global temporary tables, 
because it removes the definitions from the catalog, thus impossible to 
share by catalogs. To get global temporary tables, I think the best 
approach would be to share the catalog definition and only override the 
filename. Or something like that.

>
> 2. This should to work on slaves - it is one of ToDo
>

No, it does not work on slaves, because it still does a read-write 
transaction.

test=# begin read only;
BEGIN
test=# create fast temporary table x (id int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

No idea how difficult it'd be to make it work.

> 3. I didn't see support for memory store for column's statistics. Some
> separate questions is about production statistics - pg_stat_user_table, ..

That seems to work (both analyze and pg_stat_user_tables). Not sure 
where it's in the code, and I'm not willing to reverse engineer it.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers