Why "copy ... from stdio" does not return immediately when reading invalid data?

From: Nicolas Grilly <nicolas(at)gardentechno(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why "copy ... from stdio" does not return immediately when reading invalid data?
Date: 2011-02-02 18:20:15
Message-ID: AANLkTik9uhtpp2bthp=aXt70VHPFCSCR8ma3NDnkkwKc@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.

I tried the command "\copy ... from stdio" in psql and it looks like psql
has to read the entire input before returning a potential error, even if the
invalid value is in one of the first rows.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carlos Mennens 2011-02-02 18:32:41 Database Design Question
Previous Message Chris Browne 2011-02-02 17:45:54 Re: Why does my DB size differ between Production and DR? (Postgres 8.4)

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2011-02-02 18:20:59 Re: [HACKERS] Slow count(*) again...
Previous Message Jon Nelson 2011-02-02 18:19:20 Re: [HACKERS] Slow count(*) again...