What happens when the enqueuing fails after a successful commit?

Retries.

So you retry indefinitely? How do you keep track of your retries? Do you have a queue to get the items into the real queue?

One solution to this is two phase commits, but I am not aware of any message broker that supports that. So basically you are stuck assuming that your message broker is never going to fail, or at least not going to fail for long enough for the users to notice.

IIRC, with Postgres, you can write to Redis directly from SQL and as part of transaction. (See `redis fdw` on google.) Perhaps, there is writable FDW driver for your queue system.

(Or maybe you can just dumpt stuff to sockets by using Postgres function written in Python, JS, Perl or w/ever you prefer.)

How do you queue that job only after the transaction committed?

The same way the new built-in logical replication works: logical decoding.

this means it does not work on RDS

Tough.

This is the kind of comment that makes me wish I could burn some of my own karma to downvote more than once.

Check the return code from libpq.