Re: dropping column prevented due to inherited index

On Tue, Oct 8, 2019 at 2:12 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> Hi Ashutosh,
>
> On Mon, Oct 7, 2019 at 1:39 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> > I think we could have first deleted all the dependency of child object
> > on parent and then deleted the child itself using performDeletion().
>
> So, there are two objects to consider in this case -- column and an
> index that depends on it.
>
> For columns, we don't store any dependency records for the dependency
> between a child column and its corresponding parent column. That
> dependency is explicitly managed by the code and the attinhcount flag,
> which if set, prevents the column from being dropped on its own.
>
> Indexes do rely on dependency records for the dependency between a
> child index and its corresponding parent index. This dependency
> prevents a child index from being dropped if the corresponding parent
> index is also not being dropped.
>
> In this case, recursively dropping a child's column will in turn try
> to drop the depending child index. findDependentObject() complains
> because it can't allow a child index to be dropped, because it can't
> establish that the corresponding parent index is also being dropped.
> That's because the parent index will be dropped when the parent's
> column will be dropped, which due to current coding of
> ATExecDropColumn() is *after* all the child columns and indexes are
> dropped. If we drop the parent column and depending index first and
> then recurse to children as my proposed patch does, then the parent
> index would already have been dropped when dropping the child column
> and the depending index.
>
> > As an example let's consider the case of toast table where we first
> > delete the dependency of toast relation on main relation and then
> > delete the toast table itself otherwise the toast table deletion would
> > fail. But, the problem I see here is that currently we do not have any
> > entry in pg_attribute table that would tell us about the dependency of
> > child column on parent.
>
> I couldn't imagine how that trick could be implemented for this case. :(
>

I don't think that is possible because presently in pg_attribute table
we do not have any column indicating that there exists index on the
given attribute. If we were knowing that then we could find out if the
given index on child table have been inherited from parent and if so,
we could delete all the dependencies on the child table index first
and then delete the column itself in the child table but that doesn't
seem to be doable here. So, although the standard way that I feel to
perform an object deletion is to first remove all it's dependencies
from the pg_depend table and then delete the object itself but
considering the information available in the relevant catalog table
that doesn't seem to be possible.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com