You could check the max number of points in your geometries : SELECT max(ST_NumPoints(geom)) FROM ...
Of course you could still have invalid / abberant geometry, which you could also check (ST_IsValid, St_IsSimple). You could solve both those hypotheses if you could perform your buffer by batch. Cheers, Rémi-C 2016-07-06 15:36 GMT+02:00 Paul Ramsey <pram...@cleverelephant.ca>: > Running a multi-million row update will take a long time. > It's possible you've exposed a memory leak in ST_Buffer (the older > your version of GEOS, the more likely that is) but it's also possible > you're just running a really long update. > I find for batch processing purposes that creating fresh tables is far > preferable: > > CREATE TABLE newtable AS SELECT ST_Buffer(geom) ... FROM oldtable; > > If you still see memory issues with the above then you probably do > have a leak, *or* you're just running buffer on a sufficiently large > input geometry or with a large enough radius to blow up the memory > naturally. > > P > > > On Wed, Jul 6, 2016 at 2:13 AM, Ivan Bianchi <i...@wikiloc.com> wrote: > > Hello, > > > > I am trying to update a column using a PostGIS ST_Buffer function into a > > table of 4.257.769 rows, but after 6 hours, an Out of memory error > appears > > and the kernel starts killing processes until a Kernel Panic shows up. > > > > I have simplified the buffer target geometry and also added a gist index > to > > that column. > > > > The statement is the following: > >> > >> psql -h host -U user -W -d database -c "UPDATE table SET buffer = > >> ST_Buffer(simplified_geometry, 0.005);" > > > > > > After reading and tunning the configuration, I still have the same > result. > > > > Here's the initial memory stats: > > > >> total used free shared buff/cache > available > >> Mem: 15G 1.5G 12G 503M 1.4G > >> 13G > >> Swap: 7.8G 0B 7.8G > > > > > > > > I'm running out of ideas, as I think the postgresql.conf memory > parameters > > are quite low for the machine specs. I understand I can split the process > > and paginate the rows, but I can't see why I can't deal with this full > > statement right now. > > > > Do you think this issue is related with the postgres memory parameters > > configuration? Why is not respecting the shared_buffers or > > effective_cache_size parameters and keeps growing? > > > > > > Here's some info: > > > > Machine specs > > > > Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz (8 cores) > > 16 GB of memory > > Fedora release 23 (Twenty Three) > > Kernel - 4.5.7-202.fc23.x86_64 > > > > postgresql.conf > > > > effective_cache_size = 5GB > > shared_buffers = 3GB > > work_mem = 10MB > > > > maintenance_work_mem = 800MB > > wal_buffers = 16MB > > > > Kernel parameters > > > > vm.overcommit_memory=2 > > > > kernel.shmmax = 8340893696 > > kernel.shmall = 2036351 > > > > Versions: > > > > PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.3.1 > > 20160406 (Red Hat 5.3.1-6), 64-bit > > POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.1, 04 > > March 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.9.3" > > LIBJSON="0.12" RASTER > > > > > > Many thanks, > > > > -- > > Ivan > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
