Re: getting "ERROR: tuple concurrently updated" during concurrent grant/revoke operations (9.2.13)

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