7

I am new to PostgreSQL. The situation I have is someone created a child table inherits from the parent table. And dropped the child table. But the parent table still has the "down arrow" sign on it.

I checked and there's no other link/relation on the parent table. Is this really an issue?

I was told that the parent table is still in the 'been inherited' status and causing the performance issue. How to resolve this? By removing the 'been inherited' status' from the parent table?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
user2486675
  • 81
  • 1
  • 1
  • 2

2 Answers2

16

In addition to what @Josh already clarified, this quote from the manual should answer the question in the title:

[...] an inheritance link can be removed from a child using the NO INHERIT variant of ALTER TABLE.

ALTER TABLE child NO INHERIT parent;

Either that or just delete the child table.

Also, you can include all inheriting tables when dropping the parent table using the CASCADE key word:

A parent table cannot be dropped while any of its children remain. [...] If you wish to remove a table and all of its descendants, one easy way is to drop the parent table with the CASCADE option.

You can invert this to take advantage of the behaviour and debug your situation. Start a transaction and try to drop the parent table:

BEGIN;
DROP TABLE a;

ERROR:  cannot drop table a because other objects depend on it
DETAIL:  table b depends on table a
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error message lists inheriting tables that block the DROP command.

Unless you get an error (aborting the transaction automatically), be sure to issue a ROLLBACK if you don't actually want to delete the table:

ROLLBACK;
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
7

If you are using a recent version of PostgreSQL (i.e. 9.1 or later), please do the following:

  1. log into your database using the psql command-line client
  2. \d+ <tablename>

... where <tablename> is the name of your parent table. This will give you a list of tables which inherit from your parent table at the bottom of the output.

There is no such thing as a "been inherited" state. Either a table has inheritance children, or it doesn't.

Josh Berkus
  • 1,216
  • 8
  • 6