On Tue, Apr 1, 2025 at 1:11 AM Tom Lane wrote:
>
> Junwang Zhao writes:
> > On Mon, Mar 31, 2025 at 5:58 AM Tom Lane wrote:
> >> In v18, it's somewhat annoying that the typcache doesn't cache
> >> the typarray field; we would not need a separate get_array_type()
> >> lookup if it did. I doubt t
On Wed, Apr 2, 2025 at 6:05 AM Tom Lane wrote:
>
> Junwang Zhao writes:
> > On Tue, Apr 1, 2025 at 1:11 AM Tom Lane wrote:
> >> Attached 0001 is the same as v18, and then 0002 is the proposed
> >> addition to typcache.
>
> > I've applied the patches to master and regression passed.
> > 0002 is n
Junwang Zhao writes:
> On Tue, Apr 1, 2025 at 1:11 AM Tom Lane wrote:
>> Attached 0001 is the same as v18, and then 0002 is the proposed
>> addition to typcache.
> I've applied the patches to master and regression passed.
> 0002 is neat, I am +1 for this improvement.
Hearing no further comments
Junwang Zhao writes:
> On Mon, Mar 31, 2025 at 5:58 AM Tom Lane wrote:
>> In v18, it's somewhat annoying that the typcache doesn't cache
>> the typarray field; we would not need a separate get_array_type()
>> lookup if it did. I doubt there is any real reason for that except
>> that pg_type.typa
Hi Tom,
On Mon, Mar 31, 2025 at 5:58 AM Tom Lane wrote:
>
> I spent some time looking at the v17 patchset. There were some pretty
> strange things in it --- why were some of the variants of array_sort()
> marked as volatile, for example?
I think this was due to some copy-paste of the code nearb
I spent some time looking at the v17 patchset. There were some pretty
strange things in it --- why were some of the variants of array_sort()
marked as volatile, for example? But the two things I'd like to
suggest functionality-wise are:
* The second argument of the variants with booleans should
On Sun, Mar 16, 2025 at 9:50 PM jian he wrote:
>
> hi.
>
> before commit 4618045bee4a6d3efcb489c319649d8dd9aaa738 ([0])
It seems that patch is treating int2vector and oidvector as scalar
types.
>
> select array_sort(array(select '1 4'::int2vector union all select '1
> 2'::int2vector));
>
best to include such a test case,
so I've attached a patch.
[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=4618045bee4a6d3efcb489c319649d8dd9aaa738
From 9ab98e6470dd5f16ef6024c291e5aa99ebfbecde Mon Sep 17 00:00:00 2001
From: jian he
Date: Sun, 16 Mar 2025 21:31:55 +0800
Sub
hi.
patch rebased, also did some minor comments tweak.
From c9398dfe889f23dce147db1719aa9fe4dfaa3adc Mon Sep 17 00:00:00 2001
From: jian he
Date: Sun, 9 Mar 2025 20:45:20 +0800
Subject: [PATCH v16 1/2] general purpose array_sort
Introduce the SQL-callable function array_sort(anyarray). The
func.sgml, some lines are too long, adjust it to several lines.
From be56e368bb00f07e25b5d73a97816baceb46a92a Mon Sep 17 00:00:00 2001
From: jian he
Date: Sun, 22 Dec 2024 12:23:14 +0800
Subject: [PATCH v15 1/2] general purpose array_sort
Introduce the SQL-callable function array_sort(anyarray). The
On Fri, Nov 8, 2024 at 8:52 AM Michael Paquier wrote:
>
> I am wondering if there are more fancy cases where the saved cache
> could force a state that would lead to puzzling results, say with
> different collations that should be applied. I'd recommend to
> research that more, to reflect that i
Hi Michael,
On Fri, Nov 8, 2024 at 8:52 AM Michael Paquier wrote:
>
> On Thu, Nov 07, 2024 at 10:06:04PM +0800, Junwang Zhao wrote:
> > I'm afraid this can not be achieved in my current implementation, a simple
> > case is:
> >
> > SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[]);
> > {Abc
On Thu, Nov 07, 2024 at 10:06:04PM +0800, Junwang Zhao wrote:
> I'm afraid this can not be achieved in my current implementation, a simple
> case is:
>
> SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[]);
> {Abc,bar,bbc,CCC,foo,NULL}
> SELECT array_sort('{foo,bar,null,CCC,Abc,bbc}'::text[] C
On Thu, Nov 07, 2024 at 09:29:05AM -0500, Robert Haas wrote:
> Yes, I think that case needs to error out. It seems best to identify
> the ordering operator before you decide whether or not you have >1
> element.
+1.
--
Michael
signature.asc
Description: PGP signature
On Tue, Nov 5, 2024 at 9:13 AM Michael Paquier wrote:
>
> On Mon, Nov 04, 2024 at 03:16:35PM +0800, jian he wrote:
> > drop table if exists t;
> > CREATE TABLE t (a int[]);
> > insert into t values ('{1,3}'),('{1,2,3}'),('{11}');
> > insert into t values ('{{1,12}}'), ('{{4,3}}');
> > SELECT array
On Thu, Nov 7, 2024 at 8:56 AM Junwang Zhao wrote:
> Yeah, this is reasonable but one case I can't be sure:
>
> SELECT array_sort('{{2,3,4}}'::xid[]);
>
> This will return the array as is, but xid doesn't have a LT_OPR, should
> I error out in this case? like:
>
> could not identify ordering opera
On Thu, Nov 7, 2024 at 10:29 PM Robert Haas wrote:
>
> On Thu, Nov 7, 2024 at 8:56 AM Junwang Zhao wrote:
> > Yeah, this is reasonable but one case I can't be sure:
> >
> > SELECT array_sort('{{2,3,4}}'::xid[]);
> >
> > This will return the array as is, but xid doesn't have a LT_OPR, should
> > I
Hi jian,
On Tue, Nov 5, 2024 at 3:13 PM jian he wrote:
>
> On Mon, Nov 4, 2024 at 7:34 PM Dean Rasheed wrote:
> >
> > Testing this with an array with non-default lower bounds, it fails to
> > preserve the array bounds, which I think it should (note:
> > array_reverse() and array_shuffle() do pre
Hi Michael,
On Mon, Nov 4, 2024 at 1:46 PM Michael Paquier wrote:
>
> On Sun, Nov 03, 2024 at 11:33:05AM +0800, Junwang Zhao wrote:
> > Rebase needed due to array_reverse committed, PFA v11.
>
> There has been another conflict since you have posted this version
> (noticed that after my business i
On Tue, Nov 5, 2024 at 8:30 PM Junwang Zhao wrote:
>
>
> Thanks for the bounds preserve solution, I just looked at 0002,
>
> + if (astate->arraystate != NULL)
> + {
> + memcpy(astate->arraystate->dims, dims, ndim * sizeof(int));
> + memcpy(astate->arraystate->lbs, lbs, ndim * sizeof(int));
> + Ass
Hi jian,
On Tue, Nov 5, 2024 at 3:13 PM jian he wrote:
>
> On Mon, Nov 4, 2024 at 7:34 PM Dean Rasheed wrote:
> >
> > Testing this with an array with non-default lower bounds, it fails to
> > preserve the array bounds, which I think it should (note:
> > array_reverse() and array_shuffle() do pre
{1,2,-123}},
+{{10,-20,1},{11,123,3}},
+{{10,-20,1},{11,-123,-9}},
+{{1,2,-11},{1,2,211}}}'::int[]);
+SELECT array_sort(t.a) from t;
+SELECT array_sort((t.a) [-13:-10][0:1][21:22]) from t where array_ndims(a) = 3;
+SELECT array_sort((t.a) [-13:-11][0:1][21:22]) from t where array_nd
On Mon, Nov 04, 2024 at 03:16:35PM +0800, jian he wrote:
> drop table if exists t;
> CREATE TABLE t (a int[]);
> insert into t values ('{1,3}'),('{1,2,3}'),('{11}');
> insert into t values ('{{1,12}}'), ('{{4,3}}');
> SELECT array_sort(a) from t;
>
> In the above case,
> tuplesort_begin_datum need
On Sun, 3 Nov 2024 at 03:33, Junwang Zhao wrote:
>
> PFA v11.
>
Testing this with an array with non-default lower bounds, it fails to
preserve the array bounds, which I think it should (note:
array_reverse() and array_shuffle() do preserve the bounds):
SELECT array_reverse(a), array_shuffle(a),
On Mon, Nov 4, 2024 at 1:46 PM Michael Paquier wrote:
>
>
> +typentry = lookup_type_cache(elmtyp, TYPECACHE_LT_OPR);
> +if (!OidIsValid(typentry->lt_opr))
> +ereport(ERROR,
> +(errcode(ERRCODE_UNDEFINED_FUNCTION),
> + errmsg("could not identify order
On Sun, Nov 03, 2024 at 11:33:05AM +0800, Junwang Zhao wrote:
> Rebase needed due to array_reverse committed, PFA v11.
There has been another conflict since you have posted this version
(noticed that after my business in 027124a872d7). I have looked at
0001.
+if (ARR_NDIM(array) < 1)
+
On Wed, Oct 30, 2024 at 10:41 PM Junwang Zhao wrote:
>
> On Wed, Oct 30, 2024 at 10:17 PM Junwang Zhao wrote:
> >
> > Hi,
> >
> > On Wed, Oct 30, 2024 at 9:29 PM Aleksander Alekseev
> > wrote:
> > >
> > > Hi,
> > >
> > > Thanks for the updated patch set.
> > >
> > > > > > +Datum
> > > > > > +arr
On Wed, Oct 30, 2024 at 10:17 PM Junwang Zhao wrote:
>
> Hi,
>
> On Wed, Oct 30, 2024 at 9:29 PM Aleksander Alekseev
> wrote:
> >
> > Hi,
> >
> > Thanks for the updated patch set.
> >
> > > > > +Datum
> > > > > +array_sort_order(PG_FUNCTION_ARGS)
> > > > > +{
> > > > > +return array_sort(fcin
Hi,
On Wed, Oct 30, 2024 at 9:29 PM Aleksander Alekseev
wrote:
>
> Hi,
>
> Thanks for the updated patch set.
>
> > > > +Datum
> > > > +array_sort_order(PG_FUNCTION_ARGS)
> > > > +{
> > > > +return array_sort(fcinfo);
> > > > +}
> > > > +
> > > > +Datum
> > > > +array_sort_order_nulls_first(PG
Hi,
Thanks for the updated patch set.
> > > +Datum
> > > +array_sort_order(PG_FUNCTION_ARGS)
> > > +{
> > > +return array_sort(fcinfo);
> > > +}
> > > +
> > > +Datum
> > > +array_sort_order_nulls_first(PG_FUNCTION_ARGS)
> > > +{
> > > +return array_sort(fcinfo);
> > > +}
> >
> > Any reaso
Hi Aleksander,
On Tue, Oct 29, 2024 at 12:48 AM Aleksander Alekseev
wrote:
>
> Hi,
>
> > Based on the previous discussion, I split it into two patches in V8.
> >
> > 0001 is the general sort part without `is_ascending` or `nulls_first`,
> > the sort order is determined by the "<" operator of the
Hi Jian,
> IMMUTABLE indicates that the function cannot modify the database and always
> returns the same result when given the same argument values; that is, it does
> not do database lookups or otherwise use information not directly present in
> its
> argument list. If this option is given, any
On Tue, Oct 29, 2024 at 12:48 AM Aleksander Alekseev
wrote:.
>
> 0001:
>
> > +{ oid => '8810', descr => 'sort array',
> > + proname => 'array_sort', provolatile => 'v', prorettype => 'anyarray',
> > + proargtypes => 'anyarray', prosrc => 'array_sort'},
>
> I would expect that array_sort() should
Hi,
> Based on the previous discussion, I split it into two patches in V8.
>
> 0001 is the general sort part without `is_ascending` or `nulls_first`,
> the sort order is determined by the "<" operator of the element type.
> It also cached the type entry of both eletyp and the corresponding
> array
On Fri, Oct 25, 2024 at 8:02 PM Junwang Zhao wrote:
>
> On Fri, Oct 25, 2024 at 1:19 AM Aleksander Alekseev
> wrote:
> >
> > Hi,
> >
> > > I can accept this outcome though an optional three-valued boolean sort
> > > order (ascending and descending only) I'd argue is worth keeping. null
> > > v
On Fri, Oct 25, 2024 at 1:19 AM Aleksander Alekseev
wrote:
>
> Hi,
>
> > I can accept this outcome though an optional three-valued boolean sort
> > order (ascending and descending only) I'd argue is worth keeping. null
> > value placement too I guess, three-valued boolean (nulls_first).
>
> Per
On Thu, Oct 24, 2024 at 8:27 AM Aleksander Alekseev <
aleksan...@timescale.com> wrote:
>
> Just making an observation / thinking out loud that the requirement to
> support everything ORDER BY handles / supports (and will handle /
> support?) might make this function impractical to maintain.
>
> Pa
On Thu, Oct 24, 2024 at 8:40 PM jian he wrote:
>
> On Wed, Oct 23, 2024 at 10:28 PM Junwang Zhao wrote:
> > PFA v7 with multi-array support.
> >
>
> if (ARR_NDIM(array) == 1)
> {
> }
> else
> {
> }
> can be simplified.
> i think beginning part of array_sort can be like the following:
> (newline e
On Thu, Oct 24, 2024 at 9:06 AM Tom Lane wrote:
> This business with a textual representation of a sort clause seems like
> over-engineering ... except that it's also under-engineered, because
> the parsing is lame and incomplete. (No USING option, and the fact
> that collation comes from somewh
On Wed, Oct 23, 2024 at 10:28 PM Junwang Zhao wrote:
> PFA v7 with multi-array support.
>
if (ARR_NDIM(array) == 1)
{
}
else
{
}
can be simplified.
i think beginning part of array_sort can be like the following:
(newline emitted)
--
On Thu, Oct 24, 2024 at 7:58 AM Aleksander Alekseev <
aleksan...@timescale.com> wrote:
> Hi,
>
> > It's hardly "general purpose" if it randomly refuses to
> > sort certain types. I would say it should be able to sort
> > anything that ORDER BY will handle --- and that certainly
> > includes the c
Hi,
> I can accept this outcome though an optional three-valued boolean sort order
> (ascending and descending only) I'd argue is worth keeping. null value
> placement too I guess, three-valued boolean (nulls_first).
Perhaps these optional arguments deserve separate discussions. I
suggest merg
"David G. Johnston" writes:
> Composing function calls here seems quite undesirable from a performance
> standpoint, but maybe I over-estimate the cost of
> exploding-manipulating-freezing an array datum. Also, while I'm not in a
> good position to judge the challenge of implementing the sort par
Hi David,
>> > It's hardly "general purpose" if it randomly refuses to
>> > sort certain types. I would say it should be able to sort
>> > anything that ORDER BY will handle --- and that certainly
>> > includes the cases shown here.
>>
>> I wonder how useful / convenient the new function will be
Hi,
> It's hardly "general purpose" if it randomly refuses to
> sort certain types. I would say it should be able to sort
> anything that ORDER BY will handle --- and that certainly
> includes the cases shown here.
I wonder how useful / convenient the new function will be considering
that we alr
Sorry for the late reply.
On Mon, Oct 14, 2024 at 4:10 AM Tom Lane wrote:
>
> Junwang Zhao writes:
> > It seems there is not much meaning to sort composite type,
> > so are you proposing we should error on that?
>
> It's hardly "general purpose" if it randomly refuses to
> sort certain types. I
Junwang Zhao writes:
> It seems there is not much meaning to sort composite type,
> so are you proposing we should error on that?
It's hardly "general purpose" if it randomly refuses to
sort certain types. I would say it should be able to sort
anything that ORDER BY will handle --- and that cert
Hi Jian,
On Fri, Oct 11, 2024 at 1:12 PM jian he wrote:
>
> tricky case:
> should we allow array element type to be composite/domain?
> currently seems to work fine.
>
>
> create table t(b int[]);
> insert into t values ('{{1,3}}'), ('{{1,2}}');
>
> select array_sort((select array_agg(t) from t)
tricky case:
should we allow array element type to be composite/domain?
currently seems to work fine.
create table t(b int[]);
insert into t values ('{{1,3}}'), ('{{1,2}}');
select array_sort((select array_agg(t) from t), 'desc');
array_sort
---
{"(\
On Wed, Oct 9, 2024 at 11:46 PM Junwang Zhao wrote:
>
> On Wed, Oct 9, 2024 at 10:10 PM Junwang Zhao wrote:
> >
> > Hi Amit,
> >
> > On Thu, Oct 3, 2024 at 2:22 PM Amit Langote wrote:
> > >
> > > Hi Junwang,
> > >
> > > On Wed, Oct 2, 2024 at 11:46 PM Junwang Zhao wrote:
> > > > On Wed, Oct 2,
On Wed, Oct 9, 2024 at 10:10 PM Junwang Zhao wrote:
>
> Hi Amit,
>
> On Thu, Oct 3, 2024 at 2:22 PM Amit Langote wrote:
> >
> > Hi Junwang,
> >
> > On Wed, Oct 2, 2024 at 11:46 PM Junwang Zhao wrote:
> > > On Wed, Oct 2, 2024 at 9:51 AM jian he
> > > wrote:
> > > >
> > > > > >
> > > > > > + ty
Hi Amit,
On Thu, Oct 3, 2024 at 2:22 PM Amit Langote wrote:
>
> Hi Junwang,
>
> On Wed, Oct 2, 2024 at 11:46 PM Junwang Zhao wrote:
> > On Wed, Oct 2, 2024 at 9:51 AM jian he wrote:
> > >
> > > > >
> > > > > + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> > > > > + if (typentry == N
Hi Junwang,
On Wed, Oct 2, 2024 at 11:46 PM Junwang Zhao wrote:
> On Wed, Oct 2, 2024 at 9:51 AM jian he wrote:
> >
> > > >
> > > > + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> > > > + if (typentry == NULL || typentry->type_id != elmtyp)
> > > > + {
> > > > + typentry = lookup_typ
On Wed, Oct 2, 2024 at 9:51 AM jian he wrote:
>
> > >
> > > + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> > > + if (typentry == NULL || typentry->type_id != elmtyp)
> > > + {
> > > + typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
> > > TYPECACHE_GT_OPR);
> > > + f
> >
> > + typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
> > + if (typentry == NULL || typentry->type_id != elmtyp)
> > + {
> > + typentry = lookup_type_cache(elmtyp, sort_asc ? TYPECACHE_LT_OPR :
> > TYPECACHE_GT_OPR);
> > + fcinfo->flinfo->fn_extra = (void *) typentry;
> > + }
> > you ne
Hi Jian,
On Mon, Sep 30, 2024 at 11:13 PM jian he wrote:
>
> On Mon, Sep 30, 2024 at 1:01 PM Junwang Zhao wrote:
> >
> > > I would suggest accepting:
> > > asc
> > > desc
> > > asc nulls first
> > > asc nulls last *
> > > desc nulls first *
> > > desc nulls last
> > >
> > > As valid inputs for "
On Mon, Sep 30, 2024 at 1:01 PM Junwang Zhao wrote:
>
> > I would suggest accepting:
> > asc
> > desc
> > asc nulls first
> > asc nulls last *
> > desc nulls first *
> > desc nulls last
> >
> > As valid inputs for "dir" - and that the starred options are the defaults
> > when null position is omi
On Sun, Sep 29, 2024 at 10:51 AM David G. Johnston
wrote:
>
> On Sat, Sep 28, 2024 at 7:05 PM Junwang Zhao wrote:
>>
>> On Sat, Sep 28, 2024 at 10:41 PM jian he wrote:
>> >
>> > dir can have only two potential values, make it
>> > as a boolean would be more easier?
>> > you didn't mention inform
On Sat, Sep 28, 2024 at 7:05 PM Junwang Zhao wrote:
> On Sat, Sep 28, 2024 at 10:41 PM jian he
> wrote:
> >
> > dir can have only two potential values, make it
> > as a boolean would be more easier?
> > you didn't mention information: "by default, it will sort by
> > ascending order; the sort c
On Sat, Sep 28, 2024 at 10:41 PM jian he wrote:
>
> On Sat, Sep 28, 2024 at 7:52 PM Junwang Zhao wrote:
> >
> > PFA v2, use COLLATE keyword to supply the collation suggested by
> > Andreas offlist.
> >
> this is better. otherwise we need extra care to handle case like:
> SELECT array_sort('{1,3,5
On Sat, Sep 28, 2024 at 7:52 PM Junwang Zhao wrote:
>
> PFA v2, use COLLATE keyword to supply the collation suggested by
> Andreas offlist.
>
this is better. otherwise we need extra care to handle case like:
SELECT array_sort('{1,3,5,2,4,6}'::int[] COLLATE "pg_c_utf8");
+
+
+
On Fri, Sep 27, 2024 at 9:15 PM Junwang Zhao wrote:
>
> Hi hackers,
>
> per David's suggestion, this patch implements general
> purpose array sort.
>
> We can do the following with this patch:
>
> SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
> SELECT array_sort('{abc DEF 123abc
Hi hackers,
per David's suggestion, this patch implements general
purpose array sort.
We can do the following with this patch:
SELECT array_sort('{1.1,3.3,5.5,2.2,4.4,6.6}'::float8[], 'asc');
SELECT array_sort('{abc DEF 123abc,ábc sßs ßss DÉF,DŽxxDŽ džxxDž
Džxxdž,ȺȺȺ,ⱥⱥⱥ,ⱥȺ}'::text[]);
SELECT array_so
63 matches
Mail list logo
