From: thomas at tada.se (Thomas Hallgren)
Date: Mon, 09 Oct 2006 08:36:24 +0200
Subject: [Pljava-dev] Savepoints and PL/Java
In-Reply-To: <17B4E68B-D1E4-49AC-BCA3-6FE2B1A29516@mac.com>
References: <54C58E85-14CF-43F9-8B4A-A0EC5DF468A1@mac.com>
	<452343F8.60408@tada.se>
	<890B225A-384F-4D93-A5FE-55E47832FD81@mac.com>
	<4523D367.90404@tada.se>
	<AA5FDE6A-2C97-4B08-B504-B4814757A43B@mac.com>
	<4525E59D.6020503@tada.se>
	<17B4E68B-D1E4-49AC-BCA3-6FE2B1A29516@mac.com>
Message-ID: <4529EDE8.3070305@tada.se>

OK, can't help you there. I don't have access to a Mac. You will get 
better responses if you post your replies to the mailing-list.

Regards,
Thomas Hallgren


Jeffrey Lyon wrote:
> Thomas,
>
> Thanks for the help.  The error occurs on OS X 10.4.8, JRE SE 
> 1.5.0_06-112, Pgsql 8.1.3 and the latest and greatest PL/Java.
>
> I have an entry function created in pgsql: create function 
> dispatch(varchar, integer) returns integer as 
> 'executive.Dispatcher.dispatch' language java;
>
> ... which calls:
> *public* *static* *int* dispatch(String pService, *int* pParamset)
> *throws* SQLException
> {
> Connection db = DriverManager./getConnection/("jdbc:default:connection");
> // Savepoint savePoint = db.setSavepoint();
>
> *try*
> {
> Parameters results = /dispatch/(db, *null*, pService, 
> Parameters./loadFromParameterList/(db, pParamset));
>
>
> *if* (results.containsKey("result"))
> {
> // db.releaseSavepoint(savePoint);
> *return* results.saveToParameterList(db);
> }
> *else*
> {
> PreparedStatement _serviceCall = db.prepareStatement( "SELECT " + 
> pService + "(?) AS paramsetid " +
> "FROM dual;" );
> _serviceCall.setInt(1, pParamset);
> ResultSet serviceCall = _serviceCall.executeQuery();
> *if* (serviceCall.next())
> {
> // db.releaseSavepoint(savePoint);
> *return* serviceCall.getInt("paramsetid");
> }
> }
> }
> *catch* (Exception e)
> {
> // db.rollback(savePoint);
> e.printStackTrace();
> }
>
> *return* -1;
> }
>
>
> I call dispatch through a libpq connection.  I get the error on the 
> first comment line where I set a Savepoint.
>
> Any insight?
>
> Thanks again,
>
> Jeffrey
>
> On Oct 6, 2006, at 1:11 AM, Thomas Hallgren wrote:
>
>> Hi Jeffrey,
>> Haven't seen that one before. Can you tell me what configuration 
>> you're on?, I.e. OS, JVM, PostgreSQL and PL/Java versions. I would 
>> also like to see the backend log at the time of the error. What kind 
>> of SQL statement is it that calls this function?
>>
>> Regards,
>> Thomas Hallgren
>>
>> Jeffrey Lyon wrote:
>>> Thomas,
>>>
>>> I get the error when I set the Savepoint.
>>>
>>> Thanks,
>>>
>>> Jeff
>>>
>>> On Oct 4, 2006, at 11:29 AM, Thomas Hallgren wrote:
>>>
>>>> Hi Jeffrey,
>>>> Do you get this when you release the savepoint or when you roll 
>>>> back. If it's the latter, then I'm curious to what kind of 
>>>> exception you get. Some exceptions (syntax errors for instance) are 
>>>> considered fatal by PostgreSQL and cannot be recovered.
>>>>
>>>> Regards,
>>>> Thomas Hallgren
>>>>
>>>> Jeffrey Lyon wrote:
>>>>> Thomas,
>>>>>
>>>>> K, so now I have, effectively -
>>>>>
>>>>> int something()
>>>>> {
>>>>>   Database db = 
>>>>> DriverManager.getConnection("jdbc:default:connection");
>>>>>   Savepoint savePoint = db.setSavepoint();
>>>>>
>>>>>   int result;
>>>>>
>>>>>   try
>>>>>   {
>>>>>     result = trySomethingWithSQL(db);
>>>>>     db.releaseSavepoint(savePoint);
>>>>>     return result;
>>>>>   }
>>>>>   catch (Exception e)
>>>>>   {
>>>>>     db.rollback(savePoint);
>>>>>     return -1;
>>>>>   }
>>>>> };
>>>>>
>>>>> - and I'm getting a 'ERROR:  buffer 125 is not owned by resource 
>>>>> owner TopTransaction' from pgsql.  Am I still missing something?
>>>>>
>>>>> Thanks for the help,
>>>>>
>>>>> Jeff
>>>>>
>>>>>
>>>>> On Oct 4, 2006, at 1:17 AM, Thomas Hallgren wrote:
>>>>>
>>>>>> Hi Jeffrey,
>>>>>> AFAIK, there's no such thing as a Connection.commit(Savepoint). 
>>>>>> You can only release or roll it back. Both the 
>>>>>> Connection.release(Savepoint) and Connection.rollback(Savepoint) 
>>>>>> methods are available in PL/Java.
>>>>>>
>>>>>> Kind Regards,
>>>>>> Thomas Hallgren
>>>>>>
>>>>>>
>>>>>> Jeffrey Lyon wrote:
>>>>>>> Perhaps an easy one...
>>>>>>>
>>>>>>> I'd like to drop a Savepoint to be committed or rolled-back,  
>>>>>>> depending upon the results of a function call, i.e.:
>>>>>>>
>>>>>>> int something()
>>>>>>> {
>>>>>>>    Database db = 
>>>>>>> DriverManager.getConnection("jdbc:default:connection");
>>>>>>>    Savepoint savePoint = db.setSavepoint();
>>>>>>>
>>>>>>>    int result;
>>>>>>>
>>>>>>>    try
>>>>>>>    {
>>>>>>>      result = trySomethingWithSQL(db);
>>>>>>>    }
>>>>>>>    catch (Exception e)
>>>>>>>    {
>>>>>>>      db.rollback(savePoint);
>>>>>>>      return -1;
>>>>>>>    }
>>>>>>>
>>>>>>>    db.commit(savePoint);
>>>>>>>    return result;
>>>>>>> };
>>>>>>>
>>>>>>> I see that, although setSavepoint and releaseSavepoint are 
>>>>>>> available,  commit and rollback are not.  What is the point of 
>>>>>>> set/ releaseSavepoint if I cannot commit or rollback from a 
>>>>>>> Savepoint?  Is  there any other way to accomplish the above?
>>>>>>>
>>>>>>> Thanks in advance,
>>>>>>>
>>>>>>> Jeff Lyon
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Pljava-dev mailing list
>>>>>>> Pljava-dev at gborg.postgresql.org 
>>>>>>> <mailto:Pljava-dev at gborg.postgresql.org>
>>>>>>> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>