From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Aleksander Alekseev <a.alekseev@postgrespro.ru>
Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
Date: Sat, 30 Jul 2016 14:42:54 +0200
Message-ID: <aebacde6-2271-7dbf-a6e0-423c391d3efe@2ndquadrant.com> (raw)
In-Reply-To: <CAFj8pRAv+vPxzTQMviCsYqQrfpYei5bTvX80zRUp1Q3xAbTWyg@mail.gmail.com>
References: <20160729141552.4062e19b@fujitsu>
<39b0f423-a4df-3564-3b4a-b1de6f9eea80@2ndquadrant.com>
<CAFj8pRAv+vPxzTQMviCsYqQrfpYei5bTvX80zRUp1Q3xAbTWyg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, pavel.stehule@gmail.com, a.alekseev@postgrespro.ru
Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)
In-Reply-To: <aebacde6-2271-7dbf-a6e0-423c391d3efe@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox