Tuple concurrently updated error

[Tina Kurian]

Hi, I'm using pg-vertx-client to create databases, schemas and tables dynamicaly in postgres. Here is a modified version of my code (for simplicity). The query strings get the db-names, schema-names and user-names replaced, but in the sample code, i'm using them directly. 

private static final String ERR_PERMISSION_GRANT_ERROR_MESSAGE = "Error granting permission. ";

private static final String ADVISORY_LOCK = "SELECT pg_try_advisory_lock(" + String.valueOf(BigInteger.valueOf(Double.valueOf(Math.random()).longValue())) + ")";

private static final String CREATE_USER = "CREATE ROLE <role-name> LOGIN PASSWORD <pwd>;";

private static final String GRANT_PERMISSION1 = "GRANT CREATE, CONNECT ON DATABASE <db-name> TO <role-name>;";

private static final String GRANT_PERMISSION2 = "GRANT USAGE ON SCHEMA <schema-name> TO <role-name>;";

private static final String GRANT_PERMISSION3 = "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema-name> TO <role-name>";

private static final String GRANT_PERMISSION5 = "ALTER DEFAULT PRIVILEGES IN SCHEMA <schema-name> GRANT ALL ON SEQUENCES TO <role-name>;"; 

private static Promise<Boolean> grantDatabase(PgPool pool, String databaseName, String userName, String schemaName, Vertx vertx) {

Promise<Boolean> promise = Promise.promise();
pool.getConnection().onSuccess(conn -> {
conn.begin().compose(tx ->
      conn.query(updateQueryString(ADVISORY_LOCK, databaseName, userName))
.execute()
 .compose( res1 ->
conn.query( updateQueryString(GRANT_PERMISSION1 databaseName, userName)) .execute()
.compose(res2 ->
conn.query( updateQueryString(GRANT_PERMISSION2, schemaName, userName)) .execute()
.compose(res3 ->
conn .query(updateQueryString( GRANT_PERMISSION3, schemaName, userName)) .execute()
.compose(res4 ->
conn .query(updateQueryString(GRANT_PERMISSION5, schemaName, userName)) .execute()))))
.compose(res5 -> tx.commit()))
.eventually(v -> conn.close()).onSuccess(v -> promise.complete(Boolean.TRUE)) .onFailure(err -> promise .fail(ERR_PERMISSION_GRANT_ERROR_MESSAGE });

return promise;
}

What am i doing wrong here? How can i modify the method above to avoid the tuple concurrently updated error? It doesn't happen all the time, it's intermittent. I only have a single instance of my application running. 

I'm using version 4.3.8

Thanks in advance, 

Tina.

[GERARD Nicolas]

Hi tina,

Just a little idea on this.

It seems more of an error on the Postgres side where it can get a lock due to multiple threads.

There is a lot of error like that on the grant:

https://www.google.com/search?q=error+tuple+concurrently+updated+grant+postgresql

When you grant permission, you should try to do it not concurrently ?