Re: Drop a local temporary table within a function

14 Jul 2017

...
On 8 Jun 2017, at 15:44, martin van dinther  wrote:
Answers inserted below
On 20-03-17 23:27, Luis Larios wrote:
...
Hi, all,
Consider the following scenario.  I have a bunch of user defined functions, f_i, that create, populate, and use a local temporary table with the same name “res”:
create function f_i()
returns table( … )
begin
       # Result stats table.
       create local temporary table res(
             param1 int,
             param2 int
       );
/* Populate res */
       /* Use res in some queries */
return table(
       /* Query involving res */
       );
end;
My first question is: Does “create local temporary table x” create a table that is disposed/dropped as soon as the function where it was declared finishes execution?
No. The table remains till the end of the user session.
Just to share what I tried:

It seems that a local temporary table doesn’t exist outside the function, so there is no need to remove it afterwards:

$ cat /tmp/tmptbl.sql 
create function f () returns int
begin 
	declare res int;

	create local temporary table test(i int);
	insert into test values (42), (20);

	set res = (select sum(i) from test );
	return res;
end;

$ mclient 
…
sql>\< /tmp/tmptbl.sql
operation successful
sql>select f(); 
+------+
| L2   |
+======+
|   62 |
+------+
1 tuple (6.227ms)
sql>select (select name from sys.schemas s where s.id = t.schema_id) as sch_nm, t.* from sys.tables t where temporary = 1 or type in (20, 30);
+--------+----+------+-----------+-------+------+--------+---------------+--------+-----------+
| sch_nm | id | name | schema_id | query | type | system | commit_action | access | temporary |
+========+====+======+===========+=======+======+========+===============+========+===========+
+--------+----+------+-----------+-------+------+--------+---------------+--------+-----------+
0 tuples (19.720ms)
sql>drop table test;
DROP TABLE: no such table 'test'
sql>drop table tmp.test;
DROP TABLE: no such table 'test'
...
...
I assumed this was the case, but I’m now skeptical as we are running into all sorts of concurrency issues that point to the “res” table not being cleared out properly from memory.
This takes me to the second question:  How do I drop a temporary table if such table exists?
Use DROP TABLE "tmp".res;
DROP TABLE seems to be not allowed in a function:

sql>create function f2() returns int begin create local temporary table test2(i int); drop table tmp.test2; return 1; end;
Statement 'DROP_TABLE' is not a valid flow control statement
...
...
The reason for this is to prevent “table create” exceptions (which we are currently running into).  I’d like to achieve something along these lines:
create function f_i()
returns table( … )
begin
       # Drop local temporary table if such table exists.
       drop local temporary table res;         <-------- How do I do this in MonetDB?
# Result stats table.
       create local temporary table res(
             param1 int,
             param2 int
       );
/* Populate res */
       /* Use res in some queries */
return table(
       /* Query involving res */
       );
end;
Where, as you can see, I want to explicitly drop the local temporary table “res” if such exists.  If I leave the “local temporary” markers in the drop statement, MonetDB complains that it found unexpected “LOCAL” or “TEMPORARY”.  I wouldn’t like to remove these markers because a statement like:
drop table res;
will drop any table in the default function schema with that name.
All the temporary tables are stored in schema "tmp". To list them use: 
select (select name from sys.schemas s where s.id = t.schema_id) as sch_nm, t.* from sys.tables t where temporary = 1 or type in (20, 30);
This is handy.  Can we put it in a VIEW or something?

Jennie
...
To drop a temporary table use the full qualified name such as: DROP TABLE "tmp".res;
...
Any help and hints are well appreciated!
Thanks for the help in advance
~ Luis Angel
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list

Re: Drop a local temporary table within a function

Ying Zhang