I tried to post this with the title gvmd restarts with error: relation “information_schema_catalog_name” does not exist, but the forum kept telling me the title was unclear, so I was forced to rephrase it to something vague.
On 1 of our 8 test scanners (all running the latest stable release of gvm 10), gvmd restarts continuously, throwing the following error into our postgres log file:
2019-06-19 18:49:13.257 EDT [12032] root@gvmd STATEMENT: SELECT table_name, column_name, pg_get_serial_sequence (table_name, column_name) FROM information_schema.columns WHERE table_schema = 'public' AND pg_get_serial_sequence (table_name, column_name) IS NOT NULL;
2019-06-19 18:49:58.511 EDT [15421] root@gvmd ERROR: relation "information_schema_catalog_name" does not exist
We tracked the source query down to gvmd/src/manage_pg.c line 3284, which contains this query:
SELECT table_name, column_name, pg_get_serial_sequence (table_name, column_name) FROM information_schema.columns WHERE table_schema = 'public' AND pg_get_serial_sequence (table_name, column_name) IS NOT NULL;
We dug into why this error occurred on this machine, and not on any others, and found that it came down to the way postgres plans the query. The call to pg_get_serial_sequence in the WHERE clause throws an error if evaluated before the table_schema = 'public' condition. In most cases, these conditions are evaluated in the order they appear. In some cases, however, postgres for some reason decides to evaluate pg_get_serial_sequence first, and chokes on its inability to identify a schema for which it does not have a search path (ie not public).
We rewrote the query into one that always works by using a precomputed CTE to trick postgres’ query planner into evaluating the conditions in the desired order:
WITH serial_sequences AS (SELECT table_name, column_name, pg_get_serial_sequence (table_name, column_name) p FROM information_schema.columns WHERE table_schema = 'public') SELECT * FROM serial_sequences WHERE p IS NOT NULL;
Would someone please double-check our suggestion and consider incorporating this (or similar) into forthcoming releases? Thank you!