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, type,
qr_id, qr_message, notes)
run_isok_queries
(
|
); |
TABLE (irid, iqname, first_seen, last_seen, type,
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 -- excepting resolved and deferred rows, and stores the results in the ISOK_RESULTS table. Because the function returns rows and columns it is expected 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]](../images/tip.png) |
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 returned by the queries the function
executes, excepting resolved and deferred rows.
A row is resolved or deferred when there is a row in ISOK_RESULTS that has a IQName value matching that of the
query and a QR_ID value
matching that of the row, and that ISOK_RESULTS row has a non-NULL Resolved column or a Deferred_To value that is in the
future.
So, when called with no arguments the function returns all warning conditions that currently exist in the data, that have not been resolved or deferred, and all error conditions that currently exist in the data. When called with a query that selects specific ISOK_QUERIES to execute, only the unresolved, undeferred, warnings and errors discovered by the executed ISOK_QUERIES are returned.
Query Execution Order
When run_isok_queries() is called with no arguments, the queries are run in ISOK_QUERIES.IQName order, sorted lexically. When called with the text of an SQL query, the function runs the queries with the produced ISOK_QUERIES.IQNames, in the order given.
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[10] 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, undeferred warnings, 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
when supplying a a query as an argument to
run_isok_queries, which thereby avoids
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')
ORDER BY 1$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
Notice that the query used to select the queries to
execute has an ORDER BY clause.
Without such a clause, the order in which the queries are run,
and so the ordering of the rows returned, is
unspecified.
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'
ORDER BY isok_queries.iqname$$
) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;
[10] 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-11-03T13:07:26-06:00.