Opened 18 years ago

Closed 16 years ago

#1507 closed defect (worksforme)

mappostgis rev 1.59 breaks deferred connection close

Reported by: warmerdam Owned by: refractions
Priority: high Milestone:
Component: PostGIS Interface Version: unspecified
Severity: normal Keywords:
Cc: jerry.pisk@…

Description (last modified by warmerdam)

I find my postgis layer now fails since upgrading from rev 1.58 of
mappostgis.c to rev 1.59 which has the commit comment:

 * Revision 1.59  2005/07/16 19:07:39  jerryp
 * Bug 1420: PostGIS connector no longer needs two layer close functions.

The mapfile in question:

  http://home.gdal.org/maps/ukpoly/ukpoly.map

Has two layers from PostGIS and uses the CLOSE_CONNECTION=DEFER to
share a connection between the layers.  

When I run shp2img on this I get the following extensive report:
warmerda@gdal2200[112]% shp2img -m ukpoly.map -o out.png
msPOSTGISLayerOpen called datastatement: wkb_geometry from polygon
MSPOSTGISLayerOpen -- shared connection not available.
msConnPoolRegister(ukpoly,dbname=warmerda user=warmerda,0x8f588c8)
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerWhichShapes called
msPOSTGISLayerRetrievePGVersion(): query = select substring(version() from 12
for (position('on' in version()) - 13))
msPOSTGISLayerRetrievePGVersion: Version String: 8.0.3
msPOSTGISLayerRetrievePGVersion(): Found version 8, 0, 3
msPOSTGISLayerRetrievePK: query = select attname from pg_attribute,
pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and
pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and
pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = 'polygon'
and pg_constraint.conkey[2] is null
msPOSTGISLayerRetrievePK: No results found.
msPOSTGISLayerParseData: unique column = OID, srid='', geom_column_name =
wkb_geometry, table_name=polygon
query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),OID::text from polygon
WHERE wkb_geometry && setSRID('BOX3D(470832.833333332 4751545,489586.166666668
4765610)'::BOX3D, find_srid('','polygon','wkb_geometry') )
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- closing query_result
msConnPoolRelease(ukpoly,dbname=warmerda user=warmerda,0x8f588c8)
msPOSTGISLayerOpen called datastatement: wkb_geometry from polygon
msConnPoolRequest(uklines,dbname=warmerda user=warmerda) -> got 0x8f588c8
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerWhichShapes called
msPOSTGISLayerRetrievePGVersion(): query = select substring(version() from 12
for (position('on' in version()) - 13))
msPOSTGISLayerRetrievePGVersion: Version String: 8.0.3
msPOSTGISLayerRetrievePGVersion(): Found version 8, 0, 3
msPOSTGISLayerRetrievePK: query = select attname from pg_attribute,
pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and
pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and
pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = 'polygon'
and pg_constraint.conkey[2] is null
msPOSTGISLayerRetrievePK: No results found.
msPOSTGISLayerParseData: unique column = OID, srid='', geom_column_name =
wkb_geometry, table_name=polygon
WARNING:  there is already a transaction in progress
query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),OID::text from polygon
WHERE wkb_geometry && setSRID('BOX3D(470832.833333332 4751545,489586.166666668
4765610)'::BOX3D, find_srid('','polygon','wkb_geometry') )
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- query_result is NULL
msConnPoolRelease(uklines,dbname=warmerda user=warmerda,0x8f588c8)
msDrawMap(): Image handling error. Failed to draw layer named 'uklines'. <br>
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),OID::text from polygon
WHERE wkb_geometry && setSRID('BOX3D(470832.833333332 4751545,489586.166666668
4765610)'::BOX3D, find_srid('','polygon','wkb_geometry') )' <br><br>

Postgresql reports the error as 'ERROR:  cursor "mycursor" already exists
'<br><br>

More Help:<br><br>

Error with POSTGIS data variable. You specified '&lt;check your .map file&gt;'.<br>
Standard ways of specifiying are : <br>
(1) 'geometry_column from geometry_table' <br>
(2) 'geometry_column from (&lt;sub query&gt;) as foo using unique &lt;column
name&gt; using SRID=&lt;srid#&gt;' <br><br>

Make sure you put in the 'using unique  &lt;column name&gt;' and 'using SRID=#'
clauses in.

<br><br>For more help, please see http://postgis.refractions.net/documentation.php 

<br><br>Mappostgis.c - version of Jan 23/2004.
 <br>
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- layerinfo is  NULL
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- layerinfo is  NULL
msConnPoolClose(dbname=warmerda user=warmerda,0x8f588c8)

The key issue seems to be that the original cursor (called
mycursor) was not cleaned up properly and so the redeclaration
for the next layer fails. 

I have confirmed that the problem goes away if I comment out
the CLOSE_CONNECTION=DEFER processing options.

Change History (3)

comment:1 by fwarmerdam, 18 years ago

Cc: jerry.pisk@… added
Adding Jerry to the cc list. 

comment:2 by tomkralidis, 16 years ago

Is this still a valid issue?

comment:3 by warmerdam, 16 years ago

Description: modified (diff)
Resolution: worksforme
Status: newclosed

I have setup a roughly corresponding setup again and run it and things seem to work using the latest FWTools build of mapserver/shp2img from trunk (well a couple weeks old). So I think we can close this report. Debug output looks like this:

warmerda@gdal64[103]% shp2img -m ukpoly2.map -o out.png -all_debug 1
msPOSTGISLayerOpen called datastatement: wkb_geometry from polygon
MSPOSTGISLayerOpen -- shared connection not available.
msConnPoolRegister(ukpoly,dbname=warmerda host=gdal,0x80d03c8)
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerWhichShapes called
msPOSTGISLayerRetrievePGVersion(): query = select substring(version() from 12 for (position('on' in version()) - 13))
msPOSTGISLayerRetrievePGVersion: Version String: 8.1.5
msPOSTGISLayerRetrievePGVersion(): Found version 8, 1, 5
msPOSTGISLayerRetrievePK: query = select attname from pg_attribute, pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = 'polygon' and pg_table_is_visible(pg_class.oid) and pg_constraint.conkey[2] is null
msPOSTGISLayerParseData: unique column = ogc_fid, srid='', geom_column_name = wkb_geometry, table_name=polygon
query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),ogc_fid::text from polygon WHERE wkb_geometry && setSRID('BOX3D(470824.993311037 4751545,489594.006688963 4765610)'::BOX3D, find_srid('','polygon','wkb_geometry') )
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- closing query_result
msConnPoolRelease(ukpoly,dbname=warmerda host=gdal,0x80d03c8)
msPOSTGISLayerOpen called datastatement: wkb_geometry from polygon
msConnPoolRequest(uklines,dbname=warmerda host=gdal) -> got 0x80d03c8
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerWhichShapes called
msPOSTGISLayerRetrievePGVersion(): query = select substring(version() from 12 for (position('on' in version()) - 13))
msPOSTGISLayerRetrievePGVersion: Version String: 8.1.5
msPOSTGISLayerRetrievePGVersion(): Found version 8, 1, 5
msPOSTGISLayerRetrievePK: query = select attname from pg_attribute, pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = 'polygon' and pg_table_is_visible(pg_class.oid) and pg_constraint.conkey[2] is null
msPOSTGISLayerParseData: unique column = ogc_fid, srid='', geom_column_name = wkb_geometry, table_name=polygon
WARNING:  there is already a transaction in progress
query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),ogc_fid::text from polygon WHERE wkb_geometry && setSRID('BOX3D(470824.993311037 4751545,489594.006688963 4765610)'::BOX3D, find_srid('','polygon','wkb_geometry') )
msPOSTGISLayerFreeItemInfo called
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- closing query_result
msConnPoolRelease(uklines,dbname=warmerda host=gdal,0x80d03c8)
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- layerinfo is  NULL
msPOSTGISLayerClose datastatement: wkb_geometry from polygon
msPOSTGISLayerClose -- layerinfo is  NULL
msConnPoolClose(dbname=warmerda host=gdal,0x80d03c8)
GDAL: GDALDeregister_GTiff() called.
Note: See TracTickets for help on using tickets.