Thread: Transaction Queries!!!
Hi,
We are using postgresql as the underlying RDBMS for one of our
application.
When in a transaction if we execute an query that causes database to
return
a failure. The whole transaction gets roll backed.
Below are 2 examples
which illustrate this problem. We want the rollback to be in control of
the user. And that is why as i understand we have the postgresql rollback
command.
Examples of implicit rollback:
1>
cpdb=# begin;
BEGIN
cpdb=# insert into abc ('80219');
ERROR: parser: parse error at or near "'"
cpdb=# insert into abc values ('80219');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
2>
cpdb=# begin;
BEGIN
cpdb=# insert into abc values ('-1');
ERROR: ExecAppend: rejected due to CHECK constraint abc_i
cpdb=# insert into abc values ('1');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
A another problem is of executing update query thru ODBC API's. When we
run update query thru executeQuery() API, even though the update query may
not go thru as some column condition may not have met or the where clause
may have been wrong. In such case also the API returns success.
When the query is executed thru psql prompt it says zero rows updated.
Has this problem got to do with postgresql or ODBC API?
kindly help!!!
TIA
regards
vatsal
We are using postgresql as the underlying RDBMS for one of our
application.
When in a transaction if we execute an query that causes database to
return
a failure. The whole transaction gets roll backed.
Below are 2 examples
which illustrate this problem. We want the rollback to be in control of
the user. And that is why as i understand we have the postgresql rollback
command.
Examples of implicit rollback:
1>
cpdb=# begin;
BEGIN
cpdb=# insert into abc ('80219');
ERROR: parser: parse error at or near "'"
cpdb=# insert into abc values ('80219');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
2>
cpdb=# begin;
BEGIN
cpdb=# insert into abc values ('-1');
ERROR: ExecAppend: rejected due to CHECK constraint abc_i
cpdb=# insert into abc values ('1');
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
cpdb=#
A another problem is of executing update query thru ODBC API's. When we
run update query thru executeQuery() API, even though the update query may
not go thru as some column condition may not have met or the where clause
may have been wrong. In such case also the API returns success.
When the query is executed thru psql prompt it says zero rows updated.
Has this problem got to do with postgresql or ODBC API?
kindly help!!!
TIA
regards
vatsal
**************************Disclaimer************************************ Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individualor entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *************************************************************************** |
On Tue, 14 Oct 2003, Vatsal wrote: > We are using postgresql as the underlying RDBMS for one of our > application. When in a transaction if we execute an query that causes > database to return a failure. The whole transaction gets roll backed. Yes, all errors are treated as unrecoverable, so you're forced to roll the transaction back. At some point nested transactions or savepoints will let you have more control. > A another problem is of executing update query thru ODBC API's. When we > run update query thru executeQuery() API, even though the update query > may > not go thru as some column condition may not have met or the where > clause > may have been wrong. In such case also the API returns success. > > When the query is executed thru psql prompt it says zero rows updated. > Has this problem got to do with postgresql or ODBC API? An update that updates no rows is still a success by our general definition.
Thanks for the quick reply,
could you please elaborate or give me some pointers on Nested Transactions or Savepoints( which you are talking about in your reply to the first problem)
thanks in advance,
regards
vatsal
On Tue, 2003-10-14 at 23:49, Stephan Szabo wrote:
could you please elaborate or give me some pointers on Nested Transactions or Savepoints( which you are talking about in your reply to the first problem)
thanks in advance,
regards
vatsal
On Tue, 2003-10-14 at 23:49, Stephan Szabo wrote:
On Tue, 14 Oct 2003, Vatsal wrote: > We are using postgresql as the underlying RDBMS for one of our > application. When in a transaction if we execute an query that causes > database to return a failure. The whole transaction gets roll backed. Yes, all errors are treated as unrecoverable, so you're forced to roll the transaction back. At some point nested transactions or savepoints will let you have more control. > A another problem is of executing update query thru ODBC API's. When we > run update query thru executeQuery() API, even though the update query > may > not go thru as some column condition may not have met or the where > clause > may have been wrong. In such case also the API returns success. > > When the query is executed thru psql prompt it says zero rows updated. > Has this problem got to do with postgresql or ODBC API? An update that updates no rows is still a success by our general definition.
| ______________ |
**************************Disclaimer************************************ Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individualor entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *************************************************************************** |
On Wed, Oct 15, 2003 at 10:37:27AM +0530, Vatsal wrote: > could you please elaborate or give me some pointers on Nested > Transactions or Savepoints( which you are talking about in your reply to > the first problem) Nested transactions are not implemented in Postgres, and won't be in the next release 7.4. Neither are savepoints. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Es filósofo el que disfruta con los enigmas" (G. Coli)
Thanks a lot guys for prompt and informative reply.
regards
vatsal
regards
vatsal
| _______________________________________ |
**************************Disclaimer************************************ Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individualor entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *************************************************************************** |
Hi,
I am back with one more query.
In my application, for notification purposes, i fire a trigger on table informing the user about the change in table,
this works fine for atomic operations but not fit for compound operations i.e. till the time a transaction successfully commits,
the trigger should not be fired or some mechanism should be there so that we can buffer up fired triggers and pass it to the user at one go,
so that it looks likes a part of a transaction.
in short, how to make triggers transaction aware?
i saw similar questions on hackers list but couldnt get satisfying reply,
please help
thanks in advance
regards
vatsal
I am back with one more query.
In my application, for notification purposes, i fire a trigger on table informing the user about the change in table,
this works fine for atomic operations but not fit for compound operations i.e. till the time a transaction successfully commits,
the trigger should not be fired or some mechanism should be there so that we can buffer up fired triggers and pass it to the user at one go,
so that it looks likes a part of a transaction.
in short, how to make triggers transaction aware?
i saw similar questions on hackers list but couldnt get satisfying reply,
please help
thanks in advance
regards
vatsal
**************************Disclaimer************************************ Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individualor entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *************************************************************************** |