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 <t...@sss.pgh.pa.us> wrote: > Adrian Klaver <adrian.kla...@aklaver.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 >
