public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: David Christensen <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: DELETE CASCADE
Date: Fri, 24 Sep 2021 13:00:08 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAOxo6X+bsf85kycpoE5m2G57EFEGkCC72reNgg4Lna9Ud53-9Q@mail.gmail.com>
<CAKFQuwZe6hr=rXMCPGVQ55gnSM_zAf7h6Dx_0n-LGJMB6izhoQ@mail.gmail.com>
<[email protected]>
[ a couple of random thoughts after quickly scanning this thread ... ]
David Christensen <[email protected]> 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).
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.
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.
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
Anyway, just food for thought --- I'm not necessarily set on any
of this.
regards, tom lane
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: DELETE CASCADE
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox