I have a weird problem with creating a new record when using PostgreSQL.
From inside script/console …
>> entity = Entity.new
>> entity.first_name = "Foo"
>> entity.last_name = "bar"
>> entity.save
Throws an exception due to the following SQL error: duplicate key
violates unique constraint.
The application was working fine before and suddenly gives this
grief. I have upgraded all my gems
so I’m now running Rails 1.1.2 and the problem is still there.
Any ideas?
– G.
Here is the relevant portion of my schema.rb …
create_table “entities”, :force => true do |t|
t.column “type”, :string, :limit => 20, :default => “”, :null =>
false
t.column “first_name”, :string, :limit => 50, :default => “”, :null
=> false
t.column “last_name”, :string, :limit => 50, :default => “”, :null
=> false
t.column “other_name”, :string, :limit => 50
t.column “postal_address”, :string, :limit => 50
t.column “house_address”, :string, :limit => 50
t.column “phone_number”, :string, :limit => 20
end
I seem to recall some problem reports on the mailing list with
Postgres, Rails 1.1 and STI.
Could this be the cause? Any help is appreciated.
– G.
On Apr 29, 2006, at 5:56 PM, Guido S. wrote:
Throws an exception due to the following SQL error: duplicate key
violates unique constraint.
Your primary key index isn’t in sync or something.
Can you login to psql and run the following?
SELECT MAX(id) FROM entities;
What is the result?
Then run…
SELECT nextval(‘entities_id_seq’);
This should be higher than the last result.
Is it the same or lower? If so… did you do some importing or
restoring? (your sequence might be off)
If it’s not higher… run this to try and fix it. (run a quick
pg_dump first…)
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
reload your app…and see if its still happening.
Good luck!
-Robby
Robby R.
Founder & Executive Director
PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting
+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]
Hey!
I would like to hereby place you into the category of
CERTIFIED GENIUS
You nailed it right on the head.
I modified the sequence to use the correct value and I think I should
be good to go …
– G.
I think the requirement to manually reset the index is a bug in
postgresql. Either in
implementation or design. Restoring from a dump should restore the
indexes to a working
state. Perhaps pg_dump has an option to write a dump file that restores
the index? I had
the same problem and filed a bug report at postgresql against 8.1.3.
The database should
not be in an inconsistent state. MySQL does not screw up it indexes
after a restore, you
can insert right away.
Robby’s solution worked for me. Still, we shouldn’t have to do it.
Warren S.
On Apr 29, 2006, at 6:54 PM, Guido S. wrote:
Hey!
I would like to hereby place you into the category of
CERTIFIED GENIUS
blushes
You nailed it right on the head.
I’ve come across this several times… typically due to some import
process or something… in any event… glad that I could help. 
Cheers,
Robby R.
Founder & Executive Director
PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting
+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]
I took a look at the SQL generated by pg_dump. I think the problem is
that the sequence values are being dumped and restored … instead of
just being ignored.
So if you do a restore, but have munged the backup file then there is
a problem if you don’t sync the sequence bits as well.
Am sure I missed something, but that’s what is immediately apparent …
– G.
Yes, that’s what I was referring to. And the fix is so simple either
way 
– G.
If this line from the dump is what you are talking about then yes, I
think you have
located the bug:
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence(‘line_items’,
‘id’), 10, true);
I guess that if the pg_dump had written the following command it also
would have prevented
the problem:
REINDEX TABLE line_items;
Warren S.
Robby R. wrote:
On Apr 29, 2006, at 5:56 PM, Guido S. wrote:
Throws an exception due to the following SQL error: duplicate key
violates unique constraint.Your primary key index isn’t in sync or something.
SELECT MAX(id) FROM entities;
SELECT nextval(‘entities_id_seq’);
This should be higher than the last result.
If it’s not higher… run this to try and fix it. (run a quick
pg_dump first…)SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
Let me up this thread as it just saved my a$$!
Is this considered a bug or a feature? Because people easily get trapped
into it!
On Apr 29, 2006, at 6:39 PM, Robby R. wrote:
pg_dump first…)
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
Fixtures have to do the same, hence:
ActiveRecord::Base.connection.reset_pk_sequence!(‘entities’)
Best,
jeremy
I ran into this issue when I backuped a database and restored it back
into another system.
Warren S. wrote:
I think the requirement to manually reset the index is a bug in
postgresql. Either in
implementation or design.
I’ve never needed to do that. I wonder why…would autovacuum help?
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
I ran into this issue when I backuped a database and restored it back
into another system.
You might want to look into how that database got backed up. By default
(at least every where I’ve done it) pg_dump will include the statements
necessary to “reset” the sequences… that is, all my dumps have lines
like this:
SELECT pg_catalog.setval(‘banners_id_seq’, 6, true);
Might be you’re missing that or missing the permissions to set that.
-philip
Robby R. wrote in post #72333:
SELECT MAX(id) FROM entities;
Then run…
This should be higher than the last result.
If it’s not higher… run this to try and fix it. (run a quick
pg_dump first…)
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
reload your app…and see if its still happening.
I have the same problem but I don’t know how to solve it since I can’t
relate the solution here (names of tables, etc.) to the data given by
the OP. How do you know you need to SELECT MAX(id) FROM entities, or
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
with the information given by the OP? I can’t see the relation and
therefore I have no clue how I can solve my problem.
Robby R. wrote in post #72333:
On Apr 29, 2006, at 5:56 PM, Guido S. wrote:
…
SELECT MAX(id) FROM entities;
SELECT nextval(‘entities_id_seq’);
This should be higher than the last result.
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
…
Thank you so much for this post!
Robby R. wrote in post #72333:
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
reload your app…and see if its still happening.
Good luck!
-Robby
I don’t code with Ruby but i registered only to say THANK YOU Robby you
saved my day.
toni t. wrote in post #1102290:
Robby R. wrote in post #72333:
SELECT setval(‘entities_id_seq’, (SELECT MAX(id) FROM entities)+1);
reload your app…and see if its still happening.
Good luck!
-Robby
I don’t code with Ruby but i registered only to say THANK YOU Robby you
saved my day.
Hi,
I am writing below statement in pgsql file in function
endpoint_to_dyninfo_func()
SELECT setval(‘endpoints_dyninfo_id_seq’, (SELECT MAX(id) FROM
endpoints_dyninfo));
But i am gettign error if i am compiling using user msw as,
psql -Umsw < all_trigger_func.pgsql
ERROR: syntax error at or near “endpoints_dyninfo_id_seq”
LINE 22: SELECT setval(‘endpoints_dyninfo_id_seq’, (SELECT MAX(…
^
ERROR: function endpoint_to_dyninfo_func() does not exist
1326
(1 row)
Please help asap.
Thanks,
Ratnesh
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
Sponsor our Newsletter | Privacy Policy | Terms of Service | Remote Ruby Jobs