Hi all,
A colleague pointed out to me today that the following is actually
possible on Oracle, MySQL, et al:
template1=# create table a (i int);
CREATE TABLE
template1=# create table b (i int);
CREATE TABLE
template1=# insert into a values(1);
INSERT 0 1
template1=# select * from a left outer join b on (a.i=b.i);i | i
---+---1 |
(1 row)
template1=# select * from a left outer join b on (a.i=b.i) for update of
b;
ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of
an outer join
The comment in initplan.c around line 325 is:
/* * Presently the executor cannot support FOR UPDATE/SHARE marking of * rels appearing on the
nullableside of an outer join. (It's * somewhat unclear what that would mean, anyway: what should we *
markwhen a result row is generated from no element of the * nullable relation?) So, complain if target rel is
FORUPDATE/SHARE. * It's sufficient to make this check once per rel, so do it only * if rel wasn't already
knownnullable. */
As I said, it seems that this is actually possible on other databases.
(MySQL might not be the best example: they seem to take a write lock on
the tables, not a row lock -- tested with Innodb [MyISAM silently ignores
the lock instructions]).
I looked to the spec for instruction on this matter and could find
nothing.
I think we could, in fact, lock rows on the nullable side of the join if
we say that locking the NULL rows is not necessary. The rows do not
physical exist and I could see an argument which says that those rows do
not match any other rows which a concurrent transactions if attempting to
modify -- since they don't exist.
Does anyone have any thoughts on this matter?
Thanks,
Gavin
