Обсуждение: stracing a connection

Поиск
Список
Период
Сортировка

stracing a connection

От
"Peter Koczan"
Дата:
Hi all,

I'm running into a few problems with postgres connections, specifically notify/listening connections. What program(s) should I attach to the connection using gdb or strace in order to actually get some useful data?

i.e. I'll be using something like "strace -p [pid] [command]", what should I use for [command]?

Thanks,
Peter

Re: stracing a connection

От
"Benjamin Krajmalnik"
Дата:
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.


Deadlock

От
"Benjamin Krajmalnik"
Дата:
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
>

Re: stracing a connection

От
Alvaro Herrera
Дата:
Peter Koczan escribió:
> Hi all,
>
> I'm running into a few problems with postgres connections, specifically
> notify/listening connections. What program(s) should I attach to the
> connection using gdb or strace in order to actually get some useful data?
>
> i.e. I'll be using something like "strace -p [pid] [command]", what should I
> use for [command]?

strace -p pid
gdb -p pid

You don't need to specify the command at all.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Deadlock

От
"Milen A. Radev"
Дата:
Benjamin Krajmalnik написа:
> My aplogies - I forgot to set the subject of the problem I am having
> when I got lazy and used "reply".
[...]

Do not use "Reply" to start a new thread.

--
Milen A. Radev

Re: Deadlock

От
"Benjamin Krajmalnik"
Дата:
In case you did not read my message, it started with "My apologies"
becaue I made the mistake and noticed it.
Please, do not try to lecture me.  If you have something substanbtive to
reply concerning the issue I am having, then by all means do reply.  I
did not know you were the self-proclaimed list-server police.

Have a nice day


Re: stracing a connection

От
Peter Koczan
Дата:
Thank you Alvaro, it worked.

I got confused when I did it before since gdb couldn't figure out half
of the elements in the backtrace, and strace kept saying "I can't
attach", which was only because gdb was already attached to that same
process.

Peter

Alvaro Herrera wrote:
> Peter Koczan escribió:
>
>> Hi all,
>>
>> I'm running into a few problems with postgres connections, specifically
>> notify/listening connections. What program(s) should I attach to the
>> connection using gdb or strace in order to actually get some useful data?
>>
>> i.e. I'll be using something like "strace -p [pid] [command]", what should I
>> use for [command]?
>>
>
> strace -p pid
> gdb -p pid
>
> You don't need to specify the command at all.
>
>