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(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com