Обсуждение: Avoiding "will create implicit index" NOTICE
I'm looking for a good way to avoid triggering the "will create implicit index" NOTICE that Postgres (all versions) puts out. This ends up spamming cron scripts for no good reason: => create table junk_six (foo int, primary key (foo)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "junk_six_pkey" for table "junk_six" CREATE TABLE I've got a hacky solution (in perl), pulled from http://www.perlmonks.org/index.pl/jacques?node_id=540511 which suppresses the warning: my $tmpwarn = $SIG{__WARN__}; $SIG{__WARN__} = sub { print STDERR @_ if $_[0] !~ m/NOTICE: CREATE TABLE/; }; $sqldb->sql_execute("create table junk_six (foo int, primary key (foo));"); $SIG{__WARN__} = $tmpwarn; And I know that I can edit the warning level in postgresql.conf with some other side effects. But the best solution would be to avoid the notice in the first place. Is this possible? -Bryce Keywords: postgres, warning, error, suppress, disable, avoid, hide, stderr, stdout
On Mon, Jun 1, 2009 at 1:32 PM, Bryce Nesbitt <bryce2@obviously.com> wrote: > I'm looking for a good way to avoid triggering the "will create implicit > index" NOTICE that Postgres (all versions) puts out. This ends up spamming > cron scripts for no good reason: > > => create table junk_six (foo int, primary key (foo)); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "junk_six_pkey" for table "junk_six" > CREATE TABLE > > I've got a hacky solution (in perl), pulled from > http://www.perlmonks.org/index.pl/jacques?node_id=540511 > which suppresses the warning: > > my $tmpwarn = $SIG{__WARN__}; > $SIG{__WARN__} = sub { print STDERR @_ if $_[0] !~ m/NOTICE: CREATE TABLE/; > }; > $sqldb->sql_execute("create table junk_six (foo int, primary key (foo));"); > $SIG{__WARN__} = $tmpwarn; > > And I know that I can edit the warning level in postgresql.conf with some > other side effects. > But the best solution would be to avoid the notice in the first place. Is > this possible? You can also set log_min_messages by the connection, by the user, and by the database. alter user bubba set log_min_messages=error; etc.
Thanks, that's good. Rob Sargent wrote: > create table junk_six (foo int) > create unique index junk_six_id on junk_six(foo)
Hmm, no. I still get the NOTICE. How can I create the primary key without triggering a NOTICE? bnesbitt=> create unique index test_5_pkey on test_5 (userid, site_key); CREATE INDEX bnesbitt=> alter table test_5 add primary key (userid, site_key); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_5_pkey1" for table "test_5" ALTER TABLE bnesbitt=> \d test_5 Table "public.test_5" +-------------------+---------+-----------+ | Column | Type | Modifiers | +-------------------+---------+-----------+ | userid | integer | not null | | site_key | integer | not null | | ranking_365 | integer | default 0 | | downloads_total | integer | default 0 | | ranking_total | integer | default 0 | +-------------------+---------+-----------+ Indexes: "test_5_pkey1" PRIMARY KEY, btree (userid, site_key) "test_5_pkey" UNIQUE, btree (userid, site_key) Foreign-key constraints: "test_5_site_key_fkey" FOREIGN KEY (site_key) REFERENCES contexts(context_key) ON DELETE CASCADE "test_5_userid_fkey" FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE Bryce Nesbitt wrote: > Thanks, that's good. > > Rob Sargent wrote: > >> create table junk_six (foo int) >> create unique index junk_six_id on junk_six(foo) >> > >
In response to Bryce Nesbitt : > Hmm, no. I still get the NOTICE. How can I create the primary key > without triggering a NOTICE? Sure, set client_min_messages='...' test=*# create table bla(id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bla_pkey" for table "bla" CREATE TABLE test=*# rollback; ROLLBACK test=# set client_min_messages='warning'; SET test=*# create table bla(id int primary key); CREATE TABLE test=*# Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote: > In response to Bryce Nesbitt : > >> Hmm, no. I still get the NOTICE. How can I create the primary key >> without triggering a NOTICE? >> > > Sure, set client_min_messages='...' > > test=*# create table bla(id int primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bla_pkey" for table "bla" > CREATE TABLE > test=*# rollback; > ROLLBACK > test=# set client_min_messages='warning'; > SET > test=*# create table bla(id int primary key); > CREATE TABLE > test=*# > > > Andreas > Funny thing is I'm using set client_min_message in my own scripts! Also was assuming OP was in a scripting/temp-table mode and that the notion of explicit primary key (vs. simply stating the index) isn't of great value in that realm I don't think.