W dniu 03.01.2015 o 16:07, Adrian Klaver pisze:
On 01/03/2015 12:49 AM, Rafal Pietrak wrote:
[---------------------]
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
Ouch. That one hurt ;7
[-----------------------]
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.
Frankly I wasn't going towards gaming the system, but to check if there
are "controls" that I can use.
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.
Yes. I gather, that's what's ahead of me. In fact, after that discussion
I'm more towards setting aside some dummy prefixing scheme for
usernames, which would invalidate them when discontinued, while
maintaining them as reference keys within mailuser table. Yet, its pity
my original "clever" plan didn't worked eventually.
Thenx, all the same.
-R
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
