-
Problem report
-
Resolution: Unresolved
-
Trivial
-
None
-
7.0.10
-
Zabbix server: 7.2.3
Agent2 version: 7.0.10
zabbix-agent2-plugin-postgresql version: Version 7.0.10, built with go1.23.4
template: PostgreSQL by Zabbix agent 2 active
Steps to reproduce:
I'm migrating old custom PostgreSQL SQL scripts from Agent 1 to Agent 2 with the PostgreSQL plugin. The metric uses a dynamic schema name determined as part of the PostgreSQL database discovery. I can determine the schema name in the zabbix discovery by just using "{#DBNAME}_schemaprefix". This schema name is then passed to the agent which is used when parsing the SQL script.
Agent 1 version:
SET search_path TO :the_schema; WITH T AS ( select coalesce(sum(case when task_stat_id = 1 then 1 else 0 END), 0) waiting, coalesce(sum(case when task_stat_id = 2 then 1 else 0 END), 0) running, coalesce(sum(case when task_stat_id = 3 then 1 else 0 END), 0) failed, coalesce(sum(case when task_stat_id = 4 then 1 else 0 END), 0) completed, coalesce(sum(case when task_stat_id = 7 then 1 else 0 END), 0) timed_out, coalesce(sum(case when task_stat_id = 8 then 1 else 0 END), 0) paused, coalesce(sum(case when task_stat_id = 9 then 1 else 0 END), 0) queued FROM task ) SELECT row_to_json(T) FROM T;
This worked fine.
I've changed the script for the new plugin so that it uses $# format for parameters:
SET search_path TO $1; WITH T AS ( select coalesce(sum(case when task_stat_id = 1 then 1 else 0 END), 0) waiting, coalesce(sum(case when task_stat_id = 2 then 1 else 0 END), 0) running, coalesce(sum(case when task_stat_id = 3 then 1 else 0 END), 0) failed, coalesce(sum(case when task_stat_id = 4 then 1 else 0 END), 0) completed, coalesce(sum(case when task_stat_id = 7 then 1 else 0 END), 0) timed_out, coalesce(sum(case when task_stat_id = 8 then 1 else 0 END), 0) paused, coalesce(sum(case when task_stat_id = 9 then 1 else 0 END), 0) queued FROM task ) SELECT row_to_json(T) FROM T;
Calling it:
zabbix_agent2 -t 'pgsql.custom.query["tcp://localhost:5432","zbx_monitor","xxxxxx","database","pgsql.test", "the_schema"]'
(the_schema being the name of a schema within a database).
Result:
I get the following error:
pgsql.custom.query["tcp://localhost:5432","zbx_monitor","xxxxxx","database","pgsql.test", "the_schema"][m|ZBX_NOTSUPPORTED] [Cannot fetch data: ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601).]
I think this is due to the PG plugin using Prepared Statements and apparently you can have only one command inside the script. Prepared statements also means I can't do something like:
// ...
FROM $1.task
I believe PostgreSQL prepared statements simply don't allow it. I tried it just in case:
pgsql.custom.query["tcp://localhost:5432","zbx_monitor","xxxxxx","met_prod","pgsql.test", "the_schema"][m|ZBX_NOTSUPPORTED] [Cannot fetch data: ERROR: syntax error at or near "$1" (SQLSTATE 42601).]
Expected:
Some way of being able to query referencing a dynamic schema passed from the Zabbix item to the Agent2.