Re: DELETE CASCADE - Mailing list pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

> [ a couple of random thoughts after quickly scanning this thread ... ]
>
> David Christensen <david.christensen@crunchydata.com> writes:
>> I assume this would look something like:
>> ALTER TABLE foo ALTER CONSTRAINT my_fkey ON UPDATE CASCADE ON DELETE RESTRICT
>> with omitted referential_action implying preserving the existing one.
>
> I seem to remember somebody working on exactly that previously, though
> it's evidently not gotten committed.  In any case, we already have
>
>     ALTER TABLE ... ALTER CONSTRAINT constraint_name
>     [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
>
> which has to modify pg_trigger rows, so it's hard to see why it'd
> be at all difficult to implement this using code similar to that
> (maybe even sharing much of the code).

Sure; this was my assumption as well.

> Returning to the original thought of a DML statement option to temporarily
> override the referential_action, I wonder why only temporarily-set-CASCADE
> was considered.  It seems to me like there might also be use-cases for
> temporarily selecting the SET NULL or SET DEFAULT actions.

Agreed; DELETE CASCADE had been the originating use case, but no reason to limit it to just that
action. (I'd later expanded it to add RESTRICT as well, but barring implementation issues, probably
no reason to limit any of referential action.)

> Another angle is that if we consider the deferrability properties as
> precedent, there already is a way to override an FK constraint's
> deferrability for the duration of a transaction: see SET CONSTRAINTS.
> So I wonder if maybe the way to treat this is to invent something like
>
>     SET CONSTRAINTS my_fk_constraint [,...] ON DELETE referential_action
>
> which would override the constraints' action for the remainder of the
> transaction.  (Permission needs TBD, but probably the same as you
> would need to create a new FK constraint on the relevant table.)
>
> In comparison to the original proposal, this'd force you to be explicit
> about which constraint(s) you intend to override, but TBH I think that's
> a good thing.

I can see the argument for this in terms of being cautious/explicit about what gets removed, however
the utility in this particular form was related to being able to *avoid* having to manually figure out
the relationship chains and the specific constraints involved.  Might there be some sort of middle
ground here?

> One big practical problem, which we've never addressed in the context of
> SET CONSTRAINTS but maybe it's time to tackle, is that the SQL spec
> defines the syntax like that because it thinks constraint names are
> unique per-schema; thus a possibly-schema-qualified name is sufficient
> ID.  Of course we say that constraint names are only unique per-table,
> so SET CONSTRAINTS has always had this issue of not being very carefully
> targeted.  I think we could do something like extending the syntax
> to be
>
>     SET CONSTRAINTS conname [ON tablename] [,...] new_properties

This part seems reasonable.  I need to look at how the existing SET CONSTRAINTS is implemented;
would be interesting to see how the settings per-table/session are managed, as that would be
illustrative to additional transient state like this.

> Anyway, just food for thought --- I'm not necessarily set on any
> of this.

Sure thing; I appreciate even a little bit of your attention here.

Best,

David

--