Ruby Forum Ruby on Rails > ActiveRecord doesn't find posgresql sequences

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

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

On 9/19/06, Yuri Leikind <yuri.leikind@gmail.com> 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.

jeremy

Hi,

Jeremy Kemper wrote:
>
>
> 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