pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
call the same pl/pgsql procedure twice in the same connection session
12+ messages / 5 participants
[nested] [flat]

* call the same pl/pgsql procedure twice in the same connection session
@ 2002-03-25 10:33 jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: jack @ 2002-03-25 10:33 UTC (permalink / raw)
  To: pgsql-sql

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?

JACK
JACK




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
@ 2002-03-25 21:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Bruce Momjian @ 2002-03-25 21:06 UTC (permalink / raw)
  To: jack <datactrl@tpg.com.au>; +Cc: pgsql-sql

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.

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-03-25 21:36   ` Jan Wieck <janwieck@yahoo.com>
  2002-03-26 00:35     ` Re: call the same pl/pgsql procedure twice in the same connection Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 12+ messages in thread

From: Jan Wieck @ 2002-03-25 21:36 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-03-26 00:35     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 12+ messages in thread

From: Tom Lane @ 2002-03-26 00:35 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; jack <datactrl@tpg.com.au>; pgsql-sql

Jan Wieck <janwieck@yahoo.com> writes:
>     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.

Actually, I don't think plpgsql should make any special discrimination
against temp tables; it should be able to re-use a query plan for
exactly as long as the temp table exists.  But I agree that we need
a mechanism for tracking query-plan dependencies for this to happen.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-04-17 22:07     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Bruce Momjian @ 2002-04-17 22:07 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql


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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-04-18 16:43       ` Jan Wieck <janwieck@yahoo.com>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Richard Huxton <richardh@archonet.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Jan Wieck @ 2002-04-18 16:43 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; jack <datactrl@tpg.com.au>; pgsql-sql

Bruce Momjian wrote:
>
> Jan, instead of doing cache invalidation to fix temporary tables, can we
> disable cached plans for functions that use temporary tables?

    I  was  thinking  of  a different approach. Enhancing the SPI
    manager to detect if a plan uses  temporary  objects  and  to
    remember  the  original  querystring  in the SPI_plan. Having
    callbacks when temp object  beeing  destroyed  into  the  SPI
    manager,  causing  it to reparse and plan on the next call to
    SPI_execp() would do it for everything that uses SPI.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-04-18 17:22         ` Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 18:49           ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Bruce Momjian @ 2002-04-18 17:22 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql

Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Jan, instead of doing cache invalidation to fix temporary tables, can we
> > disable cached plans for functions that use temporary tables?
> 
>     I  was  thinking  of  a different approach. Enhancing the SPI
>     manager to detect if a plan uses  temporary  objects  and  to
>     remember  the  original  querystring  in the SPI_plan. Having
>     callbacks when temp object  beeing  destroyed  into  the  SPI
>     manager,  causing  it to reparse and plan on the next call to
>     SPI_execp() would do it for everything that uses SPI.

I was merely proposing that preventing caching of functions ueing temp
tables may be easier than trying to invalidation them on temp table
destruction.

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-04-18 18:49           ` Jan Wieck <janwieck@yahoo.com>
  2002-04-23 16:15             ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Jan Wieck @ 2002-04-18 18:49 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; jack <datactrl@tpg.com.au>; pgsql-sql

Bruce Momjian wrote:
> Jan Wieck wrote:
> > Bruce Momjian wrote:
> > >
> > > Jan, instead of doing cache invalidation to fix temporary tables, can we
> > > disable cached plans for functions that use temporary tables?
> >
> >     I  was  thinking  of  a different approach. Enhancing the SPI
> >     manager to detect if a plan uses  temporary  objects  and  to
> >     remember  the  original  querystring  in the SPI_plan. Having
> >     callbacks when temp object  beeing  destroyed  into  the  SPI
> >     manager,  causing  it to reparse and plan on the next call to
> >     SPI_execp() would do it for everything that uses SPI.
>
> I was merely proposing that preventing caching of functions ueing temp
> tables may be easier than trying to invalidation them on temp table
> destruction.

    It's neat to say "preventing caching of functions using ...",
    now tell in detail how you detect that a function "is"  using
    a  temp table?  No, I don't mean how "you" can detect it, how
    can the PL/pgSQL parser or executor detect it.  And  when  do
    you  detect  it?  Remember  that  PL/pgSQL  has  delayed  SPI
    preparation?

    Second, it doesn't really look smart to me to prevent  saving
    of  all  query  plans  just  because  one of them uses a temp
    table.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 18:49           ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-04-23 16:15             ` Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-23 16:31               ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Bruce Momjian @ 2002-04-23 16:15 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql

Jan Wieck wrote:
> Bruce Momjian wrote:
> > Jan Wieck wrote:
> > > Bruce Momjian wrote:
> > > >
> > > > Jan, instead of doing cache invalidation to fix temporary tables, can we
> > > > disable cached plans for functions that use temporary tables?
> > >
> > >     I  was  thinking  of  a different approach. Enhancing the SPI
> > >     manager to detect if a plan uses  temporary  objects  and  to
> > >     remember  the  original  querystring  in the SPI_plan. Having
> > >     callbacks when temp object  beeing  destroyed  into  the  SPI
> > >     manager,  causing  it to reparse and plan on the next call to
> > >     SPI_execp() would do it for everything that uses SPI.
> >
> > I was merely proposing that preventing caching of functions ueing temp
> > tables may be easier than trying to invalidation them on temp table
> > destruction.
> 
>     It's neat to say "preventing caching of functions using ...",
>     now tell in detail how you detect that a function "is"  using
>     a  temp table?  No, I don't mean how "you" can detect it, how
>     can the PL/pgSQL parser or executor detect it.  And  when  do
>     you  detect  it?  Remember  that  PL/pgSQL  has  delayed  SPI
>     preparation?
> 
>     Second, it doesn't really look smart to me to prevent  saving
>     of  all  query  plans  just  because  one of them uses a temp
>     table.

Well, I assume you could spin through the plan at save time and look at
every relation reference to see if it is a temp table.

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 18:49           ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-23 16:15             ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-04-23 16:31               ` Jan Wieck <janwieck@yahoo.com>
  2002-04-24 00:36                 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Jan Wieck @ 2002-04-23 16:31 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; jack <datactrl@tpg.com.au>; pgsql-sql

Bruce Momjian wrote:
> Jan Wieck wrote:
> > Bruce Momjian wrote:
> > > Jan Wieck wrote:
> > > > Bruce Momjian wrote:
> > > > >
> > > > > Jan, instead of doing cache invalidation to fix temporary tables, can we
> > > > > disable cached plans for functions that use temporary tables?
> > > >
> > > >     I  was  thinking  of  a different approach. Enhancing the SPI
> > > >     manager to detect if a plan uses  temporary  objects  and  to
> > > >     remember  the  original  querystring  in the SPI_plan. Having
> > > >     callbacks when temp object  beeing  destroyed  into  the  SPI
> > > >     manager,  causing  it to reparse and plan on the next call to
> > > >     SPI_execp() would do it for everything that uses SPI.
> > >
> > > I was merely proposing that preventing caching of functions ueing temp
> > > tables may be easier than trying to invalidation them on temp table
> > > destruction.
> >
> >     It's neat to say "preventing caching of functions using ...",
> >     now tell in detail how you detect that a function "is"  using
> >     a  temp table?  No, I don't mean how "you" can detect it, how
> >     can the PL/pgSQL parser or executor detect it.  And  when  do
> >     you  detect  it?  Remember  that  PL/pgSQL  has  delayed  SPI
> >     preparation?
> >
> >     Second, it doesn't really look smart to me to prevent  saving
> >     of  all  query  plans  just  because  one of them uses a temp
> >     table.
>
> Well, I assume you could spin through the plan at save time and look at
> every relation reference to see if it is a temp table.

Bruce,

    you are really good at making things sound simple by stopping
    half way through.

    Your  assumption  is  exactly  my  "detect  if  a  plan  uses
    temporary objects". No go on, what exactly are you doing with
    the information that the plan uses a temp table?

    And BTW, since you are the one who invented temp tables,  how
    does someone detect a temp table?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 18:49           ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-23 16:15             ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-23 16:31               ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-04-24 00:36                 ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 12+ messages in thread

From: Bruce Momjian @ 2002-04-24 00:36 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql

Jan Wieck wrote:
>     you are really good at making things sound simple by stopping
>     half way through.

Sure.  How do you think I got so far.  :-)


>     Your  assumption  is  exactly  my  "detect  if  a  plan  uses
>     temporary objects". No go on, what exactly are you doing with
>     the information that the plan uses a temp table?

If it uses a temp table, run the plan and discard it, don't save the
plan in the cache.

>     And BTW, since you are the one who invented temp tables,  how
>     does someone detect a temp table?

You have to call a macro on the relnames used in the plan.  Before
schemas, it was just a macro.   Now that Tom has them in schemas, I am
not sure how to detect them, but I assume they are in a separate schema.
Looks like he created FindTempRelations() that returns a list of temp
relations.

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: call the same pl/pgsql procedure twice in the same connection
  2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
  2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
  2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
  2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
@ 2002-04-18 17:22         ` Richard Huxton <richardh@archonet.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Richard Huxton @ 2002-04-18 17:22 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: pgsql-sql

On Thursday 18 April 2002 17:43, Jan Wieck wrote:
> Bruce Momjian wrote:
> > Jan, instead of doing cache invalidation to fix temporary tables, can we
> > disable cached plans for functions that use temporary tables?
>
>     I  was  thinking  of  a different approach. Enhancing the SPI
>     manager to detect if a plan uses  temporary  objects
...

Was there not some talk of tracking dependencies between all objects (to avoid 
the problems with dropping referenced tables/functions). If so, it sounds 
like this is part of that future things job.

- Richard Huxton



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2002-04-24 00:36 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-25 10:33 call the same pl/pgsql procedure twice in the same connection session jack <datactrl@tpg.com.au>
2002-03-25 21:06 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
2002-03-25 21:36   ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
2002-03-26 00:35     ` Re: call the same pl/pgsql procedure twice in the same connection Tom Lane <tgl@sss.pgh.pa.us>
2002-04-17 22:07     ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-18 16:43       ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-18 18:49           ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
2002-04-23 16:15             ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-23 16:31               ` Re: call the same pl/pgsql procedure twice in the same connection Jan Wieck <janwieck@yahoo.com>
2002-04-24 00:36                 ` Re: call the same pl/pgsql procedure twice in the same connection Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-18 17:22         ` Re: call the same pl/pgsql procedure twice in the same connection Richard Huxton <richardh@archonet.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