pg.ddx.io pgsql-patches@postgresql.org mailing list archive
help / color / mirror / Atom feedNew pg_dump patch, --no-stats flag, disables sending to statistics collector
9+ messages / 7 participants
[nested] [flat]
* New pg_dump patch, --no-stats flag, disables sending to statistics collector
@ 2009-01-20 21:04 Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 22:13 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Greg Smith <gsmith@gregsmith.com>
0 siblings, 2 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2009-01-20 21:04 UTC (permalink / raw)
To: ; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
This patch adds another flag to pg_dump, this time to disable statistics
collection. This is useful if your don't want pg_dump activity to show
(or clutter) your normal statistics. This may be appropriate for an
organization that regularly dumps a database for backup purposes, but
wants to analyze only the application's database use.
This is patched against CVS HEAD from today, though the code is quite
version independent. This patch is unsolicited, and as far as I know
has not been discussed on the list prior to now.
Comments?
Attachments:
pg_dump.patch (text/x-diff, 1.9K) download
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.514
diff -c -2 -r1.514 pg_dump.c
*** pg_dump.c 18 Jan 2009 20:44:45 -0000 1.514
--- pg_dump.c 20 Jan 2009 20:47:25 -0000
***************
*** 236,239 ****
--- 236,240 ----
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ static int noStatsCollection = 0;
static struct option long_options[] = {
***************
*** 278,281 ****
--- 279,283 ----
{"role", required_argument, NULL, 3},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ {"no-stats", no_argument, &noStatsCollection, 1},
{NULL, 0, NULL, 0}
***************
*** 430,433 ****
--- 432,437 ----
else if (strcmp(optarg, "no-tablespaces") == 0)
outputNoTablespaces = 1;
+ else if (strcmp(optarg, "no-stats") == 0)
+ noStatsCollection = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
use_setsessauth = 1;
***************
*** 613,616 ****
--- 617,629 ----
do_sql_command(g_conn, "SET statement_timeout = 0");
+ /*
+ * Disable collection of statistics. pg_dump's activity may be very different
+ * from what you are trying to analyze in the stats tables.
+ */
+ if( noStatsCollection ) {
+ do_sql_command(g_conn, "SET stats_block_level = false");
+ do_sql_command(g_conn, "SET stats_row_level = false");
+ }
+
/*
* Start serializable transaction to dump consistent data.
***************
*** 833,836 ****
--- 846,850 ----
printf(_(" -U, --username=NAME connect as specified database user\n"));
printf(_(" -W, --password force password prompt (should happen automatically)\n"));
+ printf(_(" --no-stats disable statistics collection (superuser only)\n"));
printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
@ 2009-01-20 21:09 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 9+ messages in thread
From: Jaime Casanova @ 2009-01-20 21:09 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
On Tue, Jan 20, 2009 at 4:04 PM, Bryce Nesbitt <bryce2@obviously.com> wrote:
> This patch adds another flag to pg_dump, this time to disable statistics
> collection. This is useful if your don't want pg_dump activity to show (or
> clutter) your normal statistics. This may be appropriate for an
> organization that regularly dumps a database for backup purposes, but wants
> to analyze only the application's database use.
>
i haven't looked at the patch nor it's functional use... but from the
top of my head jumps a question: is there a reason to not make this
the default behaviour?
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
AsesorÃa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2009-01-20 21:54 ` Bruce Momjian <bruce@momjian.us>
2009-01-20 22:15 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 22:40 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2009-01-20 21:54 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; +Cc: Bryce Nesbitt <bryce2@obviously.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
Jaime Casanova wrote:
> On Tue, Jan 20, 2009 at 4:04 PM, Bryce Nesbitt <bryce2@obviously.com> wrote:
> > This patch adds another flag to pg_dump, this time to disable statistics
> > collection. This is useful if your don't want pg_dump activity to show (or
> > clutter) your normal statistics. This may be appropriate for an
> > organization that regularly dumps a database for backup purposes, but wants
> > to analyze only the application's database use.
> >
>
> i haven't looked at the patch nor it's functional use... but from the
> top of my head jumps a question: is there a reason to not make this
> the default behaviour?
If this is a generally desired feature (and I question that), I think we
need a more general solution.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
@ 2009-01-20 22:15 ` Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 0 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2009-01-20 22:15 UTC (permalink / raw)
To: Pgsql Hackers <pgsql-hackers@postgresql.org>; +Cc: Bruce Momjian <bruce@momjian.us>; Jaime Casanova <jcasanov@systemguards.com.ec>
Bruce Momjian wrote:
> Jaime Casanova wrote:
>
>> i haven't looked at the patch nor it's functional use... but from the
>> top of my head jumps a question: is there a reason to not make this
>> the default behaviour?
>>
> If this is a generally desired feature (and I question that), I think we
> need a more general solution.
>
I'm not a big fan of flags, preferring good defaults. But I was not
bold enough to suggest this as a new default, as someone would probably
want the opposite flag. If you're measuring total server load (rather
than analyzing an application), you may want to see pg_dump activity.
As for a "general" solution: one could add the ability to inject
arbitrary sql just prior to a dump run. That would let someone roll
their own by injecting "SET stats_block_level = false", or make any
other arbitrary settings changes.
Or one might slice the statistics collector by role or user (so your
'backup' role would keep a separate tally).
On the other hand, the flag's advantage is simplicity and directness.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
@ 2009-01-20 22:40 ` Josh Berkus <josh@agliodbs.com>
2009-01-20 23:37 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Tom Lane <tgl@sss.pgh.pa.us>
2009-01-21 00:35 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2009-01-20 22:40 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; Bryce Nesbitt <bryce2@obviously.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
Bruce,
> If this is a generally desired feature (and I question that), I think we
> need a more general solution.
I'd argue that it is generally desired (or some convenient workaround)
but not urgently so. I'd also argue that if we're going to have a
--no-stats flag, it should exist for the other client ultilites as well;
if I don't want pg_dump showing up, I probably don't want Vacuum showing
up, or various other already-debugged maintenance routines.
I'd suggest putting this into the first patch review for 8.5.
--Josh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
2009-01-20 22:40 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Josh Berkus <josh@agliodbs.com>
@ 2009-01-20 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-01-20 23:40 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2009-01-20 23:37 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Jaime Casanova <jcasanov@systemguards.com.ec>; Bryce Nesbitt <bryce2@obviously.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
Josh Berkus <josh@agliodbs.com> writes:
> Bruce,
>> If this is a generally desired feature (and I question that), I think we
>> need a more general solution.
> I'd argue that it is generally desired (or some convenient workaround)
> but not urgently so.
One person asking for it does not make it "generally desired". I think
that the use-case is more than adequately served already by using
PGOPTIONS, or by running pg_dump under a user id that has the
appropriate GUC settings applied via ALTER USER.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
2009-01-20 22:40 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Josh Berkus <josh@agliodbs.com>
2009-01-20 23:37 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-01-20 23:40 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Joshua D. Drake @ 2009-01-20 23:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Bruce Momjian <bruce@momjian.us>; Jaime Casanova <jcasanov@systemguards.com.ec>; Bryce Nesbitt <bryce2@obviously.com>; Pgsql Hackers <pgsql-hackers@postgresql.org>
On Tue, 2009-01-20 at 18:37 -0500, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Bruce,
> >> If this is a generally desired feature (and I question that), I think we
> >> need a more general solution.
>
> > I'd argue that it is generally desired (or some convenient workaround)
> > but not urgently so.
>
> One person asking for it does not make it "generally desired". I think
> that the use-case is more than adequately served already by using
> PGOPTIONS, or by running pg_dump under a user id that has the
> appropriate GUC settings applied via ALTER USER.
+1
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Bruce Momjian <bruce@momjian.us>
2009-01-20 22:40 ` Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector Josh Berkus <josh@agliodbs.com>
@ 2009-01-21 00:35 ` Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 0 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2009-01-21 00:35 UTC (permalink / raw)
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Josh Berkus wrote:
> I'd argue that it is generally desired (or some convenient workaround)
> but not urgently so. I'd also argue that if we're going to have a
> --no-stats flag, it should exist for the other client ultilites as
> well; if I don't want pg_dump showing up, I probably don't want Vacuum
> showing up, or various other already-debugged maintenance routines.
>
> I'd suggest putting this into the first patch review for 8.5.
>
> --Josh
As pg_dumpall calls pg_dump, I think this is covered or at least
coverable. For vaccum, I've never seen that activity in stats? Can you
supply a more specific scenario where routine maintenance is harmfully
cluttering the stats table? A specific utility that needs attention?
For this feature I'm not so sure about "generally desired" -- I'll bet
most people don't even think about this. The question is among those
who DO think about it, what's the best behavior? Can it be argued that
excluding pg_dump is "generally desirable", for the average use case?
If there is not enough demand for a dedicated flag, I may submit a man
page patch documenting the Do-It-Yourself solution proposed by Greg
Smith, or the one proposed by Tom Lane.
G'day
-Bryce
PS: Note that no respondent on the psql user's lists thought excluding
pg_dump was even possible -- so that at least argues for desirability of
instructional material :-).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New pg_dump patch, --no-stats flag, disables sending to statistics collector
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
@ 2009-01-20 22:13 ` Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 9+ messages in thread
From: Greg Smith @ 2009-01-20 22:13 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
On Tue, 20 Jan 2009, Bryce Nesbitt wrote:
> This patch adds another flag to pg_dump, this time to disable statistics
> collection.
You can pass session parameters to anything that uses the standard libpq
library using PGOPTIONS. See
http://www.postgresql.org/docs/8.3/static/config-setting.html for a
sample. I suspect that something like:
PGOPTIONS='-c stats_block_level=false -c stats_row_level=false' pg_dump
would do the same thing as your patch without having to touch the code.
That's a pretty obscure bit of information though, and it would be
worthwhile to update the documentation suggesting such a syntax because I
think this would be handy for a lot of people. I was already planning to
do that for another use case (pgbench) once the 8.4 work here shifts from
development to testing and I have some more time for writing.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2009-01-21 00:35 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-01-20 21:04 New pg_dump patch, --no-stats flag, disables sending to statistics collector Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 21:09 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2009-01-20 21:54 ` Bruce Momjian <bruce@momjian.us>
2009-01-20 22:15 ` Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 22:40 ` Josh Berkus <josh@agliodbs.com>
2009-01-20 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-01-20 23:40 ` Joshua D. Drake <jd@commandprompt.com>
2009-01-21 00:35 ` Bryce Nesbitt <bryce2@obviously.com>
2009-01-20 22:13 ` Greg Smith <gsmith@gregsmith.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