Re: 'tuple concurrently updated' error for alter role ... set

On May 13, 2011, at 1:28 AM, Tom Lane wrote:

> Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
>> After digging in the code I've found that a RowExclusiveLock is acquired on a pg_db_role_setting table in AlterSetting(). While the name of the locks suggests that it should conflict with itself, it doesn't. After I've replaced the lock in question with ShareUpdateExclusiveLock, the problem disappeared. Attached is the simple patch with these changes.
>
> We're not likely to do that, first because it's randomly different from
> the handling of every other system catalog update, and second because it
> would serialize all updates on this catalog, and probably create
> deadlock cases that don't exist now. (BTW, as the patch is given I'd
> expect it to still fail, though perhaps with lower probability than
> before. For this to actually stop all such cases, you'd have to hold
> the lock till commit, which greatly increases the risks of deadlock.)

Fair enough. I think the AlterSetting holds the lock till commit (it does
heap_close with NoLock). The DropSetting doesn't do this though.

>
> I see no particular reason why conflicting updates like those *shouldn't*
> be expected to fail occasionally.

Excellent question, I don't have enough context to properly answer that (other
than a guess that an unexpected transaction rollback is too unexpected :))
Let me ask the customer first.

--
Alexey Klyukin
The PostgreSQL Company - Command Prompt, Inc.