Contents
Name
run_isok_queries — execute one or more of the queries stored in the ISOK_QUERIES table
Synopsis
TABLE (irid, iqname, first_seen, last_seen, category,
qr_id, qr_message, notes)
run_isok_queries
(
|
); |
TABLE (irid, iqname, first_seen, last_seen, category,
qr_id, qr_message, notes)
run_isok_queries
(
|
iqname_query); |
TEXT
iqname_query
;Input
iqname_query
The text of an SQL query. The query must return a single column of ISOK_QUERIES.IQName values.
Description
A function which runs the queries stored in the ISOK_QUERIES table, returns the output of the stored queries, and stores the results in the ISOK_RESULTS table. Because the function returns rows and columns it must be invoked in the FROM clause of a SELECT statement. (See the Examples below.)
The function may be called in one of two ways. When called with no arguments all of the queries in ISOK_QUERIES are run. When called with the text of an SQL query, a query which returns a single column containing ISOK_QUERIES.IQName values, the function runs only those queries.
Tip
Use PostgreSQL's dollar quoting when supplying a query to run_isok_queries().
The function returns a set of columns with multiple rows, a table. So it is expected to be used in the FROM clause of a SELECT statement. The columns returned by the function are the columns of the ISOK_RESULTS table, excepting the Resolved column.
The rows returned by the function are those of the newly
updated ISOK_RESULTS table, excepting
those rows with a non-NULL
Resolved column or those
rows with a Deferred_To
value that is in the future. Only those rows that are related
to the executed queries (in
ISOK_QUERIES) are returned. So, when
called with no arguments the function returns all warnings
that have not been resolved and all errors. When called with
a query that selects specific
ISOK_QUERIES to execute, only the
unresolved warnings and errors discovered by the executed
ISOK_QUERIES are returned.
The Record of Query Execution
Running an ISOK_QUERIES.Query does more than add new rows to the ISOK_RESULTS table. Updates are made to existing rows to record and track the query execution's results.
The ISOK_QUERIES.Last_Run value is updated.
On ISOK_RESULTS, the rows to update are found by matching the ISOK_RESULTS.IQName value with the ISOK_QUERIES.IQName of the executed query, while also matching the QR_ID value with the value returned in the first column of the executed query. The columns updated are: Last_Seen , Last_Role , Last_Schemas , QR_Message , and QR_Extra.
Because the record of the results produced by Isok queries are updated, a query may be refined over time to produce enough information to resolve the reported issues.
Even though the execution of run_isok_queries() does not return rows that are resolved, all rows returned by an executed query have all the aforementioned columns updated to new values. Whether a row is returned or not does not matter, the update occurs anyway.
Deletion of Old Results
If an existing ISOK_RESULTS row
matches the IQName value of the
executed query and there is no
corresponding QR_ID value
returned by the executed query, and the value of ISOK_RESULTS.Keep_Until is either NULL or
CURRENT_TIMESTAMP[5] is not earlier than ISOK_RESULTS.Keep_Until then the ISOK_RESULTS row is deleted. This empties the
ISOK_RESULTS table of errors and warnings
that no longer apply to the current state of the
database.
If the query returns warnings, this deletion behavior does not depend upon whether or not the warning is resolved.
Examples
The following example runs all the queries in ISOK_QUERIES, displays all the errors and all the unresolved warnings (unless the error or warning has been deferred), ordered first by the name of the query, within that showing newer problems first, and within that ordered by warning id.
Example 2. Executing all ISOK_QUERIES
SELECT *
FROM run_isok_queries() AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
The following example runs a single saved query with an
ISOK_QUERIES.IQName
of mycheck and displays any of these sorts
of problems found, ordered as in the previous example. This
example also demonstrates how to use dollar
quoting to give
a query to run_isok_queries and thereby
avoid problems having to do with trying to nest regular
quotes.
Example 3. Executing a single ISOK_QUERIES.Query
SELECT *
FROM run_isok_queries($$SELECT 'mycheck'$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
The following example runs multiple specific queries,
those with an ISOK_QUERIES.IQName of mycheck,
yourcheck, and
theircheck, and displays any of these sorts
of problems found, ordered as in the previous example.
As before, dollar
quoting is used to quote the query which
produces the IQNames.
Example 4. Executing many specific ISOK_QUERIES.Query-s
SELECT *
FROM run_isok_queries($$VALUES ('mycheck')
, ('yourcheck')
, ('theircheck')$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
The following example runs all the queries of the bdate type and displays any of these sorts of problems found, ordered as in the previous example. Again, dollar quoting is used.
Example 5. Executing ISOK_QUERIES of the “bdate” type
SELECT *
FROM run_isok_queries(
$$SELECT isok_queries.iqname
FROM isok_queries
WHERE isok_queries.type = 'bdate'$$
) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
[5] The time the current transaction started, which, if a transaction was not explicitly started, is the time the database engine received the current SQL statement from the client and began execution.
Page generated: 2025-06-02T23:29:41-05:00.