Обсуждение: pgsql: Add transforms feature
Add transforms feature This provides a mechanism for specifying conversions between SQL data types and procedural languages. As examples, there are transforms for hstore and ltree for PL/Perl and PL/Python. reviews by Pavel Stěhule and Andres Freund Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/cac76582053ef8ea07df65fed0757f352da23705 Modified Files -------------- contrib/Makefile | 12 + contrib/hstore_plperl/.gitignore | 4 + contrib/hstore_plperl/Makefile | 24 + .../hstore_plperl/expected/create_transform.out | 74 + contrib/hstore_plperl/expected/hstore_plperl.out | 213 + contrib/hstore_plperl/hstore_plperl--1.0.sql | 17 + contrib/hstore_plperl/hstore_plperl.c | 90 + contrib/hstore_plperl/hstore_plperl.control | 6 + contrib/hstore_plperl/hstore_plperlu--1.0.sql | 17 + contrib/hstore_plperl/hstore_plperlu.control | 6 + contrib/hstore_plperl/sql/create_transform.sql | 47 + contrib/hstore_plperl/sql/hstore_plperl.sql | 148 + contrib/hstore_plpython/.gitignore | 6 + contrib/hstore_plpython/Makefile | 31 + .../hstore_plpython/expected/hstore_plpython.out | 132 + contrib/hstore_plpython/hstore_plpython.c | 116 + contrib/hstore_plpython/hstore_plpython2u--1.0.sql | 19 + contrib/hstore_plpython/hstore_plpython2u.control | 6 + contrib/hstore_plpython/hstore_plpython3u--1.0.sql | 19 + contrib/hstore_plpython/hstore_plpython3u.control | 6 + contrib/hstore_plpython/hstore_plpythonu--1.0.sql | 19 + contrib/hstore_plpython/hstore_plpythonu.control | 6 + contrib/hstore_plpython/sql/hstore_plpython.sql | 103 + contrib/ltree_plpython/.gitignore | 6 + contrib/ltree_plpython/Makefile | 31 + contrib/ltree_plpython/expected/ltree_plpython.out | 45 + contrib/ltree_plpython/ltree_plpython.c | 32 + contrib/ltree_plpython/ltree_plpython2u--1.0.sql | 12 + contrib/ltree_plpython/ltree_plpython2u.control | 6 + contrib/ltree_plpython/ltree_plpython3u--1.0.sql | 12 + contrib/ltree_plpython/ltree_plpython3u.control | 6 + contrib/ltree_plpython/ltree_plpythonu--1.0.sql | 12 + contrib/ltree_plpython/ltree_plpythonu.control | 6 + contrib/ltree_plpython/sql/ltree_plpython.sql | 37 + doc/src/sgml/catalogs.sgml | 82 + doc/src/sgml/hstore.sgml | 19 + doc/src/sgml/information_schema.sgml | 85 + doc/src/sgml/ltree.sgml | 15 + doc/src/sgml/ref/allfiles.sgml | 2 + doc/src/sgml/ref/alter_extension.sgml | 21 + doc/src/sgml/ref/comment.sgml | 22 + doc/src/sgml/ref/create_function.sgml | 18 + doc/src/sgml/ref/create_transform.sgml | 207 + doc/src/sgml/ref/drop_transform.sgml | 123 + doc/src/sgml/reference.sgml | 2 + src/Makefile.shlib | 2 +- src/backend/catalog/Makefile | 1 + src/backend/catalog/dependency.c | 8 + src/backend/catalog/information_schema.sql | 34 +- src/backend/catalog/objectaddress.c | 65 +- src/backend/catalog/pg_aggregate.c | 1 + src/backend/catalog/pg_proc.c | 46 +- src/backend/commands/dropcmds.c | 8 + src/backend/commands/event_trigger.c | 3 + src/backend/commands/functioncmds.c | 343 +- src/backend/commands/proclang.c | 3 + src/backend/commands/typecmds.c | 1 + src/backend/nodes/copyfuncs.c | 17 + src/backend/nodes/equalfuncs.c | 15 + src/backend/parser/gram.y | 90 +- src/backend/tcop/utility.c | 16 + src/backend/utils/adt/ruleutils.c | 39 +- src/backend/utils/cache/lsyscache.c | 71 + src/backend/utils/cache/syscache.c | 23 + src/backend/utils/fmgr/funcapi.c | 44 + src/bin/pg_dump/common.c | 5 + src/bin/pg_dump/pg_dump.c | 273 +- src/bin/pg_dump/pg_dump.h | 11 + src/bin/pg_dump/pg_dump_sort.c | 11 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/dependency.h | 1 + src/include/catalog/indexing.h | 5 + src/include/catalog/pg_class.h | 2 +- src/include/catalog/pg_proc.h | 5382 ++++++++++---------- src/include/catalog/pg_proc_fn.h | 3 + src/include/catalog/pg_transform.h | 47 + src/include/commands/defrem.h | 3 + src/include/funcapi.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 15 + src/include/parser/kwlist.h | 2 + src/include/utils/lsyscache.h | 3 + src/include/utils/syscache.h | 2 + src/interfaces/ecpg/preproc/ecpg.tokens | 2 +- src/interfaces/ecpg/preproc/ecpg.trailer | 5 +- src/interfaces/ecpg/preproc/ecpg_keywords.c | 2 - src/pl/plperl/GNUmakefile | 4 +- src/pl/plperl/plperl.c | 47 +- src/pl/plperl/plperl_helpers.h | 2 + src/pl/plpython/Makefile | 40 +- src/pl/plpython/plpy_main.c | 1 + src/pl/plpython/plpy_procedure.c | 43 +- src/pl/plpython/plpy_procedure.h | 2 + src/pl/plpython/plpy_spi.c | 3 +- src/pl/plpython/plpy_typeio.c | 159 +- src/pl/plpython/plpy_typeio.h | 9 +- src/pl/plpython/plpy_util.c | 21 +- src/pl/plpython/plpy_util.h | 1 + src/pl/plpython/plpython.h | 1 + src/pl/plpython/regress-python3-mangle.mk | 35 + src/test/regress/expected/sanity_check.out | 1 + 101 files changed, 6061 insertions(+), 2838 deletions(-)
Peter Eisentraut <peter_e@gmx.net> writes:
> Add transforms feature
I don't know why this patch is fooling around with compile/link flags,
but it's broken at least prairiedog and some of the Windows critters.
regards, tom lane
On Mon, Apr 27, 2015 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter_e@gmx.net> writes: >> Add transforms feature > > I don't know why this patch is fooling around with compile/link flags, > but it's broken at least prairiedog and some of the Windows critters. It breaks as well on all the Windows machines using MS or VC toolchains... -- Michael
On 4/27/15 2:23 AM, Michael Paquier wrote: > On Mon, Apr 27, 2015 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Peter Eisentraut <peter_e@gmx.net> writes: >>> Add transforms feature >> >> I don't know why this patch is fooling around with compile/link flags, >> but it's broken at least prairiedog and some of the Windows critters. > > It breaks as well on all the Windows machines using MS or VC toolchains... The failure on bowerbird (Windows msvc) is because the build system needs to be told to add additional include directories to the compilation. The failure on frogmouth (Windows gcc) is because of some conflict with perl headers. I must have missed a trick that was already in use for plperl. Help in either case, but especially the first one, is welcome.
Peter Eisentraut wrote: > Add transforms feature > > This provides a mechanism for specifying conversions between SQL data > types and procedural languages. As examples, there are transforms > for hstore and ltree for PL/Perl and PL/Python. BF member olinguito is also failing the contrib test, because plpython is not compiled in that platform. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 04/27/2015 08:21 AM, Peter Eisentraut wrote: > On 4/27/15 2:23 AM, Michael Paquier wrote: >> On Mon, Apr 27, 2015 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Peter Eisentraut <peter_e@gmx.net> writes: >>>> Add transforms feature >>> I don't know why this patch is fooling around with compile/link flags, >>> but it's broken at least prairiedog and some of the Windows critters. >> It breaks as well on all the Windows machines using MS or VC toolchains... > The failure on bowerbird (Windows msvc) is because the build system > needs to be told to add additional include directories to the compilation. > > The failure on frogmouth (Windows gcc) is because of some conflict with > perl headers. I must have missed a trick that was already in use for > plperl. > > Help in either case, but especially the first one, is welcome. > > I haven't looked at the MSVC case yet, but one thing I notice on jacana (Windows 8.1, gcc) is that the order of include directories is different. When compiling plperl, the perl CORE directory is last in the include list, whereas for compiling hstore_plperl it's second from the beginning. I have no idea yet if that makes any difference, but would not be at all surprised it if did. We should probably be mimicking what the plperl makefile does. Incidentally, I notice that hstore_plperl.c doesn't have a copyright notice nor a single comment, not even at the start of the public functions. That seems less than good. cheers andrew
On 04/27/2015 07:54 PM, Andrew Dunstan wrote: > > On 04/27/2015 08:21 AM, Peter Eisentraut wrote: >> On 4/27/15 2:23 AM, Michael Paquier wrote: >>> On Mon, Apr 27, 2015 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> Peter Eisentraut <peter_e@gmx.net> writes: >>>>> Add transforms feature >>>> I don't know why this patch is fooling around with compile/link flags, >>>> but it's broken at least prairiedog and some of the Windows critters. >>> It breaks as well on all the Windows machines using MS or VC >>> toolchains... >> The failure on bowerbird (Windows msvc) is because the build system >> needs to be told to add additional include directories to the >> compilation. >> >> The failure on frogmouth (Windows gcc) is because of some conflict with >> perl headers. I must have missed a trick that was already in use for >> plperl. >> >> Help in either case, but especially the first one, is welcome. >> >> > > > I haven't looked at the MSVC case yet, but one thing I notice on > jacana (Windows 8.1, gcc) is that the order of include directories is > different. When compiling plperl, the perl CORE directory is last in > the include list, whereas for compiling hstore_plperl it's second from > the beginning. I have no idea yet if that makes any difference, but > would not be at all surprised it if did. We should probably be > mimicking what the plperl makefile does. > > Incidentally, I notice that hstore_plperl.c doesn't have a copyright > notice nor a single comment, not even at the start of the public > functions. That seems less than good. > > w.r.t MSVC builds, it looks like we need entries in $contrib_extraincludes in src/tools/msvc/Mkvcbuild.pm at the very least. cheers andrew
On Tue, Apr 28, 2015 at 9:55 AM, Andrew Dunstan wrote: > w.r.t MSVC builds, it looks like we need entries in $contrib_extraincludes > in src/tools/msvc/Mkvcbuild.pm at the very least. If our goal is to put back to green the Windows nodes as quick as possible, we could bypass their build this way , and we'll additionally need to update the install script and vcregress.pl/contribcheck to bypass those modules accordingly. Now I don't think that we should make the things produced inconsistent. -- Michael
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Peter Eisentraut wrote:
>> Add transforms feature
> BF member olinguito is also failing the contrib test, because plpython
> is not compiled in that platform.
Yeah. Even more specifically, olinguito does have --with-python in its
configure flags, but then the plpython Makefile skips the build because
libpython isn't available as a shared library. But the contrib test is
(I assume, haven't looked) conditional only on the configure flag.
I'm not real sure now why we felt that was a good approach. The general
project policy is that if you ask for a feature in the configure flags,
we'll build it or die trying; how come this specific Python issue gets
special treatment contrary to that policy?
So I'd vote for changing that to put the shared-library test in configure,
or at least make it a build failure later on, not "silently don't build
what was asked for". That would mean olinguito's configure flags would
have to be adjusted.
Plan B would require propagating the shared-libpython test into the
contrib makefiles, which seems pretty unpalatable even if you're willing
to defend the status quo otherwise.
regards, tom lane
On Tue, Apr 28, 2015 at 10:01 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Tue, Apr 28, 2015 at 9:55 AM, Andrew Dunstan wrote: >> w.r.t MSVC builds, it looks like we need entries in $contrib_extraincludes >> in src/tools/msvc/Mkvcbuild.pm at the very least. > > If our goal is to put back to green the Windows nodes as quick as > possible, we could bypass their build this way , and we'll > additionally need to update the install script and > vcregress.pl/contribcheck to bypass those modules accordingly. Now I > don't think that we should make the things produced inconsistent. OK, attached are two patches to put back the buildfarm nodes using MSVC to green - 0001 adds support for build and installation of the new transform modules: hstore_plperl, hstore_plpython and ltree_plpython. Note that this patch is enough to put back the buildfarm to a green state for MSVC, but it disables the regression tests for those modules, something addressed in the next patch... - 0002 adds support for regression tests for the new modules. The thing is that we need to check the major version version of Python available in configuration and choose what are the extensions to preload before the tests run. It is a little bit hacky... But it has the merit to work, and I am not sure we could have a cleaner solution by looking at the Makefiles of the transform modules that use currently conditions based on $(python_majorversion). Regards, -- Michael
Вложения
On 04/28/2015 09:04 AM, Michael Paquier wrote: > On Tue, Apr 28, 2015 at 10:01 AM, Michael Paquier > <michael.paquier@gmail.com> wrote: >> On Tue, Apr 28, 2015 at 9:55 AM, Andrew Dunstan wrote: >>> w.r.t MSVC builds, it looks like we need entries in $contrib_extraincludes >>> in src/tools/msvc/Mkvcbuild.pm at the very least. >> If our goal is to put back to green the Windows nodes as quick as >> possible, we could bypass their build this way , and we'll >> additionally need to update the install script and >> vcregress.pl/contribcheck to bypass those modules accordingly. Now I >> don't think that we should make the things produced inconsistent. > OK, attached are two patches to put back the buildfarm nodes using MSVC to green > - 0001 adds support for build and installation of the new transform > modules: hstore_plperl, hstore_plpython and ltree_plpython. Note that > this patch is enough to put back the buildfarm to a green state for > MSVC, but it disables the regression tests for those modules, > something addressed in the next patch... > - 0002 adds support for regression tests for the new modules. The > thing is that we need to check the major version version of Python > available in configuration and choose what are the extensions to > preload before the tests run. It is a little bit hacky... But it has > the merit to work, and I am not sure we could have a cleaner solution > by looking at the Makefiles of the transform modules that use > currently conditions based on $(python_majorversion). > Regards, I have pushed the first of these with some tweaks. I'm looking at the second. cheers andrew
[switching to -hackers]
On 04/28/2015 11:51 AM, Andrew Dunstan wrote:
>
> On 04/28/2015 09:04 AM, Michael Paquier wrote:
>> On Tue, Apr 28, 2015 at 10:01 AM, Michael Paquier
>> <michael.paquier@gmail.com> wrote:
>>> On Tue, Apr 28, 2015 at 9:55 AM, Andrew Dunstan wrote:
>>>> w.r.t MSVC builds, it looks like we need entries in
>>>> $contrib_extraincludes
>>>> in src/tools/msvc/Mkvcbuild.pm at the very least.
>>> If our goal is to put back to green the Windows nodes as quick as
>>> possible, we could bypass their build this way , and we'll
>>> additionally need to update the install script and
>>> vcregress.pl/contribcheck to bypass those modules accordingly. Now I
>>> don't think that we should make the things produced inconsistent.
>> OK, attached are two patches to put back the buildfarm nodes using
>> MSVC to green
>> - 0001 adds support for build and installation of the new transform
>> modules: hstore_plperl, hstore_plpython and ltree_plpython. Note that
>> this patch is enough to put back the buildfarm to a green state for
>> MSVC, but it disables the regression tests for those modules,
>> something addressed in the next patch...
>> - 0002 adds support for regression tests for the new modules. The
>> thing is that we need to check the major version version of Python
>> available in configuration and choose what are the extensions to
>> preload before the tests run. It is a little bit hacky... But it has
>> the merit to work, and I am not sure we could have a cleaner solution
>> by looking at the Makefiles of the transform modules that use
>> currently conditions based on $(python_majorversion).
>> Regards,
>
>
> I have pushed the first of these with some tweaks.
>
> I'm looking at the second.
>
>
Regarding this second patch - apart from the buggy python version test
which I just fixed in the first patch, I see this:
+ if ($pyver eq "2")
+ {
+ push @opts, "--load-extension=plpythonu";
+ push @opts, '--load-extension=' . $module . 'u';
+ }
But what do we do for Python3?
Do we actually have a Windows machine building with Python3?
cheers
andrew
On 4/26/15 12:36 PM, Tom Lane wrote:
> I don't know why this patch is fooling around with compile/link flags,
> but it's broken at least prairiedog
The addition of the link flag -undefined dynamic_lookup is so that
plugins can refer to symbols from other plugins. This is how other
platforms work by default, and it is standard for OS X in GNU libtool
and many other projects. I'm confused why prairiedog is complaining
about that. According to Internet lore, this flag seems to be available
from OS X either 10.3 or 10.1. prairiedog is listed as 10.4, but errors
with
MACOSX_DEPLOYMENT_TARGET environment variable set to: 10.1
Some of the above-mentioned other projects appear to have some OS X
version test, so we could do something like that. But before we embark
on that, could you clarify whether the build farm entry lists the
correct version?
Peter Eisentraut <peter_e@gmx.net> writes:
> ... I'm confused why prairiedog is complaining
> about that. According to Internet lore, this flag seems to be available
> from OS X either 10.3 or 10.1. prairiedog is listed as 10.4, but errors
> with
> MACOSX_DEPLOYMENT_TARGET environment variable set to: 10.1
> Some of the above-mentioned other projects appear to have some OS X
> version test, so we could do something like that. But before we embark
> on that, could you clarify whether the build farm entry lists the
> correct version?
So far as that goes, prairiedog is definitely running OS X 10.4.11.
I cannot find any sign that MACOSX_DEPLOYMENT_TARGET is explicitly set
anywhere. I resorted to grepping the entire filesystem, and found no
smoking gun but a few suggestive hits:
Binary file /usr/bin/ld matches
Binary file /usr/bin/powerpc-apple-darwin8-g++-4.0.1 matches
Binary file /usr/bin/powerpc-apple-darwin8-gcc-4.0.1 matches
/usr/share/aclocal/libtool.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/aclocal/libtool.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/aclocal/libtool.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/acinclude.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/acinclude.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/acinclude.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/aclocal.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/aclocal.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/aclocal.m4: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/configure: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/configure: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/configure: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/configure: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
/usr/share/libtool/libltdl/configure: case "${MACOSX_DEPLOYMENT_TARGET-10.1}" in
This would explain libtool & libltdl defaulting to 10.1, but I wouldn't
have thought that we use either of those tools. Still, the file dates
on these files are comparable to the file dates on gcc and ld (viz late
2005/early 2006, which matches the window in which Tiger was current).
It would not be terribly surprising if there is an equivalent default
version assumption buried in those executables.
In short then, my best guess is that the 10.4-era build tools defaulted
to building 10.1-compatible product files unless you explicitly override
that by setting MACOSX_DEPLOYMENT_TARGET, which I haven't.
regards, tom lane
I wrote:
> In short then, my best guess is that the 10.4-era build tools defaulted
> to building 10.1-compatible product files unless you explicitly override
> that by setting MACOSX_DEPLOYMENT_TARGET, which I haven't.
And indeed, explicitly setting MACOSX_DEPLOYMENT_TARGET = 10.4 gets
past the build failure. In the interests of getting the buildfarm
back to green, I've added that to the build environment spec for
prairiedog (we should have confirmation that it works in an hour or so).
However, I would really like this to be a temporary measure not the
permanent solution, for the reasons I gave earlier.
regards, tom lane
I wrote:
> And indeed, explicitly setting MACOSX_DEPLOYMENT_TARGET = 10.4 gets
> past the build failure. In the interests of getting the buildfarm
> back to green, I've added that to the build environment spec for
> prairiedog (we should have confirmation that it works in an hour or so).
Well, it sort of works. Looks like the hstore_plpython test is expecting
some library function that Python 2.3 doesn't have.
regards, tom lane
On 05/03/2015 06:27 PM, Tom Lane wrote: > I wrote: >> And indeed, explicitly setting MACOSX_DEPLOYMENT_TARGET = 10.4 gets >> past the build failure. In the interests of getting the buildfarm >> back to green, I've added that to the build environment spec for >> prairiedog (we should have confirmation that it works in an hour or so). > Well, it sort of works. Looks like the hstore_plpython test is expecting > some library function that Python 2.3 doesn't have. > > I don't know if we have a standard for supported Python versions, but Python 2.3.0 was released in 2003, and the last release in the series, 2.3.7 was released in March 2008. Is that really what ships on a modern OSX? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> On 05/03/2015 06:27 PM, Tom Lane wrote:
>> Well, it sort of works. Looks like the hstore_plpython test is expecting
>> some library function that Python 2.3 doesn't have.
> I don't know if we have a standard for supported Python versions, but
> Python 2.3.0 was released in 2003, and the last release in the series,
> 2.3.7 was released in March 2008. Is that really what ships on a modern OSX?
No, of course not; prairiedog is a very very trailing-edge box.
However, we explicitly claim support for Python >= 2.3 in the docs (see
installation.sgml), which is why prairiedog has that version and not
some other one. If we're going to move the goalposts on the minimum
supported version, it should be for a better reason than "I was too
lazy to craft a regression test using sufficiently backwards-compatible
library functions".
regards, tom lane
On 05/03/2015 06:54 PM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> On 05/03/2015 06:27 PM, Tom Lane wrote: >>> Well, it sort of works. Looks like the hstore_plpython test is expecting >>> some library function that Python 2.3 doesn't have. >> I don't know if we have a standard for supported Python versions, but >> Python 2.3.0 was released in 2003, and the last release in the series, >> 2.3.7 was released in March 2008. Is that really what ships on a modern OSX? > No, of course not; prairiedog is a very very trailing-edge box. > However, we explicitly claim support for Python >= 2.3 in the docs (see > installation.sgml), which is why prairiedog has that version and not > some other one. If we're going to move the goalposts on the minimum > supported version, it should be for a better reason than "I was too > lazy to craft a regression test using sufficiently backwards-compatible > library functions". > > Oh, sorry, I misunderstood. As it's just a matter of a function call from python by all means let's change it. The docs say "sorted" is new in 2.4. See <https://docs.python.org/2/library/functions.html#sorted>, but this will surely be fairly trivial to fix. cheers andrew