ActiveRecord::StatementInvalid: PG InFailedSqlTransaction

Question

I am trying to create an ActiveRecord Object.But I'm getting this error while creating it.

(0.1ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR:  current transaction is       aborted, commands ignored until end of transaction block

Any ideas folks regarding the issue.

Effluent

Are you using PSQL with a column type of json? Update it to PSQL 9.4 and use jsonb. Problem solved! – Lapboard

postgresql.org/docs/9.1/static/transaction-iso.html this is the main issue, we had. – Effluent

Answer 1

None of the other answers fix the root cause of the issue.

The problem is that when Postgres raises an exception, it poisons future transactions on the same connection.

The fix is to rollback the offending transaction:

begin
  ActiveRecord...do something...
rescue Exception => e
  puts "SQL error in #{ __method__ }"
  ActiveRecord::Base.connection.execute 'ROLLBACK'

  raise e
end

See reference.

Ostrowski

Also mentioned in the Rails AR::Transactions manual – Brennan

Please don't rescue from Exception, as stated here: #10048673 Exception is the root of Ruby's exception hierarchy, so when you rescue Exception you rescue from everything, including subclasses such as SyntaxError, LoadError, and Interrupt. – Dropsonde

@Dropsonde It is perfectly fine to rescue Exception when you immediately re-raise; as is done in the given answer – Tori

Rollback using raise ActiveRecord::Rollback helped me. Also for reference api.rubyonrails.org/classes/ActiveRecord/Transactions/… – Evangelista

This feels like a bandage that will hide the true root cause. The real solution is to identify the exception and fix it – Underproduction

Answer 2

I had this issue. Just restart the Rails Server and it should work

Hurless

I just had this issue and restarting the server did fix it, in fact. Does somebody have an explanation for this behaviour? – Clang

I had the same issue on test environment (rspec throws the error). rake db:drop + rake db:create + rake db:migrate solved the issue. Maybe I messed up with migration. – Decastere

Was testing in rails console. Reloading was not sufficient, needed to restart the console. Thanks! – Unheard

This is not a viable solution, restarting a server isn't an option when a service is supposed to be running autonomously. The issue is that the PG connection is in a bad state and needs to be reconnected or needs to execute ROLLBACK to exit the current transaction. – Disregardful

This occurred to me after upgrading to PG12 and Ruby 2.6.6. I use rvm and the upgrade to 2.6.6 required a rebuild of the pg gem for the new gemset. A rake db:test:prepare did not work -- but a RAILS_ENV=test rake db:drop db:create db:migrate solved the problem. – Minetta

Concurring on the comments to reset the db. Once I did rake db:drop + rake db:create + rake db:migrate, solved the issue for me. – Maurinemaurise

Restarting rails server worked for me, found this other issue: https://mcmap.net/q/143204/-postgres-quot-error-cached-plan-must-not-change-result-type-quot which explains why – Muscadel

Answer 3

This issue was occurring in my test environment, and was caused by the fact that each test was wrapped in its own transaction.

I was using the database_cleaner gem, and have it configured so as NOT to wrap tests in a transaction if they use javascript. So to solve the issue, I added js: true to each spec that was causing this problem. (Even thought the specs did not actually use javascript, this was the most convenient way to ensure that the tests would not be wrapped in a transaction. I am sure there are less hack-ish ways of doing so, though).

For reference, here is the database_cleaner config from spec/support/database_cleaner.rb:

RSpec.configure do |config|

  config.before(:suite) do
    DatabaseCleaner.clean_with :deletion
  end

  config.before(:each) do
    DatabaseCleaner.strategy = :transaction
  end

  config.before(:each, :js => true) do
    DatabaseCleaner.strategy = :deletion
  end

  config.before(:each) do
    DatabaseCleaner.start
  end

  config.after(:each) do
    DatabaseCleaner.clean
  end

end

If you are not using database_cleaner, then probably the reason the tests would be wrapped in transactions would be that the use_transactional_fixtures option is set to true in spec/spec_helper.rb. Try setting it to false.

Electrolysis

If you disable transactional fixtures without plugging in something like DatabaseCleaner, then your test database will continue to pile up data from each run. Rather than setting js: true for each spec that doesn't work as expected, you can set it for integration specs. Keep in mind that using :deletion instead of :transaction is much slower, so transactions are preferable where feasible. – Vermin

To this point, I found the added complexity of DB cleaners as opposed to default transactional tests was tough (aka more errors popping up). But my issue was that I had two requests like get ... in the same it block. Moving it out fixed this error. – Queeniequeenly

I don't think adding js: true is the best way to fix this issue. It does not solve the problem and deletion strategy is very slow. I sped up my tests by a factor of 2.5x by switching to transaction strategy. See my answer below. – Ostrowski

This worked for me and allowed me to identify the actual issue. Once it was resolved I set use_transactional_fixtures back to true and others probably should too – Penman

Answer 4

you can see what really going on in postgresql log, I spend a lot of time to dig into this issue, and finally find out that we misuse upsert gem cause a PG error, only in postgresql log have the real info of what's going on

https://github.com/seamusabshere/upsert/issues/39

Northernmost

The link didn't help much but the idea of checking the postgres logs totally did. In my case it was an unprepared test database. Thanks! – Dungaree

Answer 5

I've run into this error when referencing a column in my specs that no longer exists. Make sure your database is up to date and your code doesn't expect a column that doesn't exist.

Vermin

Answer 6

Problem:

1. The program executes incorrect SQL statement. Incorrect SQL statement is root cause of the problem.
2. The program does not ROLLBACK or RELEASE SAVEPOINT immediately after incorrect SQL statement.
3. The program executes SQL statements after incorrect SQL statement.
4. PostgreSQL raises ERROR: Current transaction is aborted, commands ignored until end of transaction block

Solution:

Find incorrect SQL statement and correct it. If you don't want to correct the SQL statement, use ROLLBACK or RELEASE SAVEPOINT after incorrect SQL statement.

Abate

Answer 7

In my case the Postgres configuration at /usr/local/var/postgres/postgresql.conf had the datetype as the international format of dmy

Changing datetype to the American format of mdy fixed this issue for me.

Linter

Answer 8

In my case, I received this error simply because I had not rake'd my test db.

Victualer

Answer 9

Had similar problem after upgrading Rails from 4.2.2 to 4.2.5 I had to upgrade pg gem and problem start happening

9) WorkPolicy#is_publicly_viewable? is publicly visible hides work if deleted
     Failure/Error: before { DatabaseCleaner.clean_with :deletion }
     ActiveRecord::StatementInvalid:
       PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
       :             SELECT tablename
                   FROM pg_tables
                   WHERE schemaname = ANY (current_schemas(false))

Teddy Widom Answer is right in this sense, just to sum the problem:

Sometimes when you use DatabaseCleaner.clean_with :deletion you may be interfering PostgreSQL transaction.

So solution for me was to replace DatabaseCleaner.clean_with :deletion in parts of the tests where this was caused with DatabaseCleaner.clean_with :truncation

Just one more thing for googling people. If you are noticing this stack trace:

An error occurred in an `after(:context)` hook.
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "table_rows" does not exist
LINE 1: ...ion_schema.tables WHERE table_schema = 'test' AND table_rows...
^

...it may be caused by this problem

Christyna

Answer 10

I got that problem. And I found out that it was my query. It mean when I query with association without specifying a table column. ex:

class Holiday < ApplicationRecord
     belongs_to :company
end

class Company < ApplicationRecord
    has_many :timeoffs
end

In Holiday model I query

company.timeoffs.where("(start_date <= ? and end_date >= ?) and id != ?", begin_date, begin_date, 1)

The error occurs because I didn't specify which table's id It worked for me after I changed the code to

company.timeoffs.where("(start_date <= ? and end_date >= ?) and time_offs.id != ?", begin_date, begin_date, 1)

Paediatrician

Answer 11

To troubleshoot this, I ran

tail -f /usr/local/var/log/postgres.log

Then it is just a matter of replicating the error and watching the output in the log file.

Underproduction

Answer 12

Run this two command on your terminal:

$ rm /opt/homebrew/var/postgres/postmaster.pid  # (Here maybe have different path)
$ brew services restart postgresql

This is working from my side.

Larrikin