David, Tom,
We have been able to refactor the query to reduce the number of nested loops. With this new query, the issue is less frequent in our tests and it is taking less time when it occurs.
However, the issue still happens from time to time after an auto-vacuum/auto-analyze run when running inside a transaction.
Since it was working fine before and something happened in v11, do you think something needs to be fixed?
Let me know if I can help in any way.
Thanks,
JF
On Tue, 13 Dec 2022 at 14:46, Jean-Francois Levesque
<jf.levesque@gmail.com> wrote:
> UPDATE "shared_models_session" SET "plan_disabled" = true WHERE
> "shared_models_session"."id" IN (SELECT V0."id" FROM "shared_models_session"
> V0 INNER JOIN "shared_models_sessionmonitoring" V1 ON (V0."id" =
> V1."session_id") WHERE V1."monitoring_id" IN (SELECT U0."id" FROM
> "shared_models_monitoring" U0 WHERE U0."owner_id" = 441) FOR UPDATE OF V0)
There may be some subtle differences around when the records are
locked, but I wonder if you couldn't just write:
UPDATE "shared_models_session" SET "plan_disabled" = true
FROM shared_models_sessionmonitoring V1
WHERE "shared_models_session"."id" = V1."session_id"
AND V1."monitoring_id" IN (SELECT U0."id" FROM
"shared_models_monitoring" U0 WHERE U0."owner_id" = 441);
which I think should get around the issue of the subquery in the
original version being executed once per matching row. I'm unsure if
there might have been some good reason to form the query in the way it
was formed. Getting around some deadlocking issue with another query
seems unlikely as there is no ORDER BY clause.
I'm not seeing why the UPDATE FROM syntax can't be used. You didn't
seem to have mentioned anything about the schema design, but even if
the join has multiple rows per shared_models_session, since you're
just setting plan_disabled to true, then it does not really matter.
Any subsequent updates will just not be done because the current
command has already updated the row. The same wouldn't be true if you
were setting plan_disabled to some value from one of the other tables.
David