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

Hi all

I've been working in psql a lot recently, and have started to wonder why
statements with syntax errors or other problems that render them
unexecutable terminate the transaction.

I understand why statements that raise errors during their execution
terminate a transaction, and that explicit savepoints may be used if
this is undesired. That's all good, and I know that
ON_ERROR_ROLLBACK=interactive provides a helper for that in psql.

Savepoints are overhead, though, and I don't understand why they're
required for statements that don't even parse. If I typo a statement and
run:

    SELETC blah FROM blah;

why is a savepoint required to stop that from terminating the
transaction? I know psql isn't parsing and validating the statements so
bad statements still go to the backend, of course, but I don't get why
the backend can't recognise an unparseable statement or statement that
references non-existent database objects and report it without killing
the transaction if it's talking to psql interactively.

Is this just a "nobody's cared enough to implement it" thing, where it'd
be possible but the simplest/safest/easiest path is to have the backend
always kill the tx and nobody's wanted to add a communication channel to
let psql tell the backend it's working interactively?

--
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/

Craig Ringer <craig@postnewspapers.com.au> writes:
> I've been working in psql a lot recently, and have started to wonder why
> statements with syntax errors or other problems that render them
> unexecutable terminate the transaction.

Well, the obvious reason is that it's hard to tell what the user meant,
so bailing is the safest response.

> I understand why statements that raise errors during their execution
> terminate a transaction,

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.

            regards, tom lane

On Tue, Jun 19, 2012 at 02:20:57AM -0400, Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
> > I've been working in psql a lot recently, and have started to wonder why
> > statements with syntax errors or other problems that render them
> > unexecutable terminate the transaction.
>
> Well, the obvious reason is that it's hard to tell what the user meant,
> so bailing is the safest response.
>
> > I understand why statements that raise errors during their execution
> > terminate a transaction,
>
> 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.

+1.  I hate tools that try to read your mind.  They invariably fail
at that.  The current behaviour is 100% correct and unambiguous.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

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.

--
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/

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/
>

On Tue, Jun 19, 2012 at 03:35:19PM +0200, Rafal Pietrak wrote:
>
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity.

Good.  One looks forward to your fully-worked-out AI/ESP patch that gets
this right every time.  While you're at it, I suggest fixing these
"obvious" mistakes:

    SELECT SELECT 'text';
    SELECT 'text;
    SELECT INSERT 'text' INTO column;
    INSERT 'text' INTO 'column';

And so on.  Every one of these is a boiled down example of a stupid
think-o I have made more than once.  This is what the command buffer
is for.

If you really want your input system to provide fairly complete syntax
checking for you, however, I will point out that psql's \e command
will happily drop you into the editor of your choice.  If you want an
editor that knows more about what you want than you do, I think you
will find it is spelled "emacs".

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity.

I beg to disagree.  Typos can manifest themselves as execution errors
just as well as syntax errors.

You are probably thinking that we could behave differently if the error
was detected by the lexer, or perhaps the lexer + grammar, rather than
later on.  But those boundaries are purely implementation artifacts,
and the division of labor isn't always obvious, especially to people not
steeped in the innards of PG.  Users are going to be confused (and
unhappy) if some errors roll back their transaction while other
not-obviously-different ones don't.

As an example, suppose you fat-finger '-' for '=' in UPDATE:

    UPDATE tab SET col - 42 WHERE ...

This is going to draw a grammar error.  But make the same mistake
a few tokens later:

    UPDATE tab SET col = 42 WHERE key - 42;

and now you will get a pretty late-stage parse analysis failure,
since it'll bleat that the argument of WHERE isn't boolean.  Users
are definitely not going to understand why the former doesn't kill
their transaction but the latter does.  Or, if we solve that problem
by saying that no parse-analysis failure kills the transaction,
where does that stop?  The boundaries between parse analysis, planning,
and execution are even squishier and more arbitrary (from a naive user's
standpoint) than the ones earlier in the process.

            regards, tom lane

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/
>>
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


...
> ON_ERROR_ROLLBACK=interactive provides a helper for that in psql.
>
> Savepoints are overhead, though, and I don't understand why they're
> required for statements that don't even parse.

Other have handled the latter part of the above already (short version:
error is the only sane response to a non-parsing statement), but as
to the first part, the overhead is really not that high. Yes, psql
will create and remove a savepoint around each statement, but this is
a very lightweight action, especially if you are using psql in
interactive mode. In other words, we already have an elegant and
lightweight approach to the described problem.


- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201206191146
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk/gn30ACgkQvJuQZxSWSsgekgCfcoBq2VjCitjrpK9CrSMFob0Y
YF8An3Qp/OQjAcRsEBahE5OIbFzEEZX/
=hHAn
-----END PGP SIGNATURE-----

On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote:
[----------------]
> > >
> >
> >
> I would be be extremely concerned about any move to allow syntax
> errors not to abort a transaction.

Me too. But it's a nuicence for interractive session when  transactions
breakes due to syntax error - still, may be as Rainer Pruy said earlier,
this may be a suggestion to maintainers of interactive tools.
>
> Even minor syntax errors may indicate that something much more serious
> is wrong.

No. We are talking about an interactive session - someone just have
misstyped something; it's a one time event.
>
> PL/1 was designed to tolerate various errors and guess what the
> programmer intended, it would make assumptions and act on them – a
> good way to hide serious programming errors.
>
> A language that is too forgiving encourages sloppy thinking.

This is "dangerous grounds" :) - without going too far I'd say, there is
also ADA (rigorious) and perl (sloopy). "statistically", anything I
installed, that's written in perl is ways more stable, then enything
else.

But I'd also say, that I prefere tools (programming languages, operating
systems, IDE, etc), that help me from makeing errors.
>
[-----------]
>
> I would far rather a compiler pull me up for minor violations, than an
> obvious error not picked up until I came to test the program. The
> compiler is not perfect and some errors will slip through. However,
> the sooner errors are detected, the less likely an error will cause
> bad problems in production.

On the other hand I find it more tedious then it pays off, when current
CC force me to explicitly typecast every pointer I write, because: "type
don't match". But that's not the point here.

The point is, that sometimes we need regorious, and sometimes we need
sloopy. Like, when we start a project, we need to "scetch", then we need
to "tighten the shoelaces". At least for me it works that way.

And we are talking about interractive psql breaking transaction because
of syntax error - almost always this is a one time typo. I'd prefere it
to be a bit more "sloopy", then deployed SQL application (e.g.
non-interactive session).
>
> The greater the size and complexity of code, the more important this
> all becomes. Mind you, even very simple SQL SELECT's might have
> results used to make critical business decisions - so even then,
> sloppy habits should be discouraged.

Hmmm, years ago I has told, that UNIX is sloopy (does not guarantee
anything to a process: neither time to dysk when writing, nor CPU time,
nor even IRQ response time), so it will not prevail. It did. And it runs
critical systems.

As postgres is my favourite database for its ease of use (to the point
where I dont try applications which only run on its closest
free-couterpart: mysql :), there is always room for improvements (my
personal wishlist for postgres is currently 11 points long and keeping
transaction on syntax errors is even beyond that list).

-R
>

I like the current behavior.  Having been pleasantly surprised that this is how
Pg operates, it is very helpful when I'm working on scripts or batches such as
for creating or populating schemas.  If it dies part way through, I know I can
just fix the problem and rerun the whole thing, without having to first undo or
skip the earlier portions.  Also, rollback for everything is much more
deterministic. -- Darren Duncan

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/

Even minor syntax errors may indicate that something much more serious is wrong.

PL/1 was designed to tolerate various errors and guess what the programmer intended, it would make assumptions and act on them – a good way to hide serious programming errors.

A language that is too forgiving encourages sloppy thinking.

A bit like in chess, if you don't follow the dictum of 'touch a piece move it' in social play (it is the rule in match and tournament play), then your level of skill in Chess is unlikely to improve much. I coach Chess at my son's school and I used to be Director-of-Play for Chess tournaments.

I remember learning C many years ago, very unforgiving. However, the discipline imposed was very beneficial to improving my programming skills.

I would far rather a compiler pull me up for minor violations, than an obvious error not picked up until I came to test the program. The compiler is not perfect and some errors will slip through. However, the sooner errors are detected, the less likely an error will cause bad problems in production.

The greater the size and complexity of code, the more important this all becomes. Mind you, even very simple SQL SELECT's might have results used to make critical business decisions - so even then, sloppy habits should be discouraged.

I would be very reluctant to hire any developer who had the mind set of seriously wanting something like psql to be forgiving of any kind of error - as it suggests that they are more careless than normal, and lack the attitude to be reliably rigorous.

On 19 June 2012 22:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
>> The point is, that SQL syntax errors are so obviusly different from
>> execution errors, that noting this distinction should not raise any
>> ambiguity.
>
> I beg to disagree.  Typos can manifest themselves as execution errors
> just as well as syntax errors.

The arguments for the current behaviour are clear and rational. I see
no challenge possible on that basis.

However, PostgreSQL is one of the only databases to behave in this
way. This causes some database applications to have subtle problems
when we migrate/port them to work with us. Some, though few, programs
actually rely on run-time errors in order to execute correctly. I
don't condone or encourage that but I do recognise that there is
substantial legacy code out there, and much of that needs to run on
multiple DBMS.

So it would be useful to have a non-default option of statement-level
abort for those cases, as an ease of use feature.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

It seems to me there is one very simple reason not to change current
behavior which those in favor are glossing over.

Most interactions with a database are not occurring over an interface
like psql with one person typing on one side and the db executing on
the other.    If that were the case I would understand the concern
that a typo should give the user an opportunity to pick up the
statement where he/she left off.

However most interactions with the database are purely through
intermediary software.  Adding a lot of "do what I mean" or "give me a
chance to retry that" adds a great deal of complexity to the job of
the software in trapping and handling errors.  It is far, far more
simple to say "syntax errors abort transactions" and leave it at that.
 I know as a developer I don't want that behavior to change.

I guess it seems to me that I would not object to a new option for
transaction behavior where one could do something like SET TRANSACTION
INTERACTIVE; and have no errors abort the transaction at all (explicit
commit or rollback required) but I would complain loudly if this were
to be the default, and I don't see a real need for it.

Best Wishes,
Chris Travers

On 20/06/2012 08:24, Chris Travers wrote:
> It seems to me there is one very simple reason not to change current
> behavior which those in favor are glossing over.
>
> Most interactions with a database are not occurring over an interface
> like psql with one person typing on one side and the db executing on
> the other.    If that were the case I would understand the concern
> that a typo should give the user an opportunity to pick up the
> statement where he/she left off.
>
> However most interactions with the database are purely through
> intermediary software.  Adding a lot of "do what I mean" or "give me a
> chance to retry that" adds a great deal of complexity to the job of
> the software in trapping and handling errors.  It is far, far more
> simple to say "syntax errors abort transactions" and leave it at that.
>  I know as a developer I don't want that behavior to change.
>
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.
>
> Best Wishes,
> Chris Travers
>

It would be very nice to turn this feature off completely as a property
of your session.

I generally see it as necessary to do everything inside a transaction
when working in the DB manually. It adds greater protection against
forgotten WHERE clauses etc.  I've seen too many DBs mashed because of a
careless typo. The current behavior encourages admins not to use
transactions because any error (typo or not) forces them to re-do all
their work so far or put in a lot of extra typing to wrap everything.


On the idea of different error behavior between bad syntax and pragmatics...

Splitting hairs between a syntax error and other errors is dangerous.
There are too many cases where the division can not be clear.  And any
implementation would find it difficult not to fall foul of the principle
of least astonishment.
http://en.wikipedia.org/wiki/Principle_of_least_astonishment

For example pg/plsql executing dynamic SQL.  An error may have been
caused by faulty arguments. However one of the arguments may have been a
SQL statement in part or full.  How should PostgreSQL behave? See the
argument as bad (data error) or the SQL it contains as a syntax error.
You can always find an answer to this that works, but will that answer
be obvious to every developer?

Regards

Phil

On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote:
[------------------]
>
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.

Awesome!

Or rather: "BEGIN [INTERACTIVE];" (mind the <TAB>) for a one shot
interaction.

-R

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Simon Riggs wrote:
> So it would be useful to have a non-default option of
> statement-level abort for those cases, as an ease of use feature.
                                               ^^^^^^^^^^^^^^^^^^^^
I think you misspelled "foot gun"

Chris Travers wrote:

> Most interactions with a database are not occurring over an interface
> like psql with one person typing on one side and the db executing on
> the other. If that were the case I would understand the concern
> that a typo should give the user an opportunity to pick up the
> statement where he/she left off.

Well, that's really up to the users/authors of other tools, if they
feel the need to scratch that itch.

> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.

I would object. That's a recipe for disaster, and goes against our
philosophy of being safe, careful, and correct.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201206200945
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk/h1BQACgkQvJuQZxSWSshZ6QCfaGG1y0d76aTMKiXTU8Gy8i2G
MjUAnAiAbf53qL3MOXUEiqKARhm2mezx
=wbJw
-----END PGP SIGNATURE-----

On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote:
> I guess it seems to me that I would not object to a new option for
> transaction behavior where one could do something like SET TRANSACTION
> INTERACTIVE; and have no errors abort the transaction at all (explicit
> commit or rollback required) but I would complain loudly if this were
> to be the default, and I don't see a real need for it.

It's already available in psql. See ON_ERROR_ROLLBACK:

http://www.postgresql.org/docs/9.2/static/app-psql.html

Regards,
    Jeff Davis

On 2012-06-19, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:

> And we are talking about interractive psql breaking transaction because
> of syntax error - almost always this is a one time typo. I'd prefere it
> to be a bit more "sloopy", then deployed SQL application (e.g.
> non-interactive session).

possibly you could program keyboard macros to handle savepoints to
have an easy way to recover from these errors, but if you're working on a
busy database keeping a transaction open whislt you think about syntax
is going to cost perfromance for the other users.


--
⚂⚃ 100% natural

On Sat, 2012-06-23 at 12:18 +0000, Jasen Betts wrote:
> On 2012-06-19, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>
> > And we are talking about interractive psql breaking transaction because
> > of syntax error - almost always this is a one time typo. I'd prefere it
> > to be a bit more "sloopy", then deployed SQL application (e.g.
> > non-interactive session).
>
> possibly you could program keyboard macros to handle savepoints to
> have an easy way to recover from these errors, but if you're working on a

Yes, but again. In my own psql usage, it goes like this: "this is a
simple and easy SQL, most of it was cut/paste anyway .... what could
possibly go wrong .... ups". But it goes wrong in less then every 20th
or 100th time, less then once in a few months. So i don't realy feel
like pushing somebody into a development effort, that woud just slightly
enhance psql comfort of usage. I most certainly want even be cooking any
macros, as .... I would forget to use it when it could be of some help.

My comment on this thread was mearly to object, that a request to allow
maintaining transaction state despite syntax error is "obviusly wrong".

> busy database keeping a transaction open whislt you think about syntax
> is going to cost perfromance for the other users.

And this is a really good point - although I do know my schemas and I
can choose appropriate moment for long hand-opened transation, mistakes
happen (well, this whole thread is about mistakes :)

Anyway, I personaly feel that psql would be more comfortable if one
could request "explicit rollback despite errors" (like by "BEGIN
INTERACTIVE").

-R