David, Thanks for the quick response. I do have a primary key defined. But my table itself has just 256 rows. I suspect for this small a size, postgres might have grouped several ids together which could cause this issue. The underlying concept which I gather from your post, is that postgres is keeping track of not only the data being accessed for modification, but also the path to access it. While my observations do support this, I never came across documentation which state this explicitly. Have I understood the problem right?
I am getting an exception in my code which I did not anticipate and could not find sufficient documentation to clear this issue. I have a rather simple table (Table_A) with two columns (Text id, Integer number). If I do the following set of commands on two separate psql consoles, I get a read/write exception.
T1: begin transaction isolation level serializable;
T2: begin transaction isolation level serializable;
T1: update Table_A set number = 1 where id = 'ID1';
T2: update Table_A set number = 1 where id = 'ID2';
T1: commit;
T2: commit;
T2 throws a 40001 exception. Note that both transactions operate on distinct rows in my table. Then why am I getting an exception? I would greatly appreciate any pointers.
Best guess - you didn't define a PK on the id column so both updates sequentially scan the table and read the record of the other transaction. Then when you go to commit T2 the read record is seen to have changed by T1 and bam!. Add a PK and you should be good.
