Re: TODO items for window functions

On Mon, 2008-12-29 at 12:35 -0500, Tom Lane wrote:
> we could lock the rows. However, consider something like this:
>
> select x, lead(x) over() from table for update limit 1;
>
> Because of the LIMIT, we'd only lock the first-returned row ...
> but the values returned would also depend on the second row of the
> table, which wouldn't get locked. In general the results could
> depend on any or all rows of the table but we might lock only some.
> This seems to me to be at variance with how you'd expect SELECT FOR
> UPDATE to behave, so I'm inclined to leave the prohibition in there
> --- at least until someone comes up with a convincing use-case for
> SELECT FOR UPDATE together with a window function, and explains why
> he doesn't care about relevant rows possibly not getting locked.
>

How is that different from a subselect?

create table foo(a int, b int);
create table bar(c int, d int);

insert into foo values(1, 10);
insert into foo values(2, 20);
insert into bar values(100, 1000);

-- connection1
BEGIN;
select a, b, (select d from bar where c = 100) as d from foo
where a = 1 for update;

-- connection2
BEGIN;
select a, b, (select d from bar where c = 100) as d from foo
where a = 2 for update;

The single tuple in bar affects both results, but the second connection
is not blocked.

Regards,
Jeff Davis