Re: Transaction Exception Question
От | Jon Swinth |
---|---|
Тема | Re: Transaction Exception Question |
Дата | |
Msg-id | 200208142056.g7EKuvM06765@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Transaction Exception Question (Andrew Sullivan <andrew@libertyrms.info>) |
Ответы |
Re: Transaction Exception Question
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Tino WildenhainДата:
Сообщение: Re: cvs postgresql current lacks 'ksqo' ? odbc/pgadmin