Forum: Ruby on Rails 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 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.

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

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

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.

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

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

-- G.

On Apr 29, 2006, at 6:39 PM, Robby Russell 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

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!

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@marnen.org

I ran into this issue when I backuped a database and restored it back 
into another system.

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

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 for this post!

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.