Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types
От | Tom Lane |
---|---|
Тема | Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types |
Дата | |
Msg-id | 1326622.1635813277@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
Ответы |
Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-bugs |
Alvaro Herrera <alvherre@alvh.no-ip.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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Peter GeogheganДата:
Сообщение: Re: BUG #17245: Index corruption involving deduplicated entries
Следующее
От: Sandeep ThakkarДата:
Сообщение: Re: CREATE INDEX CONCURRENTLY does not index prepared xact's data