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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
