Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 96F2D9FB4FA for ; Thu, 1 Mar 2007 15:38:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 03511-05 for ; Thu, 1 Mar 2007 15:38:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.240]) by postgresql.org (Postfix) with ESMTP id 05ACE9FB55C for ; Thu, 1 Mar 2007 15:38:16 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c24so451200ana for ; Thu, 01 Mar 2007 11:38:16 -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:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=RA5r0c7wzpJR93zifhx0w6oO1eIyI8SvOxla3KXE5SstBK2s6SpUveh9xFGXjAz6aK9vrrk1l9H6VEopbH2ZS5rFFRSu6I+YatquznGXtpirdVDJY32R3EeV5SMlFGryQ2CyOl2fweZ9mYxhgFmpPRk3CjOaUaS9+cHqiQpQFZc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=L7Mps6DEN6CdQgKod3PI6DiU52NmKubqXi+Y4/WRaPLXya5q1DLVCvG3u3nsQrxLgldaDgVb3bkz5fxRV8YunGr4Kkb0X6wr/7pRGGh8yHccA90wXEMfP3mWtTcsTxzDCQ3rjJVda07ruzGIaq0V5P7s1tGxCZXJpLNOww+N8tk= Received: by 10.100.143.1 with SMTP id q1mr660653and.1172777896185; Thu, 01 Mar 2007 11:38:16 -0800 (PST) Received: by 10.100.165.1 with HTTP; Thu, 1 Mar 2007 11:38:16 -0800 (PST) Message-ID: Date: Thu, 1 Mar 2007 20:38:16 +0100 From: mikie To: pgsql-php@postgresql.org Subject: Re: transactions from PHP - double COMMIT required? In-Reply-To: <200703011353.07621.xzilla@users.sourceforge.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <45E70C9A.200@bugs.unl.edu.ar> <200703011353.07621.xzilla@users.sourceforge.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/18 X-Sequence-Number: 3730 2007/3/1, Robert Treat : > On Thursday 01 March 2007 12:25, Martin A. Marques wrote: > > mikie wrote: > > > 2007/3/1, Martin Marques : > > >> 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 ? > > >> > > >> I may not have gotten your question right, but with one commit (or end) > > >> is enough. Transaction gets closed (commited or rolled back), and you > > >> can start a new transaction with BEGIN. > > > > > > Thats exactly what I was thinking, too. But PHP does things in some > > > other way, I guess. > > > I am sending an entire sql transaction block to the PHP pg_query > > > command, so it starts with BEGIN and ends with COMMIT. Then I still > > > have to execute another pg_query with "commit;" only - thats why I am > > > asking here if maybe someone know what the problem is. > > > > It's possible that php is receiving an error back from one query and ignoring > the rest of the queries being sent (though that's not my recollection of how > it works, that's the behavior of what your describing, and you're using older > software aiui) > > > Try executing the query without the last commit, and afterwords execute > > another query with commit. > > > > I personaly don't like to mix diferent querys and comands in one > > pg_query() line. Also, I am more bound to using things like MDB2 or PDO, > > which makes life easier. > > > > BTW, if you have log_statments = 'all', could you see those logs to > > check if the commits are really getting to the server? > > > > Yeah, do the above recommendation to see if all of your statements are making > thier way into pg OK, I have checked the logs. I can see both COMMIT statements in the log - the first commit from my transaction and then the other that I send to workaround the problem.