Re: Concurrent ALTER SEQUENCE RESTART Regression

On 5/2/17 11:07, Andres Freund wrote:
> On 2017-05-02 10:53:19 -0400, Peter Eisentraut wrote:
>> 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.
> 
> Wait, how does this *actually* solve anything, but scratch at the
> surface?  You just add a MAXVALUE and it starts failing (and not being
> adhered to) again?

The just committed patch somewhat disentangles the transactional from
the nontransactional parts of ALTER SEQUENCE.

RESTART is a nontransactional action.  Now you get the same concurrency
behavior for RESTART as you would for setval and nextval.  This was not
the case prior to the fix.

Other clauses such as MAXVALUE are transactional actions.  You get the
same concurrency behavior as for most DDL in PostgreSQL.  You could
argue that the RowExclusiveLock on pg_sequence in not enough and should
be perhaps ShareRowExclusiveLock to avoid "tuple concurrently updated"
messages.  But just over in 521fd4795e3ec3d0b263b62e5eb58e1557be9c86 it
was argued that that sort of thing was undesirable.

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