Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 05/07/2017 16:33, Adrian Klaver ha scritto:

On 07/05/2017 01:05 AM, Moreno Andreo wrote:

Il 04/07/2017 20:51, Daniel Verite ha scritto:

Tom Lane wrote:


Moreno Andreo  writes:
So the hint is to abandon manual COPY and let pg_dump do the hard 
work?
If it is a newline-conversion problem, compressed pg_dump archives 
would

be just as subject to corruption as your binary COPY file is.
It's mentioned in [1] that the signature at the beginning of these 
files

embed a CRLF to detect this newline-conversion problem early on,
so I would expect COPY IN to stumble on a corrupted signature
and abort earlier in the process, if that conversion occurred.
Instead the report says it fails after a number of tuples:
Given what you said, can I assume it's a file transfer or an 
hardware-driven (pendrive) problem?


Daniel also mentioned the harddrive as a possible source of error. I 
would say monitoring where and when the issues appear may help with 
determining the source.
Yeah, trying to restore the same file on another machine should help 
determine the possible culprit.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 10:57 AM, Moreno Andreo wrote:

Il 04/07/2017 19:28, Tom Lane ha scritto:

Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


Well, I have no control on how the user transfers back and forth among 
machines.
Imagine you have a zip file where you backup your daily work. After 
you've done your backup, you put it on a pendrive and go home. When 
you're at home you copy this file to your computer and decompress it.
Our application works exactly the same way, except that it does not work 
with raw files, but with PostgreSQL data.
So I don't know how a user handles its backup files once he has made his 
backup...


Well that leads to four observations:

1) How the user handles their backup files is something that might need 
to be known.


2) By using your own backup code procedure you have taken possession of 
any resultant bugs:( The list might be able to help with those anyway, 
if it is possible for you to share the code you use to create the backups.


3) 1) and 2) could be moot if Daniel's hardware corruption theory is 
correct.


4) This is probably not going to be solved until you are able to access 
the actual file(s) in question.









--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 19:28, Tom Lane ha scritto:

Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


Well, I have no control on how the user transfers back and forth among 
machines.
Imagine you have a zip file where you backup your daily work. After 
you've done your backup, you put it on a pendrive and go home. When 
you're at home you copy this file to your computer and decompress it.
Our application works exactly the same way, except that it does not work 
with raw files, but with PostgreSQL data.
So I don't know how a user handles its backup files once he has made his 
backup...




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Tom Lane]

Moreno Andreo  writes:
> So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 10:13 AM, Moreno Andreo wrote:

Il 04/07/2017 18:25, Adrian Klaver ha scritto:

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been 
extremely lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management 
(issues with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup 
always resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.

On the basis of what Daniel wrote, I think you're absolutely right.



If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it 
succeeds it mainly proves Tom's point, that using BINARY in your 
situation is a hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > 
proj_txt.sql.gz



l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz


So the hint is to abandon manual COPY and let pg_dump do the hard work?


Not necessarily, you could modify your existing code to use the text COPY.


It means rewriting the whole backup logic, but if it has to be done, 
I'll manage to do it.


Thanks!
Moreno







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 18:25, Adrian Klaver ha scritto:

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been 
extremely lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management 
(issues with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup 
always resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.

On the basis of what Daniel wrote, I think you're absolutely right.



If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it 
succeeds it mainly proves Tom's point, that using BINARY in your 
situation is a hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > 
proj_txt.sql.gz



l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz


So the hint is to abandon manual COPY and let pg_dump do the hard work?
It means rewriting the whole backup logic, but if it has to be done, 
I'll manage to do it.


Thanks!
Moreno




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management (issues 
with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup always 
resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.


If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it succeeds 
it mainly proves Tom's point, that using BINARY in your situation is a 
hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > proj_txt.sql.gz


l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz










--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:42, Adrian Klaver ha scritto:

On 07/04/2017 08:37 AM, Moreno Andreo wrote:

Il 04/07/2017 17:25, Tom Lane ha scritto:

Moreno Andreo  writes:

Il 04/07/2017 16:51, Tom Lane ha scritto:
Pushing binary data around on Windows is always a hazardous 
proposition.

So what you are saying is "in the last 5 years you've been extremely
lucky?" :-)
Yup, particularly now that you mention moving the files between 
machines.

What did you do that with exactly?
Trying to answer your question (I hope I understood correctly, 
English is not my mother tongue)


I believe what Tom was asking is what mechanism/tools do you use to 
move the 2 files below from one machine to another? 

Sorry :-)
Files are copied simply with Windows Explorer or the backup is directly 
written on the external device, if requested by the user.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:39, Adrian Klaver ha scritto:

On 07/04/2017 08:19 AM, Moreno Andreo wrote:

Il 04/07/2017 16:51, Tom Lane ha scritto:

Moreno Andreo  writes:

I've implemented a backup procedure in C# with Npgsql (using COPY TO I
dump all tables in a compressed file) that's been working well in the
last 5 years (and it's still working, since this is a single, isolated
case).
OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)
[ got corrupted data with: ]
2017-07-04 12:55:27 CEST STATEMENT:  COPY
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last) 


FROM STDIN WITH BINARY
Pushing binary data around on Windows is always a hazardous 
proposition.

I'd bet something somewhere did a newline format conversion on your
data, either adding or removing CR characters.  There might not have
been any CR or LF bytes in the data fields proper, but it'd be quite
plausible for some of the length words used in binary-COPY format to
contain such bytes.

You might be better off using plain text COPY format; it can withstand
this sort of thing much better.

regards, tom lane

When we wrote this function, we first used plain COPY format, but we 
were not satisfied by the file size we got (too big compared to data 
size), so we switched to BINARY (I don't remember if there was also 
some performance matter involved).
So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management (issues 
with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup always 
resolved the error.

This is the very first time we have this kind of error.
If I had the source machine I'd try to make a new backup...




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 16:36, Adrian Klaver ha scritto:

On 07/04/2017 04:16 AM, Moreno Andreo wrote:
I've implemented a backup procedure in C# with Npgsql (using COPY TO 
I dump all tables in a compressed file) that's been working well in 
the last 5 years (and it's still working, since this is a single, 
isolated case).


OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)


Are you restoring to same as above or to another machine or Postgres 
instance? 
Yes, that's what this function is intended to do... users can move their 
data from a computer to another one, having the application installed.

(OS: always windows, Postgresql: always 9.1.6)
Unfortunately I can't restore this file in the source machine (user is 
away).




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general