Activerecord-import & serial column in PostgreSQL

Question

I am in the process of upgrading a Rails 2.3.4 project to Rails 3.1.1. The old version used ar-extensions to handle a data import. I pulled out ar-extensions and replaced it with activerecord-import, which I understand has exactly the same interfaces.

My code calls looks like this

Student.import(columns, values)

Both args are valid arrays holding the correct data, but I get a big fat error!

The error stack looks like this:

NoMethodError (You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.split):
  activerecord (3.1.1) lib/active_record/connection_adapters/postgresql_adapter.rb:828:in 'default_sequence_name'
  activerecord (3.1.1) lib/active_record/base.rb:647:in `reset_sequence_name'
  activerecord (3.1.1) lib/active_record/base.rb:643:in `sequence_name'
  activerecord-import (0.2.9) lib/activerecord-import/import.rb:203:in `import'

Looking through the code it seems as though Activerecord-import calls activerecord which in turn looks for the name and next value of the Postgres sequence.

So activerecord-import looks for the sequence_name lib/activerecord-import/import.rb:203

# Force the primary key col into the insert if it's not
# on the list and we are using a sequence and stuff a nil
# value for it into each row so the sequencer will fire later
-> if !column_names.include?(primary_key) && sequence_name && connection.prefetch_primary_key?
   column_names << primary_key
   array_of_attributes.each { |a| a << nil }
end

It calls active record ... lib/active_record/base.rb:647:in `reset_sequence_name'

# Lazy-set the sequence name to the connection's default. This method
# is only ever called once since set_sequence_name overrides it.
def sequence_name #:nodoc:
->  reset_sequence_name
end

def reset_sequence_name #:nodoc:
  -> default = connection.default_sequence_name(table_name, primary_key)
  set_sequence_name(default)
  default
end

The code errors when serial_sequence returns nil and default_sequence_name tries to split it.

lib/active_record/connection_adapters/postgresql_adapter.rb

# Returns the sequence name for a table's primary key or some other specified key.
def default_sequence_name(table_name, pk = nil) #:nodoc:
  -> serial_sequence(table_name, pk || 'id').split('.').last
rescue ActiveRecord::StatementInvalid
  "#{table_name}_#{pk || 'id'}_seq"
end

def serial_sequence(table, column)
  result = exec_query(<<-eosql, 'SCHEMA', [[nil, table], [nil, column]])
    SELECT pg_get_serial_sequence($1, $2)
  eosql
  result.rows.first.first
end

When I execute pg_get_serial_sequence() directly against the database I get no value returned:

SELECT pg_get_serial_sequence('student', 'id')

But I can see that in the database there is a sequence called student_id_seq

I am using the following versions of Ruby, rails PG etc..

• Rails 3.1.1
• Ruby 1.9.2
• Activerecord-import 0.2.9
• pg 0.12.2
• psql (9.0.5, server 9.1.3)

I have migrated the database from MySQL to PostgreSQL, I don't think this has any bearing on the problem but I thought that I'd better add it for completeness.

I can't work out why this isn't working!

Answer 1

Summary of your description:

• The table student exists.
• The column id exists.
• The sequence student_id_seq exists.
• pg_get_serial_sequence('student', 'id') still returns NULL.

Two possible explanations:

1) The sequence is not linked to the column.

Column default and the tie between column and sequence are independent features. The mere existence of a fitting sequence does not mean it does what you presume. If you create a column as serial you get the whole package, though. Read the details in the manual.

To fix this (and if you are sure that's how it should be), you can mark the sequence as "owned by" student.id:

ALTER SEQUENCE student_id_seq OWNED BY student.id;

Also check if the column default is set as expected:

SELECT column_name, column_default
FROM   information_schema.columns
WHERE  table_name = 'student'
-- AND    schema = 'your_schema' -- if needed

If not, repair:

ALTER TABLE student ALTER COLUMN id SET DEFAULT nextval('student.id')

2) A mixup of host address / port / database / schema / capitalization of the table name.

It happens all the time. Make sure you check the same database that your app connects to, With the same user or at least the same search_path. Make sure, the objects are in the schema where you expect them and there isn't, for instance, another student table in another schema that got mixed up.