Hi everyone,
I am preparing to use Postgres in my app, found the following strange behavior:
---snip--- Sql.Sql db = Sql.Sql("pgsql://localhost", "test", "test", "test");
db->query("BEGIN"); db->query("INSERT INTO requests (SvTrId) VALUES ('lala')"); catch(db->query("INSERT INTO requests (SvTrId) VALUES ('lala')")); db->query("INSERT INTO requests (SvTrId) VALUES ('juju')"); db->query("ROLLBACK"); ---snip---
Database is opened, row with 'lala' is inserted, second query (obviously) fails as SvTrId is the primary key, but row with 'juju' is inserted *and* appears in the database - i.e. the transaction is *committed*!
Studying source code show, that once error is encounter, session is reset, i.e. running transaction is rolled back, everything is closed, temporary tables are deleted and session variables are reset as well, and after that, the next statement(s) will be committed immediately, as there is no active transaction.
In my opinion, this is not exactly correct behavior when statement fails, as session still may be needed (especially temporary tables, open cursors etc).
Im some cases, this is perfectly normal when SQL statement fails, especially in SERIALIZED transaction isolation level, and it would be more practical to leave the decision as of what to do to the application.
Yes, this is true that it is impossible to continue executing statements in failed transaction, but temporary tables etc. may still be needed.
Also, silent reconnection is not a good idea too - when there are many transactional statements are executed - it would be nice to have an option to modify this behavior.
Best regards, /Alexander
What people are using Postgres? I don't know who would look into this now when (apparently) srb has left the pike crowd.
But I agree that a failed statement shouldn't necessarily imply a rollback. Have you checked what the old postgres glue does?
I use postgres, but I don't do a lot of testing of it yet.
----- Original Message ----
From: "Martin Stjernholm, Roxen IS @ Pike developers forum" 10353@lyskom.lysator.liu.se To: pike-devel@lists.lysator.liu.se Sent: Sun, March 27, 2011 11:10:02 AM Subject: Sql.pgsql behavior in case of errors
What people are using Postgres? I don't know who would look into this now when (apparently) srb has left the pike crowd.
But I agree that a failed statement shouldn't necessarily imply a rollback. Have you checked what the old postgres glue does?
On Sun, Mar 27, 2011 at 03:10:02PM +0000, Martin Stjernholm, Roxen IS @ Pike developers forum wrote:
What people are using Postgres? I don't know who would look into this now when (apparently) srb has left the pike crowd.
Well, I need it - as I've old app which is still in heavy use, but MySQL is too slow (yes, it is).
But if the module is unmaintained, probably, I can play with it :)
But I agree that a failed statement shouldn't necessarily imply a rollback. Have you checked what the old postgres glue does?
Yes, I did - the behavior is exactly the same. Implicit reconnect and reset... This behavior is good when database is used only in simple store/retrieve mode.
Regards, /Al
Now, I'm not a Postgres user, so I might be totally wrong here, but I know I got bitten by the autocommit in MySQL once.
There isn't anything similar at work here?
aldem-pikedev@aldem.net wrote:
I am preparing to use Postgres in my app, found the following strange behavior:
---snip--- Sql.Sql db = Sql.Sql("pgsql://localhost", "test", "test", "test");
db->query("BEGIN"); db->query("INSERT INTO requests (SvTrId) VALUES ('lala')"); catch(db->query("INSERT INTO requests (SvTrId) VALUES ('lala')")); db->query("INSERT INTO requests (SvTrId) VALUES ('juju')"); db->query("ROLLBACK"); ---snip---
Database is opened, row with 'lala' is inserted, second query (obviously) fails as SvTrId is the primary key, but row with 'juju' is inserted *and* appears in the database - i.e. the transaction is *committed*!
Did anyone ever look into this? I was wading through some backlog mailinglist archives and came across this gem. I'm guessing nobody did, in which case I'll check and patch it.
Stephen R. van den Berg wrote:
aldem-pikedev@aldem.net wrote:
I am preparing to use Postgres in my app, found the following strange behavior:
---snip--- Sql.Sql db = Sql.Sql("pgsql://localhost", "test", "test", "test");
db->query("BEGIN"); db->query("INSERT INTO requests (SvTrId) VALUES ('lala')"); catch(db->query("INSERT INTO requests (SvTrId) VALUES ('lala')")); db->query("INSERT INTO requests (SvTrId) VALUES ('juju')"); db->query("ROLLBACK"); ---snip---
Database is opened, row with 'lala' is inserted, second query (obviously) fails as SvTrId is the primary key, but row with 'juju' is inserted *and* appears in the database - i.e. the transaction is *committed*!
Did anyone ever look into this? I was wading through some backlog mailinglist archives and came across this gem. I'm guessing nobody did, in which case I'll check and patch it.
Checking Pike 8.0, it works as god intended (i.e. cannot reproduce the problem). Backchecking the revision history, it seems like it should have worked since april 2009, which predates the original bugreport in 2011 by a large margin. If this problem still exists in one form or another please speak up.
pike-devel@lists.lysator.liu.se
-
aldem-pikedev＠aldem.net -
Lance Dillon -
Marcus Agehall (nu med K-m�rkt fastighet och ny elcentral) ＠ Pike (-) developers forum -
Martin Stjernholm, Roxen IS ＠ Pike developers forum
-
Stephen R. van den Berg