== PostgreSQL Weekly News - December 2, 2018 ==

== PostgreSQL Weekly News - December 2, 2018 ==

== PostgreSQL Product News ==

pgCluu 2.8, a Perl program to audit PostgreSQL performance, released.
http://pgcluu.darold.net/

postgres_dba 4.0, the missing toolset for PostgreSQL DBAs, released.
https://github.com/NikolayS/postgres_dba/releases/tag/4.0

VOPS-2.0, a PostgreSQL extension for vectorized operations, released.
https://github.com/postgrespro/vops.git

pgAdmin4 3.6, a web- and native GUI control center for PostgreSQL, released.
https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_3_6.html

pg_probackup 2.0.25, a utility to manage backup and recovery of PostgreSQL
database clusters, released.
https://github.com/postgrespro/pg_probackup

pgbitmap, a space-optimised, non-sparse, bitmap type, released.
https://github.com/marcmunro/pgbitmap

== PostgreSQL Local ==

2Q PGConf will be on December 4-5, 2018 in Chicago, IL.
http://www.2qpgconf.com/

PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo,
Japan.
http://www.pgconf.asia/EN/2018/

FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 1st, 2019.
https://2019.fosdempgday.org/

Prague PostgreSQL Developer Day 2019 (P2D2 2019) is a two-day
conference that will be held on February 13-14, 2019 in Prague, Czech Republic.
The CfP is open until January 4, 2018 at https://p2d2.cz/callforpapers
http://www.p2d2.cz/

PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka.
The CfP at the site below is open through December 15, 2018.
http://pgconf.in/

pgDay Paris 2019 will be held in Paris, France on March 12, 2019
at 199bis rue Saint-Martin.
http://2019.pgday.paris/

PGConf APAC 2019 will be held in Singapore March 19-21, 2019.
http://2019.pgconfapac.org/

PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. Both the
CfP https://2019.pgday.it/en/blog/cfp and the Call for Workshops
https://2019.pgday.it/en/blog/cfw are openuntil January 15, 2019.
https://2019.pgday.it/en/

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david(at)fetter(dot)org(dot)

== Applied Patches ==

Michaël Paquier pushed:

- Fix regression test handling of test_decoding with MSVC. The set of scripts
in charge of running the regression tests for MSVC run currently under the
assumption that only $(top_builddir) can used in option values defined in
REGRESS_OPTS, and those options need to have a specific format as well to be
correctly parsed, so fix the Makefile values so as those are correctly set.
Per complains from buildfarm member dory and whelk, with some extra testing
done on my side with MSVC to check this patch.
https://git.postgresql.org/pg/commitdiff/3955cae0c5241f5a67b15a3da2cd524c3839f8c9

- Revert all new recent changes to add PGXS options for TAP and isolation A set
of failures in buildfarm machines are proving that this is not quite ready yet
because of another set of issues: - MSVC scripts assume that REGRESS_OPTS can
only use top_builddir. Some test suites actually finish by using top_srcdir,
like pg_stat_statements which cause the regression tests to never run. -
Trying to enforce top_builddir does not work either when using VPATH as this
is not recognized properly. - TAP tests of bloom are unstable on various
platforms, causing various. failures.
https://git.postgresql.org/pg/commitdiff/1d7dd18686d0373289a3bf2b3669edae20875cbc

- Revert "Fix typo in documentation of toast storage". This reverts commit
058ef3a, per complains from Magnus Hagander and Vik Fearing.
https://git.postgresql.org/pg/commitdiff/664f01b61326eebc0d7b4ce9c89b99d3f18c3b76

- Fix typo in documentation of toast storage. Author: Nawaz Ahmed Discussion:
https://postgr.es/m/154319327168.1315.1846953598601966513@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/058ef3a1a8469bbaae6b0e6647cd031b9ca71b2d

- Fix handling of synchronous replication for stopping WAL senders. This fixes
an oversight from c6c3334 which forgot that if a subset of WAL senders are
stopping and in a sync state, other WAL senders could still be waiting for a
WAL position to be synced while committing a transaction. However the subset
of stopping senders would not release waiters, potentially breaking
synchronous replication guarantees. This commit makes sure that even WAL
senders stopping are able to release waiters and are tracked properly. On
9.4, this can also trigger an assertion failure when setting for example
max_wal_senders to 1 where a WAL sender is not able to find itself as in
synchronous state when the instance stops. Reported-by: Paul Guo Author: Paul
Guo, Michael Paquier Discussion:
https://postgr.es/m/CAEET0ZEv8VFqT3C-cQm6byOB4r4VYWcef1J21dOX-gcVhCSpmA@mail.gmail.com
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/4c703369af3e34cd7289d99c1b6f10e3b3bbbb4d

- Add missing NO_INSTALLCHECK in commit_ts and test_rls_hooks. This bypasses
installcheck if specified, which makes sense for those modules as they require
non-default configuration, something which typical users don't have. Those
have been missing from the start, still no back-patch is done. This will be
used by an upcoming patch for MSVC scripts adding support for NO_INSTALLCHECK
as installcheck is the default mode for contrib and modules for performance
reasons in the buildfarm. Author: Michael Paquier Reviewed-by: Andrew Dunstan
Discussion: https://postgr.es/m/20181126054302.GI1776@paquier.xyz
https://git.postgresql.org/pg/commitdiff/d79fb5d2370cc9166029b7afd17a26355598730a

- Add support for NO_INSTALLCHECK in MSVC scripts. When fetching a list of
tests for a given extension in contrib/ or src/test/modules/, NO_INSTALLCHECK
now gets checked first. If present, an empty list of tests is returned to let
the caller know that tests for this module need to be bypassed. This actually
fixes a set of issues with MSVC with modules using REGRESS_OPTS, as an
incorrect parsing caused the launched command to eat the first test listed.
The actual effect on the tree is that several modules listed a single test, so
regressions have been running with no actual tests. pg_stat_statements,
test_rls_hooks and commit_ts were impacted by that. Some other modules like
test_decoding (or snapshot_too_old) don't use yet PGXS rules, but their
makefiles will soon be refactored with an upcoming patch. Author: Michael
Paquier Reviewed-by: Andrew Dunstan Discussion:
https://postgr.es/m/20181126054302.GI1776@paquier.xyz
https://git.postgresql.org/pg/commitdiff/431f1599a2b7ba3e42fc06bd5b0ff5735931f556

- Add pg_partition_tree to documentation index. This fixes an oversight from
d5eec4ee. Author: Michael Paquier Reviewed-by: Amit Langote Discussion:
https://postgr.es/m/20181129072719.GC9004@paquier.xyz
https://git.postgresql.org/pg/commitdiff/44e22647f82b6e42626ee658634443635ab7fca2

- Fix various checksum check problems for pg_verify_checksums and base backups.
Three issues are fixed in this patch: - Base backups forgot to ignore files
specific to EXEC_BACKEND, leading to spurious warnings when checksums are
enabled, per analysis from me. - pg_verify_checksums forgot about files
specific to EXEC_BACKEND, leading to failures of the tool on any such build,
particularly Windows. This error was originally found by newly-introduced TAP
tests in various buildfarm members using EXEC_BACKEND. - pg_verify_checksums
forgot to count for temporary files and temporary paths, which could be valid
relation files, without checksums, per report from Andres Freund. More tests
are added to cover this case. A new test case which emulates corruption for a
file in a different tablespace is added, coming from from Michael Banck, while
I have coded the main code and refactored the test code. Author: Michael
Banck, Michael Paquier Reviewed-by: Stephen Frost, David Steele Discussion:
https://postgr.es/m/20181021134206.GA14282@paquier.xyz
https://git.postgresql.org/pg/commitdiff/5c995139752f5d57c560a0708286b99f9ca2bddd

- Switch pg_verify_checksums back to a blacklist. This basically reverts commit
d55241af705667d4503638e3f77d3689fd6be31, leaving around a portion of the
regression tests still adapted with empty relation files, and corrupted cases.
This is also proving to be failing to check properly relation files located in
a non-default tablespace path. Per discussion with various folks, including
Stephen Frost, David Steele, Andres Freund, Michael Banck and myself.
Reported-by: Michael Banck Discussion:
https://postgr.es/m/20181021134206.GA14282@paquier.xyz Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/a1c91dd1108c2e6536935619341c79c238734f77

- Fix tablespace path TAP test of pg_verify_checksums for msys. TAP tests on
msys need to run with the DTK perl, which understands msys virtualized paths.
Postgres, however, does not understand such paths, so before a path can be
used safely with CREATE TABLESPACE, it needs to be translated into a path on
the underlying file system. Per report from buildfarm member jacana.
Suggested fix is from Andrew Dunstan. Discussion:
https://postgr.es/m/20181130053555.GF2267@paquier.xyz
https://git.postgresql.org/pg/commitdiff/7d4524aed3a4720086b2914ec22f1bbbc857ac44

Álvaro Herrera pushed:

- Clarify that cross-row constraints are unsupported. Maybe we'll implement
them later, or maybe not, but let's make the statu quo clear for now. Author:
Lætitia Avrot, Patrick Francelle Reviewers: too many to list Discussion:
https://postgr.es/m/CAB_COdhUuzNFOJfc7SNNso5rOuVA3ui93KMVunEM8Yih+K5A6A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/36d442a25a1aed39d2c7c3fb24c22640a9a6545d

- Fix sample output for hash_metapage_info query. One output column was
duplicated. Couldn't resist fixing the version number while at it.
Reported-by: Gianni Ciolli
https://git.postgresql.org/pg/commitdiff/67ed3b9d73abf7d151fe5dcf9e552564b35f2c54

- Add log_statement_sample_rate parameter. This allows to set a lower
log_min_duration_statement value without incurring excessive log traffic
(which reduces performance). This can be useful to analyze workloads with
lots of short queries. Author: Adrien Nayrat Reviewed-by: David Rowley, Vik
Fearing Discussion:
https://postgr.es/m/c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info
https://git.postgresql.org/pg/commitdiff/88bdbd3f746049834ae3cc972e6e650586ec3c9d

- Silence compiler warning. My original coding was questionable anyway.
Reported-by: Sergei Kornilov Discussion:
https://postgr.es/m/9645101543575886@myt6-27270b78ac4f.qloud-c.yandex.net
https://git.postgresql.org/pg/commitdiff/9dc122585551516309c9362e673effdbf3bd79bd

Tom Lane pushed:

- Fix breakage of "\pset format latex". Commit eaf746a5b unintentionally made
psql's "latex" output format inaccessible, since not only "latex" but all
abbreviations of it were considered ambiguous against "latex-longtable".
Let's go back to the longstanding behavior that all shortened versions mean
"latex", and you have to write at least "latex-" to get "latex-longtable".
This leaves the only difference from pre-v12 behavior being that "\pset format
a" is considered ambiguous. The fact that the regression tests didn't expose
this is pretty bad, but fixing it is material for a separate commit.
Discussion:
https://postgr.es/m/cb7e1caf-3ea6-450d-af28-f524903a030c@manitou-mail.org
https://git.postgresql.org/pg/commitdiff/a7eece4fc9a416129aa692271972be5a30daa68c

- Add CSV table output mode in psql. "\pset format csv", or --csv, selects
comma-separated values table format. This is compliant with RFC 4180, except
that we aren't too picky about whether the record separator is LF or CRLF;
also, the user may choose a field separator other than comma. This output
format is directly compatible with the server's COPY CSV format, and will also
be useful as input to other programs. It's considerably safer for that
purpose than the old recommendation to use "unaligned" format, since the
latter couldn't handle data containing the field separator character. Daniel
Vérité, reviewed by Fabien Coelho and David Fetter, some tweaking by me
Discussion:
https://postgr.es/m/a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org
https://git.postgresql.org/pg/commitdiff/aa2ba50c2c13dd35a95a91638aa0a2b88c6df1eb

- Avoid locale-dependent output in numericlocale check. I'd forgotten that in
the buildfarm, parts of the regression tests may run with psql exposed to a
non-default LC_NUMERIC setting. Hence we can't assume that C locale prevails,
nor is there any accessible way to force the setting for this single test
step. Lobotomize the test case added by commit 9a98984f4 so that it covers as
much as we can of print.c without having any locale-varying output.
https://git.postgresql.org/pg/commitdiff/95dcb8fc0580c7b1c5f480b3ecaf81c5fc9801ba

- Fix translation of special characters in psql's LaTeX output modes.
latex_escaped_print() mistranslated \ and failed to provide any translation
for # ^ and ~, all of which would typically lead to LaTeX document syntax
errors. In addition it didn't translate < > and |, which would typically
render as unexpected characters. To some extent this represents shortcomings
in ancient versions of LaTeX, which if memory serves had no easy way to render
these control characters as ASCII text. But that's been fixed for, um,
decades. In any case there is no value in emitting guaranteed-to-fail output
for these characters. Noted while fooling with test cases added by commit
9a98984f4. Back-patch the code change to all supported versions.
https://git.postgresql.org/pg/commitdiff/70d7e507ef9d380bd46345e984f069207de5e24e

- Ensure static libraries have correct mod time even if ranlib messes it up. In
at least Apple's version of ranlib, the output file is updated to have a mod
time equal to the max of the timestamps of its components, and that data only
has seconds precision. On a filesystem with sub-second file timestamp
precision --- say, APFS --- this can result in the finished static library
appearing older than its input files, which causes useless rebuilds and
possible outright failures in parallel makes. We've only seen this reported
in the field from people using Apple's ranlib with a non-Apple make, because
Apple's make doesn't know about sub-second timestamps either so it doesn't
decide rebuilds are needed. But Apple's ranlib presumably shares code with at
least some BSDen, so it's not that unlikely that the same problem could arise
elsewhere. To fix, just "touch" the output file after ranlib finishes. We
seem to need this in only one place. There are other calls of ranlib in our
makefiles, but they are working on intermediate files whose timestamps are not
actually important, or else on an installed static library for which
sub-second timestamp precision is unlikely to matter either. (Also, so far as
I can tell, Apple's ranlib doesn't mess up the file timestamp in the latter
usage anyhow.) In passing, change "ranlib" to "$(RANLIB)" in one place that
was bypassing the make macro for no good reason. Per bug #15525 from Jack
Kelly (via Alyssa Ross). Back-patch to all supported branches. Discussion:
https://postgr.es/m/15525-a30da084f17a1faa@postgresql.org
https://git.postgresql.org/pg/commitdiff/826eff57c4c23f77314ba7151d3dc506ce0fa24c

- Document handling of invalid/ambiguous timestamp input near DST boundaries.
The source code comments documented this, but the user-facing docs, not so
much. Add a section to Appendix B that discusses it. In passing, improve a
couple other things in Appendix B --- notably, a long-obsolete claim that time
zone abbreviations are looked up in a fixed table. Per bug #15527 from
Michael Davidson. Discussion:
https://postgr.es/m/15527-f1be0b4dc99ebbe7@postgresql.org
https://git.postgresql.org/pg/commitdiff/d328991578d9f637fd1866639bfcbdfff64aba75

- Add a --socketdir option to pg_upgrade. This allows control of the directory
in which the postmaster sockets are created for the temporary postmasters
started by pg_upgrade. The default location remains the current working
directory, which is typically fine, but if it is deeply nested then its
pathname might be too long to be a socket name. In passing, clean up some
messiness in pg_upgrade's option handling, particularly the confusing and
undocumented way that configuration-only datadirs were handled. And fix
check_required_directory's substantially under-baked cleanup of directory
pathnames. Daniel Gustafsson, reviewed by Hironobu Suzuki, some code cleanup
by me Discussion:
https://postgr.es/m/E72DD5C3-2268-48A5-A907-ED4B34BEC223@yesql.se
https://git.postgresql.org/pg/commitdiff/2d34ad84303181111c6f0747186857ff50106267

- Rename ecpg's various "extern.h" files to have distinct names. This should
reduce confusion, and in particular make it safe to copy typename.c into
preproc/ and compile it there. This doesn't affect anything outside ecpg, and
particularly not end users, because these files don't get installed; they just
exist to share declarations among the .c files of each subdirectory.
Discussion: https://postgr.es/m/31364.1543511708@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/3295f82022ff0da95c96d8ca763e1d8902345466

- Eliminate parallel-make hazard in ecpg/preproc. Re-making ecpglib's
typename.o is dangerous because another make thread could be doing that at the
same time. While we've not heard field complaints traceable to this, it seems
inevitable that it'd bite someone eventually. Instead, symlink typename.c
into the preproc directory and recompile it there. That file is small enough
that compiling it twice isn't much of a penalty. Furthermore, this way we get
a .o file that's made without shlib CFLAGS, which seems cleaner. This
requires adding more stuff to the module's -I list. The MSVC aspect of that
is untested, but I'm sure the buildfarm will tell me if I got it wrong. Per a
suggestion from Peter Eisentraut. Although this is theoretically a bug fix,
the lack of field reports makes me feel we needn't back-patch. Discussion:
https://postgr.es/m/31364.1543511708@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/29180e5d78e545472b1665b8d415e6917a5bf333

Andres Freund pushed:

- Fix pg_upgrade for oid removal. pg_upgrade previously copied
pg_largeobject_metadata over from the old cluster. That doesn't work, because
the table has oids before 578b229718. I missed that. As most pieces of
metadata for large objects already were dumped as DDL (except for comments
overwritten by pg_upgrade, due to the copy of pg_largeobject_metadata) it
seems reasonable to just also dump grants for large objects. If we ever
consider this a relevant performance problem, we'd need to fix the rest of the
already emitted DDL too. There's still an open discussion about whether we'll
want to force a specific ordering for the dumped objects, as currently
pg_largeobjects_metadata potentially has a different ordering before/after
pg_upgrade, which can make automated testing a bit harder. Reported-By:
Andrew Dunstan Author: Andres Freund Discussion:
https://postgr.es/m/91a8a980-41bc-412b-fba2-2ba71a141c2b@2ndQuadrant.com
https://git.postgresql.org/pg/commitdiff/12a53c732cedf83f70106b5605a5003c2000d7f4

- Fix typo introduced in 578b229718. Author: Andreas Karlsson Discussion:
https://postgr.es/m/0917c86f-e906-27c0-740e-abc581480823@proxel.se
https://git.postgresql.org/pg/commitdiff/54bb22f66af9bf4279812b4e71493d164fc38fd8

- Fix ac218aa4f6 to work on versions before 9.5. Unfortunately ac218aa4f6
missed the fact that a reference to 'pg_catalog.regnamespace'::regclass
wouldn't work before that type is known. Fix that, by replacing the regtype
usage with a join to pg_type. Reported-By: Tom Lane Author: Andres Freund
Discussion: https://postgr.es/m/8863.1543297423@sss.pgh.pa.us Backpatch: 9.5-,
like ac218aa4f6
https://git.postgresql.org/pg/commitdiff/4c8750a9cc3402e4d8ec0b47901c2f9ca416b718

- Fix jit compilation bug on wide tables. The function generated to perform JIT
compiled tuple deforming failed when HeapTupleHeader's t_hoff was bigger than
a signed int8. I'd failed to realize that LLVM's getelementptr would treat an
int8 index argument as signed, rather than unsigned. That means that a hoff
larger than 127 would result in a negative offset being applied. Fix that by
widening the index to 32bit. Add a testcase with a wide table. Don't drop it,
as it seems useful to verify other tools deal properly with wide tables.
Thanks to Justin Pryzby for both reporting a bug and then reducing it to a
reproducible testcase! Reported-By: Justin Pryzby Author: Andres Freund
Discussion: https://postgr.es/m/20181115223959.GB10913@telsasoft.com
Backpatch: 11, just as jit compilation was
https://git.postgresql.org/pg/commitdiff/b238527664ec6f6c9d00dba4cc2f3dab1c8b8b04

- Ensure consistent sort order of large objects in pg_dump. The primary purpose
of this commit is to ensure pg_upgrade tests yield comparable dumps pre/post
upgrade, which got broken by 12a53c732 / 578b229718, as the order in
pg_largeobject_metadata is likely to differ pre/post upgrade. It also seems
like a generally good idea to make sure such dumps are comparable, outside of
pg_upgrade tests. LO metadata already was already dumped in an ordered manner
as the metadata is dumped in a well defined order via
sortDumpableObjectsByTypeName() and sortDumpableObjects(). But large object
data is currently not tracked via that mechanism. As Tom points out it seems
possible that at some point dumpBlobs() was assumed to dump out objects in a
well defined order, due to the use of DISTINCT, which at that time only was
done using sorting. Per complaint from Andrew Dunstan and discussion with him
and Tom Lane. Author: Andres Freund Discussion:
https://postgr.es/m/2735.1543333649@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/471a7af585b123a8c00416eabbec927f6701583d

- Update pg_upgrade test for reg* to include regrole and regnamespace. When the
regrole (0c90f6769) and regnamespace (cb9fa802b) types were added in 9.5,
pg_upgrade's check for reg* types wasn't updated. While regrole currently is
safe, regnamespace is not. It seems unlikely that anybody uses regnamespace
inside catalog tables across a pg_upgrade, but the tests should be correct
nevertheless. While at it, reorder the types checked in the query to be
alphabetical. Otherwise it's annoying to compare existing and tested for
types. Author: Andres Freund Discussion:
https://postgr.es/m/037e152a-cb25-3bcb-4f35-bdc9988f8204@2ndQuadrant.com
Backpatch: 9.5-, as regrole/regnamespace
https://git.postgresql.org/pg/commitdiff/ac218aa4f6dff6dfb4bf2675b2d9f2f23da6a7c5

Bruce Momjian pushed:

- doc: fix wording for plpgsql, add "and". Reported-by: Anthony Greene
Discussion:
https://postgr.es/m/CAPRNmnsSZ4QL75FUjcS8ND_oV+WjgyPbZ4ch2RUwmW6PWzF38w@mail.gmail.com
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/7a9d6779d91f68d68bcd9a3a36aadbdba09b172c

- C comment: remove extra '*'. Reported-by: Etsuro Fujita Discussion:
https://postgr.es/m/5BFE34DE.1080404@lab.ntt.co.jp Author: Etsuro Fujita
Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/eae9143d9a70116a8ac4b3195454942140e5afb5

Peter Eisentraut pushed:

- Update ssl test certificates and keys. Debian testing and newer now require
that RSA and DHE keys are at least 2048 bit long and no longer allow SHA-1 for
signatures in certificates. This is currently causing the ssl tests to fail
there because the test certificates and keys have been created in violation of
those conditions. Update the parameters to create the test files and create a
new set of test files. Author: Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> Reported-by: Michael Paquier
<michael(at)paquier(dot)xyz> Discussion:
https://www.postgresql.org/message-id/flat/20180917131340.GE31460%40paquier.xyz
https://git.postgresql.org/pg/commitdiff/f17889b2214194d7bd33900509bf08959d5a7efa

- Only allow one recovery target setting. The previous recovery.conf regime
accepted multiple recovery_target* settings and used the last one. This does
not translate well to the general GUC system. Specifically, under
EXEC_BACKEND, the settings are written out not in any particular order, so the
order in which they were originally set is not available to new processes.
Rather than redesign the GUC system, it was decided to abandon the old
behavior and only allow one recovery target setting. A second setting will
cause an error. However, it is allowed to set the same parameter multiple
times or unset a parameter and set a different one. Discussion:
https://www.postgresql.org/message-id/flat/27802171543235530%40iva2-6ec8f0a6115e.qloud-c.yandex.net#701a59c837ad0bf8c244344aaf3ef5a4
https://git.postgresql.org/pg/commitdiff/f2cbffc7a6183a359b339cfed65e9d609de756f0

- doc: Add appendix detailing some limits of PostgreSQL. This used to be on the
web site but was removed. The documentation is a better place for it anyway.
Author: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> Reviewed-by: John Naylor
<jcnaylor(at)gmail(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/CAKJS1f_dKdejdKB94nKZC9S5NzB-UZRcAKkE84e=JEEecDuotg(at)mail(dot)gmail(dot)com/
https://git.postgresql.org/pg/commitdiff/68120427f4902c136066d10ac9da93355877e156

Thomas Munro pushed:

- Don't count zero-filled buffers as 'read' in EXPLAIN. If you extend a
relation, it should count as a block written, not read (we write a zero-filled
block). If you ask for a zero-filled buffer, it shouldn't be counted as read
or written. Later we might consider counting zero-filled buffers with a
separate counter, if they become more common due to future work. Author:
Thomas Munro Reviewed-by: Haribabu Kommi, Kyotaro Horiguchi, David Rowley
Discussion:
https://postgr.es/m/CAEepm%3D3JytB3KPpvSwXzkY%2Bdwc5zC8P8Lk7Nedkoci81_0E9rA%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d67dae036bd71afd2be7a2fafd7bc1ef38bfcdd1

- Don't set PAM_RHOST for Unix sockets. Since commit 2f1d2b7a we have set
PAM_RHOST to "[local]" for Unix sockets. This caused Linux PAM's libaudit
integration to make DNS requests for that name. It's not exactly clear what
value PAM_RHOST should have in that case, but it seems clear that we shouldn't
set it to an unresolvable name, so don't do that. Back-patch to 9.6. Bug
#15520. Author: Thomas Munro Reviewed-by: Peter Eisentraut Reported-by:
Albert Schabhuetl Discussion:
https://postgr.es/m/15520-4c266f986998e1c5%40postgresql.org
https://git.postgresql.org/pg/commitdiff/0f9cdd7dca694d487ab663d463b308919f591c02

- Fix minor typo in dsa.c. Author: Takeshi Ideriha Discussion:
https://postgr.es/m/4E72940DA2BF16479384A86D54D0988A6F3BF22D%40G01JPEXMBKW04
https://git.postgresql.org/pg/commitdiff/2ac180c2869706fa42b89ef10055a88cc4a7847b

Tomáš Vondra:

- Use wildcard to match parens after CREATE STATISTICS. CREATE STATISTICS
completion was checking manually for the start and end of the parenthesised
list of types. That works, but we now have a better way to do that as commit
121213d9d taught word_matches() to allow '*' in the middle of an alternative.
But it only applied that to tab completion for EXPLAIN, ANALYZE and VACUUM.
Use it for CREATE STATISTICS too. Author: Dagfinn Ilmari Mannsåker
Discussion:
https://www.postgresql.org/message-id/flat/d8jwooziy1s.fsf%40dalvik.ping.uio.no
https://git.postgresql.org/pg/commitdiff/d1ce4ed2d56e42531581bb83e769a8840c591e15

- Do not decode TOAST data for table rewrites. During table rewrites (VACUUM
FULL and CLUSTER), the main heap is logged using XLOG / FPI records, and thus
(correctly) ignored in decoding. But the associated TOAST table is WAL-logged
as plain INSERT records, and so was logically decoded and passed to reorder
buffer. That has severe consequences with TOAST tables of non-trivial size.
Firstly, reorder buffer has to keep all those changes, possibly spilling them
to a file, incurring I/O costs and disk space. Secondly, ReoderBufferCommit()
was stashing all those TOAST chunks into a hash table, which got discarded
only after processing the row from the main heap. But as the main heap is not
decoded for rewrites, this never happened, so all the TOAST data accumulated
in memory, resulting either in excessive memory consumption or OOM. The fix
is simple, as commit e9edc1ba already introduced infrastructure (namely
HEAP_INSERT_NO_LOGICAL flag) to skip logical decoding of TOAST tables, but it
only applied it to system tables. So simply use it for all TOAST data in
raw_heap_insert(). That would however solve only the memory consumption issue
- the TOAST changes would still be decoded and added to the reorder buffer,
and spilled to disk (although without TOAST tuple data, so much smaller). But
we can solve that by tweaking DecodeInsert() to just ignore such INSERT
records altogether, using XLH_INSERT_CONTAINS_NEW_TUPLE flag, instead of
skipping them later in ReorderBufferCommit(). Review: Masahiko Sawada
Discussion:
https://www.postgresql.org/message-id/flat/1a17c643-e9af-3dba-486b-fbe31bc1823a%402ndquadrant.com
Backpatch: 9.4-, where logical decoding was introduced
https://git.postgresql.org/pg/commitdiff/f69c959df0db311badf609bce3ad3efde2309389

Peter Geoghegan pushed:

- Have BufFileSize() ereport() on FileSize() failure. Move the responsibility
for checking for and reporting a failure from the only current BufFileSize()
caller, logtape.c, to BufFileSize() itself. Code within buffile.c is
generally responsible for interfacing with fd.c to report irrecoverable
failures. This seems like a convention that's worth sticking to.
Reorganizing things this way makes it easy to make the error message raised in
the event of BufFileSize() failure descriptive of the underlying problem.
We're now clear on the distinction between temporary file name and BufFile
name, and can show errno, confident that its value actually relates to the
error being reported. In passing, an existing, similar buffile.c ereport() +
errcode_for_file_access() site is changed to follow the same conventions. The
API of the function BufFileSize() is changed by this commit, despite already
being in a stable release (Postgres 11). This seems acceptable, since the
BufFileSize() ABI was changed by commit aa551830421, which hasn't made it into
a point release yet. Besides, it's difficult to imagine a third party
BufFileSize() caller not just raising an error anyway, since BufFile state
should be considered corrupt when BufFileSize() fails. Per complaint from Tom
Lane. Discussion: https://postgr.es/m/26974.1540826748@sss.pgh.pa.us
Backpatch: 11-, where shared BufFiles were introduced.
https://git.postgresql.org/pg/commitdiff/1a990b207b86967cc323c00adda21fef3b2cd63e

Amit Kapila pushed:

- Fix typo.
https://git.postgresql.org/pg/commitdiff/dcfdf56e89a8d0b41874d495a44a4485e341f8c8

== Pending Patches ==

Ami Langote sent in another revision of a patch to allow generalized expression
syntax for partition bounds.

Dmitry Dolgov sent in another revision of a patch to implement generic type
subscripting and use same as the implementation for arrays and JSONB.

Dmitry Dolgov sent in two more revisions of a patch to improve the algorithms
for partition-wise JOINs.

Nikita Glukhov sent in another revision of a patch to implement the JSON TABLE
part of SQL/JSON.

Andrey Borodin sent in a patch to implement different B-tree page layouts.

Andrey Borodin sent in another revision of a patch to implement covering GiST
indexes.

Sergei Kornilov sent in another revision of a patch to enable changing
walreceiver's primary_conninfo on line.

Kyotaro HORIGUCHI sent in three more revisions of a patch to enable tab
completion debugging in psql.

Tatsuro Yamada sent in two revisions of a patch to add tab completion for ALTER
INDEX|TABLE ALTER COLUMN SET STATISTICS to psql.

Surafel Temesgen sent in two more revisions of a patch to implement COPY ...
FROM ... FILTER.

Dagfinn Ilmari Mannsåker sent in a patch to improve psql's tab completion for
CREATE TABLE.

Amit Kapila and Dilip Kumar traded patches to implement UNDO logs.

John Naylor sent in another revision of a patch to add a pg_language lookup and
replace the /ad hoc/ format for conversion functions in the genbki
infrastructure.

Nikita Glukhov sent in another revision of a patch to implement the SQL/JSON
functions.

Amit Langote sent in a patch to change ExecRelationIsTargetRelation to
compare the scan relation's OID with that of the result relations instead of the
RT index.

Edmund Horner sent in another revision of a patch to improve TID scans.

Pavan Deolasee sent in another revision of a patch to implement MERGE.

Iwata Aya sent in another revision of a patch to implement a debug log for
libpq.

Kyotaro HORIGUCHI sent in another revision of a patch to implement the stats
collector based on shared memory instead of files.

Michaël Paquier sent in another revision of a patch to use durable_unlink for
.ready and .done files for WAL segment removal.

Michael Banck and Michaël Paquier traded patches to add TAP tests for
pg_verify_checksums.

Kyotaro HORIGUCHI sent in another revision of a patch to remove catcache entries
that haven't been used for a certain time, add a pg_stat_syscache view to
display syscache usage statistics, and make it possible to set GUCs remotely and
non-transactionally.

David Rowley sent in a patch to fix an issue that manifested as inadequate
executor locking of indexes.

Stephen Frost sent in a patch to fix a comment about how xlog compression works
with "holes" in the WAL.

Evgeniy Efimkin sent in another revision of a patch to add a FOR TABLES clause
to CREATE SUBSCRIPTION.

Peter Eisentraut sent in another revision of a patch to implement chained
transactions.

Etsuro Fujita sent in three revisions of a patch to fix an inefficiency in
costing aggregate pushdown paths.

Peter Eisentraut sent in two more revisions of a patch to add columns
clientserial and issuerdn to the pg_stat_ssl system view.

Christoph Berg sent in a patch to change the "checkpoint starting" message to
use "wal".

Nikhil Sontakke sent in another revision of a patch to implement logical
decoding for two-phase transactions.

Myungkyu Lim sent in another revision of a patch to add an entry creation
timestamp column to pg_stat_replication.

Tom Lane sent in another revision of a patch to get rid of empty jointrees.

Michaël Paquier sent in another revision of a patch to add PGXS options to
control TAP and isolation tests.

John Naylor sent in another revision of a patch to avoid creation of the free
space map for small tables.

Surafel Temesgen sent in two more revisions of a patch to implement FETCH FIRST
... PERCENT.

Marius Timmer sent in another revision of a patch to add a
clientcert=verify-full auth option.

Nikita Glukhov sent in another revision of a patch to implement
k-nearest-neighbor searches on B-tree indexes.

Alexander Kukushkin sent in another revision of a patch to account for
replication by reserving max_wal_senders slots in the ProcGlobal, which
guarantees that only the walsender process could use them.

Stas Kelvich sent in another revision of a patch to implement CSN global
snapshots.

Pavel Stěhule sent in two more revisions of a patch to implement default
namespaces for XPath expressions.

Álvaro Herrera sent in another revision of a patch to make it possible to point
foreign keys to partitioned tables.

Andrew Dunstan sent in another revision of a patch to add an --exclude-database
option to pg_dumpall.

Tom Lane sent in a patch to unify and clarify the descriptions of default
privileges.

Chengchao Yu sent in a patch to fix a deadlock issue in single-user mode when an
I/O failure occurs.

Alexander Lakhin sent in another revision of a patch to fix make
installcheck-world so it doesn't fail in a clean environment.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Thomas Munro sent in another revision of a patch to implement a synchronous
replay mode for avoiding stale reads on hot standbys.

Thomas Munro sent in a patch to fix DSA area handling.