Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to create a vector layer in a QGIS project using a PostGIS query. QGIS is returning the following error:

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

I can't figure out what this error means, and I have no trouble querying the same rows, and presumably the same columns, using psql. Does anyone have an idea about what's going on here?


Update: The original query's where clause looks like:

colname in ('value1', 'value2', ..., 'value40')

I've been able to add QGIS layers using subsets of the list in the where clause, suggesting that there may be a problem either with individual rows. That hypothesis appears to be falsified by the following test, however.

I presume the QGIS query looks like

select * from tablename where colname in ...;

I can run exactly that query in a psql interactive session and it returns a result set without complaint. This observation suggests that either QGIS is not running the query I think it is, or something else is going on between QGIS and PostGIS.

I mentioned above that I can use subsets of the list in the where clause and I'm working to add each individually as its own layer to see if it balks at one of them. Unfortunately that's a time consuming task to which I can't devote myself exclusively, but I'll post another update when it's done.

share|improve this question
2  
It means there was an error with your query. How did you run the query in QGIS? While connecting from QGIS, are you using a different username than "postgres"? –  Mike T May 8 '12 at 5:34
    
@MikeToews - I'm creating the query by the following steps. 1) click "add PostGIS layer" 2) connect to server and choose schema 3) click "build query" 4) paste where clause into query window from text editor. I am not connecting as the postgres user; I'm not the admin for this server and don't have the postgres user's login credentials. I am the database owner, however, permissions aren't the issue. See update for more info. –  pyrogerg May 8 '12 at 13:47
    
Since you can successfully load supbsets, try checking if all geometries in the table are valid, e.g. using st_isvalid(). Invalid features might upset it. –  underdark May 8 '12 at 14:34
    
@underdark, Thanks for informing me about st_isvalid(), a very useful function. It appears that not all of the geometries are valid. Later today I'll figure out which subsets of the data are invalid and why and may start a new thread about whether they can be repaired or should be eliminated. If knowing that some rows contain invalid geometry is sufficient to explain the error message in my original post, then please post an answer so that I can accept it. Cheers! –  pyrogerg May 8 '12 at 15:07

1 Answer 1

up vote 2 down vote accepted

Since you can successfully load subsets, try checking if all geometries in the table are valid, e.g. using ST_IsValid().

Invalid features might upset QGIS.

Another problem source I have encountered is specifying a wrong CRS for the geometry column. But that's probably not the case here since subsets work.

share|improve this answer
    
ST_IsValid() indicates that invalid geometries exist in the table. The CRS is unlikely to be a problem because SRID=4326 is enforced as a constraint on the geometry column is PostgreSQL and PostGIS. –  pyrogerg May 8 '12 at 15:29
    
Surprisingly, I was able to load subsets of the query results that contained invalid geometry without generating an error. I didn't notice this at the time, because layers were stacking up and I wasn't attending to what new ones looked like. The error message appeared only when I attempted to view the attribute table for that layer in QGIS. –  pyrogerg May 8 '12 at 16:00
    
Does the error appear with subsets that only contain valid features? –  underdark May 8 '12 at 16:03
1  
No errors occur with subsets that only contain valid features. –  pyrogerg May 8 '12 at 16:19
1  
Note for other beginners: see workshops.opengeo.org/postgis-intro/validity.html for more info on checking geometry validity and identifying the causes of invalid geometries. –  pyrogerg May 8 '12 at 19:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.