Howdy.
I''m very new to Rails. I''ve done a few Rails+MySQL tutorials.
Last night I wanted to begin rebuilding my Java/JSP/PostgreSQL app in
Rails. I ran into a problem right away though.
I created my [persons] table with the following columns:
id / integer / not null default nextval(''persons_id_seq''),
lastname / varchar(64) / not null
...
When I would try to create a new record, I would get some Mrelation
error. Unfortunately I don''t have the details here, but I suspect it
should be easily reproducable.
Suggestions?
Thanks,
MT
On Monday 28 March 2005 11:49 am, Michael Teter wrote:> When I would try to create a new record, I would get some Mrelation > error. Unfortunately I don''t have the details here, but I suspect it > should be easily reproducable.Your database user probably doesn''t have the correct priviledges on the primary key sequence. Caleb
Are there any other possibilities? My Rails is talking to my db as the same user that created the database, the tables, and the sequence. On Mon, 28 Mar 2005 11:54:30 -0500, Caleb Tennis <caleb-PfRr3eUzJn1Wk0Htik3J/w@public.gmane.org> wrote:> On Monday 28 March 2005 11:49 am, Michael Teter wrote: > > When I would try to create a new record, I would get some Mrelation > > error. Unfortunately I don''t have the details here, but I suspect it > > should be easily reproducable. > > Your database user probably doesn''t have the correct priviledges on the > primary key sequence. > > Caleb > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Monday 28 March 2005 12:00 pm, Michael Teter wrote:> Are there any other possibilities? My Rails is talking to my db as > the same user that created the database, the tables, and the sequence.The only other one that comes to mind is that your sequence is attempting to insert a primary key value that is already in the table. You might check and see what the next value is on the sequence and make sure you don''t already have data in the table with that value. A quick check of your database log file (or Rails'' development.log, assuming you''re in dev mode) with the actual error message would be helpful. Caleb
On Mon, 28 Mar 2005 11:54:30 -0500, Caleb Tennis <caleb-PfRr3eUzJn1Wk0Htik3J/w@public.gmane.org> wrote:> On Monday 28 March 2005 11:49 am, Michael Teter wrote: > > When I would try to create a new record, I would get some Mrelation > > error. Unfortunately I don''t have the details here, but I suspect it > > should be easily reproducable. > > Your database user probably doesn''t have the correct priviledges on the > primary key sequence. >I don''t know Postgres syntax, but is id / integer / not null default nextval(''persons_id_seq''), the proper way to define a primary key + autoincrement for the ''id'' column? If there''s no primary key or autoincrement is not properly set up.. I could see that error happening.
Joe Van Dyk wrote:> On Mon, 28 Mar 2005 11:54:30 -0500, Caleb Tennis <caleb-PfRr3eUzJn1Wk0Htik3J/w@public.gmane.org> wrote: > >>On Monday 28 March 2005 11:49 am, Michael Teter wrote: >> >>>When I would try to create a new record, I would get some Mrelation >>>error. Unfortunately I don''t have the details here, but I suspect it >>>should be easily reproducable. >> >>Your database user probably doesn''t have the correct priviledges on the >>primary key sequence. >> > > > I don''t know Postgres syntax, but is > > id / integer / not null default nextval(''persons_id_seq''), > > the proper way to define a primary key + autoincrement for the ''id'' > column? If there''s no primary key or autoincrement is not properly > set up.. I could see that error happening.serial makes an implicit integer sequence. primary key implies not null. set your sequence value so it doesn''t conflict with any preloaded data. create table people ( id serial primary key, name varchar(255) ); select pg_catalog.setval(pg_catalog.pg_get_serial_sequence(''people'', ''id''), 4, true); copy people (id, name) from stdin; 1 Foo 2 Bar 3 Baz \. jeremy
* Michael Teter <michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> [2005-03-28 10:49:06 -0600]:> Last night I wanted to begin rebuilding my Java/JSP/PostgreSQL app in > Rails. I ran into a problem right away though. > > I created my [persons] table with the following columns: > id / integer / not null default nextval(''persons_id_seq''), > lastname / varchar(64) / not null > ...I''ve been using postgres for a pet project of mine and it works fine. The syntax you''re using is correct, but you could also try something like this: create table users ( "id" serial not null unique primary key, "login" varchar(80) not null, "password" varchar not null, "created_on" timestamp not null, "updated_on" timestamp not null); That will create the sequence for you. Is this PostgreSQL 8 by chance? -- Luke | PGP: 0xFBE7D8AF goseigen-Wuw85uim5zDR7s880joybQ@public.gmane.org | 2A44 9EB2 F541 C1F2 D969 56E3 8617 5B7F FBE7 D8AF
This is PostgreSQL 8. I''ve tried with both manually-created sequence (the way I always used to do), and with SERIAL type. In all cases the table was empty, so there would be no id conflict. This is basically the most simple case. Having used PostgreSQL for years with JDBC, I''m quite comfortable with dealing with sequences. I''ll setup the same scenario here at work and get more details about the error (including the log), then post them. Thanks, MT On Mon, 28 Mar 2005 12:34:15 -0500, Luke Renn <goseigen-Wuw85uim5zDR7s880joybQ@public.gmane.org> wrote:> * Michael Teter <michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> [2005-03-28 10:49:06 -0600]: > > > Last night I wanted to begin rebuilding my Java/JSP/PostgreSQL app in > > Rails. I ran into a problem right away though. > > > > I created my [persons] table with the following columns: > > id / integer / not null default nextval(''persons_id_seq''), > > lastname / varchar(64) / not null > > ... > > I''ve been using postgres for a pet project of mine and it works fine. > The syntax you''re using is correct, but you could also try something > like this: > > create table users ( "id" serial not null unique primary key, "login" > varchar(80) not null, "password" varchar not null, "created_on" timestamp not null, "updated_on" timestamp not null); > > That will create the sequence for you. Is this PostgreSQL 8 by > chance? > > -- > Luke | PGP: 0xFBE7D8AF > goseigen-Wuw85uim5zDR7s880joybQ@public.gmane.org | 2A44 9EB2 F541 C1F2 D969 56E3 8617 5B7F FBE7 D8AF > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Michael Teter wrote:> This is PostgreSQL 8. > > I''ve tried with both manually-created sequence (the way I always used > to do), and with SERIAL type. In all cases the table was empty, so > there would be no id conflict. > > This is basically the most simple case. Having used PostgreSQL for > years with JDBC, I''m quite comfortable with dealing with sequences. > > I''ll setup the same scenario here at work and get more details about > the error (including the log), then post them.>From the OP, it appears that you''re creating a Person model. Thiscorresponds to a table named "people" according to Rails'' pluralization rules. The error you''re getting is probably that no relation named "people" exists. If you want to use "persons", declare it in the model class: class Person < ActiveRecord::Base set_table_name ''persons'' end On a side note, I wish this read "table_name ''persons''" instead. set_table_name is more imperative than declarative. jeremy
No, probably not the right way to do it. What you need to do is write an Insert trigger on the table to put the new value into the key. Default values are added after the record is inserted, so because you have a default on the primary key, when you try to post the change, the DB sees that the required value is missing. The Insert trigger gets around this. On Mon, 28 Mar 2005 09:15:44 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Mon, 28 Mar 2005 11:54:30 -0500, Caleb Tennis <caleb-PfRr3eUzJn1Wk0Htik3J/w@public.gmane.org> wrote: > > On Monday 28 March 2005 11:49 am, Michael Teter wrote: > > > When I would try to create a new record, I would get some Mrelation > > > error. Unfortunately I don''t have the details here, but I suspect it > > > should be easily reproducable. > > > > Your database user probably doesn''t have the correct priviledges on the > > primary key sequence. > > > > I don''t know Postgres syntax, but is > > id / integer / not null default nextval(''persons_id_seq''), > > the proper way to define a primary key + autoincrement for the ''id'' > column? If there''s no primary key or autoincrement is not properly > set up.. I could see that error happening. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Ok I lied. I was trying to simplify the problem. The table name wasn''t "persons", it was "reps", but I didn''t want to get into any ideological crap with anyone about the problem domain. Table "reps", model Rep. My first exposure to Rails was the Orielly recipes tutorial. They use MySQL. Their first table is "recipes". It contains the following columns: id / integer / autoincrement title / varchar(255) etc. That tutorial has you immediately testing /cookbook/recipe/new, which works fine. Why shouldn''t I be able to do the same thing with PostgreSQL as the back end?
On Mon, 2005-03-28 at 13:31 -0600, Michael Teter wrote:> Ok I lied. I was trying to simplify the problem. The table name > wasn''t "persons", it was "reps", but I didn''t want to get into any > ideological crap with anyone about the problem domain. > > Table "reps", model Rep.What, it could be entries in a workout monitoring app. It could be a way of tracking Senators and House members. Either one should be okay to discus, especially when working on a rails problem.> My first exposure to Rails was the Orielly recipes tutorial. They use MySQL. > > Their first table is "recipes". It contains the following columns: > id / integer / autoincrement > title / varchar(255) > etc. > > That tutorial has you immediately testing /cookbook/recipe/new, which > works fine. > > Why shouldn''t I be able to do the same thing with PostgreSQL as the back end?While not running PostgreSQL 8, I am doing similar functions without problems. The only time I had troubles was during testing where the serial wasn''t updated when my fixture specified an id. That caused the later tests to insert data to fail with id conflicts. I solved it by not worrying about which user I picked for my tests. I just forced a setting upon the user that was returned by find_first() and then did the tests needing that set of permissions. I later thought how nice it was that the testing happened in a test database not even used for development. -- Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
On Mon, 28 Mar 2005 10:49:06 -0600, Michael Teter <michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Howdy. > > I''m very new to Rails. I''ve done a few Rails+MySQL tutorials. > > Last night I wanted to begin rebuilding my Java/JSP/PostgreSQL app in > Rails. I ran into a problem right away though. > > I created my [persons] table with the following columns: > id / integer / not null default nextval(''persons_id_seq''), > lastname / varchar(64) / not null > ... > > When I would try to create a new record, I would get some Mrelation > error. Unfortunately I don''t have the details here, but I suspect it > should be easily reproducable. > > Suggestions?Post the full text of the error? Try to get hold of it, it''ll make things a lot clearer. Things to try: Insert a record manually, include all fields but specify id as NULL. This should work. Check which fields are NOT NULL, as an empty text field in Rails get inserted as NULL. Make sure there aren''t any foreign keys on the table that are being set to a bad value. -- Phillip Hutchings http://www.sitharus.com/ sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org
Problem Solved:
Somehow Rails was out of sync with my database. The column reps.id
had a default value of nextval(''reps_id''), (which is the
correct name
of the sequence), but for some reason Rails was looking for
''reps_id_seq''.
At this point there were enough iterations that I have no idea how
things got messed up, so we''ll just close this topic here.
Thanks for the suggestions and input. I greatly appreciate having an
active forum like this to help me get started.
MT
Michael Teter wrote:> Problem Solved: > > Somehow Rails was out of sync with my database. The column reps.id > had a default value of nextval(''reps_id''), (which is the correct name > of the sequence), but for some reason Rails was looking for > ''reps_id_seq''. > > At this point there were enough iterations that I have no idea how > things got messed up, so we''ll just close this topic here. > > Thanks for the suggestions and input. I greatly appreciate having an > active forum like this to help me get started.When your run your Rep unit tests, Active Record will load fixtures and reset the sequence: SELECT setval(''public.reps_id_seq'', (SELECT MAX(id) FROM reps), true) Both schema and sequence name are effectively hardcoded. Here''s an nice patch opportunity; do we have a volunteer ;) jeremy
Michael Teter <michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Somehow Rails was out of sync with my database. The column reps.id > had a default value of nextval(''reps_id''), (which is the correct name > of the sequence), but for some reason Rails was looking for > ''reps_id_seq''.rails relies on naming conventions, some more flexible than others. the postgresql adapter expects the primary key''s sequence to be named in the form of "#{table_name}_#{primary_key}_seq" which is what you''re experiencing above. this means you''ll need to create a new sequence named reps_id_seq, start it at the current value of reps_id is at and then update the defailt value of your primary key to reference reps_id_seq. create sequence reps_id_seq; select setval(''reps_id_seq'', currval(''reps_id'')); alter table reps alter id set default nextval(''reps_id_seq''); Dave
Thanks for the clarification. I''m in good shape now (at least regarding my first obstacle!) MT On Mon, 28 Mar 2005 22:43:03 -0700, Dave Lee <dave.lee.wilson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Michael Teter <michael.teter-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Somehow Rails was out of sync with my database. The column reps.id > > had a default value of nextval(''reps_id''), (which is the correct name > > of the sequence), but for some reason Rails was looking for > > ''reps_id_seq''. > > rails relies on naming conventions, some more flexible than others. > the postgresql adapter expects the primary key''s sequence to be named > in the form of "#{table_name}_#{primary_key}_seq" which is what you''re > experiencing above. this means you''ll need to create a new sequence > named reps_id_seq, start it at the current value of reps_id is at and > then update the defailt value of your primary key to reference > reps_id_seq. > > create sequence reps_id_seq; > select setval(''reps_id_seq'', currval(''reps_id'')); > alter table reps alter id set default nextval(''reps_id_seq''); > > Dave >