Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  dezso Jun 15 '13 at 5:08
    
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. –  user2486675 Jun 15 '13 at 7:05
    
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. –  dezso Jun 15 '13 at 7:46
add comment

migrated from stackoverflow.com Jun 15 '13 at 1:10

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
add comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.