Re: [PATCH] Add transforms feature - Mailing list pgsql-hackers

On 11/15/2013 05:04 PM, Dimitri Fontaine wrote:
> Hi,
>
> Peter Eisentraut <peter_e@gmx.net> writes:
>> Rebased patch.  No changes except that merge conflicts were resolved,
>> and I had to add some Data::Dumper tweaks to the regression tests so
>> that the results came out in  consistent order on different versions of
>> Perl.
....
> On the higher level design, the big question here is about selective
> behavior. As soon as you CREATE TRANSFORM FOR hstore LANGUAGE plperl
> then any plperl function will now receive its hstore arguments as a
> proper perl hash rather than a string.
>
> Any pre-existing plperl function with hstore arguments or return type
> then needs to be upgraded to handle the new types nicely, and some of
> those might not be under the direct control of the DBA running the
> CREATE TRANSFORM command, when using some plperl extensions for example.
>
> A mechanism allowing for the transform to only be used in some functions
> but not others might be useful. The simplest such mechanism I can think
> of is modeled against the PL/Java classpath facility as specified in the
> SQL standard: you attach a classpath per schema.
If we start adding granularity, then why not go all the way?

I mean, we could do it in the following way

1) create named transforms

CREATE [DEFAULT] TRANSFORM <xformname> FOR <type> LANGUAGE <lang> (...details...);

2) use it when declaring a function

CREATE function <funcname>(   IN <argname> <type> [[USING] [TRANSFORM] <xformname>],   INOUT <argname> <type> [[USING]
[IN][TRANSFORM] <xformname>] [[USING] [OUT] [TRANSFORM] <xformname>],   OUT <argname> <type> [[USING] [TRANSFORM]
<xformname>],
... 
) LANGUAGE <lang> $$
<funcdef>
$$;

This approach allows full flexibility in using "old" packages, especially
if we define old transform behaviour as "DEFAULT TRANSFORM"

Default transforms also allow easy way for rewriting current type i/o
conversions between languages into transforms.

There are immediately a few transforms that I would find useful

A) pass field data to language as pairs of (typeoid, typebin)
   this is useful for speed, especially if you do not want to use many
of the passed arguments on most invocations

B) pass field data in as (typeoid, typebin), except do not de-toast
values but
pass in the toast ids, so the function is free to use only parts of
toasted values as it needs

C) pass field data in as string, probably the default behaviour for
languages like pl/tcl and pl/sh

D) and then of course just having a sensible transforms for extension
types like the current patch provides.

> Worst case, that I really don't think we need, would be addressing that
> per-argument:
>
>   CREATE FUNCTION foo (hash hstore WITH TRANSFORM, kv hstore) …
>
> I certainly hope we don't need that, and sure can't imagine use cases
> for that level of complexity at the time of writing this review.
>
A typical use case would be to have a "short" hstore always passed in as
dictionary
and have another possibly large hstore passed in as toast pointer.

And if we want to have all type conversions between postgres and pls
re-written
as transforms, then we do need named transforms, not just one per (pl,
type) pair.

Also, if we allow flexibility, the it is probably a good idea to
implement full flexibility
first and then look at making usage easy after that, instead of adding
flexibility in
small steps.

Once we have per-argument transforms in place, we can look at setting
per-schema
defaults for ease of use.

As large part of this is actually abstracting i/o conversions out of pl
function code,
I think we should look at allowing the conversion functions to be
written in the
target pl language in addition to C.

I'll see if I can resurrect my patch for support of "cstring" and
"internal" types in pl/python
function defs for this.

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ