When I connect to my PostgreSQL (PostGIS) database then everything seems fine until I basically add data to the map in ArcGIS Pro 3.3.
While at a first glance it seems all good, the diagnostic monitor shows the following:
Database: ip-address -> SQL: SELECT * FROM db_name.schema.table -> Number of rows returned: 0
That is strange since when I visualize the features in the Query Feature Class then it shows all the features (103.609). But when I open the attribute table then I am getting a warning: Table -> Failed to open table., yet in the background the attribute table loads and is populated.
In the same context I have also seen an error message:
Cancelling statement due to user request::SQL STATE=57014
The moment I add a definition query to the class it is getting pretty interesting when I try to see attribute data...
- I am getting again the warning Table -> Failed to load the data.
- More info on the warning reveals: Failed to retrieve a page of rows. The event class for this subscription is in an invalid partition
- When I click OK on the warning,
- the attribute table is closed
- the map shows the correct features according to the definition query
Interesting is, that when I create the definition query then I can select from the correct values in the value field of the WHERE statement, and the diagnostics monitor returns the correct count of rows from the DISTINCT statement against that column.
But when I apply the definition query then the statement issued against the database is the following:
SQL: SELECT ESRI_OID FROM db_name.schema.table WHERE column_name = selected_value ORDER BY ESRI_OID LIMIT 1
The number of rows returned is 1
There should be thousands...
Then I confirm the properties dialog of the Query Feature Class, and the diagnostics monitor shows a new SQL statement issued:
SQL: SELECT * FROM db_name.schema.table -> Number of rows returned: 0
Looking at help pages I get the advice the check the accessibility of the data, especially if the data is on a shared drive or database server.
- Regarding the rights of the user connecting successfully to the database, I am testing currently with admin rights, and I am the owner of the data.
- Regarding the error 57014, several hints are pointing at npgsql, but that's where I am out, because I have no idea if ESRI is using npgsql or not.
Anybody here who had the same problem, found a solution and solved it? @George_Thompson or @AyanPalit maybe?
- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
