ActiveRecord::StatementInvalid: PG InFailedSqlTransaction
Ruby on-RailsPostgresqlActiverecordRollbackPgRuby on-Rails Problem Overview
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.
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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.
Solution 2 - Ruby on-Rails
I had this issue. Just restart the Rails Server and it should work
Solution 3 - Ruby on-Rails
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.
Solution 4 - Ruby on-Rails
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
Solution 5 - Ruby on-Rails
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.
Solution 6 - Ruby on-Rails
Problem:
- The program executes incorrect SQL statement. Incorrect SQL statement is root cause of the problem.
- The program does not ROLLBACK or RELEASE SAVEPOINT immediately after incorrect SQL statement.
- The program executes SQL statements after incorrect SQL statement.
- 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.
Solution 7 - Ruby on-Rails
In my case, I received this error simply because I had not rake'd my test db.
Solution 8 - Ruby on-Rails
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.
Solution 9 - Ruby on-Rails
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
Solution 10 - Ruby on-Rails
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)
Solution 11 - Ruby on-Rails
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.