Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
