Re: [HACKERS] slow SP with temporary tables, PLPGSQL problems

[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
  



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] slow SP with temporary tables, PLPGSQL problems

[Pavel Stehule]

> Pavel Stehule <[EMAIL PROTECTED]> 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 NEXT r.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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]