At 04:35 PM 11/23/2004, Dmitry Yemanov wrote:

>As almost everybody mentioned, GLOBAL and LOCAL are missing ;-)

Let me see if I can follow all of this through. Does everyone agree
that the standard specifies that the data in temporary tables are
SQL-session objects and that a SQL-session is equivalent to a connection?

OK, now does everyone agree that the standard uses LOCAL and GLOBAL
to define the scope of temporary table data between SQL-client modules
and that we don't support SQL-client modules?

>Oracle supports GLOBAL TEMPORARY TABLE with global schema and
>session-specific data. AFAIU, this is what you propose. Oracle does not
>support LOCAL TEMPORARY TABLE (at least in v9).

OK, so if we added { GLOBAL | LOCAL } with LOCAL ::= notYetImplemented,
then we'd be consistent with the standard (my opinion at least) and
with Oracle.

>AFAIU, InterBase 7.5 offers exactly the same semantics. Sybase ASA - the
>same.

And with Sybase and InterBase. (Wow, the company we keep!)

>DB2 offers DECLARE GLOBAL
>TEMPORARY TABLE which is not included into the system schema and hence is
>visible only on a per-session basis.

Which is OK, because GLOBAL refers to the scope with regard to modules,
not schemas.

> MSSQL has both global and local temp
>tables defined via the non-standard syntax with specific scopes (all
>sessions or current session) and global temp tables have data shared between
>sessions.

OK, how many people think we should show our independence by ignoring
the standard and the four implementations that are more or less compliant
with it and follow the lead of MSSQL?

> MySQL has just TEMPORARY TABLE which is visible only for the
>current session. PostgreSQL offers only LOCAL TEMPORARY TABLE which is also
>visible for the current session only.

When you say visible, do you refer to the definition or the
data? Or both?

>So everybody distinguishes between GLOBAL and LOCAL in terms of the user
>session.

Hunh? Sorry. I thought you said that the visibility of the table
definition was governed by the use of schemas and that the data was
in all cases (except MSSQL) private to the connection.

> Everybody considers GLOBAL visible to other sessions except DB2
>(and perhaps MySQL). Everybody offers session-specific data for GLOBAL
>except MSSQL. Can we stick to the majority and name our implementation
>accordingly?

Sorry, the standard is very clear about the meaning of GLOBAL and
LOCAL. I really don't see why we should choose some other meaning.

> > No connection can see data stored in a temporary table by another
>connection.
>
>Questionable decision, but it seems it's what almost everybody supports.

And what the standard specifies.

> > No changes are proposed to ALTER TABLE or DROP TABLE. Changing a table
>from permanent to temporary or vice-versa is accomplished
> > by dropping the table and recreating it.
>
>Agreed, but a change between DELETE ROWS and PRESERVE ROWS should be
>allowed.

OK, but it requires that you get a relation existence lock so you
don't change the semantics of a compiled request.

> > The syntax AS <query expression> has been suggested as an alternative to
>the <table contents source> listed in the syntax above. If
> > that syntax is part of the SQL-99 standard, I can't find it.
>
><table contents source> ::=
> <table element list>
> | OF <path-resolved user-defined type name>
> [ <subtable clause> ]
> [ <table element list> ]
> | <as subquery clause>

SQL-99? However, since it's obviously part of the 2003 standard,
let me think about it. Defining tables based on other tables has
been around for a donkey's years. The full subquery makes it very
much like a persistent view ... that persists for the duration of
a single connection.

> > Changes to VIO, DPM, NAV, BTR, and IDX w...
>
>Temporary relation is an incorrect term, as views are also relations. I'd
>prefer to see an abstract class Relation with descendant classes
>PersistentTable, TemporaryTable, ExternalTable, View.

Right.

> > Choice of location for data of temporary tables .. in the database or out?

>Requires more thinking. What about PAG_temporary flag which means that a
>page I/O should be redirected to the TempSpace object instead of PIO?

Dmitry - it's late. Not a flag. But essentially, that's the idea -
that the lower parts of the I/O system - searching for free space etc.
would be subclassed to handle data outside the database.

> In
>this case we keep all advantages of VIO and DPM (CCH usage is questionable)
>with an ability of a quick data destroy.

I would think we could also keep the CacheManager. CCH... well...
it had a good long ride, but it's so full of ... history.

>Considering the GLOBAL question solved, I don't see how:
>
>1) LOCAL can be implemented

What you mean by local can't be implemented without a multi-layer
name space.

>2) AS <subquery> can be implemented

That, I think, is a question of pulling together some existing
mechanisms, cleaning them up, then generalizing and specializing
them.


Regards,


Ann