Thread: ignore_system_indexes affects DROP SCHEMA ... CASCADE reported numberof objects dropped

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

Peter Geoghegan <pg@bowt.ie> writes:
> Why should the drop cascade to 63 objects rather than 62 because I've
> set ignore_system_indexes=on?

Indeed, that seems weird.  Maybe tweak the test scripts so you can see
all the objects cascaded to, and then find out what the additional
object is?  (I think also you could look into the postmaster log,
without changing the test.)

            regards, tom lane

On Thu, May 3, 2018 at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Geoghegan <pg@bowt.ie> writes:
>> Why should the drop cascade to 63 objects rather than 62 because I've
>> set ignore_system_indexes=on?
>
> Indeed, that seems weird.  Maybe tweak the test scripts so you can see
> all the objects cascaded to, and then find out what the additional
> object is?  (I think also you could look into the postmaster log,
> without changing the test.)

I wrote a patch that makes ignore_system_indexes=off agree with
ignore_system_indexes=on in the two specific places I see problems
(the create_view and collate tests). Actually, I started with the
patch, and only ended up finding this issue later, while debugging my
own work.

The goal of this patch of mine is to have nbtree use heap TID as a
unique-ifier -- an implicit "final column" that is a first class part
of the key space (we sometimes have to store an "extra" heap TID
attribute in internal page tuples to make this work across the entire
tree structure). Vadim wanted to do something like this about 20 years
ago, and I think he had the right idea -- it's more or less what
Lehman and Yao intended.

I don't actually know what the problem is just yet, but I suspect some
subtle nbtree issue. I haven't actually debugged the underlying issue
at all just yet, but I'll start on that now. I don't want to post my
patch, since it's very much still a work in progress, and I'm not yet
100% sure that it actually truly fixes the issue.

-- 
Peter Geoghegan

On Thu, May 3, 2018 at 4:14 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> On Thu, May 3, 2018 at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Indeed, that seems weird.  Maybe tweak the test scripts so you can see
>> all the objects cascaded to, and then find out what the additional
>> object is?  (I think also you could look into the postmaster log,
>> without changing the test.)

In the case of the collate tests, these are the 17 objects I can see
with ignore_system_indexes=off, once I remove the "\set VERBOSITY
terse" line from the end of collate.sql:

drop cascades to collation mycoll2
drop cascades to function dup(anyelement)
drop cascades to table collate_test1
drop cascades to table collate_test10
drop cascades to table collate_test2
drop cascades to table collate_test20
drop cascades to table collate_test21
drop cascades to table collate_test22
drop cascades to table collate_test23
drop cascades to table collate_test4
drop cascades to table collate_test5
drop cascades to table collate_test_like
drop cascades to type testdomain
drop cascades to type testdomain_p
drop cascades to view collview1
drop cascades to view collview2
drop cascades to view collview3

(I sorted this output in my text editor)

In the case of ignore_system_indexes=on, I see the same 17 entries, in
addition to these 3 (20 total):

drop cascades to table collate_test23 column f1
drop cascades to table collate_test4 column b
drop cascades to table collate_test5 column b

Perhaps this means something to you. I find it suspicious that all 3
possibly-missing entries are "column" entries.

-- 
Peter Geoghegan

Peter Geoghegan wrote:

> In the case of ignore_system_indexes=on, I see the same 17 entries, in
> addition to these 3 (20 total):
> 
> drop cascades to table collate_test23 column f1
> drop cascades to table collate_test4 column b
> drop cascades to table collate_test5 column b
> 
> Perhaps this means something to you. I find it suspicious that all 3
> possibly-missing entries are "column" entries.

I bet this is related to how are these objects reached while walking the
dependency graph -- i.e. they are reached first as columns and reported
explicitly in the second case, but in the first case the tables are
reached first so the columns are not considered individually.  So it'd
just be because of pg_depend scan order.

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

Peter Geoghegan <pg@bowt.ie> writes:
> In the case of the collate tests, these are the 17 objects I can see
> with ignore_system_indexes=off, once I remove the "\set VERBOSITY
> terse" line from the end of collate.sql:
> ...
> drop cascades to table collate_test23
> drop cascades to table collate_test4
> drop cascades to table collate_test5
> ...

> In the case of ignore_system_indexes=on, I see the same 17 entries, in
> addition to these 3 (20 total):
> drop cascades to table collate_test23 column f1
> drop cascades to table collate_test4 column b
> drop cascades to table collate_test5 column b

Ah, I think it's just an order-of-visitation issue then.  There are
dependencies at both the column and whole-table level, specifically

schema collate_tests -> table collate_test4
schema collate_tests -> domain testdomain_p -> column collate_test4.b

I think if we already know that table collate_test4 is scheduled to be
deleted, we just ignore column collate_test4.b when the recursion reaches
that ... but if we visit those two things in the other order, then both
will be reported as deletion targets.  And it's not surprising that
disabling indexscans on pg_depend changes the visitation order.

This makes me realize that the "\set VERBOSITY terse" hack is not
really as bulletproof a way of preventing regression test diffs
during DROP CASCADE as I'd imagined :-(

            regards, tom lane

On Thu, May 3, 2018 at 7:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ah, I think it's just an order-of-visitation issue then.  There are
> dependencies at both the column and whole-table level, specifically
>
> schema collate_tests -> table collate_test4
> schema collate_tests -> domain testdomain_p -> column collate_test4.b
>
> I think if we already know that table collate_test4 is scheduled to be
> deleted, we just ignore column collate_test4.b when the recursion reaches
> that ... but if we visit those two things in the other order, then both
> will be reported as deletion targets.  And it's not surprising that
> disabling indexscans on pg_depend changes the visitation order.

I also noticed that there are cases where we see less helpful (though
still technically correct) HINT messages about which other object the
user may prefer to drop.

-- 
Peter Geoghegan

On Thu, May 3, 2018 at 7:26 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> I bet this is related to how are these objects reached while walking the
> dependency graph -- i.e. they are reached first as columns and reported
> explicitly in the second case, but in the first case the tables are
> reached first so the columns are not considered individually.  So it'd
> just be because of pg_depend scan order.

There are a couple of other odd things that look related, such as this
extract from the triggers.out section of my regression.diffs:

***************
*** 1047,1056 ****
  NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
  NOTICE:  OLD: (20,30), NEW: (20,31)
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE,
when = BEFORE, level = STATEMENT
- NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE,
when = AFTER, level = ROW
  NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when
= AFTER, level = ROW
! NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE,
when = AFTER, level = STATEMENT
  NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when
= AFTER, level = STATEMENT
  NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
  UPDATE 1
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
--- 1051,1060 ----
  NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
  NOTICE:  OLD: (20,30), NEW: (20,31)
  NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE,
when = BEFORE, level = STATEMENT
  NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when
= AFTER, level = ROW
! NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE,
when = AFTER, level = ROW
  NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when
= AFTER, level = STATEMENT
+ NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE,
when = AFTER, level = STATEMENT
  NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
  UPDATE 1
  UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;

This is a trigger on a view. I'm too tired to figure out whether or
not this is truly cause for concern right now, though

-- 
Peter Geoghegan

>>>>> "Peter" == Peter Geoghegan <pg@bowt.ie> writes:

 Peter> There are a couple of other odd things that look related, such
 Peter> as this extract from the triggers.out section of my
 Peter> regression.diffs:

That one is pretty obvious (from RelationBuildTriggers):

     * Note: since we scan the triggers using TriggerRelidNameIndexId, we will
     * be reading the triggers in name order, except possibly during
     * emergency-recovery operations (ie, IgnoreSystemIndexes). This in turn
     * ensures that triggers will be fired in name order.

Nothing in the code attempts to preserve the order of trigger firing in
the ignore_system_indexes=on case.

-- 
Andrew (irc:RhodiumToad)