Re: [PATCHES] Continue transactions after errors in psql

I think everyone agrees this should only work in interactive mode. I
think the only unknown is if it should be 'on' by default in interactive
mode? Does it make sense to follow the standard in interactive mode if
we don't follow it in non-interative mode?

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 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