Horton

Re: Concurrent ALTER SEQUENCE RESTART Regression

Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

View thread View on postgr.es
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Jason Petersen <jason@citusdata.com>, pgsql-bugs@postgresql.org
Date: 2017-05-02T14:53:19Z
Lists: pgsql-bugs, pgsql-hackers 
On 4/24/17 15:52, Jason Petersen wrote:
>   1. Create a new sequence: CREATE SEQUENCE my_seq;
>   2. Start this loop twice in different shells:
>        while true; do psql -1Xtc 'ALTER SEQUENCE my_seq RESTART 1'; done

> Each loop should repeatedly succeed and simply print ALTER SEQUENCE over and over.

> The output stream is punctuated by occasional "ERROR:  tuple concurrently updated" messages.

This message comes from the pg_sequence catalog update.  But in the case
of the RESTART clause, you don't need to update the catalog, because it
just needs to write to the sequence's relation.  So I have tweaked the
code a little to omit the catalog update if it's not needed.  Your test
case works without errors now.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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