Thread: BUG #15482: Foreign keys to a partition (NOT A PARTITIONED) break theserver

The following bug has been logged on the website:

Bug reference:      15482
Logged by:          Gianluca Alberici
Email address:      alberici@abinetworks.biz
PostgreSQL version: 11.0
Operating system:   Debian Stretch
Description:

That happens also for non circular refernces (ie other tables referring to a
partition)
If i have a partitioned table...

abi2=> \d+ _tmp_test_loop_ref_part 
                          Table "phoebe._tmp_test_loop_ref_part"
 Column |   Type   | Collation | Nullable | Default | Storage | Stats target
| Description 
--------+----------+-----------+----------+---------+---------+--------------+-------------
 id     | integer  |           | not null |         | plain   |
| 
 refid  | integer  |           |          |         | plain   |
| 
 pi     | smallint |           |          | 0       | plain   |
| 
Partition key: RANGE (pi)
Partitions: _tmp_test_loop_ref FOR VALUES FROM ('0') TO ('1')

and a partition on this table

abi2=> \d _tmp_test_loop_ref
         Table "phoebe._tmp_test_loop_ref"
 Column |   Type   | Collation | Nullable | Default 
--------+----------+-----------+----------+---------
 id     | integer  |           | not null | 
 refid  | integer  |           |          | 
 pi     | smallint |           |          | 0
Partition of: _tmp_test_loop_ref_part FOR VALUES FROM ('0') TO ('1')
Indexes:
    "_tmp_test_loop_ref_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_loop_ref" FOREIGN KEY (refid) REFERENCES _tmp_test_loop_ref(id) ON
UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "_tmp_test_loop_ref" CONSTRAINT "fk_loop_ref" FOREIGN KEY (refid)
REFERENCES _tmp_test_loop_ref(id) ON UPDATE CASCADE ON DELETE RESTRICT

is it normal that server break (segmentation fault, recovery mode and
restart !) if i 

abi2=> begin;
BEGIN
abi2=> delete from _tmp_test_loop_ref where id=1;
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.
!> 

Heres the LOG:

2018-11-01 17:34:28.266 CET [10401] LOG:  server process (PID 18213) was
terminated by signal 11: Segmentation fault
2018-11-01 17:34:28.266 CET [10401] DETAIL:  Failed process was running:
delete from _tmp_test_loop_ref where id=1;
2018-11-01 17:34:28.266 CET [10401] LOG:  terminating any other active
server processes
2018-11-01 17:34:28.266 CET [17927] WARNING:  terminating connection because
of crash of another server process
2018-11-01 17:34:28.266 CET [17927] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2018-11-01 17:34:28.266 CET [17927] HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2018-11-01 17:34:28.267 CET [18241] phoebe@abi2 FATAL:  the database system
is in recovery mode
2018-11-01 17:34:28.270 CET [10401] LOG:  all server processes terminated;
reinitializing
2018-11-01 17:34:28.318 CET [18242] LOG:  database system was interrupted;
last known up at 2018-11-01 17:27:13 CET
2018-11-01 17:34:28.491 CET [18242] LOG:  database system was not properly
shut down; automatic recovery in progress
2018-11-01 17:34:28.507 CET [18242] LOG:  redo starts at 1/E89B34B0
2018-11-01 17:34:28.507 CET [18242] LOG:  invalid record length at
1/E89B3590: wanted 24, got 0
2018-11-01 17:34:28.507 CET [18242] LOG:  redo done at 1/E89B3558
2018-11-01 17:34:28.595 CET [10401] LOG:  database system is ready to accept
connections

On 2018-Nov-01, PG Bug reporting form wrote:

> is it normal that server break (segmentation fault, recovery mode and
> restart !) if i 

Of course not.  Looking into it now.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-Nov-02, Alvaro Herrera wrote:

> On 2018-Nov-01, PG Bug reporting form wrote:
> 
> > is it normal that server break (segmentation fault, recovery mode and
> > restart !) if i 
> 
> Of course not.  Looking into it now.

I cannot reproduce it,

create table part (a int primary key, b int) partition by range (a);
create table part1 partition of part for values from (0) to (1000);
alter table part1 add foreign key (b) references part1 on update cascade on delete restrict;
insert into part1 values (1, null);
insert into part1 values (10, 1);
delete from part1 where a = 1;

This works fine (i.e. throws error that referencing row exists).

There must be some other element to your schema that I'm missing.  What
is it?  Also, if you got a core file, can you obtain a backtrace from
it?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services