On 31-01-2006 19:01:53 +1000, Lee Marks wrote:

In other words, if I say

create local temporary table foo (abc integer); insert into foo values(1); insert into foo values(2); select * from foo;

and execute the whole thing in one go, no problem. But I can't create the temp table first and then separately execute the insert and select statements. Is there any way to have connection-scoped persistence?

Is this within a transaction or not? Does this work for example: START TRANSACTION; CREATE LOCAL TEMPORARY TABLE foo (abc integer); INSERT INTO foo VALUES(1); INSERT INTO foo VALUES(2); SELECT * FROM foo; COMMIT;

Hi On Tue, 31 Jan 2006 10:19:08 +0100 Fabian Groffen wrote:

Ok, I looked it up in the SQL99 book. LOCAL and GLOBAL TEMPORARY TABLES should persist till the end of the SQL session, after which they are automatically dropped. Seems like an SQL session is defined as 'excution of one or more consecutive SQL statements by a single user'. It's not said that clear, but it looks as if the session indeed is equal to the life span of the connection. In that case it looks as if MonetDB/SQL's current way of doing it is wrong.

My experiences are with Sql Server and Postgres and with these it is the life span of the connection. In fact with postgres, there are 3 different options: (1) create temp table tbl (..) on commit preserve rows (2) create temp table tbl (..) on commit delete rows (3) create temp table tbl (..) on commit drop Option 1 is the default when there is no 'on commit' clause given and this gives the connection-based life span. It seems Monet DB is implementing option 3 (which is also a useful behaviour) but it would of course be nice to have all these possibilities. Lee.

On Tue, Jan 31, 2006 at 07:01:53PM +1000, Lee Marks wrote:

Hi Niels,

On Tue, 31 Jan 2006 07:58:04 +0100 Niels Nes wrote:

...

indeed the syntax is slightly wrong. Try the following statement instead.

create local temporary table foo (abc integer);

Thanks, this works though not quite as I had expected! The created temp table only persists for the duration of a single batch execution rather than for the duration of the connection.

In other words, if I say

create local temporary table foo (abc integer); insert into foo values(1); insert into foo values(2); select * from foo;

and execute the whole thing in one go, no problem. But I can't create the temp table first and then separately execute the insert and select statements. Is there any way to have connection-scoped persistence?

Indeed the current implementation of temporary tables is limited. Temporary tables currently only live in one transaction. Also there is currently no difference between global and local temporary tables. Clearly you found a missing feature (maybe you could file this feature request on the sourceforge pages). We hope to add this feature soon, hopefully in our next release. Niels

Thanks, Lee

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl