Re: pg_class.relnamespace NOT IN pg_namespace.oid

W dniu 2012-02-27 23:57, Tom Lane pisze:
> One possible theory for cascaded drops to fail like that is that the indexes on pg_depend are
> corrupt, so you might want to consider REINDEXing that catalog, just in case.

so before reindexing it would be worth veryfing the theory and check indexes for corruption in their
current state. But I am not sure if I know how to perform these particular checks. The only ones I
could invent myself, pretty naively right now, are as follows, but I am not sure if they give the
information wee look for:

# select * from pg_depend where classid = 1259 and objid in (52072904, 52072932);
  classid |  objid   | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+----------+----------+------------+----------+-------------+---------
     1259 | 52072904 |        0 |       2615 | 52072764 |           0 | n
     1259 | 52072932 |        0 |       2615 | 52072764 |           0 | n
(2 rows)

Time: 0.637 ms

# EXPLAIN ANALYZE select * from pg_depend where refclassid = 2615 and refobjid = 52072764;
                                                                QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using pg_depend_reference_index on pg_depend  (cost=0.00..4.43 rows=1 width=25) (actual
time=14.393..21.852 rows=2 loops=1)
    Index Cond: ((refclassid = 2615::oid) AND (refobjid = 52072764::oid))
  Total runtime: 21.906 ms
(3 rows)

Time: 22.360 ms
# EXPLAIN ANALYZE select * from pg_depend where classid = 1259 and objid in (52072904, 52072932);
                                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on pg_depend  (cost=4.70..8.67 rows=2 width=25) (actual time=22.042..22.051
rows=2 loops=1)
    Recheck Cond: ((classid = 1259::oid) AND (objid = ANY ('{52072904,52072932}'::oid[])))
    ->  Bitmap Index Scan on pg_depend_depender_index  (cost=0.00..4.70 rows=2 width=0) (actual
time=22.017..22.017 rows=2 loops=1)
          Index Cond: ((classid = 1259::oid) AND (objid = ANY ('{52072904,52072932}'::oid[])))
  Total runtime: 22.104 ms
(5 rows)