Re: question on error during COPY FROM

On Tue, Aug 23, 2016 at 6:04 AM, Jerome Wagner <jerome(dot)wagner(at)laposte(dot)net>
wrote:

> Hello,
>
> in the documentation I read
> https://www.postgresql.org/docs/current/static/sql-copy.html
>
>
> COPY stops operation at the first error. This should not lead to problems
> in the event of a COPY TO, but the target table will already have
> received earlier rows in a COPY FROM. These rows will not be visible or
> accessible, but they still occupy disk space. This might amount to a
> considerable amount of wasted disk space if the failure happened well into
> a large copy operation. You might wish to invoke VACUUM to recover the
> wasted space.
>
> does that mean that I should always execute a VACUUM to recover the wasted
> space when an error is triggered or will the auto-vacuum mechanism do the
> job by itself ?
>
> Thanks
> Jerome
>

*>but the target table will already have received earlier rows in a COPY
FROM*
*No, that is not what happens. The entire copy is not permanently written
to disk until a COMMIT or the entire statement is completed (;). On first
error, a ROLLBACK is done so there is no wasted space. That being said, it
is always good practice to schedule a VACUUM ANALYZE (but not FULL) in the
off hours to insure a cleanup and fresh statistics. *

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.