Re:

Well,

from the DB point of view only the last executing query should be atomic,
and it HAS to be rolled back if it fails (even if outside a transaction).
All the rest should be in the control of the developer... what if I want to
take different course of action based on the succes/failure of a query, and
still consider the rest as an "atomic unit of processing", which should be
committed ?
I mean the developer should be in the position to tell if the transaction is
to be rolled back or committed or even continued with new queries.
I think Postgres doesn't allow this because of the way transactions are
currently implemented (i.e. no nested transactions, or savepoints).
Finally I can live without this feature, but it would allow for greater
flexibility. I had to hack some of our existing (Oracle based) code to work
with Postgres...

Cheers,
Csaba.

-----Ursprüngliche Nachricht-----
Von: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]Im Auftrag von Daniel Serodio
Gesendet: Dienstag, 5. November 2002 17:53
An: Csaba Nagy
Cc: PostgreSQL JDBC List
Betreff: Re: [JDBC]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html