3

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.

  • Should this not be posted as two separate questions? – George.Palacios Dec 14 '18 at 13:52
  • There is no such "counter" in Postgres. – a_horse_with_no_name Dec 14 '18 at 13:58
  • @George.Palacios I did consider it, but I came to the conclusion that they would both fit under the title "Find invalid objects" – Lennart Dec 14 '18 at 14:05
  • But won't the answers be completely different? For someone browing the site in future it won't be particularly helpful. – George.Palacios Dec 14 '18 at 14:06
4

Is there a way to detect that a function is in an invalid state, preferably from SQL

For plpgsql, that's the purpose of the plpgsql_check extension.

Otherwise, functions (and now procedures too) can be written in all kinds of languages, so the job of parsing the body of functions is offloaded to these languages. PostgreSQL itself can't know when a function refers to any particular database object: it can't even parse the function's body to start with.

Also, it's arguable that code referencing a not-yet-existing table is necessarily invalid. It may be a temporary table that is meant to always be created before calling the function.

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?

When a column is dropped, it's marked as dropped but still uses a slot in pg_attribute and in the heap. If columns are constantly dropped and new columns are constantly added to a table, at some point a row would try to grow bigger than a page, which is forbidden, even though its number of live columns would be reasonable and would cause no problem in a new table. So in a way, you could say that drop column / add column are operations that can make a table unusable, when used recklessly.

There are certainly other corner cases like that, and every case would need its own detection mechanism.

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.

Framed like that, the question looks too general. In general, objects don't have a valid flag, with some exceptions such as indexes ith pg_index.indisvalid

|improve this answer|||||
  • Thanks, I'll check out the plpgsql and see if its possible to include it in the build chain. – Lennart Dec 14 '18 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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