Skip site navigation (1) Skip section navigation (2)

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

From: "Josh Tolley" <eggyknap(at)gmail(dot)com>
To: João Gonçalves <joaofgo(at)gmail(dot)com>
Cc: "PgSQL Novice MailList" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Server Programming Interface - spi.exec() overheds issue
Date: 2008-06-22 15:57:34
Message-ID: e7e0a2570806220857s24c7995l48fbd7e64f54d14c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Steve CrawfordDate: 2008-06-23 16:42:22
Subject: Re: table oids and comments
Previous:From: Tom LaneDate: 2008-06-21 18:52:15
Subject: Re: ERROR: could not access status of transaction 575

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group