Re: Call of function inside trigger much slower than explicit function call

On Tue, Feb 17, 2009 at 11:17 AM, Alexander Gorban
<alex(dot)gorban(at)gmail(dot)com> wrote:
> Hi,
>
> I have table containing bytea and text columns. It is my storage for
> image files and it's labels. Labels can be 'original' and 'thumbnail'.
> I've C-function defined in *.so library and corresponding declaration in
> postgres for scaling image. This function scale image and insert it into
> the same table with the label 'thumbnail'. I have trigger on before
> insert or update on the table which make thumbnail for image labeled as
> 'original'.
>
> Inserting single image into the table takes about 3 SECONDS!. But call
> of scaling function directly in psql command prompt is approximately 20
> times faster. If I comment out scaling function call in the trigger,
> insertion, and it is evident, becomes immediate (very fast).
>
> Here my somehow pseudo code:
>
> CREATE TABLE images_meta
> (
> data bytea,
> label text
> );
>
> CREATE FUNCTION imscale(data bytea, width integer)
> RETURNS integer AS 'libmylib.so', 'imscale' LANGUAGE 'c';
>
> CREATE FUNCTION auto_scale() RETURNS trigger AS $$
> DECLARE
> notused integer;
> BEGIN
> IF NEW.label = 'original' THEN
> notused := imscale(NEW.data, 128);
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE PLPGSQL;

Well my first guess is that when you actually do the insertion you
have to transfer the file from the client to the database, but when
you subsequently call the function by hand you're calling it on data
that is already in the database, so there's no transfer time... how
big are these images, anyway?

...Robert