RFC for adding typmods to functions

Pavel submitted a patch to add typmods to function declarations, but there
was no prior design discussion and it desperately needs some. Let me try
to summarize the issues that seem to need agreement.

The proposed patch allows optional typmods to be attached to the declared
argument and result types of a function; for example you could say
"create function foo(numeric(2)) returns numeric(4)". (Note: in existing
releases, this syntax works but the typmod information is simply
discarded.) An immediate application, not implemented here but which
we'd like to have for 8.5, is multiple anyelement types -- for example,

create function foo(anyelement, anyelement, anyelement(1), anyelement(1))
returns anyelement(1)

says that the first and second arguments must be of the same type, the
third and fourth must also be of the same type but not necessarily the
same as the first two, and the result is of this second type.

I can see the following definitional issues:

1. Are the typmods of input arguments part of the function signature,
ie, could foo(numeric(2)) and foo(numeric(3)) coexist? The proposed
patch answers "no, they are the same function and you can have only one".
This may be good enough, but there are some possible uses that we are
foreclosing by doing this. Two sample applications:

foo(numeric) a general-purpose function
foo(numeric(2)) same definition but optimized for short inputs

foo(anyelement, anyelement(1)) general case
foo(anyelement, anyelement) optimized for identical input types

The major obstacle to allowing such cases is that we'd need to invent new
ambiguous-function resolution rules that would let us figure out which
function to prefer for a given set of inputs, and it's not at all clear
how to do that --- in particular deciding that one is preferable to
another seems to require type-specific knowledge about the meaning of
different typmods. So that looks like a major can of worms, probably
requiring new APIs for custom data types.

A possible compromise is to say that you can have only one now but leave
the door open to allow more than one later. However, the function
signature is the function identity for many purposes, so it's hard to be
fuzzy about this. For example, given "CREATE FUNCTION foo(numeric(2))",
which of the following should drop the function?
DROP FUNCTION foo(numeric(2));
DROP FUNCTION foo(numeric);
DROP FUNCTION foo(numeric(3));
The traditional behavior is that any of these would work, since the
typmod was ignored anyway. If the typmod means something then the
second one is a bit surprising and the third definitely doesn't
satisfy the POLA. Are we prepared to possibly break existing apps
now by disallowing the third and/or second?

2. What is the exact meaning of attaching a typmod to an input argument?
As the patch has it, doing so means nothing at all for the purposes of
resolving which function to call, and then once we have identified the
function we will attempt to apply an implicit coercion to the actual input
argument to make it match the typmod. The first part of that is probably
reasonable if you accept the "there can be only one" answer to point #1;
but if you don't then it's completely unworkable. In any case it's worth
noting that foo(anyelement, anyelement) will accept two arguments of the
same types and different typmods, which might surprise people. The second
part is trickier, in particular the fact that the coercion is implicit.
Up to now there have been only assignment and explicit coercions that
could try to apply a typmod to a value. Our existing API for coercion
functions (see the CREATE CAST man page if you don't recall details)
doesn't even provide a way for the coercion function to distinguish
implicit from assignment coercions. Maybe this is fine --- on that same
page we say it's bad design for coercion functions to pay attention to the
cast context anyhow. But we had better agree that it's okay for such
coercions to behave more like assignment than like a traditional implicit
cast. If you want to distinguish the cases, we need to break that API.

3. What is the exact meaning of attaching a typmod to a result or output
argument? There are two fundamentally different views you can take on
this point: that the typmod is an assertion that the function result
matches the typmod, or that the typmod requests a run-time coercion step
to make the result match the typmod. For C-level functions the first of
these seems more natural; after all we take it on faith that the result is
of the declared type. In particular, you *have to* adopt that viewpoint
towards the coercion functions of the type, because the system has no
other knowledge of what a typmod means than "the results of the type's
coercion functions have the correct properties for the given typmod
value". For PL functions I doubt we want to trust the function writer
completely that his results match the typmod, but should we adopt an
approach of "check the result" (and, presumably, throw error if it doesn't
meet the typmod) or "force a coercion" (and if so, with which semantics
--- explicit, assignment, implicit)? The former would require
infrastructure we have not currently got, ie, a "check typmod" function
for datatypes supporting typmods. The latter seems a bit ugly because it
gives PL functions a subtly different set of semantics from C functions.
In either case it seems we'd have to hope that all PL authors remember
to insert code to do that, or else we have a hole in the type system:
functions returning values that don't meet the typmod the system thinks
they do. We can fix all the built-in PLs but I'll gladly wager that
at least one third-party PL will forget to deal with this, and nobody will
notice until it's reported as a security bug.

4. What about functions whose output typmod should depend on the input
typmod(s)? I mentioned earlier the example that concatenation of
varchar(M) and varchar(N) should produce varchar(M+N). We could possibly
punt on this for the time being; supporting only fixed output typmods for
now doesn't obviously foreclose us from adding support for computed
typmods later. However there is still one nasty case that we cannot
push off till later: given a function that takes and returns a polymorphic
type such as anyelement, and an actual argument with a typmod (eg
numeric(2)), is the result numeric(2) or just numeric? As things stand
we would have little choice but to say the latter, because we don't know
what the function might do with the value, and there are too many real
cases where the result might not have the same typmod. But there are
also a lot of cases where you *would* wish that it has the same typmod,
and this patch raises the stakes for throwing away typmods mid-expression.
Is this okay, and if not what could we do about it?

Unless we have consensus on all of these points I don't think we should
proceed with the patch. Comments?

regards, tom lane

Tom Lane wrote:
> Pavel submitted a patch to add typmods to function declarations, but there
> was no prior design discussion and it desperately needs some. Let me try
> to summarize the issues that seem to need agreement.
>
>

[excellent summary of problem areas snipped]

> Unless we have consensus on all of these points I don't think we should
> proceed with the patch. Comments?
>
>
>

Apart from all these it's not clear to me what the major benefits of
doing this would be. I'd like an explanation of that to start with.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Apart from all these it's not clear to me what the major benefits of
> doing this would be. I'd like an explanation of that to start with.

Well, aside from the issue about making "anyelement" more powerful
(which could be done in other ways), I can think of:

If we don't start down this path then we are never going to satisfy the
spec's expectations about type modifiers (the varchar concatenation
example among others). The given patch doesn't do that or even come
close, but it's a necessary prelude.

More generally, people have complained in the past about typmods being
red-headed stepchildren in the type system. At present, since the
majority of expression forms throw away typmod information, there's
not much hope of treating typmod on the same level as type proper.
(If you look at the history of the expression-tree code you'll notice
that we've gradually propagated typmods into more and more places.
Associating a typmod with function results is the last major holdout.)

I'm not sure that these points fully justify the work involved, but
it certainly seems to be a logical avenue of development if we can
agree on the semantics.

regards, tom lane

On Tue, Nov 17, 2009 at 6:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> Apart from all these it's not clear to me what the major benefits of
>> doing this would be. I'd like an explanation of that to start with.
>
> Well, aside from the issue about making "anyelement" more powerful
> (which could be done in other ways), I can think of:
>
> If we don't start down this path then we are never going to satisfy the
> spec's expectations about type modifiers (the varchar concatenation
> example among others).  The given patch doesn't do that or even come
> close, but it's a necessary prelude.
>
> More generally, people have complained in the past about typmods being
> red-headed stepchildren in the type system.  At present, since the
> majority of expression forms throw away typmod information, there's
> not much hope of treating typmod on the same level as type proper.
> (If you look at the history of the expression-tree code you'll notice
> that we've gradually propagated typmods into more and more places.
> Associating a typmod with function results is the last major holdout.)
>
> I'm not sure that these points fully justify the work involved, but
> it certainly seems to be a logical avenue of development if we can
> agree on the semantics.

There's something a little weird about the whole typmod concept. In
the case of varchar(n) and numeric(x,y), it seems as though it's
perhaps intended to allow the database to optimize by limiting the
amount of on-disk storage that needs to be set aside for those values.
Or you could alternatively view it as a type of constraint, like "the
length of this string is at most n characters". The thing is, in most
modern programming languages, this type of information isn't part of
the type system at all. You have a type called string, and it
represents a string of any length at all (of course, in C strings are
fixed size-arrays, but that's more the exception than the rule, and C
is a much lower-level language than SQL). Typically, details like the
maximum number of characters in the string or the desired scale and
precision of a numeric value aren't dealt with until you try to print
the value out.

So I guess really can't get worked up about the idea of propagating
this information through the type system. Even suppose we eventually
take the steps you suggesting and make it so that varchar(30) ||
varchar(40) yields varchar(70). What good is that? Why would anyone
care?

What would actually be really nice is the ability to have
parameterized types (like list-of-<some type>,
unordered-set-of-<type>, hash-with-keys-of-<some
type>-and-values-of-<some type>, function-taking-arguments-of-<various
types>-returning-<some type>) which would let us do all kinds of neat
things - but I don't see how improving support for typmods gets us any
closer to that.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> So I guess really can't get worked up about the idea of propagating
> this information through the type system. Even suppose we eventually
> take the steps you suggesting and make it so that varchar(30) ||
> varchar(40) yields varchar(70). What good is that? Why would anyone
> care?

People have complained that we don't follow the spec on this. Not many
people, perhaps, and it's certainly arguable that fixing this will be
far more trouble than it's worth. But there is a constituency that
cares --- mainly people who use client-side code that tends to fall over
if it doesn't see a suitable maxlength attached to query result columns.
The first example I came across in the archives was
http://archives.postgresql.org/pgsql-sql/2002-06/msg00235.php
[ pokes around a bit more ... ] Hm, I don't see any *recent* examples.
Maybe all that code has gotten fixed? Nah ...

> What would actually be really nice is the ability to have
> parameterized types (like list-of-<some type>,
> unordered-set-of-<type>, hash-with-keys-of-<some
> type>-and-values-of-<some type>, function-taking-arguments-of-<various
> types>-returning-<some type>) which would let us do all kinds of neat
> things - but I don't see how improving support for typmods gets us any
> closer to that.

Well, we could possibly implement hacks like the current one for
anonymous record types. But SQL isn't intended as a language for
manipulating arbitrary data types, and I think trying to make it
do stuff like the above will be an exercise in masochism. typmod
is mainly intended for tweaking the properties of base types, and
it seems fairly useful for that.

regards, tom lane

On Tue, Nov 17, 2009 at 7:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> So I guess really can't get worked up about the idea of propagating
>> this information through the type system.  Even suppose we eventually
>> take the steps you suggesting and make it so that varchar(30) ||
>> varchar(40) yields varchar(70).  What good is that?  Why would anyone
>> care?
>
> People have complained that we don't follow the spec on this.  Not many
> people, perhaps, and it's certainly arguable that fixing this will be
> far more trouble than it's worth.  But there is a constituency that
> cares --- mainly people who use client-side code that tends to fall over
> if it doesn't see a suitable maxlength attached to query result columns.
> The first example I came across in the archives was
> http://archives.postgresql.org/pgsql-sql/2002-06/msg00235.php
> [ pokes around a bit more ... ]  Hm, I don't see any *recent* examples.
> Maybe all that code has gotten fixed?  Nah ...
>
>> What would actually be really nice is the ability to have
>> parameterized types (like list-of-<some type>,
>> unordered-set-of-<type>, hash-with-keys-of-<some
>> type>-and-values-of-<some type>, function-taking-arguments-of-<various
>> types>-returning-<some type>) which would let us do all kinds of neat
>> things - but I don't see how improving support for typmods gets us any
>> closer to that.
>
> Well, we could possibly implement hacks like the current one for
> anonymous record types.  But SQL isn't intended as a language for
> manipulating arbitrary data types, and I think trying to make it
> do stuff like the above will be an exercise in masochism.

Unfortunately, I kind of agree with you. As much as I'd like to have
this, I wouldn't like it enough to seriously consider working on it at
this point.

> typmod
> is mainly intended for tweaking the properties of base types, and
> it seems fairly useful for that.

"tweaking" certainly describes how we're using it, and perhaps why
it's not worth putting a lot of effort into it. If we're going to do
a lot of work, I'd like to get something better than
slightly-improved-tweaking out of it.

...Robert

>
> So I guess really can't get worked up about the idea of propagating
> this information through the type system.  Even suppose we eventually
> take the steps you suggesting and make it so that varchar(30) ||
> varchar(40) yields varchar(70).  What good is that?

I see main sense in enhancing warning system in plpgsql - or other SQL
PL languages. When you use % reftypes - then there are potential risk
so space (variable, column) isn't well dimensioned.

Pavel

>
> 4.  What about functions whose output typmod should depend on the input
> typmod(s)?  I mentioned earlier the example that concatenation of
> varchar(M) and varchar(N) should produce varchar(M+N).  We could possibly
> punt on this for the time being; supporting only fixed output typmods for
> now doesn't obviously foreclose us from adding support for computed
> typmods later.  However there is still one nasty case that we cannot
> push off till later: given a function that takes and returns a polymorphic
> type such as anyelement, and an actual argument with a typmod (eg
> numeric(2)), is the result numeric(2) or just numeric?  As things stand
> we would have little choice but to say the latter, because we don't know
> what the function might do with the value, and there are too many real
> cases where the result might not have the same typmod.  But there are
> also a lot of cases where you *would* wish that it has the same typmod,
> and this patch raises the stakes for throwing away typmods mid-expression.
> Is this okay, and if not what could we do about it?

polymorphic functions should to ignore typmnod :( - with current
syntax - on output. I don't believe so we are able to find any
mechanism usable for non typmod types and typmod types. We could to
enhance syntax for using typmod from parameters - maybe some flag like
STRICT, maybe TYPMOD?

Regards
Pavel

>
> Unless we have consensus on all of these points I don't think we should
> proceed with the patch.  Comments?
>
>                        regards, tom lane
>

On tis, 2009-11-17 at 17:09 -0500, Tom Lane wrote:
> I can see the following definitional issues:

Should we be able to find the answers to those, or at least a basis of
discussion about those, in the SQL standard? Has anyone checked?

2009/11/18 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On tis, 2009-11-17 at 17:09 -0500, Tom Lane wrote:
>> I can see the following definitional issues:
>
> Should we be able to find the answers to those, or at least a basis of
> discussion about those, in the SQL standard?  Has anyone checked?
>

I am not sure if SQL standard is good inspiration in this case. Does
SQL standard typmod less varchar or numeric? Does SQL standard
polymorphic types?

Maybe only one should be in standard. Reply to question "should exists
functions foo(varchar(3)) and foo(varchar(10)) in same time?

Regards
Pavel

>
>

On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
> I am not sure if SQL standard is good inspiration in this case.

I'm not sure either, but I think it's premature to make a conclusion
about that without having checked at all.

2009/11/18 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
>> I am not sure if SQL standard is good inspiration in this case.
>
> I'm not sure either, but I think it's premature to make a conclusion
> about that without having checked at all.

ok, I recheck SQL/PSM part again :)

Pavel

>
>

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> there is a constituency that cares --- mainly people who use
> client-side code that tends to fall over if it doesn't see a
> suitable maxlength attached to query result columns.

I suspect it will primarily be software which is dealing with large
enough result sets that reading through it all to find the maximum
width for a column in a particular request is not viable. It's also
likely to be more of an issue with software which needs to work with
multiple database products. Finally, it simplifies life for
application developers who want a form or report to have columns which
are wide enough to handle the data which might show up, when they
don't want the column widths to change from run to run -- as when
daily reports will be added to a binder.

> The first example I came across in the archives was
> http://archives.postgresql.org/pgsql-sql/2002-06/msg00235.php

It's not surprising that we got a post about Crystal Reports having an
issue; all of the above applies to it.

-Kevin

Pavel Stehule escribió:
> 2009/11/18 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> > On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
> >> I am not sure if SQL standard is good inspiration in this case.
> >
> > I'm not sure either, but I think it's premature to make a conclusion
> > about that without having checked at all.
>
> ok, I recheck SQL/PSM part again :)

So, did this go anywhere?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2009/12/3 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Pavel Stehule escribió:
>> 2009/11/18 Peter Eisentraut <peter_e(at)gmx(dot)net>:
>> > On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
>> >> I am not sure if SQL standard is good inspiration in this case.
>> >
>> > I'm not sure either, but I think it's premature to make a conclusion
>> > about that without having checked at all.
>>
>> ok, I recheck SQL/PSM part again :)
>
> So, did this go anywhere?
>

I am read a documentation - and I am not sure. It is not part of
SQL/PSM. It is part of Foundation (SQL/Foundation) - see CD
9075-2:200x(E)
11.50 <SQL-invoked routine>. Important is section 18.d.

one parameter from all parameter list have to be unique (types are not
compatible)

Section 9.18 Data type identity speaking what are identity types, but
I am missing definition what are not compatible types.

so I if I respect 18.d, then I have not a functions foo(varchar(30))
and foo(varchar(40)).

But i am not native speaker and SQL standard is cryptographic for me.
Please, can somebody check it again.

Regards
Pavel Stehule

> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>