Re: autovacuum truncate exclusive lock round two

Jan Wieck wrote:

> Thinking about it, I'm not really happy with removing the
> autovacuum_truncate_lock_check GUC at all.
>
> Fact is that the deadlock detection code and the configuration
> parameter for it should IMHO have nothing to do with all this in
> the first place. A properly implemented application does not
> deadlock.

I don't agree. I believe that in some cases it is possible and
practicable to set access rules which would prevent deadlocks in
application access to a database. In other cases the convolutions
required in the code, the effort in educating dozens or hundreds of
programmers maintaining the code (and keeping the training current
during staff turnover), and the staff time required for compliance
far outweigh the benefit of an occasional transaction retry.
However, it is enough for your argument that there are cases where
it can be done.

> Someone running such a properly implemented application should be
> able to safely set deadlock_timeout to minutes without the
> slightest ill side effect, but with the benefit that the deadlock
> detection code itself does not add to the lock contention. The
> only reason one cannot do so today is because autovacuum's
> truncate phase could then freeze the application with an
> exclusive lock for that long.
>
> I believe the check interval needs to be decoupled from the
> deadlock_timeout again.

OK

> This will leave us with 2 GUCs at least.

Hmm. What problems do you see with hard-coding reasonable values?
Adding two or three GUC settings for a patch with so little
user-visible impact seems weird. And it seems to me (and also
seemed to Robert) as though the specific values of the other two
settings really aren't that critical as long as they are anywhere
within a reasonable range. Configuring PostgreSQL can be
intimidating enough without adding knobs that really don't do
anything useful. Can you show a case where special values would be
helpful?

-Kevin