RE: typo in doc for "Miscellaneous Coding Conventions"

[tanghy.f...@fujitsu.com]

>"were not" is typical usage when stating a contrary-to-fact hypothetical.
> If the implementation (or you) didn’t do X, then Y bad thing could happen.

Really thanks for your kindly reply and explain.
Please forgive me for my poor English. Anyway, got it.

Regards,
Tang

Re: Error building for 64-bit Windows (10)

[Michael Paquier]

Hi Andrew,

On Tue, May 18, 2021 at 08:15:35AM -0500, Brian Ye wrote:
> Thanks for the reply.
> Yes I also saw that after installing 64-bit, the 32-bit "bin" and "include"
> directories were removed.
> I think the content of the "include" are common for both 32- and 64-bit.
> Windows can run both 32-bit and
> 64-bit binaries so removing these 2 directories is probably okay.  Just my
> guess.
> Again, thanks!
> Brian Ye

hamerkop is the only buildfarm member testing krb5 builds with MSVC on
Windows.  The path used in this case is c:\Program Files\MIT\Kerberos\
so the patch of this thread is going to break the builds there if this
relies on "inc/" for the include path.  Is this the original include
folder used for this installation of krb5?

Thanks,
--
Michael


signature.asc
Description: PGP signature

Improve documentation for pg_upgrade, standbys and rsync

[Laurenz Albe]

I revently tried to upgrade a standby following the documentation,
but I found it hard to understand, and it took me several tries to
get it right.  This is of course owing to my lack of expertise with
rsync, but I think the documentation and examples could be clearer.

I think it would be a good idea to recommend the --relative option
of rsync.

Here is a patch that does that, as well as update the versions in
the code samples to something more recent.  Also, I think it makes
sense to place the data directory in the sample in /var/lib/postgresql,
which is similar to what many people will have in real life.

Yours,
Laurenz Albe
From 0ce2de70811ced07eb75215197cbb83cd7a7d2b9 Mon Sep 17 00:00:00 2001
From: Laurenz Albe 
Date: Tue, 18 May 2021 19:42:55 +0200
Subject: [PATCH] Improve doc for pg_upgrade and standby servers

Recommend using the --relative option of rsync for clarity
and adapt the code samples accordingly.
Using relative paths makes clearer what is meant by "current
directory" and "remote_dir".
---
 doc/src/sgml/ref/pgupgrade.sgml | 20 ++--
 1 file changed, 10 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index a83c63cd98..f3d6df8877 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -528,26 +528,26 @@ pg_upgrade.exe
 
   
When using link mode, standby servers can be quickly upgraded using
-   rsync.  To accomplish this, from a directory on
+   rsync.  To accomplish this, change into a directory on
the primary server that is above the old and new database cluster
-   directories, run this on the primary for each standby
+   directories and run this on the primary for each standby
server:
 
 
-rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir
+rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative old_cluster new_cluster remote_dir
 
 
where old_cluster and new_cluster are relative
to the current directory on the primary, and remote_dir
-   is above the old and new cluster directories
-   on the standby.  The directory structure under the specified
-   directories on the primary and standbys must match.  Consult the
+   is the directory on the standby that corresponds to your current directory
+   on the primary.  The directory structure under the specified
+   directories on the primary and standbys must be the same.  Consult the
rsync manual page for details on specifying the
remote directory, e.g.,
 
 
-rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.5 \
-  /opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL
+rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative 9.6 13 \
+  standby.example.com:/var/lib/postgresql
 
 
You can verify what the command will do using
@@ -576,8 +576,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/Postgr
rsync command for each tablespace directory, e.g.:
 
 
-rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \
-  /vol1/pg_tblsp/PG_9.6_201608131 standby.example.com:/vol1/pg_tblsp
+rsync --archive --delete --hard-links --size-only --no-inc-recursive --relative \
+  PG_9.6_201608131 PG_13_202007201 standby.example.com:/vol1/tblsp
 
 
If you have relocated pg_wal outside the data
-- 
2.26.3

Re: typo in doc for "Miscellaneous Coding Conventions"

[Bryn Llewellyn]

t...@sss.pgh.pa.us wrote:

> PG Doc comments form  writes:
> 
>> small fix in description at [1] as follows
> 
>> -If that were not done interrupted code that's currently inspecting errno
>> might see the wrong value.
>> +If that was not done interrupted code that's currently inspecting errno
>> might see the wrong value.
> 
> The existing text is perfectly good English; your change
> makes it less so.  I'm afraid it's been too many years since
> high school English for me to remember the exact grammatical
> term for this, but "were not" is typical usage when stating
> a contrary-to-fact hypothetical.

(1) “IF bla bla…  THEN bla bla…”

It might be more words than the bare minimum. But it helps the user separate 
out the proposition and the consequence.

(2) This is the dreaded curse of the passive voice (“mistakes were made”). 
There are many cases where an active formulation is nicer. Anyway, you can 
sidestep lots of the conundrums, like the alternatives here pose, by standing 
back and finding a different way to make the point.

> If the implementation (or you) didn’t do X, then Y bad thing could happen.

Re: typo in doc for "Miscellaneous Coding Conventions"

[Tom Lane]

PG Doc comments form  writes:
> small fix in description at [1] as follows

> -If that were not done interrupted code that's currently inspecting errno
> might see the wrong value.
> +If that was not done interrupted code that's currently inspecting errno
> might see the wrong value.

The existing text is perfectly good English; your change
makes it less so.  I'm afraid it's been too many years since
high school English for me to remember the exact grammatical
term for this, but "were not" is typical usage when stating
a contrary-to-fact hypothetical.

regards, tom lane

Re: Error building for 64-bit Windows (10)

[Brian Ye]

Hi Michael,
Thanks for the reply.
Yes I also saw that after installing 64-bit, the 32-bit "bin" and "include"
directories were removed.
I think the content of the "include" are common for both 32- and 64-bit.
Windows can run both 32-bit and
64-bit binaries so removing these 2 directories is probably okay.  Just my
guess.
Again, thanks!
Brian Ye


On Tue, May 18, 2021 at 12:57 AM Michael Paquier 
wrote:

> On Mon, May 17, 2021 at 08:07:02PM +, PG Doc comments form wrote:
> > The Solution.pm file has the following lines:
> >   if ($self->{options}->{gss})
> >   {
> >   $proj->AddIncludeDir($self->{options}->{gss} . '\inc\krb5');
> >   $proj->AddLibrary($self->{options}->{gss} .
> '\lib\i386\krb5_32.lib');
> >   $proj->AddLibrary($self->{options}->{gss} .
> > '\lib\i386\comerr32.lib');
> >   $proj->AddLibrary($self->{options}->{gss} .
> > '\lib\i386\gssapi32.lib');
> >   }
> > I had to change them to the following or the compiling failed:
> >   if ($self->{options}->{gss})
> >   {
> >   $proj->AddIncludeDir($self->{options}->{gss} . '\include');
> >   $proj->AddIncludeDir($self->{options}->{gss} .
> '\include\krb5');
> >   $proj->AddLibrary($self->{options}->{gss} .
> '\lib\amd64\krb5_64.lib');
> >   $proj->AddLibrary($self->{options}->{gss} .
> '\lib\amd64\comerr64.lib');
> >   $proj->AddLibrary($self->{options}->{gss} .
> '\lib\amd64\gssapi64.lib');
>
> Yes, you are right.  I have been playing with the deliverables we
> recommend in the docs as of [1], and there are a couple of gotchas
> here:
> - For the 32b and 64b MSI installer, the include path is not "inc",
> but "include".  So I could not get the installation on Win32 to work
> either on HEAD.
> - There is a sub-path called "include/krb5", which is not really
> necessary except if we use krb5.h, but we don't.  Upstream code
> recommends actually to use krb5/krb5.h, meaning that only "include/"
> would be sufficient.  Keeping "include/krb5/" around is not a big deal
> either.
>
> This has not been changed in ages, so perhaps few have bothered.
> Anyway, the attached patch fixes both the 32b and 64b builds for me.
> Another interesting thing is that the installation of krb5 for amd64
> and i386 cannot co-exist together, so installing one removes the
> second automatically.
>
> [1]: https://web.mit.edu/Kerberos/dist/index.html
> --
> Michael
>

typo in doc for "Miscellaneous Coding Conventions"

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/source-conventions.html
Description:

small fix in description at [1] as follows

-If that were not done interrupted code that's currently inspecting errno
might see the wrong value.
+If that was not done interrupted code that's currently inspecting errno
might see the wrong value.

[1]https://www.postgresql.org/docs/devel/source-conventions.html

Re: pgpool: APPARENT DEADLOCK!!! Complete Status: Managed Threads: 3 Active Threads: 3

[Jonathan S. Katz]

Hi Moin,

On 5/18/21 3:30 AM, Moin Akther wrote:
> Dear Team,
> 
>  
> 
> We are facing issue whenever application is connecting to pgpool 4^th Node.

This email address is for contributing to the PostgreSQL documentation[1].

The Pgpool project provides contact information on reporting issues here:

https://www.pgpool.net/mediawiki/index.php/Main_Page#Contacts

Thanks,

Jonathan

[1] https://www.postgresql.org/docs/



OpenPGP_signature
Description: OpenPGP digital signature

Re: more detailed description of tup_returned and tup_fetched

[Fujii Masao]

On 2021/05/18 18:23, Masahiro Ikeda wrote:



On 2021/05/18 16:01, Fujii Masao wrote:

On 2021/05/18 13:20, Masahiro Ikeda wrote:

Tid Range Scan increments the tup_returned, and
pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
Tid Range Scan is like sequential scan.


Yes, you're right. One interesting thing I found is;
when Tid Range Scan happens, seq_tup_read is incremented
but seq_scan is not. I'm not sure if this is expected behavior or not.


The following comment says that this behavior is expected. But, I agree it's
odd and it's natural both seq_tup_read and seq_scan are incremented at the
same time or not...

/*
  * Currently, we only have a stats counter for sequential heap scans (but
  * e.g for bitmap scans the underlying bitmap index scans will be counted,
  * and for sample scans we update stats for tuple fetches).
  */
if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
pgstat_count_heap_scan(scan->rs_base.rs_rd);



That's the reason why the document of
pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
sequential scans"


Regarding the original issue, as far as I understand correctly,

* pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
sum(pg_stat_all_indexes.idx_tup_read)
* pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)

But the counters for some system catalogs like pg_database shared
across all databases of a cluster are excluded from that calculation.
Is this my understanding right? If right, probably we can reuse
the existing descriptions for those counters to document
pg_stat_database counters. For example,


Yes, my understanding is same now.



pg_stat_database.tup_returned:> Number of live rows fetched by sequential and 
index scans in this database


I wonder "live rows fetched by index scans" may mislead. I think "live" means
it's not dead tuple and "rows" mean the tuple user want to get.

But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
scans on this index". There is no meaning of "live" and "rows", so I thought
it's better to distinguish them.

So, why don't you change to "Number of live rows fetched by sequential scans
and index entries returned by index scans in this database"?


Yes, LGTM.



pg_stat_database.tup_fetched:
Number of index entries returned by scans on indexes in this database

Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
pg_stat_database.tup_returned.


I was thinking that pg_stat_database.tup_fetched is the same as
the sum of pg_stat_all_tables.idx_tup_fetch. Because they both
are incremented by bitmap index scans, but pg_stat_all_indexes.idx_tup_read
is not.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Re: more detailed description of tup_returned and tup_fetched

[Masahiro Ikeda]

On 2021/05/18 16:01, Fujii Masao wrote:
> On 2021/05/18 13:20, Masahiro Ikeda wrote:
>> Tid Range Scan increments the tup_returned, and
>> pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok 
>> because
>> Tid Range Scan is like sequential scan.
> 
> Yes, you're right. One interesting thing I found is;
> when Tid Range Scan happens, seq_tup_read is incremented
> but seq_scan is not. I'm not sure if this is expected behavior or not.

The following comment says that this behavior is expected. But, I agree it's
odd and it's natural both seq_tup_read and seq_scan are incremented at the
same time or not...

/*
 * Currently, we only have a stats counter for sequential heap scans (but
 * e.g for bitmap scans the underlying bitmap index scans will be counted,
 * and for sample scans we update stats for tuple fetches).
 */
if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
pgstat_count_heap_scan(scan->rs_base.rs_rd);


>> That's the reason why the document of
>> pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
>> sequential scans"
> 
> Regarding the original issue, as far as I understand correctly,
> 
> * pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
> sum(pg_stat_all_indexes.idx_tup_read)
> * pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
> 
> But the counters for some system catalogs like pg_database shared
> across all databases of a cluster are excluded from that calculation.
> Is this my understanding right? If right, probably we can reuse
> the existing descriptions for those counters to document
> pg_stat_database counters. For example,

Yes, my understanding is same now.


> pg_stat_database.tup_returned:> Number of live rows fetched by sequential and 
> index scans in this database

I wonder "live rows fetched by index scans" may mislead. I think "live" means
it's not dead tuple and "rows" mean the tuple user want to get.

But, pg_stat_all_indexes.idx_tup_read says that "index entires returned by
scans on this index". There is no meaning of "live" and "rows", so I thought
it's better to distinguish them.

So, why don't you change to "Number of live rows fetched by sequential scans
and index entries returned by index scans in this database"?


> pg_stat_database.tup_fetched:
> Number of index entries returned by scans on indexes in this database
Is this the sum of pg_stat_all_indexes.idx_tup_read? This is accounted to
pg_stat_database.tup_returned.

"Number of live rows fetched by index scans in this database" seems to be 
correct.


Regards,
-- 
Masahiro Ikeda
NTT DATA CORPORATION

pgpool: APPARENT DEADLOCK!!! Complete Status: Managed Threads: 3 Active Threads: 3

[Moin Akther]

Dear Team,

We are facing issue whenever application is connecting to pgpool 4th Node.

We have 4 Applications and 4 pgpool nodes, at any point of time Application can 
able to connect only 3 pgpool nodes, when we are starting application services 
on 4 pgpool node we are getting below error messages on application logs and 
also psql command on pgpool is getting hanged..

Application Logs:

APPARENT DEADLOCK!!! Complete Status:
Managed Threads: 3
Active Threads: 3

APPARENT  DEADLOCK!!! Creating emergency threads for unassigned pending tasks !
[Timer-0] [WARN] [async.ThreadPoolAsynchronousRunner] – (in deadlocked 
PoolThread) failed to complete in maximum time 6000ms. Trying interrupt().






Thanks and Best Regards,

Moin Akther

PostgreSQL DBA

Email: moin...@hotmail.com

Re: more detailed description of tup_returned and tup_fetched

[Fujii Masao]

On 2021/05/18 13:20, Masahiro Ikeda wrote:



On 2021/05/17 20:46, Fujii Masao wrote:



On 2021/05/17 18:58, Masahiro Ikeda wrote:



On 2021/05/17 15:32, Fujii Masao wrote:



On 2021/05/14 17:00, Masahiro Ikeda wrote:

Hi,

I worried the difference between "tup_returned" and "tup_fetched" in
pg_stat_database. I assumed that "tup_returned" means the number of tuples
that returned to clients. Of course, this is wrong.


-   Number of rows returned by queries in this database
+   Number of live rows returned by sequential scans of queries in this
database

-   Number of rows fetched by queries in this database
+   Number of live rows fetched by index scan of queries in this database

I found the following comments in pgstat.h. So maybe even these
new descriptions are incorrect?

   * Note: for a table, tuples_returned is the number of tuples successfully
   * fetched by heap_getnext, while tuples_fetched is the number of tuples
   * successfully fetched by heap_fetch under the control of bitmap indexscans.
   * For an index, tuples_returned is the number of index entries returned by
   * the index AM, while tuples_fetched is the number of tuples successfully
   * fetched by heap_fetch under the control of simple indexscans for this
index.


Oh, Thanks!

I updated the sentences using the descriptions of
"pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
"pg_stat_all_index.idx_tup_read".

-   Number of rows returned by queries in this database
+   Number of rows returned by queries in this database. The rows
correspond to the live rows fetched by sequential scans and index entries
returned by scans on indexes


This is still not correct because this counter is incremented even when
other scan like TidScan happens?


Sorry, I couldn't find the way to increment tup_returned by TidScan.
Do you mean that Tid Range Scan increments the counter?


Yes, what I tried to mean is Tid Range Scan.



Tid Range Scan increments the tup_returned, and
pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
Tid Range Scan is like sequential scan.


Yes, you're right. One interesting thing I found is;
when Tid Range Scan happens, seq_tup_read is incremented
but seq_scan is not. I'm not sure if this is expected behavior or not.


That's the reason why the document of
pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
sequential scans"


Regarding the original issue, as far as I understand correctly,

* pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) + 
sum(pg_stat_all_indexes.idx_tup_read)
* pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)

But the counters for some system catalogs like pg_database shared
across all databases of a cluster are excluded from that calculation.
Is this my understanding right? If right, probably we can reuse
the existing descriptions for those counters to document
pg_stat_database counters. For example,

pg_stat_database.tup_returned:
Number of live rows fetched by sequential and index scans in this database

pg_stat_database.tup_fetched:
Number of index entries returned by scans on indexes in this database

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Re: Online Documentation Search Issue

[Laurenz Albe]

On Mon, 2021-05-17 at 16:14 +, Karoline Pauls wrote:
> 
> On 17 May 2021, 16:43, PG Doc comments form < nore...@postgresql.org> wrote:
> > Just an FYI, but I'm running into a somewhat annoying issue with the
> > documentation search that I'm not sure you're aware of.
> > Namely, searching for "BETWEEN" (upper or lowercase) yields zero results.
> > 
> > Trying a few different search queries right now, it appears I might be just
> > running into an issue where my query is a stopword, but in this case it's
> > particularly frustrating because (even once I realize that) there is no
> > obvious alternative search query. (I wouldn't have thought to search for
> > "Comparison Functions", the page where it's actually explained.)
> > 
> > Is there any way your search platform can be set to index all text that
> > appears in the documentation inside 'code' tags? Or could some sort of
> > suggestion bar be added to the results page so that if a query contains a
> > keyword (especially if it consists entirely of the keyword), a link is
> > presented to go to it's page?
> > 
> > Barring that, could searching a stopword trigger a message (and perhaps a
> > link to search it on Google) other than just "no results found"?
> 
> HAVING is another stop-word.

I find 518 lines with "between" in the documentation, so I am not sure if
removing that stopword would be beneficial.

But there is an index for the documentation:
https://www.postgresql.org/docs/current/bookindex.html#indexdiv-B
"BETWEEN" is in the index.

So is "HAVING".  Removing "HAVING" as a stopword would mean indexing
"have", and that is obviously not going to help anybody.

Yours,
Laurenz Albe