Bug report #4470

cursor states lost with identify tool on postgis layer

Added by Sandro Santilli about 13 years ago. Updated over 11 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:14397

Description

Trying to identify a feature from a postgis layer I get a message:
Warning: Query: CLOSE qgisf2 returned 7 [ERROR: current transaction is aborted, commands ignored until end of transaction block
]

Server logs show:
2011-10-31 13:11:15 CET ERROR: Operation on mixed SRID geometries
2011-10-31 13:11:15 CET STATEMENT: fetch forward 200 from qgisf2
2011-10-31 13:11:15 CET ERROR: current transaction is aborted, commands ignored until end of transaction block

I dunno how to further debug this issue. In particular I don't know what's the query being sent...

Associated revisions

Revision f990886b
Added by Jürgen Fischer over 11 years ago

fix #4470 and #7889

History

#1 Updated by Giovanni Manghi almost 13 years ago

  • Target version set to Version 1.7.4

#2 Updated by Sai Nerella almost 13 years ago

  • Affected QGIS version set to master
  • Crashes QGIS or corrupts data set to No

Any update on this issue? I am using Quantum GIS 1.7.3 to identify features on Postgis 1.5.2 layers. I have the same issue.

1 cursor states lost.
SQL: declare qgisf2 binary cursor for select
function intersects(geography, geometry) does not exist....
LINE 1: ...4336602647 43.8916652467107795)'::box3d,4326) and intersects........
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
)...

#3 Updated by Alexander Bruy almost 13 years ago

Confirmed on master with PostGIS 2.0SVN database. Errors are

1 cursor states lost.
SQL: CLOSE qgisf0
Result: 7 (ERROR:  current transaction is aborted, commands ignored until end of transaction block)
Fetching from cursor qgisf0 failed
Database error: ERROR:  Operation on mixed SRID geometries
Query: CLOSE qgisf0 returned 7 [ERROR:  current transaction is aborted, commands ignored until end of transaction block]

#4 Updated by Sandro Santilli almost 13 years ago

Alexander: are you also using 1.7.3 ? Did you try with 1.7 branch ?

Sai Nerella: your case is a different one. If you still have problem with the 1.7 git branch please file another ticket.

#5 Updated by Sandro Santilli almost 13 years ago

I think my issue is about having geometries with mixed SRIDs into the same table. Something for which support seems fixed in master in that rows are also filtered by SRID. Do you confirm jef ?

#6 Updated by Alexander Bruy almost 13 years ago

Sandro Santilli wrote:

Alexander: are you also using 1.7.3 ? Did you try with 1.7 branch ?

No, I'm on master. Now with 27249d8 I can't reproduce this error

#7 Updated by Sandro Santilli almost 13 years ago

  • Target version changed from Version 1.7.4 to Version 2.0.0
  • Resolution set to fixed

I'll assume it's fixed then. Could not reproduce with 1.7, if anyone can feel free to reopen or file another one.

#8 Updated by Jürgen Fischer almost 13 years ago

  • Status changed from Open to Closed

#9 Updated by Rudi von Staden over 11 years ago

  • Status changed from Closed to Reopened

Description
I am seeing this on QGIS 1.8 and on master (revision ee96d38) with a particular table. I think the problem occurs when the SRID of a field is different from the SRID of the data contained in the field. I encountered the issue when I loaded an unprojected shapefile (attached) into postgis, and then updated the srid with UPDATE testdata SET geom=ST_SetSRID(geom,4326); The result of select find_srid('public','testdata','geom'); is then 0, but SELECT DISTINCT ST_SRID(geom) FROM testdata; returns 4326. Loading such a table into QGIS will result in the errors below.

Error messages

Fetching from cursor qgisf0 failed
Database error: ERROR:  Operation on mixed SRID geometries

Query: CLOSE qgisf0 returned 7 [ERROR:  current transaction is aborted, commands ignored until end of transaction block]

1 cursor states lost.
SQL: CLOSE qgisf0
Result: 7 (ERROR:  current transaction is aborted, commands ignored until end of transaction block)

Steps to reproduce
1. Load attached shapefile in postgis (postgis 2.0, postgres 9.1.8 on ubuntu 12.10):

shp2pgsql Grid50_RSA tempdata | psql -d <qgisdb>

2. Update the srid of all records in the table:

UPDATE testdata SET geom=ST_SetSRID(geom,4326);

3. Load in new QGIS session (either 1.8 or master) using DB Manager
4. Try to identify a feature using identify features.

Workaround
1. Add a new geometry column to the table, eg

SELECT AddGeometryColumn('testdata','the_geom',4326, 'MULTIPOLYGON', 2);

2. Transform the geometry to the new column, eg

UPDATE testdata SET the_geom = ST_Transform(geom,4326);

3. Drop the old column:

alter table testdata drop column geom;

4. Rename the new column to the old name:

alter table testdata rename column the_geom to geom;

5. Check that the following two commands give the same result:

SELECT DISTINCT ST_SRID(geom) FROM testdata;
select find_srid('public','testdata','geom');

#10 Updated by Jürgen Fischer over 11 years ago

  • Status changed from Reopened to Closed

Also available in: Atom PDF