I have a simple task: bulk insert csv file to the table. The .ctl file is
TYPE = CSV # Input file type
ESCAPE = \ # Escape character for Quoting
DELIMITER = "," # Delimiter
PARSE_BADFILE = "/home/USERNAME/bulkload.bad"
LOGFILE = "/home/USERNAME/bulkload.log"
WRITER = BUFFERED
CHECK_CONSTRAINTS = YES
PARSE_ERRORS = 0
I'm importing csv file of approximately 6MB size. The import proceeds for 2-3 minutes, eats all free space (14 GB on main partition) and ends up in this error:
2012-10-28 12:13:22 UTC ERROR: could not extend file "base/383631/709460.1": wrote only 4096 of 8192 bytes at block 177462
2012-10-28 12:13:22 UTC HINT: Check free disk space.
2012-10-28 12:13:22 UTC STATEMENT: SELECT * FROM pg_bulkload($1)
The table I'm inserting into have 74732120 rows already.
UPDATE:
The CSV file is 4-6 MB size, 150-300k rows in average.
UPDATE 2: the table definition is
id | integer | not null default nextval('shops_offer_id_seq'::regclass)
is_trashed | boolean | not null
price | double precision | not null
availability | double precision |
description | text |
identificator_id | character varying(148) | not null
shop_id | integer | not null
date | timestamp with time zone | not null
transaction_id | integer | not null
Indexes:
"shops_offer_pkey" PRIMARY KEY, btree (id)
"shops_offer_identificator_id" btree (identificator_id)
"shops_offer_identificator_id_like" btree (identificator_id varchar_pattern_ops)
"shops_offer_is_trashed_idx" btree (is_trashed)
"shops_offer_shop_id" btree (shop_id)
"shops_offer_transaction_id" btree (transaction_id)
Foreign-key constraints:
"shops_offer_identificator_id_fkey" FOREIGN KEY (identificator_id) REFERENCES products_identificator(id) DEFERRABLE INITIALLY DEFERRED
"shops_offer_shop_id_fkey" FOREIGN KEY (shop_id) REFERENCES shops_shopcore(id) DEFERRABLE INITIALLY DEFERRED
"shops_offer_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES shops_importtransaction(id) DEFERRABLE INITIALLY DEFERRED
The row sample of CSV is
201298602,False,29.2,1.0,"","5000000000500000000037",965,"2012-11-05 11:10:23",1269570
Last time the file was 7.5MB.
We solved the issue for now by increasing disk space, but the problem persists.
COPY. – dezso Nov 2 '12 at 20:17wc -lif in doubt), columns, data types, and on-disk file size? – Craig Ringer Nov 3 '12 at 1:50COPY? – dezso Nov 5 '12 at 6:34