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
wherec.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
wherep.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
andi <= 20)) inherits (p);create table p3 (check (i > 20 and i <= 30)) inherits (p);create trigger trp1 after insert on
p1for each row execute procedure fp();create trigger trp2 after insert on p2 for each row execute procedure fp();create
triggertrp3 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.