1

I had some ruby code in a ruby script that connected to a mysql DB. When I connected I then did a MySQL2.query("BEGIN") to start a transaction and if I wanted to rollback I did MySQL2.query("ROLLBACK") or if all was well and I wanted to commit I did MySQL2.query("COMMIT").

I have now moved to a postgres database and while the PG.exec("BEGIN"), PG.exec("ROLLBACK") and PG.exec("COMMIT") do not seem to error I do get a warning 'there is no transaction in progress' when I commit so it seems it is doing auto commit (i.e. committing each SQL INSERT/UPDATE as it is done). Basically I want to be able to manually rollback or commit.

I think maybe I need to turn autocommit off but cant work out how, I tried @dbase.exec('SET AUTOCOMMIT TO OFF') but got the error 'lib/database.rb:28:in `exec': ERROR: unrecognized configuration parameter "autocommit" (PG::UndefinedObject)'.

Ive done a fair amount of googling without any luck;(.

I am using postgres 9.5 and ruby 2.4.1.

Ben Edwards
  • 425
  • 5
  • 18

1 Answers1

2

PostgreSQL does not have a setting to disable autocommit:

https://stackoverflow.com/a/17936997/3323777

You just should use BEGIN/COMMIT/ROLLBACK. BTW which adapter gem do you use? PG.exec syntax seems strange. Consider the following snippet (pg 0.20 used): 

conn = PGconn.open(:dbname => 'database', user: "user", password: "...")

conn.exec("DELETE FROM setting_entries")
conn.exec("INSERT INTO setting_entries(name) VALUES ('1')")

conn.exec("BEGIN")
conn.exec("DELETE FROM setting_entries")
conn.exec("INSERT INTO setting_entries(name) VALUES ('1')")
conn.exec("ROLLBACK")

And this is the postgre log:

(0-22/70) 2017-08-31 12:37:12 MSK [19945-1] user@database LOG:  statement: DELETE FROM setting_entries
(0-22/71) 2017-08-31 12:37:12 MSK [19945-2] user@database LOG:  statement: INSERT INTO setting_entries(name) VALUES ('1')


(0-22/72) 2017-08-31 12:37:12 MSK [19945-3] user@database LOG:  statement: DELETE FROM setting_entries
(5948637-22/72) 2017-08-31 12:37:12 MSK [19945-4] user@database LOG:  statement: INSERT INTO setting_entries(name) VALUES ('1')

As you can see, the transaction ids are the same (/72) for the last two lines. To be sure you could write a unit test where you would make two updates within a transaction, rollback them and see if both would roll back.

eugene-nikolaev
  • 1,290
  • 1
  • 13
  • 21