Re: [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION

2009/11/26 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Thu, 2009-11-26 at 05:01 +0100, Pavel Stehule wrote:
>> It working like:
>>
>> 1. EXECUTE SELECT 0 FROM generate_series(1,...);
>> 2. STORE RESULT TO TABLE zero;
>> 3. EXECUTE SELECT 1/i FROM zero;
>> 4. STORE RESULT TO TABLE tmp;
>>
>> Problem is in seq execution. Result is stored to destination after
>> execution - so any materialisation is necessary,
>>
>
> My example showed that steps 3 and 4 are not executed sequentially, but
> are executed together. If 3 was executed entirely before 4, then the
> statement:
>  insert into tmp select 1/i from zero;
> would have to read the whole table "zero" before an error is
> encountered.

you have a true. I checked it with functions in plpgsql and before trigger

postgres=# create or replace function generator() returns setof int as
$$begin raise notice 'generator start'; for i in 1..10 loop raise
notice 'generator %', i; return next i; end loop; raise notice
'generator end'; return; end$$ language plpgsql;
CREATE FUNCTION

postgres=# create or replace function rowfce(int) returns int as
$$begin raise notice 'rowfce %i', $1; return $1 + 1; end; $$ language
plpgsql;
CREATE FUNCTION

postgres=# create function trgbody() returns trigger as $$begin raise
notice 'trgbody %', new; return new; end;$$ language plpgsql;
CREATE FUNCTION

postgres=# create trigger xxx before insert on dest for each row
execute procedure trgbody();
CREATE TRIGGER

then I checked

postgres=# insert into dest select rowfce(i) from generator() g(i);
NOTICE: generator start
NOTICE: generator 1
NOTICE: generator 2
NOTICE: generator 3
NOTICE: generator 4
NOTICE: generator 5
NOTICE: generator 6
NOTICE: generator 7
NOTICE: generator 8
NOTICE: generator 9
NOTICE: generator 10
NOTICE: generator end
NOTICE: rowfce 1i
NOTICE: trgbody (2)
NOTICE: rowfce 2i
NOTICE: trgbody (3)
NOTICE: rowfce 3i
NOTICE: trgbody (4)
NOTICE: rowfce 4i
NOTICE: trgbody (5)
NOTICE: rowfce 5i
NOTICE: trgbody (6)
NOTICE: rowfce 6i
NOTICE: trgbody (7)
NOTICE: rowfce 7i
NOTICE: trgbody (8)
NOTICE: rowfce 8i
NOTICE: trgbody (9)
NOTICE: rowfce 9i
NOTICE: trgbody (10)
NOTICE: rowfce 10i
NOTICE: trgbody (11)

so INSERT INTO SELECT works well. Problem is in func scan implementation.

Regards
Pavel Stehule

>
> However, the statement errors immediately, showing that steps 3 and 4
> are pipelined.
>
> Regards,
>        Jeff Davis
>
>