Horton

Re: Concurrent ALTER SEQUENCE RESTART Regression

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Michael Paquier <michael.paquier@gmail.com>, Jason Petersen <jason@citusdata.com>, PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Date: 2017-04-27T05:58:06Z
Lists: pgsql-bugs, pgsql-hackers 
On 2017-04-26 22:07:03 -0400, Peter Eisentraut wrote:
> On 4/26/17 21:12, Andres Freund wrote:
> > I think it's unacceptable to regress with an error message here.  I've
> > seen sequence DDL being used while concurrent DML was onging in a number
> > of production use cases, and just starting to error out instead of
> > properly blocking doesn't seem acceptable to me.
> 
> It's not clear to me what the use case is here that we are optimizing
> for.  The best solution would depend on that.  Running concurrent ALTER
> SEQUENCE in a tight loop is probably not it. ;-)

Oh, and there's absolutely no need for a loop or anything:

A: CREATE SEQUENCE someseq
A: BEGIN;
A: ALTER SEQUENCE someseq RESTART ;
B: ALTER SEQUENCE someseq RESTART ;
A: COMMIT;
B: ERROR:  XX000: tuple concurrently updated

- Andres

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