Re: Contradicting information on the "vacuum threshold"

[Erwin Brandstetter]

On 11 August 2017 at 23:54, Erwin Brandstetter  wrote:

> On the one hand the manual for autovacuum_analyze_threshold claims here:
>
> https://www.postgresql.org/docs/current/static/runtime-
> config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD
>
> > Specifies the minimum number of inserted, updated or deleted tuples
> needed to trigger an ANALYZE in any one table.
>
> Similar in pg_settings.short_desc:
>
> > Minimum number of tuple inserts, updates, or deletes prior to analyze.
>
> "Minimum" indicates "row_count >= vacuum threshold". (Well "prior" makes
> that less clear ..)
>
> On the other hand the manual explains here:
>
> https://www.postgresql.org/docs/current/static/routine-
> vacuuming.html#AUTOVACUUM
>
> > Otherwise, if the number of tuples obsoleted since the last VACUUM
> exceeds the "vacuum threshold", the table is vacuumed.
>
> "Exceeds" indicates "row_count > vacuum threshold".
>
> Actual test results seem to support "row_count > vacuum threshold". See
> test case here:
>
> https://dba.stackexchange.com/questions/181960/tiny-table-
> causes-extreme-performance-degradation-fixed-by-forced-
> vacuum-why/183283#183283
>
> I suggest to either clarify the manual or change the code to actually use
> >= instead of > ... and update the explanation for autovacuum accordingly.
>
> Same for autovacuum_vacuum_threshold.
>


Still incorrect in the manual. The specified number of obsoleted rows does
*not* yet trigger an ANALYZE. Only the next one does. Off-by-one error.

A simple fix could be to replace:
*minimum number of inserted, updated or deleted tuples *
with:
* number of inserted, updated or deleted tuples that must be exceeded*

Regards
Erwin

Enhancing protocol.sgml

[Tatsuo Ishii]

In libpq.sgml following is stated:

Before PostgreSQL protocol 3.0, it was 
necessary
for the application to explicitly send the two characters
\. as a final line to indicate to the server that it 
had
finished sending COPY data.  While this still works, 
it is deprecated and the
special meaning of \. can be expected to be removed 
in a
future release.  It is sufficient to call 
PQendcopy after
having sent the actual data.

I think this should be mentioned in protocol.sgml as well. Developers
who wish to develop programs that understands frontend/backend
protocol should be able to focus on protocol.sgml. Attached is a patch
for this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 46d7e19..a98e4af 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1154,6 +1154,20 @@ SELCT 1/0;
 (if successful) or ErrorResponse (if not).

 
+   
+ 
+   Before PostgreSQL protocol 3.0, it was necessary
+   for the application to explicitly send the two characters
+   \. as a final line to indicate to the server that it
+   had finished sending COPY data.  Programs
+   implementing COPY in protocol 3.0
+   including PostgreSQL need to check and
+   ignore
+   \. just before COPYDone message for backward
+   compatibility sake. This requirement may be removed in the future.
+ 
+   
+

 In the event of a backend-detected error during copy-in mode (including
 receipt of a CopyFail message), the backend will issue an ErrorResponse