Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Deadlock
Date: Wed, 1 Aug 2007 11:43:53 -0600
Message-ID: <F4E6A2751A2823418A21D4A160B6898807A6EC@fletch.stackdump.local>
In-Reply-To: <F4E6A2751A2823418A21D4A160B6898807A6E9@fletch.stackdump.local>
Thread-Topic: Deadlock
Thread-Index: AcfUXFgcHXjBg6KNStalbQ/gjCc4/QAAf3/AAAFDPgA=
References: <4544e0330708010950v464268edsf91d44b92bf55400@mail.gmail.com>
	<F4E6A2751A2823418A21D4A160B6898807A6E9@fletch.stackdump.local>
From: "Benjamin Krajmalnik" <kraj@illumen.com>
To: "Benjamin Krajmalnik" <kraj@illumen.com>,
	"pgsql-admin" <pgsql-admin@postgresql.org>

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