Re: Duplicate key issue in a transaction block

In response to Ioana Danes <ioanasoftware@yahoo.ca>:
>
> 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
asimilar problem 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!!!!!!!! 

On what is that opinion based?  Considering the situation you describe, I
would expect it to error every time you try to run that same script twice
in parallel.

Perhaps you want to take an exclusive lock on the table?  The operation
you describe seems to suggest that you'd want to guarantee exclusive
write access to the table.

>
> 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:49CST,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/ 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/