disable and enable trigger all when a foreign keys

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

disable and enable trigger all when a foreign keys

Emanuel Araújo
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?

In this case I have to drop constraint and re-create again to get my goal.

-- Statements to reproduce
drop table if exists a;
drop table if exists b;
create table if not exists a (id int primary key , id_b int, descr text);
create table if not exists b (id int primary key , descr text);
alter table a add constraint a_id_b_fkey foreign key (id_b) references b(id);
alter table a disable trigger all ;
insert into b values (1, 'house');
insert into b values (2, 'apartment');
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');
select * from a;
select * from b;
alter table a enable trigger all;
select * from a;
select * from b;    

--
Atenciosamente,

Emanuel Araújo

Linux Certified, DBA PostgreSQL

Reply | Threaded
Open this post in threaded view
|

Re: disable and enable trigger all when a foreign keys

Tom Lane-2
=?UTF-8?Q?Emanuel_Ara=C3=BAjo?= <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: disable and enable trigger all when a foreign keys

Adrian Klaver-4
On 7/12/19 7:04 AM, Tom Lane wrote:

> =?UTF-8?Q?Emanuel_Ara=C3=BAjo?= <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: disable and enable trigger all when a foreign keys

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: disable and enable trigger all when a foreign keys

Emanuel Araújo
Yeah, I understood, I have to do that, drop and re-add constraint to avoid issues.