Rails - Apr 2006 - postgresql duplicate key violates unique constraint

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 4/30/06, Guido Sohne <guido.sohne@gmail.com>
wrote:
> 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.
>
>
>

On Apr 29, 2006, at 5:56 PM, Guido Sohne wrote:
> 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.
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 Russell
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.planetargon.com
www.robbyonrails.com

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

On 4/30/06, Robby Russell <robby.lists@planetargon.com>
wrote:
>
> On Apr 29, 2006, at 5:56 PM, Guido Sohne wrote:
>
> > 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.
>
> 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 Russell
> Founder & Executive Director
>
> PLANET ARGON, LLC
> Ruby on Rails Development, Consulting & Hosting
>
> www.planetargon.com
> www.robbyonrails.com
>
> +1 503 445 2457
> +1 877 55 ARGON [toll free]
> +1 815 642 4968 [fax]
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Apr 29, 2006, at 6:54 PM, Guido Sohne 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

Robby Russell
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.planetargon.com
www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]

On Apr 29, 2006, at 6:39 PM, Robby Russell wrote:
> On Apr 29, 2006, at 5:56 PM, Guido Sohne wrote:
>> 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.
>
> 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);
Fixtures have to do the same, hence:
  
ActiveRecord::Base.connection.reset_pk_sequence!(''entities'')

Best,
jeremy

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 Seltzer


-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On
Behalf Of Robby Russell
Sent: Sunday, April 30, 2006 5:00 AM
To: rails@lists.rubyonrails.org
Subject: Re: [Rails] postgresql duplicate key violates unique constraint

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.

On May 4, 2006, at 12:10 PM, Warren Seltzer wrote:
> 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 Seltzer
>
>
> -----Original Message-----
> From: rails-bounces@lists.rubyonrails.org [mailto:rails- 
> bounces@lists.rubyonrails.org] On
> Behalf Of Robby Russell
> Sent: Sunday, April 30, 2006 5:00 AM
> To: rails@lists.rubyonrails.org
> Subject: Re: [Rails] postgresql duplicate key violates unique  
> constraint
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

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 Seltzer


-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On
Behalf Of Guido Sohne
Sent: Thursday, May 04, 2006 3:24 PM
To: rails
Subject: Re: [Rails] postgresql duplicate key violates unique constraint


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

Yes, that''s what I was referring to. And the fix is so simple either  
way :-)

-- G.

On May 4, 2006, at 12:59 PM, Warren Seltzer wrote:
> 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 Seltzer
>
>
> -----Original Message-----
> From: rails-bounces@lists.rubyonrails.org [mailto:rails- 
> bounces@lists.rubyonrails.org] On
> Behalf Of Guido Sohne
> Sent: Thursday, May 04, 2006 3:24 PM
> To: rails
> Subject: Re: [Rails] postgresql duplicate key violates unique  
> constraint
>
>
> 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 ...
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Robby Russell wrote:
> On Apr 29, 2006, at 5:56 PM, Guido Sohne 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!
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Warren Seltzer 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?

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

I ran into this issue when I backuped a database and restored it back 
into another system.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

> 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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Robby Russell 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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Robby Russell wrote in post #72333:
> On Apr 29, 2006, at 5:56 PM, Guido Sohne 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 fro this post!

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Robby Russell 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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.