Context:
I have essentially a background maintenance script that is run through a pg_cron entry. It does inserts and updates and therefore has the potential to lock tables/rows some of the time. This script is low-priority as compared to the rest of the mostly-read demand on the database.
Question:
At a high level, I would like to know if there is something I can do that will make it so that whenever the maintenance script does something to block a read then it stops what it is doing and gets out of the way. Similar to how AUTOVACUUM works.
Example:
As an example, I am imagining a SESSION variable that sets the lock time tolerance to be very small but which makes the lockING command fail rather than the blocked command. This is where the priority concept comes in. I cannot find anything that exists like this.
Or perhaps is there another way to accomplish this that achieves the same end goal?
No, there is no such setting which makes the blocking command fail rather the blocked one. A way around it would be to keep the transaction small, so not many rows are locked at once, and not for long periods. You could perhaps make t turn synchronous_commit off in the session, to avoid the storm of fsync requests. It does mean you might need some mechanism to repeat work that got lost in a crash. You do have the advantage over VACUUM that it needs a much stronger table lock than either INSERT or UPDATE does. So you mostly only need to worry bout row locks, not table locks.