Re: idea: global temp tables

-----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-----