BUG #13935: Duplicate row in pg_constraint table which is not accessible via indexes - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      13935
Logged by:          Alexey Makhmutov
Email address:      bear2k@mail.ru
PostgreSQL version: 9.4.4
Operating system:   Linux x86_64
Description:

We are seeing a very strange situation - table pg_constrint has a duplicate
row for particular primary key constraint which is not accessible via
indexes but is visible during segment scan. There wasn't any specific
manipulation with catalog data, however the database is used with standby
node, so switchover from one instance to another is performed from time to
time.

Here is the PG version:
# select version();
                                                    version

---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)

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)

# explain select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint
where conrelid::int + 0 = 50621;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on pg_constraint  (cost=0.00..11.81 rows=1 width=23)
   Filter: (((conrelid)::integer + 0) = 50621)
(2 rows)

Here we can see two primary key constraints for the same relation, which is
very strange by itself. Now, let's try to access the same information by
using index scan:

# select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint where
conrelid = 50621;
  oid   |  ctid   |  xmin   | xmax | conrelid | contype
--------+---------+---------+------+----------+---------
 301952 | (6,136) | 4883898 |    0 |    50621 | p
(1 row)

# explain select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint
where conrelid = 50621;
                                            QUERY PLAN

---------------------------------------------------------------------------------------------------
 Index Scan using pg_constraint_conrelid_index on pg_constraint
(cost=0.14..4.16 rows=1 width=23)
   Index Cond: (conrelid = 50621::oid)
(2 rows)

We can see only one row now. Moreover, if we try to access that row by its
oid it will be still not accessible via index:

# select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint where oid in
(301952, 300466);
  oid   |  ctid   |  xmin   | xmax | conrelid | contype
--------+---------+---------+------+----------+---------
 301952 | (6,136) | 4883898 |    0 |    50621 | p
(1 row)

# explain select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint
where oid in (301952, 300466);
                                      QUERY PLAN

--------------------------------------------------------------------------------------
 Bitmap Heap Scan on pg_constraint  (cost=4.31..7.33 rows=2 width=23)
   Recheck Cond: (oid = ANY ('{301952,300466}'::oid[]))
   ->  Bitmap Index Scan on pg_constraint_oid_index  (cost=0.00..4.30 rows=2
width=0)
         Index Cond: (oid = ANY ('{301952,300466}'::oid[]))
(4 rows)

However, if accessed by full segment scan - it is visible:

# set enable_bitmapscan = off;
SET
# set enable_indexscan = off;
SET
# select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint where oid in
(301952, 300466);
  oid   |  ctid   |  xmin   | xmax | conrelid | contype
--------+---------+---------+------+----------+---------
 301952 | (6,136) | 4883898 |    0 |    50621 | p
 300466 | (7,1)   | 4786734 |    0 |    50621 | p
(2 rows)

# explain select oid,ctid,xmin,xmax,conrelid,contype from pg_constraint
where oid in (301952, 300466);
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on pg_constraint  (cost=0.00..11.18 rows=2 width=23)
   Filter: (oid = ANY ('{301952,300466}'::oid[]))
(2 rows)

>From my perspective this is something which shouldn't be observed in the
system, so it looks like some kind of bug. Is there any known problem which
may lead to such behavior?

Thanks,
Alexey Makhmutov