How to suppress notices using PostgreSQL with DBI

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.

Re: How to suppress notices using PostgreSQL with DBI
by tirwhan (Abbot) on Mar 31, 2006 at 18:25 UTC

You can set $dbh->{Warn} to 0 while executing the table creation statements. This has the drawback that other (possibly important) database error messages are also suppressed. So a better strategy can be do redefine $SIG{__WARN__} to only suppress specific warnings:

my $tmpwarn = $SIG{__WARN__};
$SIG{__WARN__} = 
  sub { print STDERR @_ if $_[0] !~ m/NOTICE:  CREATE TABLE/; };
$sth->execute();
$SIG{__WARN__} = $tmpwarn;
[download]

Or another option would of course be to redirect STDERR to a log file entirely.

All dogma is stupid.

[reply]
[d/l]
[select]

Re: How to suppress notices using PostgreSQL with DBI
by jasonk (Parson) on Mar 31, 2006 at 20:16 UTC

Just make your application create the indexes explicitly, instead of generating SQL that causes these warnings.

# code like this causes the notice you listed
CREATE TABLE foo (
  id char(32) unique
);

# if you don't want the notice, don't let it create
# implicit indexes, create them yourself explicitly
CREATE TABLE foo (
  id char(32)
);
ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_id_key UNIQUE (id);
[download]

We're not surrounded, we're in a target-rich environment!

[reply]
[d/l]

But I want a PRIMARY key (was: How to suppress notices using PostgreSQL with DBI
by tgeliot (Initiate) on May 12, 2008 at 17:16 UTC

jasonk's reply feels more elegant to me -- avoiding the creation of the message, rather than hiding it. But both our original supplicant and I want to create a PRIMARY key, not just an index. My (perhaps naive) understanding is that a primary key provides an additional performance benefit by controling the placement of records on disk, and that it is not possible to ALTER a table to add a primary key. Given this, do I have to use tirwhan's message-suppression technique to get a primary key without extra noise? Thanks Topher

[reply]