"wangs...@highgo.com.cn" <wangs...@highgo.com.cn> wrote:
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.
Kevin Grittner <kgri...@ymail.com> wrote:
This patch would allow data in a column which was not consistent
with the column definition:
test=# create table n (val numeric(5,2));
CREATE TABLE
test=# insert into n values ('123.45');
INSERT 0 1
test=# select * from n;
val
--------
123.45
(1 row)
test=# alter table n alter column val type numeric(5,4);
ALTER TABLE
test=# select * from n;
val
--------
123.45
(1 row)
Without your patch the ALTER TABLE command gets this error (as it
should):
test=# alter table n alter column val type numeric(5,4);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 4 must round to an absolute
value less than 10^1.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Thanks for your reply and test.
I had added a new function named ATNumericColumnChangeRequiresCheck to
check the data
when the scale of numeric increase.
Now,the ALTER TABLE command could prompt this error:
postgres=# alter table tt alter COLUMN t1 type numeric (5,4);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 4 must round to an absolute
value less than 10^1.
STATEMENT: alter table tt alter COLUMN t1 type numeric (5,4);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 4 must round to an absolute
value less than 10^1.
I packed a new patch about this modification.
I think this ' altering field type model ' could modify all the type
in database.
Make different modification to column‘s datatype for different
situation.
For example when you modify the scale of numeric, if you think that the
5.0 and 5.00 is different,
the table file must be rewritten; otherwise, needn't be rewritten.
Wang Shuo
HighGo Software Co.,Ltd.
September 16, 2013
diff -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-16 16:33:49.527455560 +0800
@@ -367,7 +367,10 @@
AlteredTableInfo *tab, Relation rel,
bool recurse, bool recursing,
AlterTableCmd *cmd, LOCKMODE lockmode);
-static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
+static void ATNumericColumnChangeRequiresCheck(AlteredTableInfo *tab);
+static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno,
+ int32 oldtypemod, int32 newtypemod,
+ AlteredTableInfo *tab);
static void ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
static void ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
@@ -7480,7 +7483,7 @@
newval->expr = (Expr *) transform;
tab->newvals = lappend(tab->newvals, newval);
- if (ATColumnChangeRequiresRewrite(transform, attnum))
+ if (ATColumnChangeRequiresRewrite(transform, attnum, attTup->atttypmod, targettypmod, tab))
tab->rewrite = true;
}
else if (transform)
@@ -7520,6 +7523,102 @@
}
/*
+ * check the data when the scale of numeric increase
+ */
+
+static void
+ATNumericColumnChangeRequiresCheck(AlteredTableInfo *tab)
+{
+ Relation oldrel;
+ TupleDesc oldTupDesc;
+ int i;
+ ListCell *l;
+ EState *estate;
+ ExprContext *econtext;
+ bool *isnull;
+ TupleTableSlot *oldslot;
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Snapshot snapshot;
+ List *dropped_attrs = NIL;
+ ListCell *lc;
+ Datum *values;
+
+ /*
+ * Open the relation(s). We have surely already locked the existing
+ * table.
+ */
+
+ oldrel = heap_open(tab->relid, NoLock);
+ oldTupDesc = tab->oldDesc;
+
+ for (i = 0; i < oldTupDesc->natts; i++)
+ {
+ if (oldTupDesc->attrs[i]->attisdropped)
+ dropped_attrs = lappend_int(dropped_attrs, i);
+ }
+ /*
+ * Generate the constraint and default execution states
+ */
+
+ estate = CreateExecutorState();
+
+ foreach(l, tab->newvals)
+ {
+ NewColumnValue *ex = lfirst(l);
+
+ /* expr already planned */
+ ex->exprstate = ExecInitExpr((Expr *) ex->expr, NULL);
+ }
+
+ econtext = GetPerTupleExprContext(estate);
+ oldslot = MakeSingleTupleTableSlot(oldTupDesc);
+
+ /* Preallocate values/isnull arrays */
+ i = oldTupDesc->natts;
+ values = (Datum *) palloc(i * sizeof(Datum));
+ isnull = (bool *) palloc(i * sizeof(bool));
+ memset(values, 0, i * sizeof(Datum));
+ memset(isnull, true, i * sizeof(bool));
+
+ /*
+ * Scan through the rows, generating a new row if needed and then
+ * checking all the constraints.
+ */
+ snapshot = RegisterSnapshot(GetLatestSnapshot());
+ scan = heap_beginscan(oldrel, snapshot, 0, NULL);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ foreach(lc, dropped_attrs)
+ isnull[lfirst_int(lc)] = true;
+
+ heap_deform_tuple(tuple, oldTupDesc, values, isnull);
+ ExecStoreTuple(tuple, oldslot, InvalidBuffer, false);
+ econtext->ecxt_scantuple = oldslot;
+
+ foreach(l, tab->newvals)
+ {
+
+ NewColumnValue *ex = lfirst(l);
+
+ ExecEvalExpr(ex->exprstate, econtext,
+ &isnull[ex->attnum - 1], NULL);
+ }
+
+ CHECK_FOR_INTERRUPTS();
+ }
+ heap_endscan(scan);
+
+ UnregisterSnapshot(snapshot);
+
+ ExecDropSingleTupleTableSlot(oldslot);
+
+ FreeExecutorState(estate);
+
+ heap_close(oldrel, NoLock);
+}
+
+/*
* When the data type of a column is changed, a rewrite might not be required
* if the new type is sufficiently identical to the old one, and the USING
* clause isn't trying to insert some other value. It's safe to skip the
@@ -7530,7 +7629,9 @@
* try to do that.
*/
static bool
-ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno)
+ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno,
+ int32 oldtypemod, int32 newtypemod,
+ AlteredTableInfo *tab)
{
Assert(expr != NULL);
@@ -7549,6 +7650,21 @@
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)
+ {
+ ATNumericColumnChangeRequiresCheck(tab);
+ 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
