Duplicate key issue in a transaction block - Mailing list pgsql-general

Hi Everyone,

I have a hard to reproduce scenario for a production site....I tried to simplify the code and at the end I could get a
similarproblem with the following table and java code. 
The problem is that I have a transaction that deletes all the records in a group and inserts the new records for that
group.If that transaction is called from 2 different clients for the same groupid it happens to get a duplicate key
violationwhich it should never happen on my opinion!!!!!!!! 

Example:

begin transaction
delete from infotest where groupid = 1;
insert into infotest (groupid, subgroupid, datemodified) values (1,1,'2009-01-01 12:00');
insert into infotest (groupid, subgroupid, datemodified) values (1,2,'2009-01-01 12:00');
insert into infotest (groupid, subgroupid, datemodified) values (1,3,'2009-01-01 12:00');
...
commit transaction;

I am running postgres 8.3.1 on a SUSE LINUX 10.1 (X86-64) VERSION = 10.1

This is the postgres log sequence:

2009-05-31 19:05:49.235 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,1,"BEGIN",2009-05-31 19:05:49
CST,2/8,0,LOG,00000,"executeS_1: BEGIN",,,,,,,, 
2009-05-31 19:05:49.236 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,2,"DELETE",2009-05-31 19:05:49
CST,2/8,0,LOG,00000,"execute<unnamed>: delete from infotest where groupid = $1","parameters: $1 = '1'",,,,,,, 
2009-05-31 19:05:49.257 CST,"postgres","test",25305,"172.20.23.16:36748",4a23296d.62d9,104,"SELECT",2009-05-31 19:05:49
CST,1/58,0,LOG,00000,"execute<unnamed>: select groupid, subgroupid, datemodified from infotest where groupid =
1",,,,,,,,
2009-05-31 19:05:49.258 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,3,"INSERT",2009-05-31 19:05:49
CST,2/8,884974,LOG,00000,"execute<unnamed>: insert into infotest (groupid, subgroupid, datemodified) values
($1,$2,$3)","parameters:$1 = '1', $2 = '1', $3 = '2009-06-08 11:33:19.667-04'",,,,,,, 
2009-05-31 19:05:49.258 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,4,"INSERT",2009-05-31 19:05:49
CST,2/8,884974,ERROR,23505,"duplicatekey value violates unique constraint ""pk_infotest""",,,,,,"insert into infotest
(groupid,subgroupid, datemodified) values ($1,$2,$3)",, 
2009-05-31 19:05:49.297 CST,"postgres","test",25306,"172.20.23.16:33597",4a23296d.62da,5,"idle in
transaction",2009-05-3119:05:49 CST,2/0,884974,LOG,08P01,"unexpected EOF on client connection",,,,,,,, 






This is the script to create the table:

create table infotest (groupid integer, subgroupid integer, datemodified timestamp with time zone);
alter table infotest add constraint pk_infotest primary key (groupid,subgroupid);





And this is the java code I used in a loop:

DbTest1.java file:

import java.sql.Connection;
import java..sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

public class DbTest1
{
        private Connection connection;
        private Statement sql;;
        private DatabaseMetaData dbmd;

        private static final String pgClassName = "org.postgresql.Driver";
        private static final String pgUrl = "jdbc:postgresql://172.20.23.18/test";

        private static final String seqClassName = "org.continuent.sequoia.driver.Driver";
        private static final String seqUrl = "jdbc:sequoia://172.20.23.18/abrazo";

        private String login = "postgres";
        private String password = "testpassword";

        public void doTest(String conn) throws Exception
        {
                try
                {
                        String localURL;
                        String localDriver;
                        System.out.println("Initializing Driver for " + conn);
                        if (conn.toLowerCase().equals("pg"))
                        {
                                new org.postgresql.Driver();
                                localDriver = pgClassName;
                                localURL = pgUrl;
                        }
                        else
                        {
                                new org.continuent.sequoia.driver.Driver();
                                localDriver = seqClassName;
                                localURL = seqUrl;
                        }

                        System.out..println("Getting Connection using [" + localDriver + "] from [" + localURL + "]");
                        connection = DriverManager.getConnection(localURL, login, password);
                        System.out.println("Connection established!");

                        dbmd = connection.getMetaData(); //get MetaData to confirm connection
                        System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                                        dbmd.getDatabaseProductVersion()+" successful.\n");

                        sql = connection.createStatement(); //create a statement that we can use later

                        connection.setAutoCommit(false);

                        String sqlDel = "delete from infotest where groupid = ?";
                        String sqlIns = "insert into infotest (groupid, subgroupid, datemodified) values (?,?,?)";
                        PreparedStatement psDel = connection.prepareStatement(sqlDel);
                        PreparedStatement psIns = connection.prepareStatement(sqlIns);

                        Calendar c = Calendar.getInstance();
                        int GroupId = 1;
                        int LoopCount = 100;

                        System.out.println("Begin transaction...");

                        // Delete
                        psDel.setInt(1,GroupId);
                        psDel.executeUpdate();
                        System.out.println("Finished the delete...");

                        // Insert

                        int SubGroupID;
                        for ( SubGroupID=1; SubGroupID<=LoopCount; SubGroupID++ ) {
                             psIns.setInt(1,GroupId);
                             psIns.setInt(2,SubGroupID);
                             psIns.setTimestamp(3, new Timestamp(c.getTimeInMillis()));
                             psIns.executeUpdate();
                        }
                        System.out.println("Finished the inserts...");

                        psDel.close();
                        psIns.close();

                        connection.commit();
                        System.out.println("Commit transaction...");

                        connection.setAutoCommit(true);
                        ResultSet results = sql.executeQuery("select groupid, subgroupid, datemodified from infotest
wheregroupid = "+GroupId); 
                        while (results.next())
                        {
                                System.out.println("groupid = "+results.getInt(1)+"; subgroupid =
"+results.getInt(2)+";datemodified = "+results.getTimestamp(3) ); 
                        }
                        results.close();

                        connection.close();
                }
                catch (Exception cnfe)
                {
                        cnfe.printStackTrace();
                }
        }

        public static void main (String args[])
        {
                if (args == null || args.length != 1 || (!args[0].toLowerCase().equals("pg") &&
!args[0].toLowerCase().equals("seq")))
                {
                        System.out.println("Usage: " + DbTest1.class.getName() + " pg|seq");
                        System.exit(1);
                }

                try
                {
                        DbTest1 demo = new DbTest1();
                        demo.doTest(args[0]);
                }
                catch (Exception ex)
                {
                        ex.printStackTrace();
                }
        }
}



Thank you very much,

Ioana Danes


      __________________________________________________________________
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. Optimized for Yahoo! Get it Now for
Free!at http://downloads.yahoo.com/ca/internetexplorer/