The Isok Main Tables
Prev     Next

In the table descriptions below, each table has it's own section, with sub-sections for the table's columns.

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

All timestamps track the time zone.

The ISOK_QUERIES table 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 ISOK_QUERIES using INSERT statements. This avoids problems that would otherwise arise involving the use of quote characters inside quoted strings.

Example 1. Inserting a query into ISOK_QUERIES using dollar quoting

-- Report a warning when there's a birth date before 1950
INSERT INTO isok_queries (iqname, error, type, keep, query, comment)
  VALUES('mycheck', false, 'bdate', false
       , $$SELECT 'Bad birth date: ' || 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'$$
       , $$Report a warning when there's a birthdate before 1950$$
        );


A timestamp. Date and time the query was first run by Isok. The value of this column is NULL if the query has never been run.

A timestamp. Date and time the query was most recently run by Isok. The value of this column is NULL if the query has never been run.

A BOOLEAN value. This column controls the value placed in the ISOK_RESULTS.Keep_Until column when run_isok_queries() inserts new rows in ISOK_RESULTS.

When this column is TRUE, each row returned by the query is stored in ISOK_RESULTS with a Keep_Until value of infinity. This prevents run_isok_queries() from deleting the query result row when run, when the query no longer returns the result row.

When this column is FALSE, the ISOK_RESULTS.Keep_Until value of any new rows that run_isok_queries() inserts is NULL.

This column may not be NULL.

A PostgreSQL name value. The PostgreSQL role to use to run the query.

Because different roles have differing access to database content, it can be useful to run queries with different roles in effect.

Caution

Setting the role may have security implications.

This column is not validated against existing roles.

Note that the name data type casts (transparently) to TEXT.

When this column is NULL, the effective role is not changed.

A TEXT value. The PostgreSQL schema search_path to have in effect when the query is run.

The syntax of the search path is that used by SET search_path ... and returned by SHOW search_path;.

Because queries may not always contain schema names to qualify database objects, a single query can return different results depending on the search_path in effect. So it can be useful to run different queries when different schema search paths are in effect.

Caution

Setting the search_path may have security implications.

Care must be taken when setting the search path because the search path can be set to anything, regardless of which schemas exist or are available to the user. It is quite easy to set a search path that searches no schemas. PostgreSQL will not produce any warnings or errors should you do so.

When this column is NULL, the schema search path is not changed.

A TEXT value. A query which checks for database integrity violations. The query need not end in a semi-colon. The query must return 3 columns. Although these columns are referred to by name below, the names the query gives to the columns does not matter.

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 cannot enforce the requirement that the first column be consistent over repeated runs of the query. If the query does not satisfy this requirement Isok 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 third column contains JSON data. The purpose of this column is to hold additional data on the reported condition that may need to be tracked, or queried. PostgreSQL is able to efficiently query JSONB data, which is how this column is stored.

Warning

At the time of this writing, in practice, returning a third column is optional. But this behavior should not be relied upon.

Best practice is to return a NULL value for the third column when you do not wish to store any JSON with the query result.

When only 2 columns are returned, the effect is the same as returning a NULL value in the third column.

The third column is optional, in practice, because a portion of of the PostgreSQL PL/pgSQL language is unspecified.[3]Isok cannot feasibly use the text of the Query column to determine how many columns the query returns. So it cannot prevent the query from being written to return only two columns. And, when this is the case, the present PL/pgSQL implementation allows the Query to return two columns instead of three.

Return a NULL value in the third column when there is no JSON data.

The ISOK_RESULTS table contains one row for every database integrity problem discovered by the queries in ISOK_QUERIES. That is to say, one row for every row returned by executed queries. The table'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 ISOK_QUERIES rows having a FALSE Error value, to be resolved -- permanently marked as acceptable conditions. Resolved warnings can be safely ignored thereafter, and since Isok 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 -- ISOK_RESULTS rows must have a NULL Resolved value when the row has a IQName related to an ISOK_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.[4]

The query result id generated by the stored query must be unique per query -- the combination of ISOK_RESULTS.IQName and ISOK_RESULTS.QR_ID must be unique.

A TEXT value. The ISOK_QUERIES.IQName value identifying the query which produced the result.

A timestamp value. Date and time the query result was first produced by Isok. This column may not be NULL.

A timestamp value. Date and time the query result was most recently produced by Isok. This column may not be NULL.

A PostgreSQL name value. The role (user) which was the current role when the query was last executed. Note that the name data type casts (transparently) to TEXT.

This column is not validated against existing roles.

This column may not be NULL.

An array of PostgreSQL name values. All schemas that were, implicitly or not, in the search_path, and also available to the Last_Role, when the result was returned. For more information, see the documentation of the current_schemas() function.

Note that the name data type casts (transparently) to TEXT. This column may not be NULL.

A timestamp value. Date and time the query result was resolved; that is, marked not a concern. The Isok system does not display resolved results, although of course the ISOK_RESULTS table can always be manually queried.

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

A timestamp value. Isok suppresses display of the result when the current time is before this time. Use of this column allows resolution of data integrity problems to be deferred, and hence not clutter up the output of Isok with noise that might hide other problems.

When this column is NULL Isok displays the query result.

A timestamp value. This column controls whether or not run_isok_queries() deletes the row when the ISOK_QUERIES.Query is re-run and the query does not return the row's QR_ID.

A query result that the query no longer returns is kept until the given time is reached, when the value of this column is not NULL. When the value of this column is NULL, a query result row that is no longer returned is is always deleted. For further detail see the Deletion of Old Results section of the run_isok_queries() documentation.

Tip

Using the special TIMESTAMP value of infinity entirely prevents deletion.

A JSONB value. The value of the third, optional, column returned by most recent execution of the query. This may contain any JSON deemed useful. This column serves as a catch-all container for any additional data that needs to be tracked regarding a reported condition.

The value of this column may be NULL. This is the default when the ISOK_QUERIES.Query does not return a third column.

See PostgreSQL's documentation on the JSON data types for information on how to access, index, and efficiently query the JSONB data type.


[3] The unspecified PL/pgSQL behavior being, that the target in a PL/pgSQL statement of the form “FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP” is allowed to contain more variables than the test_expression returns columns, in which case the extra variables are assigned the NULL value. Because this behavior is undocumented, it is subject to change. Should this behavior change, returning a third column will be required, not optional.

[4] To remove an ISOK_RESULTS.Deferred_To value and add a ISOK_RESULTS.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.

Prev     Next
An Overview of the Isok Tables  Home  Isok Support Tables

Page generated: 2025-06-02T22:24:59-05:00.