Re: Concurrent ALTER SEQUENCE RESTART Regression

From: Andres Freund <andres(at)anarazel(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org, Noah Misch <noah(at)leadboat(dot)com>, Jason Petersen <jason(at)citusdata(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Concurrent ALTER SEQUENCE RESTART Regression
Date: 2017-05-10 16:24:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

On 2017-05-10 10:29:02 -0400, Tom Lane wrote:
> Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com> writes:
> > On 10/05/17 07:09, Peter Eisentraut wrote:
> >> I think the correct fix is to have nextval() and ALTER SEQUENCE use
> >> sensible lock levels so that they block each other. Since
> >> nextval() currently uses AccessShareLock, the suggestion was for
> >> ALTER SEQUENCE to therefore use AccessExclusiveLock. But I think a
> >> better idea would be for nextval() to use RowExclusiveLock
> >> (analogous to UPDATE) and ALTER SEQUENCE to use
> >> ShareRowExclusiveLock, which would also satisfy issue #1.
> > When I proposed this upstream, Andres raised concern about performance
> > of nextval() if we do this, did you try to run any benchmark on this?
> As long as it doesn't block, the change in lock strength doesn't actually
> make any speed difference does it?

The issue isn't the strength, but that we currently have this weird
hackery around open_share_lock():
* Open the sequence and acquire AccessShareLock if needed
* If we haven't touched the sequence already in this transaction,
* we need to acquire AccessShareLock. We arrange for the lock to
* be owned by the top transaction, so that we don't need to do it
* more than once per xact.

This'd probably need to be removed, as we'd otherwise would get very
weird semantics around aborted subxacts. Upthread I theorized whether
that's actually still meaningful given fastpath locking and such, but I
guess we'll have to evaluate that.


Andres Freund

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-05-10 17:28:39 Re: [HACKERS] Concurrent ALTER SEQUENCE RESTART Regression
Previous Message Tom Lane 2017-05-10 14:29:02 Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2017-05-10 16:38:54 Re: [POC] hash partitioning
Previous Message Alvaro Herrera 2017-05-10 16:12:23 Re: Adding support for Default partition in partitioning