[Pljava-dev] stack depth limit exceeded (NOT from infinite recursion)

Hi Thomas,

Thanks for replying.

I have gradually increased max_stack_depth in postgresql.conf to 16384
(verified from show all in the db) but it did not seem to help.

The following is the code I have for the trigger function. Basically,
what I wanted it to do is, upon data update, I wanted to connect to
serivcemix (jbi container) and publish the event to activeMQ. The
problem happened on the line where I marked "==> <==". I also printed
out the loaded classes from sqlj.Loader.java in findClass method and it
showed a lot of loaded classes. These classes were to be passed to sql
statement for loading.

=================================
mport java.sql.SQLException;
import java.sql.ResultSet;
import java.util.logging.Logger;

import org.postgresql.pljava.TriggerData;
import org.postgresql.pljava.TriggerException;

import javax.jms.Connection;
import javax.jms.JMSException;
import javax.jms.Session;
import javax.jms.MessageProducer;

import org.activemq.ActiveMQConnectionFactory;
import org.activemq.ActiveMQConnection;
import org.activemq.message.ActiveMQTopic;

public class Triggers {

public static void afterUpdateTsr(TriggerData td) throws
SQLException, JMSException
{
if (td.isFiredForStatement())
throw new TriggerException(td, "Cannot process statement
events.");

if (td.isFiredBefore())
throw new TriggerException(td, "Must be fired after event.");

if (!td.isFiredByUpdate())
throw new TriggerException(td, "Can only process UPDATE event");

ResultSet _new = td.getNew();
ResultSet _old = td.getOld();
String[] args = td.getArguments();
String status = args[1];

if (args.length != 1)
throw new TriggerException(td, "One argument is expected.");

Logger log = Logger.getAnonymousLogger();
log.info("After UPDATE to tservicerequest table with new tstatus
= " + _new.getString(status) + ", old tstatus = " + _old.getString(status));

ActiveMQConnectionFactory factory = new
ActiveMQConnectionFactory(ActiveMQConnection.DEFAULT_USER,
ActiveMQConnection.DEFAULT_PASSWORD,
ActiveMQConnection.DEFAULT_BROKER_URL); //"tcp://localhost:61616");
ActiveMQTopic pubTopic = new
ActiveMQTopic("demo.org.servicemix.source");
//ActiveMQTopic subTopic = new
ActiveMQTopic("demo.org.servicemix.result");

log.info("Connecting to JMS Server...");
Connection connection = factory.createConnection();
log.info("Before setClientID");
==>connection.start(); <==
Session session = connection.createSession(false,
Session.AUTO_ACKNOWLEDGE);
MessageProducer producer = session.createProducer(pubTopic);
//connection.start();

log.info("Sending request to JMS Server...");
producer.send(session.createTextMessage("New tstatus= " +
_new.getString(status) + ", old tstatus= " + _old.getString(status)));

log.info("Sent message: New tstatus= " + _new.getString(status)
+ ", old tstatus= " + _old.getString(status));
//connection.close();

}
}

===============================================

Thanks a lot for help.

Regards,
Kong

Thomas Hallgren wrote:

> kstian wrote:
>
>> Hi,
>>
>> I created the following Trigger and Function:
>>
>> ======================================
>> CREATE FUNCTION afterUpdateTsr()
>> RETURNS trigger
>> AS 'pljava.Triggers.afterUpdateTservicerequest'
>> LANGUAGE java;
>>
>> CREATE TRIGGER tsr_afterUpdate
>> AFTER UPDATE ON tsr
>> FOR EACH ROW
>> EXECUTE PROCEDURE afterUpdateTsr(name);
>> ==========================================
>>
>> Then in the afterUpdateTsr() java function, I was writing codes to
>> connect to servicemix (a JBI container).
>>
>> In order to do that, I had to install 11 jar files in my postgresql
>> table using sqlj.install_jar function.
>>
>> When the aferUpdateTsr() got triggered, it was throwing "stack depth
>> limit exceeded" exception from native C. (I believed it's from
>> ExecutionPlan.c). I also printed out the loaded classes from
>> sqlj/Loader.java (findClass function) and found that pljava was
>> loading a lot of classes.
>> Was it the main reason causing this exception? How do I resolve this
>> problem?
>>
> Normally, a "stack depth limit exceeded" exception is caused by a
> function that calls SQL, that in turn calls a function that calls SQL,
> etc. until you recurse so deep that the PostgreSQL callstack is
> exhausted. If it's a controlled and expected recursion you can try to
> increase the max_stack_depth variable in your postgresql.conf
>
> If you this doesn't help, then I'll have to know a bit more about what
> your triggers are doing in order to assist you.
>
> Kind regards,
> Thomas Hallgren
>
>
>
>
>
>