Hi, I'm seeing the following behaviour with the table and functions given below:
db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 197,507 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 341,880 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 692,603 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 985,253 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 1241,334 ms Or even worse (fresh drop/create of the table and functions): db=# insert into f select id from full_sequence(1, 10000); INSERT 0 10000 Time: 22255,767 ms db=# insert into f select id from full_sequence(1, 10000); INSERT 0 10000 Time: 45398,433 ms db=# insert into f select id from full_sequence(1, 10000); INSERT 0 10000 Time: 67993,476 ms Wrapping the commands in a transaction only accumulates the penalty at commit. It seems in this case the time needed for a single deferred trigger somehow depends on the number of dead tuples in the table, because a vacuum of the table will 'reset' the query-times. However, even if I wanted to, vacuum is not allowed from within a function. What is happening here? And more importantly, what can I do to prevent this? NB. My real-world application 'collects' id's in need for deferred work, but this work is both costly and only needed once per base record. So I use an 'update' table whose content I join with the actual tables in order to do the work for _all_ the base records involved upon the first execution of the deferred trigger. At the end of the trigger, this 'update' table is emptied so any additional deferred triggers on the same table will hardly lose any time. Or at least, that was the intention.... *********** demo script *********** drop table f cascade; drop function tr_f_def() cascade; drop function full_sequence(integer, integer); drop type full_sequence_type; create table f (id int); create function tr_f_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE STRICT SECURITY INVOKER AS ' DECLARE BEGIN -- do stuff with all the ids in the table -- delete the contents -- delete from f; IF EXISTS (SELECT 1 FROM f) THEN DELETE FROM F; VACUUM F; END IF; RETURN NULL; END;'; create type full_sequence_type as (id int); create function full_sequence(integer, integer) RETURNS SETOF full_sequence_type LANGUAGE 'plpgsql' IMMUTABLE STRICT SECURITY INVOKER AS ' DECLARE my_from ALIAS FOR $1; my_to ALIAS FOR $2; result full_sequence_type%ROWTYPE; BEGIN -- just loop FOR i IN my_from..my_to LOOP result.id = i; RETURN NEXT result; END LOOP; -- finish RETURN; END;'; CREATE CONSTRAINT TRIGGER f_def AFTER INSERT ON f DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_def(); *********** demo script *********** db=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 -- Best, Frank. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html