Re: Transaction Exception Question - Mailing list pgsql-general

ah, now I understand where you got FK from.  The transaction exeception I run
into most often is caused by an Unique Key (not the PK either).  An insert
will block on UK violation when the existing record has been inserted from a
non-complete transaction.  The insert must block until the other transaction
is committed or rolled back.  A commit of the other transaction would result
in a UK violation SQL error and rollback.  A rollback of the other
transaction will result in the insert being successful.

As for your pending and posted idea, are you proposing to not have FK on the
pending table?  What do I do when the order fails an FK when moving from
pending to posted?  The whole point of the transaction is that when I am
done, everything is updated properly or nothing is updated.

Based on what I know of Postgre so far, there are two ways to solve the FK
lock issues.  Both require that the concept of read lock be added to the core
of postgre.  One way would be to keep the current trigger based FKs and add
to the allowed SQL a statement like SELECT ... FOR READ OF "table".  Another
would be to implement FKs as part of the schema definitions and do away with
the generated triggers, but that would probably be more work.  Some debate
may be necessary as to what a read lock constitutes.  For FK, a read lock
only needs to prevent the record from being deleted or the referencing key
changed.  Some would say that a read lock should block write locks but allow
other read locks.

On Wednesday 14 August 2002 01:26 pm, Andrew Sullivan wrote:
> On Wed, Aug 14, 2002 at 11:40:50AM -0700, Jon Swinth wrote:
> > As for the FK issue.  An order queue isn't feasable because of a
> > current requirement that the customer receive immediate feedback if
> > the credit card is declined and I can't contact to the credit card
> > company without a concrete order number (keeping in mind that some
>
> Oh, you can get that.  Think of a high-volume transaction system.
> You have a "pending" table and a "posted" table.  The order gets
> authorised, and the charges get approved, and the order number
> assigned when the order gets posted to the "pending" table.  A
> separate process moves orders from the "pending" to the "posted"
> table, and this latter action is what does updates, if any, to the
> "product" table.  Or is this not the problem?  I've only ever had
> writers block with FK constraints.  Maybe (probably) I'm
> misunderstanding the problem here, and your design is different from
> what I'm imagining.
>
> (On the general point that the FK implementation is less than
> optimal, I totally agree.  I just wish I had an idea on how to make
> it better.)
>
> A