Hi All,
The copy command on partitioned table causes server crash when before
insert row trigger is created on one of its partition. Please find the
following test-case to reproduce the crash.
-- create a partitioned table
create table part_copy_test (a int, b int, c text) partition by list (b);
create table part_copy_test_a1 partition of part_copy_test for values in(1);
create table part_copy_test_a2 partition of part_copy_test for values in(2);
-- insert enough rows to allow multi-insert into a partitioned table.
copy (select x,1,'One' from generate_series(1,1000) x
union all
select x,2,'two' from generate_series(1001,1010) x
union all
select x,1,'One' from generate_series(1011,1020) x) to
'/tmp/multi_insert_data.csv';
-- create before insert row trigger on part_copy_test_a2
create function part_ins_func() returns trigger language plpgsql as
$$
begin
return new;
end;
$$;
create trigger part_ins_trig before insert on part_copy_test_a2
for each row execute procedure part_ins_func();
-- run copy command on partitioned table.
copy part_copy_test from '/tmp/multi_insert_data.csv';
postgres=# copy part_copy_test from '/tmp/multi_insert_data.csv';
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.
!>
I've spent some time looking into this issue and found that,
CopyFrom() is trying perform multi-insert for the partition that has
before insert row trigger created on it which is not expected. When a
normal table with before insert row trigger is created, CopyFrom
doesn't allow multi insert on such tables and i guess same should be
the case with table partition as well. Please correct me if i my
understanding is wrong ?
Thanks,
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
