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
