Re: Inheritance of foregn key constraints.

On Tue, Apr 1, 2014 at 9:13 AM, Andrzej Mazurkiewicz <
andrzej(at)mazurkiewicz(dot)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(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)
>

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