A Start-To-Finish Set of Examples
Prev |  | Next

## A Start-To-Finish Set of Examples

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.

### The OS Side of Isok Installation

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 curl
    <uninteresting output redacted>
    $ sudo apt install unzip
    <uninteresting output redacted>
    $
    $ # Pick your Isok version
    $ export ISOK_VERSION=0.4.1
    $
    $ # Download pg_isok and install into the OS so the db engine can find it.
    $ curl https://api.pgxn.org/dist/pg_isok/${ISOK_VERSION}/pg_isok-${ISOK_VERSION}.zip > pg_isok-${ISOK_VERSION}.zip
    <uninteresting output redacted>
    $ unzip pg_isok-${ISOK_VERSION}.zip
    <uninteresting output redacted>
    $ cd pg_isok-${ISOK_VERSION}
    $ sudo make install
    <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.

### Database Setup

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;
    SET
    => CREATE SCHEMA workspace;
    CREATE SCHEMA
    => CREATE TABLE countries
         (code TEXT PRIMARY KEY, description TEXT NOT NULL);
    CREATE TABLE
    => INSERT INTO countries (code, description)
         VALUES ('oc', 'Oceania')
              , ('ea', 'Eastasia')
              , ('eu', 'Eurasia');
    INSERT 0 3
    =>

| 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 `workspace`, by default, new tables are created there.

### Our First Query

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 `NULL` in 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.type, 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     | type | 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.type
            , 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     | type | 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 `new_countries` query being created here, and so can be used when querying ISOK_RESULTS to uniquely identify any given row of this query's output. This value is saved in ISOK_RESULTS.QR_ID. We will use the fact that it is a `COUNTRIES.Code` value later in the example.
| Tip
While this example query generates a simple ISOK_RESULTS.QR_ID, often something more complex is needed to ensure these ids have the two necessary requirements, per-query uniqueness and reproducibility. Concatenating multiple values, separated by some delimiter (like “*”), can be a good approach. Alternately, instead of using a delimiter, embedding the same multiple values within a descriptive string is sometimes useful. This approach can bring two levels of detail to your reporting, with the core problem in ISOK_RESULTS.QR_ID and further information in ISOK_RESULTS.QR_Message.
| 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.

### Resolving Warnings

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.type, 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 | type | 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.type, 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     | type | 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 `infinity` tells Isok that we never want to see the warning again. The warning is resolved. 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.

### A Query That Looks For Missing Countries

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

    => --
    => -- 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.type, riq.keep_until
            , riq.qr_id, riq.qr_message, riq.qr_extra
         FROM run_isok_queries($$VALUES ('new_countries')
                                      , ('deleted_countries')
                                   ORDER BY 1$$)
           AS riq
         ORDER BY riq.iqname, riq.qr_id;
     irid |      iqname       | type | 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 `deleted_countries` query, and so is a suitable key component.
| Here, we rely on having set the ISOK_QUERIES.Keep flag in the `new_countries` query. It caused the ISOK_RESULTS.Keep_Until column to be set to `infinity`, so that the ISOK_RESULTS rows are not deleted even when the query no longer returns them. Because the ISOK_RESULTS rows remain, we are able to use their existence to test for deletion of `COUNTRIES` rows. If the ISOK_RESULTS.QR_ID were not a plain `COUNTRIES.Code` value, doing this might require more ingenuity. But this sort of thing should always be possible, given a little forethought.
| Without the `ORDER BY` the order in which the queries are executed is undefined, and therefore the ordering of the results returned is not guaranteed to be consistent.
| 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.

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

Prev |  | Next
How Isok Works | Home | Installation

Page generated: 2025-11-03T13:22:30-06:00.