Forum: Ruby on Rails RE: using postgres sequences on id columns

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F3b7b8756d0c7f71cc7460cc33aefaee?d=identicon&s=25 Daniel.Berger (Guest)
on 2005-11-12 12:49
(Received via mailing list)
> -----Original Message-----
> From: rails-bounces@lists.rubyonrails.org
> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
> Sent: Monday, November 07, 2005 5:02 PM
> To: rails@lists.rubyonrails.org
> Subject: [Rails] using postgres sequences on id columns

<snip>

> My question is: Do you have to use Serial types on id
> columns?  I would prefer to use a sequence so i can ensure
> that the ids of rows in several tables are entirely unique.

Then declare your serial types as primary keys.

Regards,

Dan
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Berger, Daniel wrote:
>>My question is: Do you have to use Serial types on id
>>columns?  I would prefer to use a sequence so i can ensure
>>that the ids of rows in several tables are entirely unique.
>
>
> Then declare your serial types as primary keys.

I'm pretty sure there is a problem with the way how RoR handles
sequences and pk's though, because I do have use sequences that are
named after the id column, but still some functionality is not
available. Please look at my bug report (apologies for the broken
markup):

http://dev.rubyonrails.org/ticket/2764

Cheers,

Jeroen
B84d42a3a5c343f8fc6ab7d7f47fd3f5?d=identicon&s=25 robby.lists (Guest)
on 2005-11-12 12:49
(Received via mailing list)
On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote:
> >
> available. Please look at my bug report (apologies for the broken markup):
>
> http://dev.rubyonrails.org/ticket/2764

There is no really good and efficient way to do this when you break the
default sequence naming conventions.

Active Record has this to help out:

http://api.rubyonrails.org/classes/ActiveRecord/Ba...

-Robby
B84d42a3a5c343f8fc6ab7d7f47fd3f5?d=identicon&s=25 robby.lists (Guest)
on 2005-11-12 12:49
(Received via mailing list)
On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote:
> > > <snip>
> > named after the id column, but still some functionality is not
> > available. Please look at my bug report (apologies for the broken markup):
> >
> > http://dev.rubyonrails.org/ticket/2764
>
> There is no really good and efficient way to do this when you break the
> default sequence naming conventions.
>
> Active Record has this to help out:
>
> http://api.rubyonrails.org/classes/ActiveRecord/Ba...

It appears that the documentation was not updated to reflect that this
works with PostgreSQL as well.

This was added in *1.12.0* (October 16th, 2005) (thanks to Rick Olson)

http://api.rubyonrails.org/files/vendor/rails/acti...

Cheers,

-Robby
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Robby Russell wrote:
>>>>>To: rails@lists.rubyonrails.org
>>>>Then declare your serial types as primary keys.
>>
> http://api.rubyonrails.org/files/vendor/rails/acti...
I certainly appreciate this, but I don't think you understand my
problem. I do *not* break the default sequence naming, I do everything
right (I think!), new records are inserting fine, yet the
connection.pk_and_sequence_for method doesn't return anything. I use
this in my tests and it's not working and that is my only problem.

Cheers,

Jeroen
A82ba1167f4d4a8d1de63820e576a87f?d=identicon&s=25 robby (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Jeroen Houben wrote:

>>>>>> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry
>>>>>> would prefer to use a sequence so i can ensure that the ids of
>>>> available. Please look at my bug report (apologies for the broken
>>
>
> I certainly appreciate this, but I don't think you understand my
> problem. I do *not* break the default sequence naming, I do everything
> right (I think!), new records are inserting fine, yet the
> connection.pk_and_sequence_for method doesn't return anything. I use
> this in my tests and it's not working and that is my only problem.
>
> Cheers,
>
> Jeroen

Can you show us what your CREATE TABLE statement looked like?

-Robby
27c57aaa4bda5ac8b0593659573b522f?d=identicon&s=25 blair (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Jeroen Houben wrote:
>>>>>> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Larry White
>>>>>> rows in several tables are entirely unique.
>>>> markup):
>>
> problem. I do *not* break the default sequence naming, I do everything
> right (I think!), new records are inserting fine, yet the
> connection.pk_and_sequence_for method doesn't return anything. I use
> this in my tests and it's not working and that is my only problem.
>
> Cheers,
>
> Jeroen

The pk_and_sequence_for function in 0.14.3 is broken.  It'll currently
only work
for Serial columns.  Try the new patch I just put up which works for me
when I
use the DEFAULT nextval('') syntax:

http://dev.rubyonrails.org/ticket/2764
http://dev.rubyonrails.com/attachment/ticket/2594/...

Please try the patch.  If it doesn't work for you, please amend the
ticket with
the exact DDL that doesn't work.

Regards,
Blair
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Robby Russell wrote:

>>>>>>> -----Original Message-----
>>>>>> <snip>
>>>>>
>>>>
>>> It appears that the documentation was not updated to reflect that this
>> problem. I do *not* break the default sequence naming, I do
>
CREATE TABLE players (
    id integer DEFAULT nextval('"players_id_seq"'::text) NOT NULL,
    positie integer,
    active boolean DEFAULT false NOT NULL,
    perm_level smallint DEFAULT 1 NOT NULL,
    email varchar(150) NOT NULL,
    fname varchar(60) NOT NULL,
    lname varchar(80) NOT NULL,
    ph_home varchar(13),
    ph_mob varchar(13),
    ph_work varchar(13),
    gender char(1) NOT NULL,
    dob date NOT NULL,
    passwd varchar(20) NOT NULL,
    token char(32),
    knltbnr bigint,
    activation_code char(32),
    account_approved boolean DEFAULT false NOT NULL,
    image boolean DEFAULT false NOT NULL,
    thumb_x integer,
    thumb_y integer,
    img_x integer,
    img_y integer,
    CONSTRAINT chk_unsigned CHECK ((id > 0))
);
HTH

Jeroen
A82ba1167f4d4a8d1de63820e576a87f?d=identicon&s=25 robby (Guest)
on 2005-11-12 12:49
(Received via mailing list)
On Tue, 2005-11-08 at 23:35 +0100, Jeroen Houben wrote:
>     lname varchar(80) NOT NULL,
>     image boolean DEFAULT false NOT NULL,
>     thumb_x integer,
>     thumb_y integer,
>     img_x integer,
>     img_y integer,
>     CONSTRAINT chk_unsigned CHECK ((id > 0))
> );

Where are you defining the PRIMARY KEY?

If this is a new app (perhaps I missed part of the thread), I would
*highly* suggest that you use the convention:

  id SERIAL PRIMARY KEY

Regards,

-Robby
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Blair Zajac wrote:

>>>>>>> -----Original Message-----
>>>>>> <snip>
>>>>>
>>>>
>>> It appears that the documentation was not updated to reflect that this
>> problem. I do *not* break the default sequence naming, I do
> only work for Serial columns.  Try the new patch I just put up which
> works for me when I use the DEFAULT nextval('') syntax:

The pk_and_sequence_for function in 0.14.3 was broken in 0.14.2 too.

What's the difference between SERIAL and other using in integer with a
sequence anyway? From what I understand SERIAL is merely a shorthand for
creating an integer column and a sequence that is the default value for
this int column. So affectvily both methods should achive exactlyh the
same. This assumption is backed by the pg docs:
http://www.postgresql.org/docs/8.1/interactive/dat...

So whichever method you pick, Rails should work as long as the name of
the sequence is recognized.

>
> http://dev.rubyonrails.org/ticket/2764
> 
http://dev.rubyonrails.com/attachment/ticket/2594/...
>
>
> Please try the patch.  If it doesn't work for you, please amend the
> ticket with the exact DDL that doesn't work.

I'll try the patch tomorrow when I'm back in the office, but here's my
DDL just in case:

CREATE TABLE players (
    id integer DEFAULT nextval('"players_id_seq"'::text) NOT NULL,
    positie integer,
    active boolean DEFAULT false NOT NULL,
    perm_level smallint DEFAULT 1 NOT NULL,
    email varchar(150) NOT NULL,
    fname varchar(60) NOT NULL,
    lname varchar(80) NOT NULL,
    ph_home varchar(13),
    ph_mob varchar(13),
    ph_work varchar(13),
    gender char(1) NOT NULL,
    dob date NOT NULL,
    passwd varchar(20) NOT NULL,
    token char(32),
    knltbnr bigint,
    activation_code char(32),
    account_approved boolean DEFAULT false NOT NULL,
    image boolean DEFAULT false NOT NULL,
    thumb_x integer,
    thumb_y integer,
    img_x integer,
    img_y integer,
    CONSTRAINT chk_unsigned CHECK ((id > 0))
);

Thanks for your help!

Jeroen
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Robby Russell wrote:

>>CREATE TABLE players (
>>    gender char(1) NOT NULL,
>>    img_y integer,
>  id SERIAL PRIMARY KEY
>
>
Sorry, forgot this bit is in the DDL too:

ALTER TABLE ONLY players
    ADD CONSTRAINT players_pkey PRIMARY KEY (id);

So that should be the equivalent of what you're saying, since Serial is
just a shorthand, it's not an actual datatype:
http://www.postgresql.org/docs/8.1/interactive/dat...

Jeroen
B9a732fc30c32098347a0177c75ee27b?d=identicon&s=25 jeroen (Guest)
on 2005-11-12 12:49
(Received via mailing list)
Blair Zajac wrote:
>>>>>>> -----Original Message-----
>>>>>> <snip>
>>>>>
>>>>
>>> works with PostgreSQL as well.
>> right (I think!), new records are inserting fine, yet the
> works for me when I use the DEFAULT nextval('') syntax:
>
> http://dev.rubyonrails.org/ticket/2764
> 
http://dev.rubyonrails.com/attachment/ticket/2594/...
>
>
> Please try the patch.  If it doesn't work for you, please amend the
> ticket with the exact DDL that doesn't work.

The patch works for me.

Thanks!

Jeroen
27c57aaa4bda5ac8b0593659573b522f?d=identicon&s=25 blair (Guest)
on 2005-11-13 19:47
(Received via mailing list)
Jeroen Houben wrote:
>>>>>> Berger, Daniel wrote:
>>>>>>>
>>>>>>>
>>>>>> markup):
>>>>>
>>>>
>>> this in my tests and it's not working and that is my only problem.
>>
>> http://dev.rubyonrails.org/ticket/2764
>> 
http://dev.rubyonrails.com/attachment/ticket/2594/...
>>
>>
>> Please try the patch.  If it doesn't work for you, please amend the
>> ticket with the exact DDL that doesn't work.
>
>
> The patch works for me.

The above referenced patch doesn't work on Postgresql 8.1 due to the way
it
handles sequences, but thanks to work by Jeremy Kemper and his commits
in the
last day, the new code works on 8.1.

However, could you try your application against the new code and svn
HEAD by
running 'rake freeze_edge' and let us know if it works?

Regards,
Blair
This topic is locked and can not be replied to.