Re: how could duplicate pkey exist in psql?

[Yan Chunlu <springrider@xxxxxxxxx>]

 I am using pgpool's replication feature, it does copy pg_xlog from one server to another, was that possible cause of the problem?

thanks for the help!

On Thu, Nov 17, 2011 at 5:38 PM, Edson Richter <richter@xxxxxxxxxxxxxx> wrote:

Em 17-11-2011 03:19, Yan Chunlu escreveu:

recently I have found several tables has exactly the same pkey,  here is the definition:
"diggcontent_data_account_pkey" PRIMARY KEY, btree (thing_id, key)


the data is like this:

  159292 | funnypics_link_point       | 41                                                                                                                     | num
  159292 | funnypics_link_point       | 40                                                                                                                     | num


I could not even update this record.

really confused about how could this happen... thanks!

I know one scenario this can happen on Linux. In my case, it was caused by a "rsync"... instead copy to a different location, script was copying pg_xlog over own pg_xlog.

I did this stupidity once, and learned for a life time. Lost two hours of work to recover everything (from backup, at least I had one).

Be careful with rsync and cp, since Linux does not block files from being overwriten even when they are in use.


Regards,

Edson.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general