Re: Calling Java from psql (was Re: requesting help)

Tom Lane writes:

> I'm generally pretty suspicious of any system design that requires
> calling outside programs from an SQL function. The problem is that
> this fundamentally breaks transactional semantics: if the transaction
> is rolled back after the function call, its effects inside the database
> disappear ... but there's no way to roll back whatever the outside
> program did. Now you have a consistency problem.

The trick here is to organize your outside programs into fairly atomic
chunks and do conservative error logging. The need to synchronize the
non-database world with the database is definitely real. But usually a
regular function call near the end of the transaction block is much more
appropriate than a trigger function.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/