Parsing SQL error when selecting from a foreign table pointing to a auditlog after executing a function call.

[thomashelgi]

Hi,

I have setup pgaudit and pgauditlogtofile on my Postgres 16.7 instance and I created a foreign table similar to what is in the README.md The table looks like this. CREATE FOREIGN TABLE myaudit.pgaudit_20250430_test ( log_time timestamptz(3) NULL, user_name text NULL, database_name text NULL, process_id int4 NULL, remote_client text NULL, session_id text NULL, session_line_num text NULL, command_tag text NULL, session_start_time text NULL, virtual_transaction_id text NULL, transaction_id text NULL, sql_state_code text NULL, audit_type text NULL, statement_id text NULL, substatement_id text NULL, "class" text NULL, command text NULL, object_type text NULL, object_name text NULL, "statement" text NULL, "parameter" text NULL, detail text NULL, hint text NULL, internal_query text NULL, internal_query_pos text NULL, context text NULL, debug_query text NULL, cursor_pos text NULL, function_name text NULL, application_name text NULL ) SERVER pglog OPTIONS (filename '/pgdata/myinstance/16/data/audit/pgaudit-20250430.csv', format 'csv', quote '"'); I also created a function/stored procedure that creates a new foreign table to the current date logfile CREATE OR REPLACE FUNCTION myaudit.add_audit_master_partition() RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE myauditpath varchar(200); BEGIN myauditpath = '/pgdata/myinstance/16/data/audit/pgaudit-'|| to_char(current_timestamp, 'YYYYMMDD') || '.csv' ; EXECUTE 'CREATE FOREIGN TABLE myaudit.pgaudit_TEMP ( log_time timestamptz(3) NULL, user_name text NULL, database_name text NULL, process_id int4 NULL, remote_client text NULL, session_id text NULL, session_line_num text NULL, command_tag text NULL, session_start_time text NULL, virtual_transaction_id text NULL, transaction_id text NULL, sql_state_code text NULL, audit_type text NULL, statement_id text NULL, substatement_id text NULL, "class" text NULL, command text NULL, object_type text NULL, object_name text NULL, "statement" text NULL, "parameter" text NULL, detail text NULL, hint text NULL, internal_query text NULL, internal_query_pos text NULL, context text NULL, debug_query text NULL, cursor_pos text NULL, function_name text NULL, application_name text NULL ) SERVER pglog OPTIONS (filename '|| quote_literal(myauditpath) ||', format '||quote_literal('csv')||')' ; RETURN 0; END; $function$ ;

My postgresql.conf configuration is pgaudit.log_filename = 'pgaudit-%Y%m%d.csv' pgaudit.log = 'all,-misc' pgaudit.log_directory = 'audit' pgaudit.log_connections = on pgaudit.log_disconnections = on pgaudit.log_rotation_age = 1d

Once configured I restart the server and everything seems to be fine until I execute the function/stored procedure

SELECT myaudit.add_audit_master_partition();

The pgaudit_TEMP table is created successfully however once I select from the table and/or the pgaudit_20250430_test which points to the same file in this case which is just to demontrate the actual error that I get once I select from the tables. The error is

SQL Error [22P04]: ERROR: missing data for column "debug_query" Where: COPY pgaudit_20250430_test, line 395: "2025-04-30 10:30:15.117 UTC,tomas,postgres,1396,10.10.10.1:57700,6811fad9.574,9,SELECT,2025-04-30 10..."

The same error appears when I execute other complex queries.

Is something wrong with my table setup and config ? Please advice and help.

Best regards, Tomas Helgi