Re: Server Programming Interface - spi.exec() overheds issue

On Wed, Jun 18, 2008 at 11:35 AM, João Gonçalves <joaofgo(at)gmail(dot)com> wrote:
> Hi!
> I'm not quite sure if this is the right place to discuss this issue but
> here goes. I've been testing PL/R language coupled with SPI and postgis to
> produce Voronoi tiles, the following function pushes a set of polygon
> vertexes into an R array and inserts the Voronoi tiles into the database.
> Since my current working dataset has something like 1.5M vertexes the
> overheads built up are huge and the proccess fails due to insuficient
> memory. Also, I can only see the results until all data is proccessed.
>
> What is the best way to handle this? Can I flush/purge pg buffers to better
> handle memory issues?
> Should I adopt a block processing strategy to narrow down the initial
> dataset through a LIMIT statement or something along this line?
> Is spi.execute() in read-only mode usable / effective in this context?
> Are spi.freetuple or spi.freetuptable usable? How?
> Any ideas?
>
> CREATE OR REPLACE FUNCTION voronoi_tiles(TEXT, TEXT, INTEGER) RETURNS
> void AS '
> library(deldir)
>
> gids<-pg.spi.exec(sprintf("SELECT DISTINCT a.poly_gid AS gid FROM
> %1$s AS a ORDER BY gid;",arg1))
>
> for (i in 1:length(gids$gid)){
>
> # Retrieve points from the auxiliary geometry
> points <- pg.spi.exec(sprintf("SELECT st_x(a.the_geom) AS x,
> st_y(a.the_geom) AS y FROM %1$s AS a WHERE a.poly_gid = %2$i;", arg1,
> gids$gid[[i]]))
>
> # External envelope
> xmin<-min(points$x)-abs(min(points$x)-max(points$x))
> xmax<-max(points$x)+abs(min(points$x)-max(points$x))
> ymin<-min(points$y)-abs(min(points$y)-max(points$y))
> ymax<-max(points$y)+abs(min(points$y)-max(points$y))
>
> # Generate the voronoi object
> voro = deldir(points$x, points$y, digits=6, frac=1e-3,
> list(ndx=2,ndy=2), rw=c(xmin,xmax,ymin,ymax))
>
> # Get the individual tiles/polygons for the Voronoi diagram
> tiles = tile.list(voro)
>
> for(j in 1:length(tiles)){
>
> tile<-tiles[[j]]
> geom = "GeomFromText(''LINESTRING("
>
> for(k in 1:length(tile$x)){
> geom = sprintf("%s %.6f %.6f,", geom, tile$x[[k]],
> tile$y[[k]])
> }
>
> # Close the tile by appending the first vertex
> geom = sprintf("%s %.6f %.6f)'' , %i)", geom, tile$x[[1]],
> tile$y[[1]], arg3)
>
> # Insert into the database
> pg.spi.exec(sprintf("INSERT INTO %1$s (gid, the_geom) VALUES
> (%2$i, %3$s)", arg2, gids$gid[[i]], geom))
> }
> }
> ' LANGUAGE 'plr';
>
> Example:
> SELECT voronoi_tiles('test_set', 'output_test_table', <SRID>);
>
>
>
>
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Try using a cursor. See pg.spi.cursor_open in the pl/r documentation.
It will allow you to issue the query once, but fetch and process
results a little at a time (like your LIMIT idea, but easier).

- Josh / eggyknap