Subject Re: [Firebird-Architect] Counter proposal to Temporary tables
Author Lester Caine
Ann W. Harrison wrote:

> A problem with the temporary table discussion is that we realized,
> at least I did, fairly well into the topic, that we were actually
> trying to deal with several different problems under the same name.
> 1) creating and maintaining connection private data that
> can be queried and updated like data in the database.
> 2) creating tables (permanent or temporary) that look like
> nd may contain the data from query result sets.
> 3) establishing a private name space.
> In the process we ran into one perfectly legitimate difference
> between Firebird and other standard-compliant database, to wit,
> Firebird DDL statements are transaction-based and schema objects
> can not be used, even by their creator, until the transaction
> that created them has committed.
> There are lots of people on this list who would like to change
> that last behavior. And perhaps we should. But we shouldn't
> change it piecemeal as part of a temporary table implementation.

That looks like a nice summary of the current situation.

So just to pad the detail here.
As I see it the only difference between 1 and 2 is the visibility of the
results. ! - data is never visible outside the connection ( is that the
same as a transaction or across transactions from the same connection? I
never understand the difference - in PHP the connection may be pooled by
the driver so several people use it ;) ). While 2 - data can be used
elsewhere, or just by the creator? What *IS* the difference between
permanent and temporary?

If the schema limitation was changed, could both of these in effect be
'views' created and destroyed in the correct 'space', with just a cache
of the generated data?

My own use of 'temporary tables' is probably of the type 'results AS
ABCD' where I can the use ABCD in the next query to further process
things, but at present I have to write 'results' to 'ABCD' which I have
created as a 'real' table, so I can use them.

So the question is, "Are we looking for one solution to what is
essentially several problems?". I think that there have been several
possible solutions proposed, but they obviously can't solve *ALL* the
problems in one. So do we need to re-define the problem so that the
pieces dovetail together rather than trying to find a one size fits all
solution? Some parts can be added now, others need to be worked towards
in later revisions? The end result may well be a standards correct
single answer, but the current code base does not allow that to be
supported easily yet?

Lester Caine
L.S.Caine Electronic Services