== PostgreSQL Weekly News - January 19, 2020 ==

== PostgreSQL Weekly News - January 19, 2020 ==

PGDay.IT 2020 will take place June 11-12 in Bergamo, Italy. The CfP
is open until February 8th, 2020 at midnight, as is the Call for Workshops.
https://2020.pgday.it/en/

== PostgreSQL Product News ==

gbouncer_fdw 0.2.0 released.
https://info.crunchydata.com/blog/making-pgbouncer-easier-to-monitor

check_pgbackrest 1.7, a Nagios-compatible monitor for pgBackRest, released.
https://github.com/dalibo/check_pgbackrest/releases

PostgreSQL TDE 12, a fork of PostgreSQL which offers transparent data encryption, released.
https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption
pgbouncer_fdw 0.2.0, a foreign data wrapper around pgbouncer, released.
https://info.crunchydata.com/blog/making-pgbouncer-easier-to-monitor

pgbouncer_wrapper 1.1.0, a system to enable SQL-based access to pgbouncer, released.
https://github.com/davidfetter/pgbouncer_wrapper

== PostgreSQL Jobs for January ==

http://archives.postgresql.org/pgsql-jobs/2020-01/

== PostgreSQL Local ==

PGDay SF will take place on January 21, 2020 at the Swedish American Hall in San
Francisco.
https://2020.pgdaysf.org/

pgDay Israel 2020 will take place on March 19, 2020 in Tel Aviv.
http://pgday.org.il/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace
Saint-Martin.
https://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020.

PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL(at)SCaLE is a two day, two track event which takes place on
March 5-6, 2020, at Pasadena Convention Center, as part of SCaLE 18X.
https://www.socallinuxexpo.org/scale/18x/postgresscale

The German-speaking PostgreSQL Conference 2020 will take place on May 15, 2019
in Stuttgart.

PGCon 2019 will take place in Ottawa on May 26-29, 2020. The CfP is open
through January 19, 2020 at http://www.pgcon.org/2020/papers.php
https://www.pgcon.org/2020/

== 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 ==

Tom Lane pushed:

- Fix edge-case crashes and misestimation in range containment selectivity. When
estimating the selectivity of "range_var <@ range_constant" or "range_var @>
range_constant", if the upper (or respectively lower) bound of the
range_constant was above the last bin of the range_var's histogram, the code
would access uninitialized memory and potentially crash (though it seems the
probability of a crash is quite low). Handle the endpoint cases explicitly to
fix that. While at it, be more paranoid about the possibility of getting NaN
or other silly results from the range type's subdiff function. And improve
some comments. Ordinarily we'd probably add a regression test case
demonstrating the bug in unpatched code. But it's too hard to get it to crash
reliably because of the uninitialized-memory dependence, so skip that. Per
bug #16122 from Adam Scott. It's been broken from the beginning, apparently,
so backpatch to all supported branches. Diagnosis by Michael Paquier, patch
by Andrey Borodin and Tom Lane. Discussion:
https://postgr.es/m/16122-eb35bc248c806c15@postgresql.org
https://git.postgresql.org/pg/commitdiff/652686a334b437f57f9bd0e3baa5dbd245a9e15d

- Reduce size of backend scanner's tables. Previously, the core scanner's
yy_transition[] array had 37045 elements. Since that number is larger than
INT16_MAX, Flex generated the array to contain 32-bit integers. By
reimplementing some of the bulkier scanner rules, this patch reduces the array
to 20495 elements. The much smaller total length, combined with the
consequent use of 16-bit integers for the array elements reduces the binary
size by over 200kB. This was accomplished in two ways: 1. Consolidate
handling of quote continuations into a new start condition, rather than
duplicating that logic for five different string types. 2. Treat Unicode
strings and identifiers followed by a UESCAPE sequence as three separate
tokens, rather than one. The logic to de-escape Unicode strings is moved to
the filter code in parser.c, which already had the ability to provide special
processing for token sequences. While we could have implemented the conversion
in the grammar, that approach was rejected for performance and maintainability
reasons. Performance in microbenchmarks of raw parsing seems equal or
slightly faster in most cases, and it's reasonable to expect that in
real-world usage (with more competition for the CPU cache) there will be a
larger win. The exception is UESCAPE sequences; lexing those is about 10%
slower, primarily because the scanner now has to be called three times rather
than one. This seems acceptable since that feature is very rarely used. The
psql and epcg lexers are likewise modified, primarily because we want to keep
them all in sync. Since those lexers don't use the space-hogging -CF option,
the space savings is much less, but it's still good for perhaps 10kB apiece.
While at it, merge the ecpg lexer's handling of C-style comments used in SQL
and in C. Those have different rules regarding nested comments, but since we
already have the ability to keep track of the previous start condition, we can
use that to handle both cases within a single start condition. This matches
the core scanner more closely. John Naylor Discussion:
https://postgr.es/m/CACPNZCvaoa3EgVWm5yZhcSTX6RAtaLgniCPcBVOCwm8h3xpWkw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/7f380c59f800f7e0fb49f45a6ff7787256851a59

- Restructure ALTER TABLE execution to fix assorted bugs. We've had numerous bug
reports about how (1) IF NOT EXISTS clauses in ALTER TABLE don't behave
as-expected, and (2) combining certain actions into one ALTER TABLE doesn't
work, though executing the same actions as separate statements does. This
patch cleans up all of the cases so far reported from the field, though there
are still some oddities associated with identity columns. The core problem
behind all of these bugs is that we do parse analysis of ALTER TABLE
subcommands too soon, before starting execution of the statement. The root of
the bugs in group (1) is that parse analysis schedules derived commands (such
as a CREATE SEQUENCE for a serial column) before it's known whether the IF NOT
EXISTS clause should cause a subcommand to be skipped. The root of the bugs
in group (2) is that earlier subcommands may change the catalog state that
later subcommands need to be parsed against. Hence, postpone parse analysis
of ALTER TABLE's subcommands, and do that one subcommand at a time, during
"phase 2" of ALTER TABLE which is the phase that does catalog rewrites. Thus
the catalog effects of earlier subcommands are already visible when we analyze
later ones. (The sole exception is that we do parse analysis for ALTER COLUMN
TYPE subcommands during phase 1, so that their USING expressions can be parsed
against the table's original state, which is what we need. Arguably, these
bugs stem from falsely concluding that because ALTER COLUMN TYPE must do early
parse analysis, every other command subtype can too.) This means that ALTER
TABLE itself must deal with execution of any non-ALTER-TABLE derived
statements that are generated by parse analysis. Add a suitable entry point to
utility.c to accept those recursive calls, and create a struct to pass through
the information needed by the recursive call, rather than making the argument
lists of AlterTable() and friends even longer. Getting this to work correctly
required a little bit of fiddling with the subcommand pass structure, in
particular breaking up AT_PASS_ADD_CONSTR into multiple passes. But otherwise
it's mostly a pretty straightforward application of the above ideas. Fixing
the residual issues for identity columns requires refactoring of where the
dependency link from an identity column to its sequence gets set up. So that
seems like suitable material for a separate patch, especially since this one
is pretty big already. Discussion:
https://postgr.es/m/10365.1558909428@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/1281a5c907b41e992a66deb13c3aa61888a62268

- Minor code beautification in regexp.c. Remove duplicated code (apparently
introduced by commit c8ea87e4b). Also get rid of some PG_USED_FOR_ASSERTS_ONLY
variables we don't really need to have. Li Japin, Tom Lane Discussion:
https://postgr.es/m/PS1PR0601MB3770A5595B6E5E3FD6F35724B6360@PS1PR0601MB3770.apcprd06.prod.outlook.com
https://git.postgresql.org/pg/commitdiff/0db7c67051806f28a9129d50695efc19372d3af2

- Move wchar.c and encnames.c to src/common/. Formerly, various frontend
directories symlinked these two sources and then built them locally. That's
an ancient, ugly hack, and we now have a much better way: put them into
libpgcommon. So do that. (The immediate motivation for this is the prospect
of having to introduce still more symlinking if we don't.) This commit moves
these two files absolutely verbatim, for ease of reviewing the git history.
There's some follow-on work to be done that will modify them a bit. Robert
Haas, Tom Lane Discussion:
https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/e6afa8918c461c1dd80c5063a950518fa4e950cd

- Update header comments for wchar.c and encnames.c. Bring these into common
style (including having proper copyright notices) and adjust their
self-declaration of where they live. Discussion:
https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/3d4cb5d6c180e38ebf84f6403cfa18c09d0b738e

- Rationalize code placement between wchar.c, encnames.c, and mbutils.c. Move
all the backend-only code that'd crept into wchar.c and encnames.c into
mbutils.c. To remove the last few #ifdef dependencies from wchar.c and
encnames.c, also make the following changes: * Adjust
get_encoding_name_for_icu to return NULL, not throw an error, for unsupported
encodings. Its sole caller can perfectly well throw an error instead. (While
at it, I also made this function and its sibling is_encoding_supported_by_icu
proof against out-of-range encoding IDs.) * Remove the overlength-name error
condition from pg_char_to_encoding. It's completely silly not to treat that
just like any other the-name-is-not-in-the-table case. Also, get rid of
pg_mic_mblen --- there's no obvious reason why conv.c shouldn't call
pg_mule_mblen instead. Other than that, this is just code movement and
comment-polishing with no functional changes. Notably, I reordered
declarations in pg_wchar.h to show which functions are frontend-accessible and
which are not. Discussion:
https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/5afaa2e42655811461044c4216e2f821cadc766d

- Repair more failures with SubPlans in multi-row VALUES lists. Commit 9b63c13f0
turns out to have been fundamentally misguided: the parent node's subPlan list
is by no means the only way in which a child SubPlan node can be hooked into
the outer execution state. As shown in bug #16213 from Matt Jibson, we can
also get short-lived tuple table slots added to the outer es_tupleTable list.
At this point I have little faith that there aren't other possible connections
as well; the long time it took to notice this problem shows that this isn't a
heavily-exercised situation. Therefore, revert that fix, returning to the
coding that passed a NULL parent plan pointer down to the transiently-built
subexpressions. That gives us a pretty good guarantee that they won't hook
into the outer executor state in any way. But then we need some other
solution to make SubPlans work. Adopt the solution speculated about in the
previous commit's log message: do expression initialization at plan startup
for just those VALUES rows containing SubPlans, abandoning the goal of
reclaiming memory intra-query for those rows. In practice it seems unlikely
that queries containing a vast number of VALUES rows would be using SubPlans
in them, so this should not give up much. (BTW, this test case also refutes
my claim in connection with the prior commit that the issue only arises with
use of LATERAL. That was just wrong: some variants of SubLink always produce
SubPlans.) As with previous patch, back-patch to all supported branches.
Discussion: https://postgr.es/m/16213-871ac3bc208ecf23@postgresql.org
https://git.postgresql.org/pg/commitdiff/41c6f9db25b5e3a8bb8afbb7d6715cff541fd41e

- Doc: rearrange the documentation of binary-string functions. Rather than
intermixing the discussion of text-string and binary-string functions, make a
clean break, moving all discussion of binary-string operations into section
9.5. This involves some duplication of function descriptions between 9.4 and
9.5, but it seems cleaner on the whole since the individual descriptions are
clearer (and on the other side of the coin, it gets rid of some duplicated
descriptions, too). Move the convert*/encode/decode functions to a separate
table, because they don't quite seem to fit under the heading of "binary
string functions". Also provide full documentation of the textual formats
supported by encode() and decode() (which was the original goal of this patch
series, many moons ago). Also move the table of built-in encoding conversions
out of section 9.4, where it no longer had any relevance whatsoever, and put
it into section 23.3 about character sets. I chose to put both that and table
23.2 (multibyte-translation-table) into a new <sect2> so as not to break up
the flow of discussion in 23.3.3. Also do a bunch of minor copy-editing on
the function descriptions in 9.4 and 9.5. Karl Pinc, reviewed by Fabien
Coelho, further hacking by me Discussion:
https://postgr.es/m/20190304163347.7bca4897@slate.meme.com
https://git.postgresql.org/pg/commitdiff/34a0a81bfb388504deaa51b16a8bb531b827e519

Tomáš Vondra pushed:

- Apply all available functional dependencies. When considering functional
dependencies during selectivity estimation, it's not necessary to bother with
selecting the best extended statistic object and then use just dependencies
from it. We can simply consider all applicable functional dependencies at
once. This means we need to deserialie all (applicable) dependencies before
applying them to the clauses. This is a bit more expensive than picking the
best statistics and deserializing dependencies for it. To minimize the
additional cost, we ignore statistics that are not applicable. Author: Tomas
Vondra Reviewed-by: Mark Dilger Discussion:
https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development
https://git.postgresql.org/pg/commitdiff/aaa6761876ba5b06a5c3fa914b2951ace1e31dee

- Apply multiple multivariate MCV lists when possible. Until now we've only used
a single multivariate MCV list per relation, covering the largest number of
clauses. So for example given a query SELECT * FROM t WHERE a = 1 AND b
=1 AND c = 1 AND d = 1 and extended statistics on (a,b) and (c,d), we'd only
pick and use one of them. This commit improves this by repeatedly picking and
applying the best statistics (matching the largest number of remaining
clauses) until no additional statistics is applicable. This greedy algorithm
is simple, but may not be optimal. A different choice of statistics may leave
fewer clauses unestimated and/or give better estimates for some other reason.
This can however happen only when there are overlapping statistics, and
selecting one makes it impossible to use the other. E.g. with statistics on
(a,b), (c,d), (b,c,d), we may pick either (a,b) and (c,d) or (b,c,d). But it's
not clear which option is the best one. We however assume cases like this are
rare, and the easiest solution is to define statistics covering the whole
group of correlated columns. In the future we might support overlapping stats,
using some of the clauses as conditions (in conditional probability sense).
Author: Tomas Vondra Reviewed-by: Mark Dilger, Kyotaro Horiguchi Discussion:
https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development
https://git.postgresql.org/pg/commitdiff/eae056c19ee8f5ebc45ac0fe13181f91c8791e00

- Allocate freechunks bitmap as part of SlabContext. The bitmap used by
SlabCheck to cross-check free chunks in a block used to be allocated for each
SlabCheck call, and was never freed. The memory leak could be fixed by simply
adding a pfree call, but it's actually a bad idea to do any allocations in
SlabCheck at all as it assumes the state of the memory management as a whole
is sane. So instead we allocate the bitmap as part of SlabContext, which
means we don't need to do any allocations in SlabCheck and the bitmap goes
away together with the SlabContext. Backpatch to 10, where the Slab context
was introduced. Author: Tomas Vondra Reported-by: Andres Freund Reviewed-by:
Tom Lane Backpatch-through: 10 Discussion:
https://www.postgresql.org/message-id/20200116044119.g45f7pmgz4jmodxj%40alap3.anarazel.de
https://git.postgresql.org/pg/commitdiff/543852fd8bf0adc56192aeb25ff83f1a12c30368

Amit Kapila pushed:

- Delete empty pages in each pass during GIST VACUUM. Earlier, we use to
postpone deleting empty pages till the second stage of vacuum to amortize the
cost of scanning internal pages. However, that can sometimes (say vacuum is
canceled or errored between first and second stage) delay the pages to be
recycled. Another thing is that to facilitate deleting empty pages in the
second stage, we need to share the information about internal and empty pages
between different stages of vacuum. It will be quite tricky to share this
information via DSM which is required for the upcoming parallel vacuum patch.
Also, it will bring the logic to reclaim deleted pages closer to nbtree where
we delete empty pages in each pass. Overall, the advantages of deleting empty
pages in each pass outweigh the advantages of postponing the same. Author:
Dilip Kumar, with changes by Amit Kapila Reviewed-by: Sawada Masahiko and Amit
Kapila Discussion:
https://postgr.es/m/CAA4eK1LGr+MN0xHZpJ2dfS8QNQ1a_aROKowZB+MPNep8FVtwAA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4e514c6180fbf71cf7a0171867c828c63afd1c37

- Fix typo. Reported-by: Antonin Houska Author: Antonin Houska
Backpatch-through: 11, where it was introduced Discussion:
https://postgr.es/m/2246.1578900133@antos
https://git.postgresql.org/pg/commitdiff/23d0dfa8fa016f7f8af25b1040d7a55ba77da6fc

- Revert test added by commit d207038053. This test was trying to test the
mechanism to release kernel FDs as needed to get us under the max_safe_fds
limit in case of spill files. To do that, it needs to set
max_files_per_process to a very low value which doesn't even permit starting
of the server in the case when there are a few already opened files. This
test also won't work on platforms where we use one FD per semaphore.
Backpatch-through: 10, till where this test was added Discussion:
https://postgr.es/m/CAA4eK1LHhERi06Q+MmP9qBXBBboi+7WV3910J0aUgz71LcnKAw@mail.gmail.com
https://postgr.es/m/6485.1578583522@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/ed7bb5c311ce059294807cf4abfa9406d95feec0

- Introduce IndexAM fields for parallel vacuum. Introduce new fields
amusemaintenanceworkmem and amparallelvacuumoptions in IndexAmRoutine for
parallel vacuum. The amusemaintenanceworkmem tells whether a particular
IndexAM uses maintenance_work_mem or not. This will help in controlling the
memory used by individual workers as otherwise, each worker can consume memory
equal to maintenance_work_mem. The amparallelvacuumoptions tell whether a
particular IndexAM participates in a parallel vacuum and if so in which phase
(bulkdelete, vacuumcleanup) of vacuum. Author: Masahiko Sawada and Amit
Kapila Reviewed-by: Dilip Kumar, Amit Kapila, Tomas Vondra and Robert Haas
Discussion:
https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com
https://postgr.es/m/CAA4eK1LmcD5aPogzwim5Nn58Ki+74a6Edghx4Wd8hAskvHaq5A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4d8a8d0c738410ec02aab46b1ebe1835365ad384

Andrew Dunstan pushed:

- Only superuser can set sslcert/sslkey in postgres_fdw user mappings. Othrwise
there is a security risk. Discussion:
https://postgr.es/m/20200109103014.GA4192@msg.df7cb.de
https://git.postgresql.org/pg/commitdiff/cebf9d6e6ee13cbf9f1a91ec633cf96780ffc985

- Add a non-strict version of jsonb_set. jsonb_set_lax() is the same as
jsonb_set, except that it takes and extra argument that specifies what to do
if the value argument is NULL. The default is 'use_json_null'. Other
possibilities are 'raise_exception', 'return_target' and 'delete_key', all
these behaviours having been suggested as reasonable by various users.
Discussion:
https://postgr.es/m/375873e2-c957-3a8d-64f9-26c43c2b16e7@2ndQuadrant.com
Reviewed by: Pavel Stehule
https://git.postgresql.org/pg/commitdiff/a83586b5543b948f9e621462537a7303b113c482

- bump catalog version as should have been done for jsonb_set_lax.
https://git.postgresql.org/pg/commitdiff/4b0e0f67f2f16c077b4e5c8a3d70c6af4355db09

Michaël Paquier pushed:

- Fix comment in heapam.c. Improvement per suggestion from Tom Lane. Author:
Daniel Gustafsson Discussion:
https://postgr.es/m/FED18699-4270-4778-8DA8-10F119A5ECF3@yesql.se
https://git.postgresql.org/pg/commitdiff/7689d907bbb177fa2a8f5aca3f968761dd16bf28

- Fix buggy logic in isTempNamespaceInUse(). The logic introduced in this
routine as of 246a6c8 would report an incorrect result when a session calls it
to check if the temporary namespace owned by the session is in use or not. It
is possible to optimize more the routine in this case to avoid a PGPROC
lookup, but let's keep the logic simple. As this routine is used only by
autovacuum for now, there were no live bugs, still let's be correct for any
future code involving it. Author: Michael Paquier Reviewed-by: Julien Rouhaud
Discussion: https://postgr.es/m/20200113093703.GA41902@paquier.xyz
Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/ac5bdf62617507b1942f6124a2696c04a16fca04

- Move OpenSSL routines for min/max protocol setting to src/common/. Two
routines have been added in OpenSSL 1.1.0 to set the protocol bounds allowed
within a given SSL context: - SSL_CTX_set_min_proto_version -
SSL_CTX_set_max_proto_version As Postgres supports OpenSSL down to 1.0.1 (as
of HEAD), equivalent replacements exist in the tree, which are only available
for the backend. A follow-up patch is planned to add control of the SSL
protocol bounds for libpq, so move those routines to src/common/ so as libpq
can use them. Author: Daniel Gustafsson Discussion:
https://postgr.es/m/4F246AE3-A7AE-471E-BD3D-C799D3748E03@yesql.se
https://git.postgresql.org/pg/commitdiff/f7cd5896a69621818189fbdd209fb2e1fc008102

- Add GUC checks for ssl_min_protocol_version and ssl_max_protocol_version.
Mixing incorrect bounds set in the SSL context leads to confusing error
messages generated by OpenSSL which are hard to act on. New checks are added
within the GUC machinery to improve the user experience as they apply to any
SSL implementation, not only OpenSSL, and doing the checks beforehand avoids
the creation of a SSL during a reload (or startup) which we know will never be
used anyway. Backpatch down to 12, as those parameters have been introduced
by e73e67c. Author: Michael Paquier Reviewed-by: Daniel Gustafsson
Discussion: https://postgr.es/m/20200114035420.GE1515@paquier.xyz
Backpatch-through: 12
https://git.postgresql.org/pg/commitdiff/41aadeeb124ee5f8e7d154a16a74d53286882b74

- Doc: Improve description of connection strings with Percent-encoding. Clarify
the description related to the use of characters which can be encoded, and add
an example. Author: Jobin Augustine Reviewed-by: Peter Eisentraut, Alvaro
Herrera, Heikki Linnakangas, Michael Paquier, Alex Shulgin Discussion:
https://postgr.es/m/CANaTPsrYgSgE2fuj3=4x=Jmx1c+NgkEDzftNknZbrMuqL+aBhQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/e0ed6817c0ee218a3681920807404603e042ff04

Peter Eisentraut pushed:

- Fix base backup with database OIDs larger than INT32_MAX. The use of pg_atoi()
for parsing a string into an Oid fails for values larger than INT32_MAX, since
OIDs are unsigned. Instead, use atooid(). While this has less error
checking, the contents of the data directory are expected to be trustworthy,
so we don't need to go out of our way to do full error checking. Discussion:
https://www.postgresql.org/message-id/flat/dea47fc8-6c89-a2b1-07e3-754ff1ab094b%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/259bbe177808986e5d226ea7ce5a1ebb74657791

- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION. Add an ALTER TABLE
subcommand for dropping the generated property from a column, per SQL
standard. Reviewed-by: Sergei Kornilov <sk(at)zsrv(dot)org> Discussion:
https://www.postgresql.org/message-id/flat/2f7f1d9c-946e-0453-d841-4f38eb9d69b6%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/f595117e24a79db6072979ab5a757431fd17232f

- walreceiver uses a temporary replication slot by default. If no permanent
replication slot is configured using primary_slot_name, the walreceiver now
creates and uses a temporary replication slot. A new setting
wal_receiver_create_temp_slot can be used to disable this behavior, for
example, if the remote instance is out of replication slots. Reviewed-by:
Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> Discussion:
https://www.postgresql.org/message-id/CA%2Bfd4k4dM0iEPLxyVyme2RAFsn8SUgrNtBJOu81YqTY4V%2BnqZA%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/329730827848f61eb8d353d5addcbd885fa823da

- Expose PQbackendPID() through walreceiver API. This will be used by a
subsequent patch. Reviewed-by: Masahiko Sawada
<masahiko(dot)sawada(at)2ndquadrant(dot)com> Discussion:
https://www.postgresql.org/message-id/CA%2Bfd4k4dM0iEPLxyVyme2RAFsn8SUgrNtBJOu81YqTY4V%2BnqZA%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/ee4ac46c8eb289bc200d0fd682e75d11b4c55b5a

- Fix compiler warning about format on Windows. On 64-bit Windows, pid_t is long
long int, so a %d format isn't enough.
https://git.postgresql.org/pg/commitdiff/fe233366f2e3ca44609c805ce0604c1e2122c3f9

- Remove libpq.rc, use win32ver.rc for libpq. For historical reasons, libpq used
a separate libpq.rc file for the Windows builds while all other components use
a common file win32ver.rc. With a bit of tweaking, the libpq build can also
use the win32ver.rc file. This removes a bit of duplicative code.
Reviewed-by: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Michael
Paquier <michael(at)paquier(dot)xyz> Discussion:
https://www.postgresql.org/message-id/flat/ad505e61-a923-e114-9f38-9867d161073f(at)2ndquadrant(dot)com
https://git.postgresql.org/pg/commitdiff/16a4a3d59cd5574fdc697ea16ef5692ce34c54d5

Dean Rasheed pushed:

- Make rewriter prevent auto-updates on views with conditional INSTEAD rules. A
view with conditional INSTEAD rules and no unconditional INSTEAD rules or
INSTEAD OF triggers is not auto-updatable. Previously we relied on a check in
the executor to catch this, but that's problematic since the planner may fail
to properly handle such a query and thus return a particularly unhelpful error
to the user, before reaching the executor check. Instead, trap this in the
rewriter and report the correct error there. Doing so also allows us to
include more useful error detail than the executor check can provide. This
doesn't change the existing behaviour of updatable views; it merely ensures
that useful error messages are reported when a view isn't updatable. Per
report from Pengzhou Tang, though not adopting that suggested fix. Back-patch
to all supported branches. Discussion:
https://postgr.es/m/CAG4reAQn+4xB6xHJqWdtE0ve_WqJkdyCV4P=trYr4Kn8_3_PEA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d751ba523546df2b2709c1ffd4d12d6a25e25bf6

Bruce Momjian pushed:

- tools/copyright.pl: skip copyright changes for *.key files. Reported-by:
Alvaro Herrera Discussion:
https://postgr.es/m/20200102184059.GA25435@alvherre.pgsql Backpatch-through:
master
https://git.postgresql.org/pg/commitdiff/7316f11be03863858fa7acb5eee97ec35c91fca4

- Revert copyright script changes to binary *.key files. This reverts part of
commit 7559d8ebfa. The copyright script has already been updated to skip
*.key files. Reported-by: Alvaro Herrera Discussion:
https://postgr.es/m/20200102184059.GA25435@alvherre.pgsql Backpatch-through:
master
https://git.postgresql.org/pg/commitdiff/344c26915136fa54c4a1bf54074ba15090b32721

- docs: change "default role" wording to "predefined role". The new wording was
determined to be more accurate. Also, update release note links that
reference these sections. Reported-by: rirans(at)comcast(dot)net Discussion:
https://postgr.es/m/157742545062.1149.11052653770497832538@wrigleys.postgresql.org
Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/0e936a2148472e6c364aee8c3e298dc16dc4240a

Álvaro Herrera pushed:

- Report progress of ANALYZE commands. This uses the progress reporting
infrastructure added by c16dc1aca5e0, adding support for ANALYZE.
Co-authored-by: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Co-authored-by:
Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp> Reviewed-by: Julien Rouhaud,
Robert Haas, Anthony Nowocien, Kyotaro Horiguchi, Vignesh C, Amit Langote
https://git.postgresql.org/pg/commitdiff/a166d408eb0b35023c169e765f4664c3b114b52e

- Set ReorderBufferTXN->final_lsn more eagerly. ... specifically, set it
incrementally as each individual change is spilled down to disk. This way, it
is set correctly when the transaction disappears without trace, ie. without
leaving an XACT_ABORT wal record. (This happens when the server crashes
midway through a transaction.) Failing to have final_lsn prevents
ReorderBufferRestoreCleanup() from working, since it needs the final_lsn in
order to know the endpoint of its iteration through spilled files. Commit
df9f682c7bf8 already tried to fix the problem, but it didn't set the final_lsn
in all cases. Revert that, since it's no longer needed. Author: Vignesh C
Reviewed-by: Amit Kapila, Dilip Kumar Discussion:
https://postgr.es/m/CALDaNm2CLk+K9JDwjYST0sPbGg5AQdvhUt0jbKyX_HdAE0jk3A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/15cac3a523cc06dba1331635f3f67445fa202a44

Robert Haas pushed:

- Fix problems with "read only query" checks, and refactor the code. Previously,
check_xact_readonly() was responsible for determining which types of queries
could not be run in a read-only transaction, standard_ProcessUtility() was
responsibility for prohibiting things which were allowed in read only
transactions but not in recovery, and utility commands were basically
prohibited in bulk in parallel mode by calls to CommandIsReadOnly() in
functions.c and spi.c. This situation was confusing and error-prone.
Accordingly, move all the checks to a new function
ClassifyUtilityCommandAsReadOnly(), which determines the degree to which a
given statement is read only. In the old code, check_xact_readonly()
inadvertently failed to handle several statement types that actually should
have been prohibited, specifically T_CreatePolicyStmt, T_AlterPolicyStmt,
T_CreateAmStmt, T_CreateStatsStmt, T_AlterStatsStmt, and T_AlterCollationStmt.
As a result, thes statements were erroneously allowed in read only
transactions, parallel queries, and standby operation. Generally, they would
fail anyway due to some lower-level error check, but we shouldn't rely on
that. In the new code structure, future omissions of this type should cause
ClassifyUtilityCommandAsReadOnly() to complain about an unrecognized node
type. As a fringe benefit, this means we can allow certain types of utility
commands in parallel mode, where it's safe to do so. This allows ALTER SYSTEM,
CALL, DO, CHECKPOINT, COPY FROM, EXPLAIN, and SHOW. It might be possible to
allow additional commands with more work and thought. Along the way, document
the thinking process behind the current set of checks, as per discussion
especially with Peter Eisentraut. There is some interest in revising some of
these rules, but that seems like a job for another patch. Patch by me,
reviewed by Tom Lane, Stephen Frost, and Peter Eisentraut. Discussion:
http://postgr.es/m/CA+TgmoZ_rLqJt5sYkvh+JpQnfX0Y+B2R+qfi820xNih6x-FQOQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2eb34ac369741c110b593e2dc2195c57d29ab8e8

Alexander Korotkov pushed:

- Avoid full scan of GIN indexes when possible. The strategy of GIN index scan
is driven by opclass-specific extract_query method. This method that needed
search mode is GIN_SEARCH_MODE_ALL. This mode means that matching tuple may
contain none of extracted entries. Simple example is '!term' tsquery, which
doesn't need any term to exist in matching tsvector. In order to handle such
scan key GIN calculates virtual entry, which contains all TIDs of all entries
of attribute. In fact this is full scan of index attribute. And typically
this is very slow, but allows to handle some queries correctly in GIN.
However, current algorithm calculate such virtual entry for each
GIN_SEARCH_MODE_ALL scan key even if they are multiple for the same attribute.
This is clearly not optimal. This commit improves the situation by
introduction of "exclude only" scan keys. Such scan keys are not capable to
return set of matching TIDs. Instead, they are capable only to filter TIDs
produced by normal scan keys. Therefore, each attribute should contain at
least one normal scan key, while rest of them may be "exclude only" if search
mode is GIN_SEARCH_MODE_ALL. The same optimization might be applied to the
whole scan, not per-attribute. But that leads to NULL values elimination
problem. There is trade-off between multiple possible ways to do this. We
probably want to do this later using some cost-based decision algorithm.
Discussion:
https://postgr.es/m/CAOBaU_YGP5-BEt5Cc0%3DzMve92vocPzD%2BXiZgiZs1kjY0cj%3DXBg%40mail.gmail.com
Author: Nikita Glukhov, Alexander Korotkov, Tom Lane, Julien Rouhaud
Reviewed-by: Julien Rouhaud, Tomas Vondra, Tom Lane
https://git.postgresql.org/pg/commitdiff/4b754d6c16e16cc1a1adf12ab0f48603069a0efd

Heikki Linnakangas pushed:

- Remove separate files for the initial contents of pg_(sh)description. This
data was only in separate files because it was the most convenient way to
handle it with a shell script. Now that we use a general-purpose programming
language, it's easy to assemble the data into the same format as the rest of
the catalogs and output it into postgres.bki. This allows removal of some
special-purpose code from initdb.c. Discussion:
https://www.postgresql.org/message-id/CACPNZCtVFtjHre6hg9dput0qRPp39pzuyA2A6BT8wdgrRy%2BQdA%40mail.gmail.com
Author: John Naylor
https://git.postgresql.org/pg/commitdiff/7aaefadaac6452b2e813fae4ea531cb12d022531

== Pending Patches ==

Justin Pryzby sent in a patch to clarify the way VACUUM VERBOSE is logged.

Justin Pryzby sent in another revision of a patch to use the correlation
statistic in costing bitmap scans.

KaiGai Kohei sent in another revision of a patch to add a
path_removal_decision_hook.

Krasiyan Andreev sent in another revision of a patch to allow distinct
aggregates within a window function.

Anastasia Lubennikova sent in another revision of a patch to add support
functions for btreeopclasses.

Álvaro Herrera sent in another revision of a patch to pg_dump to add FOREIGN to
ALTER statements, if appropriate.

Juan José Santamaría Flecha sent in two more revisions of a patch to allow
to_date() and to_timestamp() to accept localized names.

Michaël Paquier sent in a patch to fix isTempNamespaceInUse()'s handling of
MyBackendId.

Amit Kapila and Masahiko Sawada traded patches to implement block-level parallel
vacuum.

Tom Lane sent in another revision of a patch to fix psql's tab completion of
filenames.

Kyotaro HORIGUCHI sent in another revision of a patch to add a catcache
expiration feature.

Dilip Kumar sent in another revision of a patch to fix an infelicity between
logical_work_mem and logical streaming of large in-progress transactions.

Tomáš Vondra sent in a patch to improve the estimates of OR clauses, support
estimating clauses (Var op Var), and support extended statistics on expressions.

Nikita Glukhov sent in another revision of a patch to add support for
JSON_TABLE.

Andres Freund sent in a patch to update stringinfo to move more functions
inline, provide initStringInfoEx(), and remove in-core use of
appendStringInfoCharMacro(), update pqformat by moving functions for type
sending inline, and adding pq_begintypsend_ex(), annotating palloc() with malloc
and other compiler attributes, change {int4,int8}send to use pq_begintypsend_ex,
and make copy use appendBinaryStringInfoNT() for sending binary data.

Peter Eisentraut sent in another revision of a patch to fail if the PITR
recovery target is not reached.

Tom Lane sent in two revisions of a patch to remove the restriction that the
server encoding must be UTF-8 in order to write any Unicode escape with a value
outside the ASCII range.

Daniel Gustafsson and Michaël Paquier traded patches to set min/max TLS protocol
in clientside libpq.

KaiGai Kohei sent in another revision of a patch to implement TRUNCATE on
foreign tables.

Alexander Korotkov and Tom Lane traded patches to avoid full scan of GIN indexes
when possible.

Michaël Paquier sent in two revisions of a patch to improve errors when setting
incorrect bounds for SSL protocols.

David Fetter sent in three revisions of a patch to use compiler intrinsics for
bit ops in hash.

Kyotaro HORIGUCHI sent in three more revisions of a patch to rework the
WAL-skipping optimization.

Peter Geoghegan sent in another revision of a patch to add deduplication to
nbtree.

Paul Guo sent in another revision of a patch to support node initialization from
backup with tablespaces, add tests to replay create database operations on
standbys, and fix the replay of create database records on standbys.

Imai Yoshikazu sent in another revision of a patch to add pg_stat_waitaccum view
and change the measuring method of wait event time from INSTR_TIME to rdtsc.

Etsuro Fujita sent in another revision of a patch to improve the
partition-matching algorithm for partition-wise joins.

Peter Eisentraut sent in another revision of a patch to make the base backup
client an auxiliary backend process.

Maciek Sakrejda sent in another revision of a patch to merge explain worker
output.

Robert Haas sent in two revisions of a patch to make the backend JSON parser
work with front-end code by adjusting src/include/utils/jsonapi.h so it's not
backend-only, splitting the JSON lexer/parser from 'json' data type support,
removing jsonapi.c's lex_accept(), and returning errors rather than using
ereport().

Andres Freund sent in another revision of a patch to fix an aggregate crash.

Michaël Paquier sent in another revision of a patch to adjust dropping temp
schemas.

Justin Pryzby sent in a patch to add a doc review for ANALYZE progress.

Antonin Houska sent in another revision of a patch to introduce a RelInfoList
structure and use same to implement aggregate push-down.

Aleksey Kondratov sent in another revision of a patch to make physical slot
advance persistent.

Amit Khandekar sent in another revision of a patch to implement minimal logical
decoding on standbys.

Fujii Masao sent in another revision of a patch to ignore invalid pages.

Fujii Masao sent in another revision of a patch to add pg_file_sync() to
adminpack.

Asim R P sent in two revisions of a patch to fix an unnecessary delay in
streaming replication due to replay lag.

Tom Lane sent in another revision of a patch to fix for hashed subplan in
VALUES.

Heikki Linnakangas sent in another revision of a patch to remove the page-read
callback from XLogReaderState.

David Fetter sent in another revision of a patch to enable setting pg_hba.conf
permissions from initdb.

Melanie Plageman sent in a patch to show parallel leader stats in EXPLAIN
output.

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

Paul A Jungwirth sent in another revision of a patch to implement multirange and
friends.

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

Alexander Korotkov sent in another revision of a patch to pg_rewind to add
options to restore WAL files from archives.

Justin Pryzby sent in a patch to document that checkpoint_flush_after applies to
end-of-recovery checkpoint, and avoid an ambiguous "that".

Daniel Gustafsson sent in another revision of a patch to make it possible to
enable checksums online.

Richard Guo sent in another revision of a patch to fix up partition-wise join.

Richard Guo sent in another revision of a patch to implement parallel grouping
sets.

Tomáš Vondra sent in a patch to add views of SLRU statistics.

曾文旌(义从) sent in two more revisions of a patch to implement global temporary
tables.