Re: partial "on-delete set null" constraint - Mailing list pgsql-general

W dniu 02.01.2015 o 17:05, Adrian Klaver pisze:
> On 01/02/2015 07:45 AM, Rafal Pietrak wrote:
>>
>> W dniu 02.01.2015 o 16:03, Adrian Klaver pisze:
>>> On 01/02/2015 04:31 AM, Rafal Pietrak wrote:
>>>>
>> [--------------------]
>
> CCing the list.

Ups, sorry - not that button clicked.

[----------------------------]
>> But in any case, the question remains interesting for me in general:
>>
>> You say you thing "it'll foul thing up in general" - I'm qurious about
>> that.
>
> From what I see you want a semi-unique key(user, domain). Semi-unique
> in that at a point in time it is unique for a user, but over time it
> could represent various users. This is tied together by 'sort of a
> audit trail'. With out further information, I would say that is a
> frail system.

sssory. I know. Pls let it be. really.

>
>>
>> As you can see, I was able to "UPDATE maiboxes SET username = null" and
>> then "DELETE FROM mailusers" as a sequence of commands. There is nothing
>> wrong with that sequence. Naturally, in final implementation I'd have
>> additional FK from mailboxes(domain) to maildomains(domain), so that my
>> mailboxes table wan't "wonderaway" during the lifetime of the service
>> ... but that's programmers' responsibility - if I forget, my fault. At
>> the time of "delete from mailusers", all that is needed (required) from
>> the database, is not to set NULL colums that "although are asked to be
>> set NULL by action, they are also required to stay not null by
>> constraint".
>>
>> I'd say that:
>> 1. I don't know how to implement the sort of "relaxed on delate set
>> null" functionality programatically (btw: help apreciated)
>> 2. I tend to ask myself if it's possible to specify the database itself
>> to provide such functionality: either "automagically" - the "on delete
>> set null" action always skips columns declared as not null; or with a
>> little help from additional keword like "on delete set null nullable"
>> (or something)?
>
> Do not use a FK, just build your own trigger function that does what
> you want when you UPDATE/DELETE mailusers.

FK are ways better self-documenting then trigger functions, but when
everything else fails ... :(

... I tried to suplement FK (by remowing actions on delete) with a
trigger, but it didn't work:
-------------------------------------
CREATE or replace FUNCTION prepare_null () returns trigger language
plpgsql as $$ begin old.username=null; return old; end; $$;
CREATE TRIGGER prepare_null BEFORE DELETE On mailusers for each row
execute procedure prepare_null();
-------------------------------------

Is there a way to forcebly push the "old.username=null, throughout the
"on-update" FK chains into the all dependent tables, before constraints
are checked for DELETE operation? I would imagine, that trigger BEFORE
is "completed before"... the indicated action begins; but it isn't - the
above does not work.

Apparently I don't know how to do that. I'd appreciate any help.


-R