On 01/03/2015 09:05 AM, Rafal Pietrak wrote:
W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:
On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@xxxxxxxxx> wrote:
[-------------------------]
Yes. This is precisely the "semantics" I'm trying to put into the
schema: after a username is "released" from service, all it's
messages become "from unknown user".... unless thoroughly
investigated :)
It also makes a foreign key reference unusable: There is no unique
parent record to match it to, so what exactly are you referencing?
Nothing.
Which is doable, but:
1) Your FK on mailusers is foreign key (username, domain)
2) And domain text not null
3) And you want a 'smart' SET NULL action that only SETs NULL for
referencing fields in a FK that are NULL but not for those that are NOT
NULL.
Since 3) is not possible AFAIK you have the following options:
A) Set domain NULL, which defeats your purpose if I follow correctly.
B) Create a different FK, hence my suggestion about a surrogate key.
C) Forget about a FK and write your own trigger.
D) What you propose below. Though my experiences with RULEs have not
been happy. In Postgres 9.1+ you have INSTEAD OF triggers on VIEWs which
might be easier to work with.
That's precisely my point here. I'd like to have "objects" in mailboxes
table left "hanging around" after it's "disconnected" from service. FK
acting like a power cord of a vacuum cleaner: when in service: hooked
into the wall; after that vacuum cleaner stays there, only disconnected
(and the socket can be used by others).
But pondering the idea as the discussion goes, I think I'll try to use
VIEW query rewriting capabilities, to get the "SET username=NULL; then
DELETE" sequence encoded as an on delete rule of a view created on top
of mailusers table.
-R
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
