Re: Java Postgresql temporary table
On Aug 27, 12:08 am, Mark Space <marksp...@sbcglobal.net> wrote:
Arne Vajh=F8j wrote:
PostgreSQL support temporary tables that are per connection.
That's interesting, how does that work? Do you get a key back to refer
to the table, or do you have to leave the DB connection open? Somethin=
g
else perhaps?
Postgres' temporary tables are associated with either the current
transaction or with the connection, depending on how they're created.
They may even have (equally-temporary) indexes created against them.
There's a limitation on Postgres' two-phase commit implementation in
8.3 which prevents transactions involving temporary tables from being
PREPARE TRANSACTIONed in preparation for 2PC, but otherwise they're
almost always perfectly intuitive to work with.
The SQL spec demands that temporary tables' definitions be visible
globally -- that is, the statement CREATE TEMPORARY TABLE foo (bar
INTEGER) creates a table foo which is initally empty for all clients
(and possibly at the start of each transaction; I'm unclear on the
finer legalities) and whose rows are only those inserted by the same
client (transaction) selecting them. Postgres goes the other way:
temporary tables must be created explicitly by clients that need them,
and may have as many different definitions as there are live clients.
I believe under the hood they're implemented as normal tables whose
real names are gensymmed and mapped within the context of a
connection's namespace to the temporary table name, plus some magic
for dropping the table when it's no longer needed.
-o