Thread: Re: [HACKERS] Cannot dump/restore text value \N
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
Manfred Koizar <mkoi-pg@aon.at> writes:
> Here is a patch that does this and adds a few regression tests.
Uh, I did that already ... for 7.4 at least.
regards, tom lane
On Wed, 08 Oct 2003 11:31:30 +0200, I wrote:
>There haven't been too much changes in this area between 7.3 and 7.4.
Here is the patch for 7.3.4 ...
Bruce, I noticed that the original patch submission didn't contain
anything useful as a cvs log message:
Make COPY FROM a bit more compatible with COPY TO regarding
backslashes, especially \N.
Servus
Manfred
diff -ruN ../base/src/backend/commands/copy.c src/backend/commands/copy.c
--- ../base/src/backend/commands/copy.c 2003-04-26 00:14:33.000000000 +0200
+++ src/backend/commands/copy.c 2003-10-08 16:30:14.000000000 +0200
@@ -62,7 +62,8 @@
FILE *fp, char *delim, char *null_print);
static Oid GetInputFunction(Oid type);
static Oid GetTypeElement(Oid type);
-static char *CopyReadAttribute(FILE *fp, const char *delim, CopyReadResult *result);
+static char *CopyReadAttribute(FILE *fp, const char *delim, const char *nullst,
+ CopyReadResult *result, bool *isnull);
static void CopyAttributeOut(FILE *fp, char *string, char *delim);
static List *CopyGetAttnums(Relation rel, List *attnamelist);
@@ -931,6 +932,7 @@
{
bool skip_tuple;
Oid loaded_oid = InvalidOid;
+ bool isnull;
CHECK_FOR_INTERRUPTS();
@@ -954,7 +956,7 @@
if (file_has_oids)
{
- string = CopyReadAttribute(fp, delim, &result);
+ string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);
if (result == END_OF_FILE && *string == '\0')
{
@@ -963,7 +965,7 @@
break;
}
- if (strcmp(string, null_print) == 0)
+ if (isnull)
elog(ERROR, "NULL Oid");
else
{
@@ -990,7 +992,7 @@
elog(ERROR, "Missing data for column \"%s\"",
NameStr(attr[m]->attname));
- string = CopyReadAttribute(fp, delim, &result);
+ string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);
if (result == END_OF_FILE && *string == '\0' &&
cur == attnumlist && !file_has_oids)
@@ -1000,7 +1002,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 */
}
@@ -1029,7 +1031,7 @@
{
if (attnumlist == NIL && !file_has_oids)
{
- string = CopyReadAttribute(fp, delim, &result);
+ string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);
if (result == NORMAL_ATTR || *string != '\0')
elog(ERROR, "Extra data after last expected column");
if (result == END_OF_FILE)
@@ -1158,8 +1160,6 @@
*/
for (i = 0; i < num_defaults; i++)
{
- bool isnull;
-
values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
&isnull, NULL);
if (!isnull)
@@ -1175,7 +1175,6 @@
{
Node *node = constraintexprs[i];
Const *con;
- bool isnull;
if (node == NULL)
continue; /* no constraint for this attr */
@@ -1316,15 +1315,14 @@
* END_OF_FILE: EOF indication
* 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(FILE *fp, const char *delim, CopyReadResult *result)
+CopyReadAttribute(FILE *fp, const char *delim, const char *nullst,
+ CopyReadResult *result, bool *isnull)
{
int c;
int delimc = (unsigned char) delim[0];
@@ -1332,6 +1330,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;
@@ -1357,6 +1366,7 @@
}
if (c == delimc)
break;
+ CHECK_MATCH(c);
if (c == '\\')
{
c = CopyGetChar(fp);
@@ -1365,6 +1375,7 @@
*result = END_OF_FILE;
goto copy_eof;
}
+ CHECK_MATCH(c);
switch (c)
{
case '0':
@@ -1384,11 +1395,13 @@
{
val = (val << 3) + OCTVALUE(c);
CopyDonePeek(fp, c, true /* pick up */ );
+ CHECK_MATCH(c);
c = CopyPeekChar(fp);
if (ISOCTAL(c))
{
val = (val << 3) + OCTVALUE(c);
CopyDonePeek(fp, c, true /* pick up */ );
+ CHECK_MATCH(c);
}
else
{
@@ -1413,15 +1426,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;
@@ -1464,6 +1468,7 @@
*result = END_OF_FILE;
goto copy_eof;
}
+ CHECK_MATCH(c);
appendStringInfoCharMacro(&attribute_buf, c);
}
}
@@ -1471,6 +1476,8 @@
copy_eof:
+ *isnull = (matchnull && (nullst[matchlen] == '\0'));
+
if (client_encoding != server_encoding)
{
cvt = (char *) pg_client_to_server((unsigned char *) attribute_buf.data,
@@ -1486,6 +1493,7 @@
}
return attribute_buf.data;
+#undef CHECK_MATCH
}
static void
diff -ruN ../base/src/test/regress/expected/copy2.out src/test/regress/expected/copy2.out
--- ../base/src/test/regress/expected/copy2.out 2002-10-19 03:35:43.000000000 +0200
+++ src/test/regress/expected/copy2.out 2003-10-07 21:49:08.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'
@@ -49,23 +49,38 @@
SET autocommit TO 'on';
-- 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 (
@@ -81,6 +96,7 @@
ERROR: COPY: 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
@@ -88,12 +104,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
@@ -101,12 +130,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
@@ -114,6 +156,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 2002-10-19 03:35:43.000000000 +0200
+++ src/test/regress/sql/copy2.sql 2003-10-07 21:49:08.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
\.
@@ -75,6 +76,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
Manfred Koizar wrote: > On Wed, 08 Oct 2003 11:31:30 +0200, I wrote: > >There haven't been too much changes in this area between 7.3 and 7.4. > > Here is the patch for 7.3.4 ... > > Bruce, I noticed that the original patch submission didn't contain > anything useful as a cvs log message: > > Make COPY FROM a bit more compatible with COPY TO regarding > backslashes, especially \N. Oh, good point. Thanks. Can someone explain what was broken? Was it only for non-standard NULL strings? Would it silently fail? I saw your example and it seemed strange we had not seen a bug report before. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, 8 Oct 2003 11:33:24 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>Can someone explain what was broken?
COPY FROM removed backslashes before comparing the input to the
external null representation. (It had a hard-wired special code path
that allowed \N to be recognized.) The text \N was (and still is)
correctly exported as \\N, but \\N was imported as NULL.
> Was it only for non-standard NULL strings?
There were problems in both cases.
Standard NULL representation:
fred=# CREATE TABLE a (c1 text, c2 text);
CREATE TABLE
fred=# INSERT INTO a VALUES ('\\N', null);
INSERT 577147 1
fred=# SELECT * FROM a;
c1 | c2
----+----
\N |
(1 row)
fred=# COPY a TO stdout;
\\N \N
fred=# COPY a FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \\N \N
>> \.
fred=# SELECT * FROM a;
c1 | c2
----+----
\N |
|
(2 rows)
User defined NULL string:
fred=# CREATE TABLE a (c1 text, c2 text);
CREATE TABLE
fred=# INSERT INTO a VALUES ('\\X', null);
INSERT 577140 1
fred=# SELECT * FROM a;
c1 | c2
----+----
\X |
(1 row)
fred=# COPY a TO stdout WITH NULL AS '\\X';
\\X \X
fred=# COPY a FROM stdin WITH NULL AS '\\X';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \\X \X
>> \.
fred=# SELECT * FROM a;
c1 | c2
----+----
\X |
| X
(2 rows)
> Would it silently fail?
It would silently insert wrong data, unless a constraint (NOT NULL)
prevented it.
> I saw your example and it seemed
>strange we had not seen a bug report before.
Because nobody was crazy enough to store \N in his database ...
Tom has already fixed this issue for cvs head. My 7.4 patch wouldn't
apply anyway (built it against Beta 3). You might want to apply the
7.3.4 version, though.
Should I send a new patch with only the regression tests?
Servus
Manfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> Should I send a new patch with only the regression tests?
Yeah, if you think they are worth adding.
regards, tom lane
Tom Lane wrote: > Manfred Koizar <mkoi-pg@aon.at> writes: > > Should I send a new patch with only the regression tests? > > Yeah, if you think they are worth adding. Regression part of NULL patch applied. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073