Thread: Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
"Kevin J Bluck"
Date:
> I'd certainly call this a bug, if not a couple of different bugs.

There seems to be more. I see a problem not just with unqualified char not =
being treated equivalent to char(1). I also see a general problem with RETU=
RNS TABLE disregarding any explicit lengths declared for any character type=
. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it see=
ms to actually consider it as if you had declared it unqualified VARCHAR, e=
quivalent to TEXT. Similarly, a column defined as CHAR(2) will also be trea=
ted as unqualified CHAR without an explicit length, with the further proble=
m reported by Pavel of being treated as unlimited length instead of equival=
ent to CHAR(1) as expected. In other words, it seems to be effectively impo=
ssible to declare explicit lengths for RETURNS TABLE character type columns.

This doesn't happen with the RETURNS SETOF variation, only RETURNS TABLE.

Regards,

--- Kevin

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Pavel Stehule
Date:
2010/4/14 Kevin J Bluck <kevin.bluck@netce.com>:
>> I'd certainly call this a bug, if not a couple of different bugs.
>
> There seems to be more. I see a problem not just with unqualified char no=
t being treated equivalent to char(1). I also see a general problem with RE=
TURNS TABLE disregarding any explicit lengths declared for any character ty=
pe. For example, if you declare a RETURNS TABLE column as VARCHAR(15), it s=
eems to actually consider it as if you had declared it unqualified VARCHAR,=
 equivalent to TEXT. Similarly, a column defined as CHAR(2) will also be tr=
eated as unqualified CHAR without an explicit length, with the further prob=
lem reported by Pavel of being treated as unlimited length instead of equiv=
alent to CHAR(1) as expected. In other words, it seems to be effectively im=
possible to declare explicit lengths for RETURNS TABLE character type colum=
ns.
>
> This doesn't happen with the RETURNS SETOF variation, only RETURNS TABLE.
>
> Regards,

RETURNS TABLE (x int, y int) is equal to CREATE FUNCTION foo(.. OUT x
int, OUT y int) RETURNS SETOF RECORD. But PostgreSQL functions ignore
typmod for parameters - so it is not bug, it is feature :(

Pavel Stehule

>
> --- Kevin
>
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> RETURNS TABLE (x int, y int) is equal to CREATE FUNCTION foo(.. OUT x
> int, OUT y int) RETURNS SETOF RECORD. But PostgreSQL functions ignore
> typmod for parameters - so it is not bug, it is feature :(

Yeah.  Or at least, this is not something that can be changed without a
great deal of work --- and application breakage, more than likely.

You could possibly get the length to be enforced by storing into a local
variable of the record type before returning it.  I think plpgsql does
account for typmod when assigning to locals.

            regards, tom lane

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
"Kevin J Bluck"
Date:
>> PostgreSQL functions ignore typmod for parameters...

> ...not something that can be changed without a great
> deal of work --- and application breakage...

Thanks to all for the clarifications. I can make it return what I wanted wi=
th qualified character types by returning a SETOF RECORD types, where the r=
ecord type is a table or aggregate type. The key, it seems, is to avoid usi=
ng output parameters. But if RETURN TABLE doesn't respect typemods, perhaps=
 it shouldn't be legal to specify them in that clause? I don't know. It jus=
t *seems* like a bug if you don=E2=80=99t know why your carefully crafted r=
eturn type is being partially ignored. But now I do, so thanks again.

I do think Pavel G. has a real bug with the char thing, though.

--- Kevin

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Tom Lane
Date:
"Kevin J Bluck" <kevin.bluck@netce.com> writes:
> But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
> legal to specify them in that clause?

Yeah, possibly.  CREATE FUNCTION has historically accepted (and then
discarded) typmod information for all function parameter and result
types; RETURNS TABLE doesn't seem particularly different from other
cases here.  We could tighten that up, but again it's not clear whether
the probable ensuing application breakage would be worth the reduction
in astonishment quotient.

> I do think Pavel G. has a real bug with the char thing, though.

No, it's exactly the same thing: we're accepting and then throwing away
the typmod.  The fact that it's implicit rather than written out doesn't
change that.

char would be a particularly nasty case if we did reject typmod
specifications for function arguments/results, because there is no
standard syntax for specifying char without a defined max length.
You'd have to fall back to writing "bpchar", which isn't going to
make people happy either...

            regards, tom lane

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Craig Ringer
Date:
On 15/04/2010 8:05 AM, Tom Lane wrote:
> "Kevin J Bluck"<kevin.bluck@netce.com>  writes:
>> But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
>> legal to specify them in that clause?
>
> Yeah, possibly.  CREATE FUNCTION has historically accepted (and then
> discarded) typmod information for all function parameter and result
> types; RETURNS TABLE doesn't seem particularly different from other
> cases here.  We could tighten that up, but again it's not clear whether
> the probable ensuing application breakage would be worth the reduction
> in astonishment quotient.
>
>> I do think Pavel G. has a real bug with the char thing, though.
>
> No, it's exactly the same thing: we're accepting and then throwing away
> the typmod.  The fact that it's implicit rather than written out doesn't
> change that.

If the function `RETURNS SETOF user_composite_type' and
user_composite_type has type length-specifiers, that data appears to be
disregarded there too, so the function can return values of
user_composite_type that violate the constraints of that type. See my
original follow-up on the bug.

That was pretty high on my personal atonishment meter. Using `SELECT ...
INTO' with the function I was able to create a table with data in it
that violated type constraints. IMO that's a bug. If functions
disregarding typmod information is the window that lets that bogus data
into the table, IMO that's a problem.

So - I do think there's a bug here, if _tables_ can be created with
character(1) fields containing three- or four-character strings.

--
Craig Ringer

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Tom Lane
Date:
Craig Ringer <craig@postnewspapers.com.au> writes:
> So - I do think there's a bug here, if _tables_ can be created with
> character(1) fields containing three- or four-character strings.

[ shrug... ]  If you can demonstrate such a behavior, I'd agree it's a
bug, but the examples at hand have nothing to do with what gets stored
into tables.

            regards, tom lane

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Craig Ringer
Date:
On 15/04/2010 10:25 AM, Tom Lane wrote:
> Craig Ringer<craig@postnewspapers.com.au>  writes:
>> So - I do think there's a bug here, if _tables_ can be created with
>> character(1) fields containing three- or four-character strings.
>
> [ shrug... ]  If you can demonstrate such a behavior, I'd agree it's a
> bug, but the examples at hand have nothing to do with what gets stored
> into tables.

regress=> CREATE TYPE testtype AS (id int, salesourcecode character);
CREATE TYPE
regress=> CREATE OR REPLACE FUNCTION test_char_function()
   RETURNS SETOF testtype AS
$BODY$
VALUES (1, 'one'), (2, 'two'), (3, 'three');
$BODY$
   LANGUAGE 'sql';
CREATE FUNCTION
regress=> SELECT * INTO test_tab FROM test_char_function();
SELECT
regress=> select * from test_tab;
  id | salesourcecode
----+----------------
   1 | one
   2 | two
   3 | three
(3 rows)

craig=> \d test_tab
           Table "public.test_tab"
      Column     |     Type     | Modifiers
----------------+--------------+-----------
  id             | integer      |
  salesourcecode | character(1) |

--
Craig Ringer

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Pavel Stehule
Date:
2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:
> "Kevin J Bluck" <kevin.bluck@netce.com> writes:
>> But if RETURN TABLE doesn't respect typemods, perhaps it shouldn't be
>> legal to specify them in that clause?
>
> Yeah, possibly. =C2=A0CREATE FUNCTION has historically accepted (and then
> discarded) typmod information for all function parameter and result
> types; RETURNS TABLE doesn't seem particularly different from other
> cases here. =C2=A0We could tighten that up, but again it's not clear whet=
her
> the probable ensuing application breakage would be worth the reduction
> in astonishment quotient.

I think, so RETURNS TABLE can be modified for returning typmode
without significant problems - this function is called in table
context and I don't see any problematic use case.

Pavel

>
>> I do think Pavel G. has a real bug with the char thing, though.
>
> No, it's exactly the same thing: we're accepting and then throwing away
> the typmod. =C2=A0The fact that it's implicit rather than written out doe=
sn't
> change that.
>
> char would be a particularly nasty case if we did reject typmod
> specifications for function arguments/results, because there is no
> standard syntax for specifying char without a defined max length.
> You'd have to fall back to writing "bpchar", which isn't going to
> make people happy either...
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I think, so RETURNS TABLE can be modified for returning typmode
> without significant problems - this function is called in table
> context and I don't see any problematic use case.

RETURNS TABLE is just a shorthand for some OUT parameters.  I don't
believe it's either easy or a good idea to make it work differently
from every other function-argument-or-result case.

            regards, tom lane

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Pavel Stehule
Date:
2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> I think, so RETURNS TABLE can be modified for returning typmode
>> without significant problems - this function is called in table
>> context and I don't see any problematic use case.
>
> RETURNS TABLE is just a shorthand for some OUT parameters. =C2=A0I don't
> believe it's either easy or a good idea to make it work differently
> from every other function-argument-or-result case.
>

I don't know now. It minimally have to be documented

foodmart=3D# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 121,506 ms
foodmart=3D# select * from f();
   a   |    b
-------+---------
 Pavel | Stehule
(1 row)

Time: 0,718 ms
foodmart=3D# create table x as select * from f();
SELECT
Time: 105,357 ms
foodmart=3D# \d x
            Table "public.x"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | character varying |
 b      | character varying |

workaround is relative simple

foodmart=3D# create function f() returns table (a varchar(10), b
varchar(20)) as $$values('Pavel','Stehule')$$ language sql;
CREATE FUNCTION
Time: 1,009 ms
foodmart=3D# create table x as select a::varchar(20), b::varchar(20)
from (select * from f()) x ;
SELECT
Time: 48,592 ms
foodmart=3D# \d x
              Table "public.x"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 a      | character varying(20) |
 b      | character varying(20) |

Regards
Pavel Stehule


> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Bruce Momjian
Date:
Pavel Stehule wrote:
> 2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:
> > Pavel Stehule <pavel.stehule@gmail.com> writes:
> >> I think, so RETURNS TABLE can be modified for returning typmode
> >> without significant problems - this function is called in table
> >> context and I don't see any problematic use case.
> >
> > RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't
> > believe it's either easy or a good idea to make it work differently
> > from every other function-argument-or-result case.
> >
>
> I don't know now. It minimally have to be documented

Can you suggest some documentation?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

Re: Bug in CREATE FUNCTION with character type (CONFIRMED BUG)

From
Pavel Stehule
Date:
2010/5/31 Bruce Momjian <bruce@momjian.us>:
> Pavel Stehule wrote:
>> 2010/4/15 Tom Lane <tgl@sss.pgh.pa.us>:
>> > Pavel Stehule <pavel.stehule@gmail.com> writes:
>> >> I think, so RETURNS TABLE can be modified for returning typmode
>> >> without significant problems - this function is called in table
>> >> context and I don't see any problematic use case.
>> >
>> > RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't
>> > believe it's either easy or a good idea to make it work differently
>> > from every other function-argument-or-result case.
>> >
>>
>> I don't know now. It minimally have to be documented
>
> Can you suggest some documentation?

some like "typmod in declared parameters are ignored - so returned
table can not be  same as table declared by CREATE STATEMENT."

Pavel

>
> --
> =C2=A0Bruce Momjian =C2=A0<bruce@momjian.us> =C2=A0 =C2=A0 =C2=A0 =C2=A0h=
ttp://momjian.us
> =C2=A0EnterpriseDB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 http://enterprisedb.com
>
> =C2=A0+ None of us is going to be here forever. +
>
>