Re: Getting ERROR: could not open file "base/13164/t3_16388" with partition table with ON COMMIT

Hi,

On 2018/11/02 14:27, Michael Paquier wrote:
> On Fri, Nov 02, 2018 at 02:18:04PM +0900, Michael Paquier wrote:
>> This case is funky.

Interesting indeed.

>> The parent gets dropped at commit time, but it does
>> not know that it should drop the child as well per their dependencies.
>> This actually goes into the internals of performDeletion(), which is
>> scary to touch on back-branches just for such cases..

Well, performDeletion *does* drop the child, because when the parent is
dropped due to its ON COMMIT DROP action, it's done using:

/*
* Since this is an automatic drop, rather than one
* directly initiated by the user, we pass the
* PERFORM_DELETION_INTERNAL flag.
*/
performDeletion(&object,
DROP_CASCADE, PERFORM_DELETION_INTERNAL);

Note the DROP_CASCADE, which means its children will be deleted as part of
this.

> A bit more fun with inheritance:
> =# begin;
> BEGIN
> =# create temp table aa_p (a int) on commit drop;
> CREATE TABLE
> =# create temp table aa_c (a int) inherits (aa_p) on commit delete rows;
> NOTICE: 00000: merging column "a" with inherited definition
> LOCATION: MergeAttributes, tablecmds.c:2339
> CREATE TABLE
> =# insert into aa_p values (1);
> INSERT 0 1
> =# insert into aa_c values (1);
> INSERT 0 1
> =# commit;
> NOTICE: 00000: drop cascades to table aa_c
> LOCATION: reportDependentObjects, dependency.c:995
> ERROR: XX000: could not open relation with OID 16426
> LOCATION: relation_open, heapam.c:1138

I think the problem here is that the ON COMMIT DROP action on the parent
is executed before the ON COMMIT DELETE ROWS on the child. The first
action drops the child along with its parent, causing the 2nd one to fail
upon not finding the table to apply heap_truncate() to. It's
heap_truncate() that produces "ERROR: could not open relation with OID
xxx". So, the NOTICE comes from the *successful* dropping of the child
and the ERROR comes from failure of heap_truncate() to find it.

By the way, this error is same as in the partitioned case. It's simply
that the dependency type is different for regular inheritance child tables
than it is for partitions. The former requires a user command to specify
CASCADE when dropping via parent, whereas the latter doesn't. So, you see
the NOTICE in the regular inheritance case, whereas you don't in the
partitioning case. The error itself is same in both cases.

> Let's treat that as a separate issue, as this happens also with an
> unpatched build. The only reason why you cannot trigger it with
> partitions is that ON COMMIT is currently broken for them, so we should
> fix the reported case first. In consequence, I would tend to commit the
> patch proposed and take care of the first, except if of course anybody
> has an objection.

Agreed that they're two independent issues, although it wouldn't be such a
bad idea to fix them in one go, as they're both issues related to the
handling of ON COMMIT actions on tables in inheritance trees.

I've created a patch for the other issue and attached both.

Thanks,
Amit