PostgreSQL – How to Find Invalid Objects in Database

postgresql

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.

Best Answer

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

Related Solutions

Postgresql – How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup

You asked:

how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.

pg_start_backup() ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.

If the data is old, the log will update it..

If the data is new, the log will have same content. There is no hurt writing it again.

The data are never newer then the log, because the logs are write ahead (WAL).

You asked:

... xfs-freeze ...

xfs-freeze is alike to pg_start_backup(), it don't take a snapshot. You need a volume manager to do that.

You asked:

... why do create tablespace & create database statements are unsupported if the WAL can replay everything?

It is supported, just some little gotcha. See http://www.postgresql.org/docs/8.1/static/backup-online.html :

23.3.5. Caveats

CREATE TABLESPACE commands are WAL-logged with the literal absolute path, and will therefore be replayed as tablespace creations with the same absolute path. This might be undesirable if the log is being replayed on a different machine. It can be dangerous even if the log is being replayed on the same machine, but into a new data directory: the replay will still overwrite the contents of the original tablespace. To avoid potential gotchas of this sort, the best practice is to take a new base backup after creating or dropping tablespaces.

Postgresql – Dynamic access to record column in plpgsql function

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.

Here is a demo how to get around this:

CREATE TYPE mytype AS (id int, txt text);

DO
$body$
DECLARE
    _val    mytype  := (1, NULL)::mytype;
    _name   text    := 'txt';
    _isnull boolean;
BEGIN
    EXECUTE 'SELECT $1.' || quote_ident(_name) || ' IS NULL'
    USING _val
    INTO _isnull;

    IF _isnull THEN
        RAISE NOTICE 'Column "%" cannot be null', _name;
    END IF;
END;
$body$

Improved with with @Jack's idea in the comment.

You cannot use plpgsql built-in FOUND because it is not set by EXECUTE (except for RETURN QUERY EXECUTE - more here). That's why I used GET DIAGNOSTICS ... initially. But finally simplified with @Jack's idea.

quote_ident() makes sure the name is syntactically valid and protects against SQLi.

Best Answer

Related Solutions

Postgresql – How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup

Postgresql – Dynamic access to record column in plpgsql function

Related Question