Hi -
I'm seeing a behavior with updatable cursors that matches neither the
behavior
of a sensitive cursor nor an insensitive one. In summary, I'm running with
serializable as the isolation level and rows updated within the same
transaction seem to disappear under the cursor.
>From the postgres documentation (I'm using 8.3.0), specifying FOR UPDATE
should
provide the client with a sensitive cursor: "If the cursor's query includes
FOR UPDATE or FOR SHARE, then returned rows are locked at the time they are
first fetched, in the same way as for a regular SELECT command with these
options. In addition, the returned rows will be the most up-to-date
versions;
therefore these options provide the equivalent of what the SQL standard
calls a
sensitive cursor."
But then I get this behavior:
{{{
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
begin;
BEGIN
show transaction isolation level;
transaction_isolation
-----------------------
serializable
(1 row)
create table foo (a bigint);
CREATE TABLE
insert into foo select generate_series(0, 9);
INSERT 0 10
select * from foo;
a
---
0
1
2
3
4
5
6
7
8
9
(10 rows)
declare c1 no scroll cursor for select * from foo for update;
DECLARE CURSOR
update foo set a=1000 where a>5;
UPDATE 4
fetch all from c1;
a
---
0
1
2
3
4
5
(6 rows)
select * from foo;
a
------
0
1
2
3
4
5
1000
1000
1000
1000
(10 rows)
abort;
ROLLBACK
}}}
Based on my interpretation of cursor sensitivity, I should:
* See rows 0 through 9 if the cursor is insensitive. In fact, this is what
I
get if I remove the FOR UPDATE option.
* See the same as a SELECT command executed within the same transaction if
the
cursor is sensitive.
This seems like a bug to me, and it prevents one from getting sensitive
cursors
with postgres. Can anybody explain the behavior above?
thanks a lot,
-daniel
