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

On 01/02/2015 08:55 AM, Rafal Pietrak wrote:
>
> 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:

We will need to see more information. Basically the complete schema
definitions for the changed layout. All of this is interconnected,
seeing just parts of it at a time makes it difficult/impossible to
figure out.

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

Hard to say without more information. My guess though is you are going
to have to just eliminate the FK mailusers <--> mailboxes and create
your own UPDATE and DELETE triggers to do what you want.

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


--
Adrian Klaver
adrian.klaver@aklaver.com