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