Обсуждение: Transactions and "create or replace function"

I have a long query something like

   select slow_function(col) from large_table;

and half way through the query, in a separate connection, I

   CREATE OR REPLACE slow_function ....

I was surprised to see that some of the rows in my select
were processed by the old definition and some by the new.

I would have expected that since the CREATE OR REPLACE was
in a separate connection, and hense a separate transaction,
that all the results of the select() will have been processed
by the same function.


If it matters, it's postgresql 8.0.2;  the function was
actually a 3-step pl/perl function, where each step uses
spi_exec_query() to call the other steps.   Right now my
test case is large and ugly; but if this is unexpected
behavior I'm happy to make a smaller one that I can post here.

    Ron

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> I would have expected that since the CREATE OR REPLACE was
> in a separate connection, and hense a separate transaction,
> that all the results of the select() will have been processed
> by the same function.

System catalog accesses are generally done with SnapshotNow semantics
--- in particular, lookups for function definitions will be done that
way.  Although the PL languages all cache function definitions, they go
out of their way to verify that the cache is still up-to-date each time
it is used.  I don't recall the exact example that made us conclude we
had to act that way, but it's unlikely to be changed.

            regards, tom lane