Thread: 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?
http://www.postgresql.org/search.mpl
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