Thread: Suggestion
Hi All! I don't know if this is the right place to write. I was redirected here from the psycopg list (a Python PostgreSQL interface). They told me what I want is not to be done in the python extension lib because it is in the heart of PostgreSQL. Here is my problem. Every time an error occurs (e.g. I try to execute a SQL query and it fails) something happens to the backend. It is starting not to execute further commands in the same transaction. Why is that? I would like to decide if I want to continue my transaction or not. In my situation, I just can't tell if a command will fail or not. It would be too hard and too slow to determine if my command will fail or not. (Consider a difficult UPDATE command in a large database with many constraints -- you just cannot check if it will fail.) Hey, this is why exceptions was born! From the other side, I must be able to commit or rollback all my changes at the end of the block, depending on several things. I think this situlation is real and the problem is real. I had no such problems with FireBird, Oracle, MS SQL and SAP-DB. However, I still think PostgreSQL is the most advanced one. (Exception is Oracle which has a horrible price...) I'm not sure about this, but I think it would not be too hard to implement this as a setting. For example,. it could be an extension to 'SET SESSION CHARACTERISTICS'. I'm not a DB guru but I learnt DB implementation techniques and AFAIK there is no implementation barrier here. The backend could continue processing commands without big logical/concurrency/transaction handling problems. Can you help me please? Any developer out there who can explain this? Comments welcome. Cheers, G
Gandalf wrote: > > Hi All! > > I don't know if this is the right place to write. I was redirected here > from the psycopg list (a Python PostgreSQL interface). > They told me what I want is not to be done in the python extension lib > because it is in the heart of PostgreSQL. > > Here is my problem. Every time an error occurs (e.g. I try to execute a > SQL query and it fails) something happens to the backend. > It is starting not to execute further commands in the same transaction. > It is because the transaction has failed and thus rolledback. We do not support nested transactions. Sincerely, Joshua D. Drake > Why is that? I would like to decide if I want to continue my transaction > or not. In my situation, I just can't tell if a command > will fail or not. It would be too hard and too slow to determine if my > command will fail or not. (Consider a difficult UPDATE > command in a large database with many constraints -- you just cannot > check if it will fail.) Hey, this is why exceptions was > born! From the other side, I must be able to commit or rollback all my > changes at the end of the block, depending on several > things. I think this situlation is real and the problem is real. I had > no such problems with FireBird, Oracle, MS SQL and SAP-DB. However, I > still think PostgreSQL is the most advanced one. (Exception is Oracle > which has a horrible price...) > > I'm not sure about this, but I think it would not be too hard to > implement this as a setting. > For example,. it could be an extension to 'SET SESSION CHARACTERISTICS'. > I'm not a DB guru but I learnt DB implementation techniques and AFAIK > there is no implementation barrier here. > The backend could continue processing commands without big > logical/concurrency/transaction handling problems. > > Can you help me please? Any developer out there who can explain this? > Comments welcome. > > Cheers, > > G > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
>
>
>>
>> I don't know if this is the right place to write. I was redirected
>> here from the psycopg list (a Python PostgreSQL interface).
>> They told me what I want is not to be done in the python extension
>> lib because it is in the heart of PostgreSQL.
>>
>> Here is my problem. Every time an error occurs (e.g. I try to execute
>> a SQL query and it fails) something happens to the backend.
>> It is starting not to execute further commands in the same transaction.
>>
>
> It is because the transaction has failed and thus rolledback. We do
> not support nested transactions.
Sorry, this was not my question, I was not clear though. By the way, I'm
looking forward for nested transactions. I read about
WAL and I know that at some point we will have nested transactions in
PostgreSQL.
Consider this (where cmd2 is an atomic UPDATE but the others can be
complex):
try:
cmd1;
try:
cmd2;
failed = false;
except:
failed = true;
end
if failed then
cmd3;
else
cmd4;
commit;
except
rollback;
end
Is it really a nested transaction? There is only one COMMIT and one
ROLLBACK. You can try this in FireBird or MS SQL or Oracle.
They will gladly execute cmd3 or cmd4 and then commit all changes
(except the failed atomic cmd2). I personally know that the most
silly FireBird does not support nested transactions but it can do this.
This example is without executing CHECKPOINT and ROLLBACK TO
so I think it is not a real nested transaction problem. It is about not
rolling back the transaction automatically when a single command fails.
In fact, I wrote many graphical applications and all of them used this
feature without using real nested transactions.
Maybe I have a complete notion failure. :-)
Best,
G
On Thursday 11 March 2004 19:49, Gandalf wrote: > > It is because the transaction has failed and thus rolledback. We do > > not support nested transactions. > > Sorry, this was not my question, I was not clear though. By the way, I'm > looking forward for nested transactions. I read about > WAL and I know that at some point we will have nested transactions in > PostgreSQL. > > Consider this (where cmd2 is an atomic UPDATE but the others can be > complex): > > try: > cmd1; > try: > cmd2; > failed = false; > except: > failed = true; > end > if failed then > cmd3; This sort of thing will be implemented via nested transactions. Large areas of the PG code (so I have been told) assume that if they encounter a problem, they can just raise an error, stop and let the end-of-transaction code clean up behind them. It is the developers' opinion that nested transactions are the simplest/cleanest way of dealing with this. They also give you other benefits of course. If you are interested in the details, check the list archives - you're not the first to ask the question. Probably the general and hackers lists are the ones to look at. PS - this question is probably for -general rather than -advocacy, not that it's a big problem or anything. HTH -- Richard Huxton Archonet Ltd