RE: using postgres sequences on id columns

Rails
1

-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Larry W.
Sent: Monday, November 07, 2005 5:02 PM
To: [email protected]
Subject: [Rails] using postgres sequences on id columns

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

2

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

3

On Tue, 2005-11-08 at 15:53 +0100, Jeroen H. 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:

-Robby

4

Robby R. wrote:

To: [email protected]
Then declare your serial types as primary keys.

http://api.rubyonrails.org/files/vendor/rails/activerecord/CHANGELOG.html
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

5

On Tue, 2005-11-08 at 06:59 -0800, Robby R. wrote:

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:

ActiveRecord::Base

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 O.)

http://api.rubyonrails.org/files/vendor/rails/activerecord/CHANGELOG.html

Cheers,

-Robby

6

Jeroen H. wrote:

[mailto:[email protected]] 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

7

Robby R. wrote:

-----Original Message-----

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

8

Jeroen H. wrote:

[mailto:[email protected]] On Behalf Of Larry W.
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/ror-postgresql-pk_and_sequence_for-failure-0.14.3-patch.txt?format=raw

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

9

On Tue, 2005-11-08 at 23:35 +0100, Jeroen H. 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

10

Blair Z. wrote:

-----Original Message-----

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:

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/ror-postgresql-pk_and_sequence_for-failure-0.14.3-patch.txt?format=raw

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

11

Robby R. 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:

Jeroen

12

Jeroen H. 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/ror-postgresql-pk_and_sequence_for-failure-0.14.3-patch.txt?format=raw

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 K. 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

13

Blair Z. wrote:

-----Original Message-----

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
Peak Obsession

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