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@gmx.net       http://yi.org/peter-e/