On 4/24/18 14:31, Andrew Dunstan wrote:
> There is the routine IsValidJsonNumber that helps - see among others
> hstore_io.c for an example use.
I would need something like that taking a double/float8 input. But I
think there is no such shortcut available, so I just wrote out the tests
for isinf and isnan explicitly. Attached patch should fix it.
jsonb_plpython will need a similar fix.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From ec8529348008e58826442e43809772c19d02a067 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Thu, 26 Apr 2018 15:20:43 -0400
Subject: [PATCH] Prevent infinity and NaN in jsonb/plperl transform
jsonb uses numeric internally, and numeric can store NaN, but that is
not allowed by jsonb on input, so we shouldn't store it. Also prevent
infinity to get a consistent error message. (numeric input would reject
infinity anyway.)
---
.../jsonb_plperl/expected/jsonb_plperl.out | 24 +++++++++++++++++--
.../jsonb_plperl/expected/jsonb_plperlu.out | 24 +++++++++++++++++--
contrib/jsonb_plperl/jsonb_plperl.c | 16 +++++++++++--
contrib/jsonb_plperl/sql/jsonb_plperl.sql | 23 +++++++++++++++++-
contrib/jsonb_plperl/sql/jsonb_plperlu.sql | 22 +++++++++++++++++
5 files changed, 102 insertions(+), 7 deletions(-)
diff --git a/contrib/jsonb_plperl/expected/jsonb_plperl.out
b/contrib/jsonb_plperl/expected/jsonb_plperl.out
index 99a2e8e135..d6c3becf63 100644
--- a/contrib/jsonb_plperl/expected/jsonb_plperl.out
+++ b/contrib/jsonb_plperl/expected/jsonb_plperl.out
@@ -39,6 +39,26 @@ SELECT testSVToJsonb();
1
(1 row)
+CREATE FUNCTION testInf() RETURNS jsonb
+LANGUAGE plperl
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'Inf';
+return $val;
+$$;
+SELECT testInf();
+ERROR: cannot convert infinity to jsonb
+CONTEXT: PL/Perl function "testinf"
+CREATE FUNCTION testNaN() RETURNS jsonb
+LANGUAGE plperl
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'NaN';
+return $val;
+$$;
+SELECT testNaN();
+ERROR: cannot convert NaN to jsonb
+CONTEXT: PL/Perl function "testnan"
-- this revealed a bug in the original implementation
CREATE FUNCTION testRegexpResultToJsonb() RETURNS jsonb
LANGUAGE plperl
@@ -71,7 +91,7 @@ SELECT roundtrip('1');
(1 row)
SELECT roundtrip('1E+131071');
-ERROR: cannot convert infinite value to jsonb
+ERROR: cannot convert infinity to jsonb
CONTEXT: PL/Perl function "roundtrip"
SELECT roundtrip('-1');
roundtrip
@@ -207,4 +227,4 @@ SELECT roundtrip('{"1": {"2": [3, 4, 5]}, "2": 3}');
\set VERBOSITY terse \\ -- suppress cascade details
DROP EXTENSION plperl CASCADE;
-NOTICE: drop cascades to 6 other objects
+NOTICE: drop cascades to 8 other objects
diff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu.out
b/contrib/jsonb_plperl/expected/jsonb_plperlu.out
index 8053cf6aa8..65ed21f3b2 100644
--- a/contrib/jsonb_plperl/expected/jsonb_plperlu.out
+++ b/contrib/jsonb_plperl/expected/jsonb_plperlu.out
@@ -39,6 +39,26 @@ SELECT testSVToJsonb();
1
(1 row)
+CREATE FUNCTION testInf() RETURNS jsonb
+LANGUAGE plperlu
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'Inf';
+return $val;
+$$;
+SELECT testInf();
+ERROR: cannot convert infinity to jsonb
+CONTEXT: PL/Perl function "testinf"
+CREATE FUNCTION testNaN() RETURNS jsonb
+LANGUAGE plperlu
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'NaN';
+return $val;
+$$;
+SELECT testNaN();
+ERROR: cannot convert NaN to jsonb
+CONTEXT: PL/Perl function "testnan"
-- this revealed a bug in the original implementation
CREATE FUNCTION testRegexpResultToJsonb() RETURNS jsonb
LANGUAGE plperlu
@@ -71,7 +91,7 @@ SELECT roundtrip('1');
(1 row)
SELECT roundtrip('1E+131071');
-ERROR: cannot convert infinite value to jsonb
+ERROR: cannot convert infinity to jsonb
CONTEXT: PL/Perl function "roundtrip"
SELECT roundtrip('-1');
roundtrip
@@ -207,4 +227,4 @@ SELECT roundtrip('{"1": {"2": [3, 4, 5]}, "2": 3}');
\set VERBOSITY terse \\ -- suppress cascade details
DROP EXTENSION plperlu CASCADE;
-NOTICE: drop cascades to 6 other objects
+NOTICE: drop cascades to 8 other objects
diff --git a/contrib/jsonb_plperl/jsonb_plperl.c
b/contrib/jsonb_plperl/jsonb_plperl.c
index 837bae2ab5..3f9802c696 100644
--- a/contrib/jsonb_plperl/jsonb_plperl.c
+++ b/contrib/jsonb_plperl/jsonb_plperl.c
@@ -209,10 +209,22 @@ SV_to_JsonbValue(SV *in, JsonbParseState **jsonb_state,
bool is_elem)
{
double nval = SvNV(in);
+ /*
+ * jsonb doesn't allow infinity or NaN (per JSON
+ * specification), but the numeric type that is
used for the
+ * storage accepts NaN, so we have to prevent
it here
+ * explicitly. We don't really have to check
for isinf()
+ * here, as numeric doesn't allow it and it
would be caught
+ * later, but it makes for a nicer error
message.
+ */
if (isinf(nval))
ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- (errmsg("cannot
convert infinite value to jsonb"))));
+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ (errmsg("cannot
convert infinity to jsonb"))));
+ if (isnan(nval))
+ ereport(ERROR,
+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ (errmsg("cannot
convert NaN to jsonb"))));
out.type = jbvNumeric;
out.val.numeric =
diff --git a/contrib/jsonb_plperl/sql/jsonb_plperl.sql
b/contrib/jsonb_plperl/sql/jsonb_plperl.sql
index 8b0a8764af..b543579eab 100644
--- a/contrib/jsonb_plperl/sql/jsonb_plperl.sql
+++ b/contrib/jsonb_plperl/sql/jsonb_plperl.sql
@@ -34,6 +34,28 @@ CREATE FUNCTION testSVToJsonb() RETURNS jsonb
SELECT testSVToJsonb();
+CREATE FUNCTION testInf() RETURNS jsonb
+LANGUAGE plperl
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'Inf';
+return $val;
+$$;
+
+SELECT testInf();
+
+
+CREATE FUNCTION testNaN() RETURNS jsonb
+LANGUAGE plperl
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'NaN';
+return $val;
+$$;
+
+SELECT testNaN();
+
+
-- this revealed a bug in the original implementation
CREATE FUNCTION testRegexpResultToJsonb() RETURNS jsonb
LANGUAGE plperl
@@ -83,6 +105,5 @@ CREATE FUNCTION roundtrip(val jsonb) RETURNS jsonb
SELECT roundtrip('{"1": {"2": [3, 4, 5]}, "2": 3}');
-
\set VERBOSITY terse \\ -- suppress cascade details
DROP EXTENSION plperl CASCADE;
diff --git a/contrib/jsonb_plperl/sql/jsonb_plperlu.sql
b/contrib/jsonb_plperl/sql/jsonb_plperlu.sql
index 9287f7672f..9c0c083c99 100644
--- a/contrib/jsonb_plperl/sql/jsonb_plperlu.sql
+++ b/contrib/jsonb_plperl/sql/jsonb_plperlu.sql
@@ -34,6 +34,28 @@ CREATE FUNCTION testSVToJsonb() RETURNS jsonb
SELECT testSVToJsonb();
+CREATE FUNCTION testInf() RETURNS jsonb
+LANGUAGE plperlu
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'Inf';
+return $val;
+$$;
+
+SELECT testInf();
+
+
+CREATE FUNCTION testNaN() RETURNS jsonb
+LANGUAGE plperlu
+TRANSFORM FOR TYPE jsonb
+AS $$
+$val = 0 + 'NaN';
+return $val;
+$$;
+
+SELECT testNaN();
+
+
-- this revealed a bug in the original implementation
CREATE FUNCTION testRegexpResultToJsonb() RETURNS jsonb
LANGUAGE plperlu
base-commit: dd4cc9d7069c04d0ee8ae040511274c6e71b60f7
--
2.17.0
