check_postgresql_sequence exits with error when checking multiple databases

[wwuck]

Running the check_postgresql sequence action exits with an error when checking multiple databases.

PostgreSQL 11.1

$ check_postgres --action sequence --dbname postgres --dbname template1
Use of uninitialized value in numeric ge (>=) at /usr/bin/check_postgres line 8010.
Use of uninitialized value $typename in string eq at /usr/bin/check_postgres line 8013.
Use of uninitialized value $typename in string eq at /usr/bin/check_postgres line 8013.
Use of uninitialized value $seqname_l in substitution (s///) at /usr/bin/check_postgres line 8015.
Use of uninitialized value $seqname_l in concatenation (.) or string at /usr/bin/check_postgres line 8016.
Use of uninitialized value $seqname in concatenation (.) or string at /usr/bin/check_postgres line 8016.
Use of uninitialized value in numeric ge (>=) at /usr/bin/check_postgres line 8027.
ERROR:  syntax error at or near ")"
LINE 8: FROM ) foo
             ^

PostgreSQL 9.6

$ check_postgres --action sequence --dbname postgres --dbname template1
Use of uninitialized value in numeric ge (>=) at /usr/bin/check_postgres line 8027.
POSTGRES_SEQUENCE OK: DB "postgres" No sequences found DB "template1" No sequences found | time=0.09s time=0.09s 

[machack666]

Hi @tomascassidy — which version of check_postgres?

[machack666]

Also, I suspect this has more to do with how it's handling databases without sequences rather than something inherent to the versions specifically. Can you test creating a sequence in each of those databases and see if it then works? (Obviously this is something that would still need to be handled, but it would help narrow this down.)

[wwuck]

Hi @machack666

I am running the latest version 2.24.0-3.pgdg90+1 from the apt.postgresql.org repository.

I created a new sequence in both the postgres and template1 databases: CREATE SEQUENCE public.test_seq START 101;

I then ran the same command again: check_postgres --action sequence --dbname postgres --dbname template1.

Unfortunately the same error appears again.

[eizedev]

Hi,

the error is due to the fact that if more than one database is found in the loop, the DB version can no longer be checked because it only exists in $db for the first database ($info->{db}[0]{version}).

Workaround: For example you just need to change the following two lines (change $db->{version} to $info->{db}[0]{version} The line numbers can be a little bit different for you, because I changed some small things and use the master branch.

8010c8027,8028
<             next if ($db->{version} >= 10); # TODO: skip loop entirely
---
>             next if ($info->{db}[0]{version} >= 10); # TODO: skip loop entirely
8027c8045,8046
<         if ($db->{version} >= 10) {
---
>         if ($info->{db}[0]{version} >= 10) {

Then we use the "correct" SQL query for the sequences in PostgreSQL >= 10 and fill the corresponding variables.

[maletin]

this workaround is no longer working.

$ ./check_postgres.pl --action sequence --db template1,postgres
Use of uninitialized value in numeric ge (>=) at /tmp/check_postgres.pl line 8354.
Use of uninitialized value $typename in string eq at /tmp/check_postgres.pl line 8357.
Use of uninitialized value $typename in string eq at /tmp/check_postgres.pl line 8357.
Use of uninitialized value $seqname_l in substitution (s///) at /tmp/check_postgres.pl line 8359.
Use of uninitialized value $seqname_l in concatenation (.) or string at /tmp/check_postgres.pl line 8360.
Use of uninitialized value $seqname in concatenation (.) or string at /tmp/check_postgres.pl line 8360.
Use of uninitialized value in numeric ge (>=) at /tmp/check_postgres.pl line 8371.
ERROR:  syntax error at or near ")"
ZEILE 8: FROM ) foo
              ^
$ ./check_postgres.pl --version
check_postgres.pl version 2.25.0