pg_dump and backslash escapes - Mailing list pgsql-hackers

pg_dump is designed to produce dumps that can be loaded into newer
PostgreSQL versions, and it can read from older database versions.

The new backslash escape warning and sql standard strings might cause
problems for people moving from older versions of PostgreSQL, because
pg_dump assumes identifiers and strings allow escape processing.  (COPY
is unaffected.)

We have a few possible solutions.  One would be to backpatch older
backends to understand E'', and use that in pg_dump.  Another solution
is to use SET to turn off backslash warnings and SQL standard strings,
and have the older backend servers either ignore 'off' commands for
these, or wrap them in a way that they are invisible to the user if they
fail.

I have chosen the last option;  patch attached.  It will output this at
the top of dump files for releases 7.3.X, 7.4.X and 8.0.X.  8.1 and CVS
HEAD are fine.  The new dump output lines are:

    -- Set escape environment for possible loading into version >= 8.2.
    -- If variables are not supported, suppress error messages.
    SET client_min_messages = panic;
    SET log_min_messages = log;
    SET log_min_error_statement = panic;
    SET escape_string_warning = off;
    SET standard_conforming_strings = off;
    RESET log_min_error_statement;
    RESET log_min_messages;
    RESET client_min_messages;

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.127
diff -c -c -r1.127 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c    19 Apr 2006 16:02:17 -0000    1.127
--- src/bin/pg_dump/pg_backup_archiver.c    13 May 2006 05:02:23 -0000
***************
*** 2017,2022 ****
--- 2017,2039 ----
      /* Make sure function checking is disabled */
      ahprintf(AH, "SET check_function_bodies = false;\n");

+     /*
+      *    We are using backslash escapes, so make sure they are enabled.
+      *    Older servers might not understand these variables, so we
+      *    turn off error reporting.
+      */
+     ahprintf(AH, "\n-- Set escape environment for possible loading into version >= 8.2.\n");
+     ahprintf(AH, "-- If variables are not supported, suppress error messages.\n");
+     ahprintf(AH, "SET client_min_messages = panic;\n");
+     /* In 7.3, this was server_min_messages */
+     ahprintf(AH, "SET log_min_messages = log;\n");
+     ahprintf(AH, "SET log_min_error_statement = panic;\n");
+     ahprintf(AH, "SET escape_string_warning = off;\n");
+     ahprintf(AH, "SET standard_conforming_strings = off;\n");
+     ahprintf(AH, "RESET log_min_error_statement;\n");
+     ahprintf(AH, "RESET log_min_messages;\n");
+     ahprintf(AH, "RESET client_min_messages;\n");
+
      /* Avoid annoying notices etc */
      ahprintf(AH, "SET client_min_messages = warning;\n");