A client installed an unpatched version II 9.2.1 (a64.lnx/103)NPTL and
when running a ABF/4GL app the following error appears :

"E_US1265 Database reorganization activity has rendered the query plan
invalid. Try again after the db activity is complete. In case of
dynamic SQL, the statement must be prepared again."

I' ve seen this error before though. Check "E_US1265 Database
reorganization error" post
http://bit.ly/jv9qlO
for reference, although that post's code is different from the current
code that generated the same error.

Turns out it was a bug since after installing patch 14065 the error
never appeared again

This is not a question on the bug per se but when an exception should
be considered fatal.
I inspected the code and there is no exception handling. i.e. there is
no
"inquire_ingres (h_errorno=errorno)"
to catch the error, thus the exception is unhandled, the code after it
is not interrupted and execution flows as normal

As to why there is no exception handling, I can only speculate that
the guy who wrote the code thought that it is just a Select query and
thus there is no need for error checking (??)

However this error is more subtle than it seems since the query plan
cannot be run to completion and returns an incomplete/partial result
set back
To make things even harder, in the first/old case, the user was
notified of the error with a pop-up fired from within the application
(since it is a Forms based one), but in this second/new case there was
no pop-up warning (I don't know why since there is no 'seterr'
installed to supress the warning) the only indication that an error
occurred was an entry in the error log. Furthermore in this second
case a report is called that does further calculations upon the
returned data and returns a smaller than expected sum/result back.
But as far as the end user was concerned everything run ok and the
reports was generated correctly

This whole story made me think if and when, an unhandled exception
should be considered fatal or not, apart from that not having
exception handling code is bad programming practice.

The sys admin manual says that "Fatal errors are those errors that
require correction before Ingres can proceed with the program. All
errors, including fatal errors, are recoverable."

What does that actually mean? a fatal error being recoverable ? so
what is the difference between a fatal and a non-fatal one ?

nikosv wrote:

That wording is obscure and unhelpful to say the least.

It seems to me that *run-time* errors divide into two groups: execution
errors and coding errors. Coding errors might be references to
non-existant tables, columns etc., maybe ambiguous updates, failed
foreign key references, data type mismatches, failed check constraints,
permission violations, and that sort of thing. That is, errors on the
part of the programmer or DBA that aren't going to "just go away" if
you try again.

Execution errors are transient: deadlocks, non-reproducible server bugs,
system crashes, and lock wait timeouts etc. In a sense these are fatal
to a query or a transaction or a session, but the intention is correct
and they will go away if you just try again (often enough).

Thinking about it strictly from the viewpoint you raise (the
application program) I guess I see errors as transient or permanent
rather than fatal or non-fatal.

Now, if you want to drive yourself totally nuts, consider a deadlock (a
transient error). Where do you retryt from? Really? Are you sure?
How do you know that? >:-)

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 12 2012.
See www.uk-iua.org.uk

Hi Roy,
it !

Isn't it good practice to include code in a loop, with an error check,
so that it can be retried a few times before giving up?

This should allow recovery from deadlock and is no big deal to retry for
serious errors.

On 01/07/2011 06:04, nikosv wrote:

On net wrote:

The idea of looping to recover is good. I've just got back from a day
of meetings so I don't really feel like writing a lot about this just
now. I'll come back to it tomorrow--promise.

But to pass the time, and because I started this by posing the problem
as a sort of puzzle, why is merely looping not guaranteed to recover
correctly even if the deadlock condition goes away? (Hint, this is a
lifecycle issue.)


--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 12 2012.
See www.uk-iua.org.uk

I guess so but it sounds overly complicated.
Like that you must wrap all code that touches the dbms in while loops
and gotos (seen that in ESQL code) while I would prefer to use
straight forward STH (you could combine try..catch with while loops
though)

or it could work if you are on a persistent / statefull connection but
what about when you are on a disconnected cache? i.e on a web based
application how long should the client wait before he gets the error
message?

It also masquerades the problem at the application layer rather than
resolving it at the data layer (for example avoid a deadlock by
reducing the isolation level or use MVCC, locking hints etc)

On 06/07/2011 05:52, nikosv wrote:
It's simply a while loop wrapper around a transaction. ("while
transaction is in error and retry count is less than X, retry transaction");

There is a good argument for removing all database interaction into a
single place and there generic transaction handling can be applied - all
SQL in one place, not spread around the world.

I think there should be an option to ban SQL interaction from all frame
scripts in OpenROAD..

The end user shouldn't be expected to wait longer depending on a
technology choice - any tolerance of latency should be the same,
regardless of whether it's a web application or any other form of
application.

For the purposes of transaction handling it doesn't matter whether the
session is persistent or not.

There is always a potential for deadlock or delay when
accessing/updating a resource used by multiple concurrent users. The
looping mechanism allows the software to retry a transaction, which
seems a perfectly reasonable thing to do. If deadlock occurs one session
is going to have to yield a resource for the other to continue. I don't
see this as masquerading at all.

If a while loop is not used how do you propose retrying a deadlocked
transaction?

On 05/07/2011 19:53, Roy Hann wrote:
To clever for me. Retrying a failed transaction, is retrying a failed
transaction. It's either a goer or not. Isn't it?

I'm not suggesting retrying parts of a transaction, though it may have
read like that.

On 06/07/2011 06:37, On net wrote:
Perhaps I should add that the intention is not to hide all errors from
the user, but simply to include tolerance for errors that may occur
during normal running. If a transaction is retried several times and
errors still occur, then of course the error needs to be acted upon.

Picking up on the notion that users should not be kept waiting
indefinitely, we might even include in the while loop for the
transaction a check on the elapsed time since the first attempt to run
the transaction - that way we might even stop attempting retries if they
are taking too long, and let the user know.

On net wrote:

OK, here's the problem.

In SQL, a transaction is started implicitly when you execute an
SQL statement while no transaction is in progress. Once the transaction
is started all subsequent SQL statements are implicitly part of that
transaction. In the event of a deadlock or other serialization
failure, or in the event of *any* error after you SET SESSION WITH
ON_ERROR = ROLLBACK TRANSACTION, all those statements are rolled back.

At the time you code the application you might know where you intended
the transaction to begin; that is, you might know there is no SQL
statement between the last COMMIT you coded and the beginning of your
loop body.

But over time there is a good chance that bits of SQL will get
inserted, externals will get invoked, and so you have to wonder if
your transactions still begin where they once did. What exactly got
rolled back? What guarantee do you have that you are looping over all
that needs to get redone?

There are several perfectly good ways around this problem, but my point
is that managing transaction boundaries is not as trivial as we are
usually encouraged to think.

A related and probably worse problem is knowing where to COMMIT. When
you commit you are warranting that the database is in a new consistent
state. That's fine if you know what is included in the transaction when
you commit, but in principle you don't know because you can't know.

BTW, Ingres provides the BEGIN TRANSACTION statement. This has two
great benefits: it shows exactly where you (the programmer) expect a
transaction to begin, which is useful for your successors, and more
importantly, it asserts there is no transaction in progress already. So
before entering your re-try loop, always execute a BEGIN TRANSACTION.
If you get an error when you do that you know some sloppy twit has left
a transaction open, which has done mysterious things that you shouldn't
be willing to commit.

Ooooh! Yuck, no. No I didn't assume you meant that.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 12 2012.
See www.uk-iua.org.uk