Open ethervoid opened 6 years ago
It may be even SQL/Postgres level issue - it is easy to write accidentally a bit different query which calls same method several times with same parameters? Postgres expects by design function calls to be 'cheap' - also issue in postgis query planning.
Universal solution might be to add blocking cache feature to pl/proxy, so our quite expensive proxied function calls would be shielded from such issues. It would be applicable for geocoding, routing etc. Specific remedy to rewrite our queries would be simpler to implement through.
Yes, that would be great to be sincere. We could debounce the same request and cache it :)
While I was working with routing I notice that for every routing we did it was translated to 3 requests to the provider (in this case MapBox)
So I started investigating if we have some problem collecting metrics or if we were indeed doing 3 requests to the routing provider.
I checked for one Kibana and I did an isolated request confirming we were doing 3 requests
I went to the staging server and started sniffing traffic to the provider API in order to confirm we were doing 3 requests and yes we are. Here are the three syncs:
And that is cause by the unnest we can do for the returned record of the function. Let me explain. If we do:
everything is fine, we make a single request but if we do:
we do 3 requests, my guess is one per field.
My main suspect is pl/proxy and how it works with this kind of special unnesting but I need to dig deeper