pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcall 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>
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 21:06 Bruce Momjian <pgman@candle.pha.pa.us>
parent: jack <datactrl@tpg.com.au>
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 21:36 Jan Wieck <janwieck@yahoo.com>
parent: 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-26 00:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <janwieck@yahoo.com>
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-04-17 22:07 Bruce Momjian <pgman@candle.pha.pa.us>
parent: 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-04-18 16:43 Jan Wieck <janwieck@yahoo.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
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-04-18 17:22 Bruce Momjian <pgman@candle.pha.pa.us>
parent: 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-04-18 17:22 Richard Huxton <richardh@archonet.com>
parent: Jan Wieck <janwieck@yahoo.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
* Re: call the same pl/pgsql procedure twice in the same connection
@ 2002-04-18 18:49 Jan Wieck <janwieck@yahoo.com>
parent: 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-04-23 16:15 Bruce Momjian <pgman@candle.pha.pa.us>
parent: 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-04-23 16:31 Jan Wieck <janwieck@yahoo.com>
parent: 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-04-24 00:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jan Wieck <janwieck@yahoo.com>
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
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