`logmq-consumer` batch insert fails with SQLSTATE 21000 on duplicate SQS delivery, then consumer dies permanently

[miekassu]

Describe the bug

Running logmq-consumer against an SQS standard queue with the PostgreSQL log store, we hit a repeated batch insert failure:

ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)
failed to insert log entries

This appears to happen when SQS redelivers a message (standard queues are at-least-once) and both copies end up in the same insert batch. The entire batch fails, all messages return to the queue, and the same failure recurs on redelivery — log ingestion stalls.

In our case it didn't stop there: the nack/redelivery cycle started producing ChangeMessageVisibilityBatch errors, which counted as consecutive receive errors, and the worker shut down permanently:

worker failed  worker=logmq-consumer  error="max consecutive receive errors reached (10): ..."

Queue depth then accumulated until we manually restarted the container.

To Reproduce

1. Run logmq-consumer against an SQS standard queue with the PostgreSQL log store, LOG_BATCH_SIZE=1000 (default)
2. Have SQS deliver a duplicate of a log message such that both copies land in the same batch (at-least-once duplicate, or visibility timeout expiring while the original is still buffered)
3. Batch insert fails with SQLSTATE 21000 → all messages nacked and redelivered → failure recurs → eventually the consumer hits max consecutive receive errors and stops

Expected behavior

A duplicate SQS delivery should be handled idempotently (deduplicated before insert, or the batch retried without the duplicate) rather than failing the entire batch. A single duplicate delivery — which SQS standard queues don't guard against — shouldn't be able to escalate to a permanently stopped consumer.

Screenshots

N/A — log excerpts above.

Additional context

• hookdeck/outpost:v1.0.3, SQS standard queue, PostgreSQL log store, ECS/Fargate
• The failure was silent in our deployment: the container stayed running, ECS health checks kept passing, and we only noticed via queue depth growth.

[alexluong]

Hi @miekassu, thanks for opening the issue and I agree that there are definitely some improvements we can do here. I've created a few issues here to track the work that we plan to do. #947 #948 #949

The failure was silent in our deployment: the container stayed running, ECS health checks kept passing, and we only noticed via queue depth growth.

Can you share more on your setup? Are you aware of the /healthz endpoint that you can configure when there are issues? Is that something you can use with ECS?

[miekassu]

Thanks @alexluong

We run the three Outpost services as separate ECS containers. We had been relying solely on ECS health checks, which in practice were only validating that the container was reachable, not that the consumer process was still healthy and processing messages.

So when logmq-consumer got stuck, the container stayed running and ECS continued to consider it healthy. We only noticed when queue depth started growing.

We've since added a sidecar that actively polls the application's health endpoint and reports failures back to ECS, so ECS can restart the task if the consumer gets stuck. That should cover this class of issue going forward.

[alexluong]

Hi @miekassu, we just released v1.0.5 that should address this issue. Please feel free to reopen the issue or open another one if there's anything else!