Thread: Error with temporary tables

Error with temporary tables

From
Claire McLister
Date:
Hi,

  I'm using a pgsql function  that begins by creating a temporary
table, does some processing, and then drops the temporary table just
before exiting. It has been working fine for a while now, but
suddenly complains for some calls that "Relation with OID" does not
exist, at the point when it is executing the DROP table command.

  The general scheme is as follows:

  CREATE FUNCTION Foo(Integer) AS
  '
    BEGIN
       CREATE Temporary Table Bar AS (a left outer join of two tables)
           Do processing
       DROP Table Bar;
       RETURN 1;
    END
  '

  This is for Postgresql version 7.4.8

  Can someone tell me what I'm doing wrong? Should I try to use 'ON
COMMIT DROP' instead?

  Thanks

Claire

  --
  Claire McLister                        mclister@zeesource.net
  1684 Nightingale Avenue     Suite 201
  Sunnyvale, CA 94087            408-733-2737(fax)

                      http://www.zeemaps.com



Re: Error with temporary tables

From
Bruce Momjian
Date:
Read the FAQ.

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

Claire McLister wrote:
> Hi,
>
>   I'm using a pgsql function  that begins by creating a temporary
> table, does some processing, and then drops the temporary table just
> before exiting. It has been working fine for a while now, but
> suddenly complains for some calls that "Relation with OID" does not
> exist, at the point when it is executing the DROP table command.
>
>   The general scheme is as follows:
>
>   CREATE FUNCTION Foo(Integer) AS
>   '
>     BEGIN
>        CREATE Temporary Table Bar AS (a left outer join of two tables)
>            Do processing
>        DROP Table Bar;
>        RETURN 1;
>     END
>   '
>
>   This is for Postgresql version 7.4.8
>
>   Can someone tell me what I'm doing wrong? Should I try to use 'ON
> COMMIT DROP' instead?
>
>   Thanks
>
> Claire
>
>   --
>   Claire McLister                        mclister@zeesource.net
>   1684 Nightingale Avenue     Suite 201
>   Sunnyvale, CA 94087            408-733-2737(fax)
>
>                       http://www.zeemaps.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Error with temporary tables

From
Claire McLister
Date:
Thanks.

Actually, I had read the FAQ, and was correctly using the 'EXECUTE'
form of creating a temporary table. (If that had been the problem, it
would not have been working for a while.)

It turns out, the problem was a strange one. The function was
returning a set of records and one of the elements in the record was
being set like:

  R.Field := E.Value;

Where R is the returned record and E.Value was obtained from the
temporary table. The type of both R.Field and E.Value is varchar.

This was all working fine until E.Value became some large strings
with some occasional funny characters.

The hack that solved the problem was:

  R.Field := substring(E.Value from 1);

  This is a complete hack, so I'd like to find out what is going
wrong and why this worked. For now, it keeps the system functional.

Claire


On Feb 3, 2006, at 6:01 PM, Bruce Momjian wrote:

>
> Read the FAQ.
>
> ----------------------------------------------------------------------
> -----
>
> Claire McLister wrote:
>> Hi,
>>
>>   I'm using a pgsql function  that begins by creating a temporary
>> table, does some processing, and then drops the temporary table just
>> before exiting. It has been working fine for a while now, but
>> suddenly complains for some calls that "Relation with OID" does not
>> exist, at the point when it is executing the DROP table command.
>>
>>   The general scheme is as follows:
>>
>>   CREATE FUNCTION Foo(Integer) AS
>>   '
>>     BEGIN
>>        CREATE Temporary Table Bar AS (a left outer join of two
>> tables)
>>            Do processing
>>        DROP Table Bar;
>>        RETURN 1;
>>     END
>>   '
>>
>>   This is for Postgresql version 7.4.8
>>
>>   Can someone tell me what I'm doing wrong? Should I try to use 'ON
>> COMMIT DROP' instead?
>>
>>   Thanks
>>
>> Claire
>>
>>   --
>>   Claire McLister                        mclister@zeesource.net
>>   1684 Nightingale Avenue     Suite 201
>>   Sunnyvale, CA 94087            408-733-2737(fax)
>>
>>                       http://www.zeemaps.com
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>        choose an index scan if your joining column's datatypes do not
>>        match
>>
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


Re: Error with temporary tables

From
Tom Lane
Date:
Claire McLister <mclister@zeesource.net> writes:
> This was all working fine until E.Value became some large strings
> with some occasional funny characters.

> The hack that solved the problem was:

>   R.Field := substring(E.Value from 1);

>   This is a complete hack, so I'd like to find out what is going
> wrong and why this worked. For now, it keeps the system functional.

Oooh.  You were probably dealing with values that had gotten large
enough to be "toasted", ie, stored out-of-line in a TOAST table.
So the datum being passed around in memory was just a pointer to the
row in the TOAST table.  Dropping the temp table made its TOAST table
go away, resulting in a dangling pointer stored in the plpgsql variable.

The easy fix would be to forcibly detoast any value stored into a
plpgsql variable, but the performance implications of that seem a
bit nasty.  Not sure I want to do it for such a weird corner case...

            regards, tom lane

Re: Error with temporary tables

From
Claire McLister
Date:
Thanks. So, the hack we  did is okay then? Does using the substring
function de-TOAST it?

On Feb 4, 2006, at 11:24 AM, Tom Lane wrote:

> Claire McLister <mclister@zeesource.net> writes:
>> This was all working fine until E.Value became some large strings
>> with some occasional funny characters.
>
>> The hack that solved the problem was:
>
>>   R.Field := substring(E.Value from 1);
>
>>   This is a complete hack, so I'd like to find out what is going
>> wrong and why this worked. For now, it keeps the system functional.
>
> Oooh.  You were probably dealing with values that had gotten large
> enough to be "toasted", ie, stored out-of-line in a TOAST table.
> So the datum being passed around in memory was just a pointer to the
> row in the TOAST table.  Dropping the temp table made its TOAST table
> go away, resulting in a dangling pointer stored in the plpgsql
> variable.
>
> The easy fix would be to forcibly detoast any value stored into a
> plpgsql variable, but the performance implications of that seem a
> bit nasty.  Not sure I want to do it for such a weird corner case...
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


Re: Error with temporary tables

From
Tom Lane
Date:
Claire McLister <mclister@zeesource.net> writes:
> Thanks. So, the hack we  did is okay then? Does using the substring
> function de-TOAST it?

Yeah, that should work till we think of a proper fix.

            regards, tom lane