error: cannot delete from table "archive" because it does not have a replica identity and publishes deletes

[mikecann]

Hi,

Im experimenting around with pg-boss as a replacement for our redis based queue and am getting the following error periodically:

[web] [error] [ErrorsService] error: cannot delete from table "archive" because it does not have a replica identity and publishes deletes
[web]     at Parser.parseErrorMessage (C:\dev\gangbusters\battletabs\node_modules\pg-protocol\dist\parser.js:287:98)
[web]     at Parser.handlePacket (C:\dev\gangbusters\battletabs\node_modules\pg-protocol\dist\parser.js:126:29)
[web]     at Parser.parse (C:\dev\gangbusters\battletabs\node_modules\pg-protocol\dist\parser.js:39:38)
[web]     at Socket.<anonymous> (C:\dev\gangbusters\battletabs\node_modules\pg-protocol\dist\index.js:11:42)
[web]     at Socket.emit (node:events:513:28)
[web]     at addChunk (node:internal/streams/readable:324:12)
[web]     at readableAddChunk (node:internal/streams/readable:297:9)
[web]     at Readable.push (node:internal/streams/readable:234:10)
[web]     at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
[web]     at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
[web]   length: 253,
[web]   severity: 'ERROR',
[web]   code: '55000',
[web]   detail: undefined,
[web]   hint: 'To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.',
[web]   position: undefined,
[web]   internalPosition: undefined,
[web]   internalQuery: undefined,
[web]   where: undefined,
[web]   schema: undefined,
[web]   table: undefined,
[web]   column: undefined,
[web]   dataType: undefined,
[web]   constraint: undefined,
[web]   file: 'execReplication.c',
[web]   line: '581',
[web]   routine: 'CheckCmdReplicaIdentity'
[web] }

Im assuming this is happening on a schedule as part of some maintenance job PGBoss is doing but im not sure what it is in my DB that might be causing it.

Any ideas?

[mikecann]

Looks like it was due to the WAL replication slots I was also experimenting around with.

I have now deleted those slots and the error no longer shows.

There may however be a better way of handling this?

Nope, its still happening.. :(

[timgit]

This is happening because the archive table doesn't have a primary key defined.