Обсуждение: slow SP with temporary tables, any idea of solution?

Hello

I have very slow SP this type:
BEGIN CREATE TEMP TABLE xxx ON COMMIT DROP(); WHILE n > 0 LOOP -- n >> 0   FOR _r IN EXECUTE 'SELECT ...' LOOP
RETURNNEXT _r;     EXECUTE 'UPDATE xxx SET item = 1 WHERE id = '||_r.id;     n := n - 1;   END LOOP; END LOOP;
 
END;

The main problem is too much EXECUTE commands. My first idea wos 
substitute it PREPARED plans. But PREPARED plans are compiled when 
procedure is first time lunched, and I can't easy use it form temp tables. 
I can change PREPARE upd UPDATE item SET .. to EXECUTE 'PREPARE upd 
UPDATE..' and I really saved some time. But this method isn't possible for 
cmd FOR  

I have two possibility solution (before ending successfull solution for SP 
and temporary tables). First, the time for really preparing command will 
be really time of executing PREPARE command. Second, PREPARE can accept 
string parametr like EXECUTE command. But all is inpossible now.

Can you help me other possibilities?
Thank you
Pavel Stehule

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> Can you help me other possibilities?

Create the temp table only once per connection (you can use ON COMMIT
DELETE ROWS instead of ON COMMIT DROP to clean it out).  Then you won't
need to use EXECUTE.
        regards, tom lane

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> > Can you help me other possibilities?
> 
> Create the temp table only once per connection (you can use ON COMMIT
> DELETE ROWS instead of ON COMMIT DROP to clean it out).  Then you won't
> need to use EXECUTE.
> 
I am not sure so it's possible. I use persistent connect via PHP. There 
isn't trigger for new connect. But I found same problem as you. PL/pgSQL 
can't    FOR r IN EXPLAIN SELECT ..  FOR r IN EXECUTE 'EXECUTE plan()'

I rewrite SP, and I have only one SELECT without two, 20% time less, but 
it's not readable code. I don't know how much work or if its possible move 
compilation time for PREPARE on every processing of this command. I think 
so its more natural for cmd PREPARE. But in this part of PL/pgSQL are more 
problems:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$
DECLARE r RECORD;
BEGIN PREPARE se(date) AS SELECT * FROM queue WHERE activated = $1; FOR r IN EXECUTE se(CURRENT_DATE) LOOP   RETUTRN
NEXTr.activated; END LOOP; DEALLOCATE se; RETURN;
 
END; $$ LANGUAGE plpgsql;

is this code correct? I think yes. But isn't true. I get message: function 
se(date) does not exist CONTEXT: SQL statement "SELECT se(CURRENT_DATE)".

Is only theory: FOR rn IN EXECUTE 'EXECUTE se(CURRENT_DATE)' LOOP
Now I get error: cannot open non-SELECT query as cursor. Prepared commands 
are good idea, but I cant use its now.

I have Pg 8.0.1

Regards
Pavel Stehule

> > Create the temp table only once per connection (you can use ON COMMIT
> > DELETE ROWS instead of ON COMMIT DROP to clean it out).  Then you won't
> > need to use EXECUTE.
> > 
I am sorry, first time I didn't understand. Now I did some test and its 
good adivice. Sometimes I have problem understand so I can use temp. 
tables on an level of connection and its not neccesery push temp tables on 
SP level. If I don't change structure of temp tables (and its unpropably 
on produstion database) I haven't problems. I have still one question. 
What is possible so I can

CREATE OR REPLACE FONCTION foo() ..
BEGIN CREATE TEMP TABLE xxx(... INSERT INTO xxx VALUES (... 

It's works, but in time of compilation SP temp table xxx doesn't exists. 
It's mean so Id in cmd INSERT is little bit dynamic? 

I rewrite my SP and I have one notice: In documentation is note, so 
TRUNCATE TABLE is faster then DELETE FROM. It's not true for small temp 
tables. On my computer TRUNCATE needs 100ms and DELETE 8ms. It's general 
or any exception?

Thank You
Pavel Stehule