Re: Cannot dump/restore text value \N - Mailing list pgsql-hackers

On Sun, 05 Oct 2003 19:12:50 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>it seems we have to compare the null representation string to the
>pre-debackslashing input.

Here is a patch that does this and adds a few regression tests.

>(This is probably fairly easy to make happen
>in CVS tip, but it might be pretty painful in 7.3.)

There haven't been too much changes in this area between 7.3 and 7.4.
A patch against 7.3.4 will follow ...

Servus
 Manfred
diff -ruN ../base/src/backend/commands/copy.c src/backend/commands/copy.c
--- ../base/src/backend/commands/copy.c    2003-08-28 15:52:34.000000000 +0200
+++ src/backend/commands/copy.c    2003-10-08 10:43:02.000000000 +0200
@@ -90,7 +90,8 @@
        char *delim, char *null_print);
 static void CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
          char *delim, char *null_print);
-static char *CopyReadAttribute(const char *delim, CopyReadResult *result);
+static char *CopyReadAttribute(const char *delim, const char *nullst,
+                               CopyReadResult *result, bool *isnull);
 static Datum CopyReadBinaryAttribute(int column_no, FmgrInfo *flinfo,
                         Oid typelem, bool *isnull);
 static void CopyAttributeOut(char *string, char *delim);
@@ -1361,7 +1362,7 @@

             if (file_has_oids)
             {
-                string = CopyReadAttribute(delim, &result);
+                string = CopyReadAttribute(delim, null_print, &result, &isnull);

                 if (result == END_OF_FILE && *string == '\0')
                 {
@@ -1370,7 +1371,7 @@
                     break;
                 }

-                if (strcmp(string, null_print) == 0)
+                if (isnull)
                     ereport(ERROR,
                             (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
                              errmsg("null OID in COPY data")));
@@ -1403,7 +1404,7 @@
                              errmsg("missing data for column \"%s\"",
                                     NameStr(attr[m]->attname))));

-                string = CopyReadAttribute(delim, &result);
+                string = CopyReadAttribute(delim, null_print, &result, &isnull);

                 if (result == END_OF_FILE && *string == '\0' &&
                     cur == attnumlist && !file_has_oids)
@@ -1413,7 +1414,7 @@
                     break;        /* out of per-attr loop */
                 }

-                if (strcmp(string, null_print) == 0)
+                if (isnull)
                 {
                     /* we read an SQL NULL, no need to do anything */
                 }
@@ -1442,7 +1443,7 @@
             {
                 if (attnumlist == NIL && !file_has_oids)
                 {
-                    string = CopyReadAttribute(delim, &result);
+                    string = CopyReadAttribute(delim, null_print, &result, &isnull);
                     if (result == NORMAL_ATTR || *string != '\0')
                         ereport(ERROR,
                                 (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
@@ -1650,14 +1651,13 @@
  *        END_OF_FILE:    EOF indicator
  * In all cases, the string read up to the terminator is returned.
  *
- * Note: This function does not care about SQL NULL values -- it
- * is the caller's responsibility to check if the returned string
- * matches what the user specified for the SQL NULL value.
- *
  * delim is the column delimiter string.
+ * nullst says how NULL values are represented.
+ * *isnull is set true if a null attribute, else false.
  */
 static char *
-CopyReadAttribute(const char *delim, CopyReadResult *result)
+CopyReadAttribute(const char *delim, const char *nullst,
+                  CopyReadResult *result, bool *isnull)
 {
     int            c;
     int            delimc = (unsigned char) delim[0];
@@ -1665,6 +1665,17 @@
     unsigned char s[2];
     char       *cvt;
     int            j;
+    bool        matchnull = true;
+    int            matchlen = 0;
+
+#define CHECK_MATCH(c) \
+    do { \
+        if (matchnull) \
+            if (c == nullst[matchlen]) \
+                ++matchlen; \
+            else \
+                matchnull = false; \
+    } while (0)

     s[1] = 0;

@@ -1733,6 +1744,7 @@
         }
         if (c == delimc)
             break;
+        CHECK_MATCH(c);
         if (c == '\\')
         {
             c = CopyGetChar();
@@ -1741,6 +1753,7 @@
                 *result = END_OF_FILE;
                 goto copy_eof;
             }
+            CHECK_MATCH(c);
             switch (c)
             {
                 case '0':
@@ -1760,11 +1773,13 @@
                         {
                             val = (val << 3) + OCTVALUE(c);
                             CopyDonePeek(c, true /* pick up */ );
+                            CHECK_MATCH(c);
                             c = CopyPeekChar();
                             if (ISOCTAL(c))
                             {
                                 val = (val << 3) + OCTVALUE(c);
                                 CopyDonePeek(c, true /* pick up */ );
+                                CHECK_MATCH(c);
                             }
                             else
                             {
@@ -1789,15 +1804,6 @@
                     }
                     break;

-                    /*
-                     * This is a special hack to parse `\N' as
-                     * <backslash-N> rather then just 'N' to provide
-                     * compatibility with the default NULL output. -- pe
-                     */
-                case 'N':
-                    appendStringInfoCharMacro(&attribute_buf, '\\');
-                    c = 'N';
-                    break;
                 case 'b':
                     c = '\b';
                     break;
@@ -1871,6 +1877,7 @@
                     *result = END_OF_FILE;
                     goto copy_eof;
                 }
+                CHECK_MATCH(c);
                 appendStringInfoCharMacro(&attribute_buf, c);
             }
         }
@@ -1878,6 +1885,8 @@

 copy_eof:

+    *isnull = (matchnull && (nullst[matchlen] == '\0'));
+
     if (client_encoding != server_encoding)
     {
         cvt = (char *) pg_client_to_server((unsigned char *) attribute_buf.data,
diff -ruN ../base/src/test/regress/expected/copy2.out src/test/regress/expected/copy2.out
--- ../base/src/test/regress/expected/copy2.out    2003-07-27 06:53:11.000000000 +0200
+++ src/test/regress/expected/copy2.out    2003-10-07 21:12:16.000000000 +0200
@@ -2,7 +2,7 @@
     a serial,
     b int,
     c text not null default 'stuff',
-    d text not null,
+    d text,
     e text
 );
 NOTICE:  CREATE TABLE will create implicit sequence "x_a_seq" for SERIAL column "x.a"
@@ -48,23 +48,38 @@
 CONTEXT:  COPY FROM, line 1
 -- various COPY options: delimiters, oids, NULL string
 COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
+COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
+COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
 -- check results of copy in
 SELECT * FROM x;
-   a   | b  |   c   |   d    |          e
--------+----+-------+--------+----------------------
- 10000 | 21 | 31    | 41     | before trigger fired
- 10001 | 22 | 32    | 42     | before trigger fired
- 10002 | 23 | 33    | 43     | before trigger fired
- 10003 | 24 | 34    | 44     | before trigger fired
- 10004 | 25 | 35    | 45     | before trigger fired
- 10005 | 26 | 36    | 46     | before trigger fired
-     6 |    | 45    | 80     | before trigger fired
-     1 |  1 | stuff | test_1 | after trigger fired
-     2 |  2 | stuff | test_2 | after trigger fired
-     3 |  3 | stuff | test_3 | after trigger fired
-     4 |  4 | stuff | test_4 | after trigger fired
-     5 |  5 | stuff | test_5 | after trigger fired
-(12 rows)
+   a   | b  |     c      |   d    |          e
+-------+----+------------+--------+----------------------
+  9999 |    | \N         | NN     | before trigger fired
+ 10000 | 21 | 31         | 41     | before trigger fired
+ 10001 | 22 | 32         | 42     | before trigger fired
+ 10002 | 23 | 33         | 43     | before trigger fired
+ 10003 | 24 | 34         | 44     | before trigger fired
+ 10004 | 25 | 35         | 45     | before trigger fired
+ 10005 | 26 | 36         | 46     | before trigger fired
+     6 |    | 45         | 80     | before trigger fired
+     7 |    | x          | \x     | before trigger fired
+     8 |    | ,          | \,     | before trigger fired
+  3000 |    | c          |        | before trigger fired
+  4000 |    | C          |        | before trigger fired
+  4001 |  1 | empty      |        | before trigger fired
+  4002 |  2 | null       |        | before trigger fired
+  4003 |  3 | Backslash  | \      | before trigger fired
+  4004 |  4 | BackslashX | \X     | before trigger fired
+  4005 |  5 | N          | N      | before trigger fired
+  4006 |  6 | BackslashN | \N     | before trigger fired
+  4007 |  7 | XX         | XX     | before trigger fired
+  4008 |  8 | Delimiter  | :      | before trigger fired
+     1 |  1 | stuff      | test_1 | after trigger fired
+     2 |  2 | stuff      | test_2 | after trigger fired
+     3 |  3 | stuff      | test_3 | after trigger fired
+     4 |  4 | stuff      | test_4 | after trigger fired
+     5 |  5 | stuff      | test_5 | after trigger fired
+(25 rows)

 -- COPY w/ oids on a table w/o oids should fail
 CREATE TABLE no_oids (
@@ -80,6 +95,7 @@
 ERROR:  table "no_oids" does not have OIDs
 -- check copy out
 COPY x TO stdout;
+9999    \N    \\N    NN    before trigger fired
 10000    21    31    41    before trigger fired
 10001    22    32    42    before trigger fired
 10002    23    33    43    before trigger fired
@@ -87,12 +103,25 @@
 10004    25    35    45    before trigger fired
 10005    26    36    46    before trigger fired
 6    \N    45    80    before trigger fired
+7    \N    x    \\x    before trigger fired
+8    \N    ,    \\,    before trigger fired
+3000    \N    c    \N    before trigger fired
+4000    \N    C    \N    before trigger fired
+4001    1    empty        before trigger fired
+4002    2    null    \N    before trigger fired
+4003    3    Backslash    \\    before trigger fired
+4004    4    BackslashX    \\X    before trigger fired
+4005    5    N    N    before trigger fired
+4006    6    BackslashN    \\N    before trigger fired
+4007    7    XX    XX    before trigger fired
+4008    8    Delimiter    :    before trigger fired
 1    1    stuff    test_1    after trigger fired
 2    2    stuff    test_2    after trigger fired
 3    3    stuff    test_3    after trigger fired
 4    4    stuff    test_4    after trigger fired
 5    5    stuff    test_5    after trigger fired
 COPY x (c, e) TO stdout;
+\\N    before trigger fired
 31    before trigger fired
 32    before trigger fired
 33    before trigger fired
@@ -100,12 +129,25 @@
 35    before trigger fired
 36    before trigger fired
 45    before trigger fired
+x    before trigger fired
+,    before trigger fired
+c    before trigger fired
+C    before trigger fired
+empty    before trigger fired
+null    before trigger fired
+Backslash    before trigger fired
+BackslashX    before trigger fired
+N    before trigger fired
+BackslashN    before trigger fired
+XX    before trigger fired
+Delimiter    before trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 COPY x (b, e) TO stdout WITH NULL 'I''m null';
+I'm null    before trigger fired
 21    before trigger fired
 22    before trigger fired
 23    before trigger fired
@@ -113,6 +155,18 @@
 25    before trigger fired
 26    before trigger fired
 I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+1    before trigger fired
+2    before trigger fired
+3    before trigger fired
+4    before trigger fired
+5    before trigger fired
+6    before trigger fired
+7    before trigger fired
+8    before trigger fired
 1    after trigger fired
 2    after trigger fired
 3    after trigger fired
diff -ruN ../base/src/test/regress/sql/copy2.sql src/test/regress/sql/copy2.sql
--- ../base/src/test/regress/sql/copy2.sql    2003-05-14 05:26:03.000000000 +0200
+++ src/test/regress/sql/copy2.sql    2003-10-07 20:49:49.000000000 +0200
@@ -2,7 +2,7 @@
     a serial,
     b int,
     c text not null default 'stuff',
-    d text not null,
+    d text,
     e text
 );

@@ -27,6 +27,7 @@
 FOR EACH ROW EXECUTE PROCEDURE fn_x_before();

 COPY x (a, b, c, d, e) from stdin;
+9999    \N    \\N    \NN    \N
 10000    21    31    41    51
 \.

@@ -74,6 +75,24 @@
 -- various COPY options: delimiters, oids, NULL string
 COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
 500000,x,45,80,90
+500001,x,\x,\\x,\\\x
+500002,x,\,,\\\,,\\
+\.
+
+COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
+3000;;c;;
+\.
+
+COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
+4000:\X:C:\X:\X
+4001:1:empty::
+4002:2:null:\X:\X
+4003:3:Backslash:\\:\\
+4004:4:BackslashX:\\X:\\X
+4005:5:N:\N:\N
+4006:6:BackslashN:\\N:\\N
+4007:7:XX:\XX:\XX
+4008:8:Delimiter:\::\:
 \.

 -- check results of copy in