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.
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
Last time the file was 7.5MB.
We solved the issue for now by increasing disk space, but the problem persists.