Feature discussion: Should syntax errors abort a transaction?

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

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

There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing...

Tom Lane <tgl@sss.pgh.pa.us> escreveu:

-----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 Tue, Jun 19, 2012 at 8:50 AM, Edson Richter <edsonrichter@hotmail.com> wrote:

There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing...

This this this, and again, this. Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line? Keep
going? Boom, data gone because of a syntax error.

And I will be pleased that data is gone! I really did not expect anything but this.
If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...).

Regards,

Edson

Scott Marlowe <scott.marlowe@gmail.com> escreveu:

On 20/06/12 01: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/

I would be be extremely concerned about any move to allow syntax errors
not to abort a transaction.

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

But that data was supposed to get transferred into another table
first! Data shouldn't just disappear like that. If you want that kind
of behaviour use a different db that likes to throw your data away
when it shouldn't.

On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

And I will be pleased that data is gone! I really did not expect anything but this.
If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...).

Regards,

Edson

Scott Marlowe <scott.marlowe@gmail.com> escreveu:

On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter <edsonrichter@hotmail.com> wrote:

There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing...

This this this, and again, this.  Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line?  Keep
going?  Boom, data gone because of a syntax error.

--
To understand recursion, one must first understand recursion.

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

According to documentation,

"TRUNCATEis transaction-safe with respect to the data in the tables: the
truncation will be safely rolled back if the surrounding transaction
does not commit."

You will find this description at following page:

http://www.postgresql.org/docs/9.0/static/sql-truncate.html

So, when you have the "syntax error" on second line, then transaction is
rolled back (cannot proceed: and that's why Syntax Errors should be
treated as any other error) and your data is safe.

Regards,

Edson Richter.

Em 19/06/2012 18:58, Scott Marlowe escreveu:

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

According to documentation,

"TRUNCATE is transaction-safe with respect to the data in the tables: the
truncation will be safely rolled back if the surrounding transaction does
not commit."

You will find this description at following page:

http://www.postgresql.org/docs/9.0/static/sql-truncate.html

So, when you have the "syntax error" on second line, then transaction is
rolled back (cannot proceed: and that's why Syntax Errors should be treated
as any other error) and your data is safe.

Yes but the discussion was that the syntax error SHOULDN'T cause a
roll back, and I was giving an example of when a transaction should
have rolled back but wouldn't have if syntax errors didn't cause
rollback.

In a different vein, the issue of "interactive" versus "scripted" is
something I don't want to take chances on getting wrong. If I'm in
the psql terminal and type \i /tmp/somesqlile.sql is that interactive
or scripted? How can psql know? Should it know? Can I trust it to
make the right decision of interactive versus scripted each time?

I generally put more than two lines of sql in a text file, edit it,
and throw at begin; on it. run it with \i and then commit or rollback
as needed. It documents what you did so you can check it in
somewhere, and makes it repeatable.

Em 19/06/2012 22:26, Scott Marlowe escreveu:

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

According to documentation,

"TRUNCATE is transaction-safe with respect to the data in the tables: the
truncation will be safely rolled back if the surrounding transaction does
not commit."

You will find this description at following page:

http://www.postgresql.org/docs/9.0/static/sql-truncate.html

So, when you have the "syntax error" on second line, then transaction is
rolled back (cannot proceed: and that's why Syntax Errors should be treated
as any other error) and your data is safe.

Yes but the discussion was that the syntax error SHOULDN'T cause a
roll back, and I was giving an example of when a transaction should
have rolled back but wouldn't have if syntax errors didn't cause
rollback.

In a different vein, the issue of "interactive" versus "scripted" is
something I don't want to take chances on getting wrong. If I'm in
the psql terminal and type \i /tmp/somesqlile.sql is that interactive
or scripted? How can psql know? Should it know? Can I trust it to
make the right decision of interactive versus scripted each time?

I generally put more than two lines of sql in a text file, edit it,
and throw at begin; on it. run it with \i and then commit or rollback
as needed. It documents what you did so you can check it in
somewhere, and makes it repeatable.

AFAIK, psql open one connection to database - and the transaction is
connection related (two different connections does not share a
transaction). I really mean AFAIK. At this point, someone else with more
internals knowledge can give some light here.

My argument was pro "syntax error should rollback" to make things
safe... :-). Assuming psql is working with only one connection, even in
interactive mode, the transaction should remains valid.

Regards,

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