Re: "A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one"

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

xof@xxxxxxxxxxxx wrote:

Isn't one of the reasons for the savepoint (in fact, the principal reason) to reset the connection back to non-error state so that execution can continue? In that case, it really does need to create the savepoint at the start of the block, regardless of what's in it, since any statement can raise an error.

tgl@xxxxxxxxxxxxx wrote:

It also rests on the principle that the programmer shouldn't be too concerned about micro-efficiencies. You've given a perfectly good six-line implementation of what you want; use it and be happy.

...

[ shrug... ] We are not Oracle. One of the main ways in which we are not Oracle is that we support extensible database functionality. To write a "validate_conversion" function that supports extension datatypes, but doesn't use something morally equivalent to a subtransaction, would be a nightmare: large, fragile, and probably not all that much faster.

pavel.stehule@xxxxxxxxx wrote:

I remember a long and still not closed discussion about fault tolerant copy implementation. The problem is a lot of possibly redundant code for exception safe input functions, if I remember well. And it is not enough for fault tolerant copy still. Maybe it needs some refactoring of the PostgreSQL exceptions handling system to be able to handle some exceptions that come from a non-storage engine without the necessity to use safepoints. I have no idea if somebody is working on this issue now, but I don't expect so it is easy to fix it. Maybe a more probable fix can be to reduce an overhead of savepoints. This issue is more complex than can be visible from user perspective - and the complexity is based on how pg has implemented exceptions.

——————————

Thanks, Tom Lane, for your reply. The note in the doc:

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. 

uses the word "significantly". This scares the application programmer. Would you (all) consider this revised wording:

« If the executable section of a block that has an exception section makes changes to the database, then this brings significantly more expense than when there is no exception section. However, if the executable section makes only changes to local variables or session parameters, then the additional expense brought by an exception section is negligible. »

Oracle Database users had to wait about 40 years for the "validate_conversion()" built-in—despite strong and persistent user-pressure. PostreSQL is about 25 years old. So there's plenty of time to reconsider...

——————————

Thanks, Pavel Stehule, for your reply. Forget savepoints and blocks with exception sections. And forget the general case that Oracle's "validate_conversion()" handles.

It would seem to me that the code that implements "year_as_int := year_as_text" and, maybe, says "text into int won't go", is distinct from the code that it notifies to raise an error. It ought to be possible to factor out this code for re-use and use it both to do what it presently does and to implement a built-in "is_int_nn(t in text)". And maybe the same for numeric and for timestamp[tz]. These must be the common cases when such putative values come in from the UI. Proper practice insists on re-validating the conversions in the data base even though it's to be hoped that the UI will have done this.

However, in the light of Tom's « You've given a perfectly good six-line implementation of what you want; use it and be happy. », the need (almost) vanishes—except for the point that I mentioned earlier about packaging up the test in a the application development show's "utilities" kit.

——————————

Thanks, Christophe Pettus, for your reply.

it really does need to create the savepoint at the start of the block, regardless of what's in it, since any statement can raise an error.

This is necessary only when it can't be proved that the executable section that precedes the exception section cannot make database changes—hereinafter "is safe". Such safety tests are always over cautious. So, for example, it would be assumed that any transfer of control out of the executable section, and then back, was unsafe.

However, the experts have insisted that even the safety of this case cannot be proved:

> an executable section with only simple assignments that use only built-in functionality.

I suppose that "PL/pgSQL is an AST interpreter" is the clue here.

Given this, then yes, you're right.