于 2013-09-06 01:41, Jeff Janes 回复:
On Wed, Sep 4, 2013 at 10:06 PM, <wangs...@highgo.com.cn> wrote:
于 2013-09-04 23:41, Jeff Janes 回复:
On Tue, Sep 3, 2013 at 9:08 PM, <wangs...@highgo.com.cn> wrote:
Hi, Hackers!
I find that it takes a long time when I increase the scale of a
numeric
datatype.
By checking the code, I found that's because it needs to rewrite
that
table's file.
After checking that table's data file, I found only parameter
n_header
changed.
And, I found the data in that numeric field never changed.
So I thank It's not necessary to rewrite the table's file in this
case.
Anyone has more idea about this, please come to talk about this!
Jeff Janes <jeff.ja...@gmail.com> wrote:
This was fixed in version 9.2. You must be using an older version.
Cheers,
Jeff
Thanks for your reply.
To declare a column of type numeric use the syntax:
NUMERIC(precision, scale).
What I said is this scale,not yours.
Jeff Janes <jeff.ja...@gmail.com> wrote:
You're right, I had tested a change in precision, not in scale.
Sorry.
In order to avoid the rewrite, the code would have to be changed to
look up the column definition and if it specifies the scale, then
ignore the per-row n_header, and look at the n_header only if the
column is NUMERIC with no precision or scale. That should
conceptually be possible, but I don't know how hard it would be to
implement--it sounds pretty invasive to me. Then if the column was
altered from NUMERIC with scale to be a plain NUMERIC, it would have
to rewrite the table to enforce the row-wise scale to match the old
column-wise scale. Where as now that alter doesn't need a re-write.
I don't know if this would be an overall gain or not.
Cheers,
Jeff
I modified the code for this situation.I consider it very simple.
It will does not modify the table file, when the scale has been
increased exclusively.
I modified the code , as follow:
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber
varattno, int32 oldtypemod, int32 newtypemod);
in function ATExecAlterColumnGenericOptions:
if (ATColumnChangeRequiresRewrite(transform, attnum,
attTup->atttypmod, targettypmod))
tab->rewrite = true;
in the function ATColumnChangeRequiresRewrite：
else if (IsA(expr, FuncExpr))
{
int32 between = 0;
/*
* Check whether funcresulttype == 1700 and
funcid == 1703 when user modify datatype.
* If true, then we know user modify the
datatype numeric;
* Then we go to get value 'between'.
*/
if(((FuncExpr *) expr)->funcresulttype == 1700 && ((FuncExpr *)
expr)->funcid == 1703)
between = newtypemod - oldtypemod;
/*
* If 'between' satisfy the following
condition,
* Then we know the scale of the numeric was
increased.
*/
if(between > 0 && between < 1001)
return false;
else
return true;
}
I packed a patch about this modification.
Wang Shuo
HighGo Software Co.,Ltd.
September 6, 2013diff -uNr b/src/backend/commands/tablecmds.c a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c 2013-08-31 17:11:00.529744869 +0800
+++ a/src/backend/commands/tablecmds.c 2013-09-04 11:20:28.797652760 +0800
@@ -367,7 +367,7 @@
AlteredTableInfo *tab, Relation rel,
bool recurse, bool recursing,
AlterTableCmd *cmd, LOCKMODE lockmode);
-static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
+static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno, int32 oldtypemod, int32 newtypemod);
static void ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
static void ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
@@ -7480,7 +7480,7 @@
newval->expr = (Expr *) transform;
tab->newvals = lappend(tab->newvals, newval);
- if (ATColumnChangeRequiresRewrite(transform, attnum))
+ if (ATColumnChangeRequiresRewrite(transform, attnum, attTup->atttypmod, targettypmod))
tab->rewrite = true;
}
else if (transform)
@@ -7530,7 +7530,7 @@
* try to do that.
*/
static bool
-ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno)
+ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno, int32 oldtypemod, int32 newtypemod)
{
Assert(expr != NULL);
@@ -7549,6 +7549,18 @@
return true;
expr = (Node *) d->arg;
}
+ else if (IsA(expr, FuncExpr))
+ {
+ int32 between = 0;
+
+ if(((FuncExpr *) expr)->funcresulttype == 1700 && ((FuncExpr *) expr)->funcid == 1703)
+ between = newtypemod - oldtypemod;
+
+ if(between > 0 && between < 1001)
+ return false;
+ else
+ return true;
+ }
else
return true;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
