Re: Transactional DDL

Scott Marlowe wrote:
> On 8/14/07, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)com> wrote:
>> Hi,
>> I read a few lines about SP compilation in postgres
>>
>> http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
>>
>> 1. stored procedure compilation is transactional.
>> "You can recompile a stored procedure on a live system, and only
>> transactions starting after that compilation will see the changes," he said.
>> "Transactions in process can complete with the old version. Oracle just
>> blocks on the busy procedure."

Really?

When I tried it [1] - changing a function definition during the
middle of a long-running-query that used the function gave
me the surprising result that some rows were processed using
the old definition of the function and some with the new one.

The explanation from Tom [2] was that there was some good
reason function lookups used SnapshotNow.

Ron

[1] http://archives.postgresql.org/pgsql-bugs/2005-04/msg00163.php

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.

[2] http://archives.postgresql.org/pgsql-bugs/2005-04/msg00179.php