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()
TRUNCATE TABLE xxx;
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)
TRUNCATE TABLE xxx;
EXCEPTION WHEN ..... THEN
CREATE TEMP TABLE(...)
In this case we can use GUC, because visible behave should be same.
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
test=# begin read only;
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.
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Sent via pgsql-hackers mailing list (email@example.com)
To make changes to your subscription: