Skip site navigation (1) Skip section navigation (2)

Call of function inside trigger much slower than explicit function call

From: Alexander Gorban <alex(dot)gorban(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Call of function inside trigger much slower than explicit function call
Date: 2009-02-17 16:17:14
Message-ID: 1234887434.14815.37.camel@gas-laptop (view raw or flat)
Thread:
Lists: pgsql-performance
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;


Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2009-02-17 16:19:23
Subject: Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller
Previous:From: Kevin GrittnerDate: 2009-02-17 16:16:13
Subject: Re: Query composite index range in an efficient way

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group