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.""
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.""