Skip to content

Commit e033506

Browse files
zilderzilder
committed
numeric and float interval validation fixes
1 parent cca1e8b commit e033506

File tree

4 files changed

+171
-9
lines changed

4 files changed

+171
-9
lines changed

‎README.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -231,7 +231,7 @@ Drop partitions of the `parent` table (both foreign and local relations). If `de
231231
```plpgsql
232232
set_interval(relation REGCLASS, value ANYELEMENT)
233233
```
234-
Update RANGE partitioned table interval.
234+
Update RANGE partitioned table interval. Note that interval must not be negative and it must not be trivial, i.e. its value should be greater than zero for numeric types, at least 1 microsecond for `TIMESTAMP` and at least 1 day for `DATE`.
235235

236236
```plpgsql
237237
set_enable_parent(relation REGCLASS, value BOOLEAN)

‎expected/pathman_interval.out

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,99 @@ SELECT * FROM pathman_config;
7272
abc | dt | 2 | @ 1 mon
7373
(1 row)
7474

75+
DROP TABLE abc cascade;
76+
NOTICE: drop cascades to 2 other objects
77+
/* Range partitions for FLOAT4 type */
78+
CREATE TABLE abc (x FLOAT4 NOT NULL);
79+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
80+
create_range_partitions
81+
-------------------------
82+
2
83+
(1 row)
84+
85+
SELECT set_interval('abc', NULL::FLOAT4);
86+
set_interval
87+
--------------
88+
89+
(1 row)
90+
91+
/* Set a trivial interval */
92+
SELECT set_interval('abc', 0);
93+
ERROR: interval must not be trivial
94+
/* Set NaN float as interval */
95+
SELECT set_interval('abc', 'NaN'::FLOAT4);
96+
ERROR: invalid floating point interval
97+
/* Set INF float as interval */
98+
SELECT set_interval('abc', 'Infinity'::FLOAT4);
99+
ERROR: invalid floating point interval
100+
/* Set a normal interval */
101+
SELECT set_interval('abc', 100);
102+
set_interval
103+
--------------
104+
105+
(1 row)
106+
107+
DROP TABLE abc cascade;
108+
NOTICE: drop cascades to 2 other objects
109+
/* Range partitions for FLOAT8 type */
110+
CREATE TABLE abc (x FLOAT4 NOT NULL);
111+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
112+
create_range_partitions
113+
-------------------------
114+
2
115+
(1 row)
116+
117+
SELECT set_interval('abc', NULL::FLOAT8);
118+
set_interval
119+
--------------
120+
121+
(1 row)
122+
123+
/* Set a trivial interval */
124+
SELECT set_interval('abc', 0);
125+
ERROR: interval must not be trivial
126+
/* Set NaN float as interval */
127+
SELECT set_interval('abc', 'NaN'::FLOAT8);
128+
ERROR: invalid floating point interval
129+
/* Set INF float as interval */
130+
SELECT set_interval('abc', 'Infinity'::FLOAT8);
131+
ERROR: invalid floating point interval
132+
/* Set a normal interval */
133+
SELECT set_interval('abc', 100);
134+
set_interval
135+
--------------
136+
137+
(1 row)
138+
139+
DROP TABLE abc cascade;
140+
NOTICE: drop cascades to 2 other objects
141+
/* Range partitions for NUMERIC type */
142+
CREATE TABLE abc (x NUMERIC NOT NULL);
143+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
144+
create_range_partitions
145+
-------------------------
146+
2
147+
(1 row)
148+
149+
SELECT set_interval('abc', NULL::NUMERIC);
150+
set_interval
151+
--------------
152+
153+
(1 row)
154+
155+
/* Set a trivial interval */
156+
SELECT set_interval('abc', 0);
157+
ERROR: interval must not be trivial
158+
/* Set NaN numeric as interval */
159+
SELECT set_interval('abc', 'NaN'::NUMERIC);
160+
ERROR: invalid numeric interval
161+
/* Set a normal interval */
162+
SELECT set_interval('abc', 100);
163+
set_interval
164+
--------------
165+
166+
(1 row)
167+
75168
DROP TABLE abc cascade;
76169
NOTICE: drop cascades to 2 other objects
77170
/* Hash partitioned table shouldn't accept any interval value */

‎sql/pathman_interval.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,46 @@ SELECT set_interval('abc', '1 month'::INTERVAL);
3030
SELECT * FROM pathman_config;
3131
DROP TABLE abc cascade;
3232

33+
/* Range partitions for FLOAT4 type */
34+
CREATE TABLE abc (x FLOAT4 NOT NULL);
35+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
36+
SELECT set_interval('abc', NULL::FLOAT4);
37+
/* Set a trivial interval */
38+
SELECT set_interval('abc', 0);
39+
/* Set NaN float as interval */
40+
SELECT set_interval('abc', 'NaN'::FLOAT4);
41+
/* Set INF float as interval */
42+
SELECT set_interval('abc', 'Infinity'::FLOAT4);
43+
/* Set a normal interval */
44+
SELECT set_interval('abc', 100);
45+
DROP TABLE abc cascade;
46+
47+
/* Range partitions for FLOAT8 type */
48+
CREATE TABLE abc (x FLOAT4 NOT NULL);
49+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
50+
SELECT set_interval('abc', NULL::FLOAT8);
51+
/* Set a trivial interval */
52+
SELECT set_interval('abc', 0);
53+
/* Set NaN float as interval */
54+
SELECT set_interval('abc', 'NaN'::FLOAT8);
55+
/* Set INF float as interval */
56+
SELECT set_interval('abc', 'Infinity'::FLOAT8);
57+
/* Set a normal interval */
58+
SELECT set_interval('abc', 100);
59+
DROP TABLE abc cascade;
60+
61+
/* Range partitions for NUMERIC type */
62+
CREATE TABLE abc (x NUMERIC NOT NULL);
63+
SELECT create_range_partitions('abc', 'x', 0, 100, 2);
64+
SELECT set_interval('abc', NULL::NUMERIC);
65+
/* Set a trivial interval */
66+
SELECT set_interval('abc', 0);
67+
/* Set NaN numeric as interval */
68+
SELECT set_interval('abc', 'NaN'::NUMERIC);
69+
/* Set a normal interval */
70+
SELECT set_interval('abc', 100);
71+
DROP TABLE abc cascade;
72+
3373
/* Hash partitioned table shouldn't accept any interval value */
3474
CREATE TABLE abc (id SERIAL);
3575
SELECT create_hash_partitions('abc', 'id', 3);

‎src/pl_range_funcs.c

Lines changed: 37 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -686,7 +686,10 @@ interval_is_trivial(Oid atttype, Datum interval, Oid interval_type)
686686
FmgrInfo cmp_func;
687687
int32 cmp_result;
688688

689-
/* Generate default value */
689+
/*
690+
* Generate default value. For float4 and float8 values we also check
691+
* that they aren't NaN or INF
692+
*/
690693
switch(atttype)
691694
{
692695
case INT2OID:
@@ -695,14 +698,40 @@ interval_is_trivial(Oid atttype, Datum interval, Oid interval_type)
695698
default_value = Int16GetDatum(0);
696699
break;
697700
case FLOAT4OID:
698-
default_value = Float4GetDatum(0);
699-
break;
701+
{
702+
float4 f = DatumGetFloat4(interval);
703+
704+
if (isnan(f) || is_infinite(f))
705+
elog(ERROR, "invalid floating point interval");
706+
default_value = Float4GetDatum(0);
707+
break;
708+
}
700709
case FLOAT8OID:
701-
default_value = Float8GetDatum(0);
702-
break;
710+
{
711+
float8 f = DatumGetFloat8(interval);
712+
713+
if (isnan(f) || is_infinite(f))
714+
elog(ERROR, "invalid floating point interval");
715+
default_value = Float8GetDatum(0);
716+
break;
717+
}
703718
case NUMERICOID:
704-
default_value = NumericGetDatum(0);
705-
break;
719+
{
720+
Numeric ni = DatumGetNumeric(interval);
721+
Numeric numeric;
722+
723+
/* Test for NaN */
724+
if (numeric_is_nan(ni))
725+
elog(ERROR, "invalid numeric interval");
726+
727+
/* Building default value */
728+
numeric = DatumGetNumeric(DirectFunctionCall3(numeric_in,
729+
CStringGetDatum("0"),
730+
ObjectIdGetDatum(InvalidOid),
731+
Int32GetDatum(-1)));
732+
default_value = NumericGetDatum(numeric);
733+
break;
734+
}
706735
case TIMESTAMPOID:
707736
case TIMESTAMPTZOID:
708737
default_value = TimestampGetDatum(GetCurrentTimestamp());
@@ -712,8 +741,8 @@ interval_is_trivial(Oid atttype, Datum interval, Oid interval_type)
712741
Datum ts = TimestampGetDatum(GetCurrentTimestamp());
713742

714743
default_value = perform_type_cast(ts, TIMESTAMPTZOID, DATEOID, NULL);
744+
break;
715745
}
716-
break;
717746
default:
718747
return false;
719748
}

0 commit comments

Comments
 (0)