Re: Hibernate / other ORM and pg_advisory_lock

I must admit that I wasn't even aware of the JPA lock, and it does
appears to be very restricted. Shows how much attention I've paid to
the API. When I have had to use locking through Hibernate, I have
usually even bypassed Hibernate and passed the isolation level lock hint
directly to the DB within the query as then I have finer control, but I
have so far usually had the "luxury" of working on applications that are
and forever will be tied to a particular DB, probably even fossilised to
a particular version. But thanks for the link.

You have to consider when using an ORM, why you would choose to do so,
which is mainly to do much of the boilerplate coding for you. I don't
buy into the argument that it gives you DB portability, as so far, when
using an ORM I have found myself more tightly bound to a DB dialect than
if I'd gone through JDBC directly. Using an ORM comes at a cost, and
I'm not just talking about performance overheads, as it attempts to map
object trees to relational data constructs and there is a pronounced
impedance mismatch between the two. If you start out with a fresh DB
and can design it with an ORM client in mind, single field surrogate
keys on all tables, version fields on tables likely to have multiple
users, choose data types that are only supported by the ORM and client
language, don't mind feeling compromised when doing the DB design, then
it fits beautifully. However, and this is where I begin to speak some
heresy... If you access the DB through SP's or functions, don't even
bother considering an ORM. Treat the DB as a service API. If you have
an existing DB that is not conducive to modifying, that contains mostly
natural keys, many of which are composite, then you can still use an
ORM, but you will be wrestling with it all the way. If the DB tables
have datatypes that are not standard in the JDBC or ORM, then you can
make it work, but again you are potentially faced with additional effort
above and beyond. Mileage will vary from project to project, but ORM's
are no panacea, they simply serve a purpose for some situations, and for
others you need to weigh up whether the cost is worth the returns.

So back to your problem and off my soap box, perhaps an ORM is not
necessarily the best fit for your solution?

Regardless of what locking mechanism you choose, optimistic locking or
pseudo-locking, at some point one user will be faced with stale data and
you still have to reconcile that data for the user. Any attempt to
check if the data is changed by someone else before any commit or to
check if another user also has the same set of data with the potential
to modify without a transactional lock would require some form of
sentinel pattern and polling of that sentinel, whether that sentinel is
the pg_advisory_lock, a flag in a dedicated table, or whatever else you
choose. And you are still faced it how to handle data that has changed
for the current user. Is there really a business case to set up a
polling of your DB by all of your DB clients, just for some slight
usability gains? Rhetorical question. For me to entertain such an
approach, I would personally want a pretty strong case for it.

Considering your enthusiasm of RAII, I bet you will be pleased with the
planned release of C++0x with the inclusion of smart pointers.
Completely off topic and at the risk of being chastised in this forum,
you may be interested in this interview with Bjarne Stroustoup:
http://www.ddj.com/cpp/207000124;jsessionid=4D4OJVJ3EPMOCQSNDLPSKH0CJUNN2JVN?_requestid=437406
Sadly, according to Stroustoup, C++0x will most likely be C++10, not
that I have used a ~ for many years.

Cheers,

Andy