Re: COPY enhancements

Robert Haas <robertmhaas@gmail.com> wrote: 
> It seems quite odd to me that when COPY succeeds but there are
> errors, the transaction commits.  The only indication that some of
> my data didn't end up in the table is that the output says "COPY n"
> where n is less than the total number of rows I attempted to copy. 
> On the other hand, it would be equally bad if the transaction
> aborted, because then my error logging table would not get populated
> - I note that that's the behavior we do get if the max errors
> threshold is exceeded.  I'm not sure what the right answer is here,
> but it needs some thought and discussion.  I think at a minimum the
> caller needs some indication of the number of FAILED rows, not just
> the number of succesful ones.
When the COPY fails due to a high error count, we should be able to
determine:
(1)  How many rows were read.
(2)  How many of the rows read had errors.
(3)  Images of failed rows with errors found on each.
On success, we need the above, plus the failed rows in a format suable
for editing and re-applying as needed.
> Instead of logging to a table, I think we should consider making
> COPY return the tuples indicating the error as a query result, the
> same way EXPLAIN returns the query plan.
This seems attractive, particularly if it can be fed to an INSERT
statement (like INSERT ... SELECT does).  The only problem I see with
it is that PostgreSQL seems to not want to return rows from statements
which fail.  (I know this is generally considered a feature, but it
sometimes has unfortunate consequences.)  Is there a way to satisfy
(3) above on a failed COPY statement if we go this route?
-Kevin