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.
