Primarily, because it's hard to get right. Of course it can be done well, or correctly (and as The Fine Article points out, adding SKIP LOCKED to SELECT ... FOR UPDATE will go a long way towards that). But most people do them terribly.

As just one example, from a previous job: they were using SQL Alchemy in the code that interacted with the work queue, but somehow didn't realize that the DBAPI being implicitly transactional meant they'd need to manage transaction-state themselves. Consequently, they never released the implicitly opened transactions that every poll for new work created. No-one thought it was strange that the work queue system needed to be shut down weekly, so that a VACUUM FULL could reclaim the gibibytes of space that were wasted because the db couldn't clean up tuples (row version) that still appeared live to stale, abandoned transactions. Until I was all, "Uh, guys?"

The fix was obvious: disable implicit transactions. But that's just one example, if particularly egregious. I've seen and heard countless more.