How to conditionally cancel a transaction using PG Ruby Gem

Question

I am currently taking a course in databases, and one of the laboratory questions has puzzled me how to implement the above, and indeed, if it was possible. I tried to find docs , but the specific transaction method is pretty vague.

This is the first time I have tried to do any database manipulation WITHOUT a comfortable Rails duvet, and so I got a little lost. I managed to create a connection to my postgresql database and follow the instructions, the last thing I need to do is roll back the transaction based on a simple condition.

Let me show you the code:

require 'pg' @conn = PG::Connection.open(:dbname => 'db_15_11_labs') @conn.prepare('insert', 'INSERT INTO house (housenumber, street) VALUES ($1, $2) returning id') @words = ["Foo", "Bar", "Bash", "Bang"] def populate 100.times.each_with_index do |i| # cycle through the @words array for street names, use i as house number ins = @conn.exec_prepared('insert', [i, "#{@words1[i % 4]} street"]) end end 

Basically, the condition is that if the returned id ( ins[0]['id'] ) is equal, the transaction will be rolled back. I suppose if ins[0]['id'] % 2 == 0 I need to make some kind of exception, but how to do it and more importantly, how to encode this information into the syntax specified in docs ?

Thank you for your time.

EDIT 1

Now I managed to get a syntax that will allow you to put conditions in a specific transaction as follows:

 @conn.transaction do |conn| ins = @conn.exec_prepared('insert', [i, "#{@words1[i % 100]} street"]) end 

and therefore this question really becomes more "how can I throw and catch an exception when ins[0]['id'] % 2 == 0

I tried to follow some simple guidelines for collecting exceptions, but by doing the following:

 throw :id_exception if (@ins[0]['id'].to_i % 2) == 0 catch :id_exception do puts "caught :id_exception #{}" end 

inside the transaction we get 'throw': uncaught throw :id_exception (ArgumentError)

Answer 1

Rollback transactions automatically if an exception occurs in the block. Therefore, you just need to create a small Exception class for yourself, raise one inside the block and then organize it so that your program does not exit due to the raised exception.

Try something like this:

 class MyLittleIdError < StandardError end begin @conn.transaction do |conn| ins = conn.exec_prepared('insert', [i, "#{@words1[i % 100]} street"]) raise MyLittleIdError if (ins[0]['id'].to_i % 2) == 0 end rescue MyLittleIdError puts "aha! we should have rolled back." end 

Answer 2

You can try wrapping your insert statement in ActiveRecord::Base.transaction , after you insert it, check the value ins[0]['id'] , and even if it is, throw a rollback exception ... Check the documentation for ActiveRecord::Base.transaction .

My only concern - I'm not sure if this will work with exec_prepared - I assume it will, but it's worth checking it out to confirm it.