2

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.

Improve this question
4
  • 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, 2012 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.
    – Gregory
    May 8, 2012 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, 2012 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!
    – Gregory
    May 8, 2012 at 15:07

1 Answer 1

Reset to default
2

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.

Improve this answer
5
  • 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.
    – Gregory
    May 8, 2012 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.
    – Gregory
    May 8, 2012 at 16:00
  • Does the error appear with subsets that only contain valid features?
    – underdark ♦
    May 8, 2012 at 16:03
  • 1
    No errors occur with subsets that only contain valid features.
    – Gregory
    May 8, 2012 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.
    – Gregory
    May 8, 2012 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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