Re: Partitioning option for COPY

Stephan Szabo wrote:
> 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.
>
Well, if you don't insert anything in p (the table, try to avoid using
the same name for the table and the column in an example), copy will
insert (1,3) in p1 and then the trigger will evaluate

select count(*) from p where p.i = NEW.p => NEW.p is 3 and the only p.i available is 1.

This should return 0 rows and raise the exception. This seems normal to me.
The only reason it works for i is because you inserted the values before
the copy.

Am I missing something?
Emmanuel

--
Emmanuel Cecchet
Aster Data
Web: http://www.asterdata.com