[redirecting]
I have abstracted this problem, and we definitely have a newline bug that has to be fixed, IMNSHO.
Attached are 2 scripts that are identical except that one has DOS-style line endings and one has Unix style line endings. The DOS-style just fails miserably with no warning. It's not related to Windows file-end munging - the problem is observable on Linux - psql doesn't correctly detect the end of copy input with "\." if it's followed by CRNL.
The attached patch appears to solve the problem. However, while it makes us conform to the first sentence below from the docs, it doesn't comply with the second. Not sure what to do about that. Maybe there's a better solution?
"COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines. To reduce the risk of error due to un-backslashed newlines or carriage returns that were meant as data, COPY FROM will complain if the line endings in the input are not all alike."
cheers
andrew
Tom Haddon wrote:
Here's what I can share, as there's some sensitive stuff in others.
This is just a dump from one of the databases. I was able to load it using the \i switch, but it has only populated data from the first table. Also, it kept prompting me to hit a key as the screen was scrolling. This doesn't seem normal to me. And I tried running it as a SQL statement from within PgAdmin3, and got:
"ERROR: syntax error at or near "1" at character 15030"
The first "1" you see is where this error is.
COPY backup_data (id, lu, ub, tape, contents, last_writter, times_written) FROM stdin; 1 2004-07-13 09:37:14.78254 thaddon DLT000005 Full Backup (including only 2004 filings) 6/9/04\r\n\r\nNow archived 2004-06-09 6 4 2004-07-13 09:40:57.876355 thaddon DLT000009 Full Backup 7/12/04 2004-07-12 4 3 2004-07-21 16:28:09.843069 thaddon DLT000007 Full Backup 7/21/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE \r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-21 14 2 2004-07-22 13:27:01.657706 thaddon DLT000006 Full Backup 7/22/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE \r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-22 10 5 2004-07-27 10:49:22.786997 thaddon DLT000008 Incremental backup since last full 7/21/04 last updated 7/23/04\r\n\r\nWill include (as of 7/23/04):\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE \r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-23 3 6 2004-07-27 10:50:11.802647 thaddon DLT000010 Differential backup since 7/21/04\r\n\r\nC:\\Perforce\\*.* /SUBDIR \r\nD:\\backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\insight_backup\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\equilar_ca\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\*.* /SUBDIR \r\nDMTNJ1-SERVER\\dmt_media\\public\\software\\*.* /SUBDIR /EXCLUDE \r\nHOTNSOUR\\sql_backup\\*.* /SUBDIR \r\nYODA\\qa\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\06\\*.* /SUBDIR \r\nYODA\\sec_filings\\filings\\2004\\07\\*.* /SUBDIR \r\nMILLENIUM\\ExchangeBackup\\*.* /SUBDIR \r\n 2004-07-27 1 \.
Thanks, Tom
-----Original Message-----
From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: Thursday, August 12, 2004 8:40 AM
To: Tom Haddon
Cc: [EMAIL PROTECTED]
Subject: Re: [pgsql-hackers-win32] Import from Linux to Windows
Tom Haddon wrote:
I don't think so. Did a search for it in vi and nothing. It doesn't give me an error, just exits. Last statement is "CREATE TABLE".
Sorry, not very helpful...
I think you'll need to let us look at the dump file to make any progress
- otherwise we are just guessing in the dark.
thanks
andrew
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
CREATE TABLE bar (
c text,
d text
);
CREATE TABLE foo (
a text,
b text
);
COPY bar (c, d) FROM stdin;
c d
\.
COPY foo (a, b) FROM stdin;
a b
\.
select * from foo;
select * from bar;
drop table foo;
drop table bar;
CREATE TABLE bar (
c text,
d text
);
CREATE TABLE foo (
a text,
b text
);
COPY bar (c, d) FROM stdin;
c d
\.
COPY foo (a, b) FROM stdin;
a b
\.
select * from foo;
select * from bar;
drop table foo;
drop table bar;
Index: src/bin/psql/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/copy.c,v
retrieving revision 1.50
diff -c -r1.50 copy.c
*** src/bin/psql/copy.c 11 Jul 2004 21:34:03 -0000 1.50
--- src/bin/psql/copy.c 13 Aug 2004 12:02:17 -0000
***************
*** 700,705 ****
--- 700,707 ----
c = getc(copystream);
if (c == '\n' || c == EOF)
{
+ if (c == '\n' && s != copybuf && *(s-1) == '\r')
+ *(s-1) = '\0';
linedone = true;
break;
}
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
