Thread: lifetime of temp schema versus compiled image of plpgsql proc
Hi, this is pg 7.4.1 I am opening a connection to postgres starting a transaction executing a plpgsql function that creates temp tables executing a plpgsql function that populates the temp tables querying the temp table closing the transaction then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema "pg_temp_8" does not exist I am not specifying "on commit" when creating the temp tables. Are temp tables created in a transaction discarded when the transaction ends? I'm not explicitly referencing pg_temp_8 in my stored function. Can someone explain what is going on? Have I given enough information? dennis pg-user at calico dash consulting dot com
Bruce Momjian writes: > > There is an FAQ item on this --- use EXECUTE. So I should be using EXECUTE for all access to the temp tables? ie inserts, and selects (in this case). Should I use execute for the table creation? Dennis
There is an FAQ item on this --- use EXECUTE. --------------------------------------------------------------------------- Dennis wrote: > > Hi, > > this is pg 7.4.1 > > I am opening a connection to postgres > starting a transaction > executing a plpgsql function that creates temp tables > executing a plpgsql function that populates the temp tables > querying the temp table > closing the transaction > > then on the same connection, I open a transaction, execute a plpgsql > function that populates the temp tables and the function bombs with this > error message: > > ERROR: schema "pg_temp_8" does not exist > > I am not specifying "on commit" when creating the temp tables. Are temp > tables created in a transaction discarded when the transaction ends? > > I'm not explicitly referencing pg_temp_8 in my stored function. > > Can someone explain what is going on? Have I given enough information? > > dennis > pg-user at calico dash consulting dot com > > ---------------------------(end of broadcast)--------------------------- > TIP 9: 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, Pennsylvania19073
Dennis wrote: > Bruce Momjian writes: > > > > > There is an FAQ item on this --- use EXECUTE. > > So I should be using EXECUTE for all access to the temp tables? ie inserts, > and selects (in this case). Should I use execute for the table creation? All access from plpgsql functions for temp tables should use EXECUTE, yea. -- 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, Pennsylvania19073
"Dennis" <pg-user@calico-consulting.com> writes:
> then on the same connection, I open a transaction, execute a plpgsql
> function that populates the temp tables and the function bombs with this
> error message:
> ERROR: schema "pg_temp_8" does not exist
That's a bit hard to believe. Could we see a complete test case?
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Dennis wrote:
>> So I should be using EXECUTE for all access to the temp tables? ie inserts,
>> and selects (in this case). Should I use execute for the table creation?
> All access from plpgsql functions for temp tables should use EXECUTE,
I think that advice is not correct nor relevant to Dennis' problem. You
need EXECUTE if you are dropping and recreating temp tables within the
lifetime of a single connection, but he didn't do that. Even if he did
do it and hasn't told us so, that does not explain why the error message
complains about the temp *schema* and not a temp table. There's
something very strange here, because the temp schema name for a given
session is definitely fixed for the life of the session.
regards, tom lane
Hi all, I have a similar problem, i'm connecting to postgresql with th psql-odbc, create a temp table outside the transaction, insert into the temp, commit then i try to use the temp and it isn't there. Maybe an odbc problem?? are you using the odbc? _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963