Re: Urgent: Key constraints behaving weirdly

"Russell Garrett" <rg@tcslon.com> writes:
> Constraints are being weird. The reproduction instructions speak for
> themselves.

You haven't really provided enough info to let anyone do anything about
this.  Certainly no one else is going to be able to reproduce the
problem based on what you've provided.

I can think of a number of theories:

1. There's a rule or trigger ON UPDATE that is modifying the behavior of
the update you showed, and is causing the duplicate-key error (ie,
there's a bug in the rule or trigger logic and the error report is
legitimate).

2. The index is corrupt, possibly due to a hardware glitch.  (This seems
unlikely because the SELECT result appears normal, but I can't rule it
out entirely.)

3. You've stumbled across a corner-case bug that no one has seen before.

If the problem is #2 then the likely recovery method would be a REINDEX,
hopefully followed by some intensive hardware testing.

If the problem is #3 then it's fairly likely that a REINDEX would make
the failure go away, but we'd lose this opportunity to find the bug.

If you want to pursue theory #3, I would recommend the following
procedure:
    A. Shut down postmaster for long enough to make a tarball copy
       of the whole $PGDATA directory tree.  You should then be able
       to investigate the problem at leisure by loading the tarball
       on a playpen machine.
    B. REINDEX to (hopefully) make the problem go away on the
       production machine, so it can get back to work.
    C. Build a debug-enabled Postgres installation on your playpen
       machine, and start tracing through the failure with gdb.

I'd be willing to help out if you don't feel competent to carry through
step C.

            regards, tom lane