Re: 'tuple concurrently updated' error when granting permissions

Thanks Tom et al,
I appreciate the explanation.
I am certainly more appreciative of what is going on behind the scenes now
when I see 'tuple concurrently updated' errors.

I couldn't initially find a way to grant select on all present *and
future*tables in a schema, which is why we took the approach we did.
Now I have
discovered 'ALTER DEFAULT PRIVILEGES' which will allow me to remove these
concurrent permission grants - I only need to exec one ALTER DEFAULT
PRIVILEGES command when the schema is created.

Much obliged,
Jason

On Sun, Mar 2, 2014 at 6:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 02/27/2014 06:54 PM, Jason Daly wrote:
> >> When I attempt to execute the non-query "GRANT SELECT ON TABLE
> >> <schema>.<table> TO <role>", I occasionally encounter an exception from
> >> postgresql, 'tuple concurrently updated'.
>
> > My guess this is covered here:
> > http://rhaas.blogspot.com/2013/07/mvcc-catalog-access.html
>
> While MVCC catalog scans are a necessary step towards making this kind of
> thing safe, they aren't by any means sufficient. It'd be necessary to add
> some kind of locking scheme if you want to avoid "tuple concurrently
> updated" errors. This is not really any different from the situation
> where two transactions both want to update the same row in a user table:
> unless the application takes extra steps to serialize the updates, you're
> going to get "tuple concurrently updated" errors.
>
> We do have such locking for DDL on tables/indexes, but the theory in the
> past has been that it's not worth the trouble for objects represented by
> single catalog rows, such as functions or roles. You can't corrupt the
> database with concurrent updates on such a row, you'll just get a "tuple
> concurrently updated" error from all but the first-to-arrive update.
> So the benefit-to-work ratio hasn't been high enough to motivate anyone
> to work on it. Possibly this will change sometime in the future, but
> I wouldn't hold my breath waiting.
>
> In the meantime, you could consider using an application-managed advisory
> lock if you really need such grants to work transparently.
>
> However, might I suggest that you're doing it wrong? If you have enough
> traffic on permission grants for a single table that concurrent updates
> are a real hazard, it would likely be much better to invent a group role
> that holds a relatively static set of rights, and implement the everyday
> permissions changes by granting or revoking membership in the group role.
> Not only is this using SQL permissions the way they were meant to be used,
> but it should remove the concurrent-updates problem, because role
> memberships are represented by distinct rows in pg_auth_members. Also,
> our implementation isn't terribly efficient for cases where lots and lots
> of roles have separate permissions to a single object --- I think the acl
> arrays are just searched linearly, and in any case performance would
> certainly go south once they got big enough to require toasting.
>
> regards, tom lane
>