Horton

Thread

Commits

  1. Add bit_xor aggregate function

  1. [patch] bit XOR aggregate functions

    Alexey Bashtanov <bashtanov@imap.cc> — 2021-02-09T15:25:19Z

    Hi,

I personally use it as a checksum for a large unordered set, where 
performance and simplicity is prioritized over collision resilience.
Maybe there are other ways to use them.

Best, Alex

  2. Re: [patch] bit XOR aggregate functions

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-02-10T05:42:04Z

    At Tue, 9 Feb 2021 15:25:19 +0000, Alexey Bashtanov <bashtanov@imap.cc> wrote in 
> I personally use it as a checksum for a large unordered set, where
> performance and simplicity is prioritized over collision resilience.
> Maybe there are other ways to use them.

FWIW the BIT_XOR can be created using CREATE AGGREGATE.

CREATE OR REPLACE AGGREGATE BIT_XOR(IN v smallint) (SFUNC = int2xor, STYPE = smallint);
CREATE OR REPLACE AGGREGATE BIT_XOR(IN v int4) (SFUNC = int4xor, STYPE = int4);
CREATE OR REPLACE AGGREGATE BIT_XOR(IN v bigint) (SFUNC = int8xor, STYPE = bigint);
CREATE OR REPLACE AGGREGATE BIT_XOR(IN v bit) (SFUNC = bitxor, STYPE = bit);

The bit_and/bit_or aggregates are back to 2004, that commit says that:

> commit 8096fe45cee42ce02e602cbea08e969139a77455
> Author: Bruce Momjian <bruce@momjian.us>
> Date:   Wed May 26 15:26:28 2004 +0000
...
>    (2) bitwise integer aggregates named bit_and and bit_or for
>        int2, int4, int8 and bit types. They are not standard, but I find
>        them useful. I needed them once.

We already had CREATE AGGREATE at the time, so BIT_XOR can be thought
as it falls into the same category with BIT_AND and BIT_OR, that is,
we may have BIT_XOR as an intrinsic aggregation?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  3. Re: [patch] bit XOR aggregate functions

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-03-03T14:30:15Z

    On 10.02.21 06:42, Kyotaro Horiguchi wrote:
> We already had CREATE AGGREATE at the time, so BIT_XOR can be thought
> as it falls into the same category with BIT_AND and BIT_OR, that is,
> we may have BIT_XOR as an intrinsic aggregation?

I think the use of BIT_XOR is quite separate from BIT_AND and BIT_OR. 
The latter give you an "all" or "any" result of the bits set.  BIT_XOR 
will return 1 or true if an odd number of inputs are 1 or true, which 
isn't useful by itself.  But it can be used as a checksum, so it seems 
pretty reasonable to me to add it.  Perhaps the use case could be 
pointed out in the documentation.

  4. Re: [patch] bit XOR aggregate functions

    Ibrar Ahmed <ibrar.ahmad@gmail.com> — 2021-03-04T17:14:26Z

    On Wed, Mar 3, 2021 at 7:30 PM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
> > We already had CREATE AGGREATE at the time, so BIT_XOR can be thought
> > as it falls into the same category with BIT_AND and BIT_OR, that is,
> > we may have BIT_XOR as an intrinsic aggregation?
>
> I think the use of BIT_XOR is quite separate from BIT_AND and BIT_OR.
> The latter give you an "all" or "any" result of the bits set.  BIT_XOR
> will return 1 or true if an odd number of inputs are 1 or true, which
> isn't useful by itself.  But it can be used as a checksum, so it seems
> pretty reasonable to me to add it.  Perhaps the use case could be
> pointed out in the documentation.
>
>
>
>
Hi Alex,


The patch is failing just because of a comment, which is already changed by
another patch

-/* Define to build with OpenSSL support. (--with-ssl=openssl) */

+/* Define to 1 if you have OpenSSL support. */

Do you mind sending an updated patch?

http://cfbot.cputube.org/patch_32_2980.log.

I am changing the status to "Waiting for Author"


In my opinion that change no more requires so I removed that and attached
the patch.

-- 
Ibrar Ahmed

  5. Re: [patch] bit XOR aggregate functions

    Alexey Bashtanov <bashtanov@imap.cc> — 2021-03-05T12:42:55Z

    Hi all,

Thanks for your reviews.
I've updated my patch to the current master and added a documentation 
line suggesting using the new function as a checksum.

Best regards, Alex

On 04/03/2021 17:14, Ibrar Ahmed wrote:
>
>
> On Wed, Mar 3, 2021 at 7:30 PM Peter Eisentraut 
> <peter.eisentraut@enterprisedb.com 
> <mailto:peter.eisentraut@enterprisedb.com>> wrote:
>
>     On 10.02.21 06:42, Kyotaro Horiguchi wrote:
>     > We already had CREATE AGGREATE at the time, so BIT_XOR can be
>     thought
>     > as it falls into the same category with BIT_AND and BIT_OR, that is,
>     > we may have BIT_XOR as an intrinsic aggregation?
>
>     I think the use of BIT_XOR is quite separate from BIT_AND and BIT_OR.
>     The latter give you an "all" or "any" result of the bits set. 
>     BIT_XOR
>     will return 1 or true if an odd number of inputs are 1 or true, which
>     isn't useful by itself.  But it can be used as a checksum, so it
>     seems
>     pretty reasonable to me to add it.  Perhaps the use case could be
>     pointed out in the documentation.
>
>
>
>
> Hi Alex,
>
> The patch is failing just because of a comment, which is already 
> changed by another patch
>
> -/* Define to build with OpenSSL support. (--with-ssl=openssl) */
>
> +/* Define to 1 if you have OpenSSL support. */
>
>
> Do you mind sending an updated patch?
>
> http://cfbot.cputube.org/patch_32_2980.log.
>
> I am changing the status to "Waiting for Author"
>
>
> In my opinion that change no more requires so I removed that and 
> attached the patch.
>
> -- 
> Ibrar Ahmed

  6. Re: [patch] bit XOR aggregate functions

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-03-06T18:37:13Z

    On 05.03.21 13:42, Alexey Bashtanov wrote:
> Thanks for your reviews.
> I've updated my patch to the current master and added a documentation 
> line suggesting using the new function as a checksum.

committed

  7. Re: [patch] bit XOR aggregate functions

    David Fetter <david@fetter.org> — 2021-03-06T19:55:54Z

    On Wed, Mar 03, 2021 at 03:30:15PM +0100, Peter Eisentraut wrote:
> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
> > We already had CREATE AGGREATE at the time, so BIT_XOR can be
> > thought as it falls into the same category with BIT_AND and
> > BIT_OR, that is, we may have BIT_XOR as an intrinsic aggregation?
> 
> I think the use of BIT_XOR is quite separate from BIT_AND and
> BIT_OR. The latter give you an "all" or "any" result of the bits
> set.  BIT_XOR will return 1 or true if an odd number of inputs are 1
> or true, which isn't useful by itself.  But it can be used as a
> checksum, so it seems pretty reasonable to me to add it.  Perhaps
> the use case could be pointed out in the documentation.

If this is the only use case, is there some way to refuse to execute
it if it doesn't contain an unambiguous ORDER BY, as illustrated
below?

    SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
    SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
    SELECT BIT_XOR(b) FROM...                 /* errors out */

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  8. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-06T19:57:46Z

    On 3/6/21 8:55 PM, David Fetter wrote:
> On Wed, Mar 03, 2021 at 03:30:15PM +0100, Peter Eisentraut wrote:
>> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
>>> We already had CREATE AGGREATE at the time, so BIT_XOR can be
>>> thought as it falls into the same category with BIT_AND and
>>> BIT_OR, that is, we may have BIT_XOR as an intrinsic aggregation?
>>
>> I think the use of BIT_XOR is quite separate from BIT_AND and
>> BIT_OR. The latter give you an "all" or "any" result of the bits
>> set.  BIT_XOR will return 1 or true if an odd number of inputs are 1
>> or true, which isn't useful by itself.  But it can be used as a
>> checksum, so it seems pretty reasonable to me to add it.  Perhaps
>> the use case could be pointed out in the documentation.
> 
> If this is the only use case, is there some way to refuse to execute
> it if it doesn't contain an unambiguous ORDER BY, as illustrated
> below?
> 
>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
>     SELECT BIT_XOR(b) FROM...                 /* errors out */


Why would such an error be necessary, or even desirable?
-- 
Vik Fearing

  9. Re: [patch] bit XOR aggregate functions

    David Fetter <david@fetter.org> — 2021-03-06T20:00:24Z

    On Sat, Mar 06, 2021 at 08:57:46PM +0100, Vik Fearing wrote:
> On 3/6/21 8:55 PM, David Fetter wrote:
> > On Wed, Mar 03, 2021 at 03:30:15PM +0100, Peter Eisentraut wrote:
> >> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
> >>> We already had CREATE AGGREATE at the time, so BIT_XOR can be
> >>> thought as it falls into the same category with BIT_AND and
> >>> BIT_OR, that is, we may have BIT_XOR as an intrinsic aggregation?
> >>
> >> I think the use of BIT_XOR is quite separate from BIT_AND and
> >> BIT_OR. The latter give you an "all" or "any" result of the bits
> >> set.  BIT_XOR will return 1 or true if an odd number of inputs are 1
> >> or true, which isn't useful by itself.  But it can be used as a
> >> checksum, so it seems pretty reasonable to me to add it.  Perhaps
> >> the use case could be pointed out in the documentation.
> > 
> > If this is the only use case, is there some way to refuse to execute
> > it if it doesn't contain an unambiguous ORDER BY, as illustrated
> > below?
> > 
> >     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
> >     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
> >     SELECT BIT_XOR(b) FROM...                 /* errors out */
> 
> 
> Why would such an error be necessary, or even desirable?

Because there is no way to ensure that the results remain consistent
from one execution to the next without such a guarantee.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  10. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-06T20:03:25Z

    On 3/6/21 9:00 PM, David Fetter wrote:
> On Sat, Mar 06, 2021 at 08:57:46PM +0100, Vik Fearing wrote:
>> On 3/6/21 8:55 PM, David Fetter wrote:
>>> On Wed, Mar 03, 2021 at 03:30:15PM +0100, Peter Eisentraut wrote:
>>>> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
>>>>> We already had CREATE AGGREATE at the time, so BIT_XOR can be
>>>>> thought as it falls into the same category with BIT_AND and
>>>>> BIT_OR, that is, we may have BIT_XOR as an intrinsic aggregation?
>>>>
>>>> I think the use of BIT_XOR is quite separate from BIT_AND and
>>>> BIT_OR. The latter give you an "all" or "any" result of the bits
>>>> set.  BIT_XOR will return 1 or true if an odd number of inputs are 1
>>>> or true, which isn't useful by itself.  But it can be used as a
>>>> checksum, so it seems pretty reasonable to me to add it.  Perhaps
>>>> the use case could be pointed out in the documentation.
>>>
>>> If this is the only use case, is there some way to refuse to execute
>>> it if it doesn't contain an unambiguous ORDER BY, as illustrated
>>> below?
>>>
>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
>>
>>
>> Why would such an error be necessary, or even desirable?
> 
> Because there is no way to ensure that the results remain consistent
> from one execution to the next without such a guarantee.

I think one of us is forgetting how XOR works.
-- 
Vik Fearing

  11. Re: [patch] bit XOR aggregate functions

    David Fetter <david@fetter.org> — 2021-03-06T20:05:44Z

    On Sat, Mar 06, 2021 at 09:03:25PM +0100, Vik Fearing wrote:
> On 3/6/21 9:00 PM, David Fetter wrote:
> > On Sat, Mar 06, 2021 at 08:57:46PM +0100, Vik Fearing wrote:
> >> On 3/6/21 8:55 PM, David Fetter wrote:
> >>> On Wed, Mar 03, 2021 at 03:30:15PM +0100, Peter Eisentraut wrote:
> >>>> On 10.02.21 06:42, Kyotaro Horiguchi wrote:
> >>>>> We already had CREATE AGGREATE at the time, so BIT_XOR can be
> >>>>> thought as it falls into the same category with BIT_AND and
> >>>>> BIT_OR, that is, we may have BIT_XOR as an intrinsic aggregation?
> >>>>
> >>>> I think the use of BIT_XOR is quite separate from BIT_AND and
> >>>> BIT_OR. The latter give you an "all" or "any" result of the bits
> >>>> set.  BIT_XOR will return 1 or true if an odd number of inputs are 1
> >>>> or true, which isn't useful by itself.  But it can be used as a
> >>>> checksum, so it seems pretty reasonable to me to add it.  Perhaps
> >>>> the use case could be pointed out in the documentation.
> >>>
> >>> If this is the only use case, is there some way to refuse to execute
> >>> it if it doesn't contain an unambiguous ORDER BY, as illustrated
> >>> below?
> >>>
> >>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
> >>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
> >>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
> >>
> >>
> >> Why would such an error be necessary, or even desirable?
> > 
> > Because there is no way to ensure that the results remain consistent
> > from one execution to the next without such a guarantee.
> 
> I think one of us is forgetting how XOR works.

Oops. You're right.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  12. Re: [patch] bit XOR aggregate functions

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-06T20:06:26Z

    On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:

>
> > >     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
> > >     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
> > >     SELECT BIT_XOR(b) FROM...                 /* errors out */
> >
> >
> > Why would such an error be necessary, or even desirable?
>
> Because there is no way to ensure that the results remain consistent
> from one execution to the next without such a guarantee.
>

Numerous existing aggregate functions have this behavior.  Making those
error isn’t an option.  So is making this a special case something we want
to do (and also maybe make doing so the rule going forward)?

David J.

  13. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-07T09:36:35Z

    On 3/6/21 9:06 PM, David G. Johnston wrote:
> On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:
> 
>>
>>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
>>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
>>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
>>>
>>>
>>> Why would such an error be necessary, or even desirable?
>>
>> Because there is no way to ensure that the results remain consistent
>> from one execution to the next without such a guarantee.
>>
> 
> Numerous existing aggregate functions have this behavior.  Making those
> error isn’t an option.  So is making this a special case something we want
> to do (and also maybe make doing so the rule going forward)?

Aside from the fact that bit_xor() does not need this, I am opposed to
it in general.  It is not our job to make people write correct queries.
-- 
Vik Fearing

  14. Re: [patch] bit XOR aggregate functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-07T09:53:46Z

    ne 7. 3. 2021 v 10:36 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

> On 3/6/21 9:06 PM, David G. Johnston wrote:
> > On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:
> >
> >>
> >>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
> >>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
> >>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
> >>>
> >>>
> >>> Why would such an error be necessary, or even desirable?
> >>
> >> Because there is no way to ensure that the results remain consistent
> >> from one execution to the next without such a guarantee.
> >>
> >
> > Numerous existing aggregate functions have this behavior.  Making those
> > error isn’t an option.  So is making this a special case something we
> want
> > to do (and also maybe make doing so the rule going forward)?
>
> Aside from the fact that bit_xor() does not need this, I am opposed to
> it in general.  It is not our job to make people write correct queries.
>

I cannot agree with the last sentence. It is questions about costs and
benefits, but good tool should to make warnings when users does some stupid
things.

It is important at this time, because complexity in IT is pretty high, and
a lot of users are not well trained (but well trained people can make
errors too). And a lot of users have zero knowledge about technology, So
when it is possible, and when it makes sense, then Postgres should be
simple and safe. I think it is important for renome too. It is about costs
and  benefits. Good reputation is a good benefit for us too. Ordered
aggregation was designed for some purposes, and should be used, when it has
sense.

Regards

Pavel

-- 
> Vik Fearing
>
>
>

  15. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-07T10:02:46Z

    On 3/7/21 10:53 AM, Pavel Stehule wrote:
> ne 7. 3. 2021 v 10:36 odesílatel Vik Fearing <vik@postgresfriends.org>
> napsal:
> 
>> On 3/6/21 9:06 PM, David G. Johnston wrote:
>>> On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:
>>>
>>>>
>>>>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
>>>>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
>>>>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
>>>>>
>>>>>
>>>>> Why would such an error be necessary, or even desirable?
>>>>
>>>> Because there is no way to ensure that the results remain consistent
>>>> from one execution to the next without such a guarantee.
>>>>
>>>
>>> Numerous existing aggregate functions have this behavior.  Making those
>>> error isn’t an option.  So is making this a special case something we
>> want
>>> to do (and also maybe make doing so the rule going forward)?
>>
>> Aside from the fact that bit_xor() does not need this, I am opposed to
>> it in general.  It is not our job to make people write correct queries.
>>
> 
> I cannot agree with the last sentence. It is questions about costs and
> benefits, but good tool should to make warnings when users does some stupid
> things.
> 
> It is important at this time, because complexity in IT is pretty high, and
> a lot of users are not well trained (but well trained people can make
> errors too). And a lot of users have zero knowledge about technology, So
> when it is possible, and when it makes sense, then Postgres should be
> simple and safe. I think it is important for renome too. It is about costs
> and  benefits. Good reputation is a good benefit for us too. Ordered
> aggregation was designed for some purposes, and should be used, when it has
> sense.

How many cycles do you recommend we spend on determining whether  ORDER
BY a, b  is sufficient but  ORDER BY a  is not?

If we had an optimization_effort_level guc (I have often wanted that),
then I agree that this could be added to a very high level.  But we
don't, so I don't want any of it.
-- 
Vik Fearing

  16. Re: [patch] bit XOR aggregate functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-07T10:05:55Z

    ne 7. 3. 2021 v 11:02 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

> On 3/7/21 10:53 AM, Pavel Stehule wrote:
> > ne 7. 3. 2021 v 10:36 odesílatel Vik Fearing <vik@postgresfriends.org>
> > napsal:
> >
> >> On 3/6/21 9:06 PM, David G. Johnston wrote:
> >>> On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:
> >>>
> >>>>
> >>>>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
> >>>>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
> >>>>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
> >>>>>
> >>>>>
> >>>>> Why would such an error be necessary, or even desirable?
> >>>>
> >>>> Because there is no way to ensure that the results remain consistent
> >>>> from one execution to the next without such a guarantee.
> >>>>
> >>>
> >>> Numerous existing aggregate functions have this behavior.  Making those
> >>> error isn’t an option.  So is making this a special case something we
> >> want
> >>> to do (and also maybe make doing so the rule going forward)?
> >>
> >> Aside from the fact that bit_xor() does not need this, I am opposed to
> >> it in general.  It is not our job to make people write correct queries.
> >>
> >
> > I cannot agree with the last sentence. It is questions about costs and
> > benefits, but good tool should to make warnings when users does some
> stupid
> > things.
> >
> > It is important at this time, because complexity in IT is pretty high,
> and
> > a lot of users are not well trained (but well trained people can make
> > errors too). And a lot of users have zero knowledge about technology, So
> > when it is possible, and when it makes sense, then Postgres should be
> > simple and safe. I think it is important for renome too. It is about
> costs
> > and  benefits. Good reputation is a good benefit for us too. Ordered
> > aggregation was designed for some purposes, and should be used, when it
> has
> > sense.
>
> How many cycles do you recommend we spend on determining whether  ORDER
> BY a, b  is sufficient but  ORDER BY a  is not?
>
> If we had an optimization_effort_level guc (I have often wanted that),
> then I agree that this could be added to a very high level.  But we
> don't, so I don't want any of it.
>

The safeguard is mandatory ORDER BY clause.



-- 
> Vik Fearing
>

  17. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-07T10:13:42Z

    On 3/7/21 11:05 AM, Pavel Stehule wrote:
> ne 7. 3. 2021 v 11:02 odesílatel Vik Fearing <vik@postgresfriends.org>
> napsal:
> 
>> On 3/7/21 10:53 AM, Pavel Stehule wrote:
>>> ne 7. 3. 2021 v 10:36 odesílatel Vik Fearing <vik@postgresfriends.org>
>>> napsal:
>>>
>>>> On 3/6/21 9:06 PM, David G. Johnston wrote:
>>>>> On Saturday, March 6, 2021, David Fetter <david@fetter.org> wrote:
>>>>>
>>>>>>
>>>>>>>>     SELECT BIT_XOR(b ORDER BY a, c)...        /* works */
>>>>>>>>     SELECT BIT_XOR(b) OVER (ORDER BY a, c)... /* works */
>>>>>>>>     SELECT BIT_XOR(b) FROM...                 /* errors out */
>>>>>>>
>>>>>>>
>>>>>>> Why would such an error be necessary, or even desirable?
>>>>>>
>>>>>> Because there is no way to ensure that the results remain consistent
>>>>>> from one execution to the next without such a guarantee.
>>>>>>
>>>>>
>>>>> Numerous existing aggregate functions have this behavior.  Making those
>>>>> error isn’t an option.  So is making this a special case something we
>>>> want
>>>>> to do (and also maybe make doing so the rule going forward)?
>>>>
>>>> Aside from the fact that bit_xor() does not need this, I am opposed to
>>>> it in general.  It is not our job to make people write correct queries.
>>>>
>>>
>>> I cannot agree with the last sentence. It is questions about costs and
>>> benefits, but good tool should to make warnings when users does some
>> stupid
>>> things.
>>>
>>> It is important at this time, because complexity in IT is pretty high,
>> and
>>> a lot of users are not well trained (but well trained people can make
>>> errors too). And a lot of users have zero knowledge about technology, So
>>> when it is possible, and when it makes sense, then Postgres should be
>>> simple and safe. I think it is important for renome too. It is about
>> costs
>>> and  benefits. Good reputation is a good benefit for us too. Ordered
>>> aggregation was designed for some purposes, and should be used, when it
>> has
>>> sense.
>>
>> How many cycles do you recommend we spend on determining whether  ORDER
>> BY a, b  is sufficient but  ORDER BY a  is not?
>>
>> If we had an optimization_effort_level guc (I have often wanted that),
>> then I agree that this could be added to a very high level.  But we
>> don't, so I don't want any of it.
>>
> 
> The safeguard is mandatory ORDER BY clause.


And so you are now mandating an ORDER BY on every query and in every
aggregate and/or window function.  Users will not like that at all.  I
certainly shan't.
-- 
Vik Fearing

  18. Re: [patch] bit XOR aggregate functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-07T10:24:02Z

    >
> And so you are now mandating an ORDER BY on every query and in every
> aggregate and/or window function.  Users will not like that at all.  I
> certainly shan't.
>

The mandatory ORDER BY clause should be necessary for operations when the
result depends on the order. You need an order for calculation of median.
And you don't need to know an order for average. More if the result is one
number and is not possible to do a visual check of correctness (like
median).

-- 
> Vik Fearing
>

  19. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-07T10:28:55Z

    On 3/7/21 11:24 AM, Pavel Stehule wrote:
>>
>> And so you are now mandating an ORDER BY on every query and in every
>> aggregate and/or window function.  Users will not like that at all.  I
>> certainly shan't.
>>
> 
> The mandatory ORDER BY clause should be necessary for operations when the
> result depends on the order. You need an order for calculation of median.
> And you don't need to know an order for average. More if the result is one
> number and is not possible to do a visual check of correctness (like
> median).

The syntax for median (percentile_cont(0.5)) already requires an order
by clause.  You are now requiring one on array_agg().
-- 
Vik Fearing

  20. Re: [patch] bit XOR aggregate functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-07T10:37:31Z

    ne 7. 3. 2021 v 11:28 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

> On 3/7/21 11:24 AM, Pavel Stehule wrote:
> >>
> >> And so you are now mandating an ORDER BY on every query and in every
> >> aggregate and/or window function.  Users will not like that at all.  I
> >> certainly shan't.
> >>
> >
> > The mandatory ORDER BY clause should be necessary for operations when the
> > result depends on the order. You need an order for calculation of median.
> > And you don't need to know an order for average. More if the result is
> one
> > number and is not possible to do a visual check of correctness (like
> > median).
>
> The syntax for median (percentile_cont(0.5)) already requires an order
> by clause.  You are now requiring one on array_agg().
>

array_agg is discuttable, because PostgreSQL arrays are ordered set type.
But very common usage is using arrays instead and unordered sets (because
ANSI/SQL sets) are not supported. But anyway - for arrays I can do visual
check if it is ordered well or not.


-- 
> Vik Fearing
>

  21. Re: [patch] bit XOR aggregate functions

    Vik Fearing <vik@postgresfriends.org> — 2021-03-07T11:39:38Z

    On 3/7/21 11:37 AM, Pavel Stehule wrote:
> ne 7. 3. 2021 v 11:28 odesílatel Vik Fearing <vik@postgresfriends.org>
> napsal:
> 
>> On 3/7/21 11:24 AM, Pavel Stehule wrote:
>>>>
>>>> And so you are now mandating an ORDER BY on every query and in every
>>>> aggregate and/or window function.  Users will not like that at all.  I
>>>> certainly shan't.
>>>>
>>>
>>> The mandatory ORDER BY clause should be necessary for operations when the
>>> result depends on the order. You need an order for calculation of median.
>>> And you don't need to know an order for average. More if the result is
>> one
>>> number and is not possible to do a visual check of correctness (like
>>> median).
>>
>> The syntax for median (percentile_cont(0.5)) already requires an order
>> by clause.  You are now requiring one on array_agg().
>>
> 
> array_agg is discuttable, because PostgreSQL arrays are ordered set type.
> But very common usage is using arrays instead and unordered sets (because
> ANSI/SQL sets) are not supported. But anyway - for arrays I can do visual
> check if it is ordered well or not.

If by "visual check" you mean "with my human eyeballs" then I would
argue that that is always the case and we don't need nannying for other
aggregates either.
-- 
Vik Fearing

  22. Re: [patch] bit XOR aggregate functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-07T12:03:49Z

    ne 7. 3. 2021 v 12:39 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

> On 3/7/21 11:37 AM, Pavel Stehule wrote:
> > ne 7. 3. 2021 v 11:28 odesílatel Vik Fearing <vik@postgresfriends.org>
> > napsal:
> >
> >> On 3/7/21 11:24 AM, Pavel Stehule wrote:
> >>>>
> >>>> And so you are now mandating an ORDER BY on every query and in every
> >>>> aggregate and/or window function.  Users will not like that at all.  I
> >>>> certainly shan't.
> >>>>
> >>>
> >>> The mandatory ORDER BY clause should be necessary for operations when
> the
> >>> result depends on the order. You need an order for calculation of
> median.
> >>> And you don't need to know an order for average. More if the result is
> >> one
> >>> number and is not possible to do a visual check of correctness (like
> >>> median).
> >>
> >> The syntax for median (percentile_cont(0.5)) already requires an order
> >> by clause.  You are now requiring one on array_agg().
> >>
> >
> > array_agg is discuttable, because PostgreSQL arrays are ordered set type.
> > But very common usage is using arrays instead and unordered sets (because
> > ANSI/SQL sets) are not supported. But anyway - for arrays I can do visual
> > check if it is ordered well or not.
>
> If by "visual check" you mean "with my human eyeballs" then I would
> argue that that is always the case and we don't need nannying for other
> aggregates either.
>

The correct solution is using arrays like arrays and sets like sets. When
you mix two different features to one, then you will have problems.

But if I see {{1,2,3},{3,4,5}} I have some knowledge - it is not 100%, but
it is. If I have 27373 as a result of median, I have nothing other
information.

The design of arrays (in pg) was incremental - it is older than Postgres
supported ordered aggregates, and probably older than ANSI/SQL introduced
sets. So the implementation of strong safeguards is not possible for
compatibility reasons. If I designed array_agg or string_agg today, then I
prefer to design it like ordered aggregates.

Sure - it is about life philosophy, and it is about projects where you are,
and about risks, .. some people prefer risks, some people prefer
safeguards. I see a complexity boom as a very big issue - I remember good
books about programming on 50 pagers, and then now we should start from
green or zero again or we have to implement most safeguards that are
possible to hold systems workable. But anyway - a good system is robust,
and robust systems try to reduce possible errors how it is possible (human
errors are most common).

But this is offtopic in this discussion :)





-- 
> Vik Fearing
>

  23. Re: [patch] bit XOR aggregate functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-07T16:31:57Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> But this is offtopic in this discussion :)

The whole topic is off-topic.  As a general rule, things that depend on
input order shouldn't be declared as aggregates --- they should be window
functions or ordered-set aggregates, for which the syntax forces you to
specify input order.  All of the standard aggregates, and most of our
custom ones (including BIT_XOR) do not depend on input order (... mumble
floating-point roundoff error mumble ...), so forcing users to write an
ordering clause would be useless, not to mention being a SQL spec
violation.

There are a small minority like array_agg that do have such a dependency,
but as far as I recall our docs for each of those warn about the need to
sort the input for reproducible results.  I think that's sufficient.
Who's to say whether a particular query actually requires reproducible
results?  Seeing that we don't provide reproducible row ordering
without an ORDER BY, I'm not sure why we should apply a different
standard to array_agg.

			regards, tom lane

  24. Re: [patch] bit XOR aggregate functions

    David Rowley <dgrowleyml@gmail.com> — 2021-03-07T21:08:14Z

    On Sun, 7 Mar 2021 at 23:24, Pavel Stehule <pavel.stehule@gmail.com> wrote:
 The mandatory ORDER BY clause should be necessary for operations when
the result depends on the order. You need an order for calculation of
median. And you don't need to know an order for average. More if the
result is one number and is not possible to do a visual check of
correctness (like median).

I really don't think so.

# create table f (f float not null);
# insert into f values(1e100),(-1e100),(1.5);
# select sum(f order by f) from f;
 sum
-----
   0
(1 row)

# select sum(f) from f;
 sum
-----
 1.5
(1 row)

Users are going to be pretty annoyed with us if we demanded that they
include an ORDER BY for that query. Especially so since our ORDER BY
aggregate implementation still has no planner support.

David