ERROR: tuple concurrently updated when modifying privileges

On Tue, Apr 30, 2019 at 11:26 AM nickb <nickb@imap.cc> wrote:

Hello, hackers

we witnessed this slightly misleading error in production and it took us a
while to figure out what was taking place.
Below are reproduction steps:

-- setup
create table trun(cate int4);

-- session 1
begin;
truncate table trun;

-- session 2
grant insert on table trun to postgres;

-- session 1
end;

-- session 2:
ERROR: XX000: tuple concurrently updated
LOCATION: simple_heap_update, heapam.c:4474

Apparently the tuple in question is the pg_class entry of the table being
truncated. I didn't look too deep into the cause, but I'm certain the error
message could be improved at least.

Having thought about this a bit, I think the best solution would be to have
grant take out an access share lock to the tables granted. This would
prevent concurrent alter table operations from altering the schema
underneath the grant as well, and thus possibly cause other race conditions.

Any thoughts?

Regards,
Nick.

--
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin

On Tue, May 14, 2019 at 08:08:05AM +0200, Chris Travers wrote:

Having thought about this a bit, I think the best solution would be to have
grant take out an access share lock to the tables granted. This would
prevent concurrent alter table operations from altering the schema
underneath the grant as well, and thus possibly cause other race conditions.

Any thoughts?

"tuple concurrently updated" is an error message which should never be
user-facing, and unfortunately there are many scenarios where it can
be triggered by playing with concurrent DDLs:
/messages/by-id/20171228063004.GB6181@paquier.xyz

If you have an idea of patch, could you write it? Having an isolation
test case would be nice as well.
--
Michael

On Tue, May 14, 2019 at 9:11 AM Michael Paquier <michael@paquier.xyz> wrote:

On Tue, May 14, 2019 at 08:08:05AM +0200, Chris Travers wrote:

Having thought about this a bit, I think the best solution would be to

have

grant take out an access share lock to the tables granted. This would
prevent concurrent alter table operations from altering the schema
underneath the grant as well, and thus possibly cause other race

conditions.

Any thoughts?

"tuple concurrently updated" is an error message which should never be
user-facing, and unfortunately there are many scenarios where it can
be triggered by playing with concurrent DDLs:
/messages/by-id/20171228063004.GB6181@paquier.xyz

If you have an idea of patch, could you write it? Having an isolation
test case would be nice as well.

I will give Nick a chance to do the patch if he wants it (I have reached
out). Otherwise sure.

I did notice one more particularly exotic corner case that is not resolved
by this proposed fix.

If you have two transactions with try to grant onto the same pg entity
(table etc) *both* will typically fail on the same error.

I am not sure that is a bad thing because I am not sure how concurrent
grants are supposed to work with MVCC but I think that would require a
fundamentally different approach.

--
Michael

--
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin