"Paul Beach" <pbeach@...> wrote:

>
> Syntax:
>
> CREATE TEMPORARY TABLE <table name> <table contents source>
> [ON COMMIT {PRESERVE | DELETE} ROWS]

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

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). AFAIU, InterBase 7.5 offers
exactly the same semantics. Sybase ASA - the same. DB2 offers DECLARE GLOBAL
TEMPORARY TABLE which is not included into the system schema and hence is
visible only on a per-session basis. 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. 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.

So everybody distinguishes between GLOBAL and LOCAL in terms of the user
session. 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?

> No connection can see data stored in a temporary table by another

connection.

Questionable decision, but it seems it's what almost everybody supports.

> A temporary table can be either the referenced or referencing table in a

foreign key constraint, but the partner table must

> be a temporary table. If either table is ON DELETE COMMIT, both must be

ON DELETE COMMIT.

Tend to agree here.

> System table support:
> A new field will be added to RDB$RELATIONS, called RDB$RELATION_TYPE.

I'm with Arno here. Relation type is commonly considered to represent a
table or a view.

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

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

> A temporary table is represented internally by a TemporaryRelation class,

which is a subclass of the Relation class. A

> TemporaryRelation instance is created either by dfw, CStatement, or during

the processing of blr. TemporaryRelation classes hang

> off the attachment object as well as the Database object. The

TemporaryRelation object provides the location of the index root

> page for that table instance and it's pointer page list.

Fine.

> Changes to VIO, DPM, NAV, BTR, and IDX would be primarily cleanup and

applying object discipline to the driving structures. Record

> source blocks become a class hierarchy. References to the source

relation would be through either the TemporaryRelation object or

> the Relation.

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.

> Using the active database leverages the existing structures. Free space

can be located with existing mechanisms completely

> unchanged. Page number references in indexes and blobs can be used

directly. Cleaning up the pages used by a temporary table in

> the case of a system crash is problematic.
>
> Using a temporary file requires that space management and all page

references be redirected to the temporary file, increasing the

> number of layers that must be aware of this change. A temporary file is

automatically destroyed in case of system failure.

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? In
this case we keep all advantages of VIO and DPM (CCH usage is questionable)
with an ability of a quick data destroy.

Considering the GLOBAL question solved, I don't see how:

1) LOCAL can be implemented
2) AS <subquery> can be implemented


Dmitry