Re: Temporary Views

On Tue, 13 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > My feeling is that either the view is temporary, fully, or it isn't. I
> > don't see having it in the public namespace _and_ removing it on session
> > exit as defensible.
>
> I disagree ... (and who said this was necessarily the public namespace,
> anyway? Perhaps the view is in a private, but not temp namespace.)
>
> But I doubt we'll convince each other. Can we hear some other opinions?

SQL99 is pretty clear about temporary tables, at least.

From SQL99 4.16 'Tables' pp. 40,41

---

A global temporary table is a named table defined by a <table definition>
that specifies GLOBAL TEMPORARY. A created local temporary table is a
named table defined by a <table definition> that specifies LOCAL
TEMPORARY. Global and created local temporary tables are effectively
materialized only when referenced in an SQL-session. Every SQL-client
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 created local temporary table cannot be
shared between SQL-client modules of a single SQL-session. 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 <schemaname> 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.

In addition, the effective <schema name> of the schema in which the
created local temporary table is instantiated may be thought of as being
further qualified by a unique implementation-dependent name associated
with the SQL-client module in which the created local temporary table is
referenced.

---

So surely SQL sessions should not be able to see each other's temporary
tables the the views dependent upon them.

Gavin