Thread: Error with temporary tables
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
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: > > 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
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: >> 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
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