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

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.klaver@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

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.klaver@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

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

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@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

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

On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal@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

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

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@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

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

On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@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

W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:

On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@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

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 <rafal@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.klaver@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

On 03 Jan 2015, at 14:11, Alban Hertroys <haramrae@gmail.com> wrote:

On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal@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

On 03 Jan 2015, at 18:05, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:

On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@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

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

On 03 Jan 2015, at 23:14, Rafal Pietrak <rafal@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

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

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