Postgres Pro
Downloads
Home   >   mailing lists

Thread: Constraint problem

Constraint problem

From
Szmutku Zoltán
Date:
Hi everybody ,
 
I try using Postgre, but  I have some problems.
I create a constraint (  R1>=0 ), and after connect to server from VFP via ODBC .
In the client program I turn on the transactions . (  SQLSETPROP(nHandle,'Transactions',2)   )
 
When I run UPDATE statements one after the other , and one return false because of constraint ,
then the server rolling back all changing automatically.
I would like to: server ignore the bad statements (return false) and after I call rollback or commit manually....possible ?
 
Thanks your help ,
 
Zoltan 

Re: Constraint problem

From
Sean Davis
Date:
On Mar 22, 2005, at 7:26 AM, Szmutku Zoltán wrote:

> Hi everybody ,
>  
> I try using Postgre, but  I have some problems.
>  I create a constraint (  R1>=0 ), and after connect to server from
> VFP via ODBC .
> In the client program I turn on the transactions . ( 
> SQLSETPROP(nHandle,'Transactions',2)   )
>  
> When I run UPDATE statements one after the other , and one return
> false because of constraint ,
> then the server rolling back all changing automatically.
>  I would like to: server ignore the bad statements (return false) and
> after I call rollback or commit manually....possible ?
>

Within a transaction, if one statement fails, all changes will be
rolled back.  If you want to commit each statement that works, you can
commit (or rollback) after each statement.

Sean

Re: Constraint problem

From
Michael Fuhr
Date:
On Tue, Mar 22, 2005 at 07:41:50AM -0500, Sean Davis wrote:
>
> Within a transaction, if one statement fails, all changes will be
> rolled back.  If you want to commit each statement that works, you can
> commit (or rollback) after each statement.

...or use savepoints (new in PostgreSQL 8.0).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Constraint problem

From
Tom Lane
Date:
Sean Davis <sdavis2@mail.nih.gov> writes:
> On Mar 22, 2005, at 7:26 AM, Szmutku Zolt�n wrote:
>> I would like to: server ignore�the bad statements (return false)�and
>> after I call rollback or commit manually....possible ?

> Within a transaction, if one statement fails, all changes will be
> rolled back.  If you want to commit each statement that works, you can
> commit (or rollback) after each statement.

In PG 8.0, you can recover from errors without having to commit the whole
transaction by using savepoints.  There isn't any way to do that pre-8.0.

            regards, tom lane