The Isok Main Tables
Prev |  | Next

## The Isok Main Tables

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.

### ISOK_QUERIES

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$$
);

#### IQName (Isok Query Name)

A `TEXT` value. 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. This column must be unique when compared in a case-insensitive fashion.

#### Error

A `BOOLEAN` value. `TRUE` when the query finds conditions that are errors, `FALSE` when the query finds conditions that are warnings. See ISOK_RESULTS (and the Introduction to Isok) for more on warnings and errors.

This column may not be `NULL`.

#### Type

A `TEXT` value. Code classifying the query. The legal values for this column are defined by the IQ_TYPES support table.

This column may not be `NULL`.

#### First_Run

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.

#### Last_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.

#### Keep (Keep old results)

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`.

#### Role

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.

#### Search_Path

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.

#### Query

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 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 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 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.

##### The third returned column, the Extra JSON column

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.

#### Comment

A `TEXT` value. A comment on the query. This may be as verbose as necessary. This column may not be `NULL`. This column may be empty; it need not contain characters, but it may not contain only whitespace characters..

### ISOK_RESULTS (Isok query Results)

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.

#### IRID (Integrity Results Identifier)

An `BIGINT` value This column uniquely identifies the row containing the result of a database integrity query. The IRID value cannot be changed and is automatically generated with a PostgreSQL sequence.

#### IQName (Integrity Query Name)

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

#### First_Seen

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

#### Last_Seen

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

#### Last_Role

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`.

#### Last_Schemas

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`.

#### Resolved (Date and Time Resolved)

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.

#### Deferred_To

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.

#### Category

A `TEXT` value. Code classifying the query result. The legal values for this column are defined by the IR_TYPES support table.

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

#### Keep_Until

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.

#### QR_ID (Query Result IDentifier)

A `TEXT` value. This is a unique, unique per query that is, identifier for the query result. It is the first column produced by the related ISOK_QUERIES.Query.

This column may not be `NULL`.

#### QR_Message (Query Result Message)

A `TEXT` value. This is the message, the second column, produced by the most recent execution of the ISOK_QUERIES.Query.

#### QR_Extra (Query Result Extra JSON data)

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.

#### Notes

A `TEXT` value. Any notes regarding this particular query result. This column may not be `NULL`. This column may be empty; it need not contain characters, but it may not contain only whitespace characters.

[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.