Re: [HACKERS] Inheritance of foregn key constraints - dropping isinternal triggers by a
On Tuesday 01 of April 2014 11:06:00 you wrote: > On Tue, Apr 1, 2014 at 9:13 AM, Andrzej Mazurkiewicz < > > andr...@mazurkiewicz.org> wrote: > > 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 > > Try using a quoted identifier: > > DROP TRIGGER "RI_ConstraintTrigger_c_19322" ON master; > > Regards, > > -- > FabrÃzio de Royes Mello > Consultoria/Coaching PostgreSQL Good Afternoon. Sorry for my mistake. I have forgotten about modifications of names to lower case. The owner of a table can drop trigger using the command DROP TRIGGER. However, where I can find information on philosophy of that approach. For me internal triggers are marked isinternal not to be accessed using SQL commands. Removing internals, f. e. FK triggers, by hand by a programmer and even by an administrator seems not to be a recommended practice. Kind Regards Andrzej Mazurkiewicz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 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@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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inheritance of foregn key constraints.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Inheritance of foregn key constraints.
http://wiki.postgresql.org/wiki/Todo Section "Inheritance" "Allow inherited tables to inherit indexes, UNIQUE constraints, and primary/FOREIGN KEYS" Good Morning. I started to program a patch for inheritance of the foreign key constraints. I. e. after applying the patch FKs are maintained between foreign table inheritance tree (master tree) and a given table inheritance tree (detail tree). 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. 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. It also requires an end user to remember about a triggers maintaining uniqueness of a parent tree key and about consistent unique indexes/PKs. Currently the patch works for 9.3 release for ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ...; ALTER TABLE ... DROP CONSTRAINT or DROP TABLE (master or detail root). This covers my needs. As soon as the patch is ready, matching postgres standards and tested I would like to add it to the distribution. I have the following questions. 1. Is the community interested in it? So should I continue my work since it matches the postgres standards? 2. If the answer is yes, please comment the above change of the dependency type. Kind regards Andrzej Mazurkiewicz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
