Horton

Re: [BUGS] Concurrent ALTER SEQUENCE RESTART Regression

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers@postgresql.org, Noah Misch <noah@leadboat.com>, Jason Petersen <jason@citusdata.com>, Michael Paquier <michael.paquier@gmail.com>, PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Date: 2017-05-10T14:29:02Z
Lists: pgsql-bugs, pgsql-hackers 
Petr Jelinek <petr.jelinek@2ndquadrant.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?

If we were taking AccessExclusiveLock somewhere, I'd be worried about
the cost of WAL-logging those; but this proposal does not include any.

			regards, tom lane

Commits

  1. Make ALTER SEQUENCE, including RESTART, fully transactional.

  2. Modify sequence catalog tuple before invoking post alter hook.

  3. Use weaker locks when updating pg_subscription_rel

  4. Add pg_sequence system catalog

  5. Modify sequence state storage to eliminate dangling-pointer problem