Subject Re: [Firebird-Architect] RFC: Proposal for the implementation
Author Ann W. Harrison
At 05:24 AM 11/24/2004, Dmitry Yemanov wrote:

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

Do you have another interpretation of SQL-client modules? I've
never seen an implementation, but it's a concept that's been
cluttering up the standard forever. That said, I too prefer
good practice, but that leads to GDML, not SQL.

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

I think we're in violent agreement. The proposal is for one
type of temporary table (as opposed to the three in the standard)
which will be called a GLOBAL TEMPORARY TABLE. The scope of the
definition of the table is the schema. The scope of the data in
the table is the current connection.

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

Which I why I think that the <as subquery clause> should be
a separate discussion. It does introduce some interesting
problems for which the obvious "right answer" is different
for permanent tables. (Persistent tables is probably the
right term, but I can generally type "permanent" and always
need the spell corrector to fix "persistent". Besides, "persistent"
carries a connotation of "determined" or "stubborn." Dog knows,
I don't need attitude from my tables.)

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

No, actually what I had in mind for WITH DATA on permanent tables
was adding another phase to DFW which would populate the table.
Phase 1, validate the definition. Phase 2, create the physical
structure. Phase 3, populate.