Hi,
At the company I work for, we've been splitting dumps into separate
files and diffing them for a while now. By far the biggest problem we
had was with views: pg_dump by default dumps views on one line, in a
format which maximizes compatibility. Now this has several problems for
our use case:
1) The one-line equivalent of a 200-line view is completely impossible
to read.
2) If there's a difference between the two dumped view definitions,
it takes a long time to find where and what exactly it is.
3) For some reason some expressions are dumped differently depending
on how exactly they are written, cluttering the diff with false
positives.
While we can do the actual splitting of objects from a -Fc dump
relatively easily, we can't fix the view definitions after they've been
dumped. So I'm proposing a --pretty-print-views setting to pg_dump
(patch attached).
Any feedback is welcome.
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/ref/pg_dump.sgml
--- b/doc/src/sgml/ref/pg_dump.sgml
***************
*** 719,724 **** PostgreSQL documentation
--- 719,736 ----
</varlistentry>
<varlistentry>
+ <term><option>--pretty-print-views</></term>
+ <listitem>
+ <para>
+ Output views in a <quote>pretty-printed</quote> format. This makes
the
+ view definitions appearing in the dump easier to read for humans, but
+ at the same time makes them less likely to be interpreted in the exact
+ same way between different versions of <productname>PostgreSQL</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--quote-all-identifiers</></term>
<listitem>
<para>
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 137,142 **** static int dump_inserts = 0;
--- 137,143 ----
static int column_inserts = 0;
static int no_security_labels = 0;
static int no_unlogged_table_data = 0;
+ static int pretty_print_views = 0;
static int serializable_deferrable = 0;
***************
*** 345,350 **** main(int argc, char **argv)
--- 346,352 ----
{"inserts", no_argument, &dump_inserts, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
+ {"pretty-print-views", no_argument, &pretty_print_views, 1},
{"quote-all-identifiers", no_argument, "e_all_identifiers,
1},
{"role", required_argument, NULL, 3},
{"section", required_argument, NULL, 5},
***************
*** 606,611 **** main(int argc, char **argv)
--- 608,619 ----
no_security_labels = 1;
/*
+ * Pretty-printing views requires PostgreSQL 7.4 or later
+ */
+ if (pretty_print_views && fout->remoteVersion < 70400)
+ exit_horribly(NULL, "--pretty-print-views requires server
version 7.4 or later\n");
+
+ /*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
ExecuteSqlStatement(fout, "BEGIN");
***************
*** 840,845 **** help(const char *progname)
--- 848,856 ----
printf(_(" --no-security-labels do not dump security label
assignments\n"));
printf(_(" --no-tablespaces do not dump tablespace
assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table
data\n"));
+ printf(_(" --pretty-print-views\n"
+ " dump views in a more
human-readable format (reduces\n"
+ " compatibility of the
output dump)\n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if
not key words\n"));
printf(_(" --section=SECTION dump named section (pre-data,
data, or post-data)\n"));
printf(_(" --serializable-deferrable wait until the dump can run
without anomalies\n"));
***************
*** 12387,12393 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
reltypename = "VIEW";
/* Fetch the view definition */
! if (fout->remoteVersion >= 70300)
{
/* Beginning in 7.3, viewname is not unique; rely on
OID */
appendPQExpBuffer(query,
--- 12398,12411 ----
reltypename = "VIEW";
/* Fetch the view definition */
! if (pretty_print_views)
! {
! /* Server must be 7.4 or later, so viewname is not unique; rely
on OID */
! appendPQExpBuffer(query,
! "SELECT
pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid, TRUE) AS viewdef",
!
tbinfo->dobj.catId.oid);
! }
! else if (fout->remoteVersion >= 70300)
{
/* Beginning in 7.3, viewname is not unique; rely on
OID */
appendPQExpBuffer(query,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
