powered by Jive Software

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

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.

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

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.

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