Skip site navigation (1) Skip section navigation (2)

Re: R: Re: R: Re: Commitment control on updateable ResultSet

From: "Dave Cramer" <pg(at)fastcrypt(dot)com>
To: "Flavio Palumbo" <flavio(dot)palumbo(at)fastwebnet(dot)it>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: R: Re: R: Re: Commitment control on updateable ResultSet
Date: 2009-01-02 14:54:16
Message-ID: 491f66a50901020654u3203b859j607912281530bc36@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Yes, once there is an error in a transaction no other queries will succeed.

Dave

On Fri, Jan 2, 2009 at 8:59 AM, Flavio Palumbo <flavio(dot)palumbo(at)fastwebnet(dot)it
> wrote:

> Hi Dave,
>
> here is a testcase :
>
> code
> ----------------------------------------------------------------------------------------
>
> import java.sql.Connection;
> import java.sql.Statement;
> import java.sql.Savepoint;
>
> public class TestCommitPostgresRS {
>     public TestCommitPostgresRS() {
>         Connection con = null;
>         try {
>             Class.forName("org.postgresql.Driver").newInstance();
>             con =
> java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres",
> "user", "pass");
>             Statement ps =
> con.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
>                     java.sql.ResultSet.CONCUR_UPDATABLE);
>             con.setAutoCommit(false);
>             String s = "SP" +
> java.util.Calendar.getInstance().getTimeInMillis();
>             con.setSavepoint(s);
>             try {
>                 java.sql.ResultSet rsC = ps.executeQuery("select count(*)
> from (select T1.* from public.teststrike T1 for update) as query");
> // this obviuosly wrong for the sintax and raises the exception
> // org.postgresql.util.PSQLException: ERROR: SELECT FOR UPDATE/SHARE is not
> allowed with aggregate functions
> // but it should be ignored
>             } catch (Throwable tx) {
>                 System.out.println("Errore1 - " + tx);
>             }
>             java.sql.ResultSet rs = ps.executeQuery("select T1.* from
> public.teststrike T1 for update");
>  // this raises the exception
> // org.postgresql.util.PSQLException: ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> // this is the true point of interest for me
> } catch (Throwable t) {
>             System.out.println("errore - " + t);
>         }
>         try {
>             if (con != null) {
>                 con.commit();
>                 con.setAutoCommit(true);
>                 con.close();
>             }
>         } catch (Throwable t) {
>         }
>     }
>     public static void main(String[] args) {
>         new TestCommitPostgresRS();
>         System.exit(0);
>     }
> }
> code
> ----------------------------------------------------------------------------------------
> Now I realise that I execute 2 queries :
> - first to obtain the number of records included in the second query,
> ignored if raises exception
> - second to obtain real data
>
> In the test I can see that if I comment the first query the second works
> fine ... it seems that the 2 queries are tied for some reason.
>
> Any hint would be appreciated.
>
> Thanks
> Flavio
>
>
> ----Messaggio originale----
> Da: pg(at)fastcrypt(dot)com
> Data: 02/01/2009 14.16
> A: "Flavio Palumbo"<flavio(dot)palumbo(at)fastwebnet(dot)it>
> Ogg: Re: R: Re: [JDBC] Commitment control on updateable ResultSet
>
> You should be able to,
>
> Show me the exact error .
>
> Dave
>
> On Fri, Jan 2, 2009 at 8:05 AM, Flavio Palumbo <
> flavio(dot)palumbo(at)fastwebnet(dot)it> wrote:
>
>> Hi Dave,
>>
>> thanks for your replay, I followed in debug mode my program and I've seen
>> that the executeQuery method raised the exception.
>>
>> It appears as if commitment control cannot be applied to an updateable
>> ResultSet ; I cannot understand, what's wrong ?
>>
>>
>> Thanks
>> Flavio
>>
>> ----Messaggio originale----
>> Da: pg(at)fastcrypt(dot)com
>> Data: 02/01/2009 13.19
>> A: "Flavio Palumbo"<flavio(dot)palumbo(at)fastwebnet(dot)it>
>> Cc: <pgsql-jdbc(at)postgresql(dot)org>
>> Ogg: Re: [JDBC] Commitment control on updateable ResultSet
>>
>> One of the statements between setAutoCommit(false) and execute query is
>> generating an error.
>>
>> Dave
>>
>> On Fri, Jan 2, 2009 at 6:47 AM, Flavio Palumbo <
>> flavio(dot)palumbo(at)fastwebnet(dot)it> wrote:
>>
>>> Hi all,
>>>
>>> I try to control the commitment on an updatable ResultSet but I get a
>>> strange ex
>>> ception :
>>> "ERROR: current transaction is aborted, commands ignored until end of
>>> transactio
>>> n block"
>>>
>>> I do this :
>>> - Connection.setAutoCommit(false);
>>> - Connection.setSavepoint(point);
>>> - Connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
>>> java.sq
>>> l.ResultSet.CONCUR_UPDATABLE);
>>> - Statetement.executeQuery("SELECT T1.*  FROM  public.teststrike T1 for
>>> update")
>>>
>>> at this point a I get the above exception.
>>>
>>> Can somebody help me to figure out why this happens ?
>>>
>>> Thanks
>>> Flavio
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>>
>>
>>
>
>
>

In response to

pgsql-jdbc by date

Next:From: abhijeet dharurkarDate: 2009-01-04 04:34:17
Subject: help please
Previous:From: Flavio PalumboDate: 2009-01-02 13:59:35
Subject: R: Re: R: Re: Commitment control on updateable ResultSet

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group