Re: call the same pl/pgsql procedure twice in the same connection
От | Bruce Momjian |
---|---|
Тема | Re: call the same pl/pgsql procedure twice in the same connection |
Дата | |
Msg-id | 200204172207.g3HM74D09071@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: call the same pl/pgsql procedure twice in the same connection (Jan Wieck <janwieck@yahoo.com>) |
Ответы |
Re: call the same pl/pgsql procedure twice in the same connection
(Jan Wieck <janwieck@yahoo.com>)
|
Список | 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, Pennsylvania19026
В списке pgsql-sql по дате отправления: