Re: [GENERAL] partial on-delete set null constraint
W dniu 03.02.2015 o 04:44, Jim Nasby pisze: On 1/3/15 2:49 AM, Rafal Pietrak wrote: [---] 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(); - That trigger function is NOT doing the same thing as above. What you want is something that does UPDATE mailboxes SET username = null WHERE username = OLD.username. You'd need to make sure that trigger ran before the RI trigger did. Oh. yes. It actually does work. Silly me making such obvious mistakes. Nonetheless I stand my grounds on the FK internal functionality opinion, in that if FK constraint internal triggers would SET NULL (when cascaded set null is declared) only those columns of the target, that are actually NULL-able, then the coding (SQL application programmer's life like myown) would get easier in such usage scenarios. thenx anyway! -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 1/3/15 2:49 AM, Rafal Pietrak wrote:
-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.
Well, you didn't specify ON DELETE SET NULL, but that wouldn't work
anyway because it'd attempt to set both username *and* domain to NULL.
Note also that for this to work you'd probably need to specify MATCH
SIMPLE.
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();
-
That trigger function is NOT doing the same thing as above. What you
want is something that does UPDATE mailboxes SET username = null WHERE
username = OLD.username. You'd need to make sure that trigger ran before
the RI trigger did.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] partial on-delete set null constraint
Alban Hertroys haram...@gmail.com wrote: Unfortunately, I do not own a copy so I can't verify. If anyone who does own a copy could confirm or even quote the relevant section, that would be great. I have a copy of The RELATIONAL MODEL for DATABASE MANAGEMENT, VERSION 2 (RM/V2), by E. F. Codd, Copyright 1990, on my desk, but I wasn't entirely clear on what you were looking for a quote about. In the preface he says that from 1968 to 1988 he published more than 30 technical papers on the relational model, which he collectively refers to in this book as RM/V1. If you were looking for his views on NULL, I can tell you that in 1990 he preferred to refer to marks to indicate missing information, and just the index entries on the topic would be too big to quote here (taking nearly an entire page). The Missing Information chapter is 27 pages long. The Response to Technical Criticisms Regarding Missing Information is another 10 pages. Also, these chapters refer to separate discussions of particular issues related to missing values in other chapters. He mentions that RM/V1 only had one type of mark for missing data which was referred to in the earlier work as a *null* or *null value*, so the term may have originated with him (I don't have copies of all the relevant papers); but in RM/V2 he argues that the difference between a value which is missing-but-applicable (just currently unknown) is different enough from the case where a value would be inapplicable (i.e., the value is unknowable) that there should be separate marks for them, which he dubbed the A-mark and I-mark, respectively. I'm not aware of any product which has implementing the separate types of marks for missing data, but I agree with his arguments that while NULL is far superior to magic values, the NULL concept lacks enough semantic depth to avoid confusion. If I were developing a database from scratch today I would try very hard to implement his ideas regarding data marked as missing, but it's hard to see how to retro-fit it into a stable product. :-( If you have a question about a specific area of how missing values should be handled according to RM/V2, please respond with a question about it that is narrow enough to deal with in an email. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On Sun, 2015-01-25 at 14:09 +0100, Alban Hertroys wrote: The following link http://www.databaseanswers.org/codds_rules.htm sets out Ted Codd's rules according to C.J. Date. As you might have noticed, those were referred to already upthread, but that is a while ago now. Although those rules are certainly Codd's, that's an entirely different bit of database theory. It's what I did manage to find while searching the Internet for Codd relationality. What I understand of them, these 12 rules determine whether a DBMS can be called relational, whereas Codd-relationality is only applicable in DBMS's that already qualify as relational and restricts primary keys to non-nullable fields. I know it's hard to believe that the Internet does not contain some information, certainly when the information is a rule that is applied and documented in pretty much every relational database in existence (but without mentioning it by name or why it exists). I'm afraid that might be the case here though. Possibly this could happen because the theory in question predates the internet. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] partial on-delete set null constraint
The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says: rule 3: systematic treatment of null values; hmmm this is a little broader then support for null. I would think, that: 1. if a sequence of update XX set fk_field=null; then delete YY depending on that FK, for a particular schema definition works ... 2. so the implementation of FK should support that too ... to be called systematic, right? 3. and the simplest way to do that for the case at hand, within an on delete action, is to skip those parts of FK, that are marked as not null within the referring table. That would be a requirement for rdbms implementation that claims compliance with Codd rule nr.3 :) I translated Codd-relationality to English, possibly it’s named differently. Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did find is that the theory I referred to stems from 1970(!), but that’s the closest I got. My teachers got back to me; a while ago already, to be fair. They were as surprised that this can't be found on the internet as I was, but it should be possible to find it in printed form. I'd suggest a university library or a good technical book store. Apparently, this particular theory is explained in E.F. Codd: The relational model for database management. There are probably other books that do too. Unfortunately, I do not own a copy so I can't verify. If anyone who does own a copy could confirm or even quote the relevant section, that would be great. Better yet, perhaps this should find it's way (back) to the internet? I'm still much surprised that Wikipedia didn't have this. Oh, and perhaps we could get a reference in the PG docs on primary keys and NULLs to the theory? Do we have such things in the docs? It sounds like a good idea to me, I always find it helpful to know why things are how they are. But I don't write the docs so this is just one for the ideas-box. Cheers, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] partial on-delete set null constraint
On Sun, 2015-01-25 at 14:09 +0100, Alban Hertroys wrote: The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says: rule 3: systematic treatment of null values; hmmm this is a little broader then support for null. I would think, that: 1. if a sequence of update XX set fk_field=null; then delete YY depending on that FK, for a particular schema definition works ... 2. so the implementation of FK should support that too ... to be called systematic, right? 3. and the simplest way to do that for the case at hand, within an on delete action, is to skip those parts of FK, that are marked as not null within the referring table. That would be a requirement for rdbms implementation that claims compliance with Codd rule nr.3 :) I translated Codd-relationality to English, possibly it’s named differently. Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did find is that the theory I referred to stems from 1970(!), but that’s the closest I got. My teachers got back to me; a while ago already, to be fair. They were as surprised that this can't be found on the internet as I was, but it should be possible to find it in printed form. I'd suggest a university library or a good technical book store. Apparently, this particular theory is explained in E.F. Codd: The relational model for database management. There are probably other books that do too. Unfortunately, I do not own a copy so I can't verify. If anyone who does own a copy could confirm or even quote the relevant section, that would be great. Better yet, perhaps this should find it's way (back) to the internet? I'm still much surprised that Wikipedia didn't have this. Oh, and perhaps we could get a reference in the PG docs on primary keys and NULLs to the theory? Do we have such things in the docs? It sounds like a good idea to me, I always find it helpful to know why things are how they are. But I don't write the docs so this is just one for the ideas-box. Cheers, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. The following link http://www.databaseanswers.org/codds_rules.htm sets out Ted Codd's rules according to C.J. Date. I don't have a copy of the book so I can't verify the accuracy of what is published at this link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
1. I have a table with mailmessages, which has an FK to a table of hub users. 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular username from service to release that username to others. 3. I try to do that with FK on-update/on-delete actions, but to no avail: Interesting discussion on database theory! If you switched to surrogate keys for all your tables, you could soft-delete accounts with a deleted_at column, and then have a unique index on username+domain (or username+domain_id?) that is WHERE deleted_at IS NULL. Does that work? Probably you want the same approach for the maildomains table to enforce unique non-deleted domains. It seems like if you want to retain data for auditing, you don't really want to delete *anything*, including the username. Surrogate keys and a partial unique index would let you do that I believe. Paul -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
W dniu 04.01.2015 o 02:02, Alban Hertroys pisze: [--] A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination of columns which uniquely specify a row; it is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. They blatantly leave out why primary keys have that implicit NOT NULL! Is this some forgotten about piece of database theory? I’ll have to ask my teachers where they got their definition from! I'd say, that this is because it's a definition. primary key is defined that way: they,ve chosen a name (primary) and features (a unique key over not null attributes, and no other key using a name of primary), and that's it. And the goal for such definition was possibly also quite trivial: it's for the programmers like ourselves to communicate efficiently what we mean. I'm still not convinced of how the norm/specs/theory expect engine to treat inconsistent constraint setup. In this case, the setup is inconsistent: on delete set null contradicts not null attribute. So: should the engine rise an error (like it does), or should it just humbly comply with the setup, and only do what's allowed by the setup, i.e.: skip the not null columns when executing on delete action. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 02 Jan 2015, at 13:31, Rafal Pietrak ra...@ztk-rp.eu wrote: Hello, Rewriting my mail-hub I fell into the following problem: 1. I have a table with mailmessages, which has an FK to a table of hub users. 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular username from service to release that username to others. 3. I try to do that with FK on-update/on-delete actions, but to no avail: testcase---(against postgresql v9.1 hosted by debian)--- CREATE TABLE maildomains (domain text primary key, profile text not null); CREATE TABLE mailusers (username text , domain text references maidomains(domain), 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 on delete set null); You assumed a functional dependency between username and domain, while those fields actually describe independent entities that don’t necessarily go together as you found out. Hence you need to normalise further. For example: CREATE TABLE maildomains (domain text primary key, profile text not null); CREATE TABLE mailusers (username text primary key); CREATE TABLE maildomainusers (username text references mailusers(username), domain text references maildomains(domain), primary key (username, domain)); CREATE TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain text not null references maildomains(domain) on update cascade, mailmessage text not null); Is there a way to implement that sort of referrential constraints (i.e.: just partially set null on delete”)? Not as a foreign key reference delete action. Would it violate SQL standard (signifficantly), if an on delete set null action just ignored all the FK columns that have a NOT NULL constraint set? Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference _any_ mailuser in a domain: NULL means ‘unknown’, any username might match that. As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure he’s right about that. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
W dniu 03.01.2015 o 14:11, Alban Hertroys pisze: [--] You assumed a functional dependency between username and domain, while those fields actually describe independent entities that don’t necessarily go together as you found out. Hence you need to normalise further. For example: CREATE TABLE maildomains (domain text primary key, profile text not null); CREATE TABLE mailusers (username text primary key); CREATE TABLE maildomainusers (username text references mailusers(username), domain text references maildomains(domain), primary key (username, domain)); CREATE TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain text not null references maildomains(domain) on update cascade, mailmessage text not null); I don't think that this tableset actually describe an ordinary mailhub, which I'm coding. the on delete set null within mailboxes(username) act only on delete executed at mailusers; while the delete in question will be executed on maildomainusers. In particular postmaster, as a single entity in mailusers table, will have as many entries in maildomainusers as there are domains in maildomains. But some domains may live without a postmaster user ... or a postmaster user may be replaced by an alias (another table, not presented for clearity). in such case, postmaster user will be dropped from maildomainusers, but will remain in mailusers table for other domains to reference. And delete of that postmaster user from maildomainuser will not fireback into the mailboxes to set null postmaster username from mails within that domain. This additional level of normalization solves me anything, I think. Is there a way to implement that sort of referrential constraints (i.e.: just partially set null on delete”)? Not as a foreign key reference delete action. Pity. So I must look for some sort of trigger functions as I've already started, but nothing came up functioning as I'd need it to. Would it violate SQL standard (signifficantly), if an on delete set null action just ignored all the FK columns that have a NOT NULL constraint set? Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference _any_ mailuser in a domain: NULL means ‘unknown’, any username might match that. Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from unknown user unless thoroughly investigated :) As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure he’s right about that. Having only slight theoretical background, I'd say: it could be partially the reason. I think, that primary key is just a syntactic shortcut for unique AND not null - so often used, that the shortcut is so appreciated. But just unique, meaning unique just for values that happen to be known is also usefull, and thus it is allowed on equal bases only for other usage scenarios. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 01/03/2015 09:05 AM, Rafal Pietrak wrote: W dniu 03.01.2015 o 16:48, Alban Hertroys pisze: On 03 Jan 2015, at 15:20, Rafal Pietrak ra...@ztk-rp.eu wrote: [-] Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from unknown user unless thoroughly investigated :) It also makes a foreign key reference unusable: There is no unique parent record to match it to, so what exactly are you referencing? Nothing. Which is doable, but: 1) Your FK on mailusers is foreign key (username, domain) 2) And domain text not null 3) And you want a 'smart' SET NULL action that only SETs NULL for referencing fields in a FK that are NULL but not for those that are NOT NULL. Since 3) is not possible AFAIK you have the following options: A) Set domain NULL, which defeats your purpose if I follow correctly. B) Create a different FK, hence my suggestion about a surrogate key. C) Forget about a FK and write your own trigger. D) What you propose below. Though my experiences with RULEs have not been happy. In Postgres 9.1+ you have INSTEAD OF triggers on VIEWs which might be easier to work with. That's precisely my point here. I'd like to have objects in mailboxes table left hanging around after it's disconnected from service. FK acting like a power cord of a vacuum cleaner: when in service: hooked into the wall; after that vacuum cleaner stays there, only disconnected (and the socket can be used by others). But pondering the idea as the discussion goes, I think I'll try to use VIEW query rewriting capabilities, to get the SET username=NULL; then DELETE sequence encoded as an on delete rule of a view created on top of mailusers table. -R -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
A supporting view works OK. - CREATE VIEW api2users AS SELECT * from mailusers; CREATE or replace RULE remove_user AS ON DELETE TO api2users do instead (update mailboxes set username=null where username=old.username and domain=old.domain; delete from mailusers where username=old.username and domain=old.domain); DELETE FROM api2users where username='postmaster' and domain='example.com'; DELETE 1 - So it probably stays ... although I'm not particularly happy with that; Still, the problem at hand is solved. W dniu 03.01.2015 o 19:04, Alban Hertroys pisze: [---] Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the wrong database deity! That’s Codd-relationality, of course. Not Boyce. The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says: rule 3: systematic treatment of null values; hmmm this is a little broader then support for null. I would think, that: 1. if a sequence of update XX set fk_field=null; then delete YY depending on that FK, for a particular schema definition works ... 2. so the implementation of FK should support that too ... to be called systematic, right? 3. and the simplest way to do that for the case at hand, within an on delete action, is to skip those parts of FK, that are marked as not null within the referring table. That would be a requirement for rdbms implementation that claims compliance with Codd rule nr.3 :) I think :) -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 03 Jan 2015, at 18:05, Rafal Pietrak ra...@ztk-rp.eu wrote: W dniu 03.01.2015 o 16:48, Alban Hertroys pisze: On 03 Jan 2015, at 15:20, Rafal Pietrak ra...@ztk-rp.eu wrote: [-] Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from unknown user unless thoroughly investigated :) It also makes a foreign key reference unusable: There is no unique parent record to match it to, so what exactly are you referencing? Nothing. That's precisely my point here. I'd like to have objects in mailboxes table left hanging around after it's disconnected from service. FK acting like a power cord of a vacuum cleaner: when in service: hooked into the wall; after that vacuum cleaner stays there, only disconnected (and the socket can be used by others). Then you should make that reference to maildomains(domain) also NULLable. Otherwise you aren’t referencing ‘nothing’, but possibly every user in the domain (we won’t know, since NULL is ‘unknown’ after all and that could match anything). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
W dniu 03.01.2015 o 16:48, Alban Hertroys pisze: On 03 Jan 2015, at 15:20, Rafal Pietrak ra...@ztk-rp.eu wrote: [-] Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from unknown user unless thoroughly investigated :) It also makes a foreign key reference unusable: There is no unique parent record to match it to, so what exactly are you referencing? Nothing. That's precisely my point here. I'd like to have objects in mailboxes table left hanging around after it's disconnected from service. FK acting like a power cord of a vacuum cleaner: when in service: hooked into the wall; after that vacuum cleaner stays there, only disconnected (and the socket can be used by others). But pondering the idea as the discussion goes, I think I'll try to use VIEW query rewriting capabilities, to get the SET username=NULL; then DELETE sequence encoded as an on delete rule of a view created on top of mailusers table. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 03 Jan 2015, at 14:11, Alban Hertroys haram...@gmail.com wrote: On 02 Jan 2015, at 13:31, Rafal Pietrak ra...@ztk-rp.eu wrote: Would it violate SQL standard (signifficantly), if an on delete set null action just ignored all the FK columns that have a NOT NULL constraint set? As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure he’s right about that. Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the wrong database deity! That’s Codd-relationality, of course. Not Boyce. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
Re: [GENERAL] partial on-delete set null constraint
On 03 Jan 2015, at 15:20, Rafal Pietrak ra...@ztk-rp.eu wrote: W dniu 03.01.2015 o 14:11, Alban Hertroys pisze: [--] You assumed a functional dependency between username and domain, while those fields actually describe independent entities that don’t necessarily go together as you found out. Hence you need to normalise further. For example: CREATE TABLE maildomains (domain text primary key, profile text not null); CREATE TABLE mailusers (username text primary key); CREATE TABLE maildomainusers (username text references mailusers(username), domain text references maildomains(domain), primary key (username, domain)); CREATE TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain text not null references maildomains(domain) on update cascade, mailmessage text not null); I don't think that this tableset actually describe an ordinary mailhub, which I'm coding. An “ordinary mail hub” is rather subject to interpretation, so that depends on your definition of it. As I understand it, your “mail hub” collects mails from several domains for various users? I’m not really sure about the benefits of such an application, unless internet connections to the domains you’re playing hub for are really flaky - but that’s just a guess. the on delete set null within mailboxes(username) act only on delete executed at mailusers; while the delete in question will be executed on maildomainusers. It was but an example I cooked up quickly from the info you provided. Yeah, you would have to set the username reference to NULL by hand if you’d delete maildomainusers. That could easily be done using a trigger on maildomainusers, though. In particular postmaster, as a single entity in mailusers table, will have as many entries in maildomainusers as there are domains in maildomains. But some domains may live without a postmaster user ... or a postmaster user may be replaced by an alias (another table, not presented for clearity). in such case, postmaster user will be dropped from maildomainusers, but will remain in mailusers table for other domains to reference. And delete of that postmaster user from maildomainuser will not fireback into the mailboxes to set null postmaster username from mails within that domain. That description makes your problem a lot easier to envision. Pity. So I must look for some sort of trigger functions as I've already started, but nothing came up functioning as I'd need it to. Would it violate SQL standard (signifficantly), if an on delete set null action just ignored all the FK columns that have a NOT NULL constraint set? Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference _any_ mailuser in a domain: NULL means ‘unknown’, any username might match that. Yes. This is precisely the semantics I'm trying to put into the schema: after a username is released from service, all it's messages become from unknown user unless thoroughly investigated :) It also makes a foreign key reference unusable: There is no unique parent record to match it to, so what exactly are you referencing? Besides, with the schema you gave, “unless thoroughly investigated” is not going to help much to find the user; that information is no longer present unless you also store it elsewhere (for example inside your mailbox message data). As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure he’s right about that. Having only slight theoretical background, I'd say: it could be partially the reason. I think, that primary key is just a syntactic shortcut for unique AND not null - so often used, that the shortcut is so appreciated. But just unique, meaning unique just for values that happen to be known is also usefull, and thus it is allowed on equal bases only for other usage scenarios. I’m in the middle of (finally) receiving that theoretical background, so I know where you come from. I’m also in the fortunate position to have all that theoretical jargon at the ready ;) Until recently I used to think the same way about NULLs in PK's, and it holds true when you only look at the PK. However, once you add foreign key references to a table with such a PK, things change. FK’s are supposed to reference a single unique entity in a parent table, but when there are NULLs in the mix, that becomes impossible. Cheers, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 03 Jan 2015, at 23:14, Rafal Pietrak ra...@ztk-rp.eu wrote: Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the wrong database deity! That’s Codd-relationality, of course. Not Boyce. The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says: rule 3: systematic treatment of null values; hmmm this is a little broader then support for null. I would think, that: 1. if a sequence of update XX set fk_field=null; then delete YY depending on that FK, for a particular schema definition works ... 2. so the implementation of FK should support that too ... to be called systematic, right? 3. and the simplest way to do that for the case at hand, within an on delete action, is to skip those parts of FK, that are marked as not null within the referring table. That would be a requirement for rdbms implementation that claims compliance with Codd rule nr.3 :) I translated Codd-relationality to English, possibly it’s named differently. Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did find is that the theory I referred to stems from 1970(!), but that’s the closest I got. Anyway, wikipedia has the following to say about the issue, although their explanation is a bit obtuse concerning the definition of candidate keys from which to choose a primary key (http://en.wikipedia.org/wiki/Unique_key): A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination of columns which uniquely specify a row; it is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. They blatantly leave out why primary keys have that implicit NOT NULL! Is this some forgotten about piece of database theory? I’ll have to ask my teachers where they got their definition from! Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
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. Not sure about the standard, but pretty sure it will foul things up in general. From the table structures above the user is identified by a natural key of (username, domain). You are looking to break that key by losing the username in both mailusers and mailboxes. Yet you want to retain user content in mailusers. Not sure what purpose that is going to serve when you have no defined means of identifying the content? In my opinion, this is a use case for a surrogate key. As a sort of audit trail. Mail message contains everything that's necesery to recover information when a situation arises. Aah, so there is a 'defined means'. May be it's not the best way to do that, but currntly that's the plan: 1. keep the original 2. drop only minimal set of information, when user is discontiniued - currently just the username. Naturally, If I will not figure out how to setup such constraint automation, I'll have to revisit the initial plan (I hate to do that :) 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. 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. -R -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
On 01/02/2015 04:31 AM, Rafal Pietrak wrote:
Hello,
Rewriting my mail-hub I fell into the following problem:
1. I have a table with mailmessages, which has an FK to a table of hub
users.
2. I'd like to retain the content of message repository (with it's
domain key not cleared), when I drop a particular username from service
to release that username to others.
3. I try to do that with FK on-update/on-delete actions, but to no avail:
testcase---(against postgresql v9.1 hosted by
debian)---
CREATE TABLE maildomains (domain text primary key, profile text not null);
CREATE TABLE mailusers (username text , domain text references
maidomains(domain), 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 on delete set null);
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');
DELETE FROM mailusers ;
=== ERROR: SQL UPDATE ONLY public.mailboxes... etc...
But:
UPDATE mailboxes SET username = null;
DELETE FROM mailusers ;
=== OK!!!
SELECT * from mailboxes ;
username | domain| mailmessage
--+-+--
| example.com | Hello
--END testcase
I tried a TRIGGER BEFORE DELETE on table mailuser to set the username
being deleted to NULL (and simulate the above OK example part), but the
update does not propagate along constraints before constraint error is
detected and the whole delete get aborted by postgres.
Is there a way to implement that sort of referrential constraints (i.e.:
just partially set null on delete)?
Would it violate SQL standard (signifficantly), if an on delete set
null action just ignored all the FK columns that have a NOT NULL
constraint set?
Not sure about the standard, but pretty sure it will foul things up in
general. From the table structures above the user is identified by a
natural key of (username, domain). You are looking to break that key by
losing the username in both mailusers and mailboxes. Yet you want to
retain user content in mailusers. Not sure what purpose that is going to
serve when you have no defined means of identifying the content? In my
opinion, this is a use case for a surrogate key.
Thenx,
-R
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partial on-delete set null constraint
Hello,
Rewriting my mail-hub I fell into the following problem:
1. I have a table with mailmessages, which has an FK to a table of hub
users.
2. I'd like to retain the content of message repository (with it's
domain key not cleared), when I drop a particular username from service
to release that username to others.
3. I try to do that with FK on-update/on-delete actions, but to no avail:
testcase---(against postgresql v9.1 hosted by
debian)---
CREATE TABLE maildomains (domain text primary key, profile text not null);
CREATE TABLE mailusers (username text , domain text references
maidomains(domain), 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 on delete set null);
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');
DELETE FROM mailusers ;
=== ERROR: SQL UPDATE ONLY public.mailboxes... etc...
But:
UPDATE mailboxes SET username = null;
DELETE FROM mailusers ;
=== OK!!!
SELECT * from mailboxes ;
username | domain| mailmessage
--+-+--
| example.com | Hello
--END testcase
I tried a TRIGGER BEFORE DELETE on table mailuser to set the username
being deleted to NULL (and simulate the above OK example part), but the
update does not propagate along constraints before constraint error is
detected and the whole delete get aborted by postgres.
Is there a way to implement that sort of referrential constraints (i.e.:
just partially set null on delete)?
Would it violate SQL standard (signifficantly), if an on delete set
null action just ignored all the FK columns that have a NOT NULL
constraint set?
Thenx,
-R
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
Your main problem is that column domain of mailboxes is specified as NOT NULL, so of course the SET NULL option will not work. Here is the full message I see when I execute your code, which should be self explanatory. ERROR: null value in column domain violates not-null constraint DETAIL: Failing row contains (null, null, Hello). CONTEXT: SQL statement UPDATE ONLY public.mailboxes SET username = NULL, domain = NULL WHERE $1 OPERATOR(pg_catalog.=) username AND $2 OPERATOR(pg_catalog.=) domain ** Error ** ERROR: null value in column domain violates not-null constraint SQL state: 23502 Detail: Failing row contains (null, null, Hello). Context: SQL statement UPDATE ONLY public.mailboxes SET username = NULL, domain = NULL WHERE $1 OPERATOR(pg_catalog.=) username AND $2 OPERATOR(pg_catalog.=) domain
Re: [GENERAL] partial on-delete set null constraint
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: - 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. Apparently I don't know how to do that. I'd appreciate any help. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partial on-delete set null constraint
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
