Re: Does psql use nested transactions?

On Wed, Aug 18, 2004 at 02:47:26PM +0800, Christopher Kings-Lynne wrote:
> >It is not just a typo that you make in the SQL command. You often need
> >to do
> >
> > insert into table which has primary key
> > if the insert failed, do update of the existing record
>
> Do the update first then the insert.

That can still fail as concurrent session might run the same series of
update (which affects 0 records) and insert (which will fail).

Anyway, this was just an example of ways of using the database server
to do part of the work -- letting the database server do the checks
for you, raise an exception for you which you (your application) can
test and happily ignore. It's not just the insert / update thing.
The same goes for foreign keys, checks, anything where you knowingly
run a statement which can fail, and you act based on the exception you
get, _continuing_ with your transaction. This way, large part of the
application logic is shifted to the server and to the database schema.
The client just tries if the command will run OK.

--
------------------------------------------------------------------------
Honza Pazdziora | adelton(at)fi(dot)muni(dot)cz | http://www.fi.muni.cz/~adelton/
.project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
Only self-confident people can be simple.