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,v
retrieving revision 1.1.1.1
diff -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) {
+ HeapTuple tuple = 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
*/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
