Subject RE: [Firebird-Architect] RFC: Proposal for the implementation of Temporary Tables.
Author Claudio Valderrama C.
Paul Beach wrote:
> Syntax:
> CREATE TEMPORARY TABLE <table name> <table contents source>

This is a globally visible table. Even if cumbersome, the syntax should
acknowledge that, because you wrote:
> All connections can see the definition of temporary tables;
> temporary tables are part of the global name space. No connection can
> see data stored in a temporary table by another connection.

Even if we are going to discuss a month what's global and local, please make
the correct syntax from the beginning.

> 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

ON COMMIT DELETE do you mean? The other way it should be named auto-commit

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

Is this done for performance reasons, for keeping the code simple or what? I
remember that rdb$relations has already a flags fields, so it could be used
if none of the previous arguments apply. Anyway, RDB$RELATION_TYPE is a
confusing name because it's used to distinguish tables from views.

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

Someone will have to dig into it.

> Data and indexes for a temporary table could be stored on pages
> in the active database or in a temporary file created for the
> instance. Each alternative has advantages.
> 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.

Considering the performance and reliability impact of temporary tables, it
seems better to suffer the burden of having temporary files at the cost of
writing more code.