Обсуждение: getting "ERROR: tuple concurrently updated" during concurrent grant/revoke operations (9.2.13)

I was experiencing a problem where a script which was simultaneously
loading multiple database backups to private schemas was reliably
drawing the error, "ERROR:  tuple concurrently updated" during
grant/revoke steps of the restore.  (The problem isn't at all serious
because the impacted statement is irrelevant to this server.)  If I
create a file,

breakit.sql:
GRANT ALL ON SCHEMA public TO PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

and bench it, it immediately draws the error:
[merlin@mernix ~]# pgbench -n -c16 -T30 -f breakit.sql postgres
Client 4 aborted in state 0: ERROR:  tuple concurrently updated
Client 2 aborted in state 0: ERROR:  tuple concurrently updated
Client 0 aborted in state 0: ERROR:  tuple concurrently updated
Client 1 aborted in state 0: ERROR:  tuple concurrently updated
Client 10 aborted in state 0: ERROR:  tuple concurrently updated
Client 5 aborted in state 0: ERROR:  tuple concurrently updated
Client 7 aborted in state 0: ERROR:  tuple concurrently updated
Client 12 aborted in state 0: ERROR:  tuple concurrently updated
Client 14 aborted in state 0: ERROR:  tuple concurrently updated
Client 3 aborted in state 0: ERROR:  tuple concurrently updated
Client 11 aborted in state 0: ERROR:  tuple concurrently updated
Client 9 aborted in state 0: ERROR:  tuple concurrently updated
Client 13 aborted in state 0: ERROR:  tuple concurrently updated
Client 15 aborted in state 0: ERROR:  tuple concurrently updated
Client 8 aborted in state 0: ERROR:  tuple concurrently updated

Also confirmed this on 9.4.

merlin

Hi,

On 2015-06-24 10:43:22 -0500, Merlin Moncure wrote:
> I was experiencing a problem where a script which was simultaneously
> loading multiple database backups to private schemas was reliably
> drawing the error, "ERROR:  tuple concurrently updated" during
> grant/revoke steps of the restore.  (The problem isn't at all serious
> because the impacted statement is irrelevant to this server.)  If I
> create a file,

> breakit.sql:
> GRANT ALL ON SCHEMA public TO PUBLIC;
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
>
> and bench it, it immediately draws the error:
> [merlin@mernix ~]# pgbench -n -c16 -T30 -f breakit.sql postgres
> Client 4 aborted in state 0: ERROR:  tuple concurrently updated
> Client 2 aborted in state 0: ERROR:  tuple concurrently updated

To me that's not a bug. There's lots and lots of ways to get 'ERROR:
tuple concurrently updated' errors when doing catalog changes
concurrently. There's been a fair amount of work (particularly from
Robert) reducing the amount; but it's never been claimed to be complete.

I do think it'd be a good idea to fix occurrances, but I don't think
that's stuff amounting to a bug, which'd then entail backpatching.

In the specific case here you have an actual problem with your data
loading procedures - if they concurrenty affect the same schema,
including revoking permisssions - you'll possibly have more troubles
than just this. Actual users of, in this case, the public schema in
already existing schemas won't like this for example. And there
e.g. very well might be an extension in public schema.

Greetings,

Andres Freund

On Wed, Jun 24, 2015 at 10:51 AM, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On 2015-06-24 10:43:22 -0500, Merlin Moncure wrote:
>> I was experiencing a problem where a script which was simultaneously
>> loading multiple database backups to private schemas was reliably
>> drawing the error, "ERROR:  tuple concurrently updated" during
>> grant/revoke steps of the restore.  (The problem isn't at all serious
>> because the impacted statement is irrelevant to this server.)  If I
>> create a file,
>
>> breakit.sql:
>> GRANT ALL ON SCHEMA public TO PUBLIC;
>> REVOKE ALL ON SCHEMA public FROM PUBLIC;
>>
>> and bench it, it immediately draws the error:
>> [merlin@mernix ~]# pgbench -n -c16 -T30 -f breakit.sql postgres
>> Client 4 aborted in state 0: ERROR:  tuple concurrently updated
>> Client 2 aborted in state 0: ERROR:  tuple concurrently updated
>
> To me that's not a bug. There's lots and lots of ways to get 'ERROR:
> tuple concurrently updated' errors when doing catalog changes
> concurrently. There's been a fair amount of work (particularly from
> Robert) reducing the amount; but it's never been claimed to be complete.
>
> I do think it'd be a good idea to fix occurrances, but I don't think
> that's stuff amounting to a bug, which'd then entail backpatching.

right, makes sense.

> In the specific case here you have an actual problem with your data
> loading procedures - if they concurrenty affect the same schema,
> including revoking permisssions - you'll possibly have more troubles
> than just this. Actual users of, in this case, the public schema in
> already existing schemas won't like this for example. And there
> e.g. very well might be an extension in public schema.

That's pretty much correct.  I use 'sed' to route public schema to a
private one for various backups.  It works, but the GRANT/REVOKE
statements I didn't bother to route due to the fact this is an 'all
superuser' database.  Thus, the error is completely harmless.

merlin