How to drop inheritance?

Question

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?

• András Váczi: We don't know: which client you use (down arrow is not part of the PostgreSQL server) and what sort of performance issue you have.
• user2486675: am using EMS SQL Manager for PostgreSQL. The functions use this table take long time to execute. We have the same structure and data in Test and Dev but the table was not inherited, the function executes in few seconds. When we called the EMS SQL Manager support team, they said its because of the table been inherited.
• András Váczi: While being probably the most featureful PostgreSQL client, EMS SQL Manager exhibit rather strange behaviour occassionally. Please try what Josh Berkus suggested: connect to the DB with psql and check the inheritance and the function performance there.

Answer 1

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;

Answer 2

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.