How to commit a query?

[aengl]

When we upgraded from v24 to v30 we noticed that queries that run in a lambda that is terminated due to a timeout are rolled back, even though the queries are not part of a transaction.

In the simplest case, we have a lambda that looks roughly like this:

const pool = await createPool(...);
await pool.query(sql`refresh materialized view foobar`);
pool.end();
res.status(200).end();

In v24, the materialized view is always refreshed, even if it takes long enough that the lambda is terminated. In v30, the view no longer updates in cases where the lambda is terminated.

The same behaviour also applies in cases where the termination occurs after the query finishes, if another query or request delays the lambda long enough. It looks as if all queries are running in a transaction by default that is committed when the pool is closed, with no way to manually commit it before that.

I'm no longer sure how to safely implement the following scenario without resorting to a message queue:

1. Run an important query
2. Send an email that the query succeeded
3. Run an unsafe operation that may time out the lambda

If (3) fails, then (1) is rolled back, but the user already got notified that (1) succeeded.

Any help or insights are much appreciated 🙏

[gajus]

When we upgraded from v24 to v30 we noticed that queries that run in a lambda that is terminated due to a timeout are rolled back, even though the queries are not part of a transaction.

I don't believe that is the case. If you submit a PR showing otherwise, this would be a bug.

In v24, the materialized view is always refreshed, even if it takes long enough that the lambda is terminated. In v30, the view no longer updates in cases where the lambda is terminated.

If the backend is terminated, then it is expected that the materialized view would fail. It is not rolling back – the action just does not complete.

It looks as if all queries are running in a transaction by default that is committed when the pool is closed, with no way to manually commit it before that.

I don't believe that is the case. If you submit a PR showing otherwise, this would be a bug.

[aengl]

Thank you for the response!

It's possible to verify my findings with the provided code. Basically, paste the query into a JS file and terminate it before it finishes.

I'm not sure how one submits a PR showing an issue, unless you're saying that you want me to submit a PR fixing the issue. I'm happy to do so, but I'd need at least a pointer or two as to where I should start looking.

I can also provide code and deployments that prove the different behaviour definitively, if you're interested looking into this.

If the backend is terminated, then it is expected that the materialized view would fail.

The default pg library does not fail the materialized view update when the backend is terminated, and neither do earlier versions of Slonik. I had my entire team verify that fact, to be absolutely sure.

Edit: I've asked management for the possibility of setting up a sponsorship, will get back to you when I have more information 🙏

[gajus]

I'm not sure how one submits a PR showing an issue, unless you're saying that you want me to submit a PR fixing the issue.

Just add a test case that demonstrates it.

The default pg library does not fail the materialized view update when the backend is terminated

Can you clarify what you mean by "terminate backend"? Because if you are referring to pg_terminate_backend(), that would is definitely going to stop materialized view from being created.

[aengl]

Hey @gajus, sorry that it's taken so long but I've gotten green light to set up a sponsorship from Proposales. Naturally, that's not conditional on the resolution of this issue, we're going to keep sponsoring you as long as we're using Slonik internally 🙂

Just add a test case that demonstrates it.

I will look into that!

Can you clarify what you mean by "terminate backend"?

I can't say for certain what Vercel does when a lambda exceeds its maximum duration, but my guess is that they send a SIGKILL. I wouldn't know how the database connections are terminated when the process is killed. We can say with certainty that refreshing the materialized view, when started via a query using the pg library, will succeed, even if it takes longer than the maximum duration. We'd get complaints very quickly if that wasn't the case, which is what happened when we upgraded Slonik.