Server Programming Interface - spi.exec() overheds issue

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@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice