Menu
▾
▴
#402 Issue with updates on replicated tables
The remote UPDATE queries for an update statement for replicated
tables look something like this:
UPDATE ONLY public.tab1 SET tab1name1 = $2 WHERE ctid = $4
And we run the same update statement on all the datanodes, while the
particular ctid fetched from SELECT belongs to only one datanode. That
means, this looks like a general bug in update on replicated tables,
this is not a corner case. Likely, the ctids happen to be the same for
the datanodes, so it appears to be everything-fine. I hope that I am
not missing something. I am going to raise a bug for this ...
I am trying to make my application use Postgres XC and I'm running into this very problem. The application uses DBUnit and frequently updates a few rows in a table. In the test setup I am using 2 datanodes and it seems the ctids can start to differ sometimes, leading to inconsistent results for updates.
It appears one datanode can decide to start assigning ctid (0,1) again, while the other one is for instance at (0,59). As a result the data becomes different on both datanodes. When logging into the datanodes one at a time, I can clearly see the data is differs on both nodes using select queries.
If I execute: update myTable set myId = 123 where myId = 123 in this case, I sometimes get back 'one row affected', other times '0 rows affected' or a unique key constraint violation. However, if I do select * from myTable where myId = 123, I am seeing the row just fine.
This has been fixed in REL1_2_STABLE and master.