Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> Yeah, I was thinking that a possible fix might be to reject the creation
> of such an FK, but I'm not sure what would be a good test to determine
> acceptability. It's not as easy as rejecting different typmods, in
> general: for example, rejecting FKs of varchars because their max
> lengths are different would be inappropriate.

Right. I looked in the spec, and noted that they *used* to require the
referencing and referenced types to be identical back in SQL92, but
SQL99 and later only require

The declared type of each referencing column shall be comparable
to the declared type of the corresponding referenced column.

And in late-model specs, that statement is followed by this gem:

There shall not be corresponding constituents of the declared type
of a referencing column and the declared type of the corresponding
referenced column such that one constituent is datetime with time
zone and the other is datetime without time zone.

That exception is pretty weird. The SQL committee have apparently
noticed that there can be semantic oddities for non-identical types,
but they haven't pursued it very far.

> For numeric perhaps we could get away with saying that the referencing
> column must have a scale that's at least as large as the referenced
> column. But I wouldn't want to get in the business of having
> type-specific rules for this, because that seems messy and
> overcomplicated for little useful gain.

It would be *really* messy. For instance, AFAICS there is no problem
with int4 vs int8 in either direction. If you store a too-large-for-
int4 value in an int8 referenced column and we try to cascade it down
to an int4 referencing column, we throw an overflow error and the
constraint is preserved. The problem with this numeric case is that
we round off the value while storing it into the referencing column
--- but, for the specific values given, that results in no change in
the referencing value so ri_KeysEqual() decides that there's no need
to re-check the constraint. That's not an optimization I care to
give up.

As you say, the problem could be eliminated by requiring the
referencing column to be able to represent all possible referenced
values. But enforcing that sort of thing in an extensible type system
seems mighty hard, and the value received for the effort would be
mighty small. Moreover, as the datetime case shows, even that would
not be quite right. A timestamptz referencing column can surely
represent all values of plain timestamp ... but that combination is
going to bite you on the rear pretty hard, because whether two values
appear equal will vary with the timezone setting.

On the whole, I'm satisfied with the "if it breaks you get to keep
both pieces" approach to this question. The only case that seems
unimpeachably OK is both-types-the-same, but the SQL standard requires
us to accept more. If a user wants to use different types, though,
it's on their head to figure out if that's semantically sane.

AFAICT, the only place where our documentation touches on this is
5.4.5. Foreign Keys, which breezily says "Of course, the number and
type of the constrained columns need to match the number and type of
the referenced columns." So maybe we need to improve that, but I'm
not sure what to say instead. In view of these considerations,
we surely shouldn't encourage using different types.

regards, tom lane