Horton

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

Alexey Klyukin <alexk@commandprompt.com>

View thread
From: Alexey Klyukin <alexk@commandprompt.com>
To: pgsql-hackers@postgresql.org
Date: 2011-05-12T22:09:16Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix possible "tuple concurrently updated" error in ALTER TABLE.

Attachments

Hello,

We have recently observed a problem with concurrent execution of ALTER ROLE SET... in several sessions. It's similar to the one from http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=fbcf4b92aa64d4577bcf25925b055316b978744a The result is the 'tuple concurrently updated' error message, and the problem is easily reproducible:

CREATE SCHEMA test;
CREATE SCHEMA test2;
CREATE ROLE testrole;

session 1:
while [ 1 ]; do psql postgres -c 'alter role testrole set search_path=test2';done

session 2:
while [ 1 ]; do psql postgres -c 'alter role testrole set search_path=test';done

The error message appears almost immediately on my system. 

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.


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