Duplicates being removed from intarray on subtraction of another intarray
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/intarray.html
Description:
Hi,
I recently ran into an unusual issue with the intarray extension where if
you subtract one array from another the result is *also* sorted and
de-duplicated. The documentation does not seem to imply that this should be
the case, stating only that the operator "removes elements of the right
array from the left array" and not that it also de-duplicates and sorts the
result... It seems to only occur when subtracting an array. Is this the
intended behavior?
SELECT '{3,1,1,2,2,2}'::int[] - 1; --> {3,2,2,2} as you would expect
SELECT '{3,1,1,2,2,2}'::int[] - '{1}'::int[]; --> {2,3} instead of
{3,2,2,2}
I have confirmed that I get the same result when using PostgreSQL 9 through
16 on DBFiddle. See here: https://dbfiddle.uk/i-eXKhFR
I am using the official PostgreSQL 16.2 Docker image for Debian (PostgreSQL
16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc
(Debian 12.2.0-14) 12.2.0, 64-bit).
Cheers
Tom
Discourage splitting pg_wal directory
Someone pointed out this morning that we still have this in our docs: "It is advantageous if the WAL is located on a different disk from the main database files. This can be achieved by moving the pg_wal directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location." This is not as important as it used to be, and I would even hazard to say that we should not be encouraging it. There are still use cases for doing so, yes, but "advantageous" is too much. I played with some wording, but at the end of the day, I am thinking that we simply remove this paragraph entirely. Cheers, Greg the_age_of_spinning_rust_is_over.v1.patch Description: Binary data
Re: Discourage splitting pg_wal directory
On Mon, 2024-03-18 at 11:32 -0400, Greg Sabino Mullane wrote: > "It is advantageous if the WAL is located on a different disk from the > main database files. This can be achieved by moving the pg_wal directory > to another location (while the server is shut down, of course) and > creating a symbolic link from the original location in the main data > directory to the new location." > > This is not as important as it used to be, and I would even hazard to > say that we should not be encouraging it. There are still use cases for > doing so, yes, but "advantageous" is too much. I played with some wording, > but at the end of the day, I am thinking that we simply remove this > paragraph entirely. I think it is still a good idea to put data files and WAL on different file systems. Perhaps not so much with the intention of distributing I/O across different disks, but to prevent the data files from filling the WAL disk. Yours, Laurenz Albe
Re: Duplicates being removed from intarray on subtraction of another intarray
On Mon, 2024-03-18 at 08:21 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/intarray.html
>
> Hi,
> I recently ran into an unusual issue with the intarray extension where if
> you subtract one array from another the result is *also* sorted and
> de-duplicated. The documentation does not seem to imply that this should be
> the case, stating only that the operator "removes elements of the right
> array from the left array" and not that it also de-duplicates and sorts the
> result... It seems to only occur when subtracting an array. Is this the
> intended behavior?
>
> SELECT '{3,1,1,2,2,2}'::int[] - 1; --> {3,2,2,2} as you would expect
> SELECT '{3,1,1,2,2,2}'::int[] - '{1}'::int[]; --> {2,3} instead of
> {3,2,2,2}
There is no harm in documenting that; I propose the attached patch.
Yours,
Laurenz Albe
From 96a7fed7be768c047f8a74a4431716da0beb0e28 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Mon, 18 Mar 2024 16:59:43 +0100
Subject: [PATCH v1] Documentation fix for intarray's "-" operator
Document that int[] - int[] removes duplicates as well as
elements of the right array.
Discussion: https://postgr.es/m/171075007381.7104.7931589808177869854%40wrigleys.postgresql.org
---
doc/src/sgml/intarray.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/intarray.sgml b/doc/src/sgml/intarray.sgml
index c72d49b01d..ec1d525042 100644
--- a/doc/src/sgml/intarray.sgml
+++ b/doc/src/sgml/intarray.sgml
@@ -312,7 +312,7 @@
integer[]
-Removes elements of the right array from the left array.
+Removes elements of the right array and duplicate entries from the left array.
--
2.44.0
Re: Duplicates being removed from intarray on subtraction of another intarray
On 2024-03-18 09:21 +0100, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/intarray.html
> Description:
>
> Hi,
> I recently ran into an unusual issue with the intarray extension where if
> you subtract one array from another the result is *also* sorted and
> de-duplicated. The documentation does not seem to imply that this should be
> the case, stating only that the operator "removes elements of the right
> array from the left array" and not that it also de-duplicates and sorts the
> result... It seems to only occur when subtracting an array. Is this the
> intended behavior?
>
> SELECT '{3,1,1,2,2,2}'::int[] - 1; --> {3,2,2,2} as you would expect
> SELECT '{3,1,1,2,2,2}'::int[] - '{1}'::int[]; --> {2,3} instead of
> {3,2,2,2}
>
> I have confirmed that I get the same result when using PostgreSQL 9 through
> 16 on DBFiddle. See here: https://dbfiddle.uk/i-eXKhFR
> I am using the official PostgreSQL 16.2 Docker image for Debian (PostgreSQL
> 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc
> (Debian 12.2.0-14) 12.2.0, 64-bit).
I don't know if it's intended behavior but it's implemented that way
since its inception (see intset_subtract in [1]). Also the intersection
and union operators behave similarly (sorted result without duplicates).
The attached patch changes the docs to state that the operator computes
the difference.
Operator integer[] - integer OTOH appears to be more in line with
integer[] + integer[] and integer[] + integer in that it doesn't treat
the arguments as sets.
It's unfortunate that both operations use the same operator and not
something like integer[] / integer[] which would be closer to the usual
notation for set difference.
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=181ca96e7a730ba35e973d3361422e6d8a460f88
--
Erik
>From 171ba158a1857e8d3323c758c73eb804609a939a Mon Sep 17 00:00:00 2001
From: Erik Wienhold
Date: Mon, 18 Mar 2024 17:04:09 +0100
Subject: [PATCH v1] Document intarray subtraction as set difference
The subtract operator provided by intarray actually has set semantics
and computes the difference of the provided arguments instead of just
removing elements. Document that analogous to the intersection and
union operators next to it.
---
doc/src/sgml/intarray.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/intarray.sgml b/doc/src/sgml/intarray.sgml
index c72d49b01d..25838ff023 100644
--- a/doc/src/sgml/intarray.sgml
+++ b/doc/src/sgml/intarray.sgml
@@ -312,7 +312,7 @@
integer[]
-Removes elements of the right array from the left array.
+Computes the difference of the arguments.
--
2.44.0
Monetary Data Types Improvement
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/datatype-money.html Description: It's not explicitly obvious that money doesn't behave like a normal numeric type in that executing a procedure with a negative numeric value for money causes an error. The solution to this is to pass the value as a string. For example, -15.99 for money should be expressed as '-15.99'.
Re: Monetary Data Types Improvement
On 2024-03-17 03:16 +0100, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/datatype-money.html > Description: > > It's not explicitly obvious that money doesn't behave like a normal numeric > type in that executing a procedure with a negative numeric value for money > causes an error. The solution to this is to pass the value as a string. For > example, -15.99 for money should be expressed as '-15.99'. I assume it's intended to be used like other numeric datatypes (although with known issues[1]) given that this page does not mention any operations besides division. I see no reason why unary minus and even unary plus shouldn't be implemented if negative amounts are already possible. Maybe it's not worth the effort if one can just do (OP x::numeric)::money for any unary numeric operator OP instead. CREATE OPERATOR is another option. Maybe add a note like: "Money does not implement all operators that one might expect of a numeric type. For example, use (-amount::money::numeric)::money to negate amount." That would also fit nicely with the existing examples on casting to numeric and float8. The attached patch does that. [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money -- Erik
Re: Monetary Data Types Improvement
I wrote: > The attached patch does that. Hit send to early. -- Erik >From 281e684b242314d93120faf875c1456ecdddef2b Mon Sep 17 00:00:00 2001 From: Erik Wienhold Date: Mon, 18 Mar 2024 19:38:53 +0100 Subject: [PATCH v1] Add note about missing money operators --- doc/src/sgml/datatype.sgml | 10 ++ 1 file changed, 10 insertions(+) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 73e51b0b11..d4b4ec54bc 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1118,6 +1118,16 @@ SELECT '52093.89'::money::numeric::float8; value, the result is double precision (i.e., a pure number, not money); the currency units cancel each other out in the division. + + + + Money does not implement all + operators + that one might expect of a numeric type. For example, use + (-amount::numeric)::money to negate + amount. + + -- 2.44.0
Re: Monetary Data Types Improvement
On Tue, 19 Mar 2024 at 07:43, Erik Wienhold wrote: > Maybe add a note like: > > "Money does not implement all operators that one might expect of a > numeric type. For example, use (-amount::money::numeric)::money to > negate amount." > > That would also fit nicely with the existing examples on casting to > numeric and float8. The attached patch does that. My vote would go to adding a deprecation notice to that section of the docs. There's some talk [1] about how we discourage the usage of the money type and that goes on to discuss the possibilities of moving it into a contrib module. My hope would be that deprecation notice would steer most people away from using it and therefore reduce the number of questions about it due to fewer new use cases of it. David [1] https://www.postgresql.org/message-id/zxgh74ykj3iwv...@paquier.xyz
