Concurrent update

[abookin]

Hi guys,

You have some critical issues in fhirbase_create and fhirbase_update functions.

_sql := format($SQL$
      WITH archived AS (
        INSERT INTO %s (id, txid, ts, status, resource)
        SELECT id, txid, ts, status, resource
        FROM %s
        WHERE id = $2
        RETURNING *
      ), inserted AS (
         INSERT INTO %s (id, ts, txid, status, resource)
         VALUES ($2, current_timestamp, $1, 'created', $3)
         ON CONFLICT (id)
         DO UPDATE SET
          txid = $1,
          ts = current_timestamp,
          status = 'updated',
          resource = $3
         RETURNING *
      )

Under heavy load functions sometimes fail with error like:
duplicate key value violates unique constraint "<resource_name>_history_pkey"

Just imagine two parallel transactions, whey trying to change the same resource...

1. Select same row from the main table.
   SELECT id, txid, ts, status, resource
   FROM %s
   WHERE id = $2
   RETURNING
2. Insert it to the history table.
   INSERT INTO %s (id, txid, ts, status, resource)
3. COMMIT

First transaction win)

It's not about transaction isolation. This is a logical issue.