-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc., -- http://www.karlpinc.com/ -- Copyright (C) 2004-2011, Karl O. Pinc -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- DO NOT EDIT THIS FILE. It was automatically generated. Edit -- the *.m4 files instead. (Files _should_ be re-created by -- typing 'make', with the appropriate target, at the command line.) -- -- Support Tables -- -- The key, description support tables. CREATE TABLE IQ_TYPES ( IQType TEXT PRIMARY KEY NOT NULL CONSTRAINT "IQType: Cannot be empty or only whitespace characters" CHECK(btrim(IQType, E' \r\n\t\f\x0B') <> '') CONSTRAINT "IQType: Cannot contain any whitespace characters" CHECK(IQType IS NULL OR IQType !~ '( |\r|\n|\t|\f|\x0B)'), Description TEXT NOT NULL CONSTRAINT "Description: Cannot be empty or only whitespace characters" CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '') ); CREATE TABLE IR_TYPES ( IRType TEXT PRIMARY KEY NOT NULL CONSTRAINT "IRType: Cannot be empty or only whitespace characters" CHECK(btrim(IRType, E' \r\n\t\f\x0B') <> '') CONSTRAINT "IRType: Cannot contain any whitespace characters" CHECK(IRType IS NULL OR IRType !~ '( |\r|\n|\t|\f|\x0B)'), Description TEXT NOT NULL CONSTRAINT "Description: Cannot be empty or only whitespace characters" CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '') ); -- -- The primary tables -- -- isok_queries CREATE TABLE isok_queries ( iqname TEXT PRIMARY KEY CONSTRAINT "IQName: Cannot be empty or only whitespace characters" CHECK(btrim(IQName, E' \r\n\t\f\x0B') <> '') CONSTRAINT "IQName: Cannot contain any whitespace characters" CHECK(IQName IS NULL OR IQName !~ '( |\r|\n|\t|\f|\x0B)') , error BOOLEAN NOT NULL , type TEXT NOT NULL CONSTRAINT "Type must be an IQ_TYPES.IQType value" REFERENCES iq_types , keep BOOLEAN NOT NULL , first_run TIMESTAMP(0) , last_run TIMESTAMP(0) CONSTRAINT "Last_Run must be >= First_Run" CHECK(last_run IS NULL OR first_run IS NULL OR first_run <= last_run) , role name , search_path TEXT , query TEXT NOT NULL CONSTRAINT "Query: Cannot be empty or only whitespace characters" CHECK(btrim(Query, E' \r\n\t\f\x0B') <> '') , comment TEXT NOT NULL CONSTRAINT "Comment: Cannot be only whitespace characters" CHECK(Comment IS NULL OR Comment = '' OR btrim(Comment, E' \r\n\t\f\x0B') <> '') ); -- isok_results CREATE TABLE isok_results ( irid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY , iqname TEXT NOT NULL CONSTRAINT "IQName must be an ISOK_QUERIES.IQName" REFERENCES isok_queries , first_seen TIMESTAMP(0) NOT NULL , last_seen TIMESTAMP(0) NOT NULL CONSTRAINT "Last_Seen must be >= First_Seen" CHECK(first_seen <= last_seen) , last_role name NOT NULL , last_schemas name[] NOT NULL , resolved TIMESTAMP(0) CONSTRAINT "Resolved must be >= First_Seen" CHECK(resolved IS NULL OR first_seen <= resolved) , deferred_to TIMESTAMP(0) CONSTRAINT "Either Resolved or Deferred_To or both must be NULL" CHECK(NOT(resolved IS NOT NULL AND deferred_to IS NOT NULL)) CONSTRAINT "Deferred_To must be >= First_Seen" CHECK(deferred_to IS NULL OR first_seen <= deferred_to) , category TEXT CONSTRAINT "Category must be an IR_TYPES.IRType value" REFERENCES ir_types , keep_until TIMESTAMP(0) , qr_id TEXT NOT NULL CONSTRAINT "QR_ID: Cannot be empty or only whitespace characters" CHECK(btrim(QR_ID, E' \r\n\t\f\x0B') <> '') , qr_message TEXT NOT NULL CONSTRAINT "QR_Message: Cannot be empty or only whitespace characters" CHECK(btrim(QR_Message, E' \r\n\t\f\x0B') <> '') , qr_extra JSONB , notes TEXT NOT NULL CONSTRAINT "Notes: Cannot be only whitespace characters" CHECK(Notes IS NULL OR Notes = '' OR btrim(Notes, E' \r\n\t\f\x0B') <> '') , CONSTRAINT "The combination of IQName and QR_ID must be unique" UNIQUE (iqname, qr_id) ); -- pg_isok A query centered monitoring tool for PostgreSQL -- Copyright (C) 2015, 2025 The Meme Factory, Inc. http://www.karlpinc.com/ -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- -- Remarks: -- -- Create indexes on all the Isok tables. -- Remarks: -- isok_queries -- This first index is a simple way to enforce case-insensitive -- uniqueness, but is not otherwise useful. -- "lower() is not exactly the same as case-folding, because Unicode -- and human language, but it is close enough. CREATE UNIQUE INDEX "ISOK_QUERIES.IQName is case-insensitively unique" ON isok_queries (lower(iqname)); CREATE INDEX isok_queries_type ON isok_queries (type); CREATE INDEX isok_queries_last_run ON isok_queries (last_run); -- isok_results CREATE INDEX isok_results_first_seen ON isok_results (first_seen); CREATE INDEX isok_results_last_seen ON isok_results (last_seen); CREATE INDEX isok_results_resolved ON isok_results (resolved) WHERE resolved IS NOT NULL; CREATE INDEX isok_results_deferred_to ON isok_results (deferred_to) WHERE deferred_to IS NOT NULL; CREATE INDEX isok_results_category ON isok_results (category); CREATE INDEX isok_results_qr_id ON isok_results (qr_id); -- -- Support tables -- -- All support tables require their keys be case-insensitive unique. -- See the comment on INDEX isok_queries_caseunique_iqname, above. -- iq_types CREATE UNIQUE INDEX "IQ_TYPES.IQType is case-insensitively unique" ON iq_types (lower(iqtype)); CREATE UNIQUE INDEX "IQ_TYPES.Description is case-insensitively unique" ON iq_types (lower(description)); -- ir_types CREATE UNIQUE INDEX "IR_TYPES.IRType is case-insensitively unique" ON ir_types (lower(irtype)); CREATE UNIQUE INDEX "IR_TYPES.Description is case-insensitively unique" ON ir_types (lower(description)); -- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc., -- http://www.karlpinc.com/ -- Copyright (C) 2004-2011, Karl O. Pinc -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- DO NOT EDIT THIS FILE. It was automatically generated. Edit -- the *.m4 files instead. (Files _should_ be re-created by -- typing 'make', with the appropriate target, at the command line.) -- -- isok_queries -- CREATE FUNCTION isok_queries_update_func () RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN -- Function for isok_queries update triggers -- -- Copyright (C) 2015, 2025 The Meme Factory, Inc., http://www.karlpinc.com/ -- Distributed under the GNU Affero General Public License, version 3 or later. IF NEW.IQName <> OLD.IQName THEN -- IQName has changed RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of @extschema@.ISOK_QUERIES' , DETAIL = 'Value (IQName) = (' || OLD.IQName || '): @extschema@.ISOK_QUERIES.IQName cannot be changed'; RETURN NULL; END IF; IF OLD.error <> NEW.error AND NEW.error THEN PERFORM 1 FROM @extschema@.isok_results WHERE @extschema@.isok_results.iqname = NEW.iqname AND @extschema@.isok_results.resolved IS NOT NULL; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of ISOK_QUERIES' , DETAIL = 'Key (IQName) = (' || NEW.iqname || '): Value (Error) = (' || NEW.error || '): Cannot make into an error when there are already' || ' ISOK_RESULTS rows with a TRUE Resolved value'; RETURN NULL; END IF; END IF; RETURN NULL; END; $$; CREATE TRIGGER isok_queries_update_trigger AFTER UPDATE ON isok_queries FOR EACH ROW EXECUTE PROCEDURE isok_queries_update_func(); -- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc., -- http://www.karlpinc.com/ -- Copyright (C) 2004-2011, Karl O. Pinc -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- DO NOT EDIT THIS FILE. It was automatically generated. Edit -- the *.m4 files instead. (Files _should_ be re-created by -- typing 'make', with the appropriate target, at the command line.) -- -- isok_results -- CREATE FUNCTION isok_results_func () RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN -- Function for isok_results insert and update triggers -- -- Copyright (C) 2015, 2025 The Meme Factory, Inc., http://www.karlpinc.com/ -- Distributed under the GNU Affero General Public License, version 3 or later. IF TG_OP = 'UPDATE' THEN IF NEW.IRID <> OLD.IRID THEN -- IRID has changed RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of @EXTSCHEMA@.ISOK_RESULTS' , DETAIL = 'Value (IRID) = (' || OLD.IRID || '): @EXTSCHEMA@.ISOK_RESULTS.IRID cannot be changed'; RETURN NULL; END IF; END IF; -- Errors are not allowed to be resolved. IF NEW.resolved IS NOT NULL THEN PERFORM 1 FROM @extschema@.isok_queries WHERE @extschema@.isok_queries.iqname = NEW.iqname AND @extschema@.isok_queries.error; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of ISOK_QUERIES' , DETAIL = 'Key (IRID) = (' || NEW.irid || '): Value (IQName) = (' || NEW.iqname || '): Value (QR_ID) = (' || NEW.qr_id || '): Value (Resolved) = (' || NEW.resolved || '): The INTEGRITY_QUERY (' || NEW.iqname || ') produces errors, these may not be marked resolved'; RETURN NULL; END IF; END IF; RETURN NULL; END; $$; CREATE TRIGGER isok_results_trigger AFTER INSERT OR UPDATE ON isok_results FOR EACH ROW EXECUTE PROCEDURE isok_results_func(); -- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc., -- http://www.karlpinc.com/ -- Copyright (C) 2004-2011, Karl O. Pinc -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- DO NOT EDIT THIS FILE. It was automatically generated. Edit -- the *.m4 files instead. (Files _should_ be re-created by -- typing 'make', with the appropriate target, at the command line.) CREATE OR REPLACE FUNCTION run_isok_queries() RETURNS TABLE( 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 ) LANGUAGE plpgsql SECURITY INVOKER AS $$ -- Run all queries in ISOK_QUERIES. -- -- Copyright (C) 2015, 2025 The Meme Factory, Inc. http://www.karlpinc.com/ -- Distributed under the GNU Affero General Public License, version 3 or later. -- -- 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; $$; CREATE OR REPLACE FUNCTION run_isok_queries(query TEXT) RETURNS TABLE( 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 ) LANGUAGE plpgsql SECURITY INVOKER AS $$ -- Run the queries in ISOK_QUERIES that have an IQName returned by -- the SQL statement given as an argument. -- -- Copyright (C) 2015, 2025 The Meme Factory, Inc. http://www.karlpinc.com/ -- Distributed under the GNU Affero General Public License, version 3 or later. -- -- 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; saved_role @extschema@.isok_queries.role%TYPE; saved_search_path @extschema@.isok_queries.search_path%TYPE; saved_query @extschema@.isok_queries.query%TYPE; -- Dynamic commands starting_search_path TEXT; role_used name; become_starting_role TEXT; become_saved_role TEXT; -- 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 , @extschema@.isok_queries.search_path , @extschema@.isok_queries.role INTO saved_query , this_keep , saved_search_path , saved_role 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; 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 STACKED DIAGNOSTICS my_message_text = MESSAGE_TEXT , my_pg_exception_detail = PG_EXCEPTION_DETAIL , my_pg_exception_hint = PG_EXCEPTION_HINT , my_pg_exception_context = PG_EXCEPTION_CONTEXT; my_message_text := E'\n' || my_message_text; IF my_pg_exception_detail <> '' THEN my_message_text := my_message_text || E'\n'; my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail; END IF; IF my_pg_exception_hint <> '' THEN my_pg_exception_detail := my_pg_exception_detail || E'\n'; my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint; END IF; my_pg_exception_hint := my_pg_exception_hint || E'\n'; IF my_pg_exception_context <> '' THEN my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context; END IF; 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; 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 STACKED DIAGNOSTICS my_message_text = MESSAGE_TEXT , my_pg_exception_detail = PG_EXCEPTION_DETAIL , my_pg_exception_hint = PG_EXCEPTION_HINT , my_pg_exception_context = PG_EXCEPTION_CONTEXT; my_message_text := E'\n' || my_message_text; IF my_pg_exception_detail <> '' THEN my_message_text := my_message_text || E'\n'; my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail; END IF; IF my_pg_exception_hint <> '' THEN my_pg_exception_detail := my_pg_exception_detail || E'\n'; my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint; END IF; my_pg_exception_hint := my_pg_exception_hint || E'\n'; IF my_pg_exception_context <> '' THEN my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context; END IF; 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; BEGIN FOR id, msg, extra IN EXECUTE saved_query LOOP 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; -- 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 , role_used , current_schemas(TRUE) , CASE WHEN this_keep THEN 'infinity'::TIMESTAMP ELSE NULL END , id, msg, extra , '') ON CONFLICT ON CONSTRAINT "The combination of IQName and QR_ID must be unique" -- Update the existing isok_results row. DO UPDATE SET last_seen = CURRENT_TIMESTAMP , last_role = role_used , 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 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 THEN GET STACKED DIAGNOSTICS my_message_text = MESSAGE_TEXT , my_pg_exception_detail = PG_EXCEPTION_DETAIL , my_pg_exception_hint = PG_EXCEPTION_HINT , my_pg_exception_context = PG_EXCEPTION_CONTEXT; my_message_text := E'\n' || my_message_text; IF my_pg_exception_detail <> '' THEN my_message_text := my_message_text || E'\n'; my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail; END IF; IF my_pg_exception_hint <> '' THEN my_pg_exception_detail := my_pg_exception_detail || E'\n'; my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint; END IF; my_pg_exception_hint := my_pg_exception_hint || E'\n'; IF my_pg_exception_context <> '' THEN my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context; END IF; 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; 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; 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 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 THEN GET STACKED DIAGNOSTICS my_message_text = MESSAGE_TEXT , my_pg_exception_detail = PG_EXCEPTION_DETAIL , my_pg_exception_hint = PG_EXCEPTION_HINT , my_pg_exception_context = PG_EXCEPTION_CONTEXT; my_message_text := E'\n' || my_message_text; IF my_pg_exception_detail <> '' THEN my_message_text := my_message_text || E'\n'; my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail; END IF; IF my_pg_exception_hint <> '' THEN my_pg_exception_detail := my_pg_exception_detail || E'\n'; my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint; END IF; my_pg_exception_hint := my_pg_exception_hint || E'\n'; IF my_pg_exception_context <> '' THEN my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context; END IF; 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; -- Reset the role to its initial state IF saved_role IS NOT NULL THEN EXECUTE become_starting_role; END IF; -- 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; -- 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 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 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 STACKED DIAGNOSTICS my_message_text = MESSAGE_TEXT , my_pg_exception_detail = PG_EXCEPTION_DETAIL , my_pg_exception_hint = PG_EXCEPTION_HINT , my_pg_exception_context = PG_EXCEPTION_CONTEXT; my_message_text := E'\n' || my_message_text; IF my_pg_exception_detail <> '' THEN my_message_text := my_message_text || E'\n'; my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail; END IF; IF my_pg_exception_hint <> '' THEN my_pg_exception_detail := my_pg_exception_detail || E'\n'; my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint; END IF; my_pg_exception_hint := my_pg_exception_hint || E'\n'; IF my_pg_exception_context <> '' THEN my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context; END IF; 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; $$; -- pg_isok A query centered monitoring tool for PostgreSQL -- Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- -- Remarks: -- Hand-written comments. -- Note that comments extracted from the documentation are appended -- to this file. -- -- Line breaks at 50 columns, since that's what format_comments.gawk does. -- -- Support table columns. -- -- IQ_TYPES COMMENT ON COLUMN iq_types.iqtype IS 'A classification code attached to a query in the ISOK_QUERIES table.'; COMMENT ON COLUMN iq_types.description IS 'A description of the IQType code.'; -- IRTypes COMMENT ON COLUMN ir_types.irtype IS 'A classification code attached to a query result row in the ISOK_RESULTS table.'; COMMENT ON COLUMN ir_types.description IS 'A description of the IRType code.'; -- -- Comment the functions used by people -- -- run_isok_queries() COMMENT ON FUNCTION run_isok_queries() IS 'Execute all the queries in the ISOK_QUERIES table and return a table of the results.'; COMMENT ON FUNCTION run_isok_queries(query TEXT) IS 'Execute the supplied query, which produces a single column of ISOK_QUERIES.IQName values, and then execute all the queries in the ISOK_QUERIES table with those IQName values, returning a table of the result of the executed queries.'; -- -- Comment the functions used by triggers -- COMMENT ON FUNCTION isok_queries_update_func() IS 'Internal function: Implements an AFTER UPDATE FOR EACH ROW trigger on ISOK_QUERIES'; COMMENT ON FUNCTION isok_results_func() IS 'Internal function: Implements an AFTER INSERT OR UPDATE FOR EACH ROW trigger on ISOK_RESULTS'; -- -- Everything that follows is extracted from the documentation. -- COMMENT ON TABLE ISOK_QUERIES IS $isok_comment$The ISOK_QUERIES table contains one row for every query used to search for database integrity issues.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.IQName IS $isok_comment$A unique name for the query.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Type IS $isok_comment$Code classifying the query. The legal values for this column are defined by the IQ_TYPES support table.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.First_Run IS $isok_comment$Date and time the query was first run by Isok.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Last_Run IS $isok_comment$Date and time the query was most recently run by Isok.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Keep IS $isok_comment$This column controls the value placed in the ISOK_RESULTS.Keep_Until column when run_isok_queries() inserts new rows in ISOK_RESULTS.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Role IS $isok_comment$The PostgreSQL role to use to run the query.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Search_Path IS $isok_comment$The PostgreSQL schema search_path to have in effect when the query is run.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Query IS $isok_comment$A query which checks for database integrity violations.$isok_comment$; COMMENT ON COLUMN ISOK_QUERIES.Comment IS $isok_comment$A comment on the query.$isok_comment$; COMMENT ON TABLE ISOK_RESULTS IS $isok_comment$The ISOK_RESULTS table contains one row for every database integrity problem discovered by the queries in ISOK_QUERIES.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.IRID IS $isok_comment$This column uniquely identifies the row containing the$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.IQName IS $isok_comment$The ISOK_QUERIES.IQName value identifying the query which produced the result.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.First_Seen IS $isok_comment$Date and time the query result was first produced by Isok.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Last_Seen IS $isok_comment$Date and time the query result was most recently produced by Isok.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Last_Role IS $isok_comment$The role (user) which was the current role when the query was last executed.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Last_Schemas IS $isok_comment$All schemas that were, implicitly or not, in the search_path, and also available to the Last_Role, when the result was returned.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Resolved IS $isok_comment$Date and time the query result was resolved; that is, marked not a concern.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Deferred_To IS $isok_comment$Isok suppresses display of the result when the current time is before this time.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Category IS $isok_comment$Code classifying the query result. The legal values for this column are defined by the IR_TYPES support table.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Keep_Until IS $isok_comment$This column controls whether or not run_isok_queries() deletes the row when the ISOK_QUERIES.Query is re-run and the query does not return the row's QR_ID.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.QR_ID IS $isok_comment$This is a unique, unique per query that is, identifier for the query result.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.QR_Message IS $isok_comment$This is the message, the second column, produced by the most recent execution of the ISOK_QUERIES.Query.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.QR_Extra IS $isok_comment$The value of the third, optional, column returned by most recent execution of the query. This may contain any JSON deemed useful.$isok_comment$; COMMENT ON COLUMN ISOK_RESULTS.Notes IS $isok_comment$Any notes regarding this particular query result.$isok_comment$; COMMENT ON TABLE IQ_TYPES IS $isok_comment$IQ_TYPES contains one row for every code used to classify database integrity queries.$isok_comment$; COMMENT ON TABLE IR_TYPES IS $isok_comment$IR_TYPES contains one row for every code used to classify or explain sets of database integrity problems, problems discovered by Isok's queries.$isok_comment$; -- pg_isok A query centered monitoring tool for PostgreSQL -- Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as published -- by the Free Software Foundation, either version 3 of the License, or -- (at your option) any later version. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU Affero General Public License for more details. -- -- You should have received a copy of the GNU Affero General Public License -- along with this program. If not, see . -- -- Karl O. Pinc -- -- Remarks: -- Call pg_extension_config_dump(regclass, text) on all the tables and -- sequences so that their content is retained in database dumps. SELECT pg_extension_config_dump('iq_types', ''); SELECT pg_extension_config_dump('ir_types', ''); SELECT pg_extension_config_dump('isok_queries', ''); SELECT pg_extension_config_dump('isok_results', ''); SELECT pg_extension_config_dump('isok_results_irid_seq', '');