Unexpected *ABORT STATE*
Seems that during an interactive transaction at the psql prompt
any syntax error will cause the following state to be entered
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
This is a bit annoying since it reared up near the end of a long and complex
data manipulation session. I don't want to be at all pushy (just about
anything else is probably higher priority) and stress that Its not a big
problem but I was wondering why this occurs, and if it is on the todo list to
fix.
here is a sample
gbtest=> begin;
BEGIN
gbtest=> select * fro pg_trigger;
ERROR: parser: parse error at or near "fro"
gbtest=> select * from pg_trigger;
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
*ABORT STATE*
gbtest=> rollback;
Mike.
===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com
Mike Finn wrote:
problem but I was wondering why this occurs, and if it is on the todo list to
fix.
It occurs because once an error happens all the changes that have been
made may not mean anything, so the transaction must be rolled back.
Maybe there is something that could be set to interactive programs like
psql can disable this? If not there should be, becuase I too have been
anoyed with a long interactive session having to be restarted because of
a typo.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman wrote:
Mike Finn wrote:
problem but I was wondering why this occurs, and if it is on the todo list to
fix.It occurs because once an error happens all the changes that have been
made may not mean anything, so the transaction must be rolled back.
Maybe there is something that could be set to interactive programs like
psql can disable this? If not there should be, becuase I too have been
anoyed with a long interactive session having to be restarted because of
a typo.
There is absolutely nothing that could be set to "disable" it
and continue the transaction. This is because the system has
no idea how to distinguish between a syntax typo and a
duplicate key error for example. Both are simply and ERROR.
Now for a typo it'd not be critical, but for a dupkey - er -
the heap typle is there, not all indexes might have been
processed and for sure the referential integrity constraint
triggers and other stuff haven't been run. That's an
inconsistent DB state, isn't it? Are you sure you want that?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
JanWieck wrote:
That's an inconsistent DB state, isn't it?
I agree that there is in general an inconsistent state. However, in the
special case of an interactive session It would be nice to have the option of
rolling back myself.
Are you sure you want that?
Not in general -- that would be very very bad. But in the special case...
well... yes.
My guess is that the underlying problem is that psql really doesn't know when
it is in an interactive session or not. The user could use an include \i
command at the prompt and would that then be interactive?
I conceed that on the whole it works properly and that protection of data
integrity should rule above all other concerns, especially ones of
convenience.
Sorry, for the noise.
Mike
===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com
Jan Wieck wrote:
Joseph Shraibman wrote:
Mike Finn wrote:
problem but I was wondering why this occurs, and if it is on the todo list to
fix.It occurs because once an error happens all the changes that have been
made may not mean anything, so the transaction must be rolled back.
Maybe there is something that could be set to interactive programs like
psql can disable this? If not there should be, becuase I too have been
anoyed with a long interactive session having to be restarted because of
a typo.There is absolutely nothing that could be set to "disable" it
and continue the transaction. This is because the system has
no idea how to distinguish between a syntax typo and a
duplicate key error for example. Both are simply and ERROR.
Now for a typo it'd not be critical, but for a dupkey - er -
the heap typle is there, not all indexes might have been
processed and for sure the referential integrity constraint
triggers and other stuff haven't been run. That's an
inconsistent DB state, isn't it? Are you sure you want that?
Then that command would fail and not alter the state of the db as seen
within the transaction block. The previous alterations withing the
transaction block would still be there, waiting to be commited.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
I think that for that to behave as you would like, each executed line would have
to be its own transaction nested inside the larger transaction that you
explicitly declared. An error on one line would cause an implicit rollback of
everything done as a result of executing that one line, success would implicitly
commit that line, although it wouldn't be fully committed until you reach the
end of the outer explicit block. Does that sound like the behaviour you want?
If so, then you'll need to wait at least until Postgresql supports nested
transactions before this would even be a possibility.
Joseph Shraibman <jks%selectacast.net@interlock.lexmark.com> on 07/31/2001
03:23:16 PM
To: Jan Wieck <JanWieck%yahoo.com@interlock.lexmark.com>
cc: Mike Finn <mike.finn%tacticalExecutive.com@interlock.lexmark.com>,
pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Unexpected *ABORT STATE*
Jan Wieck wrote:
Joseph Shraibman wrote:
Mike Finn wrote:
problem but I was wondering why this occurs, and if it is on the todo list
to
fix.
It occurs because once an error happens all the changes that have been
made may not mean anything, so the transaction must be rolled back.
Maybe there is something that could be set to interactive programs like
psql can disable this? If not there should be, becuase I too have been
anoyed with a long interactive session having to be restarted because of
a typo.There is absolutely nothing that could be set to "disable" it
and continue the transaction. This is because the system has
no idea how to distinguish between a syntax typo and a
duplicate key error for example. Both are simply and ERROR.
Now for a typo it'd not be critical, but for a dupkey - er -
the heap typle is there, not all indexes might have been
processed and for sure the referential integrity constraint
triggers and other stuff haven't been run. That's an
inconsistent DB state, isn't it? Are you sure you want that?
Then that command would fail and not alter the state of the db as seen
within the transaction block. The previous alterations withing the
transaction block would still be there, waiting to be commited.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Import Notes
Resolved by subject fallback
wsheldah@lexmark.com wrote:
I think that for that to behave as you would like, each executed line would have
to be its own transaction nested inside the larger transaction that you
explicitly declared. An error on one line would cause an implicit rollback of
everything done as a result of executing that one line, success would implicitly
commit that line, although it wouldn't be fully committed until you reach the
end of the outer explicit block. Does that sound like the behaviour you want?
Yes, that is what I was thinking. To a user it would seem like it
should be easy because we are used to using psql outside transactions
and if there is an error then the statement simply has no effect.
If so, then you'll need to wait at least until Postgresql supports nested
transactions before this would even be a possibility.
That's what I was afraid of. I wasn't sure how things were done on the
backend.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
Mike Finn <mike.finn@tacticalExecutive.com> writes:
My guess is that the underlying problem is that psql really doesn't
know when it is in an interactive session or not.
No, the real problem is that we have only one mechanism for recovering
to a valid state after an error, and that is transaction abort.
Distinguishing statement abort from transaction abort will require
a huge amount of work --- every transaction-or-longer-lifetime data
structure in the backend will need to be looked at, for example, to see
how it can be rolled back to the proper state after a statement abort.
It'll probably get done someday, but don't hold your breath ...
regards, tom lane