If the session starts on a standby node, the DELETE query fails because standby nodes are read-only.
That’s interesting. Is that failure silent? If an exception is raised, I’d expect the agent to catch it and give up as a result. Do you have a stack trace?
BTW @mbekhta it seems this code will retry forever: hibernate-search/mapper/orm-outbox-polling/src/main/java/org/hibernate/search/mapper/orm/outboxpolling/event/impl/OutboxPollingEventProcessor.java at 97841fb3a907d88ef738c206385a9b625217b75f · hibernate/hibernate-search · GitHub
We might want to add an upper limit to the number of iteration OR the time spent in that loop, after which we give up, log an error and assume the events will get processed a second time later (which only hurts performance, not consistency)…
It seems that Hibernate Search agents don’t work correctly with PgPool-II and master-slave PostgreSQL architecture.
I confirm Hibernate Search’s outbox-polling agents require write access to the database, if that’s what you mean.
When using PgPool-II, how do you handle this problem in “normal” sessions? E.g. when you start a transaction, run a few select queries, then an insert/update/delete query, how do you make sure the connection you’re using provides write access?