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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
