I always assumed what Dave just said, but porting from Oracle & Sybase
to PostgreSQL, we ran into exactly the same issue - we also solved it
with savepoints. However, I threw together the attached sample app to
test *precisely* what ends up in the database when auto-commit is off.
For the impatient, it sets auto-commit off, and tries to insert 3 rows.
The first succeeds, the second violates a unique index, so fails, and
the third is issued after the second, so should also fail. We ignore the
exceptions, then commit. The results puzzle me somewhat:
Oracle 10g: first and third inserts are in the DB
Sybase ASE 12.5: first and third inserts are in the DB
PostgreSQL 8.1.1: first insert is in the DB
Now I agree that Oracle and Sybase have this kind of wrong - the third
insert should not succeed. However, reading Dave's statement "The
concept of an atomic transaction means that it must either succeed
completely or fail completely. PostgreSQL does this." makes me wonder if
the first insert should be in the DB either? Or am I making some sort of
mistake here? From my results, it looks more like PostgreSQL's behaviour
is "Everything up the first failure can be committed" which isn't quite
the same thing as an indivisible unit of work that succeeds or fails
completely.
Anyway, I'd be curious about people's feedback on this, as it has sort
of nagged at me since I tested it.
Regards, Philip.
Dave Cramer wrote:
> Well,
>
> The concept of an atomic transaction means that it must either succeed
> completely or fail completely. PostgreSQL does this.
>
> Dave
> On 2-Apr-06, at 8:08 AM, Amaresh Wakkar wrote:
>
>> Thanks!!
>>
>> The code works fine after I added savepoints around "Okay,even if
>> fails" statement.
>>
>> What is the idea behind taking this route(i.e all statements ignored
>> till end of block) though? Is there any archive thread discussing
>> this? In this particular case, the autocommit is off and the statement
>> has failed so there is no risk of changes being made
>> permanent(presumable PG would rollback at database level the changed
>> buffers etc.,) unless the commit() method is invoked. Why then
>> explicit rollback is needed? Is it not best if it is left to
>> programmer to handle this in exception handling code?
>>
>> I just had to add two lines of savepoints but I added them for making
>> my code work rather than using them for some application logic purpose.
>>
>> Cheers!!
>>
>> Amaresh Wakkar
>>
>>
>>
>> */"Oliver Jowett"/*// wrote:
>>
>>
>> babu_moshay wrote:
>>
>> > In my opinion, if there are reasons to throw 25P02 and abort
>> transaction unilaterally, then there are also good reasons not to
>> abort it and let programmer take the decision. A switching
>> mechanism would have been ideal.
>>
>> Create a savepoint before the possibly-failing query. If the query
>> fails
>> in the way you were expecting, roll back to the savepoint and
>> continue.
>>
>> -O
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>> ------------------------------------------------------------------------
>> Indiatimes Email now powered by *APIC Advantage*. Help!
>> <http://infinite.indiatimes.com/apic/>
>> My Presence
>> <http://imaround.indiatimes.com/IMaround/presencefr.mss?userid=<!--User
>> //-->>Help <http://infinite.indiatimes.com/apic/userpage.html>
>> ------------------------------------------------------------------------
>>
>> <http://www.indiatimes.chikka.com>
>
>
>
> ----------------
> This message has been scanned for viruses and
> dangerous content by *the Utiba Mail Server* <http://www.utiba.com/>,
> and is
> believed to be clean.
import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.SimpleDateFormat;
class main
{
static Connection conn = null;
static Properties prop = null;
public static void main(String [] args)
{
try
{
if(args.length > 0) prop = loadProp(args[0]);
else usage();
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String pass = prop.getProperty("pass");
String driver = prop.getProperty("driver");
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pass);
conn.setAutoCommit(false);
createTable();
executeInsert(1,"expected to succeed");
executeInsert(1,"expected to fail");
executeInsert(2,"expected to fail");
conn.commit();
dumpTable("tempextest");
conn.commit();
dropTable();
conn.commit();
conn.close();
log("all done");
}
catch(Exception ex)
{
log(ex);
System.exit(1);
}
}
static void executeInsert(int id, String msg)
{
log("executeInsert, id[" + id + "] msg[" + msg + "]");
PreparedStatement stmt = null;
try{
stmt = conn.prepareStatement("INSERT INTO tempextest(id,msg) VALUES(?,?)");
stmt.setInt(1,id);
stmt.setString(2,msg);
stmt.executeUpdate();
conn.commit();
} catch (SQLException sqlex) {
log(sqlex);
closeStatement(stmt);
}
}
static void createTable()
{
log("Creating table tempextest");
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("CREATE TABLE tempextest(id INT PRIMARY KEY, msg VARCHAR(20))");
stmt.executeUpdate();
} catch (SQLException sqlex) {
log(sqlex);
log("error creating table tempextest, can't proceed");
closeStatement(stmt);
System.exit(1);
}
log("Table tempextest created");
}
static void dropTable()
{
log("Dropping table tempextest");
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("DROP TABLE tempextest");
stmt.executeUpdate();
} catch (SQLException sqlex) {
log(sqlex);
closeStatement(stmt);
}
log("Table tempextest dropped");
}
static Properties loadProp(String fileName)
{
try{
Properties prop = new Properties();
FileInputStream fis = new FileInputStream(fileName);
prop.load(fis);
return prop;
} catch (Exception ex) {
log("exception loading properties: " + ex);
usage();
}
return null;
}
static void usage()
{
System.err.println("Usage: java main propfile");
System.exit(1);
}
static void log(String msg)
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S");
System.out.println(sdf.format(new java.util.Date()) + ":" + msg);
}
static void log(Exception ex)
{
log(ex.toString());
ex.printStackTrace();
}
static void log(Object o)
{
log(o.toString());
}
static void closeStatement(Statement st)
{
try{
st.close();
}catch(SQLException sqlex) {
log(sqlex);
}
}
static void dumpTable(String tableName)
{
log("dumping contents of table " + tableName);
try{
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++)
{
System.out.print(rsmd.getColumnName(i) + "\t");
if(i != rsmd.getColumnCount())
System.out.print("|");
}
System.out.println("\n---------------------------------------------------------------------");
while(rs.next())
{
for(int i = 1; i <= rsmd.getColumnCount(); i++)
{
System.out.print(rs.getString(i) + "\t");
if(i != rsmd.getColumnCount())
System.out.print("|");
}
System.out.println();
}
rs.close();
s.close();
}catch(SQLException sqlex) {
System.err.println(sqlex);
}
}
}
