On 7/12/19 7:04 AM, Tom Lane wrote:
=?UTF-8?Q?Emanuel_Ara=C3=BAjo?= <eacshm@xxxxxxxxx> 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@xxxxxxxxxxx
