Re: disable and enable trigger all when a foreign keys

On 7/12/19 7:04 AM, Tom Lane wrote:
> =?UTF-8?Q?Emanuel_Ara=C3=BAjo?= <eacshm@gmail.com> writes:
>> This is a situation when we needed fill a new store data in a family env.
>> When I run "alter table a disable trigger all;" ... the foreign key
>> "a_id_b_fkey" was disabled to, its ok, but I needed run a load data into
>> tables "a"and "b" and table a exists a tuple that not exists in column id_b
>> -> (references b(id)).
> 
>> When finished, the command "alter table a enable trigger all" was executed
>> but not alert or broken, why? Cause orphan record is there.
> 
>> This behavior is common or when doing "enable trigger all" PostgreSQL
>> whould show me a error or a warning?
> 
> Well, yeah, DISABLE TRIGGER ALL defeats enforcement of FK constraints.
> That's why you have to be superuser to use it[1].  Perhaps disabling
> only user triggers would have been the way to do what you want.

The OP is probably trying to understand why the below happens:

create table if not exists a (id int primary key , id_b int, descr text);

insert into a values (1,1,'house sold');
insert into a values (2,1,'house 1 not sold');
insert into a values (3,2,'apartment 1 not sold');
insert into a values (4,null,'house to buy');
insert into a values (5,3,'car to sell');

create table if not exists b (id int primary key , descr text);

insert into b values (1, 'house');
insert into b values (2, 'apartment');

alter table a add constraint a_id_b_fkey foreign key (id_b) references 
b(id);
ERROR:  insert or update on table "a" violates foreign key constraint 
"a_id_b_fkey"
DETAIL:  Key (id_b)=(3) is not present in table "b".

Versus the above not happening when you re-enable a trigger. I know it 
is documented:

https://www.postgresql.org/docs/11/sql-altertable.html
" Disabling or enabling internally generated constraint triggers 
requires superuser privileges; it should be done with caution since of 
course the integrity of the constraint cannot be guaranteed if the 
triggers are not executed."

Still it has caught me before and I would be interested in knowing why 
the difference?


> 
>             regards, tom lane
> 
> [1] The general assumption in PG is that superusers know what they're
> doing.
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com