I'm playing around with PostgreSQL and would like to validate that everything is valid after migrating a data model.
In: how-to-show-all-invalid-objects-in-postgresql it is stated that only indexes need to be checked, but I noticed that I can create a function that references a table that does not exist:
CREATE OR REPLACE FUNCTION DUMMY() RETURNS INT AS $$ BEGIN return( select count(1) from table_that_does_not_exist ); END; $$ LANGUAGE plpgsql;
In the same fashion, I can create a valid function and drop the dependent table without a complaint
Is there a way to detect that a function is in an invalid state, preferably from SQL? Is there other things that can be invalid in the same way (and how to detect that)?
While I'm at it, In Db2 a table has an internal counter that is increased for certain operations (drop column for example). When this counter reaches a certain value (3?) the table becomes in-accessible and needs to be reorganized (vacuum seems to be a similar operation in Postgres). Can a table in Postgres become invalid after a certain number of "dangerous" operations, if so, how would one detect that a table is about to enter an invalid state?
I guess what I'm trying to find out how I can detect if there are any invalid objects in the database before discovering it in run-time.