Re: BUG #13935: Duplicate row in pg_constraint table which is not accessible via indexes

bear2k@mail.ru wrote:

> First, select rows with segment scan:
>
> # select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint where
> conrelid::int + 0 = 50621;
>   oid   |  ctid   |  xmin   | xmax | conrelid | contype
> --------+---------+---------+------+----------+---------
>  301952 | (6,136) | 4883898 |    0 |    50621 | p
>  300466 | (7,1)   | 4786734 |    0 |    50621 | p
> (2 rows)

We've had reports of duplicate rows appearing in user tables, where only
one copy is visible through indexes, but most updates of pg_constraint
don't go through the normal executor, but rather direct catalog
manipulation of DDL implementation, so I'd be surprising if it's the
same thing.  Also, the OIDs of the two rows are different, so really the
two physical rows are not update copies of the same logical row.

I wonder if you got into this state by adding primary keys concurrently
somehow.  That should of course not be allowed, but of course there is
no UNIQUE constraint on conrelid itself, so the normal unique-ification
code in btree does not fire for this situation.

Perhaps we have a bug in some ALTER TABLE command.  Can you tell us how
you created the PK in the affected table?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services