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
distinctinstance of that created local temporary table to be materialized. That is, the contents of a
globaltemporary table or a created local temporary table cannot be shared between SQL-sessions. In addition, the
contentsof 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
