ERROR: invalid byte sequence for encoding "UTF8": 0x00 in pg_stat_statements

Question

I'm attempting to use pg_stat_statements to optimise my queries but ran into an unexpected roadblock.

SELECT total_plan_time + total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY total_time
DESC LIMIT 10;

results in ERROR: invalid byte sequence for encoding "UTF8": 0x00

I looked into this and it seems Postgres doesn't like the NULL character \00 in text fields.

Most of the existing advice online on this error are for people seeing errors inserting data into Postgres. In which case, the fix seems to be to filter the null character prior to the insert.

In this case, it seems the data is already IN postgres but it makes the view impossible to query.

a \d+ pg_stat_statements tells me that the pg_stat_statements view is built from running a function I've tried to get rid of the character using translate and replace but no luck.

Any idea how I can find the offending query with the NULL character? I'm assuming the long term fix is to trace the query with the NULL character, figure out how it's getting in.

What I've tried so far:

• I verified that its definitely bad data by doing a SELECT pg_stat_statements_reset();. The above query immediately works after that for a short while.

• I did a \ef pg_stat_statements and it seems the first argument is a boolean called show text. Passing false lets me query the table, which lets me do this:

SELECT total_plan_time + total_exec_time as total_time, queryid
FROM pg_stat_statements(false)
ORDER BY total_time
DESC LIMIT 10;

unfortunately the queryid isn't very useful unless there's someway for me to retrieve the mapped query text without this error.

Suggestions on how to proceed are appreciated! I'm on PostgreSQL 13.4

Answer 1

This must be a PostgreSQL bug and should be reported as such.

I cannot think of a better way to debug this than attaching to the PostgreSQL backend with a debugger and setting a break point at the places where this error message is thrown.