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@xxxxxxxxxxxxxx > [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of > Benjamin Krajmalnik > Sent: Wednesday, August 01, 2007 11:32 AM > To: pgsql-admin > Subject: Re: 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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
