Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc29a9c3

Browse files
committed
Fix UPDATE/DELETE WHERE CURRENT OF to support repeated update and update-
then-delete on the current cursor row. The basic fix is that nodeTidscan.chas to apply heap_get_latest_tid() to the current-scan-TID obtained from thecursor query; this ensures we get the latest row version to work with.However, since that only works if the query plan is a TID scan, we also haveto hack the planner to make sure only that type of plan will be selected.(Formerly, the planner might decide to apply a seqscan if the table is verysmall. This change is probably a Good Thing anyway, since it's hard to seehow a seqscan could really win.) That means the execQual.c code to supportCurrentOfExpr as a regular expression type is dead code, so replace it withjust an elog(). Also, add regression tests covering these cases. Notethat the added tests expose the fact that re-fetching an updated rowmisbehaves if the cursor used FOR UPDATE. That's an independent bug thatshould be fixed later. Per report from Dharmendra Goyal.
1 parent9226ba8 commitc29a9c3

File tree

6 files changed

+225
-43
lines changed

6 files changed

+225
-43
lines changed

‎src/backend/executor/execQual.c‎

Lines changed: 6 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.222 2007/09/06 17:31:58 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.223 2007/10/24 18:37:08 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3694,45 +3694,17 @@ ExecEvalArrayCoerceExpr(ArrayCoerceExprState *astate,
36943694
/* ----------------------------------------------------------------
36953695
*ExecEvalCurrentOfExpr
36963696
*
3697-
* Normally, the planner will convert CURRENT OF into a TidScan qualification,
3698-
* but we have plain execQual support in case it doesn't.
3697+
* The planner must convert CURRENT OF into a TidScan qualification.
3698+
* So, we have to be able to do ExecInitExpr on a CurrentOfExpr,
3699+
* but we shouldn't ever actually execute it.
36993700
* ----------------------------------------------------------------
37003701
*/
37013702
staticDatum
37023703
ExecEvalCurrentOfExpr(ExprState*exprstate,ExprContext*econtext,
37033704
bool*isNull,ExprDoneCond*isDone)
37043705
{
3705-
CurrentOfExpr*cexpr= (CurrentOfExpr*)exprstate->expr;
3706-
boolresult;
3707-
boollisnull;
3708-
Oidtableoid;
3709-
ItemPointertuple_tid;
3710-
ItemPointerDatacursor_tid;
3711-
3712-
if (isDone)
3713-
*isDone=ExprSingleResult;
3714-
*isNull= false;
3715-
3716-
Assert(cexpr->cvarno!=INNER);
3717-
Assert(cexpr->cvarno!=OUTER);
3718-
Assert(!TupIsNull(econtext->ecxt_scantuple));
3719-
/* Use slot_getattr to catch any possible mistakes */
3720-
tableoid=DatumGetObjectId(slot_getattr(econtext->ecxt_scantuple,
3721-
TableOidAttributeNumber,
3722-
&lisnull));
3723-
Assert(!lisnull);
3724-
tuple_tid= (ItemPointer)
3725-
DatumGetPointer(slot_getattr(econtext->ecxt_scantuple,
3726-
SelfItemPointerAttributeNumber,
3727-
&lisnull));
3728-
Assert(!lisnull);
3729-
3730-
if (execCurrentOf(cexpr,econtext,tableoid,&cursor_tid))
3731-
result=ItemPointerEquals(&cursor_tid,tuple_tid);
3732-
else
3733-
result= false;
3734-
3735-
returnBoolGetDatum(result);
3706+
elog(ERROR,"CURRENT OF cannot be executed");
3707+
return0;/* keep compiler quiet */
37363708
}
37373709

37383710

‎src/backend/executor/nodeTidscan.c‎

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeTidscan.c,v 1.55 2007/06/11 22:22:40 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeTidscan.c,v 1.56 2007/10/24 18:37:08 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -68,6 +68,7 @@ TidListCreate(TidScanState *tidstate)
6868
tidList= (ItemPointerData*)
6969
palloc(numAllocTids*sizeof(ItemPointerData));
7070
numTids=0;
71+
tidstate->tss_isCurrentOf= false;
7172

7273
foreach(l,evalList)
7374
{
@@ -165,6 +166,7 @@ TidListCreate(TidScanState *tidstate)
165166
numAllocTids*sizeof(ItemPointerData));
166167
}
167168
tidList[numTids++]=cursor_tid;
169+
tidstate->tss_isCurrentOf= true;
168170
}
169171
}
170172
else
@@ -182,6 +184,9 @@ TidListCreate(TidScanState *tidstate)
182184
intlastTid;
183185
inti;
184186

187+
/* CurrentOfExpr could never appear OR'd with something else */
188+
Assert(!tidstate->tss_isCurrentOf);
189+
185190
qsort((void*)tidList,numTids,sizeof(ItemPointerData),
186191
itemptr_comparator);
187192
lastTid=0;
@@ -269,7 +274,8 @@ TidNext(TidScanState *node)
269274

270275
/*
271276
* XXX shouldn't we check here to make sure tuple matches TID list? In
272-
* runtime-key case this is not certain, is it?
277+
* runtime-key case this is not certain, is it? However, in the
278+
* WHERE CURRENT OF case it might not match anyway ...
273279
*/
274280

275281
ExecStoreTuple(estate->es_evTuple[scanrelid-1],
@@ -319,6 +325,15 @@ TidNext(TidScanState *node)
319325
while (node->tss_TidPtr >=0&&node->tss_TidPtr<numTids)
320326
{
321327
tuple->t_self=tidList[node->tss_TidPtr];
328+
329+
/*
330+
* For WHERE CURRENT OF, the tuple retrieved from the cursor might
331+
* since have been updated; if so, we should fetch the version that
332+
* is current according to our snapshot.
333+
*/
334+
if (node->tss_isCurrentOf)
335+
heap_get_latest_tid(heapRelation,snapshot,&tuple->t_self);
336+
322337
if (heap_fetch(heapRelation,snapshot,tuple,&buffer, false,NULL))
323338
{
324339
/*

‎src/backend/optimizer/path/costsize.c‎

Lines changed: 26 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@
5454
* Portions Copyright (c) 1994, Regents of the University of California
5555
*
5656
* IDENTIFICATION
57-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.186 2007/09/22 21:36:40 tgl Exp $
57+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.187 2007/10/24 18:37:08 tgl Exp $
5858
*
5959
*-------------------------------------------------------------------------
6060
*/
@@ -769,6 +769,7 @@ cost_tidscan(Path *path, PlannerInfo *root,
769769
{
770770
Coststartup_cost=0;
771771
Costrun_cost=0;
772+
boolisCurrentOf= false;
772773
Costcpu_per_tuple;
773774
QualCosttid_qual_cost;
774775
intntuples;
@@ -778,9 +779,6 @@ cost_tidscan(Path *path, PlannerInfo *root,
778779
Assert(baserel->relid>0);
779780
Assert(baserel->rtekind==RTE_RELATION);
780781

781-
if (!enable_tidscan)
782-
startup_cost+=disable_cost;
783-
784782
/* Count how many tuples we expect to retrieve */
785783
ntuples=0;
786784
foreach(l,tidquals)
@@ -793,13 +791,35 @@ cost_tidscan(Path *path, PlannerInfo *root,
793791

794792
ntuples+=estimate_array_length(arraynode);
795793
}
794+
elseif (IsA(lfirst(l),CurrentOfExpr))
795+
{
796+
/* CURRENT OF yields 1 tuple */
797+
isCurrentOf= true;
798+
ntuples++;
799+
}
796800
else
797801
{
798802
/* It's just CTID = something, count 1 tuple */
799803
ntuples++;
800804
}
801805
}
802806

807+
/*
808+
* We must force TID scan for WHERE CURRENT OF, because only nodeTidscan.c
809+
* understands how to do it correctly. Therefore, honor enable_tidscan
810+
* only when CURRENT OF isn't present. Also note that cost_qual_eval
811+
* counts a CurrentOfExpr as having startup cost disable_cost, which we
812+
* subtract off here; that's to prevent other plan types such as seqscan
813+
* from winning.
814+
*/
815+
if (isCurrentOf)
816+
{
817+
Assert(baserel->baserestrictcost.startup >=disable_cost);
818+
startup_cost-=disable_cost;
819+
}
820+
elseif (!enable_tidscan)
821+
startup_cost+=disable_cost;
822+
803823
/*
804824
* The TID qual expressions will be computed once, any other baserestrict
805825
* quals once per retrived tuple.
@@ -2002,8 +2022,8 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
20022022
}
20032023
elseif (IsA(node,CurrentOfExpr))
20042024
{
2005-
/*This is noticeably more expensive than a typical operator */
2006-
context->total.per_tuple+=100*cpu_operator_cost;
2025+
/*Report high cost to prevent selection of anything but TID scan */
2026+
context->total.startup+=disable_cost;
20072027
}
20082028
elseif (IsA(node,SubLink))
20092029
{

‎src/include/nodes/execnodes.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.178 2007/09/20 17:56:32 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.179 2007/10/24 18:37:08 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1087,6 +1087,7 @@ typedef struct BitmapHeapScanState
10871087
/* ----------------
10881088
* TidScanState information
10891089
*
1090+
*isCurrentOf scan has a CurrentOfExpr qual
10901091
*NumTids number of tids in this scan
10911092
*TidPtr index of currently fetched tid
10921093
*TidList evaluated item pointers (array of size NumTids)
@@ -1096,6 +1097,7 @@ typedef struct TidScanState
10961097
{
10971098
ScanStatess;/* its first field is NodeTag */
10981099
List*tss_tidquals;/* list of ExprState nodes */
1100+
booltss_isCurrentOf;
10991101
inttss_NumTids;
11001102
inttss_TidPtr;
11011103
inttss_MarkTidPtr;

‎src/test/regress/expected/portals.out‎

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -982,6 +982,139 @@ SELECT * FROM uctest;
982982
8 | one
983983
(2 rows)
984984

985+
-- Check repeated-update and update-then-delete cases
986+
BEGIN;
987+
DECLARE c1 CURSOR FOR SELECT * FROM uctest;
988+
FETCH c1;
989+
f1 | f2
990+
----+-------
991+
3 | three
992+
(1 row)
993+
994+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
995+
SELECT * FROM uctest;
996+
f1 | f2
997+
----+-------
998+
8 | one
999+
13 | three
1000+
(2 rows)
1001+
1002+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
1003+
SELECT * FROM uctest;
1004+
f1 | f2
1005+
----+-------
1006+
8 | one
1007+
23 | three
1008+
(2 rows)
1009+
1010+
-- insensitive cursor should not show effects of updates or deletes
1011+
FETCH RELATIVE 0 FROM c1;
1012+
f1 | f2
1013+
----+-------
1014+
3 | three
1015+
(1 row)
1016+
1017+
DELETE FROM uctest WHERE CURRENT OF c1;
1018+
SELECT * FROM uctest;
1019+
f1 | f2
1020+
----+-----
1021+
8 | one
1022+
(1 row)
1023+
1024+
DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
1025+
SELECT * FROM uctest;
1026+
f1 | f2
1027+
----+-----
1028+
8 | one
1029+
(1 row)
1030+
1031+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
1032+
SELECT * FROM uctest;
1033+
f1 | f2
1034+
----+-----
1035+
8 | one
1036+
(1 row)
1037+
1038+
FETCH RELATIVE 0 FROM c1;
1039+
f1 | f2
1040+
----+-------
1041+
3 | three
1042+
(1 row)
1043+
1044+
ROLLBACK;
1045+
SELECT * FROM uctest;
1046+
f1 | f2
1047+
----+-------
1048+
3 | three
1049+
8 | one
1050+
(2 rows)
1051+
1052+
BEGIN;
1053+
DECLARE c1 CURSOR FOR SELECT * FROM uctest FOR UPDATE;
1054+
FETCH c1;
1055+
f1 | f2
1056+
----+-------
1057+
3 | three
1058+
(1 row)
1059+
1060+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
1061+
SELECT * FROM uctest;
1062+
f1 | f2
1063+
----+-------
1064+
8 | one
1065+
13 | three
1066+
(2 rows)
1067+
1068+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
1069+
SELECT * FROM uctest;
1070+
f1 | f2
1071+
----+-------
1072+
8 | one
1073+
23 | three
1074+
(2 rows)
1075+
1076+
-- sensitive cursor should show effects of updates or deletes
1077+
-- XXX current behavior is WRONG
1078+
FETCH RELATIVE 0 FROM c1;
1079+
f1 | f2
1080+
----+-----
1081+
8 | one
1082+
(1 row)
1083+
1084+
DELETE FROM uctest WHERE CURRENT OF c1;
1085+
SELECT * FROM uctest;
1086+
f1 | f2
1087+
----+-------
1088+
23 | three
1089+
(1 row)
1090+
1091+
DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
1092+
SELECT * FROM uctest;
1093+
f1 | f2
1094+
----+-------
1095+
23 | three
1096+
(1 row)
1097+
1098+
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
1099+
SELECT * FROM uctest;
1100+
f1 | f2
1101+
----+-------
1102+
23 | three
1103+
(1 row)
1104+
1105+
FETCH RELATIVE 0 FROM c1;
1106+
f1 | f2
1107+
----+----
1108+
(0 rows)
1109+
1110+
ROLLBACK;
1111+
SELECT * FROM uctest;
1112+
f1 | f2
1113+
----+-------
1114+
3 | three
1115+
8 | one
1116+
(2 rows)
1117+
9851118
-- Check inheritance cases
9861119
CREATE TEMP TABLE ucchild () inherits (uctest);
9871120
INSERT INTO ucchild values(100, 'hundred');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp