Re: [PATCH] Partial foreign key updates in referential integrity triggers

On Thu, Sep 2, 2021 at 1:55 PM Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:
>
> Hi,
> + case RI_TRIGTYPE_DELETE:
> + queryno = is_set_null
> + ? RI_PLAN_ONDELETE_SETNULL_DOUPDATE
> + : RI_PLAN_ONDELETE_SETDEFAULT_DOUPDATE;
>
> Should the new symbols be renamed ?
>
> RI_PLAN_ONDELETE_SETNULL_DOUPDATE -> RI_PLAN_ONDELETE_SETNULL_DODELETE
> RI_PLAN_ONDELETE_SETDEFAULT_DOUPDATE -> RI_PLAN_ONDELETE_SETDEFAULT_DODELETE

These constants are named correctly -- they follow the format:

RI_PLAN_<trigger>_<action>_<what_saved_plan_does>

These symbols refer to plans that are used for ON DELETE SET NULL
and ON DELETE SET DEFAULT triggers, which update rows in the referencing
table ("_DOUPDATE"). These triggers do not perform any deletions.

But these names are definitely confusing, and I did have to spend some time
confirming that the names were correct. I decided to rename these, as well as
the other plan keys, so they all use the same more explicit format:

RI_PLAN_<trigger>_<action>

RI_PLAN_CASCADE_DEL_DODELETE => RI_PLAN_ONDELETE_CASCADE
RI_PLAN_CASCADE_UPD_DOUPDATE => RI_PLAN_ONUPDATE_CASCADE

RI_PLAN_RESTRICT_CHECKREF => RI_PLAN_ONTRIGGER_RESTRICT

RI_PLAN_SETNULL_DOUPDATE => RI_PLAN_ONDELETE_SETNULL
and RI_PLAN_ONUPDATE_SETNULL

RI_PLAN_SETDEFAULT_DOUPDATE => RI_PLAN_ONDELETE_SETDEFAULT
and RI_PLAN_ONUPDATE_SETDEFAULT

The same plan can be used for both ON DELETE RESTRICT and ON UPDATE RESTRICT,
so we just use ONTRIGGER there. Previously, the same plan could also be
used for both ON DELETE SET NULL and ON UPDATE SET NULL, or both
ON DELETE SET DEFAULT and ON UPDATE SET DEFAULT. This is no longer the case,
so we need to add separate keys for each case. As an example, a constraint on
a table foo could specify:

FOREIGN KEY (a, b) REFERENCES bar (a, b)
ON UPDATE SET NULL
ON DELETE SET NULL (a)

In this case for the update trigger we want to do:

UPDATE foo SET a = NULL, B = NULL WHERE ...

but for the delete trigger we want to do:

UPDATE foo SET a = NULL WHERE ...

so the plans cannot be shared.

(Note that we still need separate plans even if we only support specifying
a column subset for the ON DELETE trigger. As in the above example, the
ON UPDATE trigger will always set all the columns, while the ON DELETE trigger
could only set a subset.)

- Paul