Error with temporary tables

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

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:

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

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:

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