ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported number of objects dropped
Date: 2018-05-03 21:39:20
Message-ID: CAH2-Wz=wAKwhv0PqEBFuK2_s8E60kZRMzDdyLi=-MvcM_pHN_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Setting ignore_system_indexes=off in postgresql.conf has the effect of
making almost all regression tests fail during a "make installcheck".
This is unsurprising, since warnings are emitted all over the place.
However, some of the specific ways in which it fails *are* surprising.

I see the following regressions.diff, for the create_view tests:

***************
*** 1711,1714 ****
DROP SCHEMA temp_view_test CASCADE;
NOTICE: drop cascades to 27 other objects
DROP SCHEMA testviewschm2 CASCADE;
! NOTICE: drop cascades to 62 other objects
--- 1725,1732 ----
DROP SCHEMA temp_view_test CASCADE;
NOTICE: drop cascades to 27 other objects
DROP SCHEMA testviewschm2 CASCADE;
! NOTICE: drop cascades to 63 other objects
! WARNING: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
! WARNING: using index "pg_toast_2618_index" despite IgnoreSystemIndexes

Why should the drop cascade to 63 objects rather than 62 because I've
set ignore_system_indexes=on? I know that the order of objects is
unspecified/unstable for the verbose DETAIL output of CASCADE, but
that's rather a different thing to the total number of objects
affected.

The same thing happens to the collate tests:

***************
*** 668,671 ****
--
\set VERBOSITY terse
DROP SCHEMA collate_tests CASCADE;
! NOTICE: drop cascades to 17 other objects
--- 676,679 ----
--
\set VERBOSITY terse
DROP SCHEMA collate_tests CASCADE;
! NOTICE: drop cascades to 20 other objects

--
Peter Geoghegan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-05-03 22:10:40 Re: Python 3.7 support
Previous Message Andrew Dunstan 2018-05-03 21:25:03 MSYS2 and pg_upgrade testing