sequence-check with multiple databases

[phinjensen]

ID: 98 Version: 2.19.0 Date: 2012-01-19 05:46 EST Author: Martin von Oertzen ([email protected])

---

one part of the --action=sequence needs over 3 minutes with postgres 9.1.2:

SELECT nspname, seq.relname, typname
 FROM pg_attrdef
 JOIN pg_attribute ON (attrelid, attnum) = (adrelid, adnum)
 JOIN pg_type on pg_type.oid = atttypid
 JOIN pg_class rel ON rel.oid = attrelid
 JOIN pg_class seq ON seq.relname = regexp_replace(adsrc,
$re$^nextval\('(.+?)'::regclass\)$$re$, $$\1$$)
 AND seq.relnamespace = rel.relnamespace
 JOIN pg_namespace nsp ON nsp.oid = seq.relnamespace
 WHERE adsrc ~ 'nextval' AND seq.relkind = 'S' AND typname IN ('int2', 'int4',
'int8')

on an other computer i use postgres 8.3.16:

$ check_postgres_sequence --db=postgres --perflimit=1
POSTGRES_SEQUENCE OK: DB "postgres" public.db_clients_id_seq=0% (calls
left=2147483539) | time=0.05s public.db_clients_id_seq=0%;85%;95%

$ check_postgres_sequence --db=mydb --perflimit=1
Can't use an undefined value as an ARRAY reference at check_postgres_sequence
line 7118.

$ check_postgres_sequence --db=postgres,mydb --perflimit=1
ERROR: ERROR:  relation "public.db_clients_id_seq" does not exist

[phinjensen]

Author: Martin von Oertzen ([email protected]) Date: 2013-01-10 04:40:22 EST

---

check_postgres_sequence is still wrong, when there are different databases. tested database-versions: 8.3, 8.4, 9.0, 9.1 and 9.2. check_postgres-version: 2.20.0 from 2012-12-27.

$ pg_createcluster -d /tmp/cp -p 5555 -start 9.2 cp
postgres=# create table cp ( cp serial );
NOTICE:  CREATE TABLE will create implicit sequence "cp_cp_seq" for serial
column "cp.cp"
postgres=# create database cp;
$ check_postgres.pl -p 5555 --action=sequence --db=postgres,cp
ERROR:  relation "public.cp_cp_seq" does not exist
LINE 7: FROM public.cp_cp_seq) foo

[ross211]

This got it working for me:

--- check_postgres.pl.orig  2014-01-21 16:10:44.000000000 +1100
+++ check_postgres.pl   2014-01-21 16:10:39.000000000 +1100
@@ -7235,11 +7235,20 @@
     my $MAXINT8 = 9223372036854775807;

     my $limit = 0;
+    my $maxp = 0;
+    my %seqinfo;
+
+    ## Keep track of which database we are on, to allow dbnumber to work
+    my $num = 0;

     for $db (@{$info->{db}}) {
+        $num++;
         my (@crit,@warn,@ok);
-        my $maxp = 0;
-        my %seqinfo;
+        # In MRTG mode we want an aggregate summary of all the dbs, otherwise it's per db so reset these values on each db
+        if(!$MRTG) {
+            $maxp = 0;
+            undef %seqinfo;
+        }
         my %seqperf;
         my $multidb = @{$info->{db}} > 1 ? "$db->{dbname}." : '';
         for my $r (@{$db->{slurp}}) {
@@ -7256,7 +7265,7 @@
 FROM $seqname) foo
 };

-            my $seqinfo = run_command($SQL, { target => $db });
+            my $seqinfo = run_command($SQL, { dbnumber => $num });
             my $r2 = $seqinfo->{db}[0]{slurp}[0];
             my ($last, $slots, $used, $percent, $left) = @$r2{qw/ last_value slots used percent numleft / };
             if (! defined $last) {
@@ -7268,7 +7277,7 @@
             if ($percent >= $maxp) {
                 $maxp = $percent;
                 if (! exists $opt{perflimit} or $limit++ < $opt{perflimit}) {
-                    push @{$seqinfo{$percent}} => $MRTG ? [$seqname,$percent,$slots,$used,$left] : $msg;
+                    push @{$seqinfo{$percent}} => $MRTG ? [$nicename,$percent,$slots,$used,$left] : $msg;
                 }
             }
             next if $MRTG;
@@ -7280,10 +7289,8 @@
                 push @warn => $msg;
             }
         }
-        if ($MRTG) {
-            my $msg = join ' | ' => map { $_->[0] } @{$seqinfo{$maxp}};
-            do_mrtg({one => $maxp, msg => $msg});
-        }
+        next if $MRTG;
+
         $limit = 0;
         PERF: for my $val (sort { $b <=> $a } keys %seqperf) {
             for my $seq (sort { $seqperf{$val}{$a}->[0] <=> $seqperf{$val}{$b}->[0] or $a cmp $b } keys %{$seqperf{$val}}) {
@@ -7307,6 +7314,10 @@
             }
         }
     }
+    if ($MRTG) {
+        my $msg = join ' | ' => map { $_->[0] } @{$seqinfo{$maxp}};
+        do_mrtg({one => $maxp, msg => $msg});
+    }

     return;

[eizedev]

Hi, i have posted a workaround here: https://github.com/bucardo/check_postgres/issues/155#issuecomment-470848304