Re: copy_from does not stop reading after an error

On Tue, Feb 1, 2011 at 2:57 PM, Nicolas Grilly <nicolas(at)gardentechno(dot)com> wrote:
> I have tested again the \copy command of psql and, contrary to what I wrote
> before, it looks like psql does not stop reading after an error either, and
> has to read the complete file before reporting the error.
> Conclusion: psycopg2, pg8000 and psql have the same behavior regarding the
> command "copy from stdin". The input data file is read entirely, even if
> there is incorrect data at the start of the file, and errors are reported
> only after having read the complete file.
> Therefore it is probably not a bug in psycopg2, and just a "limitation" of
> PostgreSQL protocol. Here is the protocol official documentation:
> http://www.postgresql.org/docs/9.0/static/protocol-flow.html#PROTOCOL-COPY
> I understand we have to "end" the copy before having a chance to retrieve
> PostgreSQL backend response and know if our data are correct, or not. Do you
> confirm this analysis?

No, the error message should arrive immediately. But it may be deficiency of
libpq that you cannot aquire it before ending the copy.

I'm not sure about that actually. It should be possible to call
select() & PQconsumeInput
between copy calls, thus also PQgetResult to get the error.

> It means copy_from is not designed to send a 10 gigabytes stream to
> PostgreSQL, with just one "copy from stdin" command. Maybe I have to split
> my input stream into smaller chunks and execute a "copy from stdin" command
> for each of them. Do you confirm this is the only (and adequate) solution?

It should work around the problem, with speed decrease.

--
marko