idea: global temp tables

Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am thinking about global temp tables.

These would have some value to us.

In case anyone doesn't know, this is a feature in the SQL standard.
You have a permanent definition of the schema, but the table is
materialized as a temporary table on reference by any connection.

I can't speak to the practicality of the proposed implementation
techniques.

-Kevin

Kevin Grittner wrote:

Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am thinking about global temp tables.

These would have some value to us.

In case anyone doesn't know, this is a feature in the SQL standard.
You have a permanent definition of the schema, but the table is
materialized as a temporary table on reference by any connection.

I can't speak to the practicality of the proposed implementation
techniques.

Using a global table to achieve schema-persistent temp tables seems like
a horrid hack - what would you do if the table used a type other than a
standard built-in type?

Or perhaps Pavel doesn't really mean "global" as the term is used in
Postgres (c.f. the pg_database table)?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> wrote:

Or perhaps Pavel doesn't really mean "global" as the term is used
in Postgres (c.f. the pg_database table)?

I'd bet that he doesn't. He's taking terminology from the standard,
where it means "not limited to one SQL-client module". It just means
it is available as long as you are using the connection.

-Kevin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

- -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Apr 27, 2009, at 4:44 PM, Pavel Stehule wrote:

Hello

I am thinking about global temp tables. One possible solution is
creating global temporary table like normal table and in planner stage
check using this table. When some global temporary table is detected,
then real temporary table is created and used in execution plan. It's
like:

CREATE GLOBAL TEMP TABLE foo(a varchar); -- create global empty
table foo
SELECT * FROM foo;
a) is relevant temp table for foo, use it
a) when not, then CREATE TEMP TABLE pg_temp_1.foo(LIKE foo INCLUDING
DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
b) transform origin query to SELECT * FROM pg_temp_1.foo;

Ideas? Notes? Objections?

When will postgresql offer "global" temporary tables with data which
are shared among sessions? Such tables are great for transient data
such as web session data where writing to the WAL is a waste. (On DB
startup, the tables would simply be empty.) We're currently stuck with
the memcached plugin which makes it impossible to use database
constructs such as foreign keys against the temporary data.

Cheers,
M
- -----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.10 (Darwin)

iEYEARECAAYFAkn2JHQACgkQqVAj6JpR7t4YRgCdGj8JPJY61PPaK79jnPFXu8c7
vjIAn2F1lA0Nr/2EHVPcYQohWqGjWElK
=3zYu
- -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.10 (Darwin)

iEYEARECAAYFAkn2JIIACgkQqVAj6JpR7t6IOgCdE0le+MAlcwCYNqEt+w9jt/Y3
Z/sAni8Jm3ndYZSI1pIQLBVtKnBnJ8Ee
=VXWF
-----END PGP SIGNATURE-----

Hi,

Le 27 avr. 09 à 23:32, A.M. a écrit :

When will postgresql offer "global" temporary tables with data which
are shared among sessions? Such tables are great for transient data
such as web session data where writing to the WAL is a waste. (On DB
startup, the tables would simply be empty.) We're currently stuck
with the memcached plugin which makes it impossible to use database
constructs such as foreign keys against the temporary data.

If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
session management transactions, it'll skip the WAL fsync'ing, which
is already a good start.

HTH,
--
dim

"A.M." <agentm@themactionfaction.com> wrote:

When will postgresql offer "global" temporary tables with data
which are shared among sessions?

Well, that would certainly be far different from what the standard
calls a temporary table of any flavor. In the standard all temporary
tables are restricted to a single connection, and the scope is:

GLOBAL: Schema always present. Once materialized, present for as
long as the connection exists.

CREATED LOCAL: Schema always present. Once materialized, visible
only within a particular module.

DECLARED LOCAL: No permanent schema. Materialized when declared in a
compound statement (standard BEGIN/END; not related to transaction
boundaries), and automatically dropped on exit from the compound
statement.

Current PostgreSQL temporary tables are sort of a hybrid between
GLOBAL and DECLARED LOCAL temporary tables from the standard.

-Kevin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote:

Hi,

Le 27 avr. 09 à 23:32, A.M. a écrit :

When will postgresql offer "global" temporary tables with data
which are shared among sessions? Such tables are great for
transient data such as web session data where writing to the WAL is
a waste. (On DB startup, the tables would simply be empty.) We're
currently stuck with the memcached plugin which makes it impossible
to use database constructs such as foreign keys against the
temporary data.

If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
session management transactions, it'll skip the WAL fsync'ing, which
is already a good start.

That's pretty close, but it's not table specific and wouldn't let us
to reliably mix transient data changes with real data changes.

Cheers,
M
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.10 (Darwin)

iEYEARECAAYFAkn2KNcACgkQqVAj6JpR7t4OrQCgpU9K3FzG2LWWyM245vUaop1G
ZMIAn379RDewxKUmCsZsWLo8KdWAYGIs
=kHl5
-----END PGP SIGNATURE-----

"A.M." <agentm@themactionfaction.com> wrote:

On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote:

Le 27 avr. 09 ￜ 23:32, A.M. a ￜcrit :

When will postgresql offer "global" temporary tables with data
which are shared among sessions? Such tables are great for
transient data such as web session data where writing to the WAL is

a waste. (On DB startup, the tables would simply be empty.) We're

currently stuck with the memcached plugin which makes it impossible

to use database constructs such as foreign keys against the
temporary data.

If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
session management transactions, it'll skip the WAL fsync'ing, which

is already a good start.

That's pretty close, but it's not table specific and wouldn't let us

to reliably mix transient data changes with real data changes.

Yeah, we have a dozen or so tables we use with the pattern you
describe; so the feature you describe would also have some value for
us. To avoid confusion, we don't refer to these as "temporary
tables", but rather as "permanent work tables". Again, I can't
comment on practical issues regarding implementation; but it would be
a "nice feature" to add some day. The tricky bit would be to figure
out how to ensure that it got cleaned up properly, especially if the
PostgreSQL went down or client processes wend down before tidying up.

-Kevin

On Apr 27, 2009, at 6:01 PM, Kevin Grittner wrote:

"A.M." <agentm@themactionfaction.com> wrote:

On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote:

Le 27 avr. 09 à 23:32, A.M. a écrit :

When will postgresql offer "global" temporary tables with data
which are shared among sessions? Such tables are great for
transient data such as web session data where writing to the WAL is

a waste. (On DB startup, the tables would simply be empty.) We're

currently stuck with the memcached plugin which makes it impossible

to use database constructs such as foreign keys against the
temporary data.

If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
session management transactions, it'll skip the WAL fsync'ing, which

is already a good start.

That's pretty close, but it's not table specific and wouldn't let us

to reliably mix transient data changes with real data changes.

Yeah, we have a dozen or so tables we use with the pattern you
describe; so the feature you describe would also have some value for
us. To avoid confusion, we don't refer to these as "temporary
tables", but rather as "permanent work tables". Again, I can't
comment on practical issues regarding implementation; but it would be
a "nice feature" to add some day. The tricky bit would be to figure
out how to ensure that it got cleaned up properly, especially if the
PostgreSQL went down or client processes wend down before tidying up.

Actually, for our usage, that's the easiest part- truncate all the
"permanent work tables" whenever the db starts. That's really the
only sane thing to do anyway. That's what I mean by "transient" data-
if it's there, that's great, if not, I can re-generate it (cache) or
I don't care because, if the database goes down, then the data is
useless on restart anyway.

Cheers,
M

2009/4/27 Andrew Dunstan <andrew@dunslane.net>:

Kevin Grittner wrote:

Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am thinking about global temp tables.

 These would have some value to us.
 In case anyone doesn't know, this is a feature in the SQL standard. You
have a permanent definition of the schema, but the table is
materialized as a temporary table on reference by any connection.
 I can't speak to the practicality of the proposed implementation
techniques.

Using a global table to achieve schema-persistent temp tables seems like a
horrid hack - what would you do if the table used a type other than a
standard built-in type?

Where is a problem? - there is normal dependency between types and
relation. Of course, ALTER TABLE have to be little bit different - a)
should be done, when no table is used, b) should be done only on all
temporary tables. But this technique do minimal changes in pg internal
structure. The core of problem is structure of pg_class table, that
contains possibly shared and not shared fields between global tables.
So implementation needs a) significant change of pg_class table OR b)
using some transparent table overloading

One year ago I though about some memory tables for it. But it is too
different and now, when VACUUM should be effective I thing, it is
needless.

Or perhaps Pavel doesn't really mean "global" as the term is used in
Postgres (c.f. the pg_database table)?

no, I though global tables in sense of SQL standard. What do you thing
are shared tables (in pg terminology)

regards
Pavel Stehule

2009/4/28 Kevin Grittner <Kevin.Grittner@wicourts.gov>:

"A.M." <agentm@themactionfaction.com> wrote:

On Apr 27, 2009, at 5:39 PM, Dimitri Fontaine wrote:

Le 27 avr. 09 à 23:32, A.M. a écrit :

When will postgresql offer "global" temporary tables with data
which are shared among sessions? Such tables are great for
transient data such as web session data where writing to the WAL is

a waste. (On DB startup, the tables would simply be empty.) We're

currently stuck with the memcached plugin which makes it impossible

to use database constructs such as foreign keys against the
temporary data.

If using 8.3 you can SET LOCAL synchronous_commit TO off; for web
session management transactions, it'll skip the WAL fsync'ing, which

is already a good start.

That's pretty close, but it's not table specific and wouldn't let us

to reliably mix transient data changes with real data changes.

Yeah, we have a dozen or so tables we use with the pattern you
describe; so the feature you describe would also have some value for
us.  To avoid confusion, we don't refer to these as "temporary
tables", but rather as "permanent work tables".  Again, I can't
comment on practical issues regarding implementation; but it would be
a "nice feature" to add some day.  The tricky bit would be to figure
out how to ensure that it got cleaned up properly, especially if the
PostgreSQL went down or client processes wend down before tidying up.

For me, GLOBAL TEMP TABLES should significant to increase comfort for
developers. That is main reason.

reagards
Pavel Stehule

Pavel Stehule escribi�:

Hello

I am thinking about global temp tables. One possible solution is
creating global temporary table like normal table and in planner stage
check using this table. When some global temporary table is detected,
then real temporary table is created and used in execution plan. It's
like:

CREATE GLOBAL TEMP TABLE foo(a varchar); -- create global empty table foo
SELECT * FROM foo;
a) is relevant temp table for foo, use it
a) when not, then CREATE TEMP TABLE pg_temp_1.foo(LIKE foo INCLUDING
DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
b) transform origin query to SELECT * FROM pg_temp_1.foo;

Ideas? Notes? Objections?

Maybe we could make this work by fiddling with a different smgr -- on
it, smgr_sync would be a noop, as would smgr_immedsync, and we could
kludge something up to truncate relations during recovery.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Maybe we could make this work by fiddling with a different smgr -- on
it, smgr_sync would be a noop, as would smgr_immedsync, and we could
kludge something up to truncate relations during recovery.

Interesting thought but I think it falls down on pg_statistic.

One comment I've got is that we have already concluded that the spec's
GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session
persistence of the table definitions, but rather to module visibility
which is a concept we have not got (yet). Ergo, we should not use the
phrase "global temp table" for these things. Not sure what to suggest
instead. Perhaps call them "session tables" instead of "temp tables"?

regards, tom lane

Perhaps call them "session tables" instead of "temp tables"?

regards, tom lane

Or "transient table" ...

Maybe we can define when such table lose data

But in real - there is no need in this feature - databases are made to hold
data, not to lose.

If an application requires mechanism to store transient session-data, it
should create its own session-objects.

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

Tom Lane <tgl@sss.pgh.pa.us> wrote:

we have already concluded that the spec's
GLOBAL/LOCAL TEMP TABLE distinction is not related
to cross-session persistence of the table definitions

How do you reconcile that conclusion with the following,
from ISO/IEC 9075-2:2003 (E), 4.14 Tables:

"The definition of a global temporary table or a created local
temporary table appears in a schema. In SQL language, the name and the
scope of the name of a global temporary table or a created local
temporary table are indistinguishable from those of a persistent base
table. However, because global temporary table contents are distinct
within SQL-sessions, and created local temporary tables are distinct
within SQL-client modules within SQL-sessions, the effective <schema
name> of the schema in which the global temporary table or the created
local temporary table is instantiated is an implementation-dependent
<schema name> that may be thought of as having been effectively
derived from the <schema name> of the schema in which the global
temporary table or created local temporary table is defined and the
implementation-dependent SQL- session identifier associated with the
SQL-session."

There is a distinction between the definition, which "appears in a
schema" and for which "the name and the scope ... are
indistinguishable from those of a persistent base table", versus the
effective schema in which an instance is materialized, which is
session and/or module dependent.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

we have already concluded that the spec's
GLOBAL/LOCAL TEMP TABLE distinction is not related
to cross-session persistence of the table definitions

How do you reconcile that conclusion with the following,
from ISO/IEC 9075-2:2003 (E), 4.14 Tables:

The point is that what we call "temp tables" are not either global or
local temp tables by the spec's definition. If we invent something that
behaves as Pavel suggests, then it could be considered either a global
or a local temp table per spec (without any module support you can't
really say which it is). We're stuck in a terminological problem
anyway, but it will get a whole lot worse if we fail to acknowledge that
there's more than one property involved here.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

GLOBAL/LOCAL TEMP TABLE distinction is not related to cross-session
persistence of the table definitions

On a re-read, I think I see your point -- it is the DECLARE LOCAL TEMP
TABLE versus CREATE { GLOBAL | LOCAL } TEMP TABLE which determines
whether the table definition is persisted. Both forms of CREATE TEMP
TABLE should persist the definition if you go by the standard, so you
don't want to muddy the waters by complying on one and not the other?

-Kevin

Tom Lane <tgl@sss.pgh.pa.us> wrote:

it could be considered either a global or a local temp table per
spec (without any module support you can't really say which it is).

That seems bogus -- without modules it is clearly not LOCAL. What
Pavel is requesting exactly matches the spec's definition of a global
temporary table, but it does make me uneasy that after accepting the
standard syntax, and behaving differently from it (including making no
distinction between GLOBAL and LOCAL declarations) we would suddenly
go to compliance on GLOBAL declarations but leave LOCAL as is.

Maybe too messy to try to improve.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

... Both forms of CREATE TEMP
TABLE should persist the definition if you go by the standard, so you
don't want to muddy the waters by complying on one and not the other?

Right. This goes back to our old principle of trying not to use
spec-defined syntax for not-per-spec behavior. We are already behind
the eight ball as far as temp tables go, but let's not make it worse by
blindly picking some spec-defined syntax without a plan for where we go
from here. (I'm assuming that it's reasonably likely that we will want
a spec-compatible module feature someday. We'll really have painted
ourselves into a corner if we don't think about the issue now.)

regards, tom lane