Contents
These examples demonstrate looking for unexpected new country codes, or for the unexpected disappearance of an existing country code.
The examples, or at least the installation related portions, must be run on the machine that is running the PostgreSQL server, the server running the database's backend engine.
Each example expects the code shown in previous examples to have been executed.
Begin by installing Isok into the OS, and connecting to a database.
$ # $ # Install Isok $ # $ $ # Install the required shell commands $ # (If you are on an RPM based system, use the `dnf` command instead of $ # the `apt` command.) $ sudo apt install make<uninteresting output redacted> $ sudo apt install pgxnclient
<uninteresting output redacted> $ $ # Download and install pg_isok so the db engine can find it. $ sudo pgxn install pg_isok
<uninteresting output redacted> $ $ # $ # Interact with a database $ # $ psql
psql (15.13 (Debian 15.13-0+deb12u1)) Type "help" for help. => \pset pager
Pager usage is off. =>
|
|
The sudo command is used here by way of example. The point is, these commands must be run with elevated permissions. |
|
|
The psql command may well need additional arguments supplied in order to connect to the right database server, to the right database, as the right user, and so forth. |
|
|
For purposes of the example, send all output directly to the screen, rather than to an interactive pager. |
Next, install Isok and create some sample data to be used for testing. At the end of this step we will be ready to work with Isok.
=> -- => -- Install pg_isok, in a schema called "isok" => -- => CREATE SCHEMA isok; CREATE SCHEMA => CREATE EXTENSION pg_isok SCHEMA isok; CREATE EXTENSION => => -- => -- Set up a "workspace" for the example, with sample data => -- => SET search_path TO workspace, isok;
|
|
This is for convenience, so that table names, and so forth, do not have to be qualified with the schema in which the table exists. Because the search path begins with
|
Our first goal is to configure Isok so that it will tell us when a new country is put into the system. To do that, we give Isok a query that searches for new countries. Then, we see how to use Isok to run the query, and see what running it does.
The queries given to Isok must return three columns.
- QR_ID
-
A value that is, per-query, unique.
This value, together with the the query identifier, is used as the key to identify a specific reported problem. You will need to use the key to work with particular problems. For example when telling Isok to “resolve” some problem, to prevent the problem from appearing on future reports.
- QR_Message
-
Text that fully describes the problem.
- QR_Extra
-
Any other information about the problem that needs to be stored. Only more advanced users will want to return a value in this column. Most of the time your queries will return
NULLin this column.
As in the example below, when writing INSERT statements to save your queries in Isok it is best to use dollar quoting to quote your queries.
=> --
=> -- Prepare Isok for use
=> --
=>
=> -- Create the vocabulary used to classify Isok queries.
=> INSERT INTO iq_types (iqtype, description)
VALUES ('code_chk', 'Check the system''s codes');
INSERT 0 1
=> -- Save a query that looks for new country codes.
=> INSERT INTO isok_queries (iqname, error, type, keep, query, comment)
VALUES('new_countries' -- iqname
, false -- error
, 'code_chk' -- type, from the IQ_TYPES table
, true -- keep
, $$SELECT countries.code
, 'Unexpected new country in COUNTRIES: Key (Code) = ('
|| countries.code
|| '), Value (Description) = ('
|| countries.description
|| ')'
, NULL
FROM countries
ORDER BY countries.code$$ 
-- query
, 'Find all the countries, identify them by code' -- comment
);
INSERT 0 1
=>
=> --
=> -- Initial run of Isok, show all the "new" countries.
=> --
=> SELECT riq.irid, riq.iqname, riq.category, riq.keep_until
, riq.qr_id, riq.qr_message, riq.qr_extra
FROM run_isok_queries($$VALUES ('new_countries')$$) 
AS riq
ORDER BY riq.iqname, riq.qr_id;
irid | iqname | category | keep_until | qr_id | qr_message | qr_extra
------+---------------+----------+------------+-------+------------------------------------------------------------------------------------------+----------
2 | new_countries | | infinity | ea | Unexpected new country in COUNTRIES: Key (Code) = (ea), Value (Description) = (Eastasia) |
3 | new_countries | | infinity | eu | Unexpected new country in COUNTRIES: Key (Code) = (eu), Value (Description) = (Eurasia) |
1 | new_countries | | infinity | oc | Unexpected new country in COUNTRIES: Key (Code) = (oc), Value (Description) = (Oceania) |
(3 rows)
=> -- See that the above results have been saved in ISOK_RESULTS.
=> SELECT isok_results.irid, isok_results.iqname, isok_results.category
, isok_results.keep_until, isok_results.qr_id
, isok_results.qr_message, isok_results.qr_extra
FROM isok_results
ORDER BY isok_results.iqname, isok_results.qr_id;
irid | iqname | category | keep_until | qr_id | qr_message | qr_extra
------+---------------+----------+------------+-------+------------------------------------------------------------------------------------------+----------
2 | new_countries | | infinity | ea | Unexpected new country in COUNTRIES: Key (Code) = (ea), Value (Description) = (Eastasia) |
3 | new_countries | | infinity | eu | Unexpected new country in COUNTRIES: Key (Code) = (eu), Value (Description) = (Eurasia) |
1 | new_countries | | infinity | oc | Unexpected new country in COUNTRIES: Key (Code) = (oc), Value (Description) = (Oceania) |
(3 rows)
=>
|
|
The result rows the query produces are not errors, they are warnings. Errors are not very interesting, reports always show errors. But interesting things can be done with warnings, as will be seen. |
|||
|
|
Keep the result rows in ISOK_RESULTS, even if the row is not (re)produced when the query is re-run. The utility of this should become clear below. |
|||
|
|
The country code is unique, among the query results
produced by the This value is saved in ISOK_RESULTS.QR_ID.
We will use the fact that it is a
|
|||
|
|
The explanatory message that makes clear why the row is showing up as a warning. This value is saved in ISOK_RESULTS.QR_Message. |
|||
|
|
This value is saved in ISOK_RESULTS.QR_Extra. |
|||
|
|
It is good practice to write your queries to ORDER BY something unique, so that the results are always delivered in a consistent order. |
|||
|
|
Because there's only one query, we could simply not give run_isok_queries() an argument, invoking it as run_isok_queries(). This runs “all queries”, that is, the single query we have. But it seems better practice to be explicit and pass run_isok_queries() the query we want to run. |
![[Note]](../images/note.png)
We've seen, above, that the first time Isok runs our query, it reports that every country is a new country. But the countries we have are the countries we want, so we want to resolve the issues reported with our existing countries.
To resolve each reported warning, we tell Isok that we
never want it to report the warning again.
ISOK_RESULTS is where our reported problems
are stored, as rows in the table.
We mark each row produced by the
new_countries query, telling Isok that we
never want to see the row again.
This is done by setting ISOK_RESULTS.Deferred_To to
infinity.
With that done, Isok won't show us the rows until the end of
time.
After resolving our issues the system is “mature”, in that Isok has been integrated into our operations and its tables reflect the current state of the database. It knows that the countries that already exist should exist, and no longer reports them as problems.
=> --
=> -- Tell Isok that the "new" countries are all acceptable, we don't
=> -- need to see them when looking for problems.
=> --
=> UPDATE isok_results
SET deferred_to = 'infinity'
WHERE iqname = 'new_countries';
UPDATE 3
=>
=> -- The accepted countries don't show when we re-run the query.
=> SELECT riq.irid, riq.iqname, riq.category, riq.keep_until
, riq.qr_id, riq.qr_message, riq.qr_extra
FROM run_isok_queries($$VALUES ('new_countries')$$)
AS riq
ORDER BY riq.iqname, riq.qr_id;
irid | iqname | category | keep_until | qr_id | qr_message | qr_extra
------+--------+----------+------------+-------+------------+----------
(0 rows)
=> --
=> -- New countries show up when we re-run our query, but not the ones
=> -- we've accepted.
=> --
=>
=> -- Insert a new country
=> INSERT INTO countries (code, description)
VALUES ('mv', 'Margaritaville');
INSERT 0 1
=>
=> -- Run our query again, looking for problems.
=> SELECT riq.irid, riq.iqname, riq.category, riq.keep_until
, riq.qr_id, riq.qr_message, riq.qr_extra
FROM run_isok_queries($$VALUES ('new_countries')$$)
AS riq
ORDER BY riq.iqname, riq.qr_id;
irid | iqname | category | keep_until | qr_id | qr_message | qr_extra
------+---------------+----------+------------+-------+------------------------------------------------------------------------------------------------+----------
10 | new_countries | | infinity | mv | Unexpected new country in COUNTRIES: Key (Code) = (mv), Value (Description) = (Margaritaville) |
(1 row)
=>
|
|
Using If, instead, you wanted to put off figuring out what to do about some particular warning, you could defer its reporting until some future date. |
|
|
The exciting part here, the whole point of the exercise, is that the countries we started out with don't re-appear in the report. Their existence only has to be reviewed once, no matter how many time the saved query is re-run. |
|
|
The new country shows up on our problem report. |
How do we detect that a country has gone missing?
After Isok has run the new_countries
query at least once, the ISOK_RESULTS.QR_ID column contains every existing
country code.
And, because we set the the new_countries
query's ISOK_QUERIES.Keep value to
infinity, the
new_countries query's rows are not removed
from ISOK_RESULTS, even when the query no
longer returns the row.
So, we can query ISOK_RESULTS, comparing it
to what's in the COUNTRIES table, to find
country codes that should exist, but don't. This section of the
example does exactly that.[4]
=> --
=> -- Show how to detect deleted countries
=> --
=>
=> -- Give Isok a query that finds deleted countries
=>
=> INSERT INTO isok_queries (iqname, error, type, keep, query, comment)
VALUES('deleted_countries' -- iqname
, false -- error
, 'code_chk' -- type, from the IQ_TYPES table
, false -- keep
, $$SELECT isok_results.qr_id
, 'Unexpected deletion from COUNTRIES: Key (Code) = ('
|| isok_results.qr_id
|| ')'
, NULL
FROM isok_results
WHERE isok_results.iqname = 'new_countries'
AND NOT EXISTS
(SELECT 1
FROM countries
WHERE countries.code = isok_results.qr_id)
ORDER BY isok_results.qr_id$$ -- query
, 'Report deleted COUNTRIES.Code values' -- comment
);
INSERT 0 1
=>
=> -- Delete a country
=> DELETE
FROM countries
WHERE code = 'eu';
DELETE 1
=>
=> -- Run both queries, to find both the new country and the deleted country.
=> SELECT riq.irid, riq.iqname, riq.category, riq.keep_until
, riq.qr_id, riq.qr_message, riq.qr_extra
FROM run_isok_queries($$VALUES ('new_countries')
, ('deleted_countries')$$)
AS riq
ORDER BY riq.iqname, riq.qr_id;
irid | iqname | category | keep_until | qr_id | qr_message | qr_extra
------+-------------------+----------+------------+-------+------------------------------------------------------------------------------------------------+----------
18 | deleted_countries | | | eu | Unexpected deletion from COUNTRIES: Key (Code) = (eu) |
9 | new_countries | | infinity | mv | Unexpected new country in COUNTRIES: Key (Code) = (mv), Value (Description) = (Margaritaville) |
(2 rows)
=>
|
|
Again, the country code is unique, among the query
results produced by the |
|
|
Here, we rely on having set the ISOK_QUERIES.Keep flag in the Because the ISOK_RESULTS rows
remain, we are able to use their existence to test for
deletion of |
|
|
With some ingenuity, likely involving the ISOK_RESULTS.QR_Extra column, the message could be made more informative. Whether this is worth doing is up to the reader. |
[4] Because Isok can run multiple queries in a single invocation, the careful reader might wonder whether interactions between queries can produce inaccurate results. Indeed, if a query in ISOK_QUERIES references ISOK_RESULTS, there can be interactions.
In the case we're considering, detecting deleted countries, it does not matter.
In other cases, it is possible that
some query might put rows into ISOK_RESULTS, confusing a query run afterward that uses ISOK_RESULTS, although it is hard to imagine such
a situation.
In any case, should query ordering matter, testing ISOK_RESULTS.Last_Seen against
CURRENT_TIMESTAMP, which remains constant
throughout an execution of run_isok_queries(), might help avoid the problem.
Page generated: 2025-06-16T16:33:16-05:00.