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

On 2018-Nov-02, alberici@abinetworks.biz wrote:

> Have you tried create fk after inserts and after fk delete?

I think I did -- no dice.  If you have a reproducible way to make this
crash, please send a SQL script for it.  Mind reading is failing me today.

Also, please keep the list copied when you reply.

-- 
Álvaro Herrera                PostgreSQL Expert, https://www.2ndQuadrant.com/

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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> 

"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

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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> 

"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

Seems solved in 11.1


On 11/5/18 9:16 PM, Tom Lane wrote:
> "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