Re: Partitioning option for COPY - Mailing list pgsql-hackers

Jan Urbański wrote:
> Emmanuel Cecchet wrote:
>> Hi all,
>
> Hi!,
>
>> partitioning option for COPY

> Attached are 3 files that demonstrate problems the patch has.

And the click-before-you-think prize winner is... me.

Test cases attached, see the comments for expected/actual results.

Jan
-- segfaults, probably uninitialised cache oid list

-- disabling cache fixes it
-- set copy_partitioning_cache_size = 0;

drop table parent cascade;

create table parent(i int);
create table c1 (check (i > 0 and i <= 1)) inherits (parent);

copy parent from stdin with (partitioning);
1
\.

drop table parent cascade;

create table parent(i int);
create table c1 (check (i > 0 and i <= 1)) inherits (parent);


copy parent from stdin with (partitioning);
1
\.
set copy_partitioning_cache_size = 0;

drop table parent cascade;

create table parent(i int, j int);
create table c1 (check (i > 0 and i <= 1)) inherits (parent);
create table c2 (check (i > 1 and i <= 2)) inherits (parent);
create table c3 (check (i > 2 and i <= 3)) inherits (parent);

create index c1_idx on c1(j);

copy (select i % 3 + 1, i from generate_series(1, 1000) s(i)) to '/tmp/parent';

copy parent from '/tmp/parent' with (partitioning);

analyse;

set enable_seqscan to false;
-- no rows, index was not updated
select * from c1 where j = 3;

set enable_seqscan to true;
set enable_indexscan to false;
-- some rows
select * from c1 where j = 3;
set copy_partitioning_cache_size = 0;

drop table parent cascade;
drop table audit cascade;
drop function audit();

create table parent(i int);
create table c1 (check (i > 0 and i <= 1)) inherits (parent);
create table c2 (check (i > 1 and i <= 2)) inherits (parent);
create table c3 (check (i > 2 and i <= 3)) inherits (parent);

create table audit(i int);

create function audit() returns trigger as $$ begin insert into audit(i) values (new.i); return new; end; $$ language
plpgsql;

create trigger parent_a after insert on parent for each row execute procedure audit();
-- the before trigger on the parent would get fired
-- create trigger parent_a2 before insert on parent for each row execute procedure audit();
create trigger c1_a before insert on c1 for each row execute procedure audit();
create trigger c1_a2 after insert on c1 for each row execute procedure audit();

copy parent from stdin with (partitioning);
1
2
3
\.

-- no rows
select * from audit;