Re: Transform for pl/perl - Mailing list pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

> ilmari@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> I think you'd have to convert to text and back.  That's kind of icky,
>>> but it beats failing.
>
>> I had a look, and that's what the PL/Python transform does.  Attached is
>> a patch that does that for PL/Perl too, but only if the value is
>> actually > PG_INT64_MAX.
>
>> The secondary output files are for Perls with 32bit IV/UV types, but I
>> haven't been able to test them, since Debian's Perl uses 64bit integers
>> even on 32bit platforms.
>
> Ugh.  I really don't want to maintain a separate expected-file for this,
> especially not if it's going to be hard to test.  Can we choose another
> way of exercising the code path?

How about a plperl function that returns ~0 as numeric, and doing

    select testuvtojsonb()::numeric = plperlu_maxuint();

in the test?

> Another issue with this code as written is that on 32-bit-UV platforms,
> at least some vompilers will give warnings about the constant-false
> predicate.  Not sure about a good solution for that.  Maybe it's a
> sufficient reason to invent uint8_numeric so we don't need a range check.

Yes, that does push the needle towards it being worth doing.

While playing around some more with the extension, I discoverered a few
more issues:

1) both the jsonb_plperl and jsonb_plperlu extensions contain the SQL
   functions jsonb_to_plperl and plperl_to_jsonb, so can't be installed
   simultaneously

2) jsonb scalar values are passed to the plperl function wrapped in not
   one, but _two_ layers of references

3) jsonb numeric values are passed as perl's NV (floating point) type,
   losing precision if they're integers that would fit in an IV or UV.

4) SV_to_JsonbValue() throws an error for infinite NVs, but not NaNs

Attached is a patch for the first issue.  I'll look at fixing the rest
later this week.

- ilmari
-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

From cf5771f98aa33bc7f12054d65857a3cc347465dc Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Tue, 10 Apr 2018 10:57:50 +0100
Subject: [PATCH] Fix clashing function names bettween jsonb_plperl and
 jsonb_plperlu

---
 contrib/jsonb_plperl/jsonb_plperlu--1.0.sql | 12 ++++++------
 1 file changed, 6 insertions(+), 6 deletions(-)

diff --git a/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql b/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql
index 99b8644e3b..5a5e475ad3 100644
--- a/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql
+++ b/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql
@@ -3,17 +3,17 @@
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use "CREATE EXTENSION jsonb_plperlu" to load this file. \quit
 
-CREATE FUNCTION jsonb_to_plperl(val internal) RETURNS internal
+CREATE FUNCTION jsonb_to_plperlu(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
-AS 'MODULE_PATHNAME';
+AS 'MODULE_PATHNAME', 'jsonb_to_plperl';
 
-CREATE FUNCTION plperl_to_jsonb(val internal) RETURNS jsonb
+CREATE FUNCTION plperlu_to_jsonb(val internal) RETURNS jsonb
 LANGUAGE C STRICT IMMUTABLE
-AS 'MODULE_PATHNAME';
+AS 'MODULE_PATHNAME', 'plperl_to_jsonb';
 
 CREATE TRANSFORM FOR jsonb LANGUAGE plperlu (
-    FROM SQL WITH FUNCTION jsonb_to_plperl(internal),
-    TO SQL WITH FUNCTION plperl_to_jsonb(internal)
+    FROM SQL WITH FUNCTION jsonb_to_plperlu(internal),
+    TO SQL WITH FUNCTION plperlu_to_jsonb(internal)
 );
 
 COMMENT ON TRANSFORM FOR jsonb LANGUAGE plperlu IS 'transform between jsonb and Perl';
-- 
2.17.0