Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2ADB49FB3A2 for ; Thu, 1 Mar 2007 07:15:31 -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 97451-03 for ; Thu, 1 Mar 2007 07:15:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from rehuapila.uta.fi (rehuapila.uta.fi [153.1.1.45]) by postgresql.org (Postfix) with ESMTP id 02BBB9FB38A for ; Thu, 1 Mar 2007 07:15:26 -0400 (AST) Received: from valkoapila.uta.fi (valkoapila.uta.fi [153.1.1.42]) by rehuapila.uta.fi (8.13.8/8.13.8) with ESMTP id l21BFPx0029564 for ; Thu, 1 Mar 2007 13:15:25 +0200 (EET) Received: from apila.uta.fi (localhost.localdomain [127.0.0.1]) by valkoapila.uta.fi (8.13.8/8.13.8) with ESMTP id l21BFOo9031265 for ; Thu, 1 Mar 2007 13:15:24 +0200 Received: from pippuri.uta.fi (pippuri.uta.fi [153.1.6.42]) by apila.uta.fi (8.13.8/8.13.8) with ESMTP id l21BFON5011575 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Thu, 1 Mar 2007 13:15:24 +0200 (EET) Received: from pippuri.uta.fi (localhost.localdomain [127.0.0.1]) by pippuri.uta.fi (8.13.4/8.13.4/Debian-3sarge3) with ESMTP id l21BFNCw024388 for ; Thu, 1 Mar 2007 13:15:23 +0200 Received: (from wwwroot@localhost) by pippuri.uta.fi (8.13.4/8.13.4/Submit) id l21BFNGs024387 for pgsql-php@postgresql.org; Thu, 1 Mar 2007 13:15:23 +0200 Received: from kielitranpc59.uta.fi (kielitranpc59.uta.fi [153.1.35.59]) by imp3.uta.fi (Horde MIME library) with HTTP; Thu, 01 Mar 2007 13:15:23 +0200 Message-ID: <20070301131523.fgfm83nb9y2kgo4c@imp3.uta.fi> Date: Thu, 01 Mar 2007 13:15:23 +0200 From: Mihail Mihailov To: pgsql-php@postgresql.org Subject: Re: transactions from PHP - double COMMIT required? References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; DelSp="Yes"; format="flowed" Content-Disposition: inline Content-Transfer-Encoding: quoted-printable User-Agent: Internet Messaging Program (IMP) H3 (4.1.4-cvs) X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-3.0 (apila.uta.fi [153.1.1.41]); Thu, 01 Mar 2007 13:15:24 +0200 (EET) X-Scanned-By: MIMEDefang 2.61 on 153.1.1.42 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/2 X-Sequence-Number: 3714 Hi, I think you should use ROLLBACK if the query cannot be executed. one can commit only results of the queries that work. Try ROLLBACK statement. Mihail Quoting mikie : > Hi, > > I have this problem - after sending a query from PHP script with > BEGIN-COMMIT transaction block that contains an error (e.g. I > intentionally insert wrong data into the field of "date" type) I get > an error (which is OK):ERROR: date/time field value out of range. > > Now, when I try to DELETE or perform some other query later in the > same PHP script I receive this error: > ERROR: current transaction is aborted, commands ignored until end of > transaction block > > I am 100% sure that I have closed the transaction block with > "COMMIT;". When I do the same in the console (psql) I get something > like: > > BEGIN; INSERT INTO table1 (somecolumn) VALUES ('somedata'); =20 > INSERTsdf; COMMIT; > > I get this reply > BEGIN > INSERT 180610 1 > ERROR: syntax error at or near "INSERTsdf" at character 1 > ROLLBACK > > Everything seems to be logical and works fine. So why this does not > work from PHP? I figured one thing to workaround my problem: I am > sending the query like this: > BEGIN; > (here are few insert queries, one is syntax error); > COMMIT; > Then I check the pg_result of this query in PHP, and if it is FALSE I > have to send another simple query : "COMMIT;" - yes I know this is > second commit already. But it works this way! > It seems like if PHP ignored my COMMIT command from the transaction query. > I also tried sending the transaction query like this: > BEGIN; > some queries; > COMMIT;COMMIT; > but this is not working. I need to send one transaction query, check > if the result is false, then send another COMMIT. > > Any ideas where I got something missed? > > Regards, > Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match --=20 Mihail Mihailov, lehtori K=C3=A4=C3=A4nn=C3=B6stiede (ven=C3=A4j=C3=A4) Kieli- ja k=C3=A4=C3=A4nn=C3=B6stieteiden laitos 33014 Tampereen yliopisto puh. (03) 3551 6123 s=C3=A4hk=C3=B6posti: mihail.mihailov@uta.fi