Deadlock

My aplogies - I forgot to set the subject of the problem I am having
when I got lazy and used "reply".

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of
> Benjamin Krajmalnik
> Sent: Wednesday, August 01, 2007 11:32 AM
> To: pgsql-admin
> Subject: Re: [ADMIN] stracing a connection
>
> I have ascheduled pgAgent job which runs monthly executing a
> stored procedure which handles some partitoned tables.
> Essentially, it truncated the data in a given partitio and
> then it changes its rules so it will be ready to accept the
> data for its respective next cycle.
>
> The stored procedure follows:
>
>
>
> CREATE OR REPLACE FUNCTION fn_cleardata()
> RETURNS void AS
> $BODY$
> declare
> year integer;
> month integer;
> endmonth integer;
> endyear integer;
> startoffset integer;
> endoffset integer;
> currentdate date;
> i integer;
> tablename varchar;
> startday integer;
> endday integer;
>
> begin
> currentdate := CURRENT_DATE;
> month := DATE_PART('month', currentdate)-2;
> year := DATE_PART('year', currentdate);
>
> if month <=0 then
> month := month+12;
> else
> year := year+1;
> end if;
>
> startoffset := ((month-1)*4)+1;
> endoffset := startoffset+3;
> startday := 1;
> endday := 9;
> endmonth = month;
> endyear = year;
>
> for i in startoffset..endoffset loop
> if i < 10 then
> tablename := 'tblksdata' || 0 || i;
> else
> tablename := 'tblksdata' || i;
> end if;
> EXECUTE 'TRUNCATE TABLE '||tablename;
> EXECUTE 'ALTER TABLE '||tablename||' DROP CONSTRAINT
> '||tablename||'_datecheck';
> EXECUTE 'ALTER TABLE '||tablename||' ADD CONSTRAINT
> '||tablename||'_datecheck
> CHECK (testtime >=
> '''||year||'-'||month||'-'||startday||' 00:00:00''::timestamp
> without time zone
> AND testtime <
> '''||endyear||'-'||endmonth||'-'||endday||'
> 00:00:00''::timestamp without time zone)';
> EXECUTE 'CREATE OR REPLACE RULE '||tablename||'_rl_insert AS
> ON INSERT TO tblksdata
> WHERE new.testtime >=
> '''||year||'-'||month||'-'||startday||'''::timestamp without time zone
> AND new.testtime <
> '''||endyear||'-'||endmonth||'-'||endday||'''::timestamp
> without time zone
> DO INSTEAD INSERT INTO '||tablename||'
> (testtime, replyval, statusid, kstestssysid)
> VALUES (new.testtime,
> new.replyval, new.statusid, new.kstestssysid)';
>
> startday := startday + 8;
> endday := endday + 8;
> if startday = 25 then
> endday = 1;
> endmonth = endmonth + 1;
> if endmonth > 12 then
> endyear := endyear + 1;
> endmonth := endmonth - 12;
> end if;
> end if;
> end loop;
> end
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION fn_cleardata() OWNER TO postgres;
>
>
> If I run it manually from pgAdmin during the day it runs fine
> without returning an error. When it runs scheduled, it is
> returning an error.
>
> At the same time, another function accessing the parent table
> may be running, calculating statistical data for the tests.
> The partition which is truncated and whose constraints and
> rule is being rewritten has data which is beyond the range of
> that in the statistical calculation function, yet I am
> getting a deadlock. The error in the pgAgent log
> follows:
>
>
> ERROR: deadlock detected
>
> DETAIL: Process 47642 waits for AccessExclusiveLock on
> relation 317009 of database 316900; blocked by process 46648.
>
> Process 46648 waits for RowExclusiveLock on relation 317071
> of database 316900; blocked by process 47642.
>
> CONTEXT: SQL statement "CREATE OR REPLACE RULE
> tblksdata21_rl_insert AS
>
> ON INSERT TO tblksdata
>
> WHERE new.testtime >= '2008-6-1'::timestamp without time zone
>
> AND new.testtime < '2008-6-9'::timestamp without time zone
>
> DO INSTEAD INSERT INTO tblksdata21 (testtime, replyval, statusid,
> kstestssysid)
>
> VALUES (new.testtime, new.replyval, new.statusid, new.kstestssysid)"
>
> PL/pgSQL function "fn_cleardata" line 43 at execute statement
>
> tblksdata is the parent table. The functio running against
> it is not updateing any of the data - it is simply
> calculating aggregates for specifc rows (max, min, stddev).
>
> Any suggestions would be appreciated.
> I am running PostgreSQL 8.1.4 on FreeBSD.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>