Thread: current_query in pg_stat_activity
Hi,
The "current_query" column in the "pg_stat_activity" system
table seems to be limited to 255 characters. Is there a way
to increase that limit, in order to see queries completely
which are longer than 255 characters?
Thanks in advance!
Best regards
Oliver
(PS: Using PostgreSQL 7.3.4.)
--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.
"The scanf() function is a large and complex beast that often does
something almost but not quite entirely unlike what you desired."
-- Chris Torek
Oliver Fromme <olli@lurza.secnetix.de> writes:
> The "current_query" column in the "pg_stat_activity" system
> table seems to be limited to 255 characters. Is there a way
> to increase that limit, in order to see queries completely
> which are longer than 255 characters?
You could fool with PGSTAT_ACTIVITY_SIZE in pgstat.h, but you probably
need to keep it under 1000 --- note the comments associated with
PGSTAT_NUM_TABENTRIES.
regards, tom lane
Tom Lane wrote:
> Oliver Fromme <olli@lurza.secnetix.de> writes:
> > The "current_query" column in the "pg_stat_activity" system
> > table seems to be limited to 255 characters. Is there a way
> > to increase that limit, in order to see queries completely
> > which are longer than 255 characters?
>
> You could fool with PGSTAT_ACTIVITY_SIZE in pgstat.h, but you probably
> need to keep it under 1000 --- note the comments associated with
> PGSTAT_NUM_TABENTRIES.
Thanks! It appears that the size is ultimately limited
by the MTU of the localhost interface, which is 16K on
my platform (FreeBSD), so I should be able to increase
PGSTAT_ACTIVITY_SIZE to something like 15K, right?
I'll also have to adjust the numbers in the calculations
of PGSTAT_NUM_TABENTRIES and PGSTAT_NUM_TABPURGE from
1000 to (say) 16000, of course.
Oh, by the way, while I'm at it, I think I found a small
bug in the second of those formulas (PostgreSQL 7.3.5):
#define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \
/ sizeof(PgStat_TableEntry))
That should be sizeof(Oid), shouldn't it? The bug doesn't
hurt because a Oid is much smaller than a PgStat_TableEntry,
but it limits the number of them inside a PgStat_MsgTabpurge
much more than necessary.
(I haven't looked at 7.4.1; maybe it's already fixed.)
Best regards
Oliver
--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.
"Perl will consistently give you what you want,
unless what you want is consistency."
-- Larry Wall
Oliver Fromme <olli@lurza.secnetix.de> writes:
> Tom Lane wrote:
>>> Oliver Fromme <olli@lurza.secnetix.de> writes:
>>>> The "current_query" column in the "pg_stat_activity" system
>>>> table seems to be limited to 255 characters. Is there a way
>>>> to increase that limit, in order to see queries completely
>>>> which are longer than 255 characters?
>>>
>>> You could fool with PGSTAT_ACTIVITY_SIZE in pgstat.h, but you probably
>>> need to keep it under 1000 --- note the comments associated with
>>> PGSTAT_NUM_TABENTRIES.
> Thanks! It appears that the size is ultimately limited
> by the MTU of the localhost interface, which is 16K on
> my platform (FreeBSD), so I should be able to increase
> PGSTAT_ACTIVITY_SIZE to something like 15K, right?
You'll probably find the overhead of the stats collector ballooning
quite a lot if you increase the max message size by a factor of 15 :-(.
But give it a try.
> Oh, by the way, while I'm at it, I think I found a small
> bug in the second of those formulas (PostgreSQL 7.3.5):
> #define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \
> / sizeof(PgStat_TableEntry))
> That should be sizeof(Oid), shouldn't it? The bug doesn't
> hurt because a Oid is much smaller than a PgStat_TableEntry,
> but it limits the number of them inside a PgStat_MsgTabpurge
> much more than necessary.
I think you are right. Jan?
regards, tom lane
Tom Lane wrote: > Oliver Fromme <olli@lurza.secnetix.de> writes: >> Tom Lane wrote: >>>> Oliver Fromme <olli@lurza.secnetix.de> writes: >>>>> The "current_query" column in the "pg_stat_activity" system >>>>> table seems to be limited to 255 characters. Is there a way >>>>> to increase that limit, in order to see queries completely >>>>> which are longer than 255 characters? >>>> >>>> You could fool with PGSTAT_ACTIVITY_SIZE in pgstat.h, but you probably >>>> need to keep it under 1000 --- note the comments associated with >>>> PGSTAT_NUM_TABENTRIES. > >> Thanks! It appears that the size is ultimately limited >> by the MTU of the localhost interface, which is 16K on >> my platform (FreeBSD), so I should be able to increase >> PGSTAT_ACTIVITY_SIZE to something like 15K, right? > > You'll probably find the overhead of the stats collector ballooning > quite a lot if you increase the max message size by a factor of 15 :-(. > But give it a try. > >> Oh, by the way, while I'm at it, I think I found a small >> bug in the second of those formulas (PostgreSQL 7.3.5): > >> #define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \ >> / sizeof(PgStat_TableEntry)) > >> That should be sizeof(Oid), shouldn't it? The bug doesn't >> hurt because a Oid is much smaller than a PgStat_TableEntry, >> but it limits the number of them inside a PgStat_MsgTabpurge >> much more than necessary. > > I think you are right. Jan? Looks very much like, as the payload of the purge message is just an array of oids. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Attached patch applied. Thanks for the analysis. --------------------------------------------------------------------------- Jan Wieck wrote: > Tom Lane wrote: > > Oliver Fromme <olli@lurza.secnetix.de> writes: > >> Tom Lane wrote: > >>>> Oliver Fromme <olli@lurza.secnetix.de> writes: > >>>>> The "current_query" column in the "pg_stat_activity" system > >>>>> table seems to be limited to 255 characters. Is there a way > >>>>> to increase that limit, in order to see queries completely > >>>>> which are longer than 255 characters? > >>>> > >>>> You could fool with PGSTAT_ACTIVITY_SIZE in pgstat.h, but you probably > >>>> need to keep it under 1000 --- note the comments associated with > >>>> PGSTAT_NUM_TABENTRIES. > > > >> Thanks! It appears that the size is ultimately limited > >> by the MTU of the localhost interface, which is 16K on > >> my platform (FreeBSD), so I should be able to increase > >> PGSTAT_ACTIVITY_SIZE to something like 15K, right? > > > > You'll probably find the overhead of the stats collector ballooning > > quite a lot if you increase the max message size by a factor of 15 :-(. > > But give it a try. > > > >> Oh, by the way, while I'm at it, I think I found a small > >> bug in the second of those formulas (PostgreSQL 7.3.5): > > > >> #define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \ > >> / sizeof(PgStat_TableEntry)) > > > >> That should be sizeof(Oid), shouldn't it? The bug doesn't > >> hurt because a Oid is much smaller than a PgStat_TableEntry, > >> but it limits the number of them inside a PgStat_MsgTabpurge > >> much more than necessary. > > > > I think you are right. Jan? > > Looks very much like, as the payload of the purge message is just an > array of oids. > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: src/include/pgstat.h =================================================================== RCS file: /cvsroot/pgsql-server/src/include/pgstat.h,v retrieving revision 1.18 diff -c -c -r1.18 pgstat.h *** src/include/pgstat.h 25 Dec 2003 03:52:51 -0000 1.18 --- src/include/pgstat.h 2 Mar 2004 16:34:25 -0000 *************** *** 271,277 **** * ---------- */ #define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \ ! / sizeof(PgStat_TableEntry)) /* ---------- * PgStat_MsgTabpurge Sent by the backend to tell the collector --- 271,277 ---- * ---------- */ #define PGSTAT_NUM_TABPURGE ((1000 - sizeof(PgStat_MsgHdr)) \ ! / sizeof(Oid)) /* ---------- * PgStat_MsgTabpurge Sent by the backend to tell the collector