Postgres Pro
Downloads
Home   >   mailing lists

Thread: Temporary table in pl/pgsql

Temporary table in pl/pgsql

From
Raymond O'Donnell
Date:
Hello again all,

I'm using a temporary table in a pl/PgSQL function, and I've read the
bit in the FAQ about using EXECUTE to force the table-creation query to
be re-evaluated each time the function is called.

However, it doesn't seem to work for me. The first time I call the
function, all is well; the second (and every subsequent) time, the
function bombs with the 'relation with OID XXXXX does not exist' error -
this seems to imply that the EXECUTE statement is getting cached along
with the rest of the function, which according to the docs ain't
supposed to happen :-(

I'm using 8.2.3 on WinXP.

Here's the first part of the function - I'll supply a complete test case
if necessary:

-------------------------------------------------
create or replace function tutor_availability_remaining
(
   aTerm integer,
   anInstrument varchar,
   aLevel varchar
)
returns setof tutor_availability
as
$$
declare
   OrigBlock tutor_availability%rowtype;
   SlotsForDay teachingslots%rowtype;
begin
   -- Create a termporary table to hold the results.
   -- Use EXECUTE to force this to be executed each
   -- time, as per the FAQ.
   execute 'create temporary table TheResults('
     || 'block_id integer, term_id integer, term_name varchar(40), '
     || 'the_date date, month_name varchar(12), day_name varchar(12), '
     || 'is_weekend boolean, tutor_id integer, surname varchar(40), '
     || 'firstname varchar(40), block_starts time without time zone, '
     || 'block_ends time without time zone)';

   -- stuff snipped here...
   -- etc etc

----------------------------------------

I wondered if, the string passed to EXECUTE, being entirely literal, it
was somehow getting over-optimised :) and I tried changing the second
line above to use one of the parameters passed in, thus -

   'term_id integer default ' || quote_literal(aTerm) ...

- but it made no difference.

What am I missing?

TIA,

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: Temporary table in pl/pgsql

From
"Merlin Moncure"
Date:
On 4/13/07, Raymond O'Donnell <rod@iol.ie> wrote:
> Hello again all,
>
> I'm using a temporary table in a pl/PgSQL function, and I've read the
> bit in the FAQ about using EXECUTE to force the table-creation query to
> be re-evaluated each time the function is called.
>
> However, it doesn't seem to work for me. The first time I call the
> function, all is well; the second (and every subsequent) time, the
> function bombs with the 'relation with OID XXXXX does not exist' error -
> this seems to imply that the EXECUTE statement is getting cached along
> with the rest of the function, which according to the docs ain't
> supposed to happen :-(

in addition to the 'create table' stmt, all queries that touch the
table must also be dynamic.  postgresql 8.3 will have improved plan
invalidation which will (aiui) remove this requirement.

merlin

Re: Temporary table in pl/pgsql

From
Listmail
Date:
    OK, suppose in his function :
    - if it does not exist, he creates the temp table, with ON COMMIT DELETE
ROWS
    - if it does exists, he truncates it just to be sure

    So the next execution of the function will find the temp table, it will
have the same OID, all is well.

    Now :

BEGIN
execute the function (and creates the table)
ROLLBACK
execute the function

    The Rollback will have rolled back the table creation too. Now when he
executes the function again, will it get the cached plan with the rolled
back table's OID ?


On Fri, 13 Apr 2007 22:55:49 +0200, Merlin Moncure <mmoncure@gmail.com>
wrote:

> On 4/13/07, Raymond O'Donnell <rod@iol.ie> wrote:
>> Hello again all,
>>
>> I'm using a temporary table in a pl/PgSQL function, and I've read the
>> bit in the FAQ about using EXECUTE to force the table-creation query to
>> be re-evaluated each time the function is called.
>>
>> However, it doesn't seem to work for me. The first time I call the
>> function, all is well; the second (and every subsequent) time, the
>> function bombs with the 'relation with OID XXXXX does not exist' error -
>> this seems to imply that the EXECUTE statement is getting cached along
>> with the rest of the function, which according to the docs ain't
>> supposed to happen :-(
>
> in addition to the 'create table' stmt, all queries that touch the
> table must also be dynamic.  postgresql 8.3 will have improved plan
> invalidation which will (aiui) remove this requirement.
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Re: Temporary table in pl/pgsql

From
Raymond O'Donnell
Date:
On 13/04/2007 21:55, Merlin Moncure wrote:

> in addition to the 'create table' stmt, all queries that touch the
> table must also be dynamic.  postgresql 8.3 will have improved plan
> invalidation which will (aiui) remove this requirement.

Thanks for that - just tried it and it worked.

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------