At 04:07 PM 11/23/2004, Adam Clarke wrote:

>Will an attempt to create a temporary table with the same name as an
>existing temporary table (from another uncommitted transaction) fail
>even it you use ON COMMIT DELETE?

Creating temporary tables is just like creating permanent tables.
The create statement causes changes to the system tables but the
table isn't instantiated - or useable - until the transaction that
created it commits. And yes, if two transactions both attempt to
create temporary tables called "FOO", the second will stall then
get an error if the first commits.

>I would hope not because this
>simplifies the use of temporary tables, otherwise you must manage
>conflicts yourself somehow.

Actually, you have to plan ahead or use a mechanism to
insure that your table names are unique. The latter
has a serious problem in that it will eventually run
into the 32768 table definition limit.

> > 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.
>
>Once committed with "ON COMMIT PRESERVE" only, or in both cases?

The "on commit preserve" clause does not allow data sharing between
connections. It allows a single connection to keep its temporary
data across its transactions.

> > 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.
> >
>
>I think the above should read "ON COMMIT DELETE" x 2.

Right. On delete commit is an entirely different kettle of fish.

Cheers,


Ann