Fix DoctrineDriver for postgreSQL by danydev · Pull Request #275 · bernardphp/bernard

DoctrineDriver currently has this implementation

try {
  $this->connection->insert('bernard_queues', ['name' => $queueName]);
} catch (\Exception $e) {
  // Because SQL server does not support a portable INSERT ON IGNORE syntax
  // this ignores error based on primary key.
}

While this works for most of the DB vendors, it does not work for PostgreSQL 9.5. As soon as I produce an event I receive the following error:

An exception occurred while executing 'INSERT INTO bernard_messages (queue, message, sentAt) VALUES (?, ?, ?)' 
SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block

This error is triggered by postgreSQL itself when he sees that you are in a transaction, something failed and you still try to use the same connection (in that transaction) to do queries (in this case we're trying to do the INSERT INTO bernard_messages after a failed INSERT INTO bernard_queues due to duplicate key)
Note that the error refers to INSERT INTO bernard_messages not because it's the problem, but just because postgreSQL stops us to do something on a "broken" transaction (and it became broken after trying to insert a row with an already existent key on bernard_queues).

PostgreSQL version used:

select version();
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

My proposed solution is to remove the try-catch and add a doctrine-driver specific method that checks the existence of such queue name before trying to insert it.

NOTE: It adds 1 query (on a primary key) per event produced, if we think that it's not acceptable we could cache the result executing at most 1 query per queue (or we could push further using only 1 query if we pre-fill the cache)