Thread: call the same pl/pgsql procedure twice in the same connection session
I have a function with pl/pgSQL such as... CREATE FUNCTION comp_userExp (TEXT) RETURNS INTEGER AS ' DECLAREstUserName TEXT; BEGINstUserName := 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
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, Pennsylvania19026
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 preparedplans 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
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
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
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 thatuses 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 #
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, Pennsylvania19026
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 usesa 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 #
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
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, Pennsylvania19026
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 doingwith 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 #
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, Pennsylvania19026