From: Andy Fan <zhihuifan1213@163.com>
To: Jim Jones <jim.jones@uni-muenster.de>, Postgres hackers
 <pgsql-hackers@lists.postgresql.org>
Subject: Re: New function normal_rand_array function to contrib/tablefunc.
In-Reply-To: <871q46u560.fsf@163.com> (Andy Fan's message of "Sat, 06 Jul 2024
	19:32:39 +0800")
References: <87plssezpc.fsf@163.com>
	<fd1de8c0-467b-4a41-8f7b-0d5122676ecb@uni-muenster.de>
	<871q46u560.fsf@163.com>
Date: Wed, 17 Jul 2024 14:31:22 +0800
Message-ID: <87r0bswmv9.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/87r0bswmv9.fsf%40163.com>
Precedence: bulk

Andy Fan <zhihuifan1213@163.com> writes:


(just noticed this reply is sent to Jim privately, re-sent it to
public.)

> Hi Jim,
>
>>
>> When either minval or maxval exceeds int4 the function cannot be
>> executed/found
>>
>> SELECT * FROM normal_rand_array(5, 10, 8, 42::bigint);
>>
>> ERROR:=C2=A0 function normal_rand_array(integer, integer, integer, bigin=
t)
>> does not exist
>> LINE 1: SELECT * FROM normal_rand_array(5, 10, 8, 42::bigint);
>> =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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^
>> HINT:=C2=A0 No function matches the given name and argument types. You m=
ight
>> need to add explicit type casts.
>> ---
>>
>> SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42);
>>
>> ERROR:=C2=A0 function normal_rand_array(integer, integer, bigint, intege=
r)
>> does not exist
>> LINE 1: SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42);
>> =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=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ^
>> HINT:=C2=A0 No function matches the given name and argument types. You m=
ight
>> need to add explicit type casts.
>> ---
>
>>
>> However, when both are int8 it works fine:
>
> I defined the function as below:
>
> postgres=3D# \df normal_rand_array
>                                         List of functions
>  Schema |       Name        | Result data type |           Argument data =
types            | Type=20
> --------+-------------------+------------------+-------------------------=
-----------------+------
>  public | normal_rand_array | SETOF anyarray   | integer, integer, anyele=
ment, anyelement | func
> (1 row)
>
> so it is required that the 3nd and 4th argument should have the same
> data type, that's why your first 2 test case failed and the third one
> works.  and I also think we should not add a test case / document for
> this since the behavior of 'anyelement' system.
>

This issue can be fixed with the new API defined suggested by Dean.=20

>>
>> SELECT * FROM normal_rand_array(5, 10, 8::bigint, 42::bigint);
>>
>> =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=C2=A0 normal_rand_array=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=C2=A0 =C2=A0
>> --------------------------------------------------
>> =C2=A0{29,38,31,10,23,39,9,32}
>> =C2=A0{8,39,19,31,29,15,17,15,36,20,33,19}
>> =C2=A0{15,18,42,19}
>> =C2=A0{16,31,33,11,14,20,24,9,12,17,22,42,41,24,11,41}
>> =C2=A0{15,11,36,8,28,37}
>> (5 rows)
>> ---
>>
>> Is it the expected behaviour?
>
> Yes, see the above statements.
>
>>
>> In some cases the function returns an empty array. Is it also expected?
>>
>> SELECT count(*)
>> FROM normal_rand_array(100000, 10, 8, 42) i
>> WHERE array_length(i,1) IS NULL;
>>
>> =C2=A0count
>> -------
>> =C2=A0 4533
>> (1 row)
>
> Yes, by design I think it is a feature which could generate [] case
> which should be used a special case for testing, and at the
> implementation side, the [] means the length is 0 which is caused by I
> choose the 'len' by random [0 .. len * 2], so 0 is possible and doesn't
> confict with the declared behavior.=20
>
>> In both cases, perhaps mentioning these behaviors in the docs would
>> avoid some confusion.
>
> hmm, It doesn't take some big effort to add them, but I'm feeling that
> would make the document a bit of too verbose/detailed.
>
> Sorry for the late respone!=20

--=20
Best Regards
Andy Fan