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