ActiveRecord doesn't find posgresql sequences

rubyonrails-talk
1

Hello all,

I used to develop using Rais + Mysql, now I need to switch to Postgresql.

I can't figure out why ActiveRecord cannot find the sequence needed for incrementing the id of my table.

The name of the table is users, the name of the sequence is users_id_seq: (is this the orrect name? )

        CREATE TABLE users (             id int,             first_name varchar(50),             constraint users_pkey PRIMARY KEY (id)         );

        CREATE SEQUENCE users_id_seq             START WITH 1             INCREMENT BY 1             NO MAXVALUE             NO MINVALUE             CACHE 1;

Well, saving a new records fails:

ruby script\runner "a = User.new(:first_name => 'Jack') ; a.save"

c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.6/lib/commands/runner.rb:27: c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.4/lib/active_record/connection_adapters/abstract_adapter.rb:120:in `log': RuntimeError: ERROR C23502 Mnull value in column "id" violates not-null constraint FexecMain.c L1806 RExecConstraints: INSERT INTO users ("first_name") VALUES('Jack') (ActiveRecord::StatementInvalid)

Ok, well, then I set the sequence manually:

        class User < ActiveRecord::Base           set_sequence_name "users_id_seq"         end

It has no effect! It's rather annoying.

What am I missing here?

Best regards, Yuri

P.S. I am using postgres-pr (0.4.0) rails (1.1.6) activerecord (1.14.4) postgresql 8.1

2

Or am I supposed to use

    id int default nextval('users_id_seq') ?

Am I right?

I thought Postgres adaptor will handle it by itself, but I didn't find in in the source code.

Best regards Yuri

Yuri Leikind wrote:

3

id must have a default nextval. The postgres adapter will deduce it if you provide it.

Defining your tables with id serial primary key is the typical idiom.

jeremy

4

Hi,

Jeremy Kemper wrote:

> > Or am I supposed to use > > id int default nextval('users_id_seq') ? > > Am I right? > > I thought Postgres adaptor will handle it by itself, but I didn't find > in in the source code.

id must have a default nextval. The postgres adapter will deduce it if you provide it.

Defining your tables with   id serial primary key is the typical idiom.

Well, I read the source code of ActiveRecord::Base and the postgres-pr adaptor and found out that the deduction you are referring to works only for figuring out the id of the record just created:

      def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:         execute(sql, name)         table = sql.split(" ", 4)[2]         id_value || last_insert_id(table, sequence_name || default_sequence_name(table, pk))       end

Well, that's ok, I'm fine with the rdbms taking care of incrementing the pk, I just had false expectations about Active Record, because in some O/R Mappers I used in the past (OJB) you can configure it to take care of the PKs.

Well, problem's solved, no, there's no problem, I'm pretty happy with ActiveRecord.

Yuri