For Postgres one should use in /src/java/org/jivesoftware/openfire/pubsub/PubSubPersistenceManager.java:
PURGE_FOR_SIZE = "DELETE from ofPubsubItem where id in (select ofPubsubItem.id FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=? AND nodeID=? ORDER BY creationDate DESC LIMIT ?) AS noDelete ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = ? AND nodeID = ?)";
""Reviewing the code, there's already a specific clause for HSQL, so we can add a specific clause for PostGRE, or search for a more generic delete clause.""
Openfire + Postgres
I had this same error. I tried the SQL change, recompiled, and It fixed my issue.
sent patch in, thanks.
Looks like the fix breaks MySQL, opened for this.