Re: Help with transactions - Mailing list pgsql-general

On Fri, Mar 18, 2005 at 09:22:52AM -0500, Stephen Howie wrote:

> I have a java program that excepts print streams and inserts in into a
> spool table as a bytea.  This fires a pl/pgsql trigger that passes the
> bytea to a pl/perl function to process the bytea and spits the results
> as an array back.  It then proceeds to insert the data into multiple
> tables.  Problem is that two of the tables data is inserted into inside
> this transaction, one has a foreign key to the other.  As you can guess
> I get a foreign key violation because the transaction is not committed

A transaction doesn't need to be committed for operations to see
the effects of previous operations in the same transaction, but
there could be visibility problems related to what happens when.

Could you post the simplest self-contained example that demonstrates
the problem?  It'll be easier to understand the interactions if we
can see the exact code.  In simple tests I successfully did what
you describe, so apparently my experiment didn't duplicate what
you're doing.

What version of PostgreSQL are you using?

> and as far as I understand PostgreSQL does not support dirty reads or
> nested transactions.

You probably need neither.  PostgreSQL 8.0 supports nested transactions
in the form of savepoints.

> 1) what is there another way to handle this transaction that would
> resolve this violation without using dirty reads and

Let's see what you're doing before thinking dirty reads would be a
solution.  Or perhaps you're thinking about them in a slightly
different sense than transaction literature usually describes.

> 2) It looks like running the trigger after insert on a table does
> not run as a separate transaction.

Why would it?

> Is the insert to that table suppose to fail if the trigger fails?
> To me that defeats the purpose of having a trigger after insert.

An after trigger is about visibility.  Here's an excerpt from the
"Triggers" chapter in the documentation:

  Row after triggers are most sensibly used to propagate the updates
  to other tables, or make consistency checks against other tables.
  The reason for this division of labor is that an after trigger
  can be certain it is seeing the final value of the row, while a
  before trigger cannot; there might be other before triggers firing
  after it.

An operation ain't over 'til it's over: if an after trigger doesn't
like what it sees, it can still abort the operation by raising an
exception.  That doesn't defeat the purpose -- it's part of the
purpose.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/