Unexpected purge process error at openfire debug log(Postgre SQL DB)

Openfire Openfire Support
1

Hi, I’m currently deploying OpenFire in a research environment. Checking the logs I found the following error:

DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN (SELECT id FROM

*** ofPubsubItem WHERE serviceID=uuuuuuu@dddddddd AND nodeID=http://jabber.org/protocol/geoloc ORDER BY creationDate DESC LIMIT 1) AS noDelete ***

ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = uuuuuuu@dddddddd AND nodeID = http://jabber.org/protocol/geoloc

at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(Abst ractJdbc2Statement.java:2531)

*** at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java: 1344)***

*** at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)***

*** at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement .java:2670)***

*** at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)***

*** at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)***

*** at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja va:43)***

*** at java.lang.reflect.Method.invoke(Method.java:616)***

*** at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)***

*** at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)***

*** at org.postgresql.PGStatement$$EnhancerByProxool$$6a8d54dc.executeBatch()***

*** at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPers istenceManager.java:1878)***

I tested the query against PostgreSQL (adding the corresponding quotes) and got a sintax error.

So, I looked into the source code and found this:

http://fisheye.igniterealtime.org/browse/openfire/trunk/src/java/org/jivesoftwar e/openfire/pubsub/PubSubPersistenceManager.java?r=13651

private static final String PURGE_FOR_SIZE =

"DELETE ofPubsubItem 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 = ?";

I guess that it can be rewrited as:

private static final String 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 = ?)”;

that seems to do the purging process in PostgreSQL

I build the source and replace the class in the openfire.jar, restarted openfire and then the error dissapear with no other effects.

I hope that you can review this purging process. Thanks in advance.

PD: Sorry if this post is a dupplicate, I’ve searched the forum for a while without results about this specific topic.

2

‘DELETE ofPubsubItem FROM …’ looks indeed odd. Did you try to change it to ‘DELETE FROM …’ before you did modify the where clause? A smaller change requires less time to review - I have no idea which statement makes use of indexes/is fast - Joins tend to be slow anyway.

3

Hi, I tried that, and I got a sintax error too(also I double-checked this again after your reply). Basically, I tried to make a select that seems to load the entries that the original delete command expect to purge. Then, with the working select clause I rewrite the delete sentence. I think that the original clause is correct for SQL Server, but in postgre SQL (http://www.postgresql.org/docs/9.0/static/sql-delete.html) I understood that the sintax may vary(they extend the ANSI with a USING clause that seems to work like the LEFT JOIN in the programmed clause). Reviewing the code, there’s a specific clause for HSQL, so we can add a specific clause for PostGRE, or search for a more generic delete clause.

Thanks a lot for your reply.

4

http://issues.igniterealtime.org/browse/OF-756 created.