Add an exemptor function for numeric. We store the scale in every datum, making
numeric(7,2)->numeric(8,3) unoptimizable. Precision changes work, though.
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 712,717 **** numeric_send(PG_FUNCTION_ARGS)
--- 712,754 ----
}
+ Datum
+ numeric_exemptor(PG_FUNCTION_ARGS)
+ {
+ int32 old_typmod = PG_GETARG_INT32(0);
+ int32 new_typmod = PG_GETARG_INT32(1);
+ int old_scale;
+ int new_scale;
+ int old_precis;
+ int new_precis;
+
+ /* Destination is unconstrained: never any work to do. */
+ if (new_typmod < VARHDRSZ)
+ PG_RETURN_INT32(COERCE_EXEMPT_NOCHANGE | COERCE_EXEMPT_NOERROR);
+
+ /*
+ * Source is unconstrained, and destination is constrained. A value
like
+ * 10^-1001 will always get truncated in this situation, so we can't
help.
+ */
+ if (old_typmod < VARHDRSZ)
+ PG_RETURN_INT32(0);
+
+ /* Each value stores its scale. If the scale changes, we can't help. */
+ old_scale = (old_typmod - VARHDRSZ) & 0xffff;
+ new_scale = (new_typmod - VARHDRSZ) & 0xffff;
+ if (old_scale != new_scale)
+ PG_RETURN_INT32(0);
+
+ old_precis = (old_typmod - VARHDRSZ) >> 16 & 0xffff;
+ new_precis = (new_typmod - VARHDRSZ) >> 16 & 0xffff;
+ if (new_precis >= old_precis)
+ /* Precision increases with scale unchanged: never any work to
do. */
+ PG_RETURN_INT32(COERCE_EXEMPT_NOCHANGE | COERCE_EXEMPT_NOERROR);
+
+ /* Precision falls with scale unchanged: might error, never changes
data. */
+ PG_RETURN_INT32(COERCE_EXEMPT_NOCHANGE);
+ }
+
/*
* numeric() -
*
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201101103
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201101104
#endif
*** a/src/include/catalog/pg_cast.h
--- b/src/include/catalog/pg_cast.h
***************
*** 357,362 **** DATA(insert ( 1186 1186 1200 3543 i f ));
DATA(insert ( 1266 1266 1969 3541 i f ));
DATA(insert ( 1560 1560 1685 3817 i f ));
DATA(insert ( 1562 1562 1687 3819 i f ));
! DATA(insert ( 1700 1700 1703 0 i f ));
#endif /* PG_CAST_H */
--- 357,362 ----
DATA(insert ( 1266 1266 1969 3541 i f ));
DATA(insert ( 1560 1560 1685 3817 i f ));
DATA(insert ( 1562 1562 1687 3819 i f ));
! DATA(insert ( 1700 1700 1703 3818 i f ));
#endif /* PG_CAST_H */
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2595,2600 **** DATA(insert OID = 2917 ( numerictypmodin PGNSP
PGUID 12 1 0 0 f f f t f i 1 0
--- 2595,2602 ----
DESCR("I/O typmod");
DATA(insert OID = 2918 ( numerictypmodout PGNSP PGUID 12 1 0 0 f
f f t f i 1 0 2275 "23" _null_ _null_ _null_ _null_ numerictypmodout
_null_ _null_ _null_ ));
DESCR("I/O typmod");
+ DATA(insert OID = 3818 ( numeric_exemptor PGNSP PGUID 12 1 0 0 f f f t f i
3 0 23 "23 23 16" _null_ _null_ _null_ _null_ numeric_exemptor _null_ _null_
_null_ ));
+ DESCR("numeric cast exemptor");
DATA(insert OID = 1703 ( numeric PGNSP PGUID 12
1 0 0 f f f t f i 2 0 1700 "1700 23" _null_ _null_ _null_ _null_ numeric _null_
_null_ _null_ ));
DESCR("adjust numeric to typmod precision/scale");
DATA(insert OID = 1704 ( numeric_abs PGNSP PGUID 12 1 0 0 f
f f t f i 1 0 1700 "1700" _null_ _null_ _null_ _null_ numeric_abs _null_ _null_
_null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 878,883 **** extern Datum numeric_recv(PG_FUNCTION_ARGS);
--- 878,884 ----
extern Datum numeric_send(PG_FUNCTION_ARGS);
extern Datum numerictypmodin(PG_FUNCTION_ARGS);
extern Datum numerictypmodout(PG_FUNCTION_ARGS);
+ extern Datum numeric_exemptor(PG_FUNCTION_ARGS);
extern Datum numeric (PG_FUNCTION_ARGS);
extern Datum numeric_abs(PG_FUNCTION_ARGS);
extern Datum numeric_uminus(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 1804,1845 **** DEBUG: Rebuilding index "t_touchy_f_idx"
DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
ALTER TABLE t ALTER constraint3 TYPE numeric(7,2); -- verify; FK noop
! DEBUG: Rewriting table "t"
! DEBUG: Rebuilding index "t_constraint4_key"
! DEBUG: Rebuilding index "t_integral_key"
! DEBUG: Rebuilding index "t_rational_key"
! DEBUG: Rebuilding index "t_daytimetz_key"
! DEBUG: Rebuilding index "t_daytime_key"
! DEBUG: Rebuilding index "t_stamptz_key"
! DEBUG: Rebuilding index "t_stamp_key"
! DEBUG: Rebuilding index "t_timegap_key"
! DEBUG: Rebuilding index "t_bits_key"
! DEBUG: Rebuilding index "t_network_key"
! DEBUG: Rebuilding index "t_string_idx"
! DEBUG: Rebuilding index "t_string_idx1"
! DEBUG: Rebuilding index "t_strarr_idx"
! DEBUG: Rebuilding index "t_square_idx"
! DEBUG: Rebuilding index "t_touchy_f_idx"
! DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
ALTER TABLE t ALTER constraint3 TYPE numeric(9,2); -- noop; FK noop
- DEBUG: Rewriting table "t"
- DEBUG: Rebuilding index "t_constraint4_key"
- DEBUG: Rebuilding index "t_integral_key"
- DEBUG: Rebuilding index "t_rational_key"
- DEBUG: Rebuilding index "t_daytimetz_key"
- DEBUG: Rebuilding index "t_daytime_key"
- DEBUG: Rebuilding index "t_stamptz_key"
- DEBUG: Rebuilding index "t_stamp_key"
- DEBUG: Rebuilding index "t_timegap_key"
- DEBUG: Rebuilding index "t_bits_key"
- DEBUG: Rebuilding index "t_network_key"
- DEBUG: Rebuilding index "t_string_idx"
- DEBUG: Rebuilding index "t_string_idx1"
- DEBUG: Rebuilding index "t_strarr_idx"
- DEBUG: Rebuilding index "t_square_idx"
- DEBUG: Rebuilding index "t_touchy_f_idx"
- DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
-- Change a column with an incoming foreign key constraint.
ALTER TABLE t ALTER constraint4 TYPE numeric(8,1); -- rewrite; FK error
--- 1804,1812 ----
DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
ALTER TABLE t ALTER constraint3 TYPE numeric(7,2); -- verify; FK noop
! DEBUG: Verifying table "t"
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
ALTER TABLE t ALTER constraint3 TYPE numeric(9,2); -- noop; FK noop
DEBUG: Validating foreign key constraint "t_constraint3_fkey"
-- Change a column with an incoming foreign key constraint.
ALTER TABLE t ALTER constraint4 TYPE numeric(8,1); -- rewrite; FK error
***************
*** 1883,1923 **** DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
ALTER TABLE t ALTER constraint4 TYPE numeric(7,2); -- verify; FK noop
- DEBUG: Rewriting table "t"
- DEBUG: Rebuilding index "t_integral_key"
- DEBUG: Rebuilding index "t_rational_key"
- DEBUG: Rebuilding index "t_daytimetz_key"
- DEBUG: Rebuilding index "t_daytime_key"
- DEBUG: Rebuilding index "t_stamptz_key"
- DEBUG: Rebuilding index "t_stamp_key"
- DEBUG: Rebuilding index "t_timegap_key"
- DEBUG: Rebuilding index "t_bits_key"
- DEBUG: Rebuilding index "t_network_key"
- DEBUG: Rebuilding index "t_string_idx"
- DEBUG: Rebuilding index "t_string_idx1"
- DEBUG: Rebuilding index "t_strarr_idx"
- DEBUG: Rebuilding index "t_square_idx"
- DEBUG: Rebuilding index "t_touchy_f_idx"
- DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
ALTER TABLE t ALTER constraint4 TYPE numeric(9,2); -- noop; FK noop
- DEBUG: Rewriting table "t"
- DEBUG: Rebuilding index "t_integral_key"
- DEBUG: Rebuilding index "t_rational_key"
- DEBUG: Rebuilding index "t_daytimetz_key"
- DEBUG: Rebuilding index "t_daytime_key"
- DEBUG: Rebuilding index "t_stamptz_key"
- DEBUG: Rebuilding index "t_stamp_key"
- DEBUG: Rebuilding index "t_timegap_key"
- DEBUG: Rebuilding index "t_bits_key"
- DEBUG: Rebuilding index "t_network_key"
- DEBUG: Rebuilding index "t_string_idx"
- DEBUG: Rebuilding index "t_string_idx1"
- DEBUG: Rebuilding index "t_strarr_idx"
- DEBUG: Rebuilding index "t_square_idx"
- DEBUG: Rebuilding index "t_touchy_f_idx"
- DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
-- Type-specific tests.
--- 1850,1859 ----
DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
ALTER TABLE t ALTER constraint4 TYPE numeric(7,2); -- verify; FK noop
DEBUG: Rebuilding index "t_constraint4_key"
+ DEBUG: Verifying table "t"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
ALTER TABLE t ALTER constraint4 TYPE numeric(9,2); -- noop; FK noop
DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Validating foreign key constraint "child_keycol_fkey"
-- Type-specific tests.
***************
*** 2000,2040 **** DEBUG: Rebuilding index "t_constraint4_key"
DEBUG: Rebuilding index "t_integral_key"
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(13,4);
-- noop
- DEBUG: Rewriting table "t"
- DEBUG: Rebuilding index "t_daytimetz_key"
- DEBUG: Rebuilding index "t_daytime_key"
- DEBUG: Rebuilding index "t_stamptz_key"
- DEBUG: Rebuilding index "t_stamp_key"
- DEBUG: Rebuilding index "t_timegap_key"
- DEBUG: Rebuilding index "t_bits_key"
- DEBUG: Rebuilding index "t_network_key"
- DEBUG: Rebuilding index "t_string_idx"
- DEBUG: Rebuilding index "t_string_idx1"
- DEBUG: Rebuilding index "t_strarr_idx"
- DEBUG: Rebuilding index "t_square_idx"
- DEBUG: Rebuilding index "t_touchy_f_idx"
- DEBUG: Rebuilding index "t_expr_idx"
- DEBUG: Rebuilding index "t_constraint4_key"
- DEBUG: Rebuilding index "t_integral_key"
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(11,4);
-- verify
- DEBUG: Rewriting table "t"
- DEBUG: Rebuilding index "t_daytimetz_key"
- DEBUG: Rebuilding index "t_daytime_key"
- DEBUG: Rebuilding index "t_stamptz_key"
- DEBUG: Rebuilding index "t_stamp_key"
- DEBUG: Rebuilding index "t_timegap_key"
- DEBUG: Rebuilding index "t_bits_key"
- DEBUG: Rebuilding index "t_network_key"
- DEBUG: Rebuilding index "t_string_idx"
- DEBUG: Rebuilding index "t_string_idx1"
- DEBUG: Rebuilding index "t_strarr_idx"
- DEBUG: Rebuilding index "t_square_idx"
- DEBUG: Rebuilding index "t_touchy_f_idx"
- DEBUG: Rebuilding index "t_expr_idx"
- DEBUG: Rebuilding index "t_constraint4_key"
- DEBUG: Rebuilding index "t_integral_key"
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric;
-- noop
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(5,4);
-- verify-e
--- 1936,1945 ----
DEBUG: Rebuilding index "t_integral_key"
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(13,4);
-- noop
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(11,4);
-- verify
DEBUG: Rebuilding index "t_rational_key"
+ DEBUG: Verifying table "t"
ALTER TABLE t ALTER rational TYPE numeric;
-- noop
DEBUG: Rebuilding index "t_rational_key"
ALTER TABLE t ALTER rational TYPE numeric(5,4);
-- verify-e
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
