The Warning Sub-System Main Tables

All date plus time values (timestamps) have a one second precision. Fractions of a second are not recorded.

INTEGRITY_QUERIES

The INTEGRITY_QUERIES contains one row for every query used to search for database integrity issues.

The Last_Run value cannot be before the First_Run value.

Tip

Use PostgreSQL's dollar quoting when inserting queries into INTEGRITY_QUERIES using INSERT statements. This avoids problems that would otherwise arise involving the use of quote characters inside quoted strings.

Example H.1. Inserting a query into INTEGRITY_QUERIES using dollar quoting

INSERT INTO integrity_queries (iqname, error, type, query)
  VALUES('mycheck', false, 'bdate',
         $$SELECT 'Bad birthdate: ' || mytable.id || ', ' || mytable.birthdate
                  AS id
                , 'Id ('
                  || mytable.id
                  || ') has a birthdate ('
                  || mytable.birthdate
                  || ') before 1950'
                  AS msg
             FROM mytable
             WHERE mytable.birthdate < '1950-01-01'$$
        );


IQName (Integrity Query Name)

A unique name for the query. The IQName value cannot be changed. This column may not be empty, it must contain characters, and it must contain at least one non-whitespace character. This column may not be NULL. This column may not contain whitespace characters. The IQName value may contain no more than 15 characters.

Error

A Boolean value. TRUE when the query finds conditions that are errors, FALSE when the query finds conditions that are warnings. See INTEGRITY_WARNINGS (and the Introduction to the Warning Sub-System) for more on warnings and errors.

This column may not be NULL.

Type

Code classifying the query. The legal values for this column are defined by the IQTYPES support table.

This column may not be NULL.

First_Run

Date and time the query was first run by the warning sub-system. NULL if the query has never been run.

Last_Run

Date and time the query was most recently run by the warning sub-system. NULL if the query has never been run.

Query

A query which checks for database integrity violations. The query need not end in a semi-colon. The query must return 2 columns, both of type TEXT.

The first returned column, the ID column

The first column is used as an id. It must contain a unique value. (Unique per results returned by the given query). The value must also be constant; repeated runs of the query which find the same problem must return a consistent value.

Caution

The system can not enforce the requirement that the first column be consistent over repeated runs of the query. If the query does not satisfy this requirement the warning sub-system will generate duplicates of previously reported problems.

The value of the first column may not be NULL or the empty string.

Guidelines for the value of the first column are that it should be human readable and relatively short. It should probably contain id values in order to ensure uniqueness, but only those that will not change over time.

The value of this first column may need to be typed in or otherwise referenced by a person in order to make notes regarding the problem or to change the problem's status.

The second returned column, the Msg column

The second column contains a message describing the discovered database integrity problem. It should contain a complete description of the problem and may be as verbose as necessary.

The value of the second column may not be NULL or the empty string.

Comment

A textual comment regards the query. This may be as verbose as necessary. This column may not be empty, it must contain characters, and it must contain at least one non-whitespace character. This column may contain NULL when no comment is desired.

INTEGRITY_WARNINGS (Warning Sub-System Results)

The INTEGRITY_WARNINGS table contains one row for every database integrity problem discovered by the queries in INTEGRITY_QUERIES. It's purpose is twofold. It provides an efficient way to list data integrity problems, without having to execute the potentially complex queries which discover the problems. But it's main purpose is to allow warnings, i.e. those problems discovered by the queries saved in INTEGRITY_QUERIES rows having a FALSE Error value, to be resolved -- permanently marked as acceptable conditions. Resolved warnings can be safely ignored thereafter, and since the warning sub-system automatically ignores resolved warnings those responsible for maintaining database integrity need not repeatedly concern themselves with resolved conditions.

To resolve a warning place a timestamp in the Resolved column.

Data integrity errors can not be resolved, the erroneous data condition must be fixed -- INTEGRITY_WARNINGS rows must have a NULL Resolved value when the row has a IQName related to an INTEGRITY_QUERIES row having a TRUE Error value.

The Last_Seen value, the Resolved value, and the Deferred_To value cannot be before the First_Seen value.

A resolved warning cannot be deferred -- either Resolved or Deferred_To, or both, must be NULL.[322]

The warning id generated by the stored query must be unique per query -- the combination of INTEGRITY_WARNINGS.IQName and INTEGRITY_WARNINGS.Warning_Id must be unique.

IWID (Integrity Warning Identifier)

An integer uniquely identifying the row containing the result of a database integrity query. The IWID value cannot be changed.

IQName (Integrity Query Name)

The INTEGRITY_QUERIES.IQName value identifying the query which produced the result.

First_Seen

Date and time the query result was first produced by the warning sub-system. This column may not be NULL.

Last_Seen

Date and time the query result was most recently produced by the warning sub-system. This column may not be NULL.

Resolved (Date and Time Resolved)

Date and time the query result was resolved; i.e. marked not a concern. The warning sub-system does not display resolved results, although of course the INTEGRITY_WARNINGS table can always be manually queried.

The value of this column is NULL both when the query result is a data integrity error and when it is a data integrity warning that has not yet been resolved.

Deferred_To

Date and time before which the warning sub-system will not display the result. Use of this column allows resolution of data integrity problems to be deferred, and hence not clutter up the output of the warning sub-system with noise that might hide other problems.

When this column is NULL the warning sub-system will display the query result.

Category

Code classifying the query result. The legal values for this column are defined by the WARNING_REMARKS support table.

This column may be NULL when the query result is unclassified.

Warning_Id

This is a unique, unique per query that is, identifier for the query result. It is the first column produced by the related INTEGRITY_QUERIES.Query.

This column may not be NULL.

Once given a value, the content of this column may not be altered except by a username with administrative authority.

Warning_Message

This is the message, the second column, produced by the INTEGRITY_QUERIES.Query.

Once given a value, the content of this column may not be altered except by a username with administrative authority.

Notes

Any textual notes regarding this particular query result. This column may be NULL when there are no such notes. This column may not be empty, it must contain characters, and it must contain at least one non-whitespace character.



[322] To remove an INTEGRITY_WARNINGS.Deferred_To value and add a INTEGRITY_WARNINGS.Resolved value without raising an error either update both values in the same UPDATE statement or first set the Deferred_To value to NULL and then the Resolved value to something non-NULL.


Page generated: 2022-09-15T15:33:45-04:00.