Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 67E149FB232 for ; Mon, 5 Mar 2007 11:15:08 -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 72876-06 for ; Mon, 5 Mar 2007 11:14:55 -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.242]) by postgresql.org (Postfix) with ESMTP id 7EDAC9FB225 for ; Mon, 5 Mar 2007 11:15:01 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c24so1354970ana for ; Mon, 05 Mar 2007 07:15:00 -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=b8dgZ+//l8NXpt3h1gIjGyWLGa0cOP3zIo/9phY8va7k/jTxFXjy68I3eVF2pqJDiY5d+QUYoibHO88w/McgB0jeF0aGUvK/TA0ZZtd7rcpmdbjPNUKIFUiEKBkVXEAvO01PBuGwUENi3RFiZYBAkIujJ+scFt8FpXoQykGavgg= 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=XcG4d4+Yp2KZLJPQMpEMC9TxxBg9+CPdNrq+S8ltJzbDSqGbg4vHg38eCjJyB7sYBQhZJFwgwgWnkN8RFXx6tnWvnCffD/hk38fWllttiOsir0yu99bOwTIp1upneqVC74ITxA8fGN10vV5HcAYQMoe/oSpQUnNB/tkLE22Q/Ds= Received: by 10.100.177.16 with SMTP id z16mr4308416ane.1173107699880; Mon, 05 Mar 2007 07:14:59 -0800 (PST) Received: by 10.100.165.1 with HTTP; Mon, 5 Mar 2007 07:14:59 -0800 (PST) Message-ID: Date: Mon, 5 Mar 2007 16:14:59 +0100 From: mikie To: pgsql-php@postgresql.org Subject: Re: transactions from PHP - double COMMIT required? In-Reply-To: <45EC13B1.3070601@tiggs.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> <45E81DC5.6090308@bugs.unl.edu.ar> <45EC13B1.3070601@tiggs.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/23 X-Sequence-Number: 3735 2007/3/5, Charley Tiggs : > mikie wrote: > > Perhaps I should ask again: is it my responsibility to check if the > > transaction failed and issue a ROLLBACK command, or will the PG server > > do it automatically? > > If it were me, I'd assume that responsibility as a matter of course. > Several folks here have given you names of abstraction layers that will > make that simple for you. By using ADOdb or PearDB or MDB, if the > transaction fails, it will rollback for you. If you're not going to use > one of those abstraction layers, assume that your request is going to > fail and capture the error and rollback yourself. At least then, you're > guaranteed a rollback no matter what you pass to the server. > > Try this: > > In a text file, enter all of your queries. Start with "BEGIN;" and end > with "COMMIT;". Be sure to include the error that you mentioned at the > appropriate place. > > Login to db using psql command line utility and issue the following command: > > \i /path/to/file_with_commands.txt > > On my system, when I encounter the error, it does not automatically > issue a rollback. I have to issue the rollback manually. I can see the ROLLBACK command displayed and it seems to do it automagically. > However, if I run the file as follows: > > psql mydb myusername -f /path/to/file_with_commands.txt > > The file will be processed to the end and a rollback will be issued. At > point of error, I start seeing entries about transaction aborted, > waiting til end of file to rollback. Here I can see that transaction is aborted and at the end a ROLLBACK is displayed, so it is also automagically issued It looks like there is no difference between these two methods that you suggested. Anyway I came to the solution like this: I send pg_query with "BEGIN; insert 1;insert 2; etc...;" -- here there is no commit nor rollback at the end. Now I check the result of this pg_query. If it is OK then I send single query "COMMIT;" and the case is closed (it means everything went OK). In case the result of pg_query gives FALSE I send another pg_query "ROLLBACK;". I checked the logs and the transaction ID is still the same when I send the other query with "rollback;" or "commit;" after checking the result. This seems to be the proper way of handling transaction in PHP without PDO. It has to be done manually.