Thread: disable and enable trigger all when a foreign keys

=?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.

            regards, tom lane

[1] The general assumption in PG is that superusers know what they're
doing.

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 7/12/19 7:04 AM, Tom Lane wrote:
>> Well, yeah, DISABLE TRIGGER ALL defeats enforcement of FK constraints.
>> That's why you have to be superuser to use it[1].

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

> 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.

Well, because ALTER ... ENABLE TRIGGER does what it says on the tin: it
re-enables firing of the trigger.  That means future inserts and updates
will be checked; it doesn't extend to somehow checking events for which
trigger firings didn't happen in the past.

The OP already found the best method to get out of this, which is to
drop and re-add the FK constraint.  Adding the constraint will force
a full verification scan, which is what you need here since there's
no information available about which checks were missed.

            regards, tom lane

On 2019-Jul-12, Tom Lane wrote:

> The OP already found the best method to get out of this, which is to
> drop and re-add the FK constraint.  Adding the constraint will force
> a full verification scan, which is what you need here since there's
> no information available about which checks were missed.

Maybe it would make sense to be able to mark the FK as not validated, so
that you can apply ALTER CONSTRAINT ... VALIDATE.  Seems better than
DROP + ADD.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services