Re: [HACKERS] Proposal: ON UPDATE REMOVE foreign key action

2016-10-04 Thread Kirill Berezin
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.

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. 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.

Second part of my use case is data cache. 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.

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.

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.


[HACKERS] Proposal: ON UPDATE REMOVE foreign key action

2016-10-03 Thread Kirill Berezin
*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.


*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). You cant imagine
facebook desktop and mobile sessions. 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, and then clear record on
expiration, but i feel that automatically removing on update event is more
rational.


*User impact with the change:* Instead of writing "on update" triggers for
each depended table, wished action is done by single line.

*Implementation details:* On cascade switch "update" action to "delete".

*Estimated Development Time:* Few hours or less.

*Opportunity Window Period:* Non applicable, minor feature

*Budget Money:* I am ready to implement myself, if approved.

*Contact Information:* ene...@exsul.net