Ron Mayer schrieb:
Scott Marlowe wrote:
On 8/14/07, Harpreet Dhaliwal <harpreet.dhaliwal01@xxxxxxxxx> 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/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
