Thread: DROP INHERITS

I have a question about pg_depends, namely how do you identify the dependency
tied to a given relationship. Specifically to handle DROP INHERITS. Is there
any other reason there might be a dependency between two tables other than
inheritance? If there was how would you tell the dependencies apart?

Also, it seems to me the INHRELID syscache is entirely pointless. There isn't
a single consumer of it throughout the source tree. Nor can I think of any
reason anyone would want to look up the nth parent of a table. Do I
misunderstand what purpose this syscache serves?

On the other hand I see a few places where a syscache for a particular
child-parent pair might be useful. Would it make sense to create an index and
syscache for that?

I suppose it makes more sense to optimize this on the basis of what's used in
the planner and executor rather than ALTER TABLE commands though. I don't know
what would be helpful there. 

-- 
greg

Greg Stark <gsstark@mit.edu> writes:
> I have a question about pg_depends, namely how do you identify the dependency
> tied to a given relationship. Specifically to handle DROP INHERITS. Is there
> any other reason there might be a dependency between two tables other than
> inheritance? If there was how would you tell the dependencies apart?

I can't think of another reason --- if there is more than one reason for
a dependency between particular objects, we would have to take a harder
look at the issues.  For the moment you could trawl the sources for
recordDependencyOn calls to see if there is more than one reason.

> Also, it seems to me the INHRELID syscache is entirely pointless.

I don't think anyone has checked for useless syscaches for a long time
:-(.  Please check the others while you are at it.

> On the other hand I see a few places where a syscache for a particular
> child-parent pair might be useful. Would it make sense to create an index and
> syscache for that?

Adding an index where none exists now has a very definite cost.  You
need to argue why the usefulness of the cache exceeds the cost of the
index maintenance.  This goes both ways of course; there may well be
syscaches/indexes we don't have today that would be worth having.

> I suppose it makes more sense to optimize this on the basis of what's used in
> the planner and executor rather than ALTER TABLE commands though.

No, definitely not.  Syscaches only exist to support hard-wired lookups
in the backend C code.  Indexes on system catalogs are of interest to
the planner, but not syscaches.  (So it is legitimate to have indexes
with no associated syscache.  The other way is not possible, though,
because the syscache mechanism depends upon having a matching index.)
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> > I suppose it makes more sense to optimize this on the basis of what's used in
> > the planner and executor rather than ALTER TABLE commands though.
> 
> No, definitely not.  Syscaches only exist to support hard-wired lookups
> in the backend C code.  Indexes on system catalogs are of interest to
> the planner, but not syscaches.  (So it is legitimate to have indexes
> with no associated syscache.  The other way is not possible, though,
> because the syscache mechanism depends upon having a matching index.)

I imagine the planner and/or executor have to execute hard-wired lookups in C
code all day long to check for children of tables before they can execute
queries on those tables. I meant that the performance of those lookups was
undoubtedly more critical than the performance of DDL.

-- 
greg