Newly assigned Sequence is not working

Question

In PostgreSQL, I created a new table and assigned a new sequence to the id column. If I insert a record from the PostgreSQL console it works but when I try to import a record from from Rails, it raises an exception that it is unable to find the associated sequence.

Here is the table:

\d+ user_messages;
                                                  Table "public.user_messages"
   Column    |            Type             |                         Modifiers                          | Storage  | Description 
-------------+-----------------------------+------------------------------------------------------------+----------+-------------
 id          | integer                     | not null default nextval('new_user_messages_id'::regclass) | plain    | 

But when I try to get the sequence with the SQL query which Rails uses, it returns NULL:

select pg_catalog.pg_get_serial_sequence('user_messages', 'id');
 pg_get_serial_sequence 
------------------------

(1 row)

The error being raised by Rails is:

UserMessage.import [UserMessage.new]
NoMethodError: undefined method `split' for nil:NilClass
    from /app/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:910:in `default_sequence_name'

This problem only occurs when I use the ActiveRecord extension for importing bulk records, single records get saved through ActiveRecord.

How do I fix it?

Answer 1

I think your problem is that you set all this up by hand rather than by using a serial column. When you use a serial column, PostgreSQL will create the sequence, set up the appropriate default value, and ensure that the sequence is owned by the table and column in question. From the fine manual:

pg_get_serial_sequence(table_name, column_name)
get name of the sequence that a serial or bigserial column uses

But you're not using serial or bigserial so pg_get_serial_sequence won't help.

You can remedy this by doing:

alter sequence new_user_messages_id owned by user_messages.id

I'm not sure if this is a complete solution and someone (hi Erwin) will probably fill in the missing bits.

You can save yourself some trouble here by using serial as the data type of your id column. That will create and hook up the sequence for you.

For example:

=> create sequence seq_test_id;
=> create table seq_test (id integer not null default nextval('seq_test_id'::regclass));
=> select pg_catalog.pg_get_serial_sequence('seq_test','id');
 pg_get_serial_sequence 
------------------------

(1 row)
=> alter sequence seq_test_id owned by seq_test.id;
=> select pg_catalog.pg_get_serial_sequence('seq_test','id');
 pg_get_serial_sequence 
------------------------
 public.seq_test_id
(1 row)