[Pljava-dev] Savepoints and PL/Java

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