Unique index: update error - Mailing list pgsql-hackers

Suppose there are too tuples in a table t, named
id
---
1
2

and there is a unique index on id. Now we do an update on table t
update t set id=id+1

Since PG executes the update one tuple at a time, it updates tuple "1"
to "2" and insert it into the index. Before insert into the index, it
check whether the id is still "unique" or not. No, it's not, old tuple
"2" is still in the table. So an error is raised.

I try to solve this problem this way:
First, update the table t but DON'T update the index.
Next, find all the tuples updated by this command and insert them into
the unique index.

By doing so, the problem seemed to be solved. My modifications focus
on the function "ExecutePlan". Here is my patch for PG8.1.4.
Tuplestore is used to record all the tuples being updated by this
command.

Is there any problom with it? Thanks.


Index: backend/executor/execMain.c====================================================== =============RCS file:
/home/gdliu/cvsroot/postgresql/src/backend/executor/execMain.c,vretrieving revision 1.1.1.1diff -c -r1.1.1.1
execMain.c***backend/executor/execMain.c 5 Sep 2006 07:19:08 -0000 1.1.1.1--- backend/executor/execMain.c 7 Sep 2006
08:28:34-0000****************** 1068,1073 ****--- 1068,1092 ----   long  current_tuple_count;   TupleTableSlot
*result;
++  MemoryContext holdCtidContext = NULL;+  Tuplestorestate *holdCtidStore = NULL;+  MemoryContext oldcxt = NULL;+
if(operation== CMD_UPDATE) {+    holdCtidContext =+   AllocSetContextCreate(CurrentMemoryContext,+
"HoldUpdateCTIDContext",+         ALLOCSET_DEFAULT_MINSIZE,+          ALLOCSET_DEFAULT_INITSIZE,+
ALLOCSET_DEFAULT_MAXSIZE);++   oldcxt = MemoryContextSwitchTo(holdCtidContext);+    holdCtidStore =
tuplestore_begin_heap(false,false, work_mem);+    MemoryContextSwitchTo(oldcxt);+  }+   /*    * initialize local
variables   */****************** 1287,1293 ****--- 1306,1319 ----      break;
 
     case CMD_UPDATE:      ExecUpdate(slot, tupleid, estate);++     oldcxt = MemoryContextSwitchTo(holdCtidContext);+
 slot->tts_tuple->t_data->t_ ctid = slot->tts_tuple->t_self;+
tuplestore_puttuple(holdCtidStore,slot->tts_tuple);+    MemoryContextSwitchTo(oldcxt);      result = NULL;      break;
 
****************** 1308,1313 ****--- 1334,1372 ----     break;   }
++  //insert index+  if(operation == CMD_UPDATE &&+     estate->es_result_relation_info->ri_NumIndices > 0) {+
HeapTupletuple = NULL;+    bool should_free = false;++    oldcxt = MemoryContextSwitchTo(holdCtidContext);++
tuplestore_rescan(holdCtidStore);+   for(;;) {+   tuple = (HeapTuple)tuplestore_gettuple(holdCtidStore,true,&shoul
d_free);+  if(!tuple)+     break;+   tuple->t_self = tuple->t_data->t_ctid;+   ItemPointerSetInvalid(&(tuple->t_data->
;t_ctid));+  if(!ItemPointerIsValid(&(tuple->t_self))) {+     elog(ERROR, "Insert Index: ctid is invalid.");+   }+
ExecStoreTuple(tuple,slot, InvalidBuffer, false);+   ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);+
if(should_free)+    pfree(tuple);+    }+    tuplestore_end(holdCtidStore);+    MemoryContextSwitchTo(holdCtidStore);+
holdCtidContext->methods->delete(holdCtidContext);+  }+  //*/   /*    * Process AFTER EACH STATEMENT triggers    */