Capture NOTICE from PostgreSQL in Perl DBI

user_302915 0
Viewed 64

I'm using full text search with user generated input on a PostgreSQL table, "the a" in this example.

my $dbh = DBI->connect("...", { RaiseError => 0, AutoCommit => 1 });
my $sth = $dbh->prepare("SELECT name from mytable WHERE tsv @@ plainto_tsquery('the a')");
my $rv = $sth->execute();

If the user input only contains stop words, I get a NOTICE on STDERR and the query returns no results, but produces no error.

I would like to capture that NOTICE in Perl to possibly alert the user to rephrase the search, but I can't seem to access it.

Setting RaiseError to 1 doesn't change anything and $dbh->pg_notifies returns undef.

Any ideas ?

error-handling perl postgresql
2 Answers

I presume that the mention of "NOTICE" means that something does a RAISE. How that behaves is configurable but delving into that would require far more detail.

At perl level, there are various ways to get to the STDERR stream and capture what's sent to it.

If these are warn-ings then setting a hook for $SIG{__WARN__} runs that subroutine whenever a warning is to be printed

{
    local $SIG{__WARN__} = sub { say "Got warning: $_[0]"; };

    warn "a warning";
}

So you can capture it this way, do what you wish with it, and then perhaps reemit. The $_[0] in the example has the string a warning, and after the anonymous sub runs the control is back at the next line after a warning is issued (the warn statement in this snippet). See %SIG.

I put this in a block only to be able to local-ize the change to SIG{__WARN__}, what is practically compulsory (if this global isn't local-ized its change affects all code). So if this code is in a suitable lexical scope anyway then that block isn't needed.

But this won't catch things printed directly to STDERR. For that the most practical way is to use libraries, and a simple and convenient one for this purpose is Capture::Tiny

use Capture::Tiny qw(capture);

my ($stdout, $stderr, $exit) = capture { 
    say "to STDOUT"; 
    say STDERR "to STDERR"; 
    warn "a warn-ing"; 
    # ...
};

Now $stdout has text to STDOUT while $stderr has to STDERR followed by a warn-ing. So your database code would go in such a block and the NOTICE should wind up in that $stderr variable. There is also a capture_stderr function if you prefer to capture only that this way.

user_46533790

That doesn't seem to work as it should, and it is recognized as an unsolved bug.

user_64643080
Related