I ran a simple select-for-update test on 8.1.2 and was curious as
to why the semantics are what they are. Specifically, when you
have multiple select-for-update-limit queries on the same rows,
why are rows selected by the blocked query before knowing if
some of those rows will be removed/eliminated by a preceding
select-for-update-limit?
Here's an example. I created this table:
create table foo(
id serial,
done boolean not null default false,
msg varchar);
Then I inserted some data:
select * from foo
id | done | msg
----+------+------------------------------
1 | f | Mon Aug 6 12:09:11 MDT 2007
2 | f | Mon Aug 6 12:09:12 MDT 2007
3 | f | Mon Aug 6 12:09:13 MDT 2007
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
(5 rows)
Then in transaction A,
begin;
select * from foo where not done for update limit 3;
id | done | msg
----+------+------------------------------
1 | f | Mon Aug 6 12:09:11 MDT 2007
2 | f | Mon Aug 6 12:09:12 MDT 2007
3 | f | Mon Aug 6 12:09:13 MDT 2007
(3 rows)
update foo set done = 't' where id < 4;
UPDATE 3
select * from foo;
id | done | msg
----+------+------------------------------
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
1 | t | Mon Aug 6 12:09:11 MDT 2007
2 | t | Mon Aug 6 12:09:12 MDT 2007
3 | t | Mon Aug 6 12:09:13 MDT 2007
(5 rows)
Then in transaction B, before committing transaction A,
begin;
select * from foo where not done for update limit 3;
(this blocks transaction B awaiting transaction A commit)
Then, just after commit in transaction A, I see the
previously-blocked query in transaction B returns:
select * from foo where not done for update limit 3;
id | done | msg
----+------+-----
(0 rows)
It returns zero rows when I expected it to return two (id 4 and
5). If I immediately run the same query again in transaction B,
I see what I expected to see in the preceding query:
select * from foo where not done for update limit 3;
id | done | msg
----+------+------------------------------
4 | f | Mon Aug 6 12:09:14 MDT 2007
5 | f | Mon Aug 6 12:09:15 MDT 2007
(2 rows)
So, B is selecting rows for update and applying the limit prior
to knowing which rows will be excluded by A's updates. I know
that is well-documented behavior. It just seems pretty
unintuitive. I'm just wondering if there is some good reason
for it.
TIA.
Ed
---------------------------(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
