search

mattes / migrate

Database migrations. CLI and Golang library.
Other
2.29k stars 326 forks source link

Postgres taking 16 GB of ram to insert 80k rows? #318

Open Mistobaan opened 6 years ago

Mistobaan commented 6 years ago

I am using the postgres driver to insert 80k rows.

 INSERT INTO food_des(ndb_no,fdgrp_cd,long_desc,shrt_desc,comname,manufacname,survey,ref_desc,refuse,sciname,n_factor,pro_factor,fat_factor,cho_factor) VALUES 
('01001','0100','Butter, salted','BUTTER,WITH SALT',NULL,NULL,'Y',NULL,0,NULL,6.38,4.27,8.79,3.87)
,('01002','0100','Butter, whipped, with salt','BUTTER,WHIPPED,W/ SALT',NULL,NULL,'Y',NULL,0,NULL,6.38,NULL,NULL,NULL)
,('01003','0100','Butter oil, anhydrous','BUTTER OIL,ANHYDROUS',NULL,NULL,'Y',NULL,0,NULL,6.38,4.27,8.79,3.87)

the system consistently fail with this error: (details: pq: the database system is in recovery mode) and pq: the database system is in recovery mode in line 0: SELECT pg_advisory_unlock($1)

And the reason is that it exhausts the entire memory. The entire sql migration is 1.5Mb.

What is going on here?

Mistobaan commented 6 years ago

running psql -f <migration file> runs without problems ...

neumachen commented 6 years ago

There must be a leak somewhere. I'm guessing, the leak is that the rows are not getting closed when they're executed.

GeertJohan commented 6 years ago

@magicalbanana seems to be correct. https://github.com/mattes/migrate/blob/master/database/postgres/postgres.go#L164

edit: nvm, Exec(..) doesn't keep the stream open, I was confused with Query(..)

GeertJohan commented 6 years ago

Could it maybe be that psql interprets the SQL and sends statements seperately, whereas migrate executes all the statements as a single statement?