Error when batching Bulk Upsert with Primary Key in Workflows

Queries and Resources
,
1

Continuing the discussion from Parameter Limit on Bulk Insert:

I need to upsert thousands of records into an SQL Server table. My requirements are simple. Retrieve rows from one table and insert them into another; if they already exist, update them.

This works perfectly when upserting a few hundred records, but at 1500 records, it lags a little, and at 5000 records, it hangs and never finishes.

I decided to batch the upsert as described by @yiga2 and @Kabirdas in the above thread. But when I run the loop, I get an error.

undefined
Did you forget to include a return statement?

Below is a screenshot of my loop block.

image
image591×642 29.5 KB

Suggestions appreciated. I considered reducing the number of records earlier in the workflow, but upserting the entire set is ideal for our purposes.

2

Latest news!

We had an upgrade scheduled, which support thought might help. And it did. Now, the loop upsert does run, but it has the same behavior as the "standard block" bulk upsert: at 5k records, it runs until the time out (60 seconds.) It also has new unwanted behavior that leaves all the rows it did insert locked, and I have to get a DBA to bail me out.

Have I misunderstood batch and loop? I assumed the timeout would reset with each iteration. This perhaps imagined functionality was the reason I explored loop and batch to begin with.

Edit: It looks I did not understand loop. :man_facepalming:

3

Solved. Thank you to Retool Support and posts in this community.

We learned the issue was a 504 Gateway Timeout. This was not obvious in the Workflows Resource Query component before our upgrade to 3.26.4, but we may have overlooked it.

With that information, we turned up this community article and this public one.

We added the settings below to nginx.conf and did a docker-compose restart. After that, we upserted 10k records without issue.

proxy_connect_timeout       300;
proxy_send_timeout          300;
proxy_read_timeout          300;
send_timeout                300;

No loops or batches were needed. It was a big time sink, but I learned a lot.

Thanks,

Andrew

2 Likes
4

@awilly thank you so much for posting about this, and especially for taking the time to circle back to share the progress and the solution!

Really glad that a combination of existing Retool community forum topics, other general public articles, and talking with our team helped out here. Your comments will help others learn and get unblocked going forward :rocket:

Our Workflows engineers also have visibility into these forum topics (as do engineers working on other areas of the product) and I know they learn a ton from customers' experiences, influencing their next product improvements. So all this to say - thanks again for capturing these learnings, and see you around the forums!

1 Like