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*

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.

psql \r changed behavior in pg10

[Emanuel Araújo]

Hi,

When used PostgreSQL 9.6.* it was common use in psql \r\e to clear a buffer
query and open the empty text editor.  Works fine for me and it was very
useful in my tasks.

Since PostgreSQL 10, this behavior changed and not more clear the
buffer query.

psql helps show that \r reset (clear) the query buffer but in practice
don't work or I do not understand how would work.

Example using postgrsql 11.8 :

select 1;
\e
-> Open temp file with a last statement "select 1;"
exit text editor and run command.  It's ok.
\r
\e
-> Open temp file with the same last command "select 1;"
is it right?

-- 


*Regards,Emanuel Araújo*