Deadlock - Mailing list pgsql-admin

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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.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
>