[Fwd: Re: [pgsql-hackers-win32] Import from Linux to Windows] - Mailing list pgsql-hackers

[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:andrew@dunslane.net]
>Sent: Thursday, August 12, 2004 8:40 AM
>To: Tom Haddon
>Cc: pgsql-hackers-win32@postgresql.org
>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;
                  }