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