Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Continue transactions after errors in psql

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Greg Sabino Mullane <greg(at)turnstep(dot)com>,PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Continue transactions after errors in psql
Date: 2005-04-25 21:28:26
Message-ID: 200504252128.j3PLSQs23644@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-04-25 21:52:49
Subject: Re: [PATCHES] Continue transactions after errors in psql
Previous:From: Tom LaneDate: 2005-04-25 21:10:55
Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?

pgsql-patches by date

Next:From: Bruce MomjianDate: 2005-04-25 21:52:49
Subject: Re: [PATCHES] Continue transactions after errors in psql
Previous:From: Joshua D. DrakeDate: 2005-04-25 20:08:57
Subject: Re: Constant WAL replay

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group