Hi,
I got the newest version compiled and the extension installed into Postgres on both 15 and 16. :)
However when I try to create an error log table using the function create_error_log then it seems to only create the error log table in the public schema if the source table is in the public schema as well. Thus if the schema prefix is ommited it works but adding a schema prefix produces the error saying that the table does not exist. This error is not raised in the function itself as I see it.
I looked at the function and tried to change it like this with no success.
CREATE OR REPLACE PROCEDURE dbms_errlog.create_error_log2(IN dml_table_schema character varying, IN dml_table_name character varying, IN err_log_table_name character varying DEFAULT NULL::character varying, IN err_log_table_owner name DEFAULT NULL::name, IN err_log_table_space name DEFAULT NULL::name) LANGUAGE plpgsql AS $procedure$ DECLARE sql_create_table text; sql_register_table text; fqdn_pos int := 0; err_log_tbname name := $2; err_log_namespace text; BEGIN IF dml_table_name IS NULL THEN RAISE EXCEPTION 'You must specify a DML table name.'; END IF; -- Verify that the DML table exists and get the -- schema and name of the table from the catalog SELECT n.nspname, c.relname INTO STRICT err_log_namespace, dml_table_name FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) WHERE n.nspname = dml_table_schema and c.relname = dml_table_name; -- Set the name of the error table if it is not provided IF err_log_table_name IS NULL THEN err_log_tbname := 'ERR$_'||substring(dml_table_name FROM 1 FOR 58); ELSE -- Remove quoting from table name if any err_log_table_name := replace(err_log_table_name, '"', ''); fqdn_pos := position('.' IN err_log_table_name) + 1; err_log_tbname := substring(err_log_table_name FROM fqdn_pos FOR 58); IF fqdn_pos > 1 THEN err_log_namespace := substring(err_log_table_name FROM 1 FOR fqdn_pos - 2); END IF; END IF; err_log_tbname := quote_ident(err_log_namespace) || '.' || quote_ident(err_log_tbname); -- Create the error log table sql_create_table := 'CREATE TABLE '||err_log_tbname||' ( PG_ERR_NUMBER$ text, -- PostgreSQL error number PG_ERR_MESG$ text, -- PostgreSQL error message PG_ERR_OPTYP$ char(1), -- Type of operation: insert (I), update (U), delete (D) PG_ERR_TAG$ text, -- Label used to identify the DML batch PG_ERR_QUERY$ text, -- Query at origin PG_ERR_DETAIL$ text -- Detail of the query origin )'; EXECUTE sql_create_table; IF err_log_table_owner IS NOT NULL THEN EXECUTE 'ALTER TABLE '||err_log_tbname||' OWNER TO '||err_log_table_owner; END IF; IF err_log_table_space IS NOT NULL THEN EXECUTE 'ALTER TABLE '||err_log_tbname||' SET TABLESPACE '||err_log_table_space||' NOWAIT'; END IF; sql_register_table := 'INSERT INTO dbms_errlog.register_errlog_tables VALUES ('''||dml_table_name||'''::regclass::oid, '''||err_log_tbname||'''::regclass::oid)'; EXECUTE sql_register_table; END; $procedure$
This select statement returns the schema and tablename successfully.
SELECT n.nspname, c.relname INTO STRICT err_log_namespace, dml_table_name FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) WHERE n.nspname = dml_table_schema and c.relname = dml_table_name;
It would be better to be able to create the errorlog table in a schema where the source table resides and not in the PUBLIC schema as that schema has been restricted due to vulnerabilities as of version 15.
Any help would be greatly appreciated.
Best regards,
Tomas Helgi
Hi,
I got the newest version compiled and the extension installed into Postgres on both 15 and 16. :)
However when I try to create an error log table using the function create_error_log then it seems to only create the error log table in the public schema if the source table is in the public schema as well. Thus if the schema prefix is ommited it works but adding a schema prefix produces the error saying that the table does not exist. This error is not raised in the function itself as I see it.
I looked at the function and tried to change it like this with no success.
CREATE OR REPLACE PROCEDURE dbms_errlog.create_error_log2(IN dml_table_schema character varying, IN dml_table_name character varying, IN err_log_table_name character varying DEFAULT NULL::character varying, IN err_log_table_owner name DEFAULT NULL::name, IN err_log_table_space name DEFAULT NULL::name) LANGUAGE plpgsql AS $procedure$ DECLARE sql_create_table text; sql_register_table text; fqdn_pos int := 0; err_log_tbname name := $2; err_log_namespace text; BEGIN IF dml_table_name IS NULL THEN RAISE EXCEPTION 'You must specify a DML table name.'; END IF; -- Verify that the DML table exists and get the -- schema and name of the table from the catalog SELECT n.nspname, c.relname INTO STRICT err_log_namespace, dml_table_name FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) WHERE n.nspname = dml_table_schema and c.relname = dml_table_name; -- Set the name of the error table if it is not provided IF err_log_table_name IS NULL THEN err_log_tbname := 'ERR$_'||substring(dml_table_name FROM 1 FOR 58); ELSE -- Remove quoting from table name if any err_log_table_name := replace(err_log_table_name, '"', ''); fqdn_pos := position('.' IN err_log_table_name) + 1; err_log_tbname := substring(err_log_table_name FROM fqdn_pos FOR 58); IF fqdn_pos > 1 THEN err_log_namespace := substring(err_log_table_name FROM 1 FOR fqdn_pos - 2); END IF; END IF; err_log_tbname := quote_ident(err_log_namespace) || '.' || quote_ident(err_log_tbname); -- Create the error log table sql_create_table := 'CREATE TABLE '||err_log_tbname||' ( PG_ERR_NUMBER$ text, -- PostgreSQL error number PG_ERR_MESG$ text, -- PostgreSQL error message PG_ERR_OPTYP$ char(1), -- Type of operation: insert (I), update (U), delete (D) PG_ERR_TAG$ text, -- Label used to identify the DML batch PG_ERR_QUERY$ text, -- Query at origin PG_ERR_DETAIL$ text -- Detail of the query origin )'; EXECUTE sql_create_table; IF err_log_table_owner IS NOT NULL THEN EXECUTE 'ALTER TABLE '||err_log_tbname||' OWNER TO '||err_log_table_owner; END IF; IF err_log_table_space IS NOT NULL THEN EXECUTE 'ALTER TABLE '||err_log_tbname||' SET TABLESPACE '||err_log_table_space||' NOWAIT'; END IF; sql_register_table := 'INSERT INTO dbms_errlog.register_errlog_tables VALUES ('''||dml_table_name||'''::regclass::oid, '''||err_log_tbname||'''::regclass::oid)'; EXECUTE sql_register_table; END; $procedure$This select statement returns the schema and tablename successfully.
SELECT n.nspname, c.relname INTO STRICT err_log_namespace, dml_table_name FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) WHERE n.nspname = dml_table_schema and c.relname = dml_table_name;It would be better to be able to create the errorlog table in a schema where the source table resides and not in the PUBLIC schema as that schema has been restricted due to vulnerabilities as of version 15.
Any help would be greatly appreciated.
Best regards,
Tomas Helgi