pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstracing a connection
4+ messages / 3 participants
[nested] [flat]
* stracing a connection
@ 2007-08-01 16:50 Peter Koczan <pjkoczan@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Peter Koczan @ 2007-08-01 16:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stracing a connection
@ 2007-08-01 17:31 Benjamin Krajmalnik <kraj@illumen.com>
parent: Peter Koczan <pjkoczan@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Benjamin Krajmalnik @ 2007-08-01 17:31 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stracing a connection
@ 2007-08-01 18:05 Alvaro Herrera <alvherre@commandprompt.com>
parent: Peter Koczan <pjkoczan@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2007-08-01 18:05 UTC (permalink / raw)
To: Peter Koczan <pjkoczan@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: stracing a connection
@ 2007-08-02 00:34 Peter Koczan <pjkoczan@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Koczan @ 2007-08-02 00:34 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-02 00:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-01 16:50 stracing a connection Peter Koczan <pjkoczan@gmail.com>
2007-08-01 17:31 ` Benjamin Krajmalnik <kraj@illumen.com>
2007-08-01 18:05 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-02 00:34 ` Peter Koczan <pjkoczan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox