Re: partial "on-delete set null" constraint

On 01/03/2015 12:49 AM, Rafal Pietrak wrote:
>
> W dniu 02.01.2015 o 20:37, Adrian Klaver pisze:
>> On 01/02/2015 08:55 AM, Rafal Pietrak wrote:
> [------------------]
>>>
>>> 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
>
> I'll put a complete testcase at the end of this mail. It'll not be the
> entire schema, to focus on the case at hand and avoid obfuscation of a
> problem.
>
>> eliminate the FK mailusers <--> mailboxes and create your own UPDATE
>> and DELETE triggers to do what you want.
>
> Yes, I could. But the thing is in the future lifetime of the system.
>
> With FK, when extending the system in the future (possibly by others), a
> simple look at details of MAILBOXES table gives guidance on how to add
> something similar (like internal tweets/broadcasts/etc).
>
> With TRIGGER alone (i.e. without "documenting FK"), one will have to
> analize the body of an "ever growing" function. Which at certain point
> would become too much of an effort, and "new tools" will be created as
> needed.... leading to a spaghetti code. I'd like to provide environment
> that helps avoiding that.

That is what documentation is for:) You also can add COMMENTs to
objects(www.postgresql.org/docs/9.3/interactive/sql-comment.html).
Besides if the action is sufficiently similar I could see developing a
generic function.

>
> In other words, I hope to keep FK as "constraints of data by design",
> that not neceserly is to be maintained by the database engine (by FK
> triggers), but which will help programmers write supplementary
> functions/triggers which do, what's necessary to keep that consistency.
> (that particular functionality could be satisfied if FK actions "on
> delete set null" skipped columns with "not null" attribute, but I
> understand that this is not available/feasible).
>
> So I try to write such supplementary trigger, while keeping the FK present.
>
> This gets us back to my testcase:
>
> ---------test schema-----------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE TABLE mailusers (username text , domain text references
> maildomains(domain) on update cascade, primary key (username, domain));
> CREATE TABLE mailboxes (username text, domain text not null,
> mailmessage text not null , foreign key (username, domain) references
> mailusers (username,domain) on update cascade);
> --------------------------------
>
> ----------test data-------------
> INSERT INTO maildomains (domain, profile ) VALUES ('example.com',
> 'active');
> INSERT INTO mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT INTO mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
> ----------------------------------
>
> -------------the goal functionality ... doesnt work at the
> moment--------------------
> DELETE FROM mailusers ;
> ERROR: update or delete on table "mailusers" violates foreign key
> constraint "mailboxes_username_fkey" on table "mailboxes"
> details: Key (username, domain)=(postmaster, example.com) is still
> referenced from table "mailboxes".
> --------------------------------------------------------
>
> But an application could do
> ---------a successfull scenario with expected result-------------------
> testvm=# UPDATE mailboxes SET username = null;
> UPDATE 1
> testvm=# DELETE FROM mailusers ;
> DELETE 1
> -----------------------------------------------------------
> Which works just fine.
>
> So I add a TRIGER BEFORE, to have the above first statement get executed
> just like in the above example: before the actual DELETE:

Except it is not the same thing. In the above you execute two
statements, one UPDATE(which is actually a DELETE/INSERT) and then a
DELETE. In the below you try to do everything in one statement.

> ----------------------
> 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();
> -----------------------------
>
> Yet, it doesn't work that way:
> ------------------------------
> INSERT INTO mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT INTO mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
> DELETE FROM mailusers ;
> ERROR: update or delete on table "mailusers" violates foreign key
> constraint "mailboxes_username_fkey" on table "mailboxes"
> details: Key (username, domain)=(postmaster, example.com) is still
> referenced from table "mailboxes".
> ----------------------------

Honestly I do not know the timing of FK checks, but I for one would not
rely on a function that tries to 'game' the system. The house can change
the rules.

>
> Is there a way to write a trigger function that "prepares data" of
> relevant tables by making sure, any existing FKs are no longer violated
> (like in the above testcase) at the time the actual statement (that
> would violate them) executes?

Not that I know of. I know you do not want to hear it, but you are
trying to go against the flow of RI. If you want to do that you are
going to have to roll your own code and drop the FK. Me personally I
would move the mailboxes data into a 'history' table on deletion of a
mailusers. In said history table there would be a serial column set as
the PK so there would be no (username,domain) conflict and complete
information would be retained.

>
> -R
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com