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

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

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

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073