Re: [HACKERS] Cannot dump/restore text value \N
On Sun, 05 Oct 2003 19:12:50 -0400, Tom Lane [EMAIL PROTECTED]
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.0 +0200
+++ src/backend/commands/copy.c 2003-10-08 10:43:02.0 +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;
+ boolmatchnull = 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 @@
}
Re: [HACKERS] Cannot dump/restore text value \N
Manfred Koizar [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Cannot dump/restore text value \N
To be clear, this is not about \N as the default external
representation for NULL, I'm talking about a string consisting of the
two characters backslash and uppercase-N.
CREATE TABLE nonu (tx text NOT NULL);
INSERT INTO nonu VALUES ('\\N');
SELECT * FROM nonu;
COPY nonu TO stdout;
This correctly gives:
\\N
Now try to feed that back into the table:
DELETE FROM nonu;
COPY nonu FROM stdin;
\\N
\.
ERROR: copy: line 1, CopyFrom: Fail to add null value in not null
attribute tx
lost synchronization with server, resetting connection
This happened with 7.3.4, while trying to restore a 1.3 GB dump :-(
ERROR: copy: line 809051, CopyFrom: Fail to add null value in not
null attribute text
FATAL: Socket command type 0 unknown
The bug is still in 7.4Beta3; didn't test with Beta 4 yet.
Servus
Manfred
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Cannot dump/restore text value \N
Manfred Koizar [EMAIL PROTECTED] writes: To be clear, this is not about \N as the default external representation for NULL, I'm talking about a string consisting of the two characters backslash and uppercase-N. Now that I look at it, this must have been broken since the beginning of time, or at least since we made the null representation configurable. Surprising no one noticed before. The problem is that the WITH NULL string is compared to the attribute value *after* debackslashing, and so there is no way to prevent a match to an actual valid data string. In older code it seems that the representation of NULL as \N was hardwired, and this was tested for in the process of debackslashing, so that the valid data string \\N wouldn't be mistaken for \N. For the purposes of recognizing the default \N null representation, it seems we have to compare the null representation string to the pre-debackslashing input. (This is probably fairly easy to make happen in CVS tip, but it might be pretty painful in 7.3.) Arguably this is the right semantics because in the other direction we don't backslash the outgoing null-representation string. I wonder whether it would break any existing apps though. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Cannot dump/restore text value \N
I have solved my restore problem by editing (the relevant part of) the dump (:%s/^IN^I/^IN ^I/), a one-off solution g Anyway, thanks for your investigation. On Sun, 05 Oct 2003 19:12:50 -0400, Tom Lane [EMAIL PROTECTED] wrote: it seems we have to compare the null representation string to the pre-debackslashing input. Sounds reasonable, IMHO. I wonder whether it would break any existing apps though. Couldn't be worse than silently converting valid non-null values to NULL ... Servus Manfred ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
