pg_dump pretty_print
Atached is a patch to allow pretty printing of system objects
(constraints, indexes, rules, and views) when doing a pg_dump via a
--pretty-print flag along with a warning in the docs to be careful about
doing so :)
--
Greg Sabino Mullane greg@turnstep.com greg@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8 200701191958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Attachments:
pretty_print.pg_dump.patchtext/x-patch; charset=us-ascii; name=pretty_print.pg_dump.patchDownload
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.92
diff -c -r1.92 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 28 Nov 2006 22:54:18 -0000 1.92
--- doc/src/sgml/ref/pg_dump.sgml 20 Jan 2007 00:51:43 -0000
***************
*** 546,551 ****
--- 546,563 ----
</varlistentry>
<varlistentry>
+ <term><option>--pretty-print</></term>
+ <listitem>
+ <para>
+ This option pretty-prints all constraint, index, rule, and view
+ definitions. While this result is more readable, it is less
+ likely to be interpreted the same way by future versions of
+ <productname>PostgreSQL</> and should be used carefully.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.59
diff -c -r1.59 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml 15 Jan 2007 17:22:46 -0000 1.59
--- doc/src/sgml/ref/pg_dumpall.sgml 20 Jan 2007 00:51:43 -0000
***************
*** 241,246 ****
--- 241,258 ----
</varlistentry>
<varlistentry>
+ <term><option>--pretty-print</></term>
+ <listitem>
+ <para>
+ This option pretty-prints all constraint, index, rule, and view
+ definitions. While this result is more readable, it is less
+ likely to be interpreted the same way by future versions of
+ <productname>PostgreSQL</> and should be used carefully.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.456
diff -c -r1.456 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 5 Jan 2007 22:19:48 -0000 1.456
--- src/bin/pg_dump/pg_dump.c 20 Jan 2007 00:51:43 -0000
***************
*** 119,124 ****
--- 119,126 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* flag to turn on/off pretty printing of views and rules */
+ static int pretty_print = 0;
static void help(const char *progname);
static void expand_schema_name_patterns(SimpleStringList *patterns,
***************
*** 260,265 ****
--- 262,268 ----
*/
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
+ {"pretty-print", no_argument, &pretty_print, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{NULL, 0, NULL, 0}
***************
*** 416,421 ****
--- 419,426 ----
disable_dollar_quoting = 1;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
+ else if (strcmp(optarg, "pretty-print") == 0)
+ pretty_print = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
use_setsessauth = 1;
else
***************
*** 759,764 ****
--- 764,770 ----
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
+ printf(_(" --pretty-print print rules and views in a more readable format\n"));
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
***************
*** 3194,3200 ****
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
--- 3200,3206 ----
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid,0,%s) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
***************
*** 3213,3218 ****
--- 3219,3225 ----
"AND d.refobjid = c.oid) "
"WHERE i.indrelid = '%u'::pg_catalog.oid "
"ORDER BY indexname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 80000)
***************
*** 3220,3226 ****
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
--- 3227,3233 ----
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid,0,%s) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
***************
*** 3239,3244 ****
--- 3246,3252 ----
"AND d.refobjid = c.oid) "
"WHERE i.indrelid = '%u'::pg_catalog.oid "
"ORDER BY indexname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
***************
*** 3456,3465 ****
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) as condef "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
"AND contype = 'f'",
tbinfo->dobj.catId.oid);
res = PQexec(g_conn, query->data);
check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
--- 3464,3474 ----
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid%s) as condef "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
"AND contype = 'f'",
+ (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "",
tbinfo->dobj.catId.oid);
res = PQexec(g_conn, query->data);
check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
***************
*** 3528,3537 ****
if (g_fout->remoteVersion >= 70400)
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE contypid = '%u'::pg_catalog.oid "
"ORDER BY conname",
tinfo->dobj.catId.oid);
else
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
--- 3537,3547 ----
if (g_fout->remoteVersion >= 70400)
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid,%s) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE contypid = '%u'::pg_catalog.oid "
"ORDER BY conname",
+ pretty_print ? "true" : "false",
tinfo->dobj.catId.oid);
else
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
***************
*** 4407,4417 ****
if (g_fout->remoteVersion >= 70400)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
" AND contype = 'c' "
"ORDER BY conname",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
--- 4417,4428 ----
if (g_fout->remoteVersion >= 70400)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid,%s) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
" AND contype = 'c' "
"ORDER BY conname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
***************
*** 7332,7339 ****
{
/* Beginning in 7.3, viewname is not unique; rely on OID */
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
! tbinfo->dobj.catId.oid);
}
else
{
--- 7343,7351 ----
{
/* Beginning in 7.3, viewname is not unique; rely on OID */
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid%s) as viewdef",
! tbinfo->dobj.catId.oid,
! (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "");
}
else
{
***************
*** 8448,8455 ****
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
! rinfo->dobj.catId.oid);
}
else
{
--- 8460,8468 ----
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid%s) AS definition",
! rinfo->dobj.catId.oid,
! (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "");
}
else
{
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.86
diff -c -r1.86 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 5 Jan 2007 22:19:48 -0000 1.86
--- src/bin/pg_dump/pg_dumpall.c 20 Jan 2007 00:51:43 -0000
***************
*** 65,70 ****
--- 65,71 ----
static int disable_dollar_quoting = 0;
static int disable_triggers = 0;
+ static int pretty_print = 0;
static int use_setsessauth = 0;
static int server_version;
***************
*** 110,115 ****
--- 111,117 ----
*/
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
+ {"pretty-print", no_argument, &pretty_print, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{NULL, 0, NULL, 0}
***************
*** 258,263 ****
--- 260,267 ----
appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
else if (strcmp(optarg, "disable-triggers") == 0)
appendPQExpBuffer(pgdumpopts, " --disable-triggers");
+ else if (strcmp(optarg, "pretty-print") == 0)
+ pretty_print = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
/* no-op, still allowed for compatibility */ ;
else
***************
*** 284,289 ****
--- 288,295 ----
appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
if (disable_triggers)
appendPQExpBuffer(pgdumpopts, " --disable-triggers");
+ if (pretty_print)
+ appendPQExpBuffer(pgdumpopts, " --pretty-print");
if (use_setsessauth)
appendPQExpBuffer(pgdumpopts, " --use-set-session-authorization");
***************
*** 390,395 ****
--- 396,402 ----
printf(_(" --disable-dollar-quoting\n"
" disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
+ printf(_(" --pretty-print print rules and views in a more readable format\n"));
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
Greg Sabino Mullane <greg@turnstep.com> writes:
Atached is a patch to allow pretty printing of system objects
(constraints, indexes, rules, and views) when doing a pg_dump via a
--pretty-print flag along with a warning in the docs to be careful about
doing so :)
Why exactly is that a good idea? Seems like a foot-gun with marginal
usefulness. If you want to look at things "pretty", the psql \d
commands are the tool to use.
regards, tom lane
Greg Sabino Mullane wrote:
Atached is a patch to allow pretty printing of system objects
(constraints, indexes, rules, and views) when doing a pg_dump via a
--pretty-print flag along with a warning in the docs to be careful
about doing so :)
Could you provide a less hand-waving specification of this feature? At
the moment it sounds like "This option does something fun, but we're
not sure what it is."
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Peter Eisentraut asked:
Could you provide a less hand-waving specification of this feature? At
the moment it sounds like "This option does something fun, but we're
not sure what it is.
Sure. Perhaps after I manage to convince Tom of its usefullness. :)
I can expand on what "pretty" is and add a warning akin to the
one for --tables.
Tom Lane asked:
Why exactly is that a good idea? Seems like a foot-gun with marginal
usefulness. If you want to look at things "pretty", the psql \d
commands are the tool to use.
Because sometimes all you have is access to the dump file, or because you
want to save/view a historical record; in both cases it is very hard
to actually read the input when it's crammed into a single line with minimal
whitespacing. Since we already do make some concessions as far as making
the dump file human-readable, offering this as an *optional* flag seems
(mostly) harmless.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200701221450
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFtRV1vJuQZxSWSsgRAm4iAJ9lro5hvpbYHQB6fM06uH7wUHzLlwCgpamm
wUpTmpqn4JbcEGsHmM1OTPQ=
=HrpY
-----END PGP SIGNATURE-----
Am Montag, 22. Januar 2007 20:53 schrieb Greg Sabino Mullane:
Since we already do make some concessions as far as
making the dump file human-readable, offering this as an *optional* flag
seems (mostly) harmless.
The harm here is that under undefined circumstances a dump file will not be a
proper and robust representation of the original database, which would add
significant confusion and potential for error.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Peter Eisentraut replied:
The harm here is that under undefined circumstances a dump file
will not be a proper and robust representation of the original
database, which would add significant confusion and potential for error.
What "undefined circumstances" are we talking here? If there is a chance
that pg_get_viewdef and company do not output a version that can be
read again by the database because we simply changed the whitespace, that
sounds like a serious bug to be fixed, not a reason to reject this
optional flag.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200701251003
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFuXd2vJuQZxSWSsgRA9VDAJ9S1b+4DJomO3Bmij4wvida9wtgfgCeID16
qeoNrrehtTGIeJeL8T+mx9M=
=VecV
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
Peter Eisentraut replied:
The harm here is that under undefined circumstances a dump file
will not be a proper and robust representation of the original
database, which would add significant confusion and potential for error.
What "undefined circumstances" are we talking here? If there is a chance
that pg_get_viewdef and company do not output a version that can be
read again by the database because we simply changed the whitespace, that
sounds like a serious bug to be fixed, not a reason to reject this
optional flag.
The original definition of the prettyprint flag was that it'd produce a
version that was nice to look at but not guaranteed to parse back
exactly the same; in particular it might omit parentheses that perhaps
were really needed to ensure the same parsing. (I think there might be
some other issues too ... but whitespace is NOT one of them.) It's
possible that the current prettyprint code is smart enough to never make
such an error --- and then again it's possible that it isn't. Like
Peter, I've not got much confidence in that code, and don't want to
trust pg_dump's correctness to it.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Tom Lane wrote:
The original definition of the prettyprint flag was that it'd produce a
version that was nice to look at but not guaranteed to parse back
exactly the same; in particular it might omit parentheses that perhaps
were really needed to ensure the same parsing. (I think there might be
some other issues too ... but whitespace is NOT one of them.) It's
possible that the current prettyprint code is smart enough to never make
such an error --- and then again it's possible that it isn't. Like
Peter, I've not got much confidence in that code, and don't want to
trust pg_dump's correctness to it.
Can we perhaps add to the TODO to get the pretty print functions audited
and tested out? I'm sure people are already using the pretty print option
today via psql so it seems like this should be a high priority. Plus of
course I'd like to see it added to pg_dump once Peter, yourself, and
others have more confidence in it working as one would expect.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200701301509
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFv6YcvJuQZxSWSsgRA1ujAKDqfH1lAUcba0ce8wBjN/PIRzfNxACgnVWf
XnusK0UcywWnaBDF6KE/x4E=
=WoFo
-----END PGP SIGNATURE-----