Re: [HACKERS] Continue transactions after errors in psql

Bruce Momjian wrote:
> Greg Sabino Mullane wrote:
> > > The SQL-Standard itself says that errors inside transactions should only
> > > rollback the last statement, if possible. So why is that not implemented in
> > > PostgreSQL? What I read from past discussions here, is because it's just
> > > unsave and will lead to data-garbage if you aren't very careful.
> >
> > That's a good point: if that is indeed what the standard says, we should
> > probably see about following it. Rolling back to the last savepoint seems
> > a reasonable behavior to me.
>
> The question is what to make the default:
>
> o disable it by default for all sessions (current patch)
> o enable it by default only for interactive sessions, like AUTOCOMMIT
> o enable it by default for all sessions (breaks too many apps)
> o add a third mode called 'ttyonly' and figure out a default

Based on the comments I received, and the mention that ignoring errors
is part of the SQL standard, I chose the second option, patch attached:

$ psql test
Welcome to psql 8.1devel, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=> BEGIN;
BEGIN
test=> asdf;
ERROR: syntax error at or near "asdf" at character 1
LINE 1: asdf;
^
test=> SELECT 1;
?column?
----------
1
(1 row)

test=> COMMIT;
COMMIT

Can someone confirm that this is the way Oracle works as well? I
checked on IRC and isql does it. I am uncertain how applications
behave.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073