I'm attempting to take geometry columns from two different tables, perform basic PostGIS operations on them, and have them returned in a single result set.
My SQL statement looks like the following:
SELECT (ST_Dump(ST_Polygonize(exterior_rings.the_geom))).geom AS exterior, (ST_Dump(ST_Polygonize(interior_rings.the_geom))).geom AS interior FROM (SELECT the_geom FROM geodata."_unioned_exterior_rings") AS exterior_rings, (SELECT the_geom FROM geodata."_unioned_interior_rings") AS interior_rings;
When I execute this command in pgAdmin III's arbitrary SQL query tool, after roughly 8 minutes of execution, I receive:
********** Error **********
... and the query tool complains it is no longer connected to the database. This leads me to believe the operation is timing out. The respective sizes of the tables are fairly humble.
_unioned_exterior_rings has ~4000 rows, and
_unioned_interior_rings has ~100. I've had other queries succeed after twice as much run time so I don't feel as if I'm hitting some preset limit.
When I use pgAdmin's console tool to execute the query, after some time it becomes apparent that the entire tool has been disconnected without any kind of error message. On some reconnect attempts I've received notices that the reconnection fails because the database is in a fatal recover mode. If I try again shortly there after it usually works.
When I execute the statements separately (doing the polygonize/dump on an individual table) I get the expected results after only a few seconds.
The version of PostgreSQL is 8.4, PostGIS is 1.4.2, and pgAdmin III is at 1.12.2.
I can't claim any real SQL proficiency, so I'm willing accept that my mistake may be simple or fundamental. Any help would be appreciated.