Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8D1509FB756 for ; Thu, 1 Mar 2007 09:17:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 53627-01-9 for ; Thu, 1 Mar 2007 09:16:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.239]) by postgresql.org (Postfix) with ESMTP id 24FAD9FBCFD for ; Thu, 1 Mar 2007 09:16:57 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id i31so14083wra for ; Thu, 01 Mar 2007 05:16:56 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZXZdyt2LlbKGFchSg+Fc+DRZHF61VVVeF6LnB+gB5MhxlYZq3OWiczMaZryV07dZ7jauGsJ4sNhwGe6Smxc/fJhgsnO8YYN2jJrXE0vKJnOliafUx0xG35OdDEgfsaUhlB4P0zjcPhC9WCqbmzKDJ+ue5JF+XTuEz+R4/Ekgn/o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=X815L83DEgsIC+lLvaw/J8h9kB9gnl/4Ojdl8qWoQkoFhm7jfsdVp8oyYUPfop4a+LbFTlC6hwQiOIBozuwbSCmKwlQk85ei49Glx0KNT65wsS35gZIEQ94VxntQSL9XeQ+WIVMvPon/6QkHoqtcdmVGtbrwP46r9ZIqAABgu5M= Received: by 10.114.47.1 with SMTP id u1mr346517wau.1172755013492; Thu, 01 Mar 2007 05:16:53 -0800 (PST) Received: by 10.114.113.16 with HTTP; Thu, 1 Mar 2007 05:16:53 -0800 (PST) Message-ID: Date: Thu, 1 Mar 2007 08:16:53 -0500 From: "David Legault" To: mikie Subject: Re: transactions from PHP - double COMMIT required? Cc: pgsql-php@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_62985_23537945.1172755013434" References: <20070301131523.fgfm83nb9y2kgo4c@imp3.uta.fi> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/9 X-Sequence-Number: 3721 ------=_Part_62985_23537945.1172755013434 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Probably because the pgsql extension in PHP has an internal representation that doesn't fit with what the DB does (auto rollback on error). it keeps a state of transaction flag as active even though the DB rolled back when the error occured thus flagging an error when you want to start another one or execute another query. BEGIN Queries check for error (pg_last_error) after each query, if error, rollback and exit transaction "block" COMMIT (no errors found) The best would be to enclose the query in a function so that you can "exit" the block with a return statement or some similar construct. I can't remember if PHP supports block definition like in C { // group // of // calls // block } David On 3/1/07, mikie wrote: > > > > But getting back to my problem - perhaps there is something I > > > misunderstood: is it the client application responsibility to check if > > > the transaction failed or succeeded and issue COMMIT or ROLLBACK > > > accordingly (how do I close the transaction block in that case)? > > > Or is it the database server that is suppose to check if transaction > > > succeded and perform the query, or ROLLBACK if anything went wrong? > > > > PG will rollback all transactions that have an error in some part, as it > > can't commit the transaction as a whole. > > OK, so my way of programming seems correct. > Then why do I have to send another COMMIT after failed transaction to > continue with next queries in the same php script ? > > -- > Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ------=_Part_62985_23537945.1172755013434 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Probably because the pgsql extension in PHP has an internal representation that doesn't fit with what the DB does (auto rollback on error).

it keeps a state of transaction flag as active even though the DB rolled back when the error occured thus flagging an error when you want to start another one or execute another query.

BEGIN

Queries

check for error (pg_last_error) after each query, if error, rollback and exit transaction "block"

COMMIT (no errors found)

The best would be to enclose the query in a function so that you can "exit" the block with a return statement or some similar construct. I can't remember if PHP supports block definition like in C

{
 // group
 // of
 // calls
 // block
}

David

On 3/1/07, mikie <mikie.pl@gmail.com > wrote:
> > But getting back to my problem - perhaps there is something I
> > misunderstood: is it the client application responsibility to check if
> > the transaction failed or succeeded and issue COMMIT or ROLLBACK
> > accordingly (how do I close the transaction block in that case)?
> > Or is it the database server that is suppose to check if transaction
> > succeded and perform the query, or ROLLBACK if anything went wrong?
>
> PG will rollback all transactions that have an error in some part, as it
> can't commit the transaction as a whole.

OK, so my way of programming seems correct.
Then why do I have to send another COMMIT after failed transaction to
continue with next queries in the same php script ?

--
Mike

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_62985_23537945.1172755013434--