Admin database issues with psycopg2 and pgcli: syntax error in admin command

[maherbeg]

Hi! I noticed a few issues with an admittedly old Python PG library, and with a modern version of pgcli. I wanted to create this issue just in case anyone else is seeing the same errors, even if we don't fix them. (I saw #111 so I'm totally fine with not fixing this)

psycopg2

We were attempting to integrate the datadog pgbouncer agent with pgdog, but ran into an error along the lines of

File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/base/checks/base.py\", line 1317, in run\n    self.check(instance)\n  File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/pgbouncer/pgbouncer.py\", line 228, in check\n    self._try_collect_data()\n  File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/pgbouncer/pgbouncer.py\", line 209, in _try_collect_data\n    self._ensure_connection()\n  File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/pgbouncer/pgbouncer.py\", line 187, in _ensure_connection\n    self.connection = self._new_connection()\n                      ^^^^^^^^^^^^^^^^^^^^^^\n  File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/pgbouncer/pgbouncer.py\", line 167, in _new_connection\n    connection = pg.connect(**connect_kwargs)\n                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/opt/datadog-agent/embedded/lib/python3.12/site-packages/psycopg2/__init__.py\", line 122, in connect\n    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\npsycopg2.errors.SystemError: syntax error in admin command\n\n"}]

The issue ends up being that psycopg ends up attempting to set the date style to ISO, and maybe some more things happen further along the line too. I saw this via a trace against pgdog locally

2025-10-06T15:24:50.755263Z  INFO client "admin" connected to database "admin" [192.168.148.1:32906, auth: scram]
pgdog-1    | 2025-10-06T15:24:50.755914Z TRACE request buffered [0.0602ms]
pgdog-1    | ClientRequest {
pgdog-1    |     messages: [
pgdog-1    |         Query(
pgdog-1    |             Query {
pgdog-1    |                 query: "SET datestyle TO 'ISO'",
pgdog-1    |             },
pgdog-1    |         ),
pgdog-1    |     ],
pgdog-1    |     route: None,
pgdog-1    | }
pgdog-1    | 2025-10-06T15:24:50.756060Z DEBUG cross-shard queries disabled: false
pgdog-1    | 2025-10-06T15:24:50.757125Z TRACE [192.168.148.1:32906] <-- ErrorResponse {
pgdog-1    |     severity: "ERROR",
pgdog-1    |     code: "58000",
pgdog-1    |     message: "syntax error in admin command",
pgdog-1    |     detail: None,
pgdog-1    |     context: None,
pgdog-1    |     file: None,
pgdog-1    |     routine: None,
pgdog-1    | }

I haven't taken a look at psycopg3 yet but I wouldn't be surprised if it does something similar at startup.

pgcli

pgcli uses psycopg3 but fails in a different manner. pgcli attempts to detect pgbouncer since it knows that pgbouncer is a fake database, and runs a SELECT 1 query to test the error returned. You can follow the trace in pgcli via these links

https://github.com/dbcli/pgcli/blob/f46d8446a34084cc2532041619d1f08bda7213e7/pgcli/pgexecute.py#L258-L259
https://github.com/dbcli/pgcli/blob/f46d8446a34084cc2532041619d1f08bda7213e7/pgcli/pgexecute.py#L183-L186
https://github.com/dbcli/pgcli/blob/f46d8446a34084cc2532041619d1f08bda7213e7/pgcli/pgexecute.py#L630-L635
https://github.com/dbcli/pgcli/blob/f46d8446a34084cc2532041619d1f08bda7213e7/pgcli/pgexecute.py#L85-L94

pgdog currently returns a SyntaxError, but pgcli is expecting a ProtocolViolation error to detect if this is a fake database or not (https://www.psycopg.org/psycopg3/docs/api/errors.html). I'm not sure what is technically correct via the Postgres spec here, but just wanted to highlight this.

Anyway, we're using the openmetrics integration instead which is working great, but it might be nice to be able to support the native pgbouncer datadog integration since custom metrics cost users more money in DD, which is already insanely expensive 😢

[levkk]

pgdog currently returns a SyntaxError, but pgcli is expecting a ProtocolViolation error to detect if this is a fake database or not (https://www.psycopg.org/psycopg3/docs/api/errors.html). I'm not sure what is technically correct via the Postgres spec here, but just wanted to highlight this.

Should be a straight forward thing to add. Will take a look shortly.

Anyway, we're using the openmetrics integration instead which is working great, but it might be nice to be able to support the native pgbouncer datadog integration since custom metrics cost users more money in DD, which is already insanely expensive 😢

For sure. IIRC (it's been a minute), pgbouncer integration-generated metrics are charged at regular metrics rates, so might not be a cost optimization in the end, but I could be 100% wrong about that. Either way, we should make the admin database work with datadog pgbouncer integration. It's not too hard, we just need to install it locally and hot fix until the agent tells us it collected metrics successfully. On the backlog it goes!