I just switched from using a column type of SERIAL to trying to use a
Postgres Sequence defined as:
CREATE SEQUENCE unique_sequence START 101;
The column definition went from "id SERIAL NOT NULL" to
"id INTEGER NOT NULL DEFAULT nextval(''unique_sequence''),
"
When i insert outside of rails, everything works fine. Inside rails,
however, I get this error message:
ERROR C42P01 Mrelation "project_id_seq" does not
exist Fnamespace.c L201 RRangeVarGetRelid
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.
thanks.
SERIAL is just an alias for ''integer no null default
nextval(''sequence'')...'' Looks like Rails is looking
for a sequence named project_id_seq yet you created
one named unique_sequence.
CSN
--- Larry White <ljw1001-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> I just switched from using a column type of SERIAL
> to trying to use a
> Postgres Sequence defined as:
>
> CREATE SEQUENCE unique_sequence START 101;
>
> The column definition went from "id SERIAL NOT NULL"
> to
>
> "id INTEGER NOT NULL DEFAULT
> nextval(''unique_sequence''), "
>
> When i insert outside of rails, everything works
> fine. Inside rails,
> however, I get this error message:
>
> ERROR C42P01 Mrelation "project_id_seq" does not
> exist Fnamespace.c L201 RRangeVarGetRelid
>
> 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.
>
> thanks.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Right, you need the standard sequence name of
"#{table_name}_#{primary_key_column_name}_seq", unless you use
AR''s
set_sequence_name method to name the sequence.
But you will still run into a problem with these explicitly named sequences in
Postgresql that RoR 0.14.2 doesn''t handle:
http://dev.rubyonrails.com/ticket/2594
Regards,
Blair
CSN wrote:> SERIAL is just an alias for ''integer no null default
> nextval(''sequence'')...'' Looks like Rails is
looking
> for a sequence named project_id_seq yet you created
> one named unique_sequence.
>
> CSN
>
>
> --- Larry White <ljw1001-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
>>I just switched from using a column type of SERIAL
>>to trying to use a
>>Postgres Sequence defined as:
>>
>>CREATE SEQUENCE unique_sequence START 101;
>>
>>The column definition went from "id SERIAL NOT NULL"
>> to
>>
>>"id INTEGER NOT NULL DEFAULT
>>nextval(''unique_sequence''), "
>>
>>When i insert outside of rails, everything works
>>fine. Inside rails,
>>however, I get this error message:
>>
>>ERROR C42P01 Mrelation "project_id_seq" does not
>>exist Fnamespace.c L201 RRangeVarGetRelid
>>
>>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.
>>
>>thanks.
That makes sense but I was trying to use one sequence for 5 different tables so the ids would be unique across all the tables. If the sequence has to have the same name as the table, that would be impossible. Is that what''s happening here?>Date: Mon, 7 Nov 2005 16:12:31 -0800 (PST) >From: CSN <cool_screen_name90001-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> >Subject: Re: [Rails] using postgres sequences on id columns >To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >Message-ID: <20051108001231.64300.qmail-SGDjkES4kpuA/QwVtaZbd3CJp6faPEW9@public.gmane.org> >Content-Type: text/plain; charset=iso-8859-1>SERIAL is just an alias for ''integer no null default >nextval(''sequence'')...'' Looks like Rails is looking >for a sequence named project_id_seq yet you created >one named unique_sequence.>CSN>--- Larry White <ljw1001-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:>> I just switched from using a column type of SERIAL >> to trying to use a >> Postgres Sequence defined as: >> >>CREATE SEQUENCE unique_sequence START 101; >> >> The column definition went from "id SERIAL NOT NULL" >> to >> >> "id INTEGER NOT NULL DEFAULT >> nextval(''unique_sequence''), " >> >> When i insert outside of rails, everything works >> fine. Inside rails, >> however, I get this error message: >> >> ERROR C42P01 Mrelation "project_id_seq" does not >> exist Fnamespace.c L201 RRangeVarGetRelid >> >> 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. >> > thanks._______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
I''d suggest revisiting your database design. Sounds like all five tables could be combined into one. CSN --- Larry White <ljw1001-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> That makes sense but I was trying to use one > sequence for 5 different tables > so the ids would be unique across all the tables. If > the sequence has to > have the same name as the table, that would be > impossible. Is that what''s > happening here? > > >Date: Mon, 7 Nov 2005 16:12:31 -0800 (PST) > >From: CSN <cool_screen_name90001-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> > >Subject: Re: [Rails] using postgres sequences on id > columns > >To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >Message-ID: > <20051108001231.64300.qmail-SGDjkES4kpuA/QwVtaZbd3CJp6faPEW9@public.gmane.org> > >Content-Type: text/plain; charset=iso-8859-1 > > >SERIAL is just an alias for ''integer no null > default > >nextval(''sequence'')...'' Looks like Rails is looking > >for a sequence named project_id_seq yet you created > >one named unique_sequence. > > >CSN > > >--- Larry White <ljw1001-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >> I just switched from using a column type of > SERIAL > >> to trying to use a > >> Postgres Sequence defined as: > >> > >>CREATE SEQUENCE unique_sequence START 101; > >> > >> The column definition went from "id SERIAL NOT > NULL" > >> to > >> > >> "id INTEGER NOT NULL DEFAULT > >> nextval(''unique_sequence''), " > >> > >> When i insert outside of rails, everything works > >> fine. Inside rails, > >> however, I get this error message: > >> > >> ERROR C42P01 Mrelation "project_id_seq" does not > >> exist Fnamespace.c L201 RRangeVarGetRelid > >> > >> 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. > >> > > thanks. > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >__________________________________ Yahoo! Mail - PC Magazine Editors'' Choice 2005 http://mail.yahoo.com
Blair Zajac wrote:> Right, you need the standard sequence name of > "#{table_name}_#{primary_key_column_name}_seq", unless you use AR''s > set_sequence_name method to name the sequence. > > But you will still run into a problem with these explicitly named > sequences in Postgresql that RoR 0.14.2 doesn''t handle: > > http://dev.rubyonrails.com/ticket/2594Well I have a standard name for my sequence but it doesn''t work either. ?> Player.primary_key => "id" >> Player.connection.default_sequence_name "players" => "players_id_seq" >> Player.connection.pk_and_sequence_for "players" => nil >> table definition: Table "public.players" Column | Type | Modifiers ------------------+------------------------+---------------------------------------------------- id | integer | not null default nextval(''"players_id_seq"''::text) positie | integer | active | boolean | not null default false perm_level | smallint | not null default 1 email | character varying(150) | not null fname | character varying(60) | not null lname | character varying(80) | not null Jeroen
> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White > Sent: Monday, November 07, 2005 5:02 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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
Berger, Daniel wrote:>>-----Original Message----- >>From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White >>Sent: Monday, November 07, 2005 5:02 PM >>To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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.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
On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote:> Berger, Daniel wrote: > >>-----Original Message----- > >>From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White > >>Sent: Monday, November 07, 2005 5:02 PM > >>To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. > > 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/2764There 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/Base.html#M000717 -Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com * Programming Rails | www.programmingrails.com *******************************************************/
On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote:> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: > > Berger, Daniel wrote: > > >>-----Original Message----- > > >>From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > >>[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White > > >>Sent: Monday, November 07, 2005 5:02 PM > > >>To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. > > > > 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 > > 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/Base.html#M000717It 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/activerecord/CHANGELOG.html Cheers, -Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com * Programming Rails | www.programmingrails.com *******************************************************/
Robby Russell wrote:> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: > >>On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >> >>>Berger, Daniel wrote: >>> >>>>>-----Original Message----- >>>>>From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White >>>>>Sent: Monday, November 07, 2005 5:02 PM >>>>>To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>> >>>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 >> >>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/Base.html#M000717 > > > 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/activerecord/CHANGELOG.htmlI 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
Jeroen Houben wrote:> Robby Russell wrote: > >> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >> >>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>> >>>> Berger, Daniel wrote: >>>> >>>>>> -----Original Message----- >>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry >>>>>> White >>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>> >>>> >>>> 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 >>> >>> >>> 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/Base.html#M000717 >> >> >> >> 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/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, > > JeroenCan you show us what your CREATE TABLE statement looked like? -Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com * Programming Rails | www.programmingrails.com *******************************************************/
Jeroen Houben wrote:> Robby Russell wrote: > >> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >> >>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>> >>>> Berger, Daniel wrote: >>>> >>>>>> -----Original Message----- >>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry White >>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>> >>>> >>>> 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 >>> >>> >>> 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/Base.html#M000717 >> >> >> >> 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/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, > > JeroenThe 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 -- Blair Zajac, Ph.D. <blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org> Subversion and Orca training and consulting http://www.orcaware.com/svn/
Robby Russell wrote:> Jeroen Houben wrote: > >> Robby Russell wrote: >> >>> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >>> >>>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>>> >>>>> Berger, Daniel wrote: >>>>> >>>>>>> -----Original Message----- >>>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry >>>>>>> White >>>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>>> >>>>> >>>>> >>>>> 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 >>>> >>>> >>>> >>>> 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/Base.html#M000717 >>> >>> >>> >>> >>> 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/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 > > > Can you show us what your CREATE TABLE statement looked like? >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
On Tue, 2005-11-08 at 23:35 +0100, Jeroen Houben wrote:> Robby Russell wrote: > > Can you show us what your CREATE TABLE statement looked like? > > > 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)) > );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 -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com * Programming Rails | www.programmingrails.com *******************************************************/
Blair Zajac wrote:> Jeroen Houben wrote: > >> Robby Russell wrote: >> >>> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >>> >>>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>>> >>>>> Berger, Daniel wrote: >>>>> >>>>>>> -----Original Message----- >>>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry >>>>>>> White >>>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>>> >>>>> >>>>> >>>>> 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 >>>> >>>> >>>> >>>> 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/Base.html#M000717 >>> >>> >>> >>> >>> 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/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 > > > 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: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/datatype.html#DATATYPE-SERIAL 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
Robby Russell wrote:>On Tue, 2005-11-08 at 23:35 +0100, Jeroen Houben wrote: > > >>Robby Russell wrote: >> >> >>>Can you show us what your CREATE TABLE statement looked like? >>> >>> >>> >>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)) >>); >> >> > >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 > >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/datatype.html#DATATYPE-SERIAL Jeroen
Blair Zajac wrote:> Jeroen Houben wrote: > >> Robby Russell wrote: >> >>> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >>> >>>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>>> >>>>> Berger, Daniel wrote: >>>>> >>>>>>> -----Original Message----- >>>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry >>>>>>> White >>>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>>> >>>>> >>>>> >>>>> 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 >>>> >>>> >>>> >>>> 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/Base.html#M000717 >>> >>> >>> >>> >>> 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/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 > > > 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.The patch works for me. Thanks! Jeroen
Jeroen Houben wrote:> Blair Zajac wrote: > >> Jeroen Houben wrote: >> >>> Robby Russell wrote: >>> >>>> On Tue, 2005-11-08 at 06:59 -0800, Robby Russell wrote: >>>> >>>>> On Tue, 2005-11-08 at 15:53 +0100, Jeroen Houben wrote: >>>>> >>>>>> Berger, Daniel wrote: >>>>>> >>>>>>>> -----Original Message----- >>>>>>>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>>>>>>> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Larry >>>>>>>> White >>>>>>>> Sent: Monday, November 07, 2005 5:02 PM >>>>>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.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. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> 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 >>>>> >>>>> >>>>> >>>>> >>>>> 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/Base.html#M000717 >>>> >>>> >>>> >>>> >>>> >>>> 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/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 >> >> >> >> 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. > > > 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