Re: Inheritance of foregn key constraints.

Good Afternoon.

Enclosed please find continuation of the discussion of an accidental or
malicious breaking a server consistency.

After reading please comment if there are more objections for changing the
depedency type for trigger to constraint dependency from the
DEPENDENCY_INTERNAL to DEPENDENCY_AUTO.

That change is necessary to reduce scope of modifications necessary for an
implementation of the inheritance of foregn key constraints, particularly for
removing of objects.

Kind Regards
Andrzej Mazurkiewicz

On Saturday 22 of March 2014 11:13:56 you wrote:
> Andrzej Mazurkiewicz <andrzej(at)mazurkiewicz(dot)org> writes:
> >> So in other words, somebody could (accidentally or maliciously) break the
> >> constraint by dropping one of its implementation triggers. I doubt
> >> that's
> >> acceptable.

I have done some more digging in the subject.

All following tests are perfomed on my patched 9.3 postgres server where the
depedency type for trigger to constraint dependency has been changed to the
DEPENDENCY_AUTOMATIC.

It seems that if the trigger is internal (tgisinternal = true) it is not
visible to the DROP TRIGGER command. So it cannot be deleted using DROP
TRIGGER command, although the dependency type is DEPENDENCY_AUTOMATIC (ref. to
the last SELECT).

Please have a look at the following actions.

Kind regards
Andrzej Mazurkiewicz

They are performed by a lipa user. The lipa user is not a superuser;

postgres=# CREATE USER lipa;
CREATE ROLE
postgres=# CREATE DATABASE lipa OWNER lipa;
CREATE DATABASE

postgres93(at)tata:~$ psql -W lipa lipa
Password for user lipa:
psql (9.3.3)
Type "help" for help.

lipa=> SELECT CURRENT_USER;
current_user
--------------
lipa
(1 row)

lipa=> CREATE TABLE master (master_a int, CONSTRAINT pk_master PRIMARY KEY
(master_a));
CREATE TABLE
lipa=> CREATE TABLE detail (master_a int, detail_a int, CONSTRAINT fk0_detail
FOREIGN KEY (master_a) REFERENCES master(master_a));
CREATE TABLE
lipa=> SELECT oid, tgrelid, tgname FROM pg_trigger ;
oid | tgrelid | tgname
-------+---------+------------------------------
19322 | 19313 | RI_ConstraintTrigger_a_19322
19323 | 19313 | RI_ConstraintTrigger_a_19323
19324 | 19318 | RI_ConstraintTrigger_c_19324
19325 | 19318 | RI_ConstraintTrigger_c_19325
(4 rows)

lipa=> DROP TRIGGER RI_ConstraintTrigger_c_19322 ON master;
ERROR: trigger "ri_constrainttrigger_c_19322" for table "master" does not
exist
lipa=> DROP TRIGGER RI_ConstraintTrigger_c_19322 ON detail;
ERROR: trigger "ri_constrainttrigger_c_19322" for table "detail" does not
exist

lipa=> SELECT oid, tgrelid, tgname, tgconstraint FROM pg_trigger ;
oid | tgrelid | tgname | tgconstraint
-------+---------+------------------------------+--------------
19322 | 19313 | RI_ConstraintTrigger_a_19322 | 19321
19323 | 19313 | RI_ConstraintTrigger_a_19323 | 19321
19324 | 19318 | RI_ConstraintTrigger_c_19324 | 19321
19325 | 19318 | RI_ConstraintTrigger_c_19325 | 19321
(4 rows)

lipa=> SELECT * FROM pg_depend WHERE refobjid = 19321;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
2620 | 19322 | 0 | 2606 | 19321 | 0 | a
2620 | 19323 | 0 | 2606 | 19321 | 0 | a
2620 | 19324 | 0 | 2606 | 19321 | 0 | a
2620 | 19325 | 0 | 2606 | 19321 | 0 | a
(4 rows)