Re: Transactional DDL

Ron Mayer schrieb:
> 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.

Yes - if you want to see transactional DDL, put your
function change in a transaction. If you do that, you
will see your long running other transaction is seeing
the old definition the whole time (or shortly after
you commit the function changing transaction). This is
basically "read committed".

Regards
Tino

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/