Bytea PL/Perl transform
Hi,
PostgreSQL passes bytea arguments to PL/Perl functions as hexadecimal strings, which is not only inconvenient, but also memory and time consuming.
So I decided to propose a simple transform extension to pass bytea as native Perl octet strings.
Please find the patch attached.
Regards,
Ivan Panchenko
Attachments:
bytea_plperl.patchtext/x-diff; name="=?UTF-8?B?Ynl0ZWFfcGxwZXJsLnBhdGNo?="Download
diff --git a/contrib/Makefile b/contrib/Makefile
index bbf220407b..bb997dda69 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -78,9 +78,9 @@ ALWAYS_SUBDIRS += sepgsql
endif
ifeq ($(with_perl),yes)
-SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
else
-ALWAYS_SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+ALWAYS_SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
endif
ifeq ($(with_python),yes)
diff --git a/contrib/bytea_plperl/Makefile b/contrib/bytea_plperl/Makefile
new file mode 100644
index 0000000000..1814d2f418
--- /dev/null
+++ b/contrib/bytea_plperl/Makefile
@@ -0,0 +1,39 @@
+# contrib/bytea_plperl/Makefile
+
+MODULE_big = bytea_plperl
+OBJS = \
+ $(WIN32RES) \
+ bytea_plperl.o
+PGFILEDESC = "bytea_plperl - bytea transform for plperl"
+
+PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl
+
+EXTENSION = bytea_plperlu bytea_plperl
+DATA = bytea_plperlu--1.0.sql bytea_plperl--1.0.sql
+
+REGRESS = bytea_plperl bytea_plperlu
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/bytea_plperl
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# We must link libperl explicitly
+ifeq ($(PORTNAME), win32)
+# these settings are the same as for plperl
+override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment
+# ... see silliness in plperl Makefile ...
+SHLIB_LINK_INTERNAL += $(sort $(wildcard ../../src/pl/plperl/libperl*.a))
+else
+rpathdir = $(perl_archlibexp)/CORE
+SHLIB_LINK += $(perl_embed_ldflags)
+endif
+
+# As with plperl we need to include the perl_includespec directory last.
+override CPPFLAGS := $(CPPFLAGS) $(perl_embed_ccflags) $(perl_includespec)
diff --git a/contrib/bytea_plperl/bytea_plperl--1.0.sql b/contrib/bytea_plperl/bytea_plperl--1.0.sql
new file mode 100644
index 0000000000..6544b2ac85
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperl--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperl" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperl(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperl_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperl (
+ FROM SQL WITH FUNCTION bytea_to_plperl(internal),
+ TO SQL WITH FUNCTION plperl_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperl IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperl.c b/contrib/bytea_plperl/bytea_plperl.c
new file mode 100644
index 0000000000..0e20761b69
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.c
@@ -0,0 +1,36 @@
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "plperl.h"
+#include "varatt.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(bytea_to_plperl);
+
+Datum
+bytea_to_plperl(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *in = PG_GETARG_BYTEA_PP(0);
+ return PointerGetDatum(newSVpvn_flags( (char *) VARDATA_ANY(in), VARSIZE_ANY_EXHDR(in), 0 ));
+}
+
+
+PG_FUNCTION_INFO_V1(plperl_to_bytea);
+
+Datum
+plperl_to_bytea(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *result;
+ STRLEN len;
+ SV *in = (SV *) PG_GETARG_POINTER(0);
+ char *ptr = SvPVbyte(in, len);
+ result = palloc(VARHDRSZ + len );
+ SET_VARSIZE(result, VARHDRSZ + len );
+ memcpy(VARDATA_ANY(result), ptr,len );
+ PG_RETURN_BYTEA_P(result);
+}
+
+
diff --git a/contrib/bytea_plperl/bytea_plperl.control b/contrib/bytea_plperl/bytea_plperl.control
new file mode 100644
index 0000000000..9ff0f2a8dd
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.control
@@ -0,0 +1,7 @@
+# bytea_plperl extension
+comment = 'transform between bytea and plperl'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+trusted = true
+requires = 'plperl'
diff --git a/contrib/bytea_plperl/bytea_plperlu--1.0.sql b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
new file mode 100644
index 0000000000..d43e8fbaf4
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperlu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperlu" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperlu(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'bytea_to_plperl';
+
+CREATE FUNCTION plperlu_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'plperl_to_bytea';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperlu (
+ FROM SQL WITH FUNCTION bytea_to_plperlu(internal),
+ TO SQL WITH FUNCTION plperlu_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperlu IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperlu.control b/contrib/bytea_plperl/bytea_plperlu.control
new file mode 100644
index 0000000000..96cc8c35fb
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu.control
@@ -0,0 +1,6 @@
+# bytea_plperlu extension
+comment = 'transform between bytea and plperlu'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+requires = 'plperlu'
diff --git a/contrib/bytea_plperl/expected/bytea_plperl.out b/contrib/bytea_plperl/expected/bytea_plperl.out
new file mode 100644
index 0000000000..eb6fceb9e0
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperl.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+NOTICE: installing required extension "plperl"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+ bytea | perl_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperl CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperl
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perl_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/expected/bytea_plperlu.out b/contrib/bytea_plperl/expected/bytea_plperlu.out
new file mode 100644
index 0000000000..4b69eaffb1
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperlu.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+NOTICE: installing required extension "plperlu"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+ bytea | perlu_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperlu CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperlu
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perlu_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/sql/bytea_plperl.sql b/contrib/bytea_plperl/sql/bytea_plperl.sql
new file mode 100644
index 0000000000..67dfc973c6
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperl.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperl CASCADE;
diff --git a/contrib/bytea_plperl/sql/bytea_plperlu.sql b/contrib/bytea_plperl/sql/bytea_plperlu.sql
new file mode 100644
index 0000000000..5e47788a73
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperlu.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperlu CASCADE;
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 6c81ee8fbe..0d4f9b4e7a 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -224,6 +224,22 @@ $$ LANGUAGE plperl;
(<xref linkend="plperl-database"/>).
</para>
+ <para>
+ Normally the <type>bytea</type> arguments are seen by Perl as strings in hex format (see
+ <xref linkend="datatype-binary"/>).
+ If the transform defined by the <filename>bytea_plperl</filename> extension is used, they are
+ passed and returned as native Perl octet strings, see example below:
+<programlisting>
+CREATE EXTENSION bytea_plperl;
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+</programlisting>
+
+ </para>
+
<para>
Perl can return <productname>PostgreSQL</productname> arrays as
references to Perl arrays. Here is an example:
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index e3ffc653e5..963317e3ec 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -43,7 +43,7 @@ my $contrib_extralibs = { 'libpq_pipeline' => ['ws2_32.lib'] };
my $contrib_extraincludes = {};
my $contrib_extrasource = {};
my @contrib_excludes = (
- 'bool_plperl', 'commit_ts',
+ 'bool_plperl', 'bytea_plperl', 'commit_ts',
'hstore_plperl', 'hstore_plpython',
'intagg', 'jsonb_plperl',
'jsonb_plpython', 'ltree_plpython',
@@ -789,6 +789,9 @@ sub mkvcbuild
my $bool_plperl = AddTransformModule(
'bool_plperl', 'contrib/bool_plperl',
'plperl', 'src/pl/plperl');
+ my $bytea_plperl = AddTransformModule(
+ 'bytea_plperl', 'contrib/bytea_plperl',
+ 'plperl', 'src/pl/plperl');
my $hstore_plperl = AddTransformModule(
'hstore_plperl', 'contrib/hstore_plperl',
'plperl', 'src/pl/plperl',
@@ -800,6 +803,7 @@ sub mkvcbuild
foreach my $f (@perl_embed_ccflags)
{
$bool_plperl->AddDefine($f);
+ $bytea_plperl->AddDefine($f);
$hstore_plperl->AddDefine($f);
$jsonb_plperl->AddDefine($f);
}
On 18 Mar 2023, at 23:25, Иван Панченко <wao@mail.ru> wrote:
Hi,
PostgreSQL passes bytea arguments to PL/Perl functions as hexadecimal strings, which is not only inconvenient, but also memory and time consuming.
So I decided to propose a simple transform extension to pass bytea as native Perl octet strings.
Please find the patch attached.
Thanks for the patch, I recommend registering this in the currently open
Commitfest to make sure it's kept track of:
https://commitfest.postgresql.org/43/
--
Daniel Gustafsson
Среда, 22 марта 2023, 12:45 +03:00 от Daniel Gustafsson <daniel@yesql.se>:
On 18 Mar 2023, at 23:25, Иван Панченко < wao@mail.ru > wrote:
Hi,
PostgreSQL passes bytea arguments to PL/Perl functions as hexadecimal strings, which is not only inconvenient, but also memory and time consuming.
So I decided to propose a simple transform extension to pass bytea as native Perl octet strings.
Please find the patch attached.Thanks for the patch, I recommend registering this in the currently open
Commitfest to make sure it's kept track of:
Thanks, done:
https://commitfest.postgresql.org/43/4252/
--
Daniel Gustafsson
--
Ivan
So I decided to propose a simple transform extension to pass bytea as
native Perl octet strings.
Quick review, mostly housekeeping things:
* Needs a rebase, minor failure on Mkvcbuild.pm
* Code needs standardized formatting, esp. bytea_plperl.c
* Needs to be meson-i-fied (i.e. add a "meson.build" file)
* Do all of these transforms need to be their own contrib modules? So much
duplicated code across contrib/*_plperl already (and *plpython too for that
matter) ...
Cheers,
Greg
On 2023-06-22 Th 16:56, Greg Sabino Mullane wrote:
* Do all of these transforms need to be their own contrib modules? So
much duplicated code across contrib/*_plperl already (and *plpython
too for that matter) ...
Yeah, that's a bit of a mess. Not sure what we can do about it now.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
On 2023-06-22 Th 16:56, Greg Sabino Mullane wrote:
* Do all of these transforms need to be their own contrib modules? So
much duplicated code across contrib/*_plperl already (and *plpython
too for that matter) ...Yeah, that's a bit of a mess. Not sure what we can do about it now.
Would it be possible to move the functions and other objects to a new
combined extension, and make the existing ones depend on that?
I see ALTER EXTENSION has both ADD and DROP subcommands which don't
affect the object itself, only the extension membership. The challenge
would be getting the ordering right between the upgrade/install scripts
dropping the objects from the existing extension and adding them to the
new extension.
- ilmari
=?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
Andrew Dunstan <andrew@dunslane.net> writes:
On 2023-06-22 Th 16:56, Greg Sabino Mullane wrote:
* Do all of these transforms need to be their own contrib modules? So
much duplicated code across contrib/*_plperl already (and *plpython
too for that matter) ...
Yeah, that's a bit of a mess. Not sure what we can do about it now.
Would it be possible to move the functions and other objects to a new
combined extension, and make the existing ones depend on that?
Perhaps another way could be to accept that the packaging is what it
is, but look for ways to share the repetitive source code. The .so's
wouldn't get any smaller, but they're not that big anyway.
regards, tom lane
On 22.06.23 22:56, Greg Sabino Mullane wrote:
* Do all of these transforms need to be their own contrib modules? So
much duplicated code across contrib/*_plperl already (and *plpython too
for that matter) ...
The reason the first transform modules were separate extensions is that
they interfaced between one extension (plpython, plperl) and another
extension (ltree, hstore), so it wasn't clear where to put them without
creating an additional dependency for one of them.
If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.
Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut < peter@eisentraut.org >:
On 22.06.23 22:56, Greg Sabino Mullane wrote:* Do all of these transforms need to be their own contrib modules? So
much duplicated code across contrib/*_plperl already (and *plpython too
for that matter) ...The reason the first transform modules were separate extensions is that
they interfaced between one extension (plpython, plperl) and another
extension (ltree, hstore), so it wasn't clear where to put them without
creating an additional dependency for one of them.If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.
Looks reasonable.
The new extension bytea_plperl can be easily moved into plperl now, but what should be do with the existing ones, namely jsonb_plperl and bool_plperl ?
If we leave them where they are, it would be hard to explain why some transforms are inside plperl while other ones live separately. If we move them into plperl also, wouldn’t it break some compatibility?
=?UTF-8?B?SXZhbiBQYW5jaGVua28=?= <wao@mail.ru> writes:
Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut < peter@eisentraut.org >:
If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.
The new extension bytea_plperl can be easily moved into plperl now, but what should be do with the existing ones, namely jsonb_plperl and bool_plperl ?
If we leave them where they are, it would be hard to explain why some transforms are inside plperl while other ones live separately. If we move them into plperl also, wouldn’t it break some compatibility?
It's kind of a mess, indeed. But I think we could make plperl 1.1
contain the additional transforms and just tell people they have
to drop the obsolete extensions before they upgrade to 1.1.
Fortunately, it doesn't look like functions using a transform
have any hard dependency on the transform, so "drop extension
jsonb_plperl" followed by "alter extension plperl update" should
work without cascading to all your plperl functions.
Having said that, we'd still be in the position of having to
explain why some transforms are packaged with plperl and others
not. The distinction between built-in and contrib types might
be obvious to us hackers, but I bet a lot of users see it as
pretty artificial. So maybe the existing packaging design is
fine and we should just look for a way to reduce the code
duplication.
regards, tom lane
Friday, 14 July 2023, 23:27 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
=?UTF-8?B?SXZhbiBQYW5jaGVua28=?= < wao@mail.ru > writes:Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut < peter@eisentraut.org >:
If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.The new extension bytea_plperl can be easily moved into plperl now, but what should be do with the existing ones, namely jsonb_plperl and bool_plperl ?
If we leave them where they are, it would be hard to explain why some transforms are inside plperl while other ones live separately. If we move them into plperl also, wouldn’t it break some compatibility?It's kind of a mess, indeed. But I think we could make plperl 1.1
contain the additional transforms and just tell people they have
to drop the obsolete extensions before they upgrade to 1.1.
Fortunately, it doesn't look like functions using a transform
have any hard dependency on the transform, so "drop extension
jsonb_plperl" followed by "alter extension plperl update" should
work without cascading to all your plperl functions.Having said that, we'd still be in the position of having to
explain why some transforms are packaged with plperl and others
not. The distinction between built-in and contrib types might
be obvious to us hackers, but I bet a lot of users see it as
pretty artificial. So maybe the existing packaging design is
fine and we should just look for a way to reduce the code
duplication.
The code duplication between different transforms is not in C code, but mostly in copy-pasted Makefile, *.control, *.sql and meson-build. These files could be generated from some universal templates. But, keeping in mind Windows compatibility and presence of three build system, this hardly looks like a simplification.
Probably at present time it would be better to keep the existing code duplication until plperl 1.1.
Nevertheless, dealing with code duplication is a wider task than the bytea transform, so let me suggest to keep this extension in the present form. If we decide what to do with the duplication, it would be another patch.
The mesonified and rebased version of the transform patch is attached.
regards, tom lane
Regards, Ivan
Attachments:
bytea-plperl-mesonified.patchtext/x-diff; name="=?UTF-8?B?Ynl0ZWEtcGxwZXJsLW1lc29uaWZpZWQucGF0Y2g=?="Download
diff --git a/contrib/Makefile b/contrib/Makefile
index bbf220407b..bb997dda69 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -78,9 +78,9 @@ ALWAYS_SUBDIRS += sepgsql
endif
ifeq ($(with_perl),yes)
-SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
else
-ALWAYS_SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+ALWAYS_SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
endif
ifeq ($(with_python),yes)
diff --git a/contrib/bytea_plperl/bytea_plperl--1.0.sql b/contrib/bytea_plperl/bytea_plperl--1.0.sql
new file mode 100644
index 0000000000..6544b2ac85
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperl--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperl" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperl(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperl_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperl (
+ FROM SQL WITH FUNCTION bytea_to_plperl(internal),
+ TO SQL WITH FUNCTION plperl_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperl IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperl.c b/contrib/bytea_plperl/bytea_plperl.c
new file mode 100644
index 0000000000..7ff16040c9
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.c
@@ -0,0 +1,36 @@
+/*
+ * contrib/bytea_plperl/bytea_plperl.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "plperl.h"
+#include "varatt.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(bytea_to_plperl);
+PG_FUNCTION_INFO_V1(plperl_to_bytea);
+
+Datum
+bytea_to_plperl(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *in = PG_GETARG_BYTEA_PP(0);
+ return PointerGetDatum(newSVpvn_flags( (char *) VARDATA_ANY(in), VARSIZE_ANY_EXHDR(in), 0 ));
+}
+
+Datum
+plperl_to_bytea(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *result;
+ STRLEN len;
+ SV *in = (SV *) PG_GETARG_POINTER(0);
+ char *ptr = SvPVbyte(in, len);
+ result = palloc(VARHDRSZ + len );
+ SET_VARSIZE(result, VARHDRSZ + len );
+ memcpy(VARDATA_ANY(result), ptr,len );
+ PG_RETURN_BYTEA_P(result);
+}
diff --git a/contrib/bytea_plperl/bytea_plperl.control b/contrib/bytea_plperl/bytea_plperl.control
new file mode 100644
index 0000000000..9ff0f2a8dd
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.control
@@ -0,0 +1,7 @@
+# bytea_plperl extension
+comment = 'transform between bytea and plperl'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+trusted = true
+requires = 'plperl'
diff --git a/contrib/bytea_plperl/bytea_plperlu--1.0.sql b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
new file mode 100644
index 0000000000..d43e8fbaf4
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperlu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperlu" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperlu(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'bytea_to_plperl';
+
+CREATE FUNCTION plperlu_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'plperl_to_bytea';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperlu (
+ FROM SQL WITH FUNCTION bytea_to_plperlu(internal),
+ TO SQL WITH FUNCTION plperlu_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperlu IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperlu.control b/contrib/bytea_plperl/bytea_plperlu.control
new file mode 100644
index 0000000000..96cc8c35fb
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu.control
@@ -0,0 +1,6 @@
+# bytea_plperlu extension
+comment = 'transform between bytea and plperlu'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+requires = 'plperlu'
diff --git a/contrib/bytea_plperl/expected/bytea_plperl.out b/contrib/bytea_plperl/expected/bytea_plperl.out
new file mode 100644
index 0000000000..eb6fceb9e0
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperl.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+NOTICE: installing required extension "plperl"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+ bytea | perl_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperl CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperl
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perl_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/expected/bytea_plperlu.out b/contrib/bytea_plperl/expected/bytea_plperlu.out
new file mode 100644
index 0000000000..4b69eaffb1
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperlu.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+NOTICE: installing required extension "plperlu"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+ bytea | perlu_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperlu CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperlu
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perlu_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/meson.build b/contrib/bytea_plperl/meson.build
new file mode 100644
index 0000000000..3c438c2175
--- /dev/null
+++ b/contrib/bytea_plperl/meson.build
@@ -0,0 +1,51 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+if not perl_dep.found()
+ subdir_done()
+endif
+
+bytea_plperl_sources = files(
+ 'bytea_plperl.c',
+)
+
+if host_system == 'windows'
+ bytea_plperl_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'bytea_plperl',
+ '--FILEDESC', 'bytea_plperl - bytea transform for plperl',])
+endif
+
+bytea_plperl = shared_module('bytea_plperl',
+ bytea_plperl_sources,
+ include_directories: [plperl_inc],
+ kwargs: contrib_mod_args + {
+ 'dependencies': [perl_dep, contrib_mod_args['dependencies']],
+ 'install_rpath': ':'.join(mod_install_rpaths + ['@0@/CORE'.format(archlibexp)]),
+ 'build_rpath': '@0@/CORE'.format(archlibexp),
+ },
+)
+contrib_targets += bytea_plperl
+
+install_data(
+ 'bytea_plperl.control',
+ 'bytea_plperl--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+install_data(
+ 'bytea_plperlu.control',
+ 'bytea_plperlu--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+
+tests += {
+ 'name': 'bytea_plperl',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'bytea_plperl',
+ 'bytea_plperlu',
+ ],
+ },
+}
diff --git a/contrib/bytea_plperl/sql/bytea_plperl.sql b/contrib/bytea_plperl/sql/bytea_plperl.sql
new file mode 100644
index 0000000000..67dfc973c6
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperl.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperl CASCADE;
diff --git a/contrib/bytea_plperl/sql/bytea_plperlu.sql b/contrib/bytea_plperl/sql/bytea_plperlu.sql
new file mode 100644
index 0000000000..5e47788a73
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperlu.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperlu CASCADE;
diff --git a/contrib/meson.build b/contrib/meson.build
index bd4a57c43c..10524d9323 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -17,6 +17,7 @@ subdir('basebackup_to_shell')
subdir('bool_plperl')
subdir('btree_gin')
subdir('btree_gist')
+subdir('bytea_plperl')
subdir('citext')
subdir('cube')
subdir('dblink')
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 25b1077ad7..eea293eaec 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -224,6 +224,22 @@ $$ LANGUAGE plperl;
(<xref linkend="plperl-database"/>).
</para>
+ <para>
+ Normally the <type>bytea</type> arguments are seen by Perl as strings in hex format (see
+ <xref linkend="datatype-binary"/>).
+ If the transform defined by the <filename>bytea_plperl</filename> extension is used, they are
+ passed and returned as native Perl octet strings, see example below:
+<programlisting>
+CREATE EXTENSION bytea_plperl; -- or bool_plperlu for PL/PerlU
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+</programlisting>
+
+ </para>
+
<para>
Perl can return <productname>PostgreSQL</productname> arrays as
references to Perl arrays. Here is an example:
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 9e05eb91b1..ec0a3f8097 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -43,7 +43,7 @@ my $contrib_extralibs = { 'libpq_pipeline' => ['ws2_32.lib'] };
my $contrib_extraincludes = {};
my $contrib_extrasource = {};
my @contrib_excludes = (
- 'bool_plperl', 'commit_ts',
+ 'bool_plperl', 'bytea_plperl', 'commit_ts',
'hstore_plperl', 'hstore_plpython',
'intagg', 'jsonb_plperl',
'jsonb_plpython', 'ltree_plpython',
@@ -791,6 +791,9 @@ sub mkvcbuild
my $bool_plperl = AddTransformModule(
'bool_plperl', 'contrib/bool_plperl',
'plperl', 'src/pl/plperl');
+ my $bytea_plperl = AddTransformModule(
+ 'bytea_plperl', 'contrib/bytea_plperl',
+ 'plperl', 'src/pl/plperl');
my $hstore_plperl = AddTransformModule(
'hstore_plperl', 'contrib/hstore_plperl',
'plperl', 'src/pl/plperl',
@@ -802,6 +805,7 @@ sub mkvcbuild
foreach my $f (@perl_embed_ccflags)
{
$bool_plperl->AddDefine($f);
+ $bytea_plperl->AddDefine($f);
$hstore_plperl->AddDefine($f);
$jsonb_plperl->AddDefine($f);
}
On Fri, 21 Jul 2023 at 02:59, Ivan Panchenko <wao@mail.ru> wrote:
Friday, 14 July 2023, 23:27 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
=?UTF-8?B?SXZhbiBQYW5jaGVua28=?= <wao@mail.ru> writes:
Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut < peter@eisentraut.org >:
If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.The new extension bytea_plperl can be easily moved into plperl now, but what should be do with the existing ones, namely jsonb_plperl and bool_plperl ?
If we leave them where they are, it would be hard to explain why some transforms are inside plperl while other ones live separately. If we move them into plperl also, wouldn’t it break some compatibility?It's kind of a mess, indeed. But I think we could make plperl 1.1
contain the additional transforms and just tell people they have
to drop the obsolete extensions before they upgrade to 1.1.
Fortunately, it doesn't look like functions using a transform
have any hard dependency on the transform, so "drop extension
jsonb_plperl" followed by "alter extension plperl update" should
work without cascading to all your plperl functions.Having said that, we'd still be in the position of having to
explain why some transforms are packaged with plperl and others
not. The distinction between built-in and contrib types might
be obvious to us hackers, but I bet a lot of users see it as
pretty artificial. So maybe the existing packaging design is
fine and we should just look for a way to reduce the code
duplication.The code duplication between different transforms is not in C code, but mostly in copy-pasted Makefile, *.control, *.sql and meson-build. These files could be generated from some universal templates. But, keeping in mind Windows compatibility and presence of three build system, this hardly looks like a simplification.
Probably at present time it would be better to keep the existing code duplication until plperl 1.1.
Nevertheless, dealing with code duplication is a wider task than the bytea transform, so let me suggest to keep this extension in the present form. If we decide what to do with the duplication, it would be another patch.The mesonified and rebased version of the transform patch is attached.
The patch needs to be rebased as these changes are not required anymore:
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 9e05eb91b1..ec0a3f8097 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -43,7 +43,7 @@ my $contrib_extralibs = { 'libpq_pipeline' =>
['ws2_32.lib'] };
my $contrib_extraincludes = {};
my $contrib_extrasource = {};
my @contrib_excludes = (
- 'bool_plperl', 'commit_ts',
+ 'bool_plperl', 'bytea_plperl', 'commit_ts',
'hstore_plperl', 'hstore_plpython',
'intagg', 'jsonb_plperl',
'jsonb_plpython', 'ltree_plpython',
@@ -791,6 +791,9 @@ sub mkvcbuild
my $bool_plperl = AddTransformModule(
'bool_plperl', 'contrib/bool_plperl',
'plperl', 'src/pl/plperl');
+ my $bytea_plperl = AddTransformModule(
+ 'bytea_plperl', 'contrib/bytea_plperl',
+ 'plperl', 'src/pl/plperl');Regards,
Vignesh
Hi
so 6. 1. 2024 v 16:51 odesílatel vignesh C <vignesh21@gmail.com> napsal:
On Fri, 21 Jul 2023 at 02:59, Ivan Panchenko <wao@mail.ru> wrote:
Friday, 14 July 2023, 23:27 +03:00 от Tom Lane <tgl@sss.pgh.pa.us>:
=?UTF-8?B?SXZhbiBQYW5jaGVua28=?= <wao@mail.ru> writes:
Четверг, 6 июля 2023, 14:48 +03:00 от Peter Eisentraut <
peter@eisentraut.org >:
If the transform deals with a built-in type, then they should just be
added to the respective pl extension directly.The new extension bytea_plperl can be easily moved into plperl now,
but what should be do with the existing ones, namely jsonb_plperl and
bool_plperl ?If we leave them where they are, it would be hard to explain why some
transforms are inside plperl while other ones live separately. If we move
them into plperl also, wouldn’t it break some compatibility?It's kind of a mess, indeed. But I think we could make plperl 1.1
contain the additional transforms and just tell people they have
to drop the obsolete extensions before they upgrade to 1.1.
Fortunately, it doesn't look like functions using a transform
have any hard dependency on the transform, so "drop extension
jsonb_plperl" followed by "alter extension plperl update" should
work without cascading to all your plperl functions.Having said that, we'd still be in the position of having to
explain why some transforms are packaged with plperl and others
not. The distinction between built-in and contrib types might
be obvious to us hackers, but I bet a lot of users see it as
pretty artificial. So maybe the existing packaging design is
fine and we should just look for a way to reduce the code
duplication.The code duplication between different transforms is not in C code, but
mostly in copy-pasted Makefile, *.control, *.sql and meson-build. These
files could be generated from some universal templates. But, keeping in
mind Windows compatibility and presence of three build system, this hardly
looks like a simplification.Probably at present time it would be better to keep the existing code
duplication until plperl 1.1.
Nevertheless, dealing with code duplication is a wider task than the
bytea transform, so let me suggest to keep this extension in the present
form. If we decide what to do with the duplication, it would be another
patch.The mesonified and rebased version of the transform patch is attached.
The patch needs to be rebased as these changes are not required anymore: diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm index 9e05eb91b1..ec0a3f8097 100644 --- a/src/tools/msvc/Mkvcbuild.pm +++ b/src/tools/msvc/Mkvcbuild.pm @@ -43,7 +43,7 @@ my $contrib_extralibs = { 'libpq_pipeline' => ['ws2_32.lib'] }; my $contrib_extraincludes = {}; my $contrib_extrasource = {}; my @contrib_excludes = ( - 'bool_plperl', 'commit_ts', + 'bool_plperl', 'bytea_plperl', 'commit_ts', 'hstore_plperl', 'hstore_plpython', 'intagg', 'jsonb_plperl', 'jsonb_plpython', 'ltree_plpython', @@ -791,6 +791,9 @@ sub mkvcbuild my $bool_plperl = AddTransformModule( 'bool_plperl', 'contrib/bool_plperl', 'plperl', 'src/pl/plperl'); + my $bytea_plperl = AddTransformModule( + 'bytea_plperl', 'contrib/bytea_plperl', + 'plperl', 'src/pl/plperl');Regards,
Vignesh
I am checking this patch, it looks well. All tests passed. I am sending a
cleaned patch.
I did minor formatting cleaning.
I inserted perl reference support - hstore_plperl and json_plperl does it.
+<->/* Dereference references recursively. */
+<->while (SvROK(in))
+<-><-->in = SvRV(in);Regards
Pavel
Attachments:
0001-supports-bytea-transformation-for-plperl.patchtext/x-patch; charset=UTF-8; name=0001-supports-bytea-transformation-for-plperl.patchDownload
From 340b945f06335088d22280755923e2c719e4b598 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Tue, 30 Jan 2024 10:31:00 +0100
Subject: [PATCH] supports bytea transformation for plperl
---
contrib/Makefile | 4 +-
contrib/bytea_plperl/.gitignore | 4 ++
contrib/bytea_plperl/Makefile | 39 ++++++++++++++
contrib/bytea_plperl/bytea_plperl--1.0.sql | 19 +++++++
contrib/bytea_plperl/bytea_plperl.c | 48 +++++++++++++++++
contrib/bytea_plperl/bytea_plperl.control | 7 +++
contrib/bytea_plperl/bytea_plperlu--1.0.sql | 19 +++++++
contrib/bytea_plperl/bytea_plperlu.control | 6 +++
.../bytea_plperl/expected/bytea_plperl.out | 36 +++++++++++++
.../bytea_plperl/expected/bytea_plperlu.out | 36 +++++++++++++
contrib/bytea_plperl/meson.build | 51 +++++++++++++++++++
contrib/bytea_plperl/sql/bytea_plperl.sql | 22 ++++++++
contrib/bytea_plperl/sql/bytea_plperlu.sql | 22 ++++++++
contrib/meson.build | 1 +
doc/src/sgml/plperl.sgml | 16 ++++++
15 files changed, 328 insertions(+), 2 deletions(-)
create mode 100644 contrib/bytea_plperl/.gitignore
create mode 100644 contrib/bytea_plperl/Makefile
create mode 100644 contrib/bytea_plperl/bytea_plperl--1.0.sql
create mode 100644 contrib/bytea_plperl/bytea_plperl.c
create mode 100644 contrib/bytea_plperl/bytea_plperl.control
create mode 100644 contrib/bytea_plperl/bytea_plperlu--1.0.sql
create mode 100644 contrib/bytea_plperl/bytea_plperlu.control
create mode 100644 contrib/bytea_plperl/expected/bytea_plperl.out
create mode 100644 contrib/bytea_plperl/expected/bytea_plperlu.out
create mode 100644 contrib/bytea_plperl/meson.build
create mode 100644 contrib/bytea_plperl/sql/bytea_plperl.sql
create mode 100644 contrib/bytea_plperl/sql/bytea_plperlu.sql
diff --git a/contrib/Makefile b/contrib/Makefile
index da4e2316a3..56c628df00 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -77,9 +77,9 @@ ALWAYS_SUBDIRS += sepgsql
endif
ifeq ($(with_perl),yes)
-SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
else
-ALWAYS_SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+ALWAYS_SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
endif
ifeq ($(with_python),yes)
diff --git a/contrib/bytea_plperl/.gitignore b/contrib/bytea_plperl/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/contrib/bytea_plperl/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/bytea_plperl/Makefile b/contrib/bytea_plperl/Makefile
new file mode 100644
index 0000000000..1814d2f418
--- /dev/null
+++ b/contrib/bytea_plperl/Makefile
@@ -0,0 +1,39 @@
+# contrib/bytea_plperl/Makefile
+
+MODULE_big = bytea_plperl
+OBJS = \
+ $(WIN32RES) \
+ bytea_plperl.o
+PGFILEDESC = "bytea_plperl - bytea transform for plperl"
+
+PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl
+
+EXTENSION = bytea_plperlu bytea_plperl
+DATA = bytea_plperlu--1.0.sql bytea_plperl--1.0.sql
+
+REGRESS = bytea_plperl bytea_plperlu
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/bytea_plperl
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# We must link libperl explicitly
+ifeq ($(PORTNAME), win32)
+# these settings are the same as for plperl
+override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment
+# ... see silliness in plperl Makefile ...
+SHLIB_LINK_INTERNAL += $(sort $(wildcard ../../src/pl/plperl/libperl*.a))
+else
+rpathdir = $(perl_archlibexp)/CORE
+SHLIB_LINK += $(perl_embed_ldflags)
+endif
+
+# As with plperl we need to include the perl_includespec directory last.
+override CPPFLAGS := $(CPPFLAGS) $(perl_embed_ccflags) $(perl_includespec)
diff --git a/contrib/bytea_plperl/bytea_plperl--1.0.sql b/contrib/bytea_plperl/bytea_plperl--1.0.sql
new file mode 100644
index 0000000000..6544b2ac85
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperl--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperl" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperl(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperl_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperl (
+ FROM SQL WITH FUNCTION bytea_to_plperl(internal),
+ TO SQL WITH FUNCTION plperl_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperl IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperl.c b/contrib/bytea_plperl/bytea_plperl.c
new file mode 100644
index 0000000000..3a421b02f7
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.c
@@ -0,0 +1,48 @@
+/*
+ * contrib/bytea_plperl/bytea_plperl.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "plperl.h"
+#include "varatt.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(bytea_to_plperl);
+PG_FUNCTION_INFO_V1(plperl_to_bytea);
+
+Datum
+bytea_to_plperl(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *in = PG_GETARG_BYTEA_PP(0);
+
+ return PointerGetDatum(newSVpvn_flags((char *) VARDATA_ANY(in),
+ VARSIZE_ANY_EXHDR(in), 0 ));
+}
+
+Datum
+plperl_to_bytea(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *result;
+ STRLEN len;
+ char *ptr;
+ SV *in;
+
+ in = (SV *) PG_GETARG_POINTER(0);
+
+ /* Dereference references recursively. */
+ while (SvROK(in))
+ in = SvRV(in);
+
+ ptr = SvPVbyte(in, len);
+
+ result = palloc(VARHDRSZ + len );
+ SET_VARSIZE(result, VARHDRSZ + len );
+ memcpy(VARDATA_ANY(result), ptr,len );
+
+ PG_RETURN_BYTEA_P(result);
+}
diff --git a/contrib/bytea_plperl/bytea_plperl.control b/contrib/bytea_plperl/bytea_plperl.control
new file mode 100644
index 0000000000..9ff0f2a8dd
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.control
@@ -0,0 +1,7 @@
+# bytea_plperl extension
+comment = 'transform between bytea and plperl'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+trusted = true
+requires = 'plperl'
diff --git a/contrib/bytea_plperl/bytea_plperlu--1.0.sql b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
new file mode 100644
index 0000000000..d43e8fbaf4
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperlu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperlu" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperlu(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'bytea_to_plperl';
+
+CREATE FUNCTION plperlu_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'plperl_to_bytea';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperlu (
+ FROM SQL WITH FUNCTION bytea_to_plperlu(internal),
+ TO SQL WITH FUNCTION plperlu_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperlu IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperlu.control b/contrib/bytea_plperl/bytea_plperlu.control
new file mode 100644
index 0000000000..96cc8c35fb
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu.control
@@ -0,0 +1,6 @@
+# bytea_plperlu extension
+comment = 'transform between bytea and plperlu'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+requires = 'plperlu'
diff --git a/contrib/bytea_plperl/expected/bytea_plperl.out b/contrib/bytea_plperl/expected/bytea_plperl.out
new file mode 100644
index 0000000000..eb6fceb9e0
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperl.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+NOTICE: installing required extension "plperl"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+ bytea | perl_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperl CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperl
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perl_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/expected/bytea_plperlu.out b/contrib/bytea_plperl/expected/bytea_plperlu.out
new file mode 100644
index 0000000000..4b69eaffb1
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperlu.out
@@ -0,0 +1,36 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+NOTICE: installing required extension "plperlu"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+ bytea | perlu_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+DROP EXTENSION plperlu CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to extension bytea_plperlu
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perlu_inverse_bytes(bytea)
diff --git a/contrib/bytea_plperl/meson.build b/contrib/bytea_plperl/meson.build
new file mode 100644
index 0000000000..3c438c2175
--- /dev/null
+++ b/contrib/bytea_plperl/meson.build
@@ -0,0 +1,51 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+if not perl_dep.found()
+ subdir_done()
+endif
+
+bytea_plperl_sources = files(
+ 'bytea_plperl.c',
+)
+
+if host_system == 'windows'
+ bytea_plperl_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'bytea_plperl',
+ '--FILEDESC', 'bytea_plperl - bytea transform for plperl',])
+endif
+
+bytea_plperl = shared_module('bytea_plperl',
+ bytea_plperl_sources,
+ include_directories: [plperl_inc],
+ kwargs: contrib_mod_args + {
+ 'dependencies': [perl_dep, contrib_mod_args['dependencies']],
+ 'install_rpath': ':'.join(mod_install_rpaths + ['@0@/CORE'.format(archlibexp)]),
+ 'build_rpath': '@0@/CORE'.format(archlibexp),
+ },
+)
+contrib_targets += bytea_plperl
+
+install_data(
+ 'bytea_plperl.control',
+ 'bytea_plperl--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+install_data(
+ 'bytea_plperlu.control',
+ 'bytea_plperlu--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+
+tests += {
+ 'name': 'bytea_plperl',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'bytea_plperl',
+ 'bytea_plperlu',
+ ],
+ },
+}
diff --git a/contrib/bytea_plperl/sql/bytea_plperl.sql b/contrib/bytea_plperl/sql/bytea_plperl.sql
new file mode 100644
index 0000000000..67dfc973c6
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperl.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperl CASCADE;
diff --git a/contrib/bytea_plperl/sql/bytea_plperlu.sql b/contrib/bytea_plperl/sql/bytea_plperlu.sql
new file mode 100644
index 0000000000..5e47788a73
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperlu.sql
@@ -0,0 +1,22 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , '爱' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+
+DROP EXTENSION plperlu CASCADE;
diff --git a/contrib/meson.build b/contrib/meson.build
index c12dc906ca..7fe53fafeb 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -22,6 +22,7 @@ subdir('basebackup_to_shell')
subdir('bool_plperl')
subdir('btree_gin')
subdir('btree_gist')
+subdir('bytea_plperl')
subdir('citext')
subdir('cube')
subdir('dblink')
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 25b1077ad7..eea293eaec 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -224,6 +224,22 @@ $$ LANGUAGE plperl;
(<xref linkend="plperl-database"/>).
</para>
+ <para>
+ Normally the <type>bytea</type> arguments are seen by Perl as strings in hex format (see
+ <xref linkend="datatype-binary"/>).
+ If the transform defined by the <filename>bytea_plperl</filename> extension is used, they are
+ passed and returned as native Perl octet strings, see example below:
+<programlisting>
+CREATE EXTENSION bytea_plperl; -- or bool_plperlu for PL/PerlU
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+</programlisting>
+
+ </para>
+
<para>
Perl can return <productname>PostgreSQL</productname> arrays as
references to Perl arrays. Here is an example:
--
2.43.0
Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl does it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);
That code in hstore_plperl and json_plperl is only relevant because they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature of the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.
bytea_plperl only deals with scalars (specifically strings), so should
not concern itself with references. In fact, this code breaks returning
objects with overloaded stringification, for example:
CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;
This makes the server crash with an assertion failure from Perl because
SvPVbyte() was passed a non-scalar value:
postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865: Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV && SvTYPE(sv) != SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)
Attached is a v2 patch which removes the dereferencing and includes the
above example as a test.
- ilmari
Attachments:
v2-0001-Add-bytea-transformation-for-plperl.patchtext/x-diff; charset=utf-8Download
From aabaf4f5932f59de2fed48bbba7339807a1f04bd Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <okbob@github.com>
Date: Tue, 30 Jan 2024 10:31:00 +0100
Subject: [PATCH v2] Add bytea transformation for plperl
---
contrib/Makefile | 4 +-
contrib/bytea_plperl/.gitignore | 4 ++
contrib/bytea_plperl/Makefile | 39 ++++++++++++++
contrib/bytea_plperl/bytea_plperl--1.0.sql | 19 +++++++
contrib/bytea_plperl/bytea_plperl.c | 44 ++++++++++++++++
contrib/bytea_plperl/bytea_plperl.control | 7 +++
contrib/bytea_plperl/bytea_plperlu--1.0.sql | 19 +++++++
contrib/bytea_plperl/bytea_plperlu.control | 6 +++
.../bytea_plperl/expected/bytea_plperl.out | 49 ++++++++++++++++++
.../bytea_plperl/expected/bytea_plperlu.out | 49 ++++++++++++++++++
contrib/bytea_plperl/meson.build | 51 +++++++++++++++++++
contrib/bytea_plperl/sql/bytea_plperl.sql | 31 +++++++++++
contrib/bytea_plperl/sql/bytea_plperlu.sql | 31 +++++++++++
contrib/meson.build | 1 +
doc/src/sgml/plperl.sgml | 16 ++++++
15 files changed, 368 insertions(+), 2 deletions(-)
create mode 100644 contrib/bytea_plperl/.gitignore
create mode 100644 contrib/bytea_plperl/Makefile
create mode 100644 contrib/bytea_plperl/bytea_plperl--1.0.sql
create mode 100644 contrib/bytea_plperl/bytea_plperl.c
create mode 100644 contrib/bytea_plperl/bytea_plperl.control
create mode 100644 contrib/bytea_plperl/bytea_plperlu--1.0.sql
create mode 100644 contrib/bytea_plperl/bytea_plperlu.control
create mode 100644 contrib/bytea_plperl/expected/bytea_plperl.out
create mode 100644 contrib/bytea_plperl/expected/bytea_plperlu.out
create mode 100644 contrib/bytea_plperl/meson.build
create mode 100644 contrib/bytea_plperl/sql/bytea_plperl.sql
create mode 100644 contrib/bytea_plperl/sql/bytea_plperlu.sql
diff --git a/contrib/Makefile b/contrib/Makefile
index da4e2316a3..56c628df00 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -77,9 +77,9 @@ ALWAYS_SUBDIRS += sepgsql
endif
ifeq ($(with_perl),yes)
-SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
else
-ALWAYS_SUBDIRS += bool_plperl hstore_plperl jsonb_plperl
+ALWAYS_SUBDIRS += bool_plperl bytea_plperl hstore_plperl jsonb_plperl
endif
ifeq ($(with_python),yes)
diff --git a/contrib/bytea_plperl/.gitignore b/contrib/bytea_plperl/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/contrib/bytea_plperl/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/bytea_plperl/Makefile b/contrib/bytea_plperl/Makefile
new file mode 100644
index 0000000000..1814d2f418
--- /dev/null
+++ b/contrib/bytea_plperl/Makefile
@@ -0,0 +1,39 @@
+# contrib/bytea_plperl/Makefile
+
+MODULE_big = bytea_plperl
+OBJS = \
+ $(WIN32RES) \
+ bytea_plperl.o
+PGFILEDESC = "bytea_plperl - bytea transform for plperl"
+
+PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl
+
+EXTENSION = bytea_plperlu bytea_plperl
+DATA = bytea_plperlu--1.0.sql bytea_plperl--1.0.sql
+
+REGRESS = bytea_plperl bytea_plperlu
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/bytea_plperl
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# We must link libperl explicitly
+ifeq ($(PORTNAME), win32)
+# these settings are the same as for plperl
+override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment
+# ... see silliness in plperl Makefile ...
+SHLIB_LINK_INTERNAL += $(sort $(wildcard ../../src/pl/plperl/libperl*.a))
+else
+rpathdir = $(perl_archlibexp)/CORE
+SHLIB_LINK += $(perl_embed_ldflags)
+endif
+
+# As with plperl we need to include the perl_includespec directory last.
+override CPPFLAGS := $(CPPFLAGS) $(perl_embed_ccflags) $(perl_includespec)
diff --git a/contrib/bytea_plperl/bytea_plperl--1.0.sql b/contrib/bytea_plperl/bytea_plperl--1.0.sql
new file mode 100644
index 0000000000..6544b2ac85
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperl--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperl" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperl(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperl_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperl (
+ FROM SQL WITH FUNCTION bytea_to_plperl(internal),
+ TO SQL WITH FUNCTION plperl_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperl IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperl.c b/contrib/bytea_plperl/bytea_plperl.c
new file mode 100644
index 0000000000..5a0c58d8ab
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.c
@@ -0,0 +1,44 @@
+/*
+ * contrib/bytea_plperl/bytea_plperl.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "plperl.h"
+#include "varatt.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(bytea_to_plperl);
+PG_FUNCTION_INFO_V1(plperl_to_bytea);
+
+Datum
+bytea_to_plperl(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *in = PG_GETARG_BYTEA_PP(0);
+
+ return PointerGetDatum(newSVpvn_flags((char *) VARDATA_ANY(in),
+ VARSIZE_ANY_EXHDR(in), 0 ));
+}
+
+Datum
+plperl_to_bytea(PG_FUNCTION_ARGS)
+{
+ dTHX;
+ bytea *result;
+ STRLEN len;
+ char *ptr;
+ SV *in;
+
+ in = (SV *) PG_GETARG_POINTER(0);
+
+ ptr = SvPVbyte(in, len);
+
+ result = palloc(VARHDRSZ + len );
+ SET_VARSIZE(result, VARHDRSZ + len );
+ memcpy(VARDATA_ANY(result), ptr,len );
+
+ PG_RETURN_BYTEA_P(result);
+}
diff --git a/contrib/bytea_plperl/bytea_plperl.control b/contrib/bytea_plperl/bytea_plperl.control
new file mode 100644
index 0000000000..9ff0f2a8dd
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperl.control
@@ -0,0 +1,7 @@
+# bytea_plperl extension
+comment = 'transform between bytea and plperl'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+trusted = true
+requires = 'plperl'
diff --git a/contrib/bytea_plperl/bytea_plperlu--1.0.sql b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
new file mode 100644
index 0000000000..d43e8fbaf4
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/bytea_plperl/bytea_plperlu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION bytea_plperlu" to load this file. \quit
+
+CREATE FUNCTION bytea_to_plperlu(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'bytea_to_plperl';
+
+CREATE FUNCTION plperlu_to_bytea(val internal) RETURNS bytea
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'plperl_to_bytea';
+
+CREATE TRANSFORM FOR bytea LANGUAGE plperlu (
+ FROM SQL WITH FUNCTION bytea_to_plperlu(internal),
+ TO SQL WITH FUNCTION plperlu_to_bytea(internal)
+);
+
+COMMENT ON TRANSFORM FOR bytea LANGUAGE plperlu IS 'transform between bytea and Perl';
diff --git a/contrib/bytea_plperl/bytea_plperlu.control b/contrib/bytea_plperl/bytea_plperlu.control
new file mode 100644
index 0000000000..96cc8c35fb
--- /dev/null
+++ b/contrib/bytea_plperl/bytea_plperlu.control
@@ -0,0 +1,6 @@
+# bytea_plperlu extension
+comment = 'transform between bytea and plperlu'
+default_version = '1.0'
+module_pathname = '$libdir/bytea_plperl'
+relocatable = true
+requires = 'plperlu'
diff --git a/contrib/bytea_plperl/expected/bytea_plperl.out b/contrib/bytea_plperl/expected/bytea_plperl.out
new file mode 100644
index 0000000000..99fe3aadce
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperl.out
@@ -0,0 +1,49 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+NOTICE: installing required extension "plperl"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , 'ç±' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+ bytea | perl_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+CREATE FUNCTION plperl_bytea_overload() RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ package StringOverload { use overload '""' => sub { "stuff" }; }
+ return bless {}, "StringOverload";
+ $$;
+SELECT encode(plperl_bytea_overload(), 'escape') string;
+ string
+--------
+ stuff
+(1 row)
+
+DROP EXTENSION plperl CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to extension bytea_plperl
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perl_inverse_bytes(bytea)
+drop cascades to function plperl_bytea_overload()
diff --git a/contrib/bytea_plperl/expected/bytea_plperlu.out b/contrib/bytea_plperl/expected/bytea_plperlu.out
new file mode 100644
index 0000000000..6402685036
--- /dev/null
+++ b/contrib/bytea_plperl/expected/bytea_plperlu.out
@@ -0,0 +1,49 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+NOTICE: installing required extension "plperlu"
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , 'ç±' ]), 10000), 'escape') data
+ ) line;
+ ?column?
+----------
+ t
+ t
+ t
+ t
+ t
+(5 rows)
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+ bytea | perlu_inverse_bytes
+------------------------+------------------------
+ \xcebeceb5cebdceafceb1 | \xb1ceafcebdceb5cebece
+(1 row)
+
+CREATE FUNCTION plperlu_bytea_overload() RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ package StringOverload { use overload '""' => sub { "stuff" }; }
+ return bless {}, "StringOverload";
+ $$;
+SELECT encode(plperlu_bytea_overload(), 'escape') string;
+ string
+--------
+ stuff
+(1 row)
+
+DROP EXTENSION plperlu CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to extension bytea_plperlu
+drop cascades to function cat_bytea(bytea)
+drop cascades to function perlu_inverse_bytes(bytea)
+drop cascades to function plperlu_bytea_overload()
diff --git a/contrib/bytea_plperl/meson.build b/contrib/bytea_plperl/meson.build
new file mode 100644
index 0000000000..3c438c2175
--- /dev/null
+++ b/contrib/bytea_plperl/meson.build
@@ -0,0 +1,51 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+if not perl_dep.found()
+ subdir_done()
+endif
+
+bytea_plperl_sources = files(
+ 'bytea_plperl.c',
+)
+
+if host_system == 'windows'
+ bytea_plperl_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'bytea_plperl',
+ '--FILEDESC', 'bytea_plperl - bytea transform for plperl',])
+endif
+
+bytea_plperl = shared_module('bytea_plperl',
+ bytea_plperl_sources,
+ include_directories: [plperl_inc],
+ kwargs: contrib_mod_args + {
+ 'dependencies': [perl_dep, contrib_mod_args['dependencies']],
+ 'install_rpath': ':'.join(mod_install_rpaths + ['@0@/CORE'.format(archlibexp)]),
+ 'build_rpath': '@0@/CORE'.format(archlibexp),
+ },
+)
+contrib_targets += bytea_plperl
+
+install_data(
+ 'bytea_plperl.control',
+ 'bytea_plperl--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+install_data(
+ 'bytea_plperlu.control',
+ 'bytea_plperlu--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+
+tests += {
+ 'name': 'bytea_plperl',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'bytea_plperl',
+ 'bytea_plperlu',
+ ],
+ },
+}
diff --git a/contrib/bytea_plperl/sql/bytea_plperl.sql b/contrib/bytea_plperl/sql/bytea_plperl.sql
new file mode 100644
index 0000000000..0836290244
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperl.sql
@@ -0,0 +1,31 @@
+CREATE EXTENSION bytea_plperl CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , 'ç±' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+
+SELECT 'ξενία'::bytea, perl_inverse_bytes('ξενία'::bytea);
+
+CREATE FUNCTION plperl_bytea_overload() RETURNS bytea LANGUAGE plperl
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ package StringOverload { use overload '""' => sub { "stuff" }; }
+ return bless {}, "StringOverload";
+ $$;
+
+SELECT encode(plperl_bytea_overload(), 'escape') string;
+
+DROP EXTENSION plperl CASCADE;
diff --git a/contrib/bytea_plperl/sql/bytea_plperlu.sql b/contrib/bytea_plperl/sql/bytea_plperlu.sql
new file mode 100644
index 0000000000..4bbd697f32
--- /dev/null
+++ b/contrib/bytea_plperl/sql/bytea_plperlu.sql
@@ -0,0 +1,31 @@
+CREATE EXTENSION bytea_plperlu CASCADE;
+
+CREATE FUNCTION cat_bytea(bytea) RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ return $_[0];
+ $$;
+
+SELECT data = cat_bytea(data)
+ FROM (
+ SELECT decode(repeat(unnest(ARRAY[ 'a','abc', 'abcd', 'abcdefgh\000ijkl12' , 'ç±' ]), 10000), 'escape') data
+ ) line;
+
+CREATE FUNCTION perlu_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperlu;
+
+SELECT 'ξενία'::bytea, perlu_inverse_bytes('ξενία'::bytea);
+
+CREATE FUNCTION plperlu_bytea_overload() RETURNS bytea LANGUAGE plperlu
+ TRANSFORM FOR TYPE bytea
+ AS $$
+ package StringOverload { use overload '""' => sub { "stuff" }; }
+ return bless {}, "StringOverload";
+ $$;
+
+SELECT encode(plperlu_bytea_overload(), 'escape') string;
+
+DROP EXTENSION plperlu CASCADE;
diff --git a/contrib/meson.build b/contrib/meson.build
index c12dc906ca..7fe53fafeb 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -22,6 +22,7 @@ subdir('basebackup_to_shell')
subdir('bool_plperl')
subdir('btree_gin')
subdir('btree_gist')
+subdir('bytea_plperl')
subdir('citext')
subdir('cube')
subdir('dblink')
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 25b1077ad7..eea293eaec 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -224,6 +224,22 @@
(<xref linkend="plperl-database"/>).
</para>
+ <para>
+ Normally the <type>bytea</type> arguments are seen by Perl as strings in hex format (see
+ <xref linkend="datatype-binary"/>).
+ If the transform defined by the <filename>bytea_plperl</filename> extension is used, they are
+ passed and returned as native Perl octet strings, see example below:
+<programlisting>
+CREATE EXTENSION bytea_plperl; -- or bool_plperlu for PL/PerlU
+CREATE FUNCTION perl_inverse_bytes(bytea) RETURNS bytea
+TRANSFORM FOR TYPE bytea
+AS $$
+ return join '', reverse split('', $_[0]);
+$$ LANGUAGE plperl;
+</programlisting>
+
+ </para>
+
<para>
Perl can return <productname>PostgreSQL</productname> arrays as
references to Perl arrays. Here is an example:
--
2.39.2
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature of the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so should
not concern itself with references. In fact, this code breaks returning
objects with overloaded stringification, for example:CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl because
SvPVbyte() was passed a non-scalar value:postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV && SvTYPE(sv)
!= SVt_PVFM' failed.If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes the
above example as a test.
But without dereference it returns bad value.
Maybe there should be a check so references cannot be returned? Probably is
not safe pass pointers between Perl and Postgres.
Show quoted text
- ilmari
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature of the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so should
not concern itself with references. In fact, this code breaks returning
objects with overloaded stringification, for example:CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl because
SvPVbyte() was passed a non-scalar value:postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV && SvTYPE(sv)
!= SVt_PVFM' failed.If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes the
above example as a test.But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass, and
the one I included shows that it does the right thing in that case too.
If you pass it an unblessed reference it returns the stringified version
of that, as expected.
CREATE FUNCTION plperl_reference() RETURNS bytea LANGUAGE plperl
TRANSFORM FOR TYPE bytea
AS $$ return []; $$;
SELECT encode(plperl_reference(), 'escape') string;
string
-----------------------
ARRAY(0x559a3109f0a8)
(1 row)
This would also crash if the dereferencing loop was left in place.
Maybe there should be a check so references cannot be returned? Probably is
not safe pass pointers between Perl and Postgres.
There's no reason to ban references, that would break every Perl
programmer's expectations. And there are no pointers being passed,
SvPVbyte() returns the stringified form of whatever's passed in, which
is well-behaved for both blessed and unblessed references.
If we really want to be strict, we should at least allow references to
objects that overload stringification, as they are explicitly designed
to be well-behaved as strings. But that would be a lot of extra code
for very little benefit over just letting Perl stringify everything.
- ilmari
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature of the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so should
not concern itself with references. In fact, this code breaks returning
objects with overloaded stringification, for example:CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl because
SvPVbyte() was passed a non-scalar value:postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes the
above example as a test.But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass, and
the one I included shows that it does the right thing in that case too.
If you pass it an unblessed reference it returns the stringified version
of that, as expected.
ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;
return $ref;
$$ LANGUAGE plperlu;
CREATE FUNCTION
(2024-01-30 13:44:33) postgres=# select perl_inverse_bytes(''), ' '::bytea;
┌──────────────────────────────────────┬───────┐
│ perl_inverse_bytes │ bytea │
╞══════════════════════════════════════╪═══════╡
│ \x5343414c41522830783130656134333829 │ \x20 │
└──────────────────────────────────────┴───────┘
(1 row)
expected
(2024-01-30 13:46:58) postgres=# select perl_inverse_bytes(''), ' '::bytea;
┌────────────────────┬───────┐
│ perl_inverse_bytes │ bytea │
╞════════════════════╪═══════╡
│ \x0123 │ \x20 │
└────────────────────┴───────┘
(1 row)
Show quoted text
CREATE FUNCTION plperl_reference() RETURNS bytea LANGUAGE plperl
TRANSFORM FOR TYPE bytea
AS $$ return []; $$;SELECT encode(plperl_reference(), 'escape') string;
string
-----------------------
ARRAY(0x559a3109f0a8)
(1 row)This would also crash if the dereferencing loop was left in place.
Maybe there should be a check so references cannot be returned? Probably
is
not safe pass pointers between Perl and Postgres.
There's no reason to ban references, that would break every Perl
programmer's expectations. And there are no pointers being passed,
SvPVbyte() returns the stringified form of whatever's passed in, which
is well-behaved for both blessed and unblessed references.If we really want to be strict, we should at least allow references to
objects that overload stringification, as they are explicitly designed
to be well-behaved as strings. But that would be a lot of extra code
for very little benefit over just letting Perl stringify everything.- ilmari
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature of the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so should
not concern itself with references. In fact, this code breaks returning
objects with overloaded stringification, for example:CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl because
SvPVbyte() was passed a non-scalar value:postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes the
above example as a test.But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass, and
the one I included shows that it does the right thing in that case too.
If you pass it an unblessed reference it returns the stringified version
of that, as expected.ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;
You are returning a reference, not a string.
return $ref;
$$ LANGUAGE plperlu;
CREATE FUNCTION
(2024-01-30 13:44:33) postgres=# select perl_inverse_bytes(''), ' '::bytea;
┌──────────────────────────────────────┬───────┐
│ perl_inverse_bytes │ bytea │
╞══════════════════════════════════════╪═══════╡
│ \x5343414c41522830783130656134333829 │ \x20 │
└──────────────────────────────────────┴───────┘
(1 row)
~=# select encode('\x5343414c41522830783130656134333829', 'escape');
┌───────────────────┐
│ encode │
├───────────────────┤
│ SCALAR(0x10ea438) │
└───────────────────┘
This is how Perl stringifies references in the absence of overloading.
Return the byte string directly from your function and it will do the
right thing:
CREATE FUNCTION plperlu_bytes() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$ return pack 'H*', '0123'; $$;
SELECT plperlu_bytes();
plperlu_bytes
---------------
\x0123
(1 row)
- ilmari
út 30. 1. 2024 v 17:46 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl
does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because
they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature ofthe
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so
should
not concern itself with references. In fact, this code breaks
returning
objects with overloaded stringification, for example:
CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl
because
SvPVbyte() was passed a non-scalar value:
postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes
the
above example as a test.
But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass, and
the one I included shows that it does the right thing in that case too.
If you pass it an unblessed reference it returns the stringified version
of that, as expected.ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;You are returning a reference, not a string.
I know, but for this case, should not be raised an error?
Show quoted text
return $ref;
$$ LANGUAGE plperlu;
CREATE FUNCTION
(2024-01-30 13:44:33) postgres=# select perl_inverse_bytes(''), ''::bytea;
┌──────────────────────────────────────┬───────┐
│ perl_inverse_bytes │ bytea │
╞══════════════════════════════════════╪═══════╡
│ \x5343414c41522830783130656134333829 │ \x20 │
└──────────────────────────────────────┴───────┘
(1 row)~=# select encode('\x5343414c41522830783130656134333829', 'escape');
┌───────────────────┐
│ encode │
├───────────────────┤
│ SCALAR(0x10ea438) │
└───────────────────┘This is how Perl stringifies references in the absence of overloading.
Return the byte string directly from your function and it will do the
right thing:CREATE FUNCTION plperlu_bytes() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$ return pack 'H*', '0123'; $$;SELECT plperlu_bytes();
plperlu_bytes
---------------
\x0123
(1 row)- ilmari
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:46 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and json_plperl
does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant because
they
deal with non-scalar values (hashes for hstore, and also arrays for
json) which must be passed as references. The recursive nature ofthe
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so
should
not concern itself with references. In fact, this code breaks
returning
objects with overloaded stringification, for example:
CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff" }; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl
because
SvPVbyte() was passed a non-scalar value:
postgres: ilmari regression_bytea_plperl [local] SELECT: sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and includes
the
above example as a test.
But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass, and
the one I included shows that it does the right thing in that case too.
If you pass it an unblessed reference it returns the stringified version
of that, as expected.ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;You are returning a reference, not a string.
I know, but for this case, should not be raised an error?
I don't think so, as I explained in my previous reply:
There's no reason to ban references, that would break every Perl
programmer's expectations.
To elaborate on this: when a function is defined to return a string
(which bytea effectively is, as far as Perl is converned), I as a Perl
programmer would expect PL/Perl to just stringify whatever value I
returned, according to the usual Perl rules.
I also said:
If we really want to be strict, we should at least allow references to
objects that overload stringification, as they are explicitly designed
to be well-behaved as strings. But that would be a lot of extra code
for very little benefit over just letting Perl stringify everything.
By "a lot of code", I mean everything `string_amg`-related in the
amagic_applies() function
(https://github.com/Perl/perl5/blob/v5.38.0/gv.c#L3401-L3545). We can't
just call it: it's only available since Perl 5.38 (released last year),
and we support Perl versions all the way back to 5.14.
- ilmari
út 30. 1. 2024 v 18:26 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:46 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and
json_plperl
does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant
because
they
deal with non-scalar values (hashes for hstore, and also arrays
for
json) which must be passed as references. The recursive nature of
the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so
should
not concern itself with references. In fact, this code breaks
returning
objects with overloaded stringification, for example:
CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff"}; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl
because
SvPVbyte() was passed a non-scalar value:
postgres: ilmari regression_bytea_plperl [local] SELECT:
sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and
includes
the
above example as a test.
But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass,
and
the one I included shows that it does the right thing in that case
too.
If you pass it an unblessed reference it returns the stringified
version
of that, as expected.
ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;You are returning a reference, not a string.
I know, but for this case, should not be raised an error?
I don't think so, as I explained in my previous reply:
There's no reason to ban references, that would break every Perl
programmer's expectations.To elaborate on this: when a function is defined to return a string
(which bytea effectively is, as far as Perl is converned), I as a Perl
programmer would expect PL/Perl to just stringify whatever value I
returned, according to the usual Perl rules.
ok
Pavel
Show quoted text
I also said:
If we really want to be strict, we should at least allow references to
objects that overload stringification, as they are explicitly designed
to be well-behaved as strings. But that would be a lot of extra code
for very little benefit over just letting Perl stringify everything.
By "a lot of code", I mean everything `string_amg`-related in the
amagic_applies() function
(https://github.com/Perl/perl5/blob/v5.38.0/gv.c#L3401-L3545). We can't
just call it: it's only available since Perl 5.38 (released last year),
and we support Perl versions all the way back to 5.14.- ilmari
Hi
út 30. 1. 2024 v 18:35 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
út 30. 1. 2024 v 18:26 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:46 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 17:18 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
út 30. 1. 2024 v 16:43 odesílatel Dagfinn Ilmari Mannsåker <
ilmari@ilmari.org> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
I inserted perl reference support - hstore_plperl and
json_plperl
does
it.
+<->/* Dereference references recursively. */ +<->while (SvROK(in)) +<-><-->in = SvRV(in);That code in hstore_plperl and json_plperl is only relevant
because
they
deal with non-scalar values (hashes for hstore, and also arrays
for
json) which must be passed as references. The recursive nature
of
the
dereferencing is questionable, and masked the bug fixed by commit
1731e3741cbbf8e0b4481665d7d523bc55117f63.bytea_plperl only deals with scalars (specifically strings), so
should
not concern itself with references. In fact, this code breaks
returning
objects with overloaded stringification, for example:
CREATE FUNCTION plperlu_overload() RETURNS bytea LANGUAGE plperlu
TRANSFORM FOR TYPE bytea
AS $$
package StringOverload { use overload '""' => sub { "stuff"}; }
return bless {}, "StringOverload";
$$;This makes the server crash with an assertion failure from Perl
because
SvPVbyte() was passed a non-scalar value:
postgres: ilmari regression_bytea_plperl [local] SELECT:
sv.c:2865:
Perl_sv_2pv_flags:
Assertion `SvTYPE(sv) != SVt_PVAV && SvTYPE(sv) != SVt_PVHV &&SvTYPE(sv)
!= SVt_PVFM' failed.
If I remove the dereferincing loop it succeeds:
SELECT encode(plperlu_overload(), 'escape') AS string;
string
--------
stuff
(1 row)Attached is a v2 patch which removes the dereferencing and
includes
the
above example as a test.
But without dereference it returns bad value.
Where exactly does it return a bad value? The existing tests pass,
and
the one I included shows that it does the right thing in that case
too.
If you pass it an unblessed reference it returns the stringified
version
of that, as expected.
ugly test code
(2024-01-30 13:44:28) postgres=# CREATE or replace FUNCTION
perl_inverse_bytes(bytea) RETURNS bytea
TRANSFORM FOR TYPE bytea
AS $$ my $bytes = pack 'H*', '0123'; my $ref = \$bytes;You are returning a reference, not a string.
I know, but for this case, should not be raised an error?
I don't think so, as I explained in my previous reply:
There's no reason to ban references, that would break every Perl
programmer's expectations.To elaborate on this: when a function is defined to return a string
(which bytea effectively is, as far as Perl is converned), I as a Perl
programmer would expect PL/Perl to just stringify whatever value I
returned, according to the usual Perl rules.ok
Pavel
I also said:
If we really want to be strict, we should at least allow references to
objects that overload stringification, as they are explicitly designed
to be well-behaved as strings. But that would be a lot of extra code
for very little benefit over just letting Perl stringify everything.By "a lot of code", I mean everything `string_amg`-related in the
amagic_applies() function
(https://github.com/Perl/perl5/blob/v5.38.0/gv.c#L3401-L3545). We can't
just call it: it's only available since Perl 5.38 (released last year),
and we support Perl versions all the way back to 5.14.- ilmari
I marked this patch as ready for committer.
It is almost trivial, make check-world, make doc passed
Regards
Pavel
Hi!
On Tue, Jan 30, 2024 at 8:46 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
I marked this patch as ready for committer.
The last version of the patch still provides transform for builtin
type in a separate extension. As discussed upthread such transforms
don't need separate extensions, and could be provided as part of
upgrades of existing extensions. There is probably no consensus yet
on what to do with existing extensions like jsonb_plperl and
jsonb_plpython, but we clearly shouldn't spread such cases.
------
Regards,
Alexander Korotkov
út 27. 2. 2024 v 21:03 odesílatel Alexander Korotkov <aekorotkov@gmail.com>
napsal:
Hi!
On Tue, Jan 30, 2024 at 8:46 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:I marked this patch as ready for committer.
The last version of the patch still provides transform for builtin
type in a separate extension. As discussed upthread such transforms
don't need separate extensions, and could be provided as part of
upgrades of existing extensions. There is probably no consensus yet
on what to do with existing extensions like jsonb_plperl and
jsonb_plpython, but we clearly shouldn't spread such cases.
+1
Pavel
Show quoted text
------
Regards,
Alexander Korotkov
Alexander Korotkov <aekorotkov@gmail.com> writes:
On Tue, Jan 30, 2024 at 8:46 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
I marked this patch as ready for committer.
The last version of the patch still provides transform for builtin
type in a separate extension. As discussed upthread such transforms
don't need separate extensions, and could be provided as part of
upgrades of existing extensions. There is probably no consensus yet
on what to do with existing extensions like jsonb_plperl and
jsonb_plpython, but we clearly shouldn't spread such cases.
Yeah, I think including this as part of "plperl[u] 1.1" is probably
the best way forward. The patch of record doesn't do that, so
I've set the CF entry back to Waiting On Author.
Taking a quick look at the rest of the patch ... I think the
documentation is pretty inadequate, as it just says that the transform
causes byteas to be "passed and returned as native Perl octet
strings", a term that it doesn't define, and googling doesn't exactly
clarify either. The "example" is no example at all, as it does not
show what happens or how the results are different. (Admittedly
the nearby example for the bool transform is nearly as bad, but we
could improve that too while we're at it.)
regards, tom lane