0

I would like to grant read access to all tables in my schema

GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX;

While it works on our alpha environment, it fails with our production database:

[XX000] ERROR: tuple concurrently updated

There are some examples how to solve regular queries, but when it comes to GRANT commands it is getting more difficult. According to this so question I understood that another connection is granting/revoking permissions at the same time, something I hardly believe.

Is there a way to identify these other connections? Or in general to circumvent concurrent access to pg_class (i guess)?

Thanks!

  • What is your exact Postgres version (select version(); will tell you) – a_horse_with_no_name Aug 13 at 9:38
  • it's PostgreSQL 9.6.11 – linqu Aug 13 at 9:39
  • What is the difference between you alpha environment, and production? Different PostgreSQL versions? Different schemas? – jjanes Aug 13 at 14:50
  • No, that error means that you yourself are modifying the same table row twice in the same statement. Unless you have some event trigger that causes the error, I'd suspect a PostgreSQL bug. Can you come up with a reproducible test case? Another idea: is partitioning involved? Also, what is your PostgreSQL version? – Laurenz Albe Aug 13 at 15:04
  • 1
    Oh, you didn't say it was Amazon RDS. If it cannot be reproduced with a copy of the database on real PostgreSQL, I'd assume it is either data corruption or an Amazon bug. Try to dump the database and restore it to another RDS instance. If the problem persists (but does not manifest on a copy on PostgreSQL), it is an Amazon bug. If the cloned instance doesn't exhibit the problem, it is most likely data corruption. – Laurenz Albe Aug 14 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.