My database hurts "ERROR: duplicate key violates unique constraint"

[Mike Hunter]

(Please CC me on any replies as I'm not on the list)

Hi,

After a recent power failure, a program that uses a pgsql backend
(netdisco) started to send me nastygrams. I tried the author's suggestion
of running a VACUUM FULL ANALYZE VERBOSE;, but it still sends me the
messages. The data in the database isn't too important, i.e. I'm willing
to blow away data to try to fix the problem. Any suggestions?

Thanks for your help and thanks on behalf of my whole department for
postgresql.

Mike

----- Forwarded message from Max Baker <ma*@warped.org> -----

Date: Sat, 24 Apr 2004 13:53:16 -0400
From: Max Baker <ma*@warped.org>
To: Mike Hunter <mh*****@ack.Berkeley.EDU>
Cc: ne************@lists.sourceforge.net
Subject: Re: [Netdisco] pgsql error messages
In-Reply-To: <20*******************@ack.Berkeley.EDU>
User-Agent: Mutt/1.4.2i

Mike,

The first thing I would try is the "Things are getting really slow"
procedure in README for the database.
After that I would end up posting the same question on one of the postgres
mailing lists.

-m
On Thu, Apr 15, 2004 at 04:02:54PM -0700, Mike Hunter wrote:

Hey everybody,

I can't really blame netdisco for this one; we had a power outage and this
machine went down ungracefully. Now cron sends me these notes, but
netdisco still seems to function.

DBD::Pg::db do failed: ERROR: duplicate key violates unique constraint "node_pkey" at /usr/local/netdisco/netdisco.pm line 1076.
DBD::Pg::db do failed: ERROR: duplicate key violates unique constraint "node_pkey" at /usr/local/netdisco/netdisco.pm line 967.
insert_or_update(UPDATE node SET switch='128.32.11.11',active='1',time_last='Thu Apr 15 14:06:05 2004',mac='00:03:93:48:e8:f8',port='FastEthernet1/2',oui='00:03:93' WHERE switch = '128.32.11.11' AND port = 'FastEthernet1/2' AND mac = '00:03:93:48:e8:f8' ) ERROR: duplicate key violates unique constraint "node_pkey"
at /usr/local/netdisco/netdisco line 1810
DBD::Pg::db do failed: ERROR: duplicate key violates unique constraint "node_pkey" at /usr/local/netdisco/netdisco.pm line 1076.
DBD::Pg::db do failed: ERROR: duplicate key violates unique constraint "node_pkey" at /usr/local/netdisco/netdisco.pm line 967.
at /usr/local/netdisco/netdisco line 1810

I'm gonna try rediscovering the device I suppose...any other advice?

Mike

----- End forwarded message -----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Mike Hunter <mh*****@ack.Berkeley.EDU> writes:

After a recent power failure, a program that uses a pgsql backend
(netdisco) started to send me nastygrams.

Hmm. Try REINDEX on the index involved.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)