From: kongsen.tian at tokuii.com (kstian)
Date: Fri, 23 Sep 2005 18:38:14 +0800
Subject: [Pljava-dev] stack depth limit exceeded (NOT from
	infinite	recursion)
In-Reply-To: <thhal-0eGYPBKtj8bQVL/zrUF/3KkUJjDRvrG@mailblocks.com>
References: <4333C332.3060302@tokuii.com> <4333CDF5.9040207@mailblocks.com>
	<thhal-0eGYPBKtj8bQVL/zrUF/3KkUJjDRvrG@mailblocks.com>
Message-ID: <4333DB16.40904@tokuii.com>

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