Monitor ERROR: invalid input syntax for type integer: ""

[degubellini]

Hello,
On 4 Debian 12 nodes (3 nodes + 1 monitor node), I've installed postgresql 16 and postgresql-16-auto-failover.
Packages come from postgres repository (https://apt.postgresql.org/pub/repos/apt)

I am testing postresql switchover and I run "pg_autoctl perform switchover --formation default --group 0" on a node

Looking at journalctl log files for the service, everything looks fine and "pg_autoctl show state" is showing the switchover complete. However, at the cli, before the completion of the switchover I get

Why am I getting this error? Does it mean that I can't trust the failover also if it seems that happened?

---

This is pg_autofailover version

pg_autoctl version 2.1-2.pgdg120+1 pg_autoctl extension version 2.1 compiled with PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit compatible with Postgres 11, 12, 13, 14, 15, and 16

[degubellini]

Morning, any feedback on this? or do you need more specific details?

[degubellini]

Any feedback for this?

[jamielinux]

I'm getting this issue too. Also on postgres 16.

Like you, the switchover seems to be ok, but it's hard to know if this warning means something less noticeable broke.

[wilful]

Getting same on 16.2

postgres@dffe075aa327:/$ pg_autoctl perform switchover
06:38:23 37553 INFO  Waiting 60 secs for a notification with state "primary" in formation "default" and group 0
06:38:23 37553 INFO  Listening monitor notifications about state changes in formation "default" and group 0
06:38:23 37553 INFO  Following table displays times when notifications are received
06:38:23 37553 ERROR Monitor ERROR:  invalid input syntax for type integer: ""
06:38:23 37553 ERROR Monitor CONTEXT:  unnamed portal parameter $2 = ''
06:38:23 37553 ERROR SQL query: SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id
06:38:23 37553 ERROR SQL params: 'default', ''
06:38:23 37553 ERROR Failed to get other nodes from the monitor while running "SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id" with formation default and group 0
06:38:23 37553 WARN  Failed to get_nodes() on the monitor

and this

postgres@dffe075aa327:/$ pg_autoctl set node candidate-priority --name development-postgresql-3 70
07:18:32 65130 ERROR candidate-priority value 70 is not valid. Valid values are integers from 0 to 100.

[dantio]

Same issue here.

$ pg_autoctl --version
pg_autoctl version .dirty
pg_autoctl extension version 2.1
compiled with PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
compatible with Postgres 13, 14, 15, 16 and 17

[xinferum]

Good afternoon.
Same problem, Debain 12, version pg_auto_failover:

pg_autoctl version 2.2-2.pgdg120+1
pg_autoctl extension version 2.2
compiled with PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
compatible with Postgres 13, 14, 15, 16 and 17

@dimitri , is this a serious problem, or can the warning be safely ignored? What could be the reason for this message?

And the priority over, as mentioned above, cannot be changed:

pg_autoctl set node candidate-priority --name postgres-db01.local 70
15:58:06 20077 ERROR candidate-priority value 70 is not valid. Valid values are integers from 0 to 100.

[xinferum]

I'll update the information.

The same error occurs when I try to change the cluster monitor from one to another (this practice of replacing the monitor is described in the documentation here pg_autoctl disable monitor --force), on the datanode, I run the command:

pg_autoctl disable monitor --force

10:11:08 8111 ERROR Monitor ERROR:  invalid input syntax for type integer: ""
10:11:08 8111 ERROR Monitor CONTEXT:  unnamed portal parameter $2 = ''
10:11:08 8111 ERROR SQL query: SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id
10:11:08 8111 ERROR SQL params: 'test_formation', ''
10:11:08 8111 ERROR Failed to get other nodes from the monitor while running "SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id" with formation test_formation and group 0"    
10:11:08 8111 WARN  Failed to get_nodes() on the monitor
10:11:08 8111 INFO  Failed to contact the monitor, disabling it as requested

At the same time, the old monitor is accessible to the node and the node should have been deleted from it.

Resultcode = 0, but the datanode remains on the old monitor, although when it is available, even with --force, the datanode is removed from it in RedHat-like distributions where this error is not present.

On the old monitor, I had to delete the datanode manually.

Without the --force parameter on the datanode, while the monitor is available, the pg_autoctl disable monitor command does not fire due to this error:

pg_autoctl disable monitor

10:43:02 2432 ERROR Monitor ERROR:  invalid input syntax for type integer: ""
10:43:02 2432 ERROR Monitor CONTEXT:  unnamed portal parameter $2 = ''
10:43:02 2432 ERROR SQL query: SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id
10:43:02 2432 ERROR SQL params: 'test_formation', ''
10:43:02 2432 ERROR Failed to get other nodes from the monitor while running "SELECT * FROM pgautofailover.get_nodes($1, $2) ORDER BY node_id" with formation test_formation and group 0
10:43:02 2432 WARN  Failed to get_nodes() on the monitor
10:43:02 2432 FATAL Failed to contact the monitor, use --force to continue

[xinferum]

Hello.

This pull request solves the problem with "switchover" and "disable monitor":
#1091

And this pull request solves the problem with "pg_autoctl set node candidate-priority":
#1104

I compiled pg_auto_failover from the source code with adding changes from this pull requests and it helped to solve these problems.

@dimitri