Re: Inheritance of foregn key constraints.

Good Morning.

1. At the beginning some explanations.

I am a lazy person that tries not to reinvent a wheel.
So I try to use postgres way of automatic processing, i. e. automatic removing
dependent objects (which I consider an elegant solution and I really like it).
A a result, I have used the pg_depend table to force to remove dependent
entries.

2. At the moment the following behavior is a standard one for postgres.
- a child table (inheriting form a parent table(s) no FK) can be dropped;
- a referred table (master) can be freely dropped with a CASCADE option
(causing dropping of the FK);
- a referring table (detail) can be freely dropped (causing automatic dropping
of the FK);
- a CHECK constraint is inherited and the inheritance can be removed freely
although leaving the CHECK constraint (no FK);
- an inherited table with CHECK constraint can be freely dropped (no FK);
- inheritance can be added for existing tables and it can be removed (no FK).

3. The following decisions should be taken for the FK inheritance (partly
common issues, however I try to be precise).
- (GENERAL statement) Are modifications of a master side hierarchy (a referred
side) allowed without dropping the FK?
- (GENERAL statement) Are modifications of a detail side hierarchy (a referred
side) allowed without dropping the FK?
- Is detaching childs allowed in the master and detail hierarchy without
dropping the FK?
- Is dropping tables allowed in the master and detail hierarchy without
dropping the FK?
- Is adding inheritance allowed to the master and detail hierarchies without
dropping the FK?
- Is creating inheriting tables allowed in the master and detail hierarchies
without dropping the FK?

It would be good if the decisions were consistent with the existing behavior.

The consequences of the decisions are rather far going. For large databases
adding the FK constraint might last hours or days or perhaps weeks.

For my databases, although such modification would last hours and sometimes I
have strange and changing ideas - I can live with those hours.

Personally I would vote that the above modifications SHOULD BE ALLOWED. Simply,
because we do not drop the whole master or detail hierarchy but modify it and
it gives certain flexibility to manipulating the schema.

The above flexibility is similar to adding inheritance to the existing tables
and removing inheritance for them. We do not need to create another inherited
table and to move data into it from the existing table.

3. Perhaps , after making the above decisions, a discussion about an
implementing changes should be continued.

4.
> > My patch need one change that might be of significance.
> > A type of the depencencies (pg_depend) among the FK constraint
> > (pg_constraint) and the corresponding "RI_ConstraintTrigger" triggers has
> > to be changed from DEPENDENCY_INTERNAL to DEPENDENCY_AUTO.
>
> So in other words, somebody could (accidentally or maliciously) break the
> constraint by dropping one of its implementation triggers. I doubt that's
> acceptable.

The present postgres behavior ALLOWS accidental or malicious break the
constraint by dropping one of its implementation triggers. Please ref. to the
following example.

The following script has been run by the postgres user.

CREATE DATABASE lipa;
\c lipa
CREATE TABLE master (master_a int, CONSTRAINT pk_master PRIMARY KEY
(master_a));
CREATE TABLE detail (master_a int, detail_a int, CONSTRAINT fk0_detail FOREIGN
KEY (master_a) REFERENCES master(master_a));
SELECT oid, tgrelid, tgname FROM pg_trigger ;
DELETE FROM pg_trigger WHERE oid = (SELECT min(oid) FROM pg_trigger WHERE
tgname LIKE 'RI_ConstraintTrigger%' LIMIT 1);
SELECT oid, tgrelid, tgname FROM pg_trigger ;
DROP TABLE detail;
DROP TABLE master;
\c postgres
DROP DATABASE lipa;

The results of the run are as follows.

psql -f test-malicious-dropping-FK-triggers.sql postgres

CREATE DATABASE
You are now connected to database "lipa" as user "postgres".
CREATE TABLE
CREATE TABLE
oid | tgrelid | tgname
-------+---------+------------------------------
39898 | 39889 | RI_ConstraintTrigger_a_39898
39899 | 39889 | RI_ConstraintTrigger_a_39899
39900 | 39894 | RI_ConstraintTrigger_c_39900
39901 | 39894 | RI_ConstraintTrigger_c_39901
(4 rows)

DELETE 1
oid | tgrelid | tgname
-------+---------+------------------------------
39899 | 39889 | RI_ConstraintTrigger_a_39899
39900 | 39894 | RI_ConstraintTrigger_c_39900
39901 | 39894 | RI_ConstraintTrigger_c_39901
(3 rows)

psql:test-malicious-dropping-FK-triggers.sql:8: ERROR: could not find tuple
for trigger 39898
psql:test-malicious-dropping-FK-triggers.sql:9: ERROR: could not find tuple
for trigger 39898
You are now connected to database "postgres" as user "postgres".
DROP DATABASE

>
> > If this modification is not applied, the detail child table cannot be
> > dropped without prevous dropping the whole FK constraint because the
> > removing operation depend on the FK constraint of its parent table.
>
> Dropping a child table is going to have much larger problems than that,
> no? What about the values in the child table --- don't you risk orphaning
> referencing rows? Or are you only supporting this on the referencing
> side?
>
> In any case, it seems like DROP TABLE could remove the dependency entries
> for itself, rather than taking the risk of weakening the dependency type.

The directly above issue has already been covered by section 3.

Kind regards,
Andrzej Mazurkiewicz