Call of function inside trigger much slower than explicit function call

[Alexander Gorban <alex.gorban@xxxxxxxxx>]

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;


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance