Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?

I wrote:
> Anybody have DB2, or something else that might be thought to be pretty
> close to spec-compliant?

Remarkably enough, the DB2 10.1 manuals at www.ibm.com say that it
doesn't support ON UPDATE SET NULL or ON UPDATE SET DEFAULT. I'm
disappointed in them :-(. But anyway it seems that we'll not get
that much guidance from looking at other SQL implementations, and
what precedents there are suggest that people are using the set-all-
the-columns interpretation.

After reflection it seems clear to me that set-all-the-columns is
in fact an improvement for the SET DEFAULT case, regardless of match
style. If we set only some of them, you get a mishmash of old and
new column values which is rather unlikely to match any row of the
referenced table. If we always set all of them, then (at least for
constant default values) only one "fallback" entry is required in
the referenced table. This can be seen in my example script upthread,
where I had to make a bogus referenceable entry "11, 0" to prevent
an RI failure on the MATCH SIMPLE update. Having just the one fallback
entry "0, 0" definitely seems saner from an application standpoint.

I'm less sold on set-all-the-columns for the MATCH SIMPLE SET NULL
case. In this match style, setting any referencing column to null
is sufficient to prevent an RI failure, and it could be argued that
zapping all of them discards data that might be useful. But it does
have the advantage of predictability.

From an implementation standpoint, set-all-the-columns is definitely
easier to deal with: we won't need ri_OneKeyEqual at all any more,
and RI_FKey_setnull_upd no longer has the problem of having to deal
with variant plans depending on which columns it needs to zap.
So I'm attracted to it on that basis, but I don't want to let
implementation concerns drive the decision.

On balance I think we ought to switch to set-all-the-columns, though
only in 9.3+ --- a back-patched behavioral change doesn't seem like a
good idea.

Any objections, or anyone want to do more research before we decide?

regards, tom lane