From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Transform for pl/perl
Date: Thu, 26 Apr 2018 15:30:44 -0400
Message-ID: <ba47d0fe-43b2-c19c-810d-5c35ddf443bd@2ndquadrant.com> (raw)
In-Reply-To: <84fba634-71ba-4482-7e1b-5877bff5c6cb@2ndQuadrant.com>
References: <20171127132439.23dc26ef@anthony-24-g082ur>
<CAEepm=02=Ya7p=-X5Of=JD-+9+bmmwnpd9nr8AeVnOopB6Y+NQ@mail.gmail.com>
<20180112114739.6a9f0d4c@anthony-24-g082ur>
<20180131103621.GA19345@zakirov.localdomain>
<20180215125313.290468ae@anthony-24-g082ur>
<CAFj8pRCYBPFGZG5LHdfY1Ej+a_9sb3ugyDkUaJGD37UjpRpA8Q@mail.gmail.com>
<20180312110821.29c80420@anthony-24-g082ur>
<CAFj8pRDaTYHD30CP8u2v_wwsJfxFBfthcNEp_KgEOHVN0gUfAw@mail.gmail.com>
<505345c5-0159-077d-f64e-60afb01e5fbe@postgrespro.ru>
<CAFj8pRB1mLEvmnhRL9HheF6yeKPvuzBf2tQ=s+2QXGpnCJdbqQ@mail.gmail.com>
<26a0840a-6b09-44fc-9e4e-8200c1887882@2ndquadrant.com>
<d8jtvskjzzs.fsf@dalvik.ping.uio.no>
<d8jefjojudc.fsf@dalvik.ping.uio.no>
<15075.1523287460@sss.pgh.pa.us>
<d8ja7ubjnyp.fsf@dalvik.ping.uio.no>
<17423.1523368349@sss.pgh.pa.us>
<d8j604zjgtd.fsf@dalvik.ping.uio.no>
<d8j1sfnjfpb.fsf@dalvik.ping.uio.no>
<5d7b91e8-ea88-6811-e215-e7ea1013dca9@2ndquadrant.com>
<84fba634-71ba-4482-7e1b-5877bff5c6cb@2ndQuadrant.com>
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 <peter_e@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.outindex 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 objectsdiff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu.out b/contrib/jsonb_plperl/expected/jsonb_plperlu.outindex 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 objectsdiff --git a/contrib/jsonb_plperl/jsonb_plperl.c b/contrib/jsonb_plperl/jsonb_plperl.cindex 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.sqlindex 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.sqlindex 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
Attachments:
[text/plain] 0001-Prevent-infinity-and-NaN-in-jsonb-plperl-transform.patch (6.5K, ../ba47d0fe-43b2-c19c-810d-5c35ddf443bd@2ndquadrant.com/2-0001-Prevent-infinity-and-NaN-in-jsonb-plperl-transform.patch)
download | inline diff:
From ec8529348008e58826442e43809772c19d02a067 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@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.outindex 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 objectsdiff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu.out b/contrib/jsonb_plperl/expected/jsonb_plperlu.outindex 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 objectsdiff --git a/contrib/jsonb_plperl/jsonb_plperl.c b/contrib/jsonb_plperl/jsonb_plperl.cindex 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.sqlindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, andrew.dunstan@2ndquadrant.com, ilmari@ilmari.org, tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, n.gluhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Transform for pl/perl
In-Reply-To: <ba47d0fe-43b2-c19c-810d-5c35ddf443bd@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox