Querying multifield function using .* makes multiple requests to the provider

[ethervoid]

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:

16:37:07.534849 IP dbd-services-1-st.36786 > server-54-230-130-184.ams50.r.cloudfront.net.https: Flags [S], seq 3724214428, win 29200, options [mss 1460,sackOK,TS val 2071754913 ecr 0,nop,wscale 7], length 0
16:37:07.667390 IP dbd-services-1-st.36796 > server-54-230-130-184.ams50.r.cloudfront.net.https: Flags [S], seq 296619264, win 29200, options [mss 1460,sackOK,TS val 2071755046 ecr 0,nop,wscale 7], length 0
16:37:07.773261 IP dbd-services-1-st.36806 > server-54-230-130-184.ams50.r.cloudfront.net.https: Flags [S], seq 2118231246, win 29200, options [mss 1460,sackOK,TS val 2071755152 ecr 0,nop,wscale 7], length 0

And that is cause by the unnest we can do for the returned record of the function. Let me explain. If we do:

SELECT duration, length, shape FROM cdb_route_point_to_point('POINT(-3.70237112 40.41706163)'::geometry,'POINT(-3.69909883 40.41236875)'::geometry, 'car')

everything is fine, we make a single request but if we do:

SELECT (cdb_route_point_to_point('POINT(-3.70237112 40.41706163)'::geometry,'POINT(-3.69909883 40.41236875)'::geometry, 'car')).*

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

[jaakla]

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.

[ethervoid]

Yes, that would be great to be sincere. We could debounce the same request and cache it :)