Why can't I execute truncate and grant statement on a table in parallel?

Question

My use case is that I need to execute GRANT and TRUNCATE statements on a table concurrently.

Sample scenario:

When I try to execute below statements in parallel(two separate terminals):

while true; do psql -U <user> -d <database> -c 'GRANT select ON test1 TO <user>;'; done

while true; do psql -U <user> -d <database> -c 'TRUNCATE test1;'; done

I get the following error:

ERROR:  tuple concurrently updated

I don't understand the reason for the error. The TRUNCATE statement doesn't have anything to do with privileges. Then why can't I execute these statements concurrently?

Answer 1

The concurrent update that PostgreSQL is complaining about is not an update to the table test1, but to the catalog table pg_class.

Both GRANT and TRUNCATE have to update the table's row in pg_class, one to change relacl and the other to change relfilenode.

Now while updates to normal tables are protected by locks, that is not the case for catalog updates. They use some kind of “optimistic locking” where all but the first concurrent modification will cause this error.

Now you may call this a bug, but I'd say that if you have enough concurrent GRANT and TRUNCATE statements in your application that this becomes a problem, there's something wrong with the application.

You may find this e-mail of Tom Lane on the subject enlightening.