Re: [PATCH] Add transforms feature

On Thu, Jun 13, 2013 at 8:11 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> A transform is an SQL object that supplies to functions for converting
> between data types and procedural languages. For example, a transform
> could arrange that hstore is converted to an appropriate hash or
> dictionary object in PL/Perl or PL/Python.
>
>
The patch applies and regression including contrib passes in my Mac. I
know I came late, but I have a few questions.

- vs SQL standard
I'm worried about overloading the standard. As document says, SQL standard
defines CREATE TRANSFORM syntax which is exactly the same as this proposal
but for different purpose. The standard feature is the data conversion
between client and server side data type, I guess. I am concerned about it
because in the future if someone wants to implement this SQL standard
feature, there is no way to break thing. I'd be happy if subsequent clause
was different. CREATE TYPE has two different syntax, one for composite
type and one for internal user-defined type (I'm not sure either is defined
in the standard, though) and I think we could do something like that. Or
as someone suggested in the previous thread, it might be a variant of
CAST. CREATE CAST (hstore AS plpython2u) ? Or CREATE LANGUAGE TRANSFORM
might sound better. In either case, I think we are missing the discussion
on the standard overloading.

- dependency loading issue
Although most of the use cases are via CREATE EXTENSION, it is not great to
let users to load the dependency manually. Is it possible to load
hstore.so and plpython2u.so from _PG_init of hstore_plpython2u? Because
the author of transform should certainly know the name of shared library in
the database installation, writing down the shared library names in the
init function sounds reasonable. Or do we still need to consider cases
where plpython2u.so is renamed to something else?

- function types
Although I read the suggestion to use internal type as the argument of
from_sql function, I guess it exposes another security risk. Since we
don't know what SQL type can safely be passed to the from_sql function, we
cannot check if the function is the right one at the time of CREATE
TRANSFORM. Non-super user can add his user defined type and own it, and
create a transform that with from_sql function that takes internal and
crashes with this user-defined type. A possible compromise is let only
super user create transforms, or come up with nice way to allow
func(sql_type) -> internal signature.

- create or replace causes inconsistency
I tried:
* create transform python to hstore (one way transform)
* create function f(h hstore) language python
* create or replace transform hstore to python and python to hstore (both
ways)
* call f() causes error, since it misses hstore to python transform. It
is probably looking at the old definition

- create func -> create transform is not prohibited
I saw your post in the previous discussion:

> > * I don't think recording dependencies on transforms used when creating
> > functions is a good idea as the transform might get created after the
> > functions already exists. That seems to be a pretty confusing behaviour.
>
> We need the dependencies, because otherwise dropping a transform would
> break or silently alter the behavior of functions that depend on it.
> That sounds like my worst nightmare, thinking of some applications that
> would be affected by that. But your point is a good one. I think this
> could be addressed by prohibiting the creation of a transform that
> affects functions that already exist.

However I don't see this prohibition of create transform if there is
already such function. You are not planning to address this issue?

For now, that's it. I'm going to dig more later.

Thanks,

Hitoshi Harada