Re: Transaction Exception Question

Thanks Andrew for your reply.

You confused me at first.  I guess the second paragraph was on my issue about 
FK triggers using write locks on parent tables.

Your right in that I may need a work around for transactions being forced to 
rollback on exception.  Savepoints may indeed be the answer I am looking for. 
 Although I would like to see them implemented internally in the DB so that 
the transaction automatically goes back to the point just before the 
exception.  I have already accepted the fact that the DB will be this way for 
a while.  The purpose of the original e-mail was to see if things were this 
way for technical reasons (which would mean this could be added to the todo 
list) or for idealistic reasons.

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 customers will hit back on their 
browser and try to submit again).  I would illiminate a lot of contention if 
I could do the credit card authorization later and just cancel the order.

As for de-normalizing the DB.  Product is only one of the FK fields in 
contention.  There is also order status, carrier, carrier service, inv type, 
inv status, and others.  If I have to disable all the FK's to make things 
work, why did I insist in a DB with foreign keys in the first place?

I am rasing these issues because I think PostgreSQL can be a serious 
contender on high volume applications.  I just don't want to have to trade 
good DB and application design for speed.

On Wednesday 14 August 2002 11:12 am, Andrew Sullivan wrote:
> On Wed, Aug 14, 2002 at 08:50:32AM -0700, Jon Swinth wrote:
> > In the example I gave, the record is already there but the second client
> > cannot see it yet (not commited) so it attempts an insert too.  If the
> > first client is successful and commits then the second client will get an
> > SQL error on insert for duplicate key.  In Postgre currently this
> > required that the second client rollback everything in the transaction
> > when it would be a simple matter to catch the duplicate key error, select
> > back the record, and update it.
>
> Could you cache the locally-submitted bits from previously in the
> transaction, and then resubmit them as part of a new transaction?  I
> know that's not terribly efifcient, but if you _really_ need
> transactions running that long, it may be the only way until
> savepoints are added.
>
> I wonder, however, if this isn't one of those cases where proper
> theory-approved normalisation is the wrong way to go.  Maybe you need
> an order-submission queue table to keep contention low on the
> (products?  I think that was your example) table.
>
> A