"Ann W. Harrison" <aharrison@...> wrote:

>
> 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?

I think so.

> 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?

Do you know any RDBMS which support SQL-client modules in your understanding
of this term? If no, there's no need to follow the spec. I prefer good
practice over abstract non-materialized theory.

> >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.

We don't need to implement LOCAL at all at this stage and we'll still be
consistent with the standard.

> 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?

Definitely, not me ;-)

> > 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?

Definition.

> >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.

I meant that if DBMS declares GLOBAL TEMP TABLE, then it explicitly states
that its globality is defined in terms of sessions. GTT persists in the
system schema, so it's visible for all. This is what "global" means for
them. The only exception is DB2.

> >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.

I don't buy this argument, sorry. What you propose is named GTT in Oracle,
Sybase, PostgreSQL and InterBase. If we don't understand what the SQL spec
means and if nobody supports what it looks to mean, then I see no reason to
follow this 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.

OK, at least this is quite consistent and understandable.

> >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.

Of course.

> >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.

Yep, this is something I was thinking about.

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

It can, although I'm not sure it will be an elegant solution. I just wanted
to ask whether your proposal covers only GTTs (in my understanding) or both?
If the latter, there were no mentions on how it could be implemented.

> >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.

You cannot insert data in the table which is not committed. Note that AS
<subquery> is defined for all types of tables, including pesistent ones. If
you mean implicit DDL autocommit, then we'll have a problem with non-atomic
execution of CREATE TABLE. Or you had in mind that AS <subquery> is to be
implemented for TTs only and TTs are designed to avoid DFW and allocate the
root pages immediately?


Dmitry