pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Kalcher <martin.kalcher@aboutsource.net>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: John Naylor <john.naylor@enterprisedb.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: [PATCH] Introduce array_shuffle() and array_sample()
Date: Thu, 21 Jul 2022 13:15:43 +0200
Message-ID: <f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net> (raw)
In-Reply-To: <CAEZATCUPcdxeRLmiFvuzZ=kKVpx8kOKTYDPY4oVv3hQBJYfsCQ@mail.gmail.com>
References: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net>
	<5ef15c6f-e108-53af-3d82-587fac394f1a@aboutsource.net>
	<463181.1658097987@sss.pgh.pa.us>
	<5b7a1ee9-f942-2502-3a0c-e9fc12511139@aboutsource.net>
	<469954.1658100033@sss.pgh.pa.us>
	<f36445ec-aad9-0531-39f8-ca3a3bb4860e@aboutsource.net>
	<CAFBsxsG_PK3BkhMcfeLmwDS+FBDZKAaUtfrb6KjU79FZ-3H46A@mail.gmail.com>
	<687490.1658152096@sss.pgh.pa.us>
	<1708d95a-629e-5ac5-deab-d20a8d0b2b0e@aboutsource.net>
	<CA+TgmoaPKwPqhmrk7i1jRjqqt5=JDLeGze5wYPav5ietV4OFbw@mail.gmail.com>
	<809171.1658176490@sss.pgh.pa.us>
	<817328.1658178181@sss.pgh.pa.us>
	<0b5dea2b-37fa-6c81-ea9d-0341ab6f87d6@aboutsource.net>
	<091a9f69-1e5d-6558-41a7-2607a5fc9ff7@aboutsource.net>
	<CA+TgmoYqC42KS7mypjMKxvGY2oPYz=_kvf1zcDNdawXXiVXqQA@mail.gmail.com>
	<1064800.1658240423@sss.pgh.pa.us>
	<7e7cf274-09cb-00cf-1715-979f011e40b0@aboutsource.net>
	<CAEZATCUPcdxeRLmiFvuzZ=kKVpx8kOKTYDPY4oVv3hQBJYfsCQ@mail.gmail.com>

Am 21.07.22 um 10:41 schrieb Dean Rasheed:
> 
> A couple of quick comments on the current patch:

Thank you for your feedback!

> It's important to mark these new functions as VOLATILE, not IMMUTABLE,
> otherwise they won't work as expected in queries. See
> https://www.postgresql.org/docs/current/xfunc-volatility.html

CREATE FUNCTION marks functions as VOLATILE by default if not explicitly 
marked otherwise. I assumed function definitions in pg_proc.dat have the 
same behavior. I will fix that.
https://www.postgresql.org/docs/current/sql-createfunction.html

> It would be better to use pg_prng_uint64_range() rather than rand() to
> pick elements. Partly, that's because it uses a higher quality PRNG,
> with a larger internal state, and it ensures that the results are
> unbiased across the range. But more importantly, it interoperates with
> setseed(), allowing predictable sequences of "random" numbers to be
> generated -- something that's useful in writing repeatable regression
> tests.

I agree that we should use pg_prng_uint64_range(). However, in order to 
achieve interoperability with setseed() we would have to use 
drandom_seed (rather than pg_global_prng_state) as rng state, which is 
declared statically in float.c and exclusively used by random(). Do we 
want to expose drandom_seed to other functions?

> Assuming these new functions are made to interoperate with setseed(),
> which I think they should be, then they also need to be marked as
> PARALLEL RESTRICTED, rather than PARALLEL SAFE. See
> https://www.postgresql.org/docs/current/parallel-safety.html, which
> explains why setseed() and random() are parallel restricted.

As mentioned above, i assumed the default here is PARALLEL UNSAFE. I'll 
fix that.

> In my experience, the requirement for sampling with replacement is
> about as common as the requirement for sampling without replacement,
> so it seems odd to provide one but not the other. Of course, we can
> always add a with-replacement function later, and give it a different
> name. But maybe array_sample() could be used for both, with a
> "with_replacement" boolean parameter?

We can also add a "with_replacement" boolean parameter which is false by 
default to array_sample() later. I do not have a strong opinion about 
that and will implement it, if desired. Any opinions about 
default/no-default?

Martin





view thread (51+ messages)  latest in thread

Message-ID: <f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net>
Permalink:  ../f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net/
Also on:    postgresql.org/message-id/f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: martin.kalcher@aboutsource.net, dean.a.rasheed@gmail.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andrew@dunslane.net, john.naylor@enterprisedb.com, thomas.munro@gmail.com, pgsql-hackers@lists.postgresql.org
  Subject: Re: [PATCH] Introduce array_shuffle() and array_sample()
  In-Reply-To: <f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net>
  References: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net> <5ef15c6f-e108-53af-3d82-587fac394f1a@aboutsource.net> <463181.1658097987@sss.pgh.pa.us> <5b7a1ee9-f942-2502-3a0c-e9fc12511139@aboutsource.net> <469954.1658100033@sss.pgh.pa.us> <f36445ec-aad9-0531-39f8-ca3a3bb4860e@aboutsource.net> <CAFBsxsG_PK3BkhMcfeLmwDS+FBDZKAaUtfrb6KjU79FZ-3H46A@mail.gmail.com> <687490.1658152096@sss.pgh.pa.us> <1708d95a-629e-5ac5-deab-d20a8d0b2b0e@aboutsource.net> <CA+TgmoaPKwPqhmrk7i1jRjqqt5=JDLeGze5wYPav5ietV4OFbw@mail.gmail.com> <809171.1658176490@sss.pgh.pa.us> <817328.1658178181@sss.pgh.pa.us> <0b5dea2b-37fa-6c81-ea9d-0341ab6f87d6@aboutsource.net> <091a9f69-1e5d-6558-41a7-2607a5fc9ff7@aboutsource.net> <CA+TgmoYqC42KS7mypjMKxvGY2oPYz=_kvf1zcDNdawXXiVXqQA@mail.gmail.com> <1064800.1658240423@sss.pgh.pa.us> <7e7cf274-09cb-00cf-1715-979f011e40b0@aboutsource.net> <CAEZATCUPcdxeRLmiFvuzZ=kKVpx8kOKTYDPY4oVv3hQBJYfsCQ@mail.gmail.com> <f6267e99-4448-b47c-9fe8-ec23690b931f@aboutsource.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox