Thread: temporary tables, plpgsql and yes i bet this has been asked before
in a plpgsql function
create temporary table X
....
drop table X
then in another function
loop
call the other function
end loop
i get the problem that the second time the table is created (ie in the
second call) it is not visible
now yes i could declare the table in the calling funtion but then the
original function is less useful to me
any clues?
murray
Murray Prior Hobbs wrote: > > in a plpgsql function > > create temporary table X > > .... > > > drop table X > > > then in another function > > loop > > call the other function > > end loop > > > i get the problem that the second time the table is created (ie in the > second call) it is not visible > > now yes i could declare the table in the calling funtion but then the > original function is less useful to me Interesting. Can you recreate the problem in psql without the functions and show an example? I am confused how this could be failing. -- 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
Murray Prior Hobbs <murray@efone.com> writes:
> in a plpgsql function
> create temporary table X
> ....
> drop table X
You need to use EXECUTE for all accesses to the temp table, in order
to defeat plpgsql's attempts to cache plans for those queries.
regards, tom lane
Bruce Momjian wrote: > Murray Prior Hobbs wrote: > > > > in a plpgsql function > > > > create temporary table X > > > > .... > > > > > > drop table X > > > > > > then in another function > > > > loop > > > > call the other function > > > > end loop > > > > > > i get the problem that the second time the table is created (ie in the > > second call) it is not visible > > > > now yes i could declare the table in the calling funtion but then the > > original function is less useful to me > > Interesting. Can you recreate the problem in psql without the functions > and show an example? I am confused how this could be failing. I bet he's using the temp table without EXECUTE. That would've failed even without the other "calling" function around it, as soon as he uses it more than once per session. SPI still needs to learn how to determine if a query uses temp tables somewhere in order to silently re-preparequeries when required. And I am absolutely sure this is not an issue we should address in PL/pgSQL. It hasto be solved on the SPI level. 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
> I bet he's using the temp table without EXECUTE. That > would've failed even without the other "calling" function > around it, as soon as he uses it more than once per session. > > SPI still needs to learn how to determine if a query uses > temp tables somewhere in order to silently re-prepare queries > when required. And I am absolutely sure this is not an issue > we should address in PL/pgSQL. It has to be solved on the SPI > level. Will this still be an issue with schemas? I am thinking no. -- 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
Jan Wieck <janwieck@yahoo.com> writes:
> SPI still needs to learn how to determine if a query uses
> temp tables somewhere in order to silently re-prepare queries
> when required. And I am absolutely sure this is not an issue
> we should address in PL/pgSQL. It has to be solved on the SPI
> level.
Temp tables are the wrong way to think about it. *Any* invalidation
of schema data referred to in a query plan should cause replanning.
We have a TODO item covering this already, don't we?
regards, tom lane
Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > SPI still needs to learn how to determine if a query uses > > temp tables somewhere in order to silently re-prepare queries > > when required. And I am absolutely sure this is not an issue > > we should address in PL/pgSQL. It has to be solved on the SPI > > level. > > Temp tables are the wrong way to think about it. *Any* invalidation > of schema data referred to in a query plan should cause replanning. > We have a TODO item covering this already, don't we? Of course, still. Nothing changed on that since we discussed it the last time ;-P 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