We have N number of separate processes that can kick at the same time which all talk to the same postgres database. These "processes" are jdbc code which drop a user/schema and then recreate user/schema. We do this by connecting as a DBA user which is used across all processes. Each user/schema is unique so it's not as if all processes are attempting to drop the same. With that said 1 process will always succeed while the rest fail with:
ERROR: tuple concurrently updated
We are dropping with the following code:
String postgresRevokeUser = "REVOKE ALL ON DATABASE " + postgresDB + " FROM " + this.getUsername();
String postgresDropSchema = "DROP SCHEMA IF EXISTS " + this.getSchema() + " CASCADE";
String postgresDropUser = "DROP USER IF EXISTS " + this.getUsername();
connection = getDbaConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
statement.addBatch(postgresRevokeUser);
statement.addBatch(postgresDropSchema);
statement.addBatch(postgresDropUser);
statement.executeBatch();
connection.commit();
I can easily imagine what the issue is just don't know how to get around it (without putting in some synchronization between processes but that will be a fair amount of work). Any help would be great.