Re: [HACKERS] Unique index: update error
On Fri, 2006-09-15 at 09:16 +0800, Golden Liu wrote: this problem I'm sorry but I don't see any problem. Why would you want to issue that kind of SQL statement? Assuming you really do, why not just DELETE/re-INSERT ? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unique index: update error
On 9/18/06, Tom Lane [EMAIL PROTECTED] wrote: Jim Nasby [EMAIL PROTECTED] writes: On Sep 14, 2006, at 9:16 PM, Golden Liu wrote: 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. I suspect that your change adds a non-trivial overhead, which means we don't want it to be the normal case. There's a bigger problem: begin; update tab set col1 = ... where unique_key = ...; update tab set col2 = ... where unique_key = ...; commit; If the first update doesn't insert index entries into unique_key's index, then the second update won't find the tuples it needs to update (unless we hack the planner to not trust the index as valid ... and then it'd fall back on a seqscan, which is hardly acceptable anyway). The first update DOES insert index entries into unique_key's index. In fact, index entries will be inserted after each command, not each transaction. Is this right? Or should we insert index entries after each transaction? The scheme that I've thought about involves inserting index entries as usual, but instead of having the aminsert code error out immediately upon finding a duplicate, have it make an entry in a list of things that need to be rechecked before commit. This wins as long as potential conflicts are uncommon. Performance could suck if the list gets too large --- but we have more or less the same hazard now for foreign-key checks, and it mostly works well enough. (In fact, maybe the existing deferred trigger event list is the thing to use for the deferred conflict rechecks.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Unique index: update error
Golden Liu [EMAIL PROTECTED] writes: ... The first update DOES insert index entries into unique_key's index. Right, but weren't you proposing to make it not do so? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Unique index: update error
On Sep 14, 2006, at 9:16 PM, Golden Liu wrote: 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. Isn't that what a deferred constraint normally does? I suspect that your change adds a non-trivial overhead, which means we don't want it to be the normal case. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Unique index: update error
Jim Nasby [EMAIL PROTECTED] writes: On Sep 14, 2006, at 9:16 PM, Golden Liu wrote: 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. I suspect that your change adds a non-trivial overhead, which means we don't want it to be the normal case. There's a bigger problem: begin; update tab set col1 = ... where unique_key = ...; update tab set col2 = ... where unique_key = ...; commit; If the first update doesn't insert index entries into unique_key's index, then the second update won't find the tuples it needs to update (unless we hack the planner to not trust the index as valid ... and then it'd fall back on a seqscan, which is hardly acceptable anyway). The scheme that I've thought about involves inserting index entries as usual, but instead of having the aminsert code error out immediately upon finding a duplicate, have it make an entry in a list of things that need to be rechecked before commit. This wins as long as potential conflicts are uncommon. Performance could suck if the list gets too large --- but we have more or less the same hazard now for foreign-key checks, and it mostly works well enough. (In fact, maybe the existing deferred trigger event list is the thing to use for the deferred conflict rechecks.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Unique index: update error
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 - 1.1.1.1
--- backend/executor/execMain.c 7 Sep 2006 08:28:34 -
***
*** 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
