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 (ref. to > the last SELECT). > > Please have a look at the following actions. > > 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) >
Try using a quoted identifier: DROP TRIGGER "RI_ConstraintTrigger_c_19322" ON master; Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
