PostgreSQL Deadlock Debugging – Log Responsible Queries with Node.js

deadlocknode.jspostgresql

I have a system of many node scripts that read and/or write automatically on a Postgres database all the time. One of those scripts randomly gets a deadlock. I would like to debug this, but the problem is that I don't know which other query cause the deadlock. (I use pg)

So my question is:

If I catch an error like this

{ error: deadlock detected
at Connection.parseE (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:604:13)
at Connection.parseMessage (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:403:19)
at Socket.<anonymous> (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:123:22)
at Socket.emit (events.js:197:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:150:17)
  name: 'error',
  length: 336,
  severity: 'ERROR',
  code: '40P01',
  detail:
   'Process 2376 waits for ShareLock on transaction 55837412; blocked by process 22585.\nProcess 22585 waits for ShareLock on transaction 55837411; blocked by process 2376.',
  hint: 'See server log for query details.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'while locking tuple (226684,50) in relation "lotti"',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'deadlock.c',
  line: '1146',
  routine: 'DeadLockReport' }

can I get the query and/or user of the other process in order to isolate and identify the problem? Or anything like this, if it makes sense.

Best Answer

Sure. Just look into the PostgreSQL log file.

This information is not sent to the client for security reasons: otherwise, you could craft SQL statements that deadlock with other queries to see somebody else's statements, which might contain confidential data.