Re: I get ERROR: column "table_name" does not exist
On 2024-06-07 14:24 +0200, Florian Koch wrote: > I made a gist with the error. > https://gist.github.com/floratmin/a4b404f0fd66b03a0428c07686b71410 The problem is the semicolon after PG_DATATYPE_NAME, causing the TABLE_NAME line to be a separate statement: >exception_datatype := PG_DATATYPE_NAME; >exception_table:= TABLE_NAME; -- Erik
Re: I get ERROR: column "table_name" does not exist
On 2024-06-06 15:08 +0200, Florian Koch wrote:
> This is the working example, when I replace any of e.g.
> 'PG_EXCEPTION_HINT' with 'TABLE_NAME'. Then the function throws. I am
> using postgres 16.1.
Perhaps you did not declare variable table_name and run something like:
RETURN jsonb_build_object('table', table_name);
But that's still guesswork without seeing the actual function definition
with your edits.
> On Wed, Jun 5, 2024 at 6:32 PM Erik Wienhold wrote:
> >
> > On 2024-06-04 21:50 +0200, PG Doc comments form wrote:
> > > When using GET STACKED DIAGNOSTICS in PL/pgSQL accessing TABLE_NAME,
> > > COLUMN_NAME, CONSTRAINT_NAME or SCHEMA_NAME throws an error.
> > >
> > > The function "shared.test_raise_exception":
> > > https://github.com/rin-nas/postgresql-patterns-library/issues/8#issuecomment-2147984520
> >
> > * Do you have a reproducer with those diagnostic items? The linked
> > script does not use any of the items that you've mentioned.
> >
> > * Which Postgres version?
--
Erik
Re: I get ERROR: column "table_name" does not exist
On 2024-06-04 21:50 +0200, PG Doc comments form wrote: > When using GET STACKED DIAGNOSTICS in PL/pgSQL accessing TABLE_NAME, > COLUMN_NAME, CONSTRAINT_NAME or SCHEMA_NAME throws an error. > > The function "shared.test_raise_exception": > https://github.com/rin-nas/postgresql-patterns-library/issues/8#issuecomment-2147984520 * Do you have a reproducer with those diagnostic items? The linked script does not use any of the items that you've mentioned. * Which Postgres version? -- Erik
Re: 17beta1 source download does not include docs & manpages
On 2024-05-28 21:02 +0200, Marcel Hofstetter wrote: > Unfortunately I'm unable to produce the html files on my > Solaris 11.4 CBE system (margay buildfarm member) > > -bash-5.1$ gmake html > gmake -C doc html > gmake[1]: Entering directory > '/export/home/marcel/source/postgresql-17beta1/doc' > gmake -C src html > gmake[2]: Entering directory > '/export/home/marcel/source/postgresql-17beta1/doc/src' > gmake -C sgml html > gmake[3]: Entering directory > '/export/home/marcel/source/postgresql-17beta1/doc/src/sgml' > /usr/bin/xsltproc --nonet --path . --path . --stringparam pg.version > '17beta1' stylesheet.xsl postgres-full.xml > runtime error: file stylesheet-common.xsl line 124 element value-of > Variable 'chunk.base.dir' has not been declared. > XPath error : Undefined variable What DocBook XSL stylesheet version do you have installed? The docs say that 1.77.0 is required: https://www.postgresql.org/docs/17/docguide-toolsets.html#DOCGUIDE-TOOLSETS-DOCBOOK-XSL > runtime error: file stylesheet-common.xsl line 124 element value-of > XPath evaluation returned no result. > no result for postgres-full.xml > gmake[3]: *** [Makefile:130: html-stamp] Error 10 > gmake[3]: Leaving directory > '/export/home/marcel/source/postgresql-17beta1/doc/src/sgml' > gmake[2]: *** [Makefile:8: html] Error 2 > gmake[2]: Leaving directory > '/export/home/marcel/source/postgresql-17beta1/doc/src' > gmake[1]: *** [Makefile:16: html] Error 2 > gmake[1]: Leaving directory > '/export/home/marcel/source/postgresql-17beta1/doc' > gmake: *** [GNUmakefile:27: html] Error 2 > > -bash-5.1$ uname -a > SunOS g0071 5.11 11.4.42.111.0 sun4v sparc sun4v logical-domain -- Erik
Re: 17beta1 source download does not include docs & manpages
On 2024-05-28 14:15 +0200, Marcel Hofstetter wrote: > 16.3 source download includes manpages and html docs. > > -bash-5.1$ tar tvf postgresql-16.3.tar | grep html | wc -l > 1166 > -bash-5.1$ tar tvf postgresql-16.3.tar | grep man1 | wc -l > 35 > > > 17beta1 does not. > > -bash-5.1$ tar tvf postgresql-17beta1.tar | grep html | wc -l > 3 > -bash-5.1$ tar tvf postgresql-17beta1.tar | grep man1 | wc -l > 0 > > is this intended? Yes, that build step was removed[1] in order to only create a tarball of files in the git tree from now on. Maybe the release notes should make that more clear instead of just saying "Remove make's distprep option". [1] https://www.postgresql.org/message-id/e07408d9-e5f2-d9fd-5672-f53354e9305e%40eisentraut.org -- Erik
Re: column_name of ALTER MATERIALIZED VIEW should only refer to an existing column
On 2024-05-23 06:04 +0200, Michael Paquier wrote: > On Wed, May 22, 2024 at 02:12:36PM +0900, Michael Paquier wrote: > > Will fix once we are out of release freeze time on HEAD. Thanks! > > And done as of dd087e1c13bf. Thank you Michael! -- Erik
column_name of ALTER MATERIALIZED VIEW should only refer to an existing column
Here's a patch for $SUBJECT. Looks like the current wording was copied from ALTER TABLE. In ALTER VIEW we correctly state that column_name must be an existing column. -- Erik >From 1d7accf2311f4fda2d8ef9a0485985620c5b4b39 Mon Sep 17 00:00:00 2001 From: Erik Wienhold Date: Wed, 22 May 2024 02:30:16 +0200 Subject: [PATCH] doc: fix column_name parameter in ALTER MATERIALIZED VIEW Parameter column_name must be an existing column because ALTER MATERIALIZED VIEW cannot add new columns. The old description was likely copied from ALTER TABLE. --- doc/src/sgml/ref/alter_materialized_view.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml index da7ed04597..f81a7393f5 100644 --- a/doc/src/sgml/ref/alter_materialized_view.sgml +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -100,7 +100,7 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE namecolumn_name - Name of a new or existing column. + Name of an existing column. -- 2.45.1
Re: nchar is undocumented
On 2024-05-06 10:59 +0200, Alvaro Herrera wrote: > By the way, this neighboring sentence is a bit awkward > > " It must be ... or a type for which there is a cast from json to that type." > > Would it be better to say > " It must be ... or a type to which a cast from json exists." > ? Yeah, that sounds better, but I'd say: "[...] or a type _for_ which a cast from json exists." Or maybe just "[...] or any type that can be cast to json." ? -- Erik
nchar is undocumented
While studying the parser, I noticed that we support nchar and the n'' literal. But both concepts are undocumented. That may be intentional because nchar is just an alias for char and because all our string types are multibyte and therefore essentially national character strings. I was looking through the archive for previous discussions and found: * Thread [1] (about making nchar a distinct type) which noted that docs are missing but nobody proposed a fix. * Thread [2] where Peter Eisentraut suggested leaving nchar undocumented until it's figured out if our implementation matches the standard. What bugs me about the missing docs is that 7081ac46ace added nchar to the intro for table 9.47[3], assuming that the reader already knows about nchar. That's the only instance of nchar in the docs (besides the keyword list). So, I think we should either remove that one nchar instance (because it doesn't add any real value) or document it properly. The attached patch does the latter. [1] https://www.postgresql.org/message-id/B1A7485194DE4FDAB8FA781AFB570079%40maumau [2] https://www.postgresql.org/message-id/1275895438.1849.1.camel%40fsopti579.F-Secure.com [3] https://www.postgresql.org/docs/16/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE -- Erik >From 314e0ffc1efb854f603e325c13a39ba871aebbf5 Mon Sep 17 00:00:00 2001 From: Erik Wienhold Date: Sun, 5 May 2024 02:15:53 +0200 Subject: [PATCH] Document nchar data type alias Also mention that we support national character string constants. --- doc/src/sgml/datatype.sgml | 6 +++--- doc/src/sgml/syntax.sgml | 7 +++ 2 files changed, 10 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 6646820d6a..3063b7e2cb 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1234,9 +1234,9 @@ SELECT '52093.89'::money::numeric::float8; The type name varchar is an alias for character -varying, while bpchar (with length specifier) and -char are aliases for character. The -varchar and char aliases are defined in the +varying, while bpchar (with length specifier), +nchar, and char are aliases for character. +The varchar and char aliases are defined in the SQL standard; bpchar is a PostgreSQL extension. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 4dfbbd0862..6579b2908a 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -334,6 +334,13 @@ SELECT 'foo' 'bar'; by SQL; PostgreSQL is following the standard.) + + + PostgreSQL also accepts national character + string constants (n'sample') for compliance with + the SQL standard. Those are essentially the same as + regular string constants. + -- 2.45.0
Re: nested tags in glossary entries in html docs
On 2024-04-25 22:00 +0200, Alexander Lakhin wrote: > 25.04.2024 12:24, Alvaro Herrera wrote: > > On 2024-Apr-12, Erik Wienhold wrote: > > > > > There's this bug[1] in the DocBook XSLT stylesheets. Looks like the > > > fix[2] landed in 1.79.2 (latest version on Arch, > > Maybe one of these days we should get going with the migration to > > Docbook 5.x that Jürgen Purtz proposed. > > > > https://postgr.es/m/21ed3fd9-9020-4b53-b04f-a08a831b6...@purtz.de > > > > In the meantime, if anyone wants to suggest a XSLT patch to carry in our > > local definition, we could try that. > > Please try the attached patch, which adds name="glossterm">, borrowed from /usr/share/xml/docbook/stylesheet/ > docbook-xsl/xhtml/inline.xsl (I have docbook-xsl 1.79.2 installed), to our > local stylesheet-html-common.xsl. > > I applied the modification from [1] (in two places) and it looks like the > nested issue is gone. > > [1] > https://github.com/docbook/xslt10-stylesheets/pull/72/commits/62144252364492aecd71a3c8d5e6e1624af84785 It works. There are already a couple of upstream fixes copied into our stylesheets, with links to bug reports. So I also created one for reference, with my upthread test case: https://github.com/docbook/xslt10-stylesheets/issues/267 -- Erik
Re: nested tags in glossary entries in html docs
On 2024-04-25 15:40 +0200, Jürgen Purtz wrote: > On 25.04.24 13:24, Jürgen Purtz wrote: > > > > On 25.04.24 11:24, Alvaro Herrera wrote: > > > On 2024-Apr-12, Erik Wienhold wrote: > > > > > > > There's this bug[1] in the DocBook XSLT stylesheets. Looks like the > > > > fix[2] landed in 1.79.2 (latest version on Arch, > > > Maybe one of these days we should get going with the migration to > > > Docbook 5.x that Jürgen Purtz proposed. > > > > > > https://postgr.es/m/21ed3fd9-9020-4b53-b04f-a08a831b6...@purtz.de > > > > > > In the meantime, if anyone wants to suggest a XSLT patch to carry in our > > > local definition, we could try that. > > > > > Great recommendation. I may have time in the second half of June to > > pursue the migration further. There is a performance problem, which > > possibly results from our XSLT script that optimizes the > > transformation-speed and works well in 4.x. > > > ... or do we have a problem with the fact that our xml files are not > well-formed? Some of them contain more than one root-element: > > xmllint --noout *.sgml ref/*.sgml 2> >(grep Extra) No, those files are not processed as standalone documents but are transcluded into postgres-full.xml from which postgres.html is then generated. And postgres-full.xml is well-formed according to xmllint. -- Erik
Re: Documentation Chapter 16: 8.21
On 2024-04-20 14:22 +0200, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/datatype-pseudo.html > Description: > > I tryed to implement a safe_cast function with 2 parameters and a return of > type "any" but it failed. I get a syntax error, if I use "any" as a data > type, because it is a sql function (similar to array contains) and not a > data type. You have to use a double-quoted identifier, i.e. "any" instead of ANY. But it won't work with PL languages: => create function f() returns "any" as 'select 1' language sql; ERROR: SQL functions cannot return type "any" You have to code a C function for that. > And if there is a data type "any" in postgres too, how should it work as > parameter and wheres the difference to "anyelement"? "any" is like "anyelement" just without the polymorphic[1] behavior. So an argument of type "any" does not determine the result type for "anyarray" as "anyelement" would. [1] https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC -- Erik
Re: nested tags in glossary entries in html docs
On 2024-04-12 18:29 +0200, Anton Voloshin wrote: > In REL_13_STABLE and above, generated HTML have a broken HTML: nested href="..."> tags for all links to glossary. Somehow, this results in > duplicated tags on the https://www.postgresql.org/docs/ > > Found by tab-navigating https://www.postgresql.org/docs/16/rowtypes.html > where we see (spacing added to avoid line wraps): > ... create a href="glossary.html#GLOSSARY-DOMAIN" href="glossary.html#GLOSSARY-DOMAIN" > title="Domain">domain over the composite type ... > > So, empty , and then the real . This resulted in stopping twice on the > "domain" link (right before, and then on the > "domain" word itself) while tab-navigating. There's this bug[1] in the DocBook XSLT stylesheets. Looks like the fix[2] landed in 1.79.2 (latest version on Arch, matching the latest snapshot on GitHub from 2020-06-03) because I can see the change in /usr/share/xml/docbook/xsl-stylesheets-1.79.2-nons/html/inline.xsl and /usr/share/xml/docbook/xsl-stylesheets-1.79.2-nons/xhtml/inline.xsl. But I still get those nested with a simple test: http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd;> Test A B B Lorem ipsum Generating the XHTML with xsltproc --nonet /usr/share/xml/docbook/xsl-stylesheets-1.79.2-nons/xhtml/docbook.xsl test.sgml | grep '' gives me B > Not sure about how to fix this (don't really know docbook). My XSLT skills are quite rusty, but maybe it's possible to omit the outer and just emit and its child in our stylesheets. [1] https://github.com/docbook/xslt10-stylesheets/issues/24 [2] https://github.com/docbook/xslt10-stylesheets/commit/c242ce2b8c1a5ebfdb2e719f788367bb1ddee8ea -- Erik
Re: ALTER TABLE atomicity with sub-commands
On 2024-04-02 16:40 +0200, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-altertable.html > Description: > > It is not entirely clear (to me) that ALTER TABLE statements with > comma-separated sub-commands are atomic. Despite of saying "perform X > actions in one operation" in one of the examples, it is not explicitly said > that the operation will be rolled back if one of the sub-commands fails. > > From the examples, we have: > > ALTER TABLE distributors > ALTER COLUMN address TYPE varchar(80), > ALTER COLUMN name TYPE varchar(100); > > Will the `address` column type change rollback if the `ALTER COLUMN name > TYPE varchar(100)` subcommand fails? > > Currently reading the docs for version 15. Yes, ALTER TABLE, like all statements, is one atomic change. >From BEGIN[1]: By default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done). [1] https://www.postgresql.org/docs/15/sql-begin.html -- Erik
Re: Monetary Data Types Improvement
On 2024-03-18 23:24 +0100, David Rowley wrote: > 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. > > [1] https://www.postgresql.org/message-id/zxgh74ykj3iwv...@paquier.xyz +1 Huh, I didn't know that it used to have a deprecation notice at some point. But that note was removed in 8.3: https://www.postgresql.org/message-id/20071009123315.5fb283c1.darcy%40druid.net -- 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 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: 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
Re: [PATCH] Fix link to pg_ident_file_mappings
On 2024-02-21 08:50 +0100, Daniel Gustafsson wrote: > > On 21 Feb 2024, at 03:24, Erik Wienhold wrote: > > > > The docs on pg_reload_conf() in v15, v16, and devel have an incorrect > > link to pg_ident_file_mappings. The attached patch fixes that. > > Nice catch, will fix. Thanks! -- Erik
[PATCH] Fix link to pg_ident_file_mappings
The docs on pg_reload_conf() in v15, v16, and devel have an incorrect
link to pg_ident_file_mappings. The attached patch fixes that.
--
Erik
>From 70ec25bf5e24c1b59c8a0b51afc04d87d9c26a0e Mon Sep 17 00:00:00 2001
From: Erik Wienhold
Date: Wed, 21 Feb 2024 03:13:10 +0100
Subject: [PATCH] Doc: fix link to pg_ident_file_mappings
---
doc/src/sgml/func.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 830566028c..e5fa82c161 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27211,7 +27211,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
of its children.) You can use the
pg_file_settings,
pg_hba_file_rules
and
-pg_ident_file_mappings
views
+pg_ident_file_mappings
views
to check the configuration files for possible errors, before reloading.
--
2.43.2
Re: [DOCS] Add example about date ISO format
On 2023-11-27 15:22 +0100, Alvaro Herrera wrote: > On 2023-Nov-27, Erik Wienhold wrote: > > > Two issues that I fixed in the attached patch: > > > > * it's called "extended format" not "T format" (the "T" is mandatory > > anyway) > > +1 > > > * the time zone was missing from the result output > > This is wrong. Actually, there's no timezone in value, because the use > of the AT TIME ZONE operator makes the type be TIMESTAMP WITHOUT TIME > ZONE. You would notice this if you were to change the incorrect literal > Z in your format string with "TZ" (which expands to empty), with "OF" > (which expands to "+00"), or with "TZH:TZM" (which expands to the full > timezone shift): Thanks for the explanation. Bruce used literal Z in the format string. I just corrected the sample output to match the format. Or we just use current_timestamp along with pattern TZH:TZM which also adds less to the width of the displayed table in case Bruce is concerned about that ;) -- Erik
Re: [DOCS] Add example about date ISO format
On 2023-11-24 18:29 +0100, Bruce Momjian wrote:
> On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote:
> > On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:
> > > Okay, I moved it into the "Note" section that talked about ISO 8601
> > > output with "T", in the attached patch.
> >
> > Fine by me, except that I would rather have "returns" or "produces"
> > instead of the questionable verb "outputs".
>
> The majority of people seem to want it in table 9.31, so I have moved it
> there.
Fine by me.
> It does almost double the width of the displayed table though.
> You can see the new output here:
>
>
> https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE
>
> Patch attached.
Two issues that I fixed in the attached patch:
* the time zone was missing from the result output
* it's called "extended format" not "T format" (the "T" is mandatory
anyway)
--
Erik
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 20da3ed033..8a83ac0529 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
to_char(current_timestamp,
'FMDay,FMDDHH12:MI:SS')
'Tuesday,605:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SSZ')
+'2022-12-06T05:39:18Z',
+ISO 8601 extended format
+
to_char(-0.1, '99.99')
'-.10'
Re: [DOCS] Add example about date ISO format
On 2023-11-22 17:58 +0100, Bruce Momjian wrote: > On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote: > > > > + > > > > + > > > > +to_char(current_timestamp AT TIME ZONE 'UTC', > > > > +'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC > > > > This might be excessive, but should we have an example with other time > > zones? ISO 8601 is not limited to UTC. For example: > > -MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM > > > > Fractional seconds are also possible: -MM-DD"T"HH24:MI:SS,FF6 > > Uh, I think the goal was to show how to output ISO 8601 output with "T". > I assume they can figure out how to customize that. Fair point. > > > > +date/time in ISO 8601 date/time format. > > > > + > > > > + > > > > + > > > > > > > > > > > > > > > > > > +1 on the idea, but from the context it looks like you added that example > > > at the regular expression matching functions. > > > > > > I think the example had best be at "8.5.2. Date/Time Output", in > > > doc/src/sgml/datatype.sgml around line 2552. > > > > +1 for moving it to section 8.5.2. > > Okay, I moved it into the "Note" section that talked about ISO 8601 > output with "T", in the attached patch. > > I will apply this only to master since it is not a correction. LGTM. -- Erik
Re: [DOCS] Add example about date ISO format
On 2023-11-22 10:14 +0100, Laurenz Albe wrote:
> On Tue, 2023-11-21 at 23:33 -0500, Bruce Momjian wrote:
> > On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@iki.fi wrote:
> > > The following documentation comment has been logged on the website:
> > >
> > > Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
> > > Description:
> > >
> > > The documentation should include an example how to format datetime entry
> > > into most commonly known ISO format. This is a bit tricky as literal
> > > character needs to included with quotes:
> > >
> > > to_char(NOW(), -MM-DDTHH24:MI:SSZ)
> >
> > I know this is a six-year-old idea, but it is still a good one. I have
> > developed the attached patch I would like to apply to master.
> >
> > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> > index 93f068edcf..297cafb341 100644
> > --- a/doc/src/sgml/func.sgml
> > +++ b/doc/src/sgml/func.sgml
> > @@ -8489,6 +8489,14 @@ SELECT regexp_match('abc01234xyz',
> > '(?:(.*?)(\d+)(.*)){1,1}');
> >
> >
> >
> > +
> > +
> > +to_char(current_timestamp AT TIME ZONE 'UTC',
> > +'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC
This might be excessive, but should we have an example with other time
zones? ISO 8601 is not limited to UTC. For example:
-MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM
Fractional seconds are also possible: -MM-DD"T"HH24:MI:SS,FF6
> > +date/time in ISO 8601 date/time format.
> > +
> > +
> > +
> >
> >
> >
>
> +1 on the idea, but from the context it looks like you added that example
> at the regular expression matching functions.
>
> I think the example had best be at "8.5.2. Date/Time Output", in
> doc/src/sgml/datatype.sgml around line 2552.
+1 for moving it to section 8.5.2.
--
Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-20 08:14 +0100, Peter Eisentraut wrote: > On 19.11.23 21:34, Erik Wienhold wrote: > > * Making explicit references to ISO 8601:2004 where section numbers are > >referenced. Mostly in source comments but also a couple of places in > >the docs. This is about avoiding confusion as ISO 8601:2019 has been > >published since then, with different section numbers[1]. The pre-2004 > >editions also have different section numbers. References to general > >ISO 8601 concepts (e.g. week numbers) are left unchanged because those > >are not tied to any particular edition. > > Maybe we should change the references to the 2019 edition instead? I wouldn't do that without knowing the full text of the standard. Maybe I can eyeball which 2004 sections map to 2019 sections but I wouldn't be confident in that. Also 8601:2019 removed 24:00 as midnight for some reason but it is allowed again with an ammendment from 2022. Not sure what else changed besides the "main changes" summarized in the foreword of [1]. And I can't find the 2022 ammendment on iso.org. [1] https://www.iso.org/obp/ui/en/#iso:std:iso:8601:-1:ed-1:v1:en -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-15 15:46 +0100, Tom Lane wrote:
> Erik Wienhold writes:
> > On 2023-11-15 12:53 +0100, Peter Eisentraut wrote:
> >> I think we should reframe "ISO" to mean "ISO 9075" and remove all claims of
> >> alignment with ISO 8601 and RFC 3339.
>
> > Agree. So just list the example inputs without any reference to a
> > particular standard, except for ISO 9075 to show that Postgres is
> > SQL-standard-compliant?
>
> I think that would remove useful context without actually improving
> anything. (The datetime input code would be far simpler if it
> meant only to read the exact format mentioned in the SQL spec.)
I wrote the attached patch to hopefully clarify the ISO 8601 references.
The two main changes are:
* Making explicit references to ISO 8601:2004 where section numbers are
referenced. Mostly in source comments but also a couple of places in
the docs. This is about avoiding confusion as ISO 8601:2019 has been
published since then, with different section numbers[1]. The pre-2004
editions also have different section numbers. References to general
ISO 8601 concepts (e.g. week numbers) are left unchanged because those
are not tied to any particular edition.
* Remove the claim that the SQL standard requires ISO 8601 formats as
clarified by Peter Eisentraut. I left the general references to ISO
8601 and RFC 3339 because those relate to the date format that
Postgres implements in addition to the standard SQL formats. Also
change time zone input samples that are described as ISO 8601 but do
not match the standard format.
[1] https://www.iso.org/obp/ui/en/#iso:std:iso:8601:-1:ed-1:v1:en
--
Erik
>From c98e2a86ee4ddaff0e9dc7d2e1e811736c00b48d Mon Sep 17 00:00:00 2001
From: Erik Wienhold
Date: Sun, 19 Nov 2023 19:18:07 +0100
Subject: [PATCH v1] Fix references to ISO 8601
* Name the exact edition in sources and docs that reference particular
sections of the standard. ISO 8601:2019 edition was published since
then. So we should spell it out that we reference ISO 8601:2004 to
avoid confusion because other editions have different section numbers
and titles.
* Fix a typo in references to sections 4.4.4.2.1 and 4.4.4.2.2 in
ISO 8601:2004.
* Fix a reference to the ISO 8601:1988 where the edition is not relevant
because it does not name a particular section but only the general
concept of week numbers.
* Fix general references to ISO 8601 in the docs of the datetime data
types. Remove claims that the SQL standard requires ISO 8601 formats.
SQL only references ISO 8601 to define "date" and UTC, but defines its
own date and time syntax. Postgres implements ISO 8601 formats in
addition to the standard.
* Fix time zone input examples that are described as ISO 8601 but do not
match the standard syntax by omitting the leading zero of the hour
component.
---
doc/src/sgml/config.sgml | 2 +-
doc/src/sgml/datatype.sgml | 26 ++
src/backend/utils/adt/datetime.c | 12 +-
src/interfaces/ecpg/pgtypeslib/interval.c | 10 -
src/interfaces/ecpg/pgtypeslib/timestamp.c | 2 +-
src/test/regress/expected/interval.out | 4 ++--
src/test/regress/sql/interval.sql | 4 ++--
7 files changed, 29 insertions(+), 31 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fc35a46e5e..a1a3981dd9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9526,7 +9526,7 @@ SET XML OPTION { DOCUMENT | CONTENT };
parameter was set to non-ISO output.
The value iso_8601 will produce output matching the
time
interval format with designators defined in section
-4.4.3.2 of ISO 8601.
+4.4.3.2 of ISO 8601:2004.
The IntervalStyle parameter also affects the
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index e4a7b07033..1c54305e25 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2088,7 +2088,7 @@ MINUTE TO SECOND
same as 16:05; input hour must be = 12
- 04:05:06.789-8
+ 04:05:06.789-08
ISO 8601, with time zone as UTC offset
@@ -2150,16 +2150,16 @@ MINUTE TO SECOND
UTC offset for PST
- -8:00
+ -08:00
UTC offset for PST (ISO 8601 extended format)
- -800
+ -0800
UTC offset for PST (ISO 8601 basic format)
- -8
- UTC offset for PST (ISO 8601 basic format)
+ -08
+ UTC offset for PST (ISO 8601)
zulu
@@ -2207,7 +2207,7 @@ MINUTE TO SECOND
and:
-1999-01-08 04:05:06 -8:00
+1999-01-08 04:05:06 -08:00
are valid values, which follow the ISO 86
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-15 12:53 +0100, Peter Eisentraut wrote: > On 15.11.23 09:37, Erik Wienhold wrote: > > On 2023-11-15 08:16 +0100, Peter Eisentraut wrote: > > > The SQL standard does not refer to ISO 8601 to define date formats, it has > > > its own definitions. In fact, PostgreSQL implements more date formats > > > than > > > the SQL standard requires. > > > > Really? Then what does the standard mean with section "Definitions > > taken from ISO 8601" which I quoted in [1]? Just using the term "date" > > without adopting its syntax? > > Exactly, it just imports the definitions of those terms. Thanks, now I see. SQL only defines date format '-MM-DD' (, MM, and DD can be any unsigned integer) with this BNF: > ::= > DATE > > ::= > > > ::= > > > ::= > And timestamp is only defined with a space separator which is clearly not ISO 8601: > ::= > > > And the Postgres docs also say "The SQL standard requires the use of the > > ISO 8601 format." [2] > > [2] > > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT > > Yeah, that isn't correct. > > I think we should reframe "ISO" to mean "ISO 9075" and remove all claims of > alignment with ISO 8601 and RFC 3339. Agree. So just list the example inputs without any reference to a particular standard, except for ISO 9075 to show that Postgres is SQL-standard-compliant? -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-15 08:16 +0100, Peter Eisentraut wrote: > The SQL standard does not refer to ISO 8601 to define date formats, it has > its own definitions. In fact, PostgreSQL implements more date formats than > the SQL standard requires. Really? Then what does the standard mean with section "Definitions taken from ISO 8601" which I quoted in [1]? Just using the term "date" without adopting its syntax? And the Postgres docs also say "The SQL standard requires the use of the ISO 8601 format." [2] [1] https://www.postgresql.org/message-id/piavtdd7mhmkpzpgvxaek3hz3e2kan3c2fitn5iqta6nyrpgyl%40txongxshxkxw [2] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-13 17:23 +0100, Tom Lane wrote: > Erik Wienhold writes: > > On 2023-11-13 15:24 +0100, Erik Wienhold wrote: > >> I also noticed that when people say "ISO 8601" they usually mean RFC > >> 3389 or some subset of ISO 8601. > > > Forgot this fine visualization of the differences: > > https://ijmacd.github.io/rfc3339-iso8601/ > > I'm inclined not to change anything here, for a couple of reasons: > > 1. PG accepts a fairly large number of ISO 8601 variants (not all); > not only the RFC 3339 format. So s/ISO 8601/RFC 3339/g would be > incorrect. I did not say that occurrences of "ISO 8601" should be replaced with "RFC 3339". Just that the docs should have a cautionary note about the SQL standard using a subset of ISO 8601 and that Postgres does not implement (at the moment) all of its date formats. > Besides, I think more people know what ISO 8601 is than > know what RFC 3339 is, so that change would also be confusing. Fair point. > 2. If ijmacd's pretty graphic is correct, then what we say about > 'T' versus space is correct, even if it isn't the whole truth. > I'm not quite sure that ijmacd is correct, though, because of > this bit in 3339: > > NOTE: Per [ABNF] and ISO8601, the "T" and "Z" characters in this > syntax may alternatively be lower case "t" or "z" respectively. > > which suggests that 8601 is also case-insensitive. I don't plan > to go buy a copy of that spec to find out, though. I dug up my old copies (1988/2000/2004) from uni (let me know if you're interested ;) Lower case is allowed if upper case is not available. And it doesn't allow space in any edition. Quote from the 2004 edition: > 3.4.1 Introduction > > NOTE 1 In date and time representations lower case characters may > be used when upper case characters are not available. > > NOTE 2 Encoding of characters for the interchange of dates and times > is not in the scope of this International Standard. > > Unless explicitly allowed by this International Standard the character > "space" shall not be used in the representations. Probably still the same in the current edition but I won't pay for that either. -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-13 15:24 +0100, Erik Wienhold wrote: > I also noticed that when people say "ISO 8601" they usually mean RFC > 3389 or some subset of ISO 8601. Forgot this fine visualization of the differences: https://ijmacd.github.io/rfc3339-iso8601/ -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-13 12:27 +0100, Roman Frołow wrote: > It seems I was wrong. > Now T is mandatory in ISO8601 and from RFC3339 it seems this requirement is > relaxed. Right. RFC 3339 is from 2002 and it references ISO 8601:1988 where T was optional. (Until today I didn't even know about the 2019 edition.) RFC 3339 made the separator mandatory and also permitted any character. Also SQL only uses part of ISO 8601 (without specifying a particular edition), namely the definition of UTC and date according to 3.1.4 in Part 2 of the standard: > 3.1.4 Definitions taken from ISO 8601 > > For the purposes of this part of ISO/IEC 9075, the definitions of the > following terms given in ISO 8601 apply: > > 3.1.4.1 Coordinated Universal Time (UTC) > 3.1.4.2 date (date, calendar in ISO 8601) I think the docs should clarify that "ISO 8601" in this case refers to a subset of definitions as layed out in the SQL standard. For example, week date (SELECT '2023-W46-1'::date) is not accepted by Postgres, but ordinal date (SELECT '2023-317'::date) is. I also noticed that when people say "ISO 8601" they usually mean RFC 3389 or some subset of ISO 8601. [1] [1] https://robinpokorny.com/blog/why-developers-should-stop-using-iso-8601-for-date-time/ -- Erik
Re: T is a mandatory date time separator in RFC3339 but documentation states differently
On 2023-11-11 23:45 +0100, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/git.html > Description: > > >ISO 8601 specifies the use of uppercase letter T to separate the date and > time. PostgreSQL accepts that format on input, but on output it uses a space > rather than T, as shown above. This is for readability and for consistency > with RFC 3339 as well as some other database systems. This note probably refers to section 5.6. of RFC 3339 [1] which allows applications to choose space over "T". > https://www.postgresql.org/docs/current/datatype-datetime.html > > Short answer: T (or t as discouraged alternative). > > After reading on this as much as I could, it turns out the time separator > must be a T or t. What has made think this way is first of all this thread > in the GNU lists where F. Alexander Njemz contacted the authors of RFC3339 > Graham Klyne and Chris Newman asking if T is mandatory and got this response > from Mr. Klyne: > > > In short: "yes" > > > > Per section 5.5, the intent in this draft was to specify a timestamp > format using elements from and compatible with 8601, but eliminating as far > as reasonable any variations that could make timestamp data harder to > process. This includes making the 'T' mandatory in date+time values. > > Just for clarity's sake, this is stated in the section 5.5: > > > Simplicity is achieved by making most fields and punctuation mandatory. But the word "most" certainly leaves some wiggle room. > This clearly clashes with a non-mandatory T and strongly makes me think that > the this syntax in that problematic passage refers to ISO8601 and not > RFC3339. > > https://stackoverflow.com/questions/63783868/what-are-valid-date-time-separators-in-rfc3339-strings/63882162#63882162 [1] https://datatracker.ietf.org/doc/html/rfc3339#section-5.6 -- Erik
Re: Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html
On 2023-10-04 16:24 +0200, PG Doc comments form write: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/queries-order.html > Description: > > The document only says this about unsorted queries: > > > After a query has produced an output table (after the select list has been > > processed) it can optionally be sorted. If sorting is not chosen, the rows > > will be returned in an unspecified order. The actual order in that case will > > depend on the scan and join plan types and the order on disk, but it must > > not be relied on. A particular output ordering can only be guaranteed if the > > sort step is explicitly chosen. > > It mentions "If sorting is not chosen". This sort of implies that if you > pick a sort the output order is predictable. However I believe that the only > actual guarantee is if the sort columns selected produce a unique value. > > For example if you do `ORDER BY name` and have two rows with the same name I > don't think the order of those rows is predictable. Right, without an explicit sorting the order of rows is unpredictable. When only sorting over some columns/expressions then the ordering is only predictable in those columns/expressions. The order of rows with ties within the same sorted group is also unpredictable. This is also implied on the same page after the first example: "When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values." This implies that without later values, those rows remain in unpredictable order. The SQL standards linked by [1] provide some definitions: SQL:2003, Part 2, Annex C, 26b: "The relative ordering of two rows that are not distinct with respect to the is implementation-dependent." SQL:2011, Part 2, Annex C, 24e: "If a immediately contains an and a group of two or more rows in the table specified by that contain values that are not distinct in all sort keys specified in the , then the ordering of these rows in that group is implementation-dependent." I find the first one quite succinct. > I think the docs should be updated to either: > > 1. Clearly state that the order **is** consent as long as any sort clause is > present, and specify what that order is. What do you mean with "any sort clause"? Any sort clause at all? Or a sort clause that covers all columns? If the order should be predictable it must be specified by the client to some degree. And if the client does not care about rows with ties than he should not be required to specify a more specific sorting. > 2. Update the quoted sentence to refer to "If sorting is not chosen or the > sort columns do not form a unique key" instead of just "If sorting is not > chosen". I think "unique key" is misleading in this case because sorting still leaves duplicates. I'd go with something that mentions "sorted groups" and "tie breaks" or some form of the quoted SQL:2003 definition. [1] https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F -- Erik
Re: Error in Example
On 31/08/2023 01:29 CEST PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/15/indexes-partial.html > Description: > > The IP addresses used to exemplify which one will be covered by the partial > index are inverted. No, the example is correct. It's about only indexing addresses outside some hypothetical organization's subnet 192.168.100.0/24. Notice that the index predicate is negated. Of course the predicate could be written without inverting the range: WHERE (client_ip <= inet '192.168.100.0' OR client_ip >= inet '192.168.100.255') -- Erik
Re: CREATE TRIGGER documentation inconsistensies
> On 24/08/2023 09:50 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/sql-createtrigger.html > Description: > > In PostgreSQL 14 and 15 (previous versions I've not checked) is stated that > CREATE TRIGGER statement is as follows: > > CREATE [OR REPLACE] [CONSTRAINT] TRIGGER > > however while CREATE TRIGGER works correctly the CREATE OR REPLACE TRIGGER > construction leads to > > SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "TRIGGER" > > looks like CREATE TRIGGER doesn't support OR REPLACE, but documentation is > then incorrect. Works for me on 14.9 and 15.4: BEGIN; CREATE TABLE mytbl (f1 int); CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RETURN null; END $$; CREATE OR REPLACE TRIGGER mytrig BEFORE INSERT ON mytbl EXECUTE FUNCTION mytrigger(); COMMIT; CREATE OR REPLACE TRIGGER was added in 14. Did you test on an older version? -- Erik
Re: Wrong statement on the materialized views page
> On 21/08/2023 09:02 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/rules-materializedviews.html > Description: > > There might be an issue on this page: > https://www.postgresql.org/docs/current/rules-materializedviews.html > > I think the statement > CREATE TABLE mymatview AS SELECT * FROM mytab; > should actuall be > CREATE VIEW mymatview AS SELECT * FROM mytab; No, CREATE TABLE is correct in this case because that section is about the persistence of matview results like a table. -- Erik
Re: Change "two" to "three" for decades of development in history
> On 22/06/2023 23:00 CEST Bruce Momjian wrote: > > On Thu, Jun 22, 2023 at 01:47:16PM -0700, Kirk Parker wrote: > > > > I don't suppose DocBook has macro and system-variable capabilities? That > > could provide a set-and-forget solution to this? > > Uh, I am not aware of any. An SGML entity [0] or an xsltproc stringparam [1] looks viable. Question is how to calculate the number of decades in the Makefile. It's trivial in SQL :) =# select extract(decade from now()) - extract(decade from date '1986-01-01'); ?column? -- 4 (1 row) =# select extract(day from now() - date '1986-01-01') / 365.25 / 10; ?column? 3.7472963723477071 (1 row) [0] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/Makefile;h=71cbef230f34a6ad6b41a667c7d2977fb6560378;hb=HEAD#l98 [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/Makefile;h=71cbef230f34a6ad6b41a667c7d2977fb6560378;hb=HEAD#l56 -- Erik
Re: large scale reliable software system
I recommend posting this to pgsql-admin or pgsql-general instead of pgsql-docs to get more responses. Besides that, pgsql-docs is for discussions on the Postgres docs. > On 21/06/2023 18:52 CEST B M wrote: > > I would like to know your advices to develop a large scale reliable software > system using PostgreSQL in the back-end, This is very general but the following points hopefully lead you in the right direction: > through which i can share the storage with the different system users where > they login to the system through the web application front-end with different > passwords and usernames , save the privacy of each user data, improve overall > system security The OWASP Cheat Sheets give advice on securing web applications: https://cheatsheetseries.owasp.org/ > and performance, achieve fast response, My general advice: design and implement for correctness, then measure and optimize. For performance, look into high availability: https://www.postgresql.org/docs/current/high-availability.html > make backups and save the stored data from loss. Use pgBackRest or pgbarman with WAL streaming to reduce the recovery point objective (the maximum period in which data might be lost). > The system will be hosted on a cloud. Some cloud providers offer Postgres instances including backups, e.g. Google Cloud Platform which I'm looking into right now. -- Erik
Re: Further clarification in documentation: No deletion of unreferenced large objects
> On 12/06/2023 15:48 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/datatype-binary.html > Description: > > When deleting a row that references (contains) a large object, I think that > most users expect the DBMS to take care of the, now unreferenced, BLOB. It's > good to know that PostgreSQL handles this differently and that one must > periodically use vacuumlo to avoid BLOB data piling up. At least, that's > what I understood. > > I think a small paragraph in the documentation about this would help many > people. I can help with writing, if needed. bytea and large objects are two different ways of storing binary data. vacuumlo does not apply to bytea. Extension lo also mentions the issue with orphan objects already: https://www.postgresql.org/docs/15/lo.html Some further info on bytea vs large objects: https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/ -- Erik
Re: pg data type bool is the boolean ，but in the documents there is no description about " bool is equal to boolean”
> On 04/06/2023 17:04 CEST yanliang lei wrote: > > bool datatype is equal to boolean datatyp, but the documents > (https://www.postgresql.org/docs/15/datatype-boolean.html) there is no > description about " bool dataype is equal to boolean datatype ” The type aliases are listed in table 8.1: https://www.postgresql.org/docs/15/datatype.html#DATATYPE-TABLE -- Erik
Re: 5.11.1 Table Partitioning Overview - "...the size of the table should exceed the physical memory.."
> On 30/05/2023 07:06 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/ddl-partitioning.html > Description: > > Is this rule of thumb a typo? > > "The exact point at which a table will benefit from partitioning depends on > the application, although a rule of thumb is that the size of the table > should exceed the physical memory of the database server." > > I believe it should say the "a rule of thumb is that the size of the table > should NOT exceed the physical memory of the database server." That rule of thumb is correct (or at least not wrong). The reasoning behind it is that table partitioning provides a benefit if individual partitions fit into physical memory and queries target just a few partitions instead of the entire table. Subsequent queries will benefit if the necessary tables have already been loaded into memory. If a table does not fit into memory, Postgres will have to constantly swap out memory. Also work_mem may be exceeded sooner with larger tables and Postgres has to eventually write temporary files to disk to store intermediate results, e.g. for sorting and hashing. Physical memory means RAM, not disk, if that's your issue here. -- Erik
Re: Cross-Product JOIN?
> On 25/05/2023 23:46 CEST Laurenz Albe wrote:
>
> On Thu, 2023-05-25 at 08:59 +0200, Erik Wienhold wrote:
> > > Since we are talking about Cartesian products: is the term "Cartesian
> > > join"
> > > used anywhere?
> >
> > Not in the docs:
>
> I was trying to be funny. The Cartesian product of {Cartesian,cross} and
> {product,join} would be {Cartesian product,Cartesian join,cross product,cross
> join}.
And someone beat you to it and apparently planted the joke in the codebase.
(Good one, but unfortunately hard to tell apart from a serious question.)
--
Erik
Re: Cross-Product JOIN?
> On 25/05/2023 06:52 CEST Laurenz Albe wrote: > > On Wed, 2023-05-24 at 21:10 +0200, Erik Wienhold wrote: > > > On 24/05/2023 15:46 CEST Erik Wienhold wrote: > > > > > > Personally, I think it should read cartesian product because cross > > > product is > > > an overloaded term and cartesian product is used more often in the > > > documentation > > > overall. > > > > > > But the same page [0] also uses cross product when talking about grouping > > > sets. > > > > > > [0] https://www.postgresql.org/docs/15/queries-table-expressions.html > > > > Here's a patch that fixes those two places. > > +1 > > "Cross product" seems to be a misbegotten hybrid of "cross join" and > "Cartesian product". Cross product can mean Cartesian product depending on the context. [0] > Since we are talking about Cartesian products: is the term "Cartesian join" > used anywhere? Not in the docs: $ git grep -iw cartesian -- doc doc/src/sgml/cube.sgml:the lower-dimensional one is assumed to be a Cartesian projection, i. e., having zeroes doc/src/sgml/perform.sgml: would be inefficient, since the full Cartesian product of A and C doc/src/sgml/planstats.sgml: Cartesian product of the two inputs, multiplied by the doc/src/sgml/queries.sgml:(that is, the Cartesian product of their rows is formed; see below). doc/src/sgml/queries.sgml:T2 (i.e., a Cartesian product), doc/src/sgml/ref/select.sgml:specified, the result is the Cartesian product (cross join) of all doc/src/sgml/ref/select.sgml:Cartesian product. doc/src/sgml/ref/select.sgml:Cartesian product (i.e., all combined rows that pass its join doc/src/sgml/ref/select.sgml:They produce a simple Cartesian product, the same result as you get from but in a few comments: $ git grep -i 'cartesian\(\s\+join\|\s*$\)' src/backend/optimizer/geqo/geqo_eval.c: * a cartesian join to be performed. When force is false, do only src/backend/optimizer/path/joinrels.c: * upper level, we must be willing to make a cartesian join of a and b; src/backend/optimizer/path/joinrels.c: * cartesian joins in this case (no bushy). src/test/regress/expected/join.out:-- test for ability to use a cartesian join when necessary src/test/regress/sql/join.sql:-- test for ability to use a cartesian join when necessary [0] https://mathworld.wolfram.com/CartesianProduct.html -- Erik
Re: Cross-Product JOIN?
> On 24/05/2023 15:46 CEST Erik Wienhold wrote: > > Personally, I think it should read cartesian product because cross product is > an overloaded term and cartesian product is used more often in the > documentation > overall. > > But the same page [0] also uses cross product when talking about grouping > sets. > > [0] https://www.postgresql.org/docs/15/queries-table-expressions.html Here's a patch that fixes those two places. -- ErikFrom b23bd267ac9c4e79efb472c08dc35737bc8e9ca6 Mon Sep 17 00:00:00 2001 From: Erik Wienhold Date: Wed, 24 May 2023 20:21:47 +0200 Subject: [PATCH] Use Cartesian product consistently in docs Fixes two places in the docs that use "cross product" instead of the more common "Cartesian product", used throughout the rest of the docs. For the NATURAL JOIN without common column names it is easiest to refer to CROSS JOIN which is already described as the Cartesian product. --- doc/src/sgml/queries.sgml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 6986ec5c92..a75a241d62 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -392,7 +392,7 @@ FROM table_reference , table_r input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like -JOIN ... ON TRUE, producing a cross-product join. +CROSS JOIN. @@ -1362,7 +1362,7 @@ GROUPING SETS ( If multiple grouping items are specified in a single GROUP BY -clause, then the final list of grouping sets is the cross product of the +clause, then the final list of grouping sets is the Cartesian product of the individual items. For example: GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e)) -- 2.40.1
Re: Cross-Product JOIN?
> On 24/05/2023 10:22 CEST Atomic_Sheep wrote: > > "Finally, NATURAL is a shorthand form of USING: it forms a USING list > consisting of all column > names that appear in both input tables. As with USING, these columns > appear only once in the > output table. If there are no common column names, NATURAL JOIN > behaves like JOIN ... > ON TRUE, producing a cross-product join." > > Did it mean cartesian product and not cross-product? Cross product means cartesian product in this context. So technically correct. Personally, I think it should read cartesian product because cross product is an overloaded term and cartesian product is used more often in the documentation overall. But the same page [0] also uses cross product when talking about grouping sets. The source code uses cross product in a couple of comments, though. [0] https://www.postgresql.org/docs/15/queries-table-expressions.html -- Erik
Re: Correction: Postgres emum documentation 8.7.4 should read 63 characters (instead of bytes)
> On 01/04/2023 00:17 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/datatype-enum.html > Description: > > Postgres documentation, section 8.7.4 gives the limit on enum labels as 63 > bytes. > Testing this with an oversized string gives the following error: > SQL Error [42602]: ERROR: invalid enum label > Detail: Labels must be 63 characters or less. The documentation is correct. NAMEDATALEN is in bytes not characters. The error message was fixed to say bytes in v14. https://www.postgresql.org/message-id/flat/CAB8KJ%3DitZEJ7C9BacTHSYgeUysH4xx8wDiOnyppnSLyn6-g%2BBw%40mail.gmail.com -- Erik
Re: Split_Part w/negative integer does not work
> On 24/03/2023 21:45 CET PG Doc comments form wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/functions-string.html
> Description:
>
> When n is negative, an error occurs.
>
> Documentation
> split_part ( string text, delimiter text, n integer ) → text
> Splits string at occurrences of delimiter and returns the n'th field
> (counting from one), or when n is negative, returns the |n|'th-from-last
> field.
> split_part('abc~@~def~@~ghi', '~@~', 2) → def
> split_part('abc,def,ghi,jkl', ',', -2) → ghi
>
> Observation
> SELECT split_part('abc,def,ghi,jkl', ',', -1);
> generates
> ERROR: field position must be greater than zero
> SQL state: 22023
Works on 14 and 15. Support for negative indexes was added in 14. What
version do you use?
https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.12.5.8
--
Erik
Re: Publishing Postgres Manual as a book
> On 23/03/2023 04:34 CET Ian Lawrence Barwick wrote: > > 2023年3月23日(木) 12:23 Erik Wienhold : > > > > > On 23/03/2023 02:35 CET Siddharth Jain wrote: > > > > > > The Postgres Manual is available online as a PDF but I think having it in > > > a > > > book form could be useful to some. > > > > > > Is it legal for me to publish the manual as a book? > > > > IANAL, but I'd say yes as long as you don't take a fee. The legal notice of > > the documentation grants distribution of the documentation "for any purpose, > > without fee". > > Also not a lawyer, but the full sentence is: > > > Permission to use, copy, modify, and distribute this software and its > > documentation for any purpose, without fee, and without a written agreement > > is > > hereby granted > > which in my non-lawyerly interpretation means anyone copying, modifying and > distributing the documentation may do so without paying a fee. A distributor > could charge what they like. I know the fee clause from licenses such as Zero-Clause BSD, ISC License, and GPLv3. They state "with or without fee" or "gratis or for a fee" pertaining to the distribution. That's also how I interpret the fee clause in the PostgreSQL license with the difference that it does not permit taking a fee. > AFAIR the documentation has previously been published in book form, though as > it runs to about 3,000 A4 pages now, the logistics are non-trivial. It's also out of date pretty quickly with four releases a year. -- Erik
Re: Publishing Postgres Manual as a book
> On 23/03/2023 02:35 CET Siddharth Jain wrote: > > The Postgres Manual is available online as a PDF but I think having it in a > book form could be useful to some. > > Is it legal for me to publish the manual as a book? IANAL, but I'd say yes as long as you don't take a fee. The legal notice of the documentation grants distribution of the documentation "for any purpose, without fee". [0] Also mind the trademark policy. [1] [0] https://www.postgresql.org/docs/current/legalnotice.html [1] https://www.postgresql.org/about/policies/trademarks/ -- Erik
Re: 1.3 Creating a database - windows 10x64
> On 02/02/2023 01:52 CET PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/tutorial-createdb.html > Description: > > I installed pgsql with binary for windows from official website. > Going through the tutorial I found that I have no createdb command and for > me to create database I need to use sql statement CREATE DATABASE Installed via EnterpriseDB? The binaries are in C:/Program Files/PostgreSQL/15/bin/ which is not automatically added to environment variable %path%. -- Erik
