[check_postgres] [commit] Refactor EXISTS thing, make older PG version testing better.
check_postgres at bucardo.org
check_postgres at bucardo.org
Wed Apr 29 20:11:26 UTC 2009
Committed by Greg Sabino Mullane <greg at endpoint.com>
Refactor EXISTS thing, make older PG version testing better.
---
check_postgres.pl | 2 +-
t/02_backends.t | 5 +-
t/02_bloat.t | 3 +-
t/02_connection.t | 2 +-
t/02_disabled_triggers.t | 7 +-
t/02_fsm_pages.t | 9 +--
t/02_fsm_relations.t | 9 +--
t/02_last_analyze.t | 2 +-
t/02_last_vacuum.t | 2 +-
t/02_locks.t | 3 +-
t/02_query_runtime.t | 6 +-
t/02_sequence.t | 14 +----
t/02_version.t | 1 -
t/02_wal_files.t | 3 +-
t/99_perlcritic.t | 2 -
t/CP_Testing.pm | 147 +++++++++++++++++++++++++++++++++-------------
16 files changed, 132 insertions(+), 85 deletions(-)
diff --git a/check_postgres.pl b/check_postgres.pl
index 8189b37..789886c 100755
--- a/check_postgres.pl
+++ b/check_postgres.pl
@@ -2539,7 +2539,7 @@ sub check_connection {
## Parse it out and return our information
for $db (@{$info->{db}}) {
- if ($db->{slurp} !~ /PostgreSQL (\S+)/o) { ## no critic (ProhibitUnusedCapture)
+ if ($db->{slurp} !~ /PostgreSQL (\d+\.\d+\S+)/o) { ## no critic (ProhibitUnusedCapture)
add_unknown msg('invalid-query', $db->{slurp});
next;
}
diff --git a/t/02_backends.t b/t/02_backends.t
index ed31df3..1f079f9 100644
--- a/t/02_backends.t
+++ b/t/02_backends.t
@@ -113,7 +113,8 @@ like ($cp->run('-c -7'), qr{^POSTGRES_BACKENDS CRITICAL}, $t);
like ($cp->run('-c -8'), qr{^POSTGRES_BACKENDS OK}, $t);
$t=qq{$S works when no items caught by pg_stat_activity};
-## This is tricky to test properly.
+
+$cp->drop_schema_if_exists($fakeschema);
$cp->create_fake_pg_table('pg_stat_activity');
like ($cp->run(), qr{^POSTGRES_BACKENDS OK: .+No connections}, $t);
@@ -151,4 +152,6 @@ like ($cp->run('--include=postgres --exclude=postgres'), qr{POSTGRES_BACKENDS OK
$t=qq{$S returned correct performance data with include};
like ($cp->run('--include=postgres'), qr{ \| time=(\d\.\d\d) ardala=0 beedeebeedee=0 postgres=3}, $t);
+$cp->drop_schema_if_exists($fakeschema);
+
exit;
diff --git a/t/02_bloat.t b/t/02_bloat.t
index 07fa768..b05c14f 100644
--- a/t/02_bloat.t
+++ b/t/02_bloat.t
@@ -50,8 +50,7 @@ like ($cp->run("-w=1% --include=foobar"), qr{^$label UNKNOWN:.+No matching relat
$t=qq{$S returns okay for fresh database with no bloat};
like ($cp->run("-w=1m"), qr{^$label OK: DB "postgres"}, $t);
-local $dbh->{Warn} = 0;
-$dbh->do("DROP TABLE IF EXISTS $tname");
+$cp->drop_table_if_exists($tname);
$dbh->do("CREATE TABLE $tname AS SELECT 123::int AS foo FROM generate_series(1,10000)");
$dbh->do("UPDATE $tname SET foo = foo") for 1..1;
$dbh->do('ANALYZE');
diff --git a/t/02_connection.t b/t/02_connection.t
index b3c23b2..b44d3a9 100644
--- a/t/02_connection.t
+++ b/t/02_connection.t
@@ -50,7 +50,7 @@ like ($cp->run('-c 23%%'), qr{^ERROR: No warning or critical}, $t);
$t=qq{$S returns correct MRTG output when rows found};
is ($cp->run('--output=MRTG'), qq{1\n0\n\n\n}, $t);
-$cp->bad_fake_version();
+$cp->fake_version('ABC');
$t=qq{$S fails if there's a fake version function};
like ($cp->run(), qr{^POSTGRES_CONNECTION UNKNOWN:}, $t);
$cp->reset_path();
diff --git a/t/02_disabled_triggers.t b/t/02_disabled_triggers.t
index 5ed391b..dee8fe1 100644
--- a/t/02_disabled_triggers.t
+++ b/t/02_disabled_triggers.t
@@ -52,12 +52,11 @@ for (-1, 0, 'a') {
sub cleanup {
$dbh->rollback;
- local $dbh->{Warn} = 0;
- $dbh->do(qq{DROP TABLE IF EXISTS "$testtbl"});
- $dbh->do(qq{DROP FUNCTION IF EXISTS "${testtrig_prefix}func"()});
- $dbh->commit;
+ $cp->drop_table_if_exists($testtbl);
+ $cp->drop_function_if_exists($testtrig_prefix.'func','');
}
END { cleanup(); }
+
# Set up a test table with two triggers.
cleanup();
$dbh->do(qq{CREATE TABLE "$testtbl" (a integer)});
diff --git a/t/02_fsm_pages.t b/t/02_fsm_pages.t
index 105be21..ce02c83 100644
--- a/t/02_fsm_pages.t
+++ b/t/02_fsm_pages.t
@@ -30,12 +30,9 @@ like ($cp->run('--critical=50'), qr{ERROR:.+must be a percentage}, $t);
## Create a fake fsm 'view' for testing
$cp->set_fake_schema();
my $schema = $cp->get_fake_schema();
-{
- local $dbh->{Warn};
- $dbh->do("DROP TABLE IF EXISTS $schema.pg_freespacemap_pages");
- $dbh->do("DROP TABLE IF EXISTS $schema.pg_freespacemap_relations");
- $dbh->do('DROP FUNCTION IF EXISTS public.version()');
-}
+$cp->drop_table_if_exists($schema, 'pg_freespacemap_pages');
+$cp->drop_table_if_exists($schema, 'pg_freespacemap_relations');
+
$dbh->do(qq{
CREATE TABLE $schema.pg_freespacemap_pages (
reltablespace oid,
diff --git a/t/02_fsm_relations.t b/t/02_fsm_relations.t
index 9f0e66a..8ee30d8 100644
--- a/t/02_fsm_relations.t
+++ b/t/02_fsm_relations.t
@@ -30,12 +30,9 @@ like ($cp->run('--critical=50'), qr{ERROR:.+must be a percentage}, $t);
## Create a fake fsm 'view' for testing
$cp->set_fake_schema();
my $schema = $cp->get_fake_schema();
-{
- local $dbh->{Warn};
- $dbh->do("DROP TABLE IF EXISTS $schema.pg_freespacemap_pages");
- $dbh->do("DROP TABLE IF EXISTS $schema.pg_freespacemap_relations");
- $dbh->do('DROP FUNCTION IF EXISTS public.version()');
-}
+$cp->drop_table_if_exists($schema, 'pg_freespacemap_pages');
+$cp->drop_table_if_exists($schema, 'pg_freespacemap_relations');
+
$dbh->do(qq{
CREATE TABLE $schema.pg_freespacemap_pages (
reltablespace oid,
diff --git a/t/02_last_analyze.t b/t/02_last_analyze.t
index 9eef6f8..9865dd3 100644
--- a/t/02_last_analyze.t
+++ b/t/02_last_analyze.t
@@ -54,7 +54,7 @@ like ($cp->run(qq{-w 0 --includeuser=gandalf}), qr{No matching.*user}, $t);
local $dbh->{Warn};
$dbh->do('ANALYZE');
-$dbh->do(qq{DROP TABLE IF EXISTS $testtbl});
+$cp->drop_table_if_exists($testtbl);
$dbh->do(qq{CREATE TABLE $testtbl AS SELECT 123::INTEGER AS a FROM generate_series(1,200000)});
$dbh->commit();
diff --git a/t/02_last_vacuum.t b/t/02_last_vacuum.t
index 4527daf..62b5262 100644
--- a/t/02_last_vacuum.t
+++ b/t/02_last_vacuum.t
@@ -55,7 +55,7 @@ like ($cp->run(qq{-w 0 --includeuser=gandalf}), qr{No matching.*user}, $t);
local $dbh->{Warn};
local $dbh->{AutoCommit} = 1;
$dbh->do('VACUUM');
-$dbh->do(qq{DROP TABLE IF EXISTS $testtbl});
+$cp->drop_table_if_exists($testtbl);
$dbh->do(qq{CREATE TABLE $testtbl AS SELECT 123::INTEGER AS a FROM generate_series(1,200000)});
$dbh->commit();
diff --git a/t/02_locks.t b/t/02_locks.t
index 2f231c5..c4b055d 100644
--- a/t/02_locks.t
+++ b/t/02_locks.t
@@ -27,6 +27,7 @@ like ($cp->run('--dbname=foo'), qr{database "foo" does not exist}, $t);
$t=qq{$S fails when no matching databases found};
like ($cp->run('--include=foo'), qr{No matching databases found}, $t);
+$cp->drop_schema_if_exists($fakeschema);
$cp->create_fake_pg_table('pg_locks');
$SQL = q{SELECT oid FROM pg_database WHERE datname = 'postgres'};
my $dboid = $dbh->selectall_arrayref($SQL)->[0][0];
@@ -71,6 +72,6 @@ $t=qq{$S returns correct multiple item output};
like ($cp->run('--warning="waiting=1;exclusive=2"'),
qr{POSTGRES_LOCKS WARNING.*total "waiting" locks: 1 \* total "exclusive" locks: 2 }, $t);
-$cp->remove_fake_pg_table('pg_locks');
+$cp->drop_schema_if_exists($fakeschema);
exit;
diff --git a/t/02_query_runtime.t b/t/02_query_runtime.t
index 224d187..e0c5333 100644
--- a/t/02_query_runtime.t
+++ b/t/02_query_runtime.t
@@ -22,9 +22,9 @@ $dbname = $cp->get_dbname;
$host = $cp->get_host();
my $label = 'POSTGRES_QUERY_RUNTIME';
-local $dbh->{Warn} = 0;
-$dbh->do(qq{DROP TABLE IF EXISTS $testtbl CASCADE});
-$dbh->do(qq{DROP VIEW IF EXISTS $testview});
+$cp->drop_table_if_exists($testtbl);
+$cp->drop_view_if_exists($testview);
+
$dbh->do(qq{CREATE TABLE "$testtbl" ("a" integer)}) or die $dbh->errstr;
$dbh->commit;
diff --git a/t/02_sequence.t b/t/02_sequence.t
index a67e095..cc90e09 100644
--- a/t/02_sequence.t
+++ b/t/02_sequence.t
@@ -28,11 +28,7 @@ $t=qq{$S fails when called with an invalid option};
like ($cp->run('--critical=80'), qr{ERROR:.+must be a percentage}, $t);
my $seqname = 'cp_test_sequence';
-{
- local $dbh->{Warn};
- $dbh->do("DROP SEQUENCE IF EXISTS $seqname");
- $dbh->commit();
-}
+$cp->drop_sequence_if_exists($seqname);
$t=qq{$S works when no sequences exist};
like ($cp->run(''), qr{OK:.+No sequences found}, $t);
@@ -44,13 +40,7 @@ $t=qq{$S fails when sequence not readable};
like ($cp->run(''), qr{ERROR:\s*(?:Could not determine|cannot access temporary)}, $t);
$dbh->do("CREATE SEQUENCE $seqname");
-$dbh->commit();
-
-{
- local $dbh->{Warn};
- $dbh->do("DROP SEQUENCE IF EXISTS ${seqname}2");
- $dbh->commit();
-}
+$cp->drop_sequence_if_exists($seqname.'2');
$t=qq{$S returns correct information for a new sequence};
like ($cp->run(''), qr{OK:.+public.cp_test_sequence=0% \(calls left=9223372036854775806\)}, $t);
diff --git a/t/02_version.t b/t/02_version.t
index e37408b..9f93b33 100644
--- a/t/02_version.t
+++ b/t/02_version.t
@@ -108,7 +108,6 @@ is ($cp->run('--output=MRTG --mrtg=7.8.12'), qq{1\n0\n\n7.8.12\n}, $t);
local $dbh->{Warn} = 0;
$dbh->do('DROP SCHEMA cptest CASCADE');
-$dbh->do('DROP FUNCTION IF EXISTS public.version()');
$cp->reset_path();
exit;
diff --git a/t/02_wal_files.t b/t/02_wal_files.t
index 9cf74f3..22c79d7 100644
--- a/t/02_wal_files.t
+++ b/t/02_wal_files.t
@@ -32,6 +32,7 @@ $t=qq{$S works as expected for criticals};
like ($cp->run('--critical=30'), qr{POSTGRES_WAL_FILES OK}, $t);
like ($cp->run('--critical=0'), qr{POSTGRES_WAL_FILES CRITICAL}, $t);
+$cp->drop_schema_if_exists($fakeschema);
$cp->create_fake_pg_table('pg_ls_dir', 'text');
like ($cp->run('--critical=1'), qr{POSTGRES_WAL_FILES OK}, $t);
@@ -48,6 +49,6 @@ is ($cp->run('--critical=1 --output=mrtg'), "99\n0\n\n\n", $t);
$t=qq{$S returns correct MRTG information};
is ($cp->run('--critical=101 --output=mrtg'), "99\n0\n\n\n", $t);
-$cp->remove_fake_pg_table('pg_ls_dir()');
+$cp->drop_schema_if_exists($fakeschema);
exit;
diff --git a/t/99_perlcritic.t b/t/99_perlcritic.t
index 0ad4d09..6346d3c 100644
--- a/t/99_perlcritic.t
+++ b/t/99_perlcritic.t
@@ -111,8 +111,6 @@ $critic = Perl::Critic->new(-severity => 1, '-profile-strictness' => 'quiet');
my $count = 1;
for my $filename (sort @testfiles) {
-e $filename or die qq{Could not find "$filename"!};
- pass('Skipping test files for now');
- next;
my @vio = $critic->critique($filename);
my $vios = 0;
diff --git a/t/CP_Testing.pm b/t/CP_Testing.pm
index e7d4f7b..c7fac75 100644
--- a/t/CP_Testing.pm
+++ b/t/CP_Testing.pm
@@ -191,8 +191,12 @@ sub test_database_handle {
$dbh->{RaiseError} = 1;
if (! exists $self->{keep_old_schema}) {
- local $dbh->{Warn};
- $dbh->do("DROP SCHEMA IF EXISTS $fakeschema CASCADE");
+ my $SQL = "SELECT count(*) FROM pg_namespace WHERE nspname = " . $dbh->quote($fakeschema);
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ local $dbh->{Warn} = 0;
+ $dbh->do("DROP SCHEMA $fakeschema CASCADE");
+ }
}
if ($arg->{dbname} ne $self->{dbname}) {
@@ -319,8 +323,7 @@ sub create_fake_pg_table {
my $dbh = $self->{dbh} || die;
my $dbuser = $self->{testuser} || die;
if ($self->schema_exists($dbh,$fakeschema)) {
- local $dbh->{Warn};
- $dbh->do("DROP TABLE IF EXISTS $fakeschema.$name");
+ $self->drop_table_if_exists($fakeschema,$name);
}
else {
$dbh->do("CREATE SCHEMA $fakeschema");
@@ -336,7 +339,7 @@ sub create_fake_pg_table {
if ($args) {
local $dbh->{Warn};
- $dbh->do("DROP FUNCTION IF EXISTS $fakeschema.$name($args)");
+ $self->drop_function_if_exists($fakeschema,$name,$args);
$dbh->do("CREATE FUNCTION $fakeschema.$name($args) RETURNS SETOF TEXT LANGUAGE SQL AS 'SELECT * FROM $fakeschema.$name; '");
}
@@ -366,23 +369,6 @@ sub set_fake_schema {
} ## end of set_fake_schema
-sub remove_fake_pg_table {
-
- my $self = shift;
- my $name = shift || die;
- (my $name2 = $name) =~ s/\(.+//;
- my $dbh = $self->{dbh} || die;
- my $dbuser = $self->{testuser} || die;
- {
- local $dbh->{Warn};
- $dbh->do("DROP TABLE IF EXISTS public.$name2");
- }
- $dbh->do("ALTER USER $dbuser SET search_path = public");
- $dbh->commit();
-
-} ## end of remove_fake_pg_table
-
-
sub table_exists {
my ($self,$dbh,$table) = @_;
@@ -409,6 +395,101 @@ sub schema_exists {
} ## end of schema_exists
+sub drop_schema_if_exists {
+
+ my ($self,$name) = @_;
+ my $dbh = $self->{dbh} || die;
+
+ if (! exists $self->{keep_old_schema}) {
+ my $SQL = "SELECT count(*) FROM pg_namespace WHERE nspname = " . $dbh->quote($name);
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ local $dbh->{Warn};
+ $dbh->do("DROP SCHEMA $name CASCADE");
+ $dbh->commit();
+ }
+ }
+ return;
+
+} ## end of drop_schema_if_exists
+
+
+sub drop_table_if_exists {
+
+ my ($self,$name,$name2) = @_;
+ my $dbh = $self->{dbh} || die;
+
+ my $schema = '';
+ if ($name2) {
+ $schema = $name;
+ $name = $name2;
+ }
+
+ my $safetable = $dbh->quote($name);
+ my $safeschema = $dbh->quote($schema);
+ my $SQL = $schema
+ ? q{SELECT count(*) FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) }.
+ qq{WHERE relkind = 'r' AND nspname = $safeschema AND relname = $safetable}
+ : qq{SELECT count(*) FROM pg_class WHERE relkind='r' AND relname = $safetable};
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ local $dbh->{Warn};
+ $dbh->do("DROP TABLE $name CASCADE");
+ $dbh->commit();
+ }
+ return;
+
+} ## end of drop_table_if_exists
+
+
+sub drop_view_if_exists {
+
+ my ($self,$name) = @_;
+ my $dbh = $self->{dbh} || die;
+
+ my $SQL = q{SELECT count(*) FROM pg_class WHERE relkind='v' AND relname = } . $dbh->quote($name);
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ local $dbh->{Warn};
+ $dbh->do("DROP VIEW $name");
+ $dbh->commit();
+ }
+ return;
+
+} ## end of drop_view_if_exists
+
+
+sub drop_sequence_if_exists {
+
+ my ($self,$name) = @_;
+ my $dbh = $self->{dbh} || die;
+
+ my $SQL = q{SELECT count(*) FROM pg_class WHERE relkind = 'S' AND relname = } . $dbh->quote($name);
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ $dbh->do("DROP SEQUENCE $name");
+ $dbh->commit();
+ }
+
+} ## end of drop_sequence_if_exists
+
+
+sub drop_function_if_exists {
+
+ my ($self,$name,$args) = @_;
+ my $dbh = $self->{dbh} || die;
+
+ my $SQL = q{SELECT count(*) FROM pg_proc WHERE proname = }. $dbh->quote($name);
+ my $count = $dbh->selectall_arrayref($SQL)->[0][0];
+ if ($count) {
+ $dbh->do("DROP FUNCTION $name($args)");
+ $dbh->commit();
+ }
+ return;
+
+} ## end of drop_function_if_exists
+
+
sub fake_version {
my $self = shift;
@@ -430,30 +511,11 @@ SELECT 'PostgreSQL $version on fakefunction for check_postgres.pl testing'::text
});
$dbh->do("ALTER USER $dbuser SET search_path = $fakeschema, public, pg_catalog");
$dbh->commit();
+ return;
} ## end of fake version
-sub bad_fake_version {
-
- my $self = shift;
- my $version = shift || '9.9';
- my $dbh = $self->{dbh} || die;
- my $dbuser = $self->{testuser} || die;
-
- $dbh->do(qq{
-CREATE OR REPLACE FUNCTION public.version()
-RETURNS TEXT
-LANGUAGE SQL
-AS \$\$
-SELECT 'Postgres $version on fakefunction for check_postgres.pl testing'::text;
-\$\$
-});
- $dbh->do("ALTER USER $dbuser SET search_path = public, pg_catalog");
- $dbh->commit();
-
-} ## end of bad fake version
-
sub fake_self_version {
## Look out...
@@ -472,6 +534,7 @@ sub fake_self_version {
} ## end of fake_self_version
+
sub restore_self_version {
my $self = shift;
--
1.6.0.5
More information about the Check_postgres
mailing list