Обсуждение: 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