Re: Select For Update and Left Outer Join

Okay, I finally had time to install a bunch of databases and see what
the "industry" thinks.

I tested the four seemingly most pertinent databases. The ability to
"select for update" with a left outer join is present on these
databases:
* Oracle
* SQL Server (not the same syntax, but does support the functionality)
* MySQL

It is not present in DB2, though I didn't check if it offered workarounds.

Oracle and SQL Server did not limit the creation of the row that would
fill the empty join.

The ability to lock on outer joins is quite useful. I've even been
contacted to ask if I was aware of any progress in this area.

So, given that the majority of the important databases support it, I
would propose that it would be a reasonable addition to PostgreSQL.

It seems there is a difference of opinion about the locking model, but
it seems that there are two possibilities:
1. Disallow it, hoping to save a naive user from an error.
2. Allow it, providing a significant piece of functionality.

I would argue that the ability to perform a significant function such
as this outweighs the needs of the naive user to be informed of his
potential errors.

Thanks for your consideration in this matter.

Patrick Earl