Postgres - out of memory on looping insert

Question

I'm parsing an XML file with a few thousand elements in it for insertion into a sql database. Everything works fine with sqlite, but postgres dies on me with this error:

PGError: ERROR:  out of shared memory

I was monitoring locks, and it looks like there's a lock for each insert statement, and although I'm just looping over activerecord "save" calls (which should have a transaction each) these locks seem to hang around such that I have a few thousand open at a time.

As an experiment, I wrapped the save calls in a transaction:

Coupon.connection.transaction do  
  @coupon.save!
end

And that appears to have resolved my locks issue. However, it does seem there is something wrong here. Anyone know what it might be? I am using the "pg" driver.

Thanks, Tom

Have you considered Kettle for the task? kettle.pentaho.com

Dave Jarvis
– Dave Jarvis

2010-10-10 02:33:09 +00:00
Commented Oct 10, 2010 at 2:33 — Dave Jarvis
– Dave Jarvis, Commented Oct 10, 2010 at 2:33
have you got a log of what SQL Rails generates ?

bobflux
– bobflux

2011-05-06 12:09:28 +00:00
Commented May 6, 2011 at 12:09 — bobflux
– bobflux, Commented May 6, 2011 at 12:09

JamesHarrison · Accepted Answer · 2010-10-10 02:59:42Z

1

Your server may just be set up to only use a small amount of shared memory - http://www.postgresql.org/docs/8.1/static/kernel-resources.html details all this sort of stuff very nicely. Try raising that for starters. A few thousand elements defeating PostgreSQL does sound very odd, though - how large are the objects you're storing? What does your AR code look like? There's not a lot of information to go on here.

answered Oct 10, 2010 at 2:59

JamesHarrison

1532 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Lianza Over a year ago

The elements are fairly small... about 20 attributes, only one of which is a text datatype. My AR code is also simple, but I'm not sure what you're looking for. Create a coupon, assign a few attributes, save. I have a few validators, and no before/after save callbacks. I agree with you that it's very odd that a stock install of postgres would need any scalability tweaking for something that sqlite handles no-sweat. Plus, my code is super simple. So, that's why I'm thinking there's something AR/Rails/Driver-y going on here.

Collectives™ on Stack Overflow

Postgres - out of memory on looping insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related