Re: Empty values of pg_stats_ext when table is empty

[Bruce Momjian]

On Mon, Nov  1, 2021 at 01:23:01PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/14/view-pg-stats-ext.html
> Description:
> 
> When table mytable is being inherited it may not contain any data but still
> return row sets from the inheriting tables. Now doing CREATE STATISTICS on
> mytable will work but won't have any values.
> 
> This is very difficult to realize from a user's perspective and can be
> either considered a feature or a bug, i.e. being enhanced.
> 
> Given this is considered as a feature, I suggest to add a comment at the end
> of this documentation
> https://www.postgresql.org/docs/current/view-pg-stats-ext.html like this:
> 
> <<
> Note then there are no values when the table is empty which happens when
> there are tables which inherit from it.
> <<
> 
> I could also think of a run-time warning?

I talked to Tomas Vondra and he says there will be proper parent
statistics in PG 15, due to be released in the next 1-2 months.  Here is
the PG 15 release note item:

https://www.postgresql.org/docs/15/release-15.html

Allow extended statistics to record statistics for a parent with all its
children (Tomas Vondra, Justin Pryzby)

Regular statistics already tracked parent and parent/all-children
statistics separately.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

PostgreSQL 15 minor documentation improvements

[Максим Яблоков]

Hello!

I’ve noticed that some changes concerning replacement of archive_command 
by archive_library in the documentation for PostgreSQL 15 do not quite 
fit in:


1. https://www.postgresql.org/docs/15/continuous-archiving.html
Archiving of these files happens automatically since you have already 
configured archive_library. - what about using 
archive_command? Should it be "...since you have already configured 
archive_library or archive_command"?


2. The same page.
If the archive process has fallen behind because of failures of the 
archive library, it will keep retrying until the archive succeeds and 
the backup is complete. - again, what about archive command?


3. https://www.postgresql.org/docs/15/app-pgbasebackup.html

All WAL records required for the backup must contain sufficient 
full-page writes, which requires you to enable |full_page_writes| on the 
primary and not to use a tool in your |archive_library| to remove 
full-page writes from WAL files. - the same question here. I believe 
that we can write in general: and not to use a tool to remove full-page 
writes from WAL files (?).


I have prepared a small patch with possible changes of these places, and 
also a separate patch with some improvements concerning missed/inapt 
tags. Please have a look.


--
Best regards,
Maxim Yablokov
Technical writer-translator
Postgres Professional diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 73a774d3d7f..2a64f601ffe 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -945,11 +945,11 @@ SELECT * FROM pg_backup_stop(wait_for_archive => true);
  On a standby, archive_mode must be always in order
  for pg_backup_stop to wait.
  Archiving of these files happens automatically since you have
- already configured archive_library. In most cases this
- happens quickly, but you are advised to monitor your archive
+ already configured archive_library or archive_command.
+ In most cases this happens quickly, but you are advised to monitor your archive
  system to ensure there are no delays.
- If the archive process has fallen behind
- because of failures of the archive library, it will keep retrying
+ If the archive process has fallen behind because of failures of the archive library or
+ archive command, it will keep retrying
  until the archive succeeds and the backup is complete.
  If you wish to place a time limit on the execution of
  pg_backup_stop, set an appropriate
diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml
index 56ac7b754be..b0a18c6d489 100644
--- a/doc/src/sgml/ref/pg_basebackup.sgml
+++ b/doc/src/sgml/ref/pg_basebackup.sgml
@@ -102,8 +102,7 @@ PostgreSQL documentation
  
   All WAL records required for the backup must contain sufficient full-page writes,
   which requires you to enable full_page_writes on the primary and
-  not to use a tool in your archive_library to remove
-  full-page writes from WAL files.
+  not to use a tool to remove full-page writes from WAL files.
  
 

diff --git a/doc/src/sgml/adminpack.sgml b/doc/src/sgml/adminpack.sgml
index 40cab29b247..28ace574dd5 100644
--- a/doc/src/sgml/adminpack.sgml
+++ b/doc/src/sgml/adminpack.sgml
@@ -22,8 +22,9 @@
   functions in , which
   provide read-only access.)
   Only files within the database cluster directory can be accessed, unless the
-  user is a superuser or given privileges of one of the pg_read_server_files,
-  or pg_write_server_files roles, as appropriate for the function, but either a
+  user is a superuser or given privileges of one of the
+  pg_read_server_files or pg_write_server_files
+  roles, as appropriate for the function, but either a
   relative or absolute path is allowable.
  
 
diff --git a/doc/src/sgml/archive-modules.sgml b/doc/src/sgml/archive-modules.sgml
index ee70e922490..37b1fc061ed 100644
--- a/doc/src/sgml/archive-modules.sgml
+++ b/doc/src/sgml/archive-modules.sgml
@@ -112,8 +112,8 @@ typedef bool (*ArchiveFileCB) (const char *file, const char *path);
 was successfully archived, which may include recycling or removing the
 original WAL file.  If false is returned, the server will
 keep the original WAL file and retry archiving later.
-file will contain just the file name of the WAL file to
-archive, while path contains the full path of the WAL
+file will contain just the file name of the WAL file to
+archive, while path contains the full path of the WAL
 file (including the file name).

   
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 73a774d3d7f..4eab6411a4e 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -668,9 +668,10 @@ test ! -f /mnt/server/archivedir/000100A90065 && cp pg_wal/0

 If the archive function emits an ERROR or
 FATAL, the archiver process aborts and gets restarted by
-the po

Re: nicer examples for aggregate calls

[Bruce Momjian]

On Sat, Oct 23, 2021 at 01:51:48PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/14/tutorial-agg.html
> Description:
> 
> currently, all of the examples are very simple, like
> 
> SELECT city, max(temp_lo)
> FROM weather
> WHERE city LIKE 'S%'-- (1)
> GROUP BY city
> HAVING max(temp_lo) < 40;
> 
> this example would be more complex and would allow users to search for
> clause "filter":
> 
> Finally, if we only care about cities whose names begin with “S” and we want
> to calculate the number of observations in each city with temp_lo over 30,
> we might do:
> 
> SELECT city, max(temp_lo), count(*) filter (temp_lo>30), 
> FROM weather
> WHERE city LIKE 'S%'-- (1)
> GROUP BY city
> HAVING max(temp_lo) < 40;

Good idea.  We didn't support FILTER at the time this query was added.
Here is a patch which adds it.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index 6f31a5a1d6..8243bf51af 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -726,19 +726,20 @@ SELECT city, max(temp_lo)
 which gives us one output row per city.  Each aggregate result is
 computed over the table rows matching that city.
 We can filter these grouped
-rows using HAVING:
+rows using HAVING and the output count using
+FILTER:
 
 
-SELECT city, max(temp_lo)
+SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30)
 FROM weather
 GROUP BY city
 HAVING max(temp_lo) < 40;
 
 
 
-  city   | max
--+-
- Hayward |  37
+  city   | max | count
+-+-+---
+ Hayward |  37 | 5
 (1 row)
 
 
@@ -748,7 +749,7 @@ SELECT city, max(temp_lo)
 names begin with S, we might do:
 
 
-SELECT city, max(temp_lo)
+SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30)
 FROM weather
 WHERE city LIKE 'S%'-- 
 GROUP BY city

CREATE INDEX...USING

[Jeff Janes]

This was recently added to CREATE INDEX reference page:

+The optional USING clause specifies an index
+type as described in .  If not
+specified, a default index type will be used based on the
+data types of the columns.

But I think this is wrong, the default type is BTREE, it does not depend on
the data type.  Or at least, I've never witnessed the claimed behavior.
The claim also conflicts with what is said at
https://www.postgresql.org/docs/10/indexes-types.html

Cheers,

Jeff

Re: CREATE INDEX...USING

[Bruce Momjian]

On Wed, Aug 17, 2022 at 12:41:03PM -0400, Jeff Janes wrote:
> This was recently added to CREATE INDEX reference page:
> 
> +        The optional USING clause specifies an index
> +        type as described in .  If not
> +        specified, a default index type will be used based on the
> +        data types of the columns.
> 
> But I think this is wrong, the default type is BTREE, it does not depend on 
> the
> data type.  Or at least, I've never witnessed the claimed behavior.  The claim
> also conflicts with what is said at https://www.postgresql.org/docs/10/
> indexes-types.html

You are correct --- parser/gram.y has:

access_method_clause:
USING name  { $$ = $2; }
| /*EMPTY*/ { $$ = 
DEFAULT_INDEX_TYPE; }

and from include/catalog/index.h:

#define DEFAULT_INDEX_TYPE  "btree"

Patch attached.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 944fdb4b09..cb4b43f637 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -154,9 +154,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] 

 The optional USING clause specifies an index
-type as described in .  If not
-specified, a default index type will be used based on the
-data types of the columns.
+type as described in .  The
+default is btree.

   
  

CREATE STATISTICS and partitoins/inheritance

[Bruce Momjian]

In looking at CREATE STATISTICS, I was confused how inheritance and
partisions were handled, so I confirmed with Tomas that the ANALYZE
manual page accurately describes how extended statistics are handled
since PG 15, so I plan to apply this patch to the CREATE STATISTICS
docs.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

diff --git a/doc/src/sgml/ref/create_statistics.sgml b/doc/src/sgml/ref/create_statistics.sgml
index b847944f37..2a9a0bf932 100644
--- a/doc/src/sgml/ref/create_statistics.sgml
+++ b/doc/src/sgml/ref/create_statistics.sgml
@@ -146,7 +146,9 @@ CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_
 
  
   The name (optionally schema-qualified) of the table containing the
-  column(s) the statistics are computed on.
+  column(s) the statistics are computed on;  see  for an explanation of the handling of
+  inheritance and partitions.
  
 

Re: documentation describing the range of a number type 'integer' is incorrect

[Bruce Momjian]

On Fri, Oct 22, 2021 at 11:55:33AM -0700, Kevin Sweet wrote:
> The bugs team agreed that it's a bug. The documentation accurately represents
> the buggy code. I thought you might want to update the doc when the bug is
> fixed.

This was fixed in this commit:

commit 5fcf3945bd
Author: John Naylor 
Date:   Fri Jul 30 13:50:23 2021 -0400

Fix range check in ECPG numeric to int conversion

The previous coding guarded against -INT_MAX instead of INT_MIN,
leading to -2147483648 being rejected as out of range.

Per bug #17128 from Kevin Sweet

Discussion: 
https://www.postgresql.org/message-id/flat/17128-55a8a879727a3e3a%40postgresql.org
Reviewed-by: Tom Lane
Backpatch to all supported branches

This was backpatched to all branches, so any release after July 30, 2021
should have this fix.  I don't see any docs that need adjusting.

---

> 
> 
> On 10/22/21 6:43 AM, Alvaro Herrera wrote:
> 
> On 2021-Jul-29, PG Doc comments form wrote:
> 
> 
> https://www.postgresql.org/docs/13/datatype-numeric.html says that 
> the range
> of a numeric type integer is -2147483648 to +2147483647 but
> PGTYPESnumeric_to_int considers -2147483648 (a perfectly valid 32-bit
> integer) to be invalid because it compares to -INT_MAX instead of 
> INT_MIN or
> (-INT_MAX - 1). This goes back to the initial commit in the git repo 
> for
> src/interfaces/ecpg/pgtypeslib/numeric.c. And doc/src/sgml/ecpg.sgml
> documents the min being -INT_MAX.
> 
> This sounds like an ECPG bug, not a documentation problem.
> 
> 
> 

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Update documentation page for translators

[Bruce Momjian]

Is there a reason this patch was not applied?

---

On Fri, Nov 12, 2021 at 02:46:40PM +0100, Daniel Gustafsson wrote:
> > On 22 Oct 2021, at 13:12, PG Doc comments form  
> > wrote:
> > 
> > The following documentation comment has been logged on the website:
> > 
> > Page: https://www.postgresql.org/docs/11/nls-translator.html
> > Description:
> > 
> > In the documentation for translators
> > (https://www.postgresql.org/docs/11/nls-translator.html) it is mentioned to
> > use any text editor for translating .po files. It can be very error prone
> > with regular text editors, especiially for non-tech people such as
> > translators.
> 
> I think this makes sense, having done some translation work myself I agree 
> that
> the PO editors out there can help quite a bit.  Now, I'm sure anyone 
> interested
> is perfectly capable of Googling, but since this chapter is there to help
> potential or new contributors I think it's fine to lower the bar and be a bit
> more verbose.
> 
> I don't feel like linking to any specific tool though, so I just added a small
> sentence to indicate that options exist, and that we don't necessarily require
> anyone to use a text editor.
> 
> CC:ing developers who have spent time with translations for input.
> 
> --
> Daniel Gustafsson https://vmware.com/
> 

> diff --git a/doc/src/sgml/nls.sgml b/doc/src/sgml/nls.sgml
> index d49f44f3f2..0b3c638594 100644
> --- a/doc/src/sgml/nls.sgml
> +++ b/doc/src/sgml/nls.sgml
> @@ -202,7 +202,9 @@ make update-po
> Editing the PO Files
>  
> 
> -The PO files can be edited with a regular text editor.  The
> +The PO files can be edited with a regular text editor. There are also
> +several specialized editors for PO files which can help the process with
> +translation specific features.  The
>  translator should only change the area between the quotes after
>  the msgstr directive, add comments, and alter the fuzzy flag.
>  There is (unsurprisingly) a PO mode for Emacs, which I find quite


-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Update documentation page for translators

[Daniel Gustafsson]

> On 17 Aug 2022, at 22:56, Bruce Momjian  wrote:

> Is there a reason this patch was not applied?

Only that it fell of my radar, if you think it's adding value I'm happy to get
it done now.

--
Daniel Gustafsson   https://vmware.com/

Re: CREATE INDEX...USING

[Jeff Janes]

On Wed, Aug 17, 2022 at 2:58 PM Bruce Momjian  wrote:

> On Wed, Aug 17, 2022 at 12:41:03PM -0400, Jeff Janes wrote:
> > This was recently added to CREATE INDEX reference page:
> >
> > +The optional USING clause specifies an index
> > +type as described in .  If not
> > +specified, a default index type will be used based on the
> > +data types of the columns.
> >
> > But I think this is wrong, the default type is BTREE, it does not depend
> on the
> > data type.  Or at least, I've never witnessed the claimed behavior.  The
> claim
> > also conflicts with what is said at https://www.postgresql.org/docs/10/
> > indexes-types.html
>
> You are correct --- parser/gram.y has:
>
> access_method_clause:
> USING name  { $$ = $2; }
> | /*EMPTY*/ { $$ =
> DEFAULT_INDEX_TYPE; }
>
> and from include/catalog/index.h:
>
> #define DEFAULT_INDEX_TYPE  "btree"
>
> Patch attached.
>
>
>
That looks good to me.  But now looking over the linked pages more, it
seems like https://www.postgresql.org/docs/current/indexes-types.html was
never changed to admit the possibility of custom index access methods (like
bloom) and neither was the 'replaceable class="parameter">method' section
of https://www.postgresql.org/docs/current/sql-createindex.html.

Also, is it odd that we say essentially the same thing for literal USING as
we say for the replaceable /method/?

Cheers,

Jeff

Re: Update documentation page for translators

[Bruce Momjian]

On Wed, Aug 17, 2022 at 10:58:55PM +0200, Daniel Gustafsson wrote:
> > On 17 Aug 2022, at 22:56, Bruce Momjian  wrote:
> 
> > Is there a reason this patch was not applied?
> 
> Only that it fell of my radar, if you think it's adding value I'm happy to get
> it done now.

Sure, it looked useful to me too.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Update documentation page for translators

[Daniel Gustafsson]

> On 17 Aug 2022, at 23:24, Bruce Momjian  wrote:
> 
> On Wed, Aug 17, 2022 at 10:58:55PM +0200, Daniel Gustafsson wrote:
>>> On 17 Aug 2022, at 22:56, Bruce Momjian  wrote:
>> 
>>> Is there a reason this patch was not applied?
>> 
>> Only that it fell of my radar, if you think it's adding value I'm happy to 
>> get
>> it done now.
> 
> Sure, it looked useful to me too.

Done, thanks.

--
Daniel Gustafsson   https://vmware.com/

Re: CREATE INDEX...USING

[Bruce Momjian]

On Wed, Aug 17, 2022 at 05:11:23PM -0400, Jeff Janes wrote:
> That looks good to me.  But now looking over the linked pages more, it seems
> like https://www.postgresql.org/docs/current/indexes-types.html was never
> changed to admit the possibility of custom index access methods (like bloom)

Uh, bloom is in /contrib, so we wouldn't mention that in the main docs,
I think.  However, it might be nice to mention you can add others.

> and neither was the 'replaceable class="parameter">method' section of https://
> www.postgresql.org/docs/current/sql-createindex.html.

Yes, seems we should say that you can install your own methods that can
be used, e.g., bloom.

> Also, is it odd that we say essentially the same thing for literal USING as we
> say for the replaceable /method/?

Well, this is embarrassing.  Someone reported there was no mention of
USING in the CREATE INDEX docs, and I didn't see it either, so I added
it.

However, CREATE INDEX just lists the parameters, not the keywords, so it
was already there as 'method', as you mentioned above.  I will just
remove the USING section I recently added.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: PostgreSQL 15 minor documentation improvements

[Michael Paquier]

On Wed, Aug 17, 2022 at 06:09:13PM +0300, Максим Яблоков wrote:
> I have prepared a small patch with possible changes of these places, and
> also a separate patch with some improvements concerning missed/inapt tags.
> Please have a look.

archive_command is used only when archive_library is not set AFAIK,
but an archive_library could also freely use an archive_command if it
wishes to.  But, yes, I agree that the current wording in backup.sgml
is kind of confusing because of this reason, so I am fine to have a
reference to both archive_library *and* archive_command in this area
of the docs.

 All WAL records required for the backup must contain sufficient full-page 
writes,
 which requires you to enable full_page_writes on the 
primary and
-not to use a tool in your archive_library to remove
-full-page writes from WAL files.
+not to use a tool to remove full-page writes from WAL files.

Hmm.  My opinion here is to do a simplification, and remove simply the
last part of the paragraph about tools that manipulate WAL files
as the first sentence makes it clear, in my opinion, that if those
FPWs are not around the server could become kaput.

Most of the changes in PGSQL15_tags_fix.patch seem right to me.
Still, you'd better check that the docs compile, as of:

> --- a/doc/src/sgml/basebackup-to-shell.sgml
> +++ b/doc/src/sgml/basebackup-to-shell.sgml
> @@ -12,9 +12,9 @@
>called shell. This makes it possible to run
>pg_basebackup --target=shell or, depending on how this
>module is configured,
> -  pg_basebackup --target=shell:DETAIL_STRING, and cause
> -  a server command chosen by the server administrator to be executed for
> -  each tar archive generated by the backup process. The command will receive
> +  pg_basebackup 
> --target=shell:DETAIL_STRING,

I am pretty sure that this line is going to cause a compilation
failure of the docs.  Anyway, this should be use a  markup,
no?
--
Michael


signature.asc
Description: PGP signature