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.
Best Answer
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.