Thread: FK type mismatches?
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
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
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
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
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, Pennsylvania19073
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, Pennsylvania19073
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
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, Pennsylvania19073
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
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
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, Pennsylvania19073