Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#3008 closed defect (worksforme)

pgsql2shp version 2.1.4 crash

Reported by: nap2000 Owned by: pramsey
Priority: medium Milestone: PostGIS 2.1.6
Component: postgis Version: 2.1.x
Keywords: pgsql2shp Cc:


As far as I know nothing has changed on my side since pgsql2shp version 2.1.3 which worked.

The crash file is attached. The first few lines of which are:

ProblemType?: Crash Architecture: amd64 CrashCounter?: 1 Date: Wed Dec 17 00:17:01 2014 DistroRelease?: Ubuntu 14.04 ExecutablePath?: /usr/bin/pgsql2shp ExecutableTimestamp?: 1412113909

ProcCmdline?: pgsql2shp -f /smap/temp/78f455c7-5166-4e9b-b3c2-9aa9633a82c6/s1_main -u ws -P ws1234 results select\ s1_main.prikey\ as\ prikey,s1_main._user\ as\ xuser,s1_main._version\ as\ xversion,s1_main._device\ as\ xdevice,to_char(s1_main._start,\ 'YYYY-MM-DD\ HH24:MI:SS')\ as\ xstart,to_char(s1_main._end,\ 'YYYY-MM-DD\ HH24:MI:SS')\ as\ xend,\ as\ name,s1_main.found\ as\ found,s1_main.street_number\ as\ street_number,s1_main.street_name\ as\ street_name,s1_main.suburb_town\ as\ suburb_town,s1_main.site_name\ as\ site_name,s1_main.location\ as\ location,s1_main.location_other\ as\ location_other,s1_main.incident\ as\ incident,s1_main.class\ as\ class,s1_main.hotshots\ as\ hotshots,''\
\ s1_main.hotshot_image\ as\ hotshot_image,s1_main.risk\ as\ risk,s1_main.risk_description\ as\ risk_description,s1_main.waste_bins\ as\ waste_bins,s1_main.recycling_bins\ as\ recycling_bins,s1_main.butt_bins\ as\ butt_bins,s1_main.binsbins_overflowing\ as\ binsbins_overflowing,s1_main.binsbins_repair\ as\ binsbins_repair,s1_main.binsbins_signage\ as\ binsbins_signage,s1_main.sourcepassing_vehicles\ as\ sourcepassing_vehicles,s1_main.sourcepassing_pedestrians\ as\ sourcepassing_pedestrians,s1_main.sourcegathering_points_seats\ as\ sourcegathering_points_seats,s1_main.sourceschools\ as\ sourceschools,s1_main.sourcebank_atm\ as\ sourcebank_atm,s1_main.sourcetransport_stop\ as\ sourcetransport_stop,s1_main.sourceretail\ as\ sourceretail,s1_main.sourcefood_outlets\ as\ sourcefood_outlets,s1_main.sourceoutdoor_event\ as\ sourceoutdoor_event,s1_main.sourcetourist_site\ as\ sourcetourist_site,s1_main.sourceweather\ as\ sourceweather,s1_main.sourceother\ as\ sourceother,s1_main.source_other\ as\ source_other,s1_main.green_waste\ as\ green_waste,s1_main.building_waste\ as\ building_waste,s1_main.soils_fill\ as\ soils_fill,s1_main.general_other\ as\ general_other,s1_main.asbestos\ as\ asbestos,s1_main.other_material\ as\ other_material,s1_main.mattresses\ as\ mattresses,\ as\ furniture,s1_main.whitegoods\ as\ whitegoods,s1_main.tyres\ as\ tyres,s1_main.ewaste\ as\ ewaste,s1_main.car_bodies\ as\ car_bodies,s1_main.evidence\ as\ evidence,s1_main.comments\ as\ comments,s1_main.investigation\ as\ investigation,s1_main.cleanup\ as\ cleanup,s1_main.cleanup_other\ as\ cleanup_other,s1_main.the_geom\ as\ the_geom\ from\ s1_main\ where\ s1_main._bad='false'

ProcCwd?: /var/lib/tomcat7

Attachments (4)

_usr_bin_pgsql2shp.106.crash (376.3 KB) - added by nap2000 7 years ago.
Crash log file
s1_main-1.csv (1.8 KB) - added by nap2000 7 years ago.
CSV export of table (geometry converted to lat, lon columns)
s1_main.sql (5.2 KB) - added by nap2000 7 years ago.
pg_dump of table (pg_dump --table public.s1_main results)
postgis to shape queries.txt (2.5 KB) - added by nap2000 7 years ago.
Query causing crash and one that works

Download all attachments as: .zip

Change History (15)

Changed 7 years ago by nap2000

Crash log file

comment:1 Changed 7 years ago by robe

nap2000 -- you have a sample file that causes the crash you can provide? Link will be fine or if small enough can attach to this ticket.

comment:2 Changed 7 years ago by robe

I meant an sql backup of the table or a sample that would trigger the same issue if that is possible.

Changed 7 years ago by nap2000

Attachment: s1_main-1.csv added

CSV export of table (geometry converted to lat, lon columns)

Changed 7 years ago by nap2000

Attachment: s1_main.sql added

pg_dump of table (pg_dump --table public.s1_main results)

comment:3 Changed 7 years ago by pramsey

Milestone: PostGIS 2.1.5PostGIS 2.1.6

comment:4 Changed 7 years ago by nap2000

Can you give me an indication of what is causing the crash so I can work around it until the release is available. (the extract statement is automatically generated).

comment:5 Changed 7 years ago by robe


I tried the table you had and exported using 2 windows boxes I have and couldn't trigger a crash using the below command. I do have an Ubuntu 14.04 box I can try on, but I need to upgrade that since its running 2.1.3 which you say works. Have you tried upgrading to 2.1.5?

Also can you paste in the output of:

select postgis_full_version() || ' ' || version();

pgsql2shp -f c:/test -h localhost -p 5439 -u postgres -P whatever mygisdb s1_main

-- I tried on these two

POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER PostgreSQL 9.4.0, compiled by Visual C++ build 1800, 64-bit

as well as

POSTGIS="2.2.0dev r13180" GEOS="3.5.0dev-CAPI-1.9.0 r4038" SFCGAL="1.0.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY (topology procs from "2.1.5 r13152" need upgrade) RASTER PostgreSQL 9.4.0, compiled by Visual C++ build 1800, 64-bit

comment:6 Changed 7 years ago by robe

On closer inspection I see you were outputting a query and not the table

1) does it crash if you output the whole table? and if not

2) I can't make out your query can you attach the exact command you were running in a text file so it doesn't get mangled?

I looked thru the logs of changes made to pgsql2shp and no changes as far as I can tell were made in 2.1.4 (last change was the one I made which would have been pushed in 2.1.3)

So whatever you are seeing might be a change in a dependency or something further up in our code that pgsql2shp is using.

comment:7 Changed 7 years ago by nap2000

Hi Robe,

I was wrong when I said the problem occurred with a new postgis version. Its the specific query and as you can see below it looks like postgis 2.1.2.

1) select postgis_full_version()
' ' version();

POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit (1 row)

2) If I remove arbitrary columns from the query then the crash does not happen. However it does not appear to be a specific column that is causing it. I am attaching a file with the full query that crashes and a smaller query that does not.

Changed 7 years ago by nap2000

Query causing crash and one that works

comment:8 Changed 7 years ago by robe

Okay I tried the crashing one on my 2.2.0 seems to work fine. Didn't try on my 2.1.5 but nothing between 2.2.0 and 2.1.5 has changed.

Any chance you can upgrade to see if the issue goes away. Your issue sounds like #1008 but that was fixed a while ago. Can you verify the version of pgsql2shp you are running?

Just run pgsqlshp without any args. Just want to rule out you are using an old version that had the too many columns issue.

comment:9 Changed 7 years ago by robe

Just to be clear, the information from pgsql2shp I am looking for is the top header:

RELEASE: 2.2.0dev (r13180)

comment:10 Changed 7 years ago by pramsey

Resolution: worksforme
Status: newclosed

comment:11 Changed 6 years ago by nap2000

Closed 14 months ago. I really dropped the ball on this. The problem has still been occurring on exports with a large number of columns and pgsql2shp version 2.1.2. Its seems pretty clear that upgrading to 2.1.5 would fix the problem. However I have worked around it by using ogr2ogr which works with the extracts that were failing. May revisit pgsql2shp after I get around to upgrading the databases. Thanks for your work on this.

Note: See TracTickets for help on using tickets.