Same data-update-behaviour for Query Feature Class / Postgresql-DB as for Enterprise Geodatabase Feature Class / ArcSDE

[nadja_swiss_parks]

We came across this inconsistent behaviour:

ArcSDE with Enterprise Geodatabase Feature Class:

We've got multiple schemata with various vector data, e.g. schema "xxx" with data "test". These data are periodically updated by its provider, who changes sometimes also the associated data design. Meaning some attributes are deleted, other attributes are added and the geometry is almost always updated. To not have to update every map which uses that data, we will replace the old "test" data with the updated data, which will also be called "test". The old "test" data will be deleted from the ArcSDE. This approach is well established with our clients.

PostgreSQL with Query Feature Class :

Recently, we tried the same approach with a PostGIS / PostgresQL Database with Query Feature Classes. Same schema "xxx", same data "test". If we replace the test-data with updated data with the same data design, it works fine. However if the data design changes (e.g. attribute deleted), the data can be displayed in ArcGIS Pro (currently 3.3.0), but the attribute table cannot be opened. the following error message is displayed: Attribute column not found.

 

We would like that it would be possible to update the data (regardless of the underlying data design) as it was possible with  Enterprise Geodatabase Feature Class in ArcSDE. 

We assume the issue originates in the Query (see Layer Properties >> Source >> Data Source >> Query) sent to the DB, which is currently 

select objectid,attr1,attr2,attr3,...,attrN from xxx.test

it lists all attributes (attr1,attr2,attr3,...,attrN) instead of using the asterix (*). if the query is manually changed so that the asterix is used, the attribute table can be opened. However the query cannot be changed a priori or per default, which makes this workaround not  a usable workaround. 

We would like to propose, that either the same data update behaviour is used for  Query Feature Class / Postgresql-DB as it was for Enterprise Geodatabase Feature Class / ArcSDE or that the used default Query can be defined inthe database or the options.

[SSWoodward]

Thanks for the idea @nadja_swiss_parks. I want to make sure I understand.

In your original configuration, you had data in an enterprise geodatabase, and this data would often be updated with new data and even schema changes to the table ( new fields or removed fields etc..) and this allowed all your maps accessing this data to be automatically updated when the data changed.

Now, you have moved your data out of an enterprise geodatabase to a standard PostgreSQL Database and you are trying to access it with a query layer.  The issue you are having is that when the data's fields change your query is no longer valid because you are specifically asking the database for fields that no longer exist. The solution you are hoping for is that the query layer will update its query to match your field changes automatically.

Is that a correct understanding of the situation?

Can you expand on why a select * query is not a viable option in this situation?

[nadja_swiss_parks]

Yes, @SSWoodward you understand correctly. 

The "select *" query is currently not a viable solution because it would have to be adjusted manually for each and every layer in each and every map - given the number of data, users and projects, this is simply not an option. The "select *" query could be a solution if a) it was the default query (why would you create a query that lists all attributes in the first place??), b) there was an option to define the default query once on the database or in ArcGIS Online for the entire organisation or c) if it could be passed as an argument in the creation of the query layer (we're using the ArcGIS SDK for .NET and arcpy).