Re: [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 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