All date plus time values (timestamps) have a one second precision. Fractions of a second are not recorded.
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.
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'$$
);
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.
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.
Code classifying the query. The legal values for this column are defined by the IQTYPES support table.
This column may not be NULL.
Date and time the query was first run by the warning
sub-system. NULL if the query has never been run.
Date and time the query was most recently run by the
warning sub-system. NULL if the query has never been
run.
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 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.
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 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.[279]
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.
An integer uniquely identifying the row containing the result of a database integrity query. The IWID value cannot be changed.
The INTEGRITY_QUERIES.IQName value identifying the query which produced the result.
Date and time the query result was first
produced by the warning sub-system. This column may not be NULL.
Date and time the query result was most recently
produced by the warning sub-system. This column may not be NULL.
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.
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.
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.
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.
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.
[279] 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.