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

Re: Transaction Questions

From: Richard Kut <rkut(at)intelerad(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org, Mathew Kanner <Mathew(dot)Kanner(at)intelerad(dot)com>
Subject: Re: Transaction Questions
Date: 2006-02-24 17:23:22
Message-ID: 200602241223.22881.rkut@intelerad.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Tom!

	Thanks for the clarification. Maybe I am trying to use the wrong mechanism 
for what I need to do. Let me explain. 

	Our application will try to insert a record into a table, and if the row 
already exists, then that row will be updated instead. The application will 
do this for more than one record at a time. Here is some pseudo-code to show 
you what I mean:

BEGIN
  INSERT
    OR UPDATE
  INSERT 
     OR UPDATE
COMMIT

	Suppose the second INSERT fails with a duplicate key,  we cannot do the 
update (or get the previous INSERT) because the ROLLBACK is mandatory.With 
our previous database server (Sybase), the rollback is not mandatory but the 
choice is left to the application programmer.  We have considered using an 
existence test before the INSERT but that gives different semantics, as would 
some sort of UPSERT (insert/update combo statement)

So,
 - Is this mandatory rollback behavior configurable?  
 - If not,  what other programming paradigm is recommended instead?  



On Friday 24 February 2006 11:54, Tom Lane wrote:
> Richard Kut <rkut(at)intelerad(dot)com> writes:
> > 	Thanks for the quick response. However, the ROLLBACK that you see
> > occurred because I ended the transaction using END; and not because I
> > explicitly asked for a ROLLBACK.
>
> That is a ROLLBACK.  END means "COMMIT if transaction is OK, else
> ROLLBACK".
>
> 			regards, tom lane

-- 
Regards,

Richard Kut
Database Administrator
Research & Development
Intelerad Medical Systems Inc.
460 Ste-Catherine West, Suite 210
Montreal, Quebec, Canada H3B 1A7
Tel:     514.931.6222 x7733
Fax:     514.931.4653
rkut(at)intelerad(dot)com
www.intelerad.com

This email or any attachments may contain confidential or legally
privileged information intended for the sole use of the addressees. Any
use, redistribution, disclosure, or reproduction of this information,
except as intended, is prohibited. If you received this
email in error, please notify the sender and remove all copies of the
message, including any attachments.

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-02-24 17:43:10
Subject: Re: Transaction Questions
Previous:From: Tom LaneDate: 2006-02-24 16:54:38
Subject: Re: Transaction Questions

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