Re: bytea vs. pg_dump - Mailing list pgsql-hackers

I wrote:
> While I've been poking at the pg_dump issues, it's occurred to me that
> changing the default would be a great forcing function for finding out
> any lurking problems.  What I'm inclined to do now is to commit it
> *with* the change of default, and let it be that way at least for a
> few alpha-test releases.  We can vote on whether to switch the default
> back before 8.5 final.

For the archives, attached is a patch to switch the default and then
make pg_dump force use of hex mode.  This is just so we won't forget
what needs changing if we decide to switch the default back ...

            regards, tom lane

*** doc/src/sgml/config.sgml.orig    Tue Aug  4 12:08:35 2009
--- doc/src/sgml/config.sgml    Tue Aug  4 12:40:34 2009
***************
*** 4068,4078 ****
        <listitem>
         <para>
          Sets the output format for values of type <type>bytea</type>.
!         Valid values are <literal>hex</literal> (the default)
          and <literal>escape</literal> (the traditional PostgreSQL
          format).  See <xref linkend="datatype-binary"> for more
          information.  The <type>bytea</type> type always
          accepts both formats on input, regardless of this setting.
         </para>
        </listitem>
       </varlistentry>
--- 4068,4079 ----
        <listitem>
         <para>
          Sets the output format for values of type <type>bytea</type>.
!         Valid values are <literal>hex</literal>
          and <literal>escape</literal> (the traditional PostgreSQL
          format).  See <xref linkend="datatype-binary"> for more
          information.  The <type>bytea</type> type always
          accepts both formats on input, regardless of this setting.
+         The default is <literal>escape</literal>.
         </para>
        </listitem>
       </varlistentry>
*** doc/src/sgml/datatype.sgml.orig    Tue Aug  4 12:08:35 2009
--- doc/src/sgml/datatype.sgml    Tue Aug  4 12:41:03 2009
***************
*** 1196,1202 ****
      <quote>escape</quote> format, and <quote>hex</quote> format.  Both
      of these are always accepted on input.  The output format depends
      on the configuration parameter <xref linkend="guc-bytea-output">;
!     the default is hex.  (Note that the hex format was introduced in
      <productname>PostgreSQL</productname> 8.5; earlier versions and some
      tools don't understand it.)
     </para>
--- 1196,1202 ----
      <quote>escape</quote> format, and <quote>hex</quote> format.  Both
      of these are always accepted on input.  The output format depends
      on the configuration parameter <xref linkend="guc-bytea-output">;
!     the default is escape.  (Note that the hex format was introduced in
      <productname>PostgreSQL</productname> 8.5; earlier versions and some
      tools don't understand it.)
     </para>
*** src/backend/utils/adt/varlena.c.orig    Tue Aug  4 12:08:36 2009
--- src/backend/utils/adt/varlena.c    Tue Aug  4 12:42:36 2009
***************
*** 30,36 ****


  /* GUC variable */
! int        bytea_output = BYTEA_OUTPUT_HEX;

  typedef struct varlena unknown;

--- 30,36 ----


  /* GUC variable */
! int        bytea_output = BYTEA_OUTPUT_ESCAPE;

  typedef struct varlena unknown;

*** src/backend/utils/misc/guc.c.orig    Tue Aug  4 12:08:36 2009
--- src/backend/utils/misc/guc.c    Tue Aug  4 12:42:04 2009
***************
*** 2553,2559 ****
              NULL
          },
          &bytea_output,
!         BYTEA_OUTPUT_HEX, bytea_output_options, NULL, NULL
      },

      {
--- 2553,2559 ----
              NULL
          },
          &bytea_output,
!         BYTEA_OUTPUT_ESCAPE, bytea_output_options, NULL, NULL
      },

      {
*** src/backend/utils/misc/postgresql.conf.sample.orig    Mon Aug  3 15:59:39 2009
--- src/backend/utils/misc/postgresql.conf.sample    Tue Aug  4 12:42:15 2009
***************
*** 424,430 ****
  #statement_timeout = 0            # in milliseconds, 0 is disabled
  #vacuum_freeze_min_age = 50000000
  #vacuum_freeze_table_age = 150000000
! #bytea_output = 'hex'            # hex, escape
  #xmlbinary = 'base64'
  #xmloption = 'content'

--- 424,430 ----
  #statement_timeout = 0            # in milliseconds, 0 is disabled
  #vacuum_freeze_min_age = 50000000
  #vacuum_freeze_table_age = 150000000
! #bytea_output = 'escape'        # hex, escape
  #xmlbinary = 'base64'
  #xmloption = 'content'

*** src/bin/pg_dump/pg_dump.c.orig    Tue Aug  4 12:08:36 2009
--- src/bin/pg_dump/pg_dump.c    Tue Aug  4 12:39:39 2009
***************
*** 599,604 ****
--- 599,610 ----
          do_sql_command(g_conn, "SET extra_float_digits TO 2");

      /*
+      * If supported, select hex format for bytea, for speed reasons.
+      */
+     if (g_fout->remoteVersion >= 80500)
+         do_sql_command(g_conn, "SET bytea_output TO hex");
+
+     /*
       * If synchronized scanning is supported, disable it, to prevent
       * unpredictable changes in row ordering across a dump and reload.
       */