On 5 April 2016 at 17:43, Rod Taylor <rod.tay...@gmail.com> wrote: > The intention of this feature is to give the ability to slip into a normal > workload for non-urgent maintenance work. In essence, instead of lock > waiters being in a Queue, DEFERRABLE causes the current lock statement to > always be last. >
Good idea; this was on my list of things to implement. I was going to call it WAIT PATIENTLY option. > It was discussed at last years pgCon as useful for replication tools > adding/removing triggers. I've also seen more than one plpgsql loop using > subtransactions and LOCK TABLE .. NOWAIT to achieve a similar effect. IMO, > it's much cleaner built in. > Agreed, but your implementation is essentially just the same looping concept, which I don't much like. > If a lock is successfully obtained on one table, but not on all tables, it > releases that lock and will retry to get them as a group in the future. > Since inheritance acts as a group of tables (top + recursive cascade to > children), this implementation is necessary even if only a single table is > specified in the command. > I'd prefer to see this as a lock wait mode where it sits in the normal lock queue BUT other lock requestors are allowed to queue jump past it. That should be just a few lines changed in the lock conflict checker and some sleight of hand in the lock queue code. That way we avoid the busy-wait loop and multiple DEFERRABLE lock waiters queue up normally. -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services