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.""
Environment
Openfire + Postgres
Activity
Show:
Marcin Cieślak March 28, 2015 at 9:31 PM
Edited
Looks like the fix breaks MySQL, opened for this.
Daryl Herzmann April 17, 2014 at 8:29 PM
sent patch in, thanks.
Greg Hanley April 5, 2014 at 2:03 PM
I had this same error. I tried the SQL change, recompiled, and It fixed my issue.
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.""