Re:

Hi all,

I was wondering if there's any chance of this behavior to change in the
future ?
I mean will it be possible to continue a transaction after one of the SQLs
failed, by only rolling back what that query did ?
In many real life applications recovery is very possible after a failed
query, and (the not failed part of) the transaction should be committed.
This is one of the big differences in behavior between Postgres and Oracle,
making life hard for porting...

Cheers,
Csaba.

-----Ursprungliche Nachricht-----
Von: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]Im Auftrag von Karl Goldstein
Gesendet: Dienstag, 5. November 2002 17:15
An: pgsql-jdbc(at)postgresql(dot)org
Betreff: Re: [JDBC]

Patrice,

Yes, that is exactly what I realized yesterday (in my case, it was a
previous query in the same
transaction that I ported from an Oracle-based app that wasn't parsing right
in postgresql).

David asked me to write a simple test case that demonstrates the problem.
Here it is:

-- BEGIN NoResultTest.java --

import java.io.*;
import java.sql.*;
import java.text.*;

public class NoResultTest {

Connection db;
Statement st;

public void testNoResult(String args[]) throws Exception {

String url = args[0];
String usr = args[1];
String pwd = args[2];

// Load the driver
Class.forName("org.postgresql.Driver");

// Connect to database
System.err.println("Connecting to Database URL = " + url);
db = DriverManager.getConnection(url, usr, pwd);

System.err.println("Connected...Now creating a statement");
st = db.createStatement();

// create table outside of transaction to simulate a pre-existing table
st.executeUpdate("create table empty (empty_id integer)");

// No results error does not occur unless auto-commit is turned off
db.setAutoCommit(false);

try {
PreparedStatement ps =
db.prepareStatement("select empty_id emptyID from empty");
ResultSet rs = ps.executeQuery();
rs.next();
rs.close();
} catch (SQLException e) {
// should always throw a parse exception
e.printStackTrace();
// this fixes the problem
// db.rollback();
}

PreparedStatement ps =
db.prepareStatement("select empty_id AS emptyID from empty");
ResultSet rs = ps.executeQuery();
System.err.println("Has result from well-formed query: " + rs.next());
rs.close();

st.executeUpdate("drop table empty");

// Finally close the database
System.err.println("Now closing the connection");
st.close();
db.close();
}

public static void main(String args[]) throws Exception {

NoResultTest test = new NoResultTest();
test.testNoResult(args);
}
}
-- END NoResultTest.java --

Here is the output:

[karl(at)phoenix karl]$ java -version
java version "1.4.1-rc"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-rc-b19)
Java HotSpot(TM) Client VM (build 1.4.1-rc-b19, mixed mode)
[karl(at)phoenix karl]$ java -cp .:pgjdbc2.jar NoResultTest
jdbc:postgresql:karl k\
arl karl
Connecting to Database URL = jdbc:postgresql:karl
Connected...Now creating a statement
java.sql.SQLException: ERROR: parser: parse error at or near "emptyid"

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
at org.postgresql.Connection.ExecSQL(Connection.java:398)
at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
at NoResultTest.testNoResult(NoResultTest.java:32)
at NoResultTest.main(NoResultTest.java:57)
Exception in thread "main" No results were returned by the query.
at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
at NoResultTest.testNoResult(NoResultTest.java:42)
at NoResultTest.main(NoResultTest.java:57)

Thanks,

Karl

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
the definition of a transaction?

"A transaction is an atomic unit of processing; it is eigher performed
in its entirety or not at all"

My understanding of this is that if one statement failed, all of the
following statements should fail.

On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> Hi all,
>
> I was wondering if there's any chance of this behavior to change in the
> future ?
> I mean will it be possible to continue a transaction after one of the SQLs
> failed, by only rolling back what that query did ?
> In many real life applications recovery is very possible after a failed
> query, and (the not failed part of) the transaction should be committed.
> This is one of the big differences in behavior between Postgres and Oracle,
> making life hard for porting...
>
> Cheers,
> Csaba.
>
> -----Ursprungliche Nachricht-----
> Von: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]Im Auftrag von Karl Goldstein
> Gesendet: Dienstag, 5. November 2002 17:15
> An: pgsql-jdbc(at)postgresql(dot)org
> Betreff: Re: [JDBC]
>
>
> Patrice,
>
> Yes, that is exactly what I realized yesterday (in my case, it was a
> previous query in the same
> transaction that I ported from an Oracle-based app that wasn't parsing right
> in postgresql).
>
> David asked me to write a simple test case that demonstrates the problem.
> Here it is:
>
> -- BEGIN NoResultTest.java --
>
> import java.io.*;
> import java.sql.*;
> import java.text.*;
>
> public class NoResultTest {
>
> Connection db;
> Statement st;
>
> public void testNoResult(String args[]) throws Exception {
>
> String url = args[0];
> String usr = args[1];
> String pwd = args[2];
>
> // Load the driver
> Class.forName("org.postgresql.Driver");
>
> // Connect to database
> System.err.println("Connecting to Database URL = " + url);
> db = DriverManager.getConnection(url, usr, pwd);
>
> System.err.println("Connected...Now creating a statement");
> st = db.createStatement();
>
> // create table outside of transaction to simulate a pre-existing table
> st.executeUpdate("create table empty (empty_id integer)");
>
> // No results error does not occur unless auto-commit is turned off
> db.setAutoCommit(false);
>
> try {
> PreparedStatement ps =
> db.prepareStatement("select empty_id emptyID from empty");
> ResultSet rs = ps.executeQuery();
> rs.next();
> rs.close();
> } catch (SQLException e) {
> // should always throw a parse exception
> e.printStackTrace();
> // this fixes the problem
> // db.rollback();
> }
>
> PreparedStatement ps =
> db.prepareStatement("select empty_id AS emptyID from empty");
> ResultSet rs = ps.executeQuery();
> System.err.println("Has result from well-formed query: " + rs.next());
> rs.close();
>
> st.executeUpdate("drop table empty");
>
> // Finally close the database
> System.err.println("Now closing the connection");
> st.close();
> db.close();
> }
>
> public static void main(String args[]) throws Exception {
>
> NoResultTest test = new NoResultTest();
> test.testNoResult(args);
> }
> }
> -- END NoResultTest.java --
>
> Here is the output:
>
> [karl(at)phoenix karl]$ java -version
> java version "1.4.1-rc"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-rc-b19)
> Java HotSpot(TM) Client VM (build 1.4.1-rc-b19, mixed mode)
> [karl(at)phoenix karl]$ java -cp .:pgjdbc2.jar NoResultTest
> jdbc:postgresql:karl k\
> arl karl
> Connecting to Database URL = jdbc:postgresql:karl
> Connected...Now creating a statement
> java.sql.SQLException: ERROR: parser: parse error at or near "emptyid"
>
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
> at org.postgresql.Connection.ExecSQL(Connection.java:398)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
> at
> org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
> t.java:99)
> at NoResultTest.testNoResult(NoResultTest.java:32)
> at NoResultTest.main(NoResultTest.java:57)
> Exception in thread "main" No results were returned by the query.
> at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
> at
> org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
> t.java:99)
> at NoResultTest.testNoResult(NoResultTest.java:42)
> at NoResultTest.main(NoResultTest.java:57)
>
> Thanks,
>
> Karl
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
[]'s
Daniel Serodio

I don't have a strong opinion either way. For me, the main problem with the current behavior is
simply that the error message is confusing. If it is indeed the case that any SQLException
invalidates the current transaction (and my impression is that this is not intended), then the
driver should report that directly and not even let you try to execute later statements. The "No
results were returned by the query" error just left me scratching my head.

Thanks,

Karl

--- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> the definition of a transaction?
>
> "A transaction is an atomic unit of processing; it is eigher performed
> in its entirety or not at all"
>
> My understanding of this is that if one statement failed, all of the
> following statements should fail.
>
> On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > Hi all,
> >
> > I was wondering if there's any chance of this behavior to change in the
> > future ?
> > I mean will it be possible to continue a transaction after one of the SQLs
> > failed, by only rolling back what that query did ?
> > In many real life applications recovery is very possible after a failed
> > query, and (the not failed part of) the transaction should be committed.
> > This is one of the big differences in behavior between Postgres and Oracle,
> > making life hard for porting...
> >
> > Cheers,
> > Csaba.

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

Unfortunately, until the backend gives us usefull error codes, there's
not much we can do about catching exceptions intelligently.

And yes, the behaviour is intended, once a transaction has failed, you
need to end, or roll it back

Dave
On Tue, 2002-11-05 at 12:19, Karl Goldstein wrote:
> I don't have a strong opinion either way. For me, the main problem with the current behavior is
> simply that the error message is confusing. If it is indeed the case that any SQLException
> invalidates the current transaction (and my impression is that this is not intended), then the
> driver should report that directly and not even let you try to execute later statements. The "No
> results were returned by the query" error just left me scratching my head.
>
> Thanks,
>
> Karl
>
> --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> > I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> > the definition of a transaction?
> >
> > "A transaction is an atomic unit of processing; it is eigher performed
> > in its entirety or not at all"
> >
> > My understanding of this is that if one statement failed, all of the
> > following statements should fail.
> >
> > On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > > Hi all,
> > >
> > > I was wondering if there's any chance of this behavior to change in the
> > > future ?
> > > I mean will it be possible to continue a transaction after one of the SQLs
> > > failed, by only rolling back what that query did ?
> > > In many real life applications recovery is very possible after a failed
> > > query, and (the not failed part of) the transaction should be committed.
> > > This is one of the big differences in behavior between Postgres and Oracle,
> > > making life hard for porting...
> > >
> > > Cheers,
> > > Csaba.
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Dave Cramer <Dave(at)micro-automation(dot)net>

Csaba,

This is probably the wrong mail list to be asking this question since
this isn't a jdbc issue but an issue for the database itself.

I know that this item is on the database's TODO list, but I don't think
anyone is working on it.

thanks,
--Barry

Csaba Nagy wrote:
> Hi all,
>
> I was wondering if there's any chance of this behavior to change in the
> future ?
> I mean will it be possible to continue a transaction after one of the SQLs
> failed, by only rolling back what that query did ?
> In many real life applications recovery is very possible after a failed
> query, and (the not failed part of) the transaction should be committed.
> This is one of the big differences in behavior between Postgres and Oracle,
> making life hard for porting...
>
> Cheers,
> Csaba.
>
> -----Ursprungliche Nachricht-----
> Von: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]Im Auftrag von Karl Goldstein
> Gesendet: Dienstag, 5. November 2002 17:15
> An: pgsql-jdbc(at)postgresql(dot)org
> Betreff: Re: [JDBC]
>
>
> Patrice,
>
> Yes, that is exactly what I realized yesterday (in my case, it was a
> previous query in the same
> transaction that I ported from an Oracle-based app that wasn't parsing right
> in postgresql).
>
> David asked me to write a simple test case that demonstrates the problem.
> Here it is:
>
> -- BEGIN NoResultTest.java --
>
> import java.io.*;
> import java.sql.*;
> import java.text.*;
>
> public class NoResultTest {
>
> Connection db;
> Statement st;
>
> public void testNoResult(String args[]) throws Exception {
>
> String url = args[0];
> String usr = args[1];
> String pwd = args[2];
>
> // Load the driver
> Class.forName("org.postgresql.Driver");
>
> // Connect to database
> System.err.println("Connecting to Database URL = " + url);
> db = DriverManager.getConnection(url, usr, pwd);
>
> System.err.println("Connected...Now creating a statement");
> st = db.createStatement();
>
> // create table outside of transaction to simulate a pre-existing table
> st.executeUpdate("create table empty (empty_id integer)");
>
> // No results error does not occur unless auto-commit is turned off
> db.setAutoCommit(false);
>
> try {
> PreparedStatement ps =
> db.prepareStatement("select empty_id emptyID from empty");
> ResultSet rs = ps.executeQuery();
> rs.next();
> rs.close();
> } catch (SQLException e) {
> // should always throw a parse exception
> e.printStackTrace();
> // this fixes the problem
> // db.rollback();
> }
>
> PreparedStatement ps =
> db.prepareStatement("select empty_id AS emptyID from empty");
> ResultSet rs = ps.executeQuery();
> System.err.println("Has result from well-formed query: " + rs.next());
> rs.close();
>
> st.executeUpdate("drop table empty");
>
> // Finally close the database
> System.err.println("Now closing the connection");
> st.close();
> db.close();
> }
>
> public static void main(String args[]) throws Exception {
>
> NoResultTest test = new NoResultTest();
> test.testNoResult(args);
> }
> }
> -- END NoResultTest.java --
>
> Here is the output:
>
> [karl(at)phoenix karl]$ java -version
> java version "1.4.1-rc"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-rc-b19)
> Java HotSpot(TM) Client VM (build 1.4.1-rc-b19, mixed mode)
> [karl(at)phoenix karl]$ java -cp .:pgjdbc2.jar NoResultTest
> jdbc:postgresql:karl k\
> arl karl
> Connecting to Database URL = jdbc:postgresql:karl
> Connected...Now creating a statement
> java.sql.SQLException: ERROR: parser: parse error at or near "emptyid"
>
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
> at org.postgresql.Connection.ExecSQL(Connection.java:398)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
> at
> org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
> t.java:99)
> at NoResultTest.testNoResult(NoResultTest.java:32)
> at NoResultTest.main(NoResultTest.java:57)
> Exception in thread "main" No results were returned by the query.
> at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
> at
> org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
> t.java:99)
> at NoResultTest.testNoResult(NoResultTest.java:42)
> at NoResultTest.main(NoResultTest.java:57)
>
> Thanks,
>
> Karl
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Thanks for clarifying that, Dave. In the absence of useful error codes from the backend, would it
be possible to simply add an "exceptionThrown" flag in an appropriate place, which gets set when a
SQLException is thrown in a transaction? Then, if the client code attempts to execute any further
statements in that transaction, the driver could check the flag and give a clear exception to the
effect of "Sorry, an exception already occurred in this transaction, you have to rollback before
you can do anything else reliably with this connection."

Regards,

Karl

--- Dave Cramer <Dave(at)micro-automation(dot)net> wrote:
> Unfortunately, until the backend gives us usefull error codes, there's
> not much we can do about catching exceptions intelligently.
>
> And yes, the behaviour is intended, once a transaction has failed, you
> need to end, or roll it back
>
> Dave
> On Tue, 2002-11-05 at 12:19, Karl Goldstein wrote:
> > I don't have a strong opinion either way. For me, the main problem with the current behavior
> is
> > simply that the error message is confusing. If it is indeed the case that any SQLException
> > invalidates the current transaction (and my impression is that this is not intended), then the
> > driver should report that directly and not even let you try to execute later statements. The
> "No
> > results were returned by the query" error just left me scratching my head.
> >
> > Thanks,
> >
> > Karl
> >
> > --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> > > I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> > > the definition of a transaction?
> > >
> > > "A transaction is an atomic unit of processing; it is eigher performed
> > > in its entirety or not at all"
> > >
> > > My understanding of this is that if one statement failed, all of the
> > > following statements should fail.
> > >
> > > On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > > > Hi all,
> > > >
> > > > I was wondering if there's any chance of this behavior to change in the
> > > > future ?
> > > > I mean will it be possible to continue a transaction after one of the SQLs
> > > > failed, by only rolling back what that query did ?
> > > > In many real life applications recovery is very possible after a failed
> > > > query, and (the not failed part of) the transaction should be committed.
> > > > This is one of the big differences in behavior between Postgres and Oracle,
> > > > making life hard for porting...
> > > >
> > > > Cheers,
> > > > Csaba.
> >
> > __________________________________________________
> > Do you Yahoo!?
> > HotJobs - Search new jobs daily now
> > http://hotjobs.yahoo.com/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> --
> Dave Cramer <Dave(at)micro-automation(dot)net>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

On Tue, 2002-11-05 at 15:25, Dave Cramer wrote:
> Unfortunately, until the backend gives us usefull error codes, there's
> not much we can do about catching exceptions intelligently.

Well, at least in this particular situation, the backed raises a NOTICE
"current transaction is aborted, queries ignored until end of
transaction block". Maybe the driver can use this notice to give a more
meaningful exception message?

> And yes, the behaviour is intended, once a transaction has failed, you
> need to end, or roll it back

I just read Csaba's reply to my previous post, and now I have a better
understanding of this. We can't do much about it if the backend doesn't
support savepoints, right?

> Dave
> On Tue, 2002-11-05 at 12:19, Karl Goldstein wrote:
> > I don't have a strong opinion either way. For me, the main problem with the current behavior is
> > simply that the error message is confusing. If it is indeed the case that any SQLException
> > invalidates the current transaction (and my impression is that this is not intended), then the
> > driver should report that directly and not even let you try to execute later statements. The "No
> > results were returned by the query" error just left me scratching my head.
> >
> > Thanks,
> >
> > Karl
> >
> > --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> > > I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> > > the definition of a transaction?
> > >
> > > "A transaction is an atomic unit of processing; it is eigher performed
> > > in its entirety or not at all"
> > >
> > > My understanding of this is that if one statement failed, all of the
> > > following statements should fail.
> > >
> > > On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > > > Hi all,
> > > >
> > > > I was wondering if there's any chance of this behavior to change in the
> > > > future ?
> > > > I mean will it be possible to continue a transaction after one of the SQLs
> > > > failed, by only rolling back what that query did ?
> > > > In many real life applications recovery is very possible after a failed
> > > > query, and (the not failed part of) the transaction should be committed.
> > > > This is one of the big differences in behavior between Postgres and Oracle,
> > > > making life hard for porting...
> > > >
> > > > Cheers,
> > > > Csaba.
> >
> > __________________________________________________
> > Do you Yahoo!?
> > HotJobs - Search new jobs daily now
> > http://hotjobs.yahoo.com/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> --
> Dave Cramer <Dave(at)micro-automation(dot)net>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
--
[]'s
Daniel Serodio

Karl,

I thought about this, and it presumes that the driver knows that it is
in a transaction. At this point the driver has tried to stay pretty
lightweight assuming the programmer would take care of these things, so
it doesn't even know that it is in a transaction.

I would prefer to see this solved without adding more parsing in the
driver.

Dave

On Tue, 2002-11-05 at 12:35, Karl Goldstein wrote:
> Thanks for clarifying that, Dave. In the absence of useful error codes from the backend, would it
> be possible to simply add an "exceptionThrown" flag in an appropriate place, which gets set when a
> SQLException is thrown in a transaction? Then, if the client code attempts to execute any further
> statements in that transaction, the driver could check the flag and give a clear exception to the
> effect of "Sorry, an exception already occurred in this transaction, you have to rollback before
> you can do anything else reliably with this connection."
>
> Regards,
>
> Karl
>
> --- Dave Cramer <Dave(at)micro-automation(dot)net> wrote:
> > Unfortunately, until the backend gives us usefull error codes, there's
> > not much we can do about catching exceptions intelligently.
> >
> > And yes, the behaviour is intended, once a transaction has failed, you
> > need to end, or roll it back
> >
> > Dave
> > On Tue, 2002-11-05 at 12:19, Karl Goldstein wrote:
> > > I don't have a strong opinion either way. For me, the main problem with the current behavior
> > is
> > > simply that the error message is confusing. If it is indeed the case that any SQLException
> > > invalidates the current transaction (and my impression is that this is not intended), then the
> > > driver should report that directly and not even let you try to execute later statements. The
> > "No
> > > results were returned by the query" error just left me scratching my head.
> > >
> > > Thanks,
> > >
> > > Karl
> > >
> > > --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> > > > I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> > > > the definition of a transaction?
> > > >
> > > > "A transaction is an atomic unit of processing; it is eigher performed
> > > > in its entirety or not at all"
> > > >
> > > > My understanding of this is that if one statement failed, all of the
> > > > following statements should fail.
> > > >
> > > > On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > > > > Hi all,
> > > > >
> > > > > I was wondering if there's any chance of this behavior to change in the
> > > > > future ?
> > > > > I mean will it be possible to continue a transaction after one of the SQLs
> > > > > failed, by only rolling back what that query did ?
> > > > > In many real life applications recovery is very possible after a failed
> > > > > query, and (the not failed part of) the transaction should be committed.
> > > > > This is one of the big differences in behavior between Postgres and Oracle,
> > > > > making life hard for porting...
> > > > >
> > > > > Cheers,
> > > > > Csaba.
> > >
> > > __________________________________________________
> > > Do you Yahoo!?
> > > HotJobs - Search new jobs daily now
> > > http://hotjobs.yahoo.com/
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > --
> > Dave Cramer <Dave(at)micro-automation(dot)net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
--
Dave Cramer <davec(at)fastcrypt(dot)com>
Cramer Consulting

Karl,

What version of the driver are you using? I think the error reported in
this case in the latest version (7.3) is better than the error from the
7.2 driver.

--Barry

Karl Goldstein wrote:
> I don't have a strong opinion either way. For me, the main problem with the current behavior is
> simply that the error message is confusing. If it is indeed the case that any SQLException
> invalidates the current transaction (and my impression is that this is not intended), then the
> driver should report that directly and not even let you try to execute later statements. The "No
> results were returned by the query" error just left me scratching my head.
>
> Thanks,
>
> Karl
>
> --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
>
>>I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
>>the definition of a transaction?
>>
>>"A transaction is an atomic unit of processing; it is eigher performed
>>in its entirety or not at all"
>>
>>My understanding of this is that if one statement failed, all of the
>>following statements should fail.
>>
>>On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
>>
>>>Hi all,
>>>
>>>I was wondering if there's any chance of this behavior to change in the
>>>future ?
>>>I mean will it be possible to continue a transaction after one of the SQLs
>>>failed, by only rolling back what that query did ?
>>>In many real life applications recovery is very possible after a failed
>>>query, and (the not failed part of) the transaction should be committed.
>>>This is one of the big differences in behavior between Postgres and Oracle,
>>>making life hard for porting...
>>>
>>>Cheers,
>>>Csaba.
>>
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Barry,

I've primarily been using the 7.2 driver. I'm pretty sure I tried the latest driver as well, and
got the same error message.

In any event, now that the expected behavior is clear to me I can carry on with my app. I would
suggest, however, adding a note about this behavior to the JDBC documentation [1], since it does
differ from the way the Oracle JDBC driver behaves, for example.

Karl

[1] http://candle.pha.pa.us/main/writings/pgsql/sgml/jdbc.html

--- Barry Lind <blind(at)xythos(dot)com> wrote:
> Karl,
>
> What version of the driver are you using? I think the error reported in
> this case in the latest version (7.3) is better than the error from the
> 7.2 driver.
>
> --Barry
>
> Karl Goldstein wrote:
> > I don't have a strong opinion either way. For me, the main problem with the current behavior
> is
> > simply that the error message is confusing. If it is indeed the case that any SQLException
> > invalidates the current transaction (and my impression is that this is not intended), then the
> > driver should report that directly and not even let you try to execute later statements. The
> "No
> > results were returned by the query" error just left me scratching my head.
> >
> > Thanks,
> >
> > Karl
> >
> > --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> >
> >>I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> >>the definition of a transaction?
> >>
> >>"A transaction is an atomic unit of processing; it is eigher performed
> >>in its entirety or not at all"
> >>
> >>My understanding of this is that if one statement failed, all of the
> >>following statements should fail.
> >>
> >>On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> >>
> >>>Hi all,
> >>>
> >>>I was wondering if there's any chance of this behavior to change in the
> >>>future ?
> >>>I mean will it be possible to continue a transaction after one of the SQLs
> >>>failed, by only rolling back what that query did ?
> >>>In many real life applications recovery is very possible after a failed
> >>>query, and (the not failed part of) the transaction should be committed.
> >>>This is one of the big differences in behavior between Postgres and Oracle,
> >>>making life hard for porting...
> >>>
> >>>Cheers,
> >>>Csaba.
> >>
> >
> > __________________________________________________
> > Do you Yahoo!?
> > HotJobs - Search new jobs daily now
> > http://hotjobs.yahoo.com/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

Karl,

This behaviour isn't an artifact of the driver, but of the server. As I
mentioned earlier the driver doesn't even know it is in a transaction.

Dave

On Tue, 2002-11-05 at 16:35, Karl Goldstein wrote:
> Barry,
>
> I've primarily been using the 7.2 driver. I'm pretty sure I tried the latest driver as well, and
> got the same error message.
>
> In any event, now that the expected behavior is clear to me I can carry on with my app. I would
> suggest, however, adding a note about this behavior to the JDBC documentation [1], since it does
> differ from the way the Oracle JDBC driver behaves, for example.
>
> Karl
>
> [1] http://candle.pha.pa.us/main/writings/pgsql/sgml/jdbc.html
>
> --- Barry Lind <blind(at)xythos(dot)com> wrote:
> > Karl,
> >
> > What version of the driver are you using? I think the error reported in
> > this case in the latest version (7.3) is better than the error from the
> > 7.2 driver.
> >
> > --Barry
> >
> > Karl Goldstein wrote:
> > > I don't have a strong opinion either way. For me, the main problem with the current behavior
> > is
> > > simply that the error message is confusing. If it is indeed the case that any SQLException
> > > invalidates the current transaction (and my impression is that this is not intended), then the
> > > driver should report that directly and not even let you try to execute later statements. The
> > "No
> > > results were returned by the query" error just left me scratching my head.
> > >
> > > Thanks,
> > >
> > > Karl
> > >
> > > --- Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> wrote:
> > >
> > >>I've never worked with Oracle, just MySQL and PostgreSQL, but isn't this
> > >>the definition of a transaction?
> > >>
> > >>"A transaction is an atomic unit of processing; it is eigher performed
> > >>in its entirety or not at all"
> > >>
> > >>My understanding of this is that if one statement failed, all of the
> > >>following statements should fail.
> > >>
> > >>On Tue, 2002-11-05 at 14:31, Csaba Nagy wrote:
> > >>
> > >>>Hi all,
> > >>>
> > >>>I was wondering if there's any chance of this behavior to change in the
> > >>>future ?
> > >>>I mean will it be possible to continue a transaction after one of the SQLs
> > >>>failed, by only rolling back what that query did ?
> > >>>In many real life applications recovery is very possible after a failed
> > >>>query, and (the not failed part of) the transaction should be committed.
> > >>>This is one of the big differences in behavior between Postgres and Oracle,
> > >>>making life hard for porting...
> > >>>
> > >>>Cheers,
> > >>>Csaba.
> > >>
> > >
> > > __________________________________________________
> > > Do you Yahoo!?
> > > HotJobs - Search new jobs daily now
> > > http://hotjobs.yahoo.com/
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Dave Cramer <Dave(at)micro-automation(dot)net>

Daniel Serodio <daniel(at)checkforte(dot)com(dot)br> writes:
> Well, at least in this particular situation, the backed raises a NOTICE
> "current transaction is aborted, queries ignored until end of
> transaction block". Maybe the driver can use this notice to give a more
> meaningful exception message?

FYI, that NOTICE condition is changed to an ERROR as of 7.3, so you
can't miss it ;-)

regards, tom lane