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

I do no, but you really need rescale the image when he comes to database? or
can you doing this after, in a schudeled job?
If you need resize the image en it comes, I believe you pay a price related
about performance because the this is working to save image, the toast
strtucture are receiving the data and the server need sync this... all at
same time...
can you mail the times for the insert with and without this trigger?

2009/2/17 Robert Haas <robertmhaas(at)gmail(dot)com>

> On Tue, Feb 17, 2009 at 12:46 PM, Alexander Gorban
> <alex(dot)gorban(at)gmail(dot)com> wrote:
> > В Втр, 17/02/2009 в 12:24 -0500, Robert Haas пишет:
> >> 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
> >
> > Also I've defined function to load images from disk directly inside sql
> > query:
> >
> > CREATE FUNCTION bytea_load_from_file(path text) RETURNS BYTEA
> > AS 'libmylib.so','bytea_load_from_file' LANGUAGE C;
> >
> > and use it in both cases - for insertion of image and to call function
> > directly. So, there is no difference it times spent for image loading.
> > Here is code that I use
> > 1. Insertion example:
> > test_base=# insert INTO images_meta(label,data) VALUES('original',
> > bytea_load_from_file('/tmp/test.jpg'));
> >
> > 2. Direct call:
> > test_base=#select imscale(bytea_load_from_file('/tmp/test.jpg'),128);
> >
> > I realize, that insertion require more operations to perform (insert
> > initial image, fire after insert trigger, insert thumbnail, fire trigger
> > again after insertion thumbnail). But these operations not seems very
> > hard.
> >
> > Size of image, that I use for tests is about 2MB. That is why 3sec. it
> > is very long time to process it.
>
> Well, that does sound weird... can you post the full definition for
> the images_meta table? Are there any other triggers on that table?
> Is it referenced by any foreign keys? How fast is the insert if you
> drop the trigger?
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Ivo Nascimento - Iann
-------------------------------------
| twitter: ivonascimento . |
| http://ianntech.com.br. |
| ZCE ID 227463685 |
-------------------------------------