Feature/26

[SpyQuel]

Wouldn't it be better to perform a select and based on the result decide if the following operation should be an Insert or an Update instead of making the logic exception-driven?

[andrea-rockt]

I would use this strategy to perform upserts

https://www.postgresqltutorial.com/postgresql-upsert/

[tmnd1991]

@andrea-rockt : that would limit the compatibility of the plugin to Postgres 9.5 onwards, as of now the plugin might be jdbc instead of postgresql, I'm quite sure. @SpyQuel : that looks like a good advice. In theory, if I do everything in the same transaction, I wouldn't hit cases like someone that deletes data between my select and my update/insert, is that right?

[SpyQuel]

It should depend on the isolation level set on the DB: if phantom reads and repeatable reads are managed you are ensured that no other transaction is changing the data between you selects and your inserts (or updates). Since it depends on the target DB configuration maybe we should put a disclaimer on its documentation.

[tmnd1991]

I'm definitely not an expert, can you see @SpyQuel if the transactionIsolationLevel I set is enough?

[SpyQuel]

Yes, it is more than enough! Serializable transactions prevent both non-repeatable reads and phantom reads. Since Postgres implementation is compliant to the general SQL transactional levels (https://www.postgresql.org/docs/9.5/transaction-iso.html) we should be fine :)