== PostgreSQL Weekly News - December 1, 2019 ==

== PostgreSQL Weekly News - December 1, 2019 ==

== PostgreSQL Product News ==

Ajqvue Version 3.1, a java-based UI which supports PostgreSQL, released.
http://ajqvue.com

pgquarrel 0.6.0, a tool which compares PostgreSQL database schemas and outputs a
set of commands to turn a database schema into another one, released.
http://eulerto.github.io/pgquarrel

pg_filedump 12.0 a utility to format PostgreSQL heap/index/control files into a
human-readable form, released.
https://wiki.postgresql.org/wiki/Pg_filedump

== PostgreSQL Jobs for December ==

http://archives.postgresql.org/pgsql-jobs/2019-12/

== PostgreSQL Local ==

2Q PGConf 2019 will be held December 4 & 5 in Chicago.
https://www.2qpgconf.com/

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.
The CfP is open through January 15, 2020.
http://pgday.org.il/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace
Saint-Martin. The CfP is open through December 31, 2019 at midnight, Paris time
at https://2020.pgday.paris/callforpapers/
https://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020. The CfP is open through December 31, 2019 at
https://2020.nordicpgday.org/cfp/

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.

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

- Remove a couple of unnecessary if-tests. Commit abd9ca377 replaced a couple of
while-loops in fmtfloat() with calls to dopr_outchmulti, but I (tgl) failed to
notice that the new if-tests guarding those calls were really unnecessary,
because they're inside a larger if-block checking the same thing. Ranier
Vilela Discussion:
https://postgr.es/m/MN2PR18MB2927850AB00CF39CC370D107E34B0@MN2PR18MB2927.namprd18.prod.outlook.com
https://git.postgresql.org/pg/commitdiff/91da65f4ac2837e0792071e42b2e2101059f1b1b

- Stabilize the results of pg_notification_queue_usage(). This function wasn't
touched in commit 51004c717, but that turns out to be a bad idea, because its
results now include any dead space that exists in the NOTIFY queue on account
of our being lazy about advancing the queue tail. Notably, the isolation
tests now fail if run twice without a server restart between, because
async-notify's first test of the function will already show a positive value.
It seems likely that end users would be equally unhappy about the result's
instability. To fix, just make the function call asyncQueueAdvanceTail before
computing its result. That should end in producing the same value as before,
and it's hard to believe that there's any practical use-case where
pg_notification_queue_usage() is called so often as to create a performance
degradation, especially compared to what we did before. Out of paranoia, also
mark this function parallel-restricted (it was volatile, but parallel-safe by
default, before). Although the code seems to work fine when run in a parallel
worker, that's outside the design scope of async.c, and it's a bit scary to
have intentional side-effects happening in a parallel worker. There seems no
plausible use-case where it'd be important to try to parallelize this, so
let's not take any risk of introducing new bugs. In passing, re-pgindent
async.c and run reformat-dat-files on pg_proc.dat, just because I'm a neatnik.
Discussion: https://postgr.es/m/13881.1574557302@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/8b7ae5a82d04312672c919ecea3c30b1ec7faaf2

- Stabilize NOTIFY behavior by transmitting notifies before ReadyForQuery. This
patch ensures that, if any notify messages were received during a
just-finished transaction, they get sent to the frontend just before not just
after the ReadyForQuery message. With libpq and other client libraries that
act similarly, this guarantees that the client will see the notify messages as
available as soon as it thinks the transaction is done. This probably makes
no difference in practice, since in realistic use-cases the application would
have to cope with asynchronous arrival of notify events anyhow. However, it
makes it a lot easier to build cross-session-notify test cases with stable
behavior. I'm a bit surprised now that we've not seen any buildfarm
instability with the test cases added by commit b10f40bf0. Tests that I
intend to add in an upcoming bug fix are definitely unstable without this.
Back-patch to 9.6, which is as far back as we can do NOTIFY testing with the
isolationtester infrastructure. Discussion:
https://postgr.es/m/13881.1574557302@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/7900269724424b6deca516eba3dd8e4bf6621bea

- Avoid assertion failure with LISTEN in a serializable transaction. If LISTEN
is the only action in a serializable-mode transaction, and the session was not
previously listening, and the notify queue is not empty, predicate.c reported
an assertion failure. That happened because we'd acquire the transaction's
initial snapshot during PreCommit_Notify, which was called *after* predicate.c
expects any such snapshot to have been established. To fix, just swap the
order of the PreCommit_Notify and PreCommit_CheckForSerializationFailure calls
during CommitTransaction. This will imply holding the notify-insertion lock
slightly longer, but the difference could only be meaningful in serializable
mode, which is an expensive option anyway. It appears that this is just an
assertion failure, with no consequences in non-assert builds. A snapshot used
only to scan the notify queue could not have been involved in any
serialization conflicts, so there would be nothing for
PreCommit_CheckForSerializationFailure to do except assign it a prepareSeqNo
and set the SXACT_FLAG_PREPARED flag. And given no conflicts, neither of
those omissions affect the behavior of ReleasePredicateLocks. This admittedly
once-over-lightly analysis is backed up by the lack of field reports of
trouble. Per report from Mark Dilger. The bug is old, so back-patch to all
supported branches; but the new test case only goes back to 9.6, for lack of
adequate isolationtester infrastructure before that. Discussion:
https://postgr.es/m/3ac7f397-4d5f-be8e-f354-440020675694@gmail.com Discussion:
https://postgr.es/m/13881.1574557302@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6b802cfc7fab0f38001ae465ccd4f7f565b6169d

- Doc: improve discussion of race conditions involved in LISTEN. The user docs
didn't really explain how to use LISTEN safely, so clarify that. Also clean
up some fuzzy-headed explanations in comments. No code changes. Discussion:
https://postgr.es/m/3ac7f397-4d5f-be8e-f354-440020675694@gmail.com
https://git.postgresql.org/pg/commitdiff/d3aa114ac4de9ecc558ba77ed5c85e2ad9ad01d4

- Fix unportable printf format introduced in commit 9290ad198. "%ld" is not an
acceptable format spec for int64 variables, though it accidentally works on
most non-Windows 64-bit platforms. Follow the lead of commit 6a1cd8b92, and
use "%lld" with an explicit cast to long long. Per buildfarm.
https://git.postgresql.org/pg/commitdiff/5883f5fe27d7b52c812dd0f8cbda67373a14c451

- Allow access to child table statistics if user can read parent table. The fix
for CVE-2017-7484 disallowed use of pg_statistic data for planning purposes if
the user would not be able to select the associated column and a non-leakproof
function is to be applied to the statistics values. That turns out to disable
use of pg_statistic data in some common cases involving
inheritance/partitioning, where the user does have permission to select from
the parent table that was actually named in the query, but not from a child
table whose stats are needed. Since, in non-corner cases, the user *can*
select the child table's data via the parent, this restriction is not actually
useful from a security standpoint. Improve the logic so that we also check
the permissions of the originally-named table, and allow access if select
permission exists for that. When checking access to stats for a simple child
column, we can map the child column number back to the parent, and perform
this test exactly (including not allowing access if the child column isn't
exposed by the parent). For expression indexes, the current logic just
insists on whole-table select access, and this patch allows access if the user
can select the whole parent table. In principle, if the child table has extra
columns, this might allow access to stats on columns the user can't read. In
practice, it's unlikely that the planner is going to do any stats calculations
involving expressions that are not visible to the query, so we'll ignore that
fine point for now. Perhaps someday we'll improve that logic to detect
exactly which columns are used by an expression index ... but today is not
that day. Back-patch to v11. The issue was created in 9.2 and up by the
CVE-2017-7484 fix, but this patch depends on the append_rel_array[] planner
data structure which only exists in v11 and up. In practice the issue is most
urgent with partitioned tables, so fixing v11 and later should satisfy much of
the practical need. Dilip Kumar and Amit Langote, with some kibitzing by me
Discussion: https://postgr.es/m/3876.1531261875@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/553d2ec2710be5ae304c40134643c8f6d754af67

- Fix misbehavior with expression indexes on ON COMMIT DELETE ROWS tables. We
implement ON COMMIT DELETE ROWS by truncating tables marked that way, which
requires also truncating/rebuilding their indexes. But
RelationTruncateIndexes asks the relcache for up-to-date copies of any index
expressions, which may cause execution of eval_const_expressions on them,
which can result in actual execution of subexpressions. This is a bad thing to
have happening during ON COMMIT. Manuel Rigger reported that use of a SQL
function resulted in crashes due to expectations that ActiveSnapshot would be
set, which it isn't. The most obvious fix perhaps would be to push a snapshot
during PreCommit_on_commit_actions, but I think that would just open the door
to more problems: CommitTransaction explicitly expects that no user-defined
code can be running at this point. Fortunately, since we know that no tuples
exist to be indexed, there seems no need to use the real index expressions or
predicates during RelationTruncateIndexes. We can set up dummy index
expressions instead (we do need something that will expose the right data
type, as there are places that build index tupdescs based on this), and just
ignore predicates and exclusion constraints. In a green field it'd likely be
better to reimplement ON COMMIT DELETE ROWS using the same "init fork"
infrastructure used for unlogged relations. That seems impractical without
catalog changes though, and even without that it'd be too big a change to
back-patch. So for now do it like this. Per private report from Manuel
Rigger. This has been broken forever, so back-patch to all supported
branches.
https://git.postgresql.org/pg/commitdiff/c35b714caff008c875b484656de7d168a7bc45f9

Thomas Munro pushed:

- doc: Fix whitespace in syntax. Back-patch to 10. Author: Andreas Karlsson
Discussion:
https://postgr.es/m/043acae2-a369-b7fa-be48-1933aa2e82d1%40proxel.se
https://git.postgresql.org/pg/commitdiff/1974853d899db69a72361a9052fd699c9dcd028e

Andrew Dunstan pushed:

- Use native methods to open input in TestLib::slurp_file on Windows. It is
hoped that this will avoid some errors that we have seen before, but lately
with greater frequency, in buildfarm animals. For now apply only to master.
If this proves effective it can be backpatched. Discussion:
https://postgr.es/m/13900.1572839580@sss.pgh.pa.us Author: Juan José
Santamaría Flecha
https://git.postgresql.org/pg/commitdiff/114541d58e5970e51b78b77b65de16210beaab43

- Close stdin where it's not needed in TestLib.pm procedures. Where possible, do
this using a pseudoterminal, so that things like openssl that want to open
/dev/tty if stdin isn't a tty won't. Elsewhere, i.e. Windows, just close by
providing an empty string using the standard IPC::Run pipe mechanism. Patch
by Andrew Dunstan, based on an idea from Craig Ringer. Reviewed by Mark
Dilger. Discussion:
https://postgr.es/m/873ebb57-fc98-340d-949d-691b1810bf66@2ndQuadrant.com
https://git.postgresql.org/pg/commitdiff/9af34f3c6b02779fac6dbb6cd4c5bb225a019f43

- Fix closing stdin in TestLib.pm. Commit 9af34f3c6b naively assumed that all
non-windows platforms would have pseudoterminals and that perl would have
IO::Pty. Such is not the case. Instead of assumung anything, test for the
presence of IO::Pty and only use code that might depend on it if it's present.
The test result is exposed in $TestLib::have_io_pty so that it can be
conveniently used in SKIP tests. Discussion:
https://postgr.es/m/20191126044110.GB5435@paquier.xyz
https://git.postgresql.org/pg/commitdiff/792dba73c8f30528e51c4967d3be8ec4bdc5234b

- Revert "Close stdin where it's not needed in TestLib.pm procedures". This
reverts commits 9af34f3c6b and 792dba73c8. The code has been found not to be
portable. Discussion:
https://postgr.es/m/2658c496-f885-02db-13bb-228423782524@2ndQuadrant.com
https://git.postgresql.org/pg/commitdiff/ca266a069a20c32a8f0a1df982a5a67d9483bcb3

- Don't use native methods in TestLib::slurp_file on Msys. Commit 114541d58e has
upset some buildfarm members running Msys, that weren't previously having
problems, so the use of native Windows methods to open files is restricted by
this patch to only MSVC builds.
https://git.postgresql.org/pg/commitdiff/f6f59826f01188aa9603983d00b0cd3496e9359d

- libq support for sslpassword connection param, DER format keys. This patch
providies for support for password protected SSL client keys in libpq, and for
DER format keys, both encrypted and unencrypted. There is a new connection
parameter sslpassword, which is supplied to the OpenSSL libraries via a
callback function. The callback function can also be set by an application by
calling PQgetSSLKeyPassHook(). There is also a function to retreive the
connection setting, PQsslpassword(). Craig Ringer and Andrew Dunstan
Reviewed by: Greg Nancarrow Discussion:
https://postgr.es/m/f7ee88ed-95c4-95c1-d4bf-7b415363ab62@2ndQuadrant.com
https://git.postgresql.org/pg/commitdiff/4dc63552109f65cebbe168203bd62c5e4c753162

Michaël Paquier pushed:

- Refactor reloption handling for index AMs in-core. This reworks the reloption
parsing and build of a couple of index AMs by creating new structures for each
index AM's options. This split was already done for BRIN, GIN and GiST (which
actually has a fillfactor parameter), but not for hash, B-tree and SPGiST
which relied on StdRdOptions due to an overlap with the default option set.
This saves a couple of bytes for rd_options in each relcache entry with
indexes making use of relation options, and brings more consistency between
all index AMs. While on it, add a couple of AssertMacro() calls to make sure
that utility macros to grab values of reloptions are used with the expected
index AM. Author: Nikolay Shaplov Reviewed-by: Amit Langote, Michael Paquier,
Álvaro Herrera, Dent John Discussion:
https://postgr.es/m/4127670.gFlpRb6XCm@x200m
https://git.postgresql.org/pg/commitdiff/4cb658af70027c3544fb843d77b2e84028762747

- Fix inconsistent variable name in static function of mac8.c. Both argument
names were reversed in the declaration of the function. Author: Ranier Vilela
Discussion:
https://postgr.es/m/MN2PR18MB292755AEFF9A9144B220ABEEE34B0@MN2PR18MB2927.namprd18.prod.outlook.com
https://git.postgresql.org/pg/commitdiff/2aa84520b3508dda273b9bbffab7bf87f0d98bf8

- Add safeguards for pg_fsync() called with incorrectly-opened fds. On some
platforms, fsync() returns EBADFD when opening a file descriptor with O_RDONLY
(read-only), leading ultimately now to a PANIC to prevent data corruption.
This commit adds a new sanity check in pg_fsync() based on fcntl() to make
sure that we don't repeat again mistakes with incorrectly-set file descriptors
so as problems are detected at an early stage. Without that, such errors
could only be detected after running Postgres on a specific supported platform
for the culprit code path, which could take some time before being found.
b8e19b93 was a fix for such a problem, which got undetected for more than 5
years, and a586cc4b fixed another similar issue. Note that the new check
added works as well when fsync=off is configured, so as all regression tests
would detect problems as long as assertions are enabled. fcntl() being not
available on Windows, the new checks do not happen there. Author: Michael
Paquier Reviewed-by: Mark Dilger Discussion:
https://postgr.es/m/20191009062640.GB21379@paquier.xyz
https://git.postgresql.org/pg/commitdiff/12198239c0a5122e29619d50f76f89adc5bc7ade

Amit Kapila pushed:

- Make the order of the header file includes consistent. Similar to commits
14aec03502, 7e735035f2 and dddf4cdc33, this commit makes the order of header
file inclusion consistent in more places. Author: Vignesh C Reviewed-by: Amit
Kapila Discussion:
https://postgr.es/m/CALDaNm2Sznv8RR6Ex-iJO6xAdsxgWhCoETkaYX=+9DW3q0QCfA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/e0487223ecac9cbb7f673e4ff6d2e4086e591abf

- Don't shut down Gather[Merge] early under Limit. Revert part of commit
19df1702f5. Early shutdown was added by that commit so that we could collect
statistics from workers, but unfortunately, it interacted badly with rescans.
The problem is that we ended up destroying the parallel context which is
required for rescans. This leads to rescans of a Limit node over a Gather
node to produce unpredictable results as it tries to access destroyed parallel
context. By reverting the early shutdown code, we might lose statistics in
some cases of Limit over Gather [Merge], but that will require further study
to fix. Reported-by: Jerry Sievers Diagnosed-by: Thomas Munro Author: Amit
Kapila, testcase by Vignesh C Backpatch-through: 9.6 Discussion:
https://postgr.es/m/87ims2amh6.fsf@jsievers.enova.com
https://git.postgresql.org/pg/commitdiff/080313f8296fb0bcc74bd70fc8e15cd64f45945e

- Move pump_until to TestLib.pm. The subroutine pump_until provides the
functionality to poll until the given string is matched, or a timeout occurs.
 This can be used from other places as well, so moving it to TestLib.pm.  The
immediate need is for an upcoming regression test patch for dropdb utility.
Author: Vignesh C Reviewed-by: Amit Kapila Discussion:
https://postgr.es/m/CAP_rwwmLJJbn70vLOZFpxGw3XD7nLB_7+NKz46H5EOO2k5H7OQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/290acac92b1d7bebb4ec68fe8b7a5cb442333eda

- Add tests for '-f' option in dropdb utility. This will test that the force
option works when there is an active backend connected to the database being
dropped. Author: Pavel Stehule and Vignesh C Reviewed-by: Amit Kapila and
Vignesh C Discussion:
https://postgr.es/m/CAP_rwwmLJJbn70vLOZFpxGw3XD7nLB_7+NKz46H5EOO2k5H7OQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/8a7e9e9dad56419ff987e5f6baaf411a03c1951a

- Revert commits 290acac92b and 8a7e9e9dad. This commit revert the commits to
add a test case that tests the 'force' option when there is an active backend
connected to the database being dropped. This feature internally sends
SIGTERM to all the backends connected to the database being dropped and then
the same is reported to the client. We found that on Windows, the client end
of the socket is not able to read the data once we close the socket in the
server which leads to loss of error message which is not what we expect. We
also observed similar behavior in other cases like pg_terminate_backend(),
pg_ctl kill TERM <pid>. There are probably a few others like that. The fix
for this requires further study. Discussion:
https://postgr.es/m/E1iaD8h-0004us-K9@gemulon.postgresql.org
https://git.postgresql.org/pg/commitdiff/98a9b37ba70f24b28478360d9cf7f190b0f75f8d

Álvaro Herrera pushed:

- Refactor WAL file-reading code into WALRead(). XLogReader, walsender and
pg_waldump all had their own routines to read data from WAL files to memory,
with slightly different approaches according to the particular conditions of
each environment. There's a lot of commonality, so we can refactor that into
a single routine WALRead in XLogReader, and move the differences to a separate
(simpler) callback that just opens the next WAL-segment. This results in a
clearer (ahem) code flow. The error reporting needs are covered by filling in
a new error-info struct, WALReadError, and it's the caller's responsibility to
act on it. The backend has WALReadRaiseError() to do so. We no longer ever
need to seek in this interface; switch to using pg_pread(). Author: Antonin
Houska, with contributions from Álvaro Herrera Reviewed-by: Michaël Paquier,
Kyotaro Horiguchi Discussion: https://postgr.es/m/14984.1554998742@spoje.net
https://git.postgresql.org/pg/commitdiff/0dc8ead46363fec6f621a12c7e1f889ba73b55a9

- Remove useless "return;" lines. Discussion:
https://postgr.es/m/20191128144653.GA27883@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/3974c4a72459fc07acef3ee1369d63a7b8305b62

Robert Haas pushed:

- Use procsignal_sigusr1_handler for auxiliary processes. AuxiliaryProcessMain
does ProcSignalInit, so one might expect that auxiliary processes would need
to respond to SendProcSignal, but none of the auxiliary processes do that.
Change them to use procsignal_sigusr1_handler instead of their own private
handlers so that they do. Besides seeming more correct, this is also less
code. It shouldn't make any functional difference right now because, as far as
we know, there are no current cases where SendProcSignal targets an auxiliary
process, but there are plans to change that in the future. Andres Freund
Discussion:
http://postgr.es/m/20181030051643.elbxjww5jjgnjaxg@alap3.anarazel.de
https://git.postgresql.org/pg/commitdiff/0d3c3aae3366891f1c3d6bac326070660be36f76

Etsuro Fujita pushed:

- Fix typo in comment.
https://git.postgresql.org/pg/commitdiff/47a3c7fa06538c181be815db44b5d7e8efe696ef

Peter Eisentraut pushed:

- Move configure --disable-float8-byval to pg_config_manual.h. This build option
was once useful to maintain compatibility with version-0 functions, but those
are no longer supported, so this option is no longer useful for end users. We
keep the option available to developers in pg_config_manual.h so that it is
easy to test the pass-by-reference code paths without having to fire up a
32-bit machine. Discussion:
https://www.postgresql.org/message-id/flat/f3e1e576-2749-bbd7-2d57-3f9dcf75255a(at)2ndquadrant(dot)com
https://git.postgresql.org/pg/commitdiff/4513d8b07bf342028ca95250b6e1d759858abdd3

- Add error position to an error message. Reviewed-by: Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/6e7aa4a1-be6a-1a75-b1f9-83a678e5184a(at)2ndquadrant(dot)com
https://git.postgresql.org/pg/commitdiff/d4feadeca1591fd5fe91bdf73a7897553f5366d7

- Remove any-user DML capability from allow_system_table_mods. Previously,
allow_system_table_mods allowed a non-superuser to do DML on a system table
without further permission checks. This has been removed, as it was quite
inconsistent with the rest of the meaning of this setting. (Since
allow_system_table_mods was previously only accessible with a server restart,
it is unlikely that anyone was using this possibility.) Reviewed-by: Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:
https://www.postgresql.org/message-id/flat/8b00ea5e-28a7-88ba-e848-21528b632354%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/508bf95b767140ec1a339bcb53538d21deb9d995

- Make allow_system_table_mods settable at run time. Make
allow_system_table_mods settable at run time by superusers. It was previously
postmaster start only. We don't want to make system catalog DDL wide-open,
but there are occasionally useful things to do like setting reloptions or
statistics on a busy system table, and blocking those doesn't help anyone.
Also, this enables the possibility of writing a test suite for this setting.
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:
https://www.postgresql.org/message-id/flat/8b00ea5e-28a7-88ba-e848-21528b632354%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/c4a7a392ec8f0ff7701d84768080721ff8a7782e

- Add a regression test for allow_system_table_mods. Add a regression test file
that exercises the kinds of commands that allow_system_table_mods allows.
This is put in the "unsafe_tests" suite, so it won't accidentally create a
mess if someone runs the normal regression tests against an instance that they
care about. Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:
https://www.postgresql.org/message-id/flat/8b00ea5e-28a7-88ba-e848-21528b632354%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/7fc380f83d466b43a8f65bb52c925c1ab19736ea

- Small code simplification. FLOAT8PASSBYVAL can be used instead of
USE_FLOAT8_BYVAL here.
https://git.postgresql.org/pg/commitdiff/e6c2d17c5367ddcd900450c6a857dac8630da0ca

Bruce Momjian pushed:

- pg_upgrade: improve instructions for fixing incompatible isn use. This
clarifies instructions on how to dump/reload databases that use incompatible
isn versions. Reported-by: Alvaro Herrera Discussion:
https://postgr.es/m/20191114190652.GA23586@alvherre.pgsql Reviewed-by: Daniel
Gustafsson <daniel(at)yesql(dot)se> Backpatch-through: 13
https://git.postgresql.org/pg/commitdiff/f0b57aec1d41f89611efac4d6938a725b056ed00

- pg_upgrade: adjust error paragraph width to be consistent. Previously these
error paragraphs were too wide. Backpatch-through: 13
https://git.postgresql.org/pg/commitdiff/60b35b7f1ea1d5cd17805e30299fd21616855b7d

Tomáš Vondra pushed:

- Fix choose_best_statistics to check clauses individually. When picking the
best extended statistics object for a list of clauses, it's not enough to look
at attnums extracted from the clause list as a whole. Consider for example
this query with OR clauses: SELECT * FROM t WHERE (t.a = 1) OR (t.b = 1)
OR (t.c = 1) with a statistics defined on columns (a,b). Relying on attnums
extracted from the whole OR clause, we'd consider the statistics usable. That
does not work, as we see the conditions as a single OR-clause, referencing an
attribute not covered by the statistic, leading to empty list of clauses to be
estimated using the statistics and an assert failure. This changes
choose_best_statistics to check which clauses are actually covered, and only
using attributes from the fully covered ones. For the previous example this
means the statistics object will not be considered as compatible with the
OR-clause. Backpatch to 12, where MCVs were introduced. The issue does not
affect older versions because functional dependencies don't handle OR clauses.
Author: Tomas Vondra Reviewed-by: Dean Rasheed Reported-By: Manuel Rigger
Discussion:
https://postgr.es/m/CA+u7OA7H5rcE2=8f263w4NZD6ipO_XOrYB816nuLXbmSTH9pQQ@mail.gmail.com
Backpatch-through: 12
https://git.postgresql.org/pg/commitdiff/c676e659b246f94d571b57b559f80cb2dc03e73b

- Remove unnecessary clauses_attnums variable. Commit c676e659b2 reworked how
choose_best_statistics() picks the best extended statistics, but failed to
remove clauses_attnums which is now unnecessary. So get rid of it and
backpatch to 12, same as c676e659b2. Author: Tomas Vondra Discussion:
https://postgr.es/m/CA+u7OA7H5rcE2=8f263w4NZD6ipO_XOrYB816nuLXbmSTH9pQQ@mail.gmail.com
Backpatch-through: 12
https://git.postgresql.org/pg/commitdiff/6d61c3f1cb7134c3ad80d29e216563571cc43de2

- Use memcpy instead of a byte loop in pglz_decompress. The byte loop used in
pglz_decompress() because of possible overlap may be quite inefficient, so
this commit replaces it with memcpy. The gains do depend on the data
(compressibility) and hardware, but seem to be quite significant. Author:
Andrey Borodin Reviewed-by: Michael Paquier, Konstantin Knizhnik, Tels
Discussion:
https://postgr.es/m/469C9ED9-348C-4FE7-A7A7-B0FA671BEE4C@yandex-team.ru
https://git.postgresql.org/pg/commitdiff/c60e520f6e0e8db9618cad042df071a6752f3c06

- Fix off-by-one error in PGTYPEStimestamp_fmt_asc. When using %b or %B patterns
to format a date, the code was simply using tm_mon as an index into array of
month names. But that is wrong, because tm_mon is 1-based, while array indexes
are 0-based. The result is we either use name of the next month, or a segfault
(for December). Fix by subtracting 1 from tm_mon for both patterns, and add a
regression test triggering the issue. Backpatch to all supported versions (the
bug is there far longer, since at least 2003). Reported-by: Paul Spencer
Backpatch-through: 9.4 Discussion:
https://postgr.es/m/16143-0d861eb8688d3fef%40postgresql.org
https://git.postgresql.org/pg/commitdiff/3ff660bbeb96086cb1cf880bfb4e2e350cbd21b2

== Pending Patches ==

Ranier Vilela sent in a patch to fix a possible underflow.

Dilip Kumar sent in a patch to create a fastpath for sending changes to output
plugin in logical decoding.

Surafel Temesgen sent in another revision of a patch to implement conflict
handling for COPY FROM.

Amit Langote sent in another revision of a patch to move more code to attmap.c.

Jehan-Guillaume de Rorthais sent in a patch to fix subscriber invalid memory
access on DDL.

Suraj Kharage sent in another revision of a patch to make it possible to check
whether that backup is validated or corruption-free without restarting the
server.

Michaël Paquier sent in another revision of a patch to implement safeguards
against incorrect fd flags for fsync().

Tom Lane and John Naylor traded patches to handle UESCAPEs in the parser.

Amit Langote sent in another revision of a patch to do row filtering for logical
replication.

Álvaro Herrera and Surafel Temesgen traded patches to implement FETCH FIRST ...
WITH TIES.

Vigneshwaran C sent in four more revisions of a patch to implement dropdb
--force.

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

Ranier Vilela sent in a patch to fix a possible string overflow with sscanf in
xlog.c.

Ranier Vilela sent in two revisions of a patch to remove a double assignment in
nbtree.c.

Vyacheslav Makarov sent in a patch to increase the maximum size of
track_activity_query_size from 100KiB to 1MiB.

Kyotaro HORIGUCHI sent in two more revisions of a patch to fix a WAL logging
issue.

Andrey Borodin sent in another revision of a patch to use memcpy in pglz
decompression.

Yugo Nagata and Tatsuo Ishii traded patches to implement incremental
materialized views.

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

Kyotaro HORIGUCHI sent in a patch to emit a meaningful message for references to
a concurrently added column.

Pavel Stěhule sent in another revision of a patch to implement commontype and
commontypearray.

Maxence Ahlouche sent in a patch to ensure that all-space prompts consider only
the length of the final line of PROMPT1 when computing the size of PROMPT2.

Alexander Korotkov sent in another revision of a patch to allow CLUSTER, VACUUM
FULL and REINDEX to change tablespace on the fly.

Tom Lane sent in a patch to modernize SQL functions' result type coercions.

Fabien COELHO sent in another revision of a patch to increase the test coverage
of psql.

Jeff Davis sent in another revision of a patch to implement memory-bounded hash
aggregation.

Peter Smith and Michaël Paquier traded patches to add more compile-time asserts
to expose inconsistencies.

Kyotaro HORIGUCHI sent in two more revisions of a patch to remove the page-read
callback from XLogReaderState.

Tatsuro Yamada sent in two more revisions of a patch to add a progress report
for ANALYZE.

Peter Eisentraut sent in another revision of a patch to remove any-user DML
capability from allow_system_table_mods, make allow_system_table_mods settable
at run time, and add a regression test for allow_system_table_mods.

Kirk Jamison sent in another revision of a patch to optimize dropping of
relation buffers using dlist.

Pavel Stěhule sent in another revision of a patch to add type info support
functions for functions that use "any" type.

Álvaro Herrera sent in a patch to remove some useless returns.

Michaël Paquier and Fabien COELHO traded patches to share query cancellation
code across fe-utils.

Mark Dilger sent in a patch to check whether XID sequences actually have to be
contiguous. Amit Kapila sent in an XID burner, possibly originally by Jeff
Janes.

Thomas Munro sent in two revisions of a patch to fix an issue that manifested as
checkpointer: PANIC: could not fsync file: No such file or directory by sending
all the SYNC_FORGET_REQUEST messages up front, and another patch that fixes it
by not using _mdfd_getseg() in mdsyncfiletag().

Amit Langote sent in three revisions of a patch to compactify pgbench init
progress output.

Pengzhou Tang sent in another revision of a patch to implement parallel grouping
sets.

Andrew Gierth sent in a patch to implement WITH TIES by using a window function
expression to compute a stopping point.

Luis Carril sent in another revision of a patch to add an option to dump foreign
data in pg_dump.

Fabien COELHO sent in another revision of a patch to add \aset to pgbench to
store results of a combined query.

Peter Eisentraut sent in another revision of a patch to make better use of
ParseState in ProcessUtility.

Masahiko Sawada sent in a patch to fix usages of XLogFileNameP in critical
sections.

Peter Eisentraut sent in a patch to update the minimal required TLS version to
1.2.

David Fetter sent in three revisions of a patch to make autovacuum sort tables
in descending order of xid_age.

Artur Zakirov sent in another revision of a patch to fix a bug that manifested
as pg_upgrade fails with non-standard ACL.

Álvaro Herrera sent in a patch atop the range_agg patch to simplify
makeMultirangeConstructors, silence a compiler warning, use shorter variable
names, protect a comment against pgindent, run pg_indent, and add some comments
and Asserts to makeUniqueTypeName.

Thomas Munro sent in a patch to raise errors for I/O errors during
BufFileRead(), report I/O errors from BufFileFlush() via ereport(), and align
BufFileWrite()'s error reporting with BufFileRead()'s.

Noah Misch sent in a patch to add a test for DDL unlogged sync.

Alexey Bashtanov sent in another revision of a patch to log bind parameter
values on error.

Tomáš Vondra sent in another revision of a patch to implement incremental sort.

Thomas Munro sent in another revision of a patch to add an SQL type xid8 to
expose FullTransactionId to users, and introduce xid8 variants of the txid_XXX()
fmgr functions.

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

Tomáš Vondra sent in two revisions of a patch to fix a bug that resulted in a
surprisingly expensive join planning query.

Tom Lane sent in three revisions of a patch to fix a bug that manifested as
bogus EXPLAIN results with column aliases for mismatched partitions.