[sc161683] Update CDB_TableMetadata_Trigger to handle race conditions

[Shylpx]

Resources

• Shortcut story

Context

• When a cartodified table is updated, the following code is triggered in order to log into cdb_tablemetadata the name of the table and the last time it was updated:

  WITH nv as (
    SELECT TG_RELID as tabname, now() as t
  ), updated as (
    UPDATE @extschema@.CDB_TableMetadata x SET updated_at = nv.t
    FROM nv WHERE x.tabname = nv.tabname
    RETURNING x.tabname
  )
  INSERT INTO @extschema@.CDB_TableMetadata SELECT nv.* 
  FROM nv LEFT JOIN updated USING(tabname)
  WHERE updated.tabname IS NULL;

• What we are doing in that code is checking if the row already exists and update it (updated section). If that section returns NULL, it means that there isn't a row for that table, and we insert a new one. But there is a remote chance of race condition when two inserts are made simultaneously and the row doesn't exist yet: if both inserts try to update the row, they will both try to insert the record into the table. The first one will work, but the second one will fail, since the table's name is the primary key.
• It is suspected that this race of condition is the one inconsistently raising the following error, mainly when there are COPY procedures involved: duplicate key value violates unique constraint \"cdb_tablemetadata_pkey\".

Changes

• Rescuing UNIQUE_VIOLATION exception and retrying the update.

[shortcut-integration[bot]]

This pull request has been linked to Shortcut Story #161683: Duplicate key value on cdb_tablemetadata table after INSERT or COPY from due to CDB_TableMetadata_Trigger.

[Shylpx]

Closing, more information in https://github.com/CartoDB/cartodb/pull/16346#issuecomment-1309095799