pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAvoiding "will create implicit index" NOTICE
6+ messages / 4 participants
[nested] [flat]
* Avoiding "will create implicit index" NOTICE
@ 2009-06-01 19:32 Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 2 replies; 6+ messages in thread
From: Bryce Nesbitt @ 2009-06-01 19:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Avoiding "will create implicit index" NOTICE
@ 2009-06-01 20:07 Scott Marlowe <scott.marlowe@gmail.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2009-06-01 20:07 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Avoiding "will create implicit index" NOTICE
@ 2009-06-11 06:20 Bryce Nesbitt <bryce2@obviously.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 1 reply; 6+ messages in thread
From: Bryce Nesbitt @ 2009-06-11 06:20 UTC (permalink / raw)
To: pgsql-sql
Thanks, that's good.
Rob Sargent wrote:
> create table junk_six (foo int)
> create unique index junk_six_id on junk_six(foo)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Avoiding "will create implicit index" NOTICE
@ 2009-06-11 07:03 Bryce Nesbitt <bryce2@obviously.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bryce Nesbitt @ 2009-06-11 07:03 UTC (permalink / raw)
To: pgsql-sql
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)
>>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Avoiding "will create implicit index" NOTICE
@ 2009-06-11 07:31 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2009-06-11 07:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Avoiding "will create implicit index" NOTICE
@ 2009-06-11 16:05 Rob Sargent <robjsargent@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2009-06-11 16:05 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-06-11 16:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-01 19:32 Avoiding "will create implicit index" NOTICE Bryce Nesbitt <bryce2@obviously.com>
2009-06-01 20:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-06-11 06:20 ` Bryce Nesbitt <bryce2@obviously.com>
2009-06-11 07:03 ` Bryce Nesbitt <bryce2@obviously.com>
2009-06-11 07:31 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-06-11 16:05 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox