Re: [PATCH] Add GUCs for predicate lock promotion thresholds

From: Kevin Grittner <kgrittn(at)gmail(dot)com>
To: Dagfinn Ilmari Mannsåker <ilmari(at)ilmari(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Add GUCs for predicate lock promotion thresholds
Date: 2017-01-23 18:32:42
Message-ID: CACjxUsO9sswX+0UWwDWjF_mCOnw3CnVM0ex9d+a7Wj=jzt09bw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Jan 23, 2017 at 9:50 AM, Dagfinn Ilmari Mannsåker
<ilmari(at)ilmari(dot)org> wrote:

> [new patch addressing issues raised]

Thanks!

>> In releases prior to this patch, max_pred_locks_per_relation was
>> calculated as "max_pred_locks_per_transaction / 2". I know that
>> people have sometimes increased max_pred_locks_per_transaction
>> specifically to raise the limit on locks within a relation before
>> the promotion to relation granularity occurs. It seems kinda
>> anti-social not to support a special value for continuing that
>> behavior or, if we don't want to do that, at least modifying
>> pg_upgrade to set max_pred_locks_per_relation to the value that was
>> in effect in the old version.
>
> This is exactly what we've been doing at my workplace

It occurred to me that it would make sense to allow these settings
to be attached to a database or table (though *not* a role). I'll
look at that when I get back if you don't get to it first.

>>> ilmari(at)ilmari(dot)org (Dagfinn Ilmari Mannsåker) writes:
>>>> One thing I don't like about this patch is that if a user has
>>>> increased max_pred_locks_per_transaction, they need to set
>>>> max_pred_locks_per_relation to half of that to retain the current
>>>> behaviour, or they'll suddenly find themselves with a lot more
>>>> relation locks. If it's possible to make a GUCs default value
>>>> dependent on the value of another, that could be a solution.
>>>> Otherwise, the page lock threshold GUC could be changed to be
>>>> expressed as a fraction of max_pred_locks_per_transaction, to keep
>>>> the current behaviour.
>>
>>> Another option would be to have a special sentinel (e.g. -1) which is
>>> the default, and keeps the current behaviour.
>
> The attached updated patch combines the two behaviours. Positive values
> mean an absolute number of locks, while negative values mean
> max_predicate_locks_per_transaction / -max_predicate_locks_per_relation.
> Making the default value -2 keeps backwards compatibility.
>
> Alternatively we could have a separate setting for the fraction (which
> could then be a floating-point number, for finer control), and use the
> absolute value if that is zero.

I will need some time to consider that....

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Corey Huinker 2017-01-23 18:34:50 Re: Allowing nonzero return codes from \quit
Previous Message David G. Johnston 2017-01-23 18:31:54 Re: Undefined psql variables