dnl pg_isok  A query centered monitoring tool for PostgreSQL
dnl Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
dnl
dnl  This program is free software: you can redistribute it and/or modify
dnl  it under the terms of the GNU Affero General Public License as published
dnl  by the Free Software Foundation, either version 3 of the License, or
dnl  (at your option) any later version.
dnl
dnl  This program is distributed in the hope that it will be useful,
dnl  but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
dnl  GNU Affero General Public License for more details.
dnl
dnl  You should have received a copy of the GNU Affero General Public License
dnl  along with this program.  If not, see <https://www.gnu.org/licenses/>.
dnl
dnl Run_isok_queries function.
dnl
dnl Karl O. Pinc <kop@karlpinc.com>
dnl
dnl
dnl Remarks:
dnl   If the m4 macro `isok_disable_role' is defined, the code related
dnl   to SET ROLE is omitted.
dnl
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
dnl

dnl plpgsql fragment to define columns returned
dnl
dnl Syntax: _return_cols()
dnl
dnl
changequote({,})
define({_return_cols},{
    irid              @extschema@.isok_results.irid%TYPE
  , iqname            @extschema@.isok_results.iqname%TYPE
  , first_seen        @extschema@.isok_results.first_seen%TYPE
  , last_seen         @extschema@.isok_results.last_seen%TYPE
  , last_role         @extschema@.isok_results.last_role%TYPE
  , last_schemas      @extschema@.isok_results.last_schemas%TYPE
  , deferred_to       @extschema@.isok_results.deferred_to%TYPE
  , category          @extschema@.isok_results.category%TYPE
  , keep_until        @extschema@.isok_results.keep_until%TYPE
  , qr_id             @extschema@.isok_results.qr_id%TYPE
  , qr_message        @extschema@.isok_results.qr_message%TYPE
  , qr_extra          @extschema@.isok_results.qr_extra%TYPE
  , notes             @extschema@.isok_results.notes%TYPE
})dnl
changequote(`,')dnl


dnl plpgsql fragment to put exception diagnostic info into vars.
dnl
dnl Syntax: _get_exception_info(msg, detail, hint, context)
dnl
dnl Input:
dnl  names of variables in which to save exception info
dnl
changequote({,})
define({_get_exception_info},{
        GET STACKED DIAGNOSTICS
            $1 = MESSAGE_TEXT
          , $2 = PG_EXCEPTION_DETAIL
          , $3 = PG_EXCEPTION_HINT
          , $4 = PG_EXCEPTION_CONTEXT;
        
        $1 := E'\n' || $1;
        IF $2 <> '' THEN
           $1 := $1 || E'\n';
           $2 := E'\nThe detail is:\n' || $2;
        END IF;
        IF $3 <> '' THEN
           $2 := $2 || E'\n';
           $3 := E'\nThe hint is:\n' || $3;
        END IF;
        $3 := $3 || E'\n';
        IF $4 <> '' THEN
           $4 := E'\n\nAdditional diagnostic information:\n' || $4;
        END IF;
})dnl
changequote(`,')dnl


dnl plpgsql fragment for the list of exceptions trapped when executing
dnl user-supplied sql.
dnl
dnl Syntax: _user_sql_exceptions()
dnl
dnl We want all of these to trap most that could go wrong when a
dnl user-supplied bit of sql is executed:
dnl  feature_not_supported  (0A000)
dnl  data_exception  (22000)  bad expressions (divide by 0, etc.)
dnl  integrity_constraint_violation (23000)  violating insert/update
dnl  invalid_sql_statement_name (26000)
dnl  sql_routine_exception (2F000)
dnl  syntax_error_or_access_rule_violation (42000)
dnl
changequote({,})
define({_user_sql_exceptions},{feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation})dnl
changequote(`,')dnl


CREATE OR REPLACE FUNCTION run_isok_queries()
  RETURNS TABLE(_return_cols())
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run all queries in ISOK_QUERIES.
  --
  -- AGPL_notice(`  --', `2015, 2025',
                 `The Meme Factory, Inc.  http://www.karlpinc.com/')
  --
  -- Syntax: run_isok_queries()
  --
  -- Arguments:
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE

  BEGIN

  RETURN QUERY EXECUTE 'SELECT * FROM @extschema@.run_isok_queries(
                          $RIQ$SELECT @extschema@.isok_queries.iqname
                                 FROM @extschema@.isok_queries
                                 ORDER BY @extschema@.isok_queries.iqname$RIQ$
                                 )';
  END;
$$;


dnl Change the m4 quotes so we can use the ifdef macro with quotes
changequote({,})dnl
CREATE OR REPLACE FUNCTION run_isok_queries(query TEXT)
  RETURNS TABLE(_return_cols())
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run the queries in ISOK_QUERIES that have an IQName returned by
  -- the SQL statement given as an argument.
  --
  -- AGPL_notice({  --}, {2015, 2025},
                 {The Meme Factory, Inc.  http://www.karlpinc.com/})
  --
  -- Syntax: run_isok_queries(query)
  --
  -- Arguments:
  --   query  A query returning 1 column, of INTEGRITY_QUERY.IQName values.
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE

    -- From query results
    this_iqname @extschema@.isok_queries.iqname%TYPE;
    this_keep @extschema@.isok_queries.keep%TYPE;
ifdef({isok_disable_role},{},{dnl
    saved_role @extschema@.isok_queries.role%TYPE;
}){}dnl
ifdef({isok_disable_search_path},{},{dnl
    saved_search_path @extschema@.isok_queries.search_path%TYPE;
}){}dnl
    saved_query @extschema@.isok_queries.query%TYPE;

    -- Dynamic commands
ifdef({isok_disable_search_path},{},{dnl
    starting_search_path TEXT;
}){}dnl
ifdef({isok_disable_role},{},{dnl
    role_used name;
    become_starting_role TEXT;
    become_saved_role TEXT;
}){}dnl

    -- Saved query results
    id TEXT;
    msg TEXT;
    extra JSONB;

    -- Exception handling
    my_message_text TEXT;
    my_pg_exception_detail TEXT;
    my_pg_exception_hint TEXT;
    my_pg_exception_context TEXT;

    exception_caught BOOLEAN := FALSE;

  BEGIN
    -- Run the supplied query and iterate over the saved queries.
    FOR this_iqname IN EXECUTE query LOOP

      -- Keep track of which query results we receive from the query
      CREATE TEMP TABLE _riq_qr_ids AS
        SELECT @extschema@.isok_results.qr_id
          FROM @extschema@.isok_results
        WITH NO DATA;

      -- Get the saved query
      SELECT
             @extschema@.isok_queries.query, @extschema@.isok_queries.keep
ifdef({isok_disable_search_path},{},{dnl
           , @extschema@.isok_queries.search_path
}){}dnl
ifdef({isok_disable_role},{},{dnl
           , @extschema@.isok_queries.role
}){}dnl
        INTO
             saved_query                   , this_keep
ifdef({isok_disable_search_path},{},{dnl
           , saved_search_path
}){}dnl
ifdef({isok_disable_role},{},{dnl
           , saved_role
}){}dnl
        FROM @extschema@.isok_queries
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      IF NOT FOUND THEN
        RAISE EXCEPTION data_exception USING
          MESSAGE = 'Error in run_isok_queries(TEXT)'
          , DETAIL = 'Supplied query returned a value ('
                     || textualize({this_iqname})
                     || ') that is not an existing'
                     || ' ISOK_QUERIES.IQName value';
        RETURN;
      END IF;

ifdef({isok_disable_search_path},{},{dnl
      IF saved_search_path IS NOT NULL THEN
        -- Use the given search path while executing the query
        starting_search_path := current_setting('search_path');

        BEGIN
          EXECUTE 'SET LOCAL search_path TO ' || saved_search_path || ';';

        EXCEPTION
          WHEN syntax_error_or_access_rule_violation THEN
            _get_exception_info(my_message_text,
                                my_pg_exception_detail,
                                my_pg_exception_hint,
                                my_pg_exception_context)
            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting search_path before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the search_path with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Search_Path is:\n'
                         || saved_search_path
                         || my_pg_exception_context;
        END;
      END IF;
}){}dnl

ifdef({isok_disable_role},{},{dnl
      IF saved_role IS NULL THEN
        role_used := current_user;
      ELSE
        -- The query has an associated role
        role_used := saved_role;

        -- Set the role, if any

        -- Save commands to switch back and forth between roles
        become_starting_role := 'SET LOCAL ROLE TO ' || current_user || ';';
        become_saved_role := 'SET LOCAL ROLE TO ' || saved_role || ';';

        BEGIN
          EXECUTE become_saved_role;

        EXCEPTION
          -- We want all of these to trap most that could go wrong:
          -- data_exception  (22000)  no such role, etc.
          -- syntax_error_or_access_rule_violation (42000)
          WHEN data_exception
               OR syntax_error_or_access_rule_violation THEN
            _get_exception_info(my_message_text,
                                my_pg_exception_detail,
                                my_pg_exception_hint,
                                my_pg_exception_context)
            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting role before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the role with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Role is:\n'
                         || saved_role
                         || my_pg_exception_context;
        END;
      END IF;
}){}dnl

      BEGIN
        FOR id, msg, extra IN EXECUTE saved_query LOOP
ifdef({isok_disable_role},{},{dnl
          IF saved_role IS NOT NULL THEN
            -- We don't know that the role used to execute the query
            -- has permissions to the ISOK_RESULTS table.
            EXECUTE become_starting_role;
          END IF;
}){}dnl

          -- Remember that we processed the id.
          INSERT INTO _riq_qr_ids (qr_id)
            VALUES (id);

          BEGIN
            -- Make a new isok_results row.
            INSERT INTO @extschema@.isok_results
              (iqname            , first_seen       , last_seen
                     , last_role
                     , last_schemas
                     , keep_until
                     , qr_id, qr_message, qr_extra
                     , notes)
              VALUES (this_iqname, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
ifdef({isok_disable_role},{dnl
                    , current_user
},{{}dnl
                    , role_used
}){}dnl
                    , current_schemas(TRUE)
                    , CASE WHEN this_keep
                        THEN 'infinity'::TIMESTAMP
                        ELSE NULL
                      END
                    , id, msg, extra
                    , '')
              ON CONFLICT
                ON CONSTRAINT "isok_unique_result_constraint"
                -- Update the existing isok_results row.
                DO UPDATE
                  SET last_seen = CURRENT_TIMESTAMP
ifdef({isok_disable_role},{dnl
                    , last_role = current_user
},{{}dnl
                    , last_role = role_used
}){}dnl
                    , last_schemas = current_schemas(TRUE)
                    , qr_message = msg
                    , qr_extra = extra
                  WHERE @extschema@.isok_results.iqname = this_iqname
                        AND @extschema@.isok_results.qr_id = id;
          EXCEPTION
            -- We want a different error message here because the
            -- context is useless, so don't include that.
            -- When something goes wrong here it's usually the values
            -- returned by the supplied query.  Trap everything,
            -- just in case.
            WHEN _user_sql_exceptions THEN
              _get_exception_info(my_message_text,
                                  my_pg_exception_detail,
                                  my_pg_exception_hint,
                                  my_pg_exception_context)
              exception_caught := TRUE;
              RAISE EXCEPTION data_exception USING
                MESSAGE = 'run_isok_queries:'
                          || ' Error storing the results of an'
                          || ' ISOK_QUERIES.Query'
                , DETAIL = 'Problem in the saved query with IQName = ('
                           || this_iqname || E'):\n'
                           || 'The error is: ' || my_message_text
                           || my_pg_exception_detail
                           || my_pg_exception_hint
                           || E'\nThe query is:\n'
                           || saved_query;
          END;

ifdef({isok_disable_role},{},{dnl
          IF saved_role IS NOT NULL THEN
            -- Put the desired effective role back in place for
            -- continued query execution.  There is no documentation
            -- of how row-level security policies execution is
            -- affected by changing the effective role while a query
            -- executes.  So make sure the effective role does not
            -- change while the query executes.
            EXECUTE become_saved_role;
          END IF;
}){}dnl
        END LOOP;
      EXCEPTION
        -- We want all of these to trap most that could go wrong when a
        -- user-supplied bit of sql is executed
        WHEN _user_sql_exceptions THEN
          _get_exception_info(my_message_text,
                              my_pg_exception_detail,
                              my_pg_exception_hint,
                              my_pg_exception_context)
          exception_caught := TRUE;
          RAISE EXCEPTION data_exception USING
            MESSAGE = 'run_isok_queries:'
                      || ' Error executing stored ISOK_QUERIES.Query'
            , DETAIL = 'Problem in the saved query with IQName = ('
                       || this_iqname || E'):\n'
                       || 'The error is: ' || my_message_text
                       || my_pg_exception_detail
                       || my_pg_exception_hint
                       || E'\nThe query is:\n'
                       || saved_query
                       || my_pg_exception_context;
      END;

ifdef({isok_disable_role},{},{dnl
      -- Reset the role to its initial state
      IF saved_role IS NOT NULL THEN
        EXECUTE become_starting_role;
      END IF;
}){}dnl

ifdef({isok_disable_search_path},{},{dnl
      -- Reset the search path to its initial state
      IF saved_search_path IS NOT NULL THEN
        EXECUTE 'SET LOCAL search_path TO ' || starting_search_path || ';';
      END IF;
}){}dnl

      -- Note that we've processed the saved query.
      UPDATE @extschema@.isok_queries
        SET last_run = CURRENT_TIMESTAMP
          , first_run = COALESCE(first_run, CURRENT_TIMESTAMP)
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      -- Delete old query results that are not found this time.
      CREATE UNIQUE INDEX _riq_qr_ids_qr_id
        ON _riq_qr_ids (qr_id);

      DELETE FROM @extschema@.isok_results
        WHERE @extschema@.isok_results.iqname = this_iqname
              AND (@extschema@.isok_results.keep_until IS NULL
                   OR @extschema@.isok_results.keep_until
                       <= CURRENT_TIMESTAMP::TIMESTAMP(0))
              AND NOT EXISTS(
                  SELECT 1
                    FROM _riq_qr_ids
                    WHERE @extschema@.isok_results.qr_id
                          = _riq_qr_ids.qr_id);

      -- Cleanup
      DROP TABLE _riq_qr_ids CASCADE;

      -- Return the rows found with their new values.
      RETURN QUERY
        SELECT @extschema@.isok_results.irid
             , @extschema@.isok_results.iqname
             , @extschema@.isok_results.first_seen
             , @extschema@.isok_results.last_seen
             , @extschema@.isok_results.last_role
             , @extschema@.isok_results.last_schemas
             , @extschema@.isok_results.deferred_to
             , @extschema@.isok_results.category
             , @extschema@.isok_results.keep_until
             , @extschema@.isok_results.qr_id
             , @extschema@.isok_results.qr_message
             , @extschema@.isok_results.qr_extra
             , @extschema@.isok_results.notes
          FROM @extschema@.isok_results
          WHERE @extschema@.isok_results.iqname = this_iqname
                AND @extschema@.isok_results.resolved IS NULL
                AND (@extschema@.isok_results.deferred_to IS NULL
                     OR @extschema@.isok_results.deferred_to
                        <= CURRENT_TIMESTAMP::TIMESTAMP(0));
    END LOOP; -- Over iqname

  EXCEPTION
    -- We want all of these to trap most that could go wrong when a
    -- user-supplied bit of sql is executed
    WHEN _user_sql_exceptions THEN
      IF exception_caught THEN
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL;
        RAISE EXCEPTION data_exception USING
            MESSAGE = my_message_text
          , DETAIL = my_pg_exception_detail;
      ELSE
        _get_exception_info(my_message_text,
                            my_pg_exception_detail,
                            my_pg_exception_hint,
                            my_pg_exception_context)
        RAISE EXCEPTION data_exception USING
          MESSAGE = 'run_isok_queries:'
                    || ' Error executing supplied query argument'
          , DETAIL = E'\nThe error is: ' || my_message_text
                     || my_pg_exception_detail
                     || my_pg_exception_hint
                     || E'\nThe query is:\n'
                     || query
                     || my_pg_exception_context;
      END IF;
   -- WHEN OTHERS THEN
   --   RAISE EXCEPTION data_exception USING
   --     MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM;
  END;
$$;
changequote(`,')dnl