[postgis-users] Odd problem with pgsql2shp

[Ethan Alpert]

I don't think the commands are failing. I removed all the XX's in the
output to shorten my email.

Anyhow I pg_dumped the table, dropped it and restored it. I then dropped
the indexes now I can dump the table in question in < 1min. I'm curious
about why an index would affect pgsql2shp or what realoading the table
all at once does to affect performance of pgsql2shp? I'm going to see if
readding the indexes recreates the problem. At this point I'm more
inclined to just leave the indexes off since the table is most
frequently used to just store the data to create a shapefile and I'm not
currently using it through any of the indexes.

-e

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
strk at refractions.net
Sent: Tuesday, July 26, 2005 7:14 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Odd problem with pgsql2shp


Both your examples are returning non-zero result,
which means a failure. Are you on a 64bit machine ?
The dump seems to choke on second record, can you
try isolate that geometry into a single table
and see if dumping that small table works ?

--strk;

On Tue, Jul 26, 2005 at 05:47:53PM -0600, Ethan Alpert wrote:
> 
> I'm running postgres 7.4.6 with postgis 0.9.0.
> 
> I have a table that suddenly about a month ago started taking 
> significantly more time to dump to a shapefile all of the sudden.
> 
> I run vacuum analyze nightly on my database. This particular table has

> had a lot of data inserted into it when the problem started occurring 
> but the number or records is only 16,156. The polygons are all 5 
> vertice rectangles. Suddenly it's taking nearly 20 minutes to dump to 
> a shape.
> 
> Meanwhile a table with over 500,000 records takes less than 4 minutes.

> I am at a complete loss to understand what about this particular table

> is causing the problem.
> 
> I'd like to upgrade but this is a production system and I really don't

> want to take it down. I may have to if I can't come up with an 
> explanation and fix for this problem.
> 
> I can find no differences in how the two tables I'm dumping are 
> defined with respect to indexes.
> 
> Has anyone ever run into anything like this? Any idea what I can 
> possibly try to do to fix this?
> 
> -bash-2.05b$ /usr/bin/time pgsql2shp -u spatial spatialdb dems 
> Initializing... Done.
> Dumping: X..X [16156 rows].
> Command exited with non-zero status 1
> 0.27user 0.35system 17:50.62elapsed 0%CPU (0avgtext+0avgdata 
> 0maxresident)k
> 0inputs+0outputs (168major+490minor)pagefaults 0swaps
> -bash-2.05b$ /usr/bin/time pgsql2shp -u spatial spatialdb snaps 
> Initializing... Done.
> Dumping: X..X [568961 rows].
> Command exited with non-zero status 1
> 12.62user 15.47system 3:42.43elapsed 12%CPU (0avgtext+0avgdata 
> 0maxresident)k
> 0inputs+0outputs (168major+15637minor)pagefaults 0swaps
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users