Re: Feature discussion: Should syntax errors abort a transaction?

Hm, sorry but I still can not get into that argument.

Take your example 3 (COMINT in place of COMMIT)

How should the DB know that (and how) to safely recover from such error?
You need to tell - and there are tools to do so right available.

In an interactive session:
- use "autocommit=on" to indicate that any statement surely will not
invalidate any previous one
  Then the "problem" is non-existent

- if you need transactional grouping of statements:
  you may envelope each statement with transactional sub structure (e.g.
SAVEPOINT....RELEASE)
  to indicate to the DB that only the inner most level of transaction is
at stake and
  the "environment" outside that statement may cope with errors.

  Agreed, this is "unexpected" if coming from a DB that treats syntax
errors differently.
  (May be sometimes there will be a mode with interactive tools that
provide such enveloping implicitly (if requested by user))

In a non-interactive session it is more obvious.
What should happen when after the failed "COMMIT" above the session is
to be terminated?
The pending transaction is to be terminated anyway.
Moreover, of a syntax error happens with a statement (e.g. some update)
and a later statement is assuming it had succeeded  and will ruin your
data if not,
would you still appreciate the DB to simply ignore the error (logging a
message of course) and
later on happily commit inconsistent data?
I'm sure, there will be loud outcry if such would be possible by mere
syntax error handling.

If your application is prepared to handle syntax errors during run, then
use available tools, if not
(and most application likely will not provide such logic), accept the
need for testing your applications.


Any reaction for a transactional system has to guarantee consistency
even for the price of convenience. Thus, convenience may cost some extra
effort.

At the end, I read the complaint as a suggestion to maintainers of
interactive tools
to build such interactive convenience into their tools.
But do not detect evidence for this to be a "feature" of the DB in the
first place.

Rainer
On 19.06.2012 15:35, Rafal Pietrak wrote:
> On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote:
>> On 06/19/2012 02:20 PM, Tom Lane wrote:
>>> So you're suggesting that "SELECT 1/0;" should terminate a transaction,
>>> but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
>>> squishy pretty fast when you try to decide which sorts of errors are
>>> more important than others.
>>>
>> When put that way, it seems blindingly obvious. You have a talent for
>> making a devastating point very succinctly.
> I'd humbly disagree.
>
> Not to drag this discussiong any further, just to make a point that the
> other approach is also "blindingly obvious". Only the other way around.
>
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity. In Tom's example "ROLBACK":
> 1. should not break the transaction
> 2. should only raise NOTICE: "syntax error"
> 2.1. in case this was issued from command line - user can always
> ROL<TAB> to see what's next.
> 2.2. in case of a compiled program sending a "ROLBACK" to the
> backend .... hack, the programmer should know better.
> 3. and BTW: what about rolling back a tediously cooked sequence of
> statements finished by "COMINT"?
>
> Things are not so obvious. And frankly, if not for the "<TAB>" I'd have
> case (3) so often, that it would have driven me crasy.
>
>
> -R
>
>> --
>> Craig Ringer
>>
>> POST Newspapers
>> 276 Onslow Rd, Shenton Park
>> Ph: 08 9381 3088     Fax: 08 9388 2258
>> ABN: 50 008 917 717
>> http://www.postnewspapers.com.au/
>>
>
>