pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlifetime of temp schema versus compiled image of plpgsql proc
7+ messages / 4 participants
[nested] [flat]
* lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-22 19:29 Dennis <pg-user@calico-consulting.com>
0 siblings, 2 replies; 7+ messages in thread
From: Dennis @ 2004-04-22 19:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-22 21:32 Dennis <pg-user@calico-consulting.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Dennis @ 2004-04-22 21:32 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-22 23:46 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2004-04-22 23:46 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-22 23:54 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Dennis <pg-user@calico-consulting.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2004-04-22 23:54 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-23 02:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-04-23 02:06 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-23 02:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-04-23 02:16 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Dennis <pg-user@calico-consulting.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lifetime of temp schema versus compiled image of plpgsql proc
@ 2004-04-23 21:10 Jaime Casanova <el_vigia_ec@hotmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jaime Casanova @ 2004-04-23 21:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-04-23 21:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-22 19:29 lifetime of temp schema versus compiled image of plpgsql proc Dennis <pg-user@calico-consulting.com>
2004-04-22 23:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-04-22 21:32 ` Dennis <pg-user@calico-consulting.com>
2004-04-22 23:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-04-23 02:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-23 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-23 21:10 Re: lifetime of temp schema versus compiled image of plpgsql proc Jaime Casanova <el_vigia_ec@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox