[I previously posted this to pgsql-sql, but received no response as of
yet... it's more of a development team oriented message in any case.]
In ORMs like NHibernate, there are a few strategies for mapping
inheritance to SQL. One of these is "Joined Subclass," which allows
for the elimination of duplicate data and clean separation of class
contents.
With a class hierarchy such as this:
Pet
Dog : Pet
Cat : Pet
The query to get all the pets is as follows:
select * from Pet
left join Dog on Dog.Id = Pet.Id
left join Cat on Cat.Id = Pet.Id
Now suppose you want to lock to ensure that your Cat is not updated
concurrently. You add FOR UPDATE, but then PostgreSQL gets upset and
complains that locking on the nullable side of an outer join is not
allowed.
From our data model, we know that for every single Pet, there can
never be a Dog or Cat that spontaneously appears, so locking in this
case is totally safe. Unfortunately, PostgreSQL doesn't seem to
provide any mechanism to lock just the rows involved in this query.
Any advice? I'd be happy if such a thing was implemented in the
engine, as it's supported by other databases without trouble.
As another note, I'm one of the NHibernate developers and I'm working
to get all the NHibernate tests working with PostgreSQL. The two
significant cases I've had to avoid testing are the "FOR UPDATE"
mentioned above and null characters in UTF strings. Storing a UTF
"char" which defaults to zero doesn't work on PostgreSQL because it's
apparently still using zero-terminated string functions. :(
Aside from those two things, it looks like PostgreSQL is going to be
passing all the tests soon, so that's good news. :)
Patrick Earl
