Re: Re: [HACKERS] Is it necessary to rewrite table whileincreasing the scale of datatype numeric？

于 2013-09-06 01:41, Jeff Janes 回复:
> On Wed, Sep 4, 2013 at 10:06 PM,  <wangshuo@highgo.com.cn> wrote:
>> 于 2013-09-04 23:41, Jeff Janes 回复:
>>
>>> On Tue, Sep 3, 2013 at 9:08 PM,  <wangshuo@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.janes@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.janes@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, 2013