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

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.

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

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?

-R