Nodejs on-page data does not update with laggy external DB, including sockets

[xsfizzix]

Potential bug, but I’m leaving this off the “bugs” category as I might be missing something.

Wappler Version : 5.2.3
Operating System : Windows 10
Server Model: Nodejs
Database Type: External Postgres
Hosting Type: Docker

Expected behavior

Server API waits for external DB query to complete before issuing response, or firing next step (including a socket refresh)

Actual behavior

Server API sends response or executes next action well before external query completes

I’ve setup a simple POST API using an external DB with a (rather laggy) connection time. The roundtrip time is about 2s. The API simply performs an insert.

A fresh form page that simply executes the API and refreshes a pull of the table contents.

Action: when the form is submitted, the API post returns a response instantly, then attempts a reload of the SC, well-before the DB insert actually completes.

The crude fix on the forums is to put an endlessly-repeating Action Schedule on-page to refresh the SC, which is too resource-intensive for heavy queries and not ideal for messaging scenarios. Hence sockets. However the issue still persists…

Re-wired the API to use a socket:

Then set the SC on-page to live-refresh via socket and removed the re-load SC from the form-submit on-success action flow.

However, when submitting the form, the SC still DOES NOT load the fresh record. The below vid demonstrated the behavior, and only displays the newly-inserted record when the page is fully-refreshed.

What’s occurring is the API is calling the Refresh Server Action before the laggy DB query completes, rendering the implementation unusable. If I manually add a Wait in the API, which is still undesirable due to not knowing the variable lag-time, the issue surprisingly still persists.

Am I missing something? Or does the API truly not wait for laggy DB connections to close and commit?

[xsfizzix]

(Additionally, if I deploy to a live enviro better-geo-located to the DB, where the roundtrip time is about 0.25 sec, everything works just fine (both the sockets method and the on-sucess SC load action flow). So this issue appears only when using a laggy external DB)

[patrick]

We always wait for the response of the database driver. Which database do you use? Perhaps the database still holds a lock on the table and the select query returns a snapshot from before the insert because of the lock. Behavior can be different per database and is difficult to tell exactly what the exact cause of your problem would be.

[xsfizzix]

Thanks for the reply Patrick. This is a Postgres 14.5 DB. I noticed the API POST would respond in about 100-200ms (as can be seen in the vid), when I knew in fact there was at least a few seconds of connection time. I swapped the insert out with a long-running stored procedure (4-5 seconds) and the POST still returned in 200ms. which doesn’t make sense if the implementation was waiting for the driver to close.

I proceeded to alter the API into a GET and run the same, to see if I could force a different behavior, but the same thing still occurred.

Again, as soon as I co-located the web service to the hosted DB, everything instantly resolved. For this project, that’s perfectly fine so I’m leaving the two geo-located and trucking on. Just wanted to throw a shout-out just in case, for the next project that might require a geo-fenced data storage or a super-laggy pre-existing DB instance. Regardless, I appreciate your response and thanks much.