Re: Ris: BUG #15482: Foreign keys to a partition (NOT A PARTITIONED) break the server

Alvaro,

Actually it is a strange behavior, look at this:

abi2=> begin;
BEGIN
abi2=>*delete from _abi_main_pof_t where poftid=1843635; server closed the
connection unexpectedly     This probably means the server terminated
abnormally     before or while processing the request. The connection to
the server was lost. Attempting reset: Failed. !> \q*
root@wnet0:~# psql -Uphoebe abi2 -p5433
psql (11.0 (Debian 11.0-1.pgdg90+2))
Type "help" for help.

abi2=> *vacuum full analyze _abi_main_pof_t;* VACUUM abi2=> *vacuum full
analyze _abi_main_pof_r;*
VACUUM
abi2=> begin;
BEGIN
abi2=>*delete from _abi_main_pof_t where poftid=1843635;****ERROR:  update or
delete on table "_abi_main_pof_t" violates foreign key constraint
"_abi_main_pof_t_refid_fkey" on table "_abi_main_pof_t"****DETAIL:  Key
(poftid)=(1843635) is still referenced from table
"_abi_main_pof_t".****abi2=>*

Now, these two tables were originally normal tables with huge data in it.

They were added a pi (partition index) field to be child of a master
partitioned by range.

A master table were created and a 'brother' table too to host 'unused
records'.

Tables were put toghether and by updating pi's records were moved around
child tables.

This were done by removing triggers and fk's to speed up moving process.

Then keys and triggers were rebuilt and the problem arised.

After vacuuming as you can see...

I will repeat the whole 'migration' process the next days and see if it
happens again. If you want i can set up a user account on this machine
to play with this better than i could.

I think it could be useful to investigate the overall stability of the
server.

Let me know if youre interested in it.

Best Regards,

G

"Ing. Gianluca Alberici" <alberici@abinetworks.biz> writes:

Actually it is a strange behavior, look at this:

abi2=> begin;
BEGIN
abi2=>*delete from _abi_main_pof_t where poftid=1843635; server closed the
connection unexpectedly     This probably means the server terminated
abnormally     before or while processing the request. The connection to
the server was lost. Attempting reset: Failed. !> \q*

Is it possible that you've done ALTER TABLE ADD COLUMN on _abi_main_pof_t
or any other table touched by this query? If so, I bet this is explained
by that expand_tuple bug.

regards, tom lane

Guys,

I suppose Tom got it right: here we go. Alvaro, if you're not able to
reproduce i am opening an account for you.

abi2=> create table _tmp_test_loop_ref (id int4, refid int4, primary key (id));
CREATE TABLE
abi2=> alter table _tmp_test_loop_ref add constraint fk_loop_ref foreign key (refid) references _tmp_test_loop_ref(id) on update cascade on delete restrict;
ALTER TABLE
abi2=> insert into _tmp_test_loop_ref values (2,null);
INSERT 0 1
abi2=> insert into _tmp_test_loop_ref values (1,2);
INSERT 0 1
abi2=> begin; delete from _tmp_test_loop_ref where id=1;
BEGIN
DELETE 1
abi2=> rollback;
ROLLBACK
abi2=> create table _tmp_test_loop_ref_part (like _tmp_test_loop_ref including defaults including constraints);
CREATE TABLE
abi2=> drop table _tmp_test_loop_ref_part;
DROP TABLE
abi2=> alter table _tmp_test_loop_ref add pi smallint default 0;
ALTER TABLE
abi2=> create table _tmp_test_loop_ref_part (like _tmp_test_loop_ref including defaults including constraints) partition by range(pi);
CREATE TABLE
abi2=> begin; delete from _tmp_test_loop_ref where id=1;
BEGIN
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Hello,

It turns out that partitioning has nothing to do with this.

abi2=> create table _tmp_test_loop_ref (id int4, refid int4, primary key (id));
CREATE TABLE
abi2=> alter table _tmp_test_loop_ref add constraint fk_loop_ref foreign key (refid) references _tmp_test_loop_ref(id) on update cascade on delete restrict;
ALTER TABLE
abi2=> insert into _tmp_test_loop_ref values (2,null);
INSERT 0 1
abi2=> insert into _tmp_test_loop_ref values (1,2);
INSERT 0 1
abi2=> alter table _tmp_test_loop_ref add pi smallint default 0;
ALTER TABLE
abi2=> begin; delete from _tmp_test_loop_ref where id=1;
BEGIN
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Sorry for coming again...

G

Seems solved in 11.1