How to resolve Integrity constraint violation: 1062 Duplicate entry on node_access?
For posterity, I did not figure out the line of code that were  causing this but the issue is that node_save was being called twice on a new node, which DID create a legitimate duplicate key, but then it would also delete the offending row. This drives the developer bonkers.

It has something to do with Workflow (probably), and I am not particularly proud of this but you can remedy by making one small change to node.module:

/* ADDED SPR */
$delete = true; //$op == 'update';
/* END ADDED SPR */


Top and bottom lines are for easy searching of code tweaks for upgrades.