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"

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.

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
"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.

  • 3
    Please include the table definition. And, if possible, try uploading the same CSV with COPY. – dezso Nov 2 '12 at 20:17
  • ... and how big is the CSV? Rows (use wc -l if in doubt), columns, data types, and on-disk file size? – Craig Ringer Nov 3 '12 at 1:50
  • 1
    Have you tried COPY? – dezso Nov 5 '12 at 6:34
  • Nah, sorry, no possibility for now. I'll try it later. – Mudblood Nov 5 '12 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.