Re: Partitioning option for COPY

On Sun, 22 Nov 2009, Emmanuel Cecchet wrote:

> As I explained to Tom, if the after row trigger is called asynchronously
> I get a relcache leak on the child table at the end of the copy
> operation. If the trigger is called synchronously (like a before row
> trigger) it works fine. Also calling the after row trigger synchronously
> allows me to detect any potential problem between the actions of the
> trigger and the routing decision. I am open to any suggestion for a more
> elegant solution.

Well, I think there are still some issues there that at least need to be
better documented.

For example,
create or replace function fi() returns trigger as '
begin
if (NEW.p is not null) then
if (select count(*) from i where i.i = NEW.p) = 0 then
raise exception ''No parent'';
end if;
end if;
return NEW;
end;
' language 'plpgsql';

create or replace function fc() returns trigger as '
begin
if (NEW.p is not null) then
if (select count(*) from c where c.i = NEW.p) = 0 then
raise exception ''No parent'';
end if;
end if;
return NEW;
end;
' language 'plpgsql';

create or replace function fp() returns trigger as '
begin
if (NEW.p is not null) then
if (select count(*) from p where p.i = NEW.p) = 0 then
raise exception ''No parent'';
end if;
end if;
return NEW;
end;
' language 'plpgsql';

drop table i;
drop table c;
drop table p cascade;

create table i(i int, p int);
create trigger tri after insert on i for each row execute procedure fi();

create table c(i int, p int);
create trigger trc after insert on c for each row execute procedure fc();

create table p(i int, p int);
create table p1 (check (i > 0 and i <= 10)) inherits (p);
create table p2 (check (i > 10 and i <= 20)) inherits (p);
create table p3 (check (i > 20 and i <= 30)) inherits (p);
create trigger trp1 after insert on p1 for each row execute procedure fp();
create trigger trp2 after insert on p2 for each row execute procedure fp();
create trigger trp3 after insert on p3 for each row execute procedure fp();

insert into i values (1,3),(2,1),(3,NULL);
copy c from stdin;
1 3
2 1
3 \N
\.
copy p from stdin with (partitioning);
1 3
2 1
3 \N
\.

gives me a successful load into i and c, but not into p with the current
patch AFAICS while a load where the 3 row is first does load.