Thread: Proposal: ON UPDATE REMOVE foreign key action

<div dir="ltr"><p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>One-lineSummary:</b> On foreign
keyupdate we unable to remove all depended records. <span style="font-size:12.7px">Currently we have "ON REMOVE CASCADE
DELETE",but no "ON UPDATE CASCADE DELETE". </span><span style="font-size:12.7px">We can only update field to NULL or
DEFAULT.</span><pstyle="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><spanstyle="font-size:12.7px"><br
/></span><pstyle="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>BusinessUse-case:</b> Cache
expirationon hash/version update. Revoke all access on account id update.<p style="margin:0.4em 0px
0.5em;line-height:1.5em"><fontcolor="#000000" face="sans-serif"><span style="font-size:12.7px">In my case i met this
situation: Iam using access links to share user account. Account owner can give private link to somebody, and its
sessionbecome mirrored. (Owner access to account granted). You cant imagine facebook desktop and mobile sessions. It's
justshortcut for entering credentials. Now i am implementing "revoke all </span></font><span
style="font-size:12.7px;color:rgb(0,0,0);font-family:sans-serif">butme". Its done simple, since each user is uuid
indexed,i am just generate new uuid for current account. Old uuid become invalid to other sessions - since no record is
foundin base.<br />I want to remove any pending access links, prevent bad guy restore access.<br />I can possibly set
linkedaccount to NULL, and then clear record on expiration, but i feel that automatically removing on update event is
morerational.</span><p style="margin:0.4em 0px 0.5em;line-height:1.5em"><span
style="font-size:12.7px;color:rgb(0,0,0);font-family:sans-serif"><br/></span><p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>Userimpact with the
change:</b> Insteadof writing "on update" triggers for each depended table, wished action is done by single line.<p
style="margin:0.4em0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>Implementationdetails:</b> On
cascadeswitch "update" action to "delete".<p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>EstimatedDevelopment Time:</b> Few
hoursor less.<p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>OpportunityWindow Period:</b> Non
applicable,minor feature<p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>BudgetMoney:</b> I am ready to
implementmyself, if approved.<p style="margin:0.4em 0px
0.5em;line-height:1.5em;color:rgb(0,0,0);font-family:sans-serif;font-size:12.7px"><b>ContactInformation:</b> <a
href="mailto:enelar@exsul.net">enelar@exsul.net</a></div>

On 10/3/16, Kirill Berezin <enelar@exsul.net> wrote:
> *One-line Summary:* On foreign key update we unable to remove all depended
> records. Currently we have "ON REMOVE CASCADE DELETE", but no "ON UPDATE
> CASCADE DELETE". We can only update field to NULL or DEFAULT.

I think there are three causes why we don't have it implemented.
The first one is that there is no such grammar in the SQL spec (your
version is also wrong: SQL spec has "ON DELETE CASCADE" as well as "ON
DELETE CASCADE" [or any other action instead of "CASCADE"]).

The second one is in almost all cases there is no reason to delete
rows because of updating referenced row. If these rows are still
connected, they should be updated, if not --- left as is ("NO ACTION")
or with reference link deleted ("SET NULL" or "DEFAULT").
These rows has data, that's why they are still in tables. They can be
deleted (by reference) if and only if "parent" or "linked" data (all
data, not just referenced key) is deleted.

> *Business Use-case:* Cache expiration on hash/version update. Revoke all
> access on account id update.

> In my case i met this situation: I am using access links to share user
> account. Account owner can give private link to somebody, and its session
> become mirrored. (Owner access to account granted).

And the third cause is avoiding of bad design. If you has to give
access to anyone and you know access will be revoked soon (or late),
it is wise to give private link with different identificator which can
be easily found and removed by a grantor id (your id).

> You cant imagine facebook desktop and mobile sessions.

Which, of course, have different session ids. You can revoke session
without renaming your own.

> It's just shortcut for
> entering credentials. Now i am implementing "revoke all but me". Its done
> simple, since each user is uuid indexed, i am just generate new uuid for
> current account. Old uuid become invalid to other sessions - since no
> record is found in base.
> I want to remove any pending access links, prevent bad guy restore access.
> I can possibly set linked account to NULL,

Why just don't delete them when grantor revokes access?

> and then clear record on
> expiration, but i feel that automatically removing on update event is more
> rational.

I personally don't see necessity to introduce new non-spec grammar.
If you think I has not understood you, send an example with schema ---
what you have now and how you expect it should be.

-- 
Best regards,
Vitaly Burovoy

<div dir="ltr"><div class="gmail_extra">Disclaimer: sorry, i dont understand, should i reply to each of you personally,
orjust answer to channel. Some feedbacks were sended in personal, and some include channel copy. <br /><br />Thanks for
responses,you understand it correctly.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">When i said
"anybody",i mean inclusive owner himself. For example cookie poisoning.</div><div class="gmail_extra">There is no
"another"session, technically. They similar to the server, they even can have same IP.</div><div
class="gmail_extra">Yes,we can't prevent it with CSRF cookies, but it is not the point of current
conversation.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">I can make business logic outside
table:make extra query. Im just dont like how it looks from perspective of encapsulation.</div><div
class="gmail_extra">Eachtable should describe itself, like object in OOP language. With SQL constructions or
triggers/constraits.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Second part of my use case is
datacache. When user update version(generation), cache should be flushed. As easy example: imagine i am fetching
currencyvalue. And till end of the day, i am honor current course. (Or any other information, that has certain origin
checkpoints).When i am updating origin state (current day, server version, ip address, neural network generation), i am
haveto invalidate all previous data.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">Like i am
calculatingdigits of the square root, of some number. The more i spend time, the closer my approx value to irrational
result.But when original value has changed - all previous data does not make sense. I am flushing it and starting from
digit1.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">This is allegorical examples to my
real-worldcases. I may try imagine some hypothetical situations, when this functionality more welcomed. But, i am
respectreasons why do not apply this proposal. If my update didn't shift the balance, its ok. on update trigger is not
suchpainful.</div></div> 

On 10/4/16, Kirill Berezin <enelar@exsul.net> wrote:
> Disclaimer: sorry, i dont understand, should i reply to each of you
> personally, or just answer to channel. Some feedbacks were sended in
> personal, and some include channel copy.

Usually discussions are in the list, therefore you should use "reply
to all" (see [1]).
Exception is when a sender notes "Off the list".

> Thanks for responses, you understand it correctly.
>
> When i said "anybody", i mean inclusive owner himself. For example cookie
> poisoning.
> There is no "another" session, technically. They similar to the server,
> they even can have same IP.
> Yes, we can't prevent it with CSRF cookies, but it is not the point of
> current conversation.
>
> I can make business logic outside table: make extra query.

Good decision. Your case needs exactly what you've just written.

> Im just dont like how it looks from perspective of encapsulation.
> Each table should describe itself, like object in OOP language.
> With SQL constructions or triggers/constraits.

SQL is not OOP. There is no "encapsulation".

> Second part of my use case is data cache.

Hmm. Usage of RDBMS as a cache with an overhead for Isolation and
Durability (from ACID)? Really?
As for me it is a bad idea for most cases.

> When user update
> version(generation), cache should be flushed. As easy example: imagine i am
> fetching currency value. And till end of the day, i am honor current
> course. (Or any other information, that has certain origin checkpoints).
> When i am updating origin state (current day, server version, ip address,
> neural network generation), i am have to invalidate all previous data.

It is a bad example. Companies working with currency exchange rates
always keep their values as historical data.

> Like i am calculating digits of the square root, of some number. The more i
> spend time, the closer my approx value to irrational result. But when
> original value has changed - all previous data does not make sense. I am
> flushing it and starting from digit 1.

Why do you "update" original value instead of deleting old one and
inserting new value?

> This is allegorical examples to my real-world cases. I may try imagine some
> hypothetical situations, when this functionality more welcomed. But, i am
> respect reasons why do not apply this proposal. If my update didn't shift
> the balance, its ok. on update trigger is not such painful.

All your cases (except the exchange rate one) can be done using two
queries: delete original row (which deletes other linked data "ON
DELETE CASCADE") and insert a new one. You don't even have to use
transactions!
If your business logic is so "OOP", you can use stored procedures, but
introducing new grammar specially for concrete task is a bad idea.


Of course at first sight there is a meaningless sequence "ON UPDATE
SET (NULL|DEFAULT)", but the meaning of SET NULL and SET DEFAULT for
both ON UPDATE and ON DELETE is using them for "unlinking" data from
the referenced one. It is similar to "NO ACTION" but explicitly change
them as they are no longer connected to the referenced row (by
referencing column list).

Also your proposal is not consistent: ON UPDATE REMOVE (DELETE?), but
ON DELETE - what? again remove/delete?


[1] https://wiki.postgresql.org/wiki/Mailing_Lists#Using_the_discussion_lists
-- 
Best regards,
Vitaly Burovoy