Re: call the same pl/pgsql procedure twice in the same connection

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,
Pennsylvania19026