How to show all invalid objects in PostgresQL

Question

Looking for the view I can list up all 'invalid' objects in PostgreSQL. In Oracle, we can use dab_objects.status column but I'm not sure if there is a simple way to do such a thing in PostgreSQL.

Maybe, I can check the invalid indexes with below code. How can I do that for other objects?

SELECT pg_class.relname 
FROM pg_class, pg_index 
WHERE pg_index.indisvalid = false 
AND pg_index.indexrelid = pg_class.oid;

Laurenz Albe · Accepted Answer · 2018-07-31T06:48:47.713

10

I don't think that you have to check anything else, since other objects cannot become invalid in PostgreSQL.

Oracle and PostgreSQL work quite differently in that respect:

In Oracle, you can always ALTER an object (for example a table) even if there are dependent objects (for example views). The dependent objects then become invalid and have to be recompiled.
In PostgreSQL, you cannot ALTER an object that has dependend objects in a way that renders these dependent objects invalid. You have to drop and recreate the dependent objects.

Invalid indexes can be left behind by a failed CREATE INDEX command.

edited Jul 31 '18 at 06:48

answered Aug 24 '16 at 09:57

Laurenz Albe

209,280
17
206
263

Great explanation. Only index should be considered in this area. Is that correct? – Sigularity Aug 24 '16 at 14:46
As far as I know, yes. – Laurenz Albe Aug 25 '16 at 02:30
@LaurenzAlbeI think I downvoted your answer by mistake. – Tajinder Jul 31 '18 at 05:26
@LaurenzAlbe Actually, I saw today, that I have downvoted the answer yesterday, and it is not allowing me to un-downvote as the vote is already locked and i can change my vote only if the answer is edited, I want to un-downvote the asnwer as answer was useful to me. Sorry for the mistake. – Tajinder Jul 31 '18 at 06:42
Edited so you can do it. – Laurenz Albe Jul 31 '18 at 06:49

How to show all invalid objects in PostgresQL

1 Answers1

Linked