From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | GLOBAL vs LOCAL temp tables |
Date: | 2003-04-15 14:35:44 |
Message-ID: | 7903.1050417344@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I've just been having an informative off-list conversation with Mike
Sykes. As he pointed out in a message that Marc forwarded to the list
http://archives.postgresql.org/pgsql-hackers/2003-04/msg00411.php
we shouldn't feel bad about the fact that our temp table implementation
doesn't conform to the standard's semantics for temp tables, because
almost no one else does it the spec's way either. Oracle and DB2, to
name a couple of big players, do it effectively the same way we do.
But he also points out that we are confused about the difference between
GLOBAL and LOCAL temporary tables. In the spec, this distinction does
*not* mean cross-session vs session-private temp tables, as we wrote in
the documentation. In fact, there are no cross-session temp tables at
all in SQL92. GLOBAL means there is one instance per session, while
LOCAL means there is one instance per module invocation (which is thus
necessarily within a session). The text of the spec is clear:
... Global and created local temporary tables are
effectively materialized only when referenced in an SQL-session.
Every <module> in every SQL-session that references a created local
temporary table causes a distinct instance of that created local
temporary table to be materialized. That is, the contents of a
global temporary table or a created local temporary table cannot
be shared between SQL-sessions. In addition, the contents of a cre-
ated local temporary table cannot be shared between <module>s of a
single SQL-session.
Since we don't have modules, the distinction between GLOBAL and LOCAL
temp tables is meaningless for us. However, if we were to someday
implement modules, we would probably expect that the existing flavor of
temp tables would remain globally visible throughout each session. That
is, the temp tables we have more nearly approximate the spec's GLOBAL
temp tables than LOCAL temp tables.
As Mike pointed out in the message referenced above, Oracle's and DB2's
Postgres-equivalent syntax uses GLOBAL not LOCAL to describe temp
tables.
So it now seems clear to me that we are in error to reject CREATE GLOBAL
TEMP TABLE; we ought to accept that.
What I am wondering now is if we should flip the logic to reject CREATE
LOCAL TEMP TABLE? Or should we just silently accept both? I'm leaning
towards the latter, on the grounds of backward compatibility.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2003-04-15 15:49:11 | Re: [GENERAL] Problem about pgsql's column alias |
Previous Message | Zeugswetter Andreas SB SD | 2003-04-15 14:25:29 | Re: [GENERAL] Problem about pgsql's column alias |