From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Jan Wieck <janwieck@yahoo.com>
Cc: jack <datactrl@tpg.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: call the same pl/pgsql procedure twice in the same connection
Date: Wed, 17 Apr 2002 18:07:04 -0400 (EDT)
Message-ID: <200204172207.g3HM74D09071@candle.pha.pa.us> (raw)
In-Reply-To: <200203252136.g2PLaEg25650@saturn.janwieck.net>
Jan, instead of doing cache invalidation to fix temporary tables, can we
disable cached plans for functions that use temporary tables?
---------------------------------------------------------------------------
Jan Wieck wrote:
> Bruce Momjian wrote:
> > jack wrote:
> > > I have a function with pl/pgSQL such as...
> > > CREATE FUNCTION comp_userExp (TEXT) RETURNS INTEGER AS '
> > > DECLARE
> > > stUserName TEXT;
> > > BEGIN
> > > stUserName := upper($1);
> > >
> > > CREATE TEMP TABLE comuser AS
> > > SELECT * FROM comt_user1
> > > WHERE userName=stUserName;
> > >
> > > CREATE TEMP TABLE comUser1 AS
> > > SELECT a.userName FROM comt_user2 a, comuser b
> > > WHERE a.userName = b.userName
> > > ORDER BY b.userName;
> > >
> > > CREATE TEMP TABLE comUser2 AS
> > > SELECT a.userName FROM comt_user3 a, comuser b
> > > WHERE a.userName = b.userName
> > > ORDER BY b.userName;
> > >
> > > DROP Table comuser,comuser1,comuser2;
> > >
> > > RETURN 0;
> > >
> > > END;'
> > > LANGUAGE 'PLPGSQL';
> > >
> > > This function can't run twice in the same connection session. After tracing
> > > error, the reason is because 2nd and 3rd SQL refer to table comuser. It will
> > > cause "can't find relation number xxxxx". I think ,after first run, the
> > > procedure just use relation number to access table, while actually table was
> > > dropped and create again with a different relation number on the 2nd time.
> > > If I disconnect database, and re-connect again, it won't cuase any problem.
> > > If don't want to disconnect and connect, is there any way to fix the
> > > problem?
> >
> > Yes, this is coming up a lot recently, maybe an FAQ. You need to use
> > EXECUTE in plpgsql so the string is reparsed every time and the proper
> > oid assigned.
>
> This is somehow connected to the temporary view discussion,
> as it needs the same detection if a query depends on
> temporary objects. As soon as we have a detection mechanism
> for it, I can modify PL/pgSQL not to save prepared plans for
> these statements.
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, janwieck@yahoo.com, datactrl@tpg.com.au
Subject: Re: call the same pl/pgsql procedure twice in the same connection
In-Reply-To: <200204172207.g3HM74D09071@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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