Esri Specs:
ArcGIS Server 11.1, ArcPro 3.1
PostGIS running on AWS Aurora. Not a managed geodatabase. DB views having tables, tables having geography columns, with some foreign data wrapper foreign tables joined, pointing to other systems for the per-user permissions content.
We have a unique relational data model, where the features in layers change based on the user viewing them. The primary layers are groups, users, and devices. A group has users, a user has devices. Data is segmented by customer_id.
There are 2 types of users - full administrators can see everything for their customer_id. Restricted administrators can only see certain records, based on dynamic/realtime membership in groups for their customer_id.
No user can see data for all customer_ids.
We have 2 sets of database views - for both sets of views, they are ONLY valid when a where clause restricts the data returned.
Full admin views - must include filter where customer_id = 123
Restricted admin views - must include filter where customer_id = 123 and admin_id = 456
If we were to query these views without a where clause, we are requesting an insane amount of data, beyond the capacity of the systems to deliver it. When publishing services having query layers to these views, the service times out, and will not start up.
For each layer in the service definition, when the service starts esri is asking postgis - "select * from massiveview". This is an impossible query to request for these views.
One workaround has been to have a view on top of a view - before publishing the service, we make a view
CREATE VIEW vw_all_addresses_layer_v1 as
(
select id, name, description
from all_addresses_layer
where customer_id = 123
);
and then after the service is published and started, we recreate the view to exclude the where clause
DROP VIEW IF EXISTS vw_all_addresses_layer_v1;
CREATE VIEW vw_all_addresses_layer_v1 as
(
select id:, name, description
from all_addresses_layer
);
By doing this, we are able to have the service start up, because the select * query is reasonable.
After publishing, if we query the service using the proper filters, the service responds quickly. However, we cannot ever stop and start (or recycle) this service, because it would timeout, due to the massive amount of data.
Why are "select * from queryLayer" queries being executed?
How can we prevent these "select * from queryLayer" queries from being executed?
When are they executed during a service's lifecycle? (upon start, upload uploading service definition, upon recycling)? When more instances spin up? Just the first instance?
Is there data captured from these select * requests, and stored in a file somewhere, that we could pre-populate with content, so that the query is not executed during service start up? Any count that is captured by a select * would not be valid.
Why do we see queries like this: SELECT * FROM sde.sde_layers LIMIT 0 when we are not using SDE? If we had this SDE table, would the "select *" queries still occur?
Last question - does ArcGIS server have a preference for using geometry or geography as the spatial data type? We see requests like this for tables having geography data: SELECT ST_GEOMETRYTYPE(pin::GEOMETRY), ST_SRID(pin) FROM public.vw_all_geoaddresses_layer_esri WHERE pin is not null limit 1;
Thank you, this is a big bottleneck in our potential adoption of this technology.
@TanuHoque your named was mentioned on a call earlier as an SME on this topic.
