Should this produce a warning?

nconway=# create table a (b int4 unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "a_b_key" for
table "a"
CREATE TABLE
nconway=# create table c (d int8 references a (b));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE

Aside from the logical inconsistency, it will also lead to poor
performance since the type mismatch will prevent index scans. I've
noticed a couple people have reported performance issues due to making
this kind of mistake.

-Neil



Thu, 04 Sep 2003 11:57:04 -0400
Neil Conway <neilc@samurai.com>
FK type mismatches?
Neil Conway <neilc@samurai.com> writes:
> Should this produce a warning?
> [ foreign-key reference to column of a different datatype ]
> Aside from the logical inconsistency, it will also lead to poor
> performance since the type mismatch will prevent index scans. I've
> noticed a couple people have reported performance issues due to making
> this kind of mistake.

There was some discussion of this a couple weeks back, but I don't recall
any clear consensus emerging.  I'm for it myself though.

If we follow Peter's recently proposed guideline, this would have to be
a NOTICE not a WARNING, because the command absolutely is doing what you
told it to do.  Peter, does that make you uncomfortable?  It's not
exactly the answer I would've wanted.  In this context it seems like we
want WARNING to mean "we'll do what you told us to do, but are you really
*sure* it is what you want?"

			regards, tom lane

Fri, 05 Sep 2003 02:14:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FK type mismatches? 
Neil Conway writes:

> Should this produce a warning?
>
> nconway=# create table a (b int4 unique);
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "a_b_key" for
> table "a"
> CREATE TABLE
> nconway=# create table c (d int8 references a (b));
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE TABLE

I don't think so.  We don't produce warnings in other cases of potential
index mismatches either.

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 5 Sep 2003 23:06:15 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FK type mismatches?
Peter Eisentraut wrote:
> Neil Conway writes:
> 
> > Should this produce a warning?
> >
> > nconway=# create table a (b int4 unique);
> > NOTICE:  CREATE TABLE / UNIQUE will create implicit index "a_b_key" for
> > table "a"
> > CREATE TABLE
> > nconway=# create table c (d int8 references a (b));
> > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> > check(s)
> > CREATE TABLE
> 
> I don't think so.  We don't produce warnings in other cases of potential
> index mismatches either.

The issue is that it isn't likely someone would be doing a foreign key
mismatch, while a mismatch in a query would be more likely.  We could
make it a HINT and then people could configure their servers to suppress
the hint if they wish.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 5 Sep 2003 17:09:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FK type mismatches?
Tom Lane writes:

> If we follow Peter's recently proposed guideline, this would have to be
> a NOTICE not a WARNING, because the command absolutely is doing what you
> told it to do.  Peter, does that make you uncomfortable?

The message itself makes me a bit uncomfortable right now, but a NOTICE
absolutely not.

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 5 Sep 2003 23:09:58 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FK type mismatches? 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > If we follow Peter's recently proposed guideline, this would have to be
> > a NOTICE not a WARNING, because the command absolutely is doing what you
> > told it to do.  Peter, does that make you uncomfortable?
> 
> The message itself makes me a bit uncomfortable right now, but a NOTICE
> absolutely not.

Oh, OK, cool.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 5 Sep 2003 17:31:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FK type mismatches?
On Fri, 2003-09-05 at 17:06, Peter Eisentraut wrote:
> Neil Conway writes:
> 
> > Should this produce a warning?
> >
> > nconway=# create table a (b int4 unique);
> > NOTICE:  CREATE TABLE / UNIQUE will create implicit index "a_b_key" for
> > table "a"
> > CREATE TABLE
> > nconway=# create table c (d int8 references a (b));
> > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> > check(s)
> > CREATE TABLE
> 
> I don't think so.  We don't produce warnings in other cases of potential
> index mismatches either.
> 

In all this discussion of NOTICE vs. WARNING, can someone remind me the
logic for INFO?  I can't seem to recall the differentiator there either.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


05 Sep 2003 17:40:37 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: FK type mismatches?
Robert Treat wrote:
> On Fri, 2003-09-05 at 17:06, Peter Eisentraut wrote:
> > Neil Conway writes:
> > 
> > > Should this produce a warning?
> > >
> > > nconway=# create table a (b int4 unique);
> > > NOTICE:  CREATE TABLE / UNIQUE will create implicit index "a_b_key" for
> > > table "a"
> > > CREATE TABLE
> > > nconway=# create table c (d int8 references a (b));
> > > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> > > check(s)
> > > CREATE TABLE
> > 
> > I don't think so.  We don't produce warnings in other cases of potential
> > index mismatches either.
> > 
> 
> In all this discussion of NOTICE vs. WARNING, can someone remind me the
> logic for INFO?  I can't seem to recall the differentiator there either.

Right now I see INFO being used mostly for vacuum status.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 5 Sep 2003 17:59:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FK type mismatches?
Robert Treat writes:

> In all this discussion of NOTICE vs. WARNING, can someone remind me the
> logic for INFO?  I can't seem to recall the differentiator there either.

Info is something you request explicitly.  In the past, the result for
EXPLAIN and SHOW were sent as INFO, but now those are sent as query
results, and there are in fact very few INFO instances left.  Also, INFO
is not affect by the log level settings.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 6 Sep 2003 00:09:01 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FK type mismatches?
Peter Eisentraut <peter_e@gmx.net> writes:
> Robert Treat writes:
>> In all this discussion of NOTICE vs. WARNING, can someone remind me the
>> logic for INFO?  I can't seem to recall the differentiator there either.

> Info is something you request explicitly.  In the past, the result for
> EXPLAIN and SHOW were sent as INFO, but now those are sent as query
> results, and there are in fact very few INFO instances left.  Also, INFO
> is not affect by the log level settings.

In a severity sense I think INFO is identical to NOTICE.  We invented
the category as a means of preserving the pre-existing behavior of
VACUUM VERBOSE (ie, always show the messages) when we added
client_min_messages configurability.

			regards, tom lane

Fri, 05 Sep 2003 19:48:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FK type mismatches? 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > If we follow Peter's recently proposed guideline, this would have to be
> > a NOTICE not a WARNING, because the command absolutely is doing what you
> > told it to do.  Peter, does that make you uncomfortable?
> 
> The message itself makes me a bit uncomfortable right now, but a NOTICE
> absolutely not.

Added to TODO:

	* Issue NOTICE if foreign key data type doesn't match primary key

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Sep 2003 16:17:02 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FK type mismatches?