No, the error message should arrive immediately. But it may be deficiency of
libpq that you cannot aquire it before ending the copy.
I have analyzed the PostgreSQL protocol using Wireshark (an open source packet analyzer), and I confirm 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:
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.
I have tried that, and many other things, and I have carefully read libpq source code, and my understanding is that it is impossible with the current implementation of libpq.
It is impossible because the function pqParseInput3 (file fe-protocol3.c) does not parse error responses while the connection is in PGASYNC_COPY_IN state. We have to call PQputCopyEnd to switch the connection to PGASYNC_BUSY and start error messages parsing.
Can Marko or someone else confirm this analysis? Any idea to improve that?
Regards,
Nicolas Grilly
PS : Considering that both psycopg2 and psql are built on top of libpq, this a quite logical they behave the same regarding late reporting of copy errors, contrary to pg8000 that could easily read, parse and report the errors early.