Обсуждение: Why "copy ... from stdio" does not return immediately when reading invalid data?
Why "copy ... from stdio" does not return immediately when reading invalid data?
От
Nicolas Grilly
Дата:
Hello,
I am importing gigabytes of data into PostgreSQL, and I don't want to wait 10 minutes just to discover an error in the 10th line of my input file.
Is it a limitation of PostgreSQL protocol, of the library lipq, or of the tool psql?
Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send gigabytes of data with just one "copy ... from stdio" query, and is there a way to be notified of a potential error before calling PQputCopyEnd? Or do I have to send my data in small chunks (for example batch of 10000 rows), issue a PQputCopyEnd, check for errors, and continue with the next chunk?
Thanks for your help and advice.
Regards,
Nicolas Grilly
Re: Why "copy ... from stdio" does not return immediately when reading invalid data?
От
John R Pierce
Дата:
On 02/02/11 10:20 AM, Nicolas Grilly wrote: > Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to > send gigabytes of data with just one "copy ... from stdio" query, and > is there a way to be notified of a potential error before calling > PQputCopyEnd? Or do I have to send my data in small chunks (for > example batch of 10000 rows), issue a PQputCopyEnd, check for errors, > and continue with the next chunk? I would batch the data, maybe 1000 lines or even 100 lines at a time if these errors are at all frequent. put the errored batches in an exception list or something so you can sort them out later.
Re: Why "copy ... from stdio" does not return immediately when reading invalid data?
От
Nicolas Grilly
Дата:
I have analyzed the PostgreSQL protocol using Wireshark (an open source packet analyzer), and I observed that the PostgreSQL backend, while doing a COPY ... FROM STDIN, reports errors as soon as possible (especially errors related to invalid data).
Therefore, the "late" reporting of errors while doing a COPY ... FROM STDIN is not a limitation of the underlying protocol; it is a limitation (or a design choice) of the libpq library.
It looks like this is a well known issue because it is listed on the todo list:
And was discussed before:
Do you think it is possible to change that behavior, or work around it?
While reading libpq source code, I noticed the function pqParseInput3 (file fe-protocol3.c) ignores error responses while the connection is in PGASYNC_COPY_IN state. Maybe we can make a special case for the COPY FROM subprotocol and handle errors early, in order to make them available to PQgetResult? Is is feasible in a simple way or is it a bad idea?
Regards,
Nicolas Grilly
On Wed, Feb 2, 2011 at 20:06, John R Pierce <pierce@hogranch.com> wrote:
On 02/02/11 10:20 AM, Nicolas Grilly wrote:I would batch the data, maybe 1000 lines or even 100 lines at a time if these errors are at all frequent. put the errored batches in an exception list or something so you can sort them out later.Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send gigabytes of data with just one "copy ... from stdio" query, and is there a way to be notified of a potential error before calling PQputCopyEnd? Or do I have to send my data in small chunks (for example batch of 10000 rows), issue a PQputCopyEnd, check for errors, and continue with the next chunk?