== PostgreSQL Weekly News - September 8, 2019 ==
== PostgreSQL Jobs for September ==
== PostgreSQL Local ==
PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the
Rosen Centre Hotel.
PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019
PostgreSQL Conference Europe 2019 will be held on October 15-18, 2019 in Milan,
2Q PGConf 2019 will be held December 4 & 5 in Chicago.
The CFP is open through August 30, 2019.
pgDay Paris 2020 will be held in Paris, France on March 26, 2020
at Espace Saint-Martin.
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
PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka.
== 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 [hidden email].
== Applied Patches ==
Peter Eisentraut pushed:
- Fix compiler warning. Fix a warning about unused variable on Windows when
- pg_checksums: Handle read and write returns correctly. The read() return was
not checking for errors, the write() return was not checking for short writes.
Reviewed-by: Michael Paquier <[hidden email]> Discussion:
- Better error messages for short reads/writes in SLRU. This avoids getting a
Could not read from file ...: Success. for a short read or write (since errno
is not set in that case). Instead, report a more specific error messages.
Reviewed-by: Michael Paquier <[hidden email]> Discussion:
- Improve base backup protocol documentation. Document that the tablespace sizes
are in units of kilobytes. Make the pg_basebackup source code a bit clearer
about this, too. Reviewed-by: Magnus Hagander <[hidden email]>
- Clarify pg_dump documentation. Clarify in the help output and documentation
that -n, -t etc. take a "pattern" rather than a "schema" or "table" etc. This
was especially confusing now that the new pg_dumpall --exclude-database option
was documented with "pattern" and the others not, even though they all behave
the same. Discussion:
- Use explicit_bzero. Use the explicit_bzero() function in places where it is
important that security information such as passwords is cleared from memory.
There might be other places where it could be useful; this is just an initial
collection. For platforms that don't have explicit_bzero(), provide various
fallback implementations. (explicit_bzero() itself isn't standard, but as
Linux/glibc, FreeBSD, and OpenBSD have it, it's the most common spelling, so
it makes sense to make that the invocation point.) Discussion:
- libpq: ccache -> credential cache. The term "ccache" is overloaded. Let's be
more clear, in case someone other than a Kerberos wizard has to read this
- doc: Postgres -> PostgreSQL.
- doc: Fix awkward markup.
- Message style fixes.
- doc: Clean up title case use. Note: Following existing practice, titles of
formalpara and step are not titlecased.
- doc: effective -> efficient.
- Fix behavior of AND CHAIN outside of explicit transaction blocks. When using
COMMIT AND CHAIN or ROLLBACK AND CHAIN not in an explicit transaction block,
the previous implementation would leave a transaction block active in the
ROLLBACK case but not the COMMIT case. To fix for now, error out when using
these commands not in an explicit transaction block. This restriction could
be lifted if a sensible definition and implementation is found. Bug: #15977
Author: fn ln <[hidden email]> Reviewed-by: Fabien COELHO
Michaël Paquier pushed:
- Add overflow-safe math inline functions for unsigned integers. Similarly to
the signed versions added in 4d6ad31, this adds a set of inline functions for
overflow checks with unsigned integers, including uint16, uint32 and uint64.
This relies on compiler built-in overflow checks by default if available. The
behavior of unsigned integers is well-defined so the fallback implementations
checks are simple for additions and subtractions. Multiplications avoid
division-based checks which are expensive if possible, still this can happen
for uint64 if 128-bit integers are not available. While on it, the code in
common/int.h is reorganized to avoid too many duplicated comments. The new
macros will be used in a follow-up patch. All thanks to Andres Freund for the
input provided. Author: Fabien Coelho, Michael Paquier Discussion:
- Fix memory leak with lower, upper and initcap with ICU-provided collations.
The leak happens in str_tolower, str_toupper and str_initcap, which are used
in several places including their equivalent SQL-level functions, and can only
be triggered when using an ICU-provided collation when converting the input
string. b615920 fixed a similar leak. Backpatch down 10 where ICU collations
have been introduced. Author: Konstantin Knizhnik Discussion:
- Doc: Replace mention to "K bytes" by "kilobytes" in textsearch.sgml. "kB" or
"kilobyte" is used in the documentation. Author: Daniel Gustafsson
- Delay fsyncs of pg_basebackup until the end of backup. Since the addition of
fsync requests in bc34223 to make base backup data consistent on disk once
pg_basebackup finishes, each tablespace tar file is individually flushed once
completed, with an additional flush of the parent directory when the base
backup finishes. While holding a connection to the server, a fsync request
taking a long time may cause a failure of the base backup, which is annoying
for any integration. A recent example of breakage can involve
tcp_user_timeout, but wal_sender_timeout can cause similar problems. While
reviewing the code, there was a second issue causing too many fsync requests
to be done for the same WAL data. As recursive fsyncs are done at the end of
the backup for both the plain and tar formats from the base target directory
where everything is written, it is fine to disable fsyncs when fetching or
streaming WAL. Reported-by: Ryohei Takahashi Author: Michael Paquier
Reviewed-by: Ryohei Takahashi Discussion:
- Fix thinko when ending progress report for a backend. The logic ending
progress reporting for a backend entry introduced by b6fb647 causes callers of
pgstat_progress_end_command() to do some extra work when track_activities is
enabled as the process fields are reset in the backend entry even if no
command were started for reporting. This resets the fields only if a command
is registered for progress reporting, and only if track_activities is enabled.
Author: Masahiho Sawada Discussion:
- Make use of generic logging in vacuumlo and oid2name. Doing the switch reduces
the footprint of "progname" in both utilities for the messages produced. This
also cleans up a couple of inconsistencies in the message formats. Author:
Michael Paquier Reviewed-by: Álvaro Herrera, Peter Eisentraut Discussion:
Álvaro Herrera pushed:
- Add POD documentation to TestLib.pm. This module was pretty much undocumented.
Fix that. Inspired by a preliminary patch sent by Ramanarayana, heavily
updated by Andrew Dunstan, and reviewed by Michael Paquier. Discussion:
- Remove 'msg' parameter from convert_tuples_by_name. The message was included
as a parameter when this function was added in dcb2bda9b704, but I don't think
it has ever served any useful purpose. Let's stop spreading it pointlessly.
Reviewed by Amit Langote and Peter Eisentraut. Discussion:
- Make XLogReaderInvalReadState static. This function is only used by
xlogreader.c itself, so there's no need to export it. It was introduced by
commit 3b02ea4f0780 with the apparent intention that it could be used
externally, but I couldn't find any external code calling it. I (Álvaro)
couldn't resist the urge to sort nearby function prototypes properly while at
it. Author: Antonin Houska Discussion:
Tom Lane pushed:
- Handle corner cases correctly in psql's reconnection logic. After an
unexpected connection loss and successful reconnection, psql neglected to
resynchronize its internal state about the server, such as server version.
Ordinarily we'd be reconnecting to the same server and so this isn't really
necessary, but there are scenarios where we do need to update --- one example
is where we have a list of possible connection targets and they're not all
alike. Define "resynchronize" as including connection_warnings(), so that
this case acts the same as \connect. This seems useful; for example, if the
server version did change, the user might wish to know that. An attuned user
might also notice that the new connection isn't SSL-encrypted, for example,
though this approach isn't especially in-your-face about such changes.
Although this part is a behavioral change, it only affects interactive
sessions, so it should not break any applications. Also, in do_connect, make
sure that we desynchronize correctly when abandoning an old connection in
non-interactive mode. These problems evidently are the result of people
patching only one of the two places where psql deals with connection changes,
so insert some cross-referencing comments in hopes of forestalling future bugs
of the same ilk. Lastly, in Windows builds, issue codepage mismatch warnings
only at startup, not during reconnections. psql's codepage can't change
during a reconnect, so complaining about it again seems like useless noise.
Peter Billen and Tom Lane. Back-patch to all supported branches. Discussion:
- Avoid touching replica identity index in ExtractReplicaIdentity(). In what
seems like a fit of misplaced optimization, ExtractReplicaIdentity() accessed
the relation's replica-identity index without taking any lock on it. Usually,
the surrounding query already holds some lock so this is safe enough ... but
in the case of a previously-planned delete, there might be no existing lock.
Given a suitable test case, this is exposed in v12 and HEAD by an assertion
added by commit b04aeb0a0. The whole thing's rather poorly thought out
anyway; rather than looking directly at the index, we should use the
index-attributes bitmap that's held by the parent table's relcache entry, as
the caller functions do. This is more consistent and likely a bit faster,
since it avoids a cache lookup. Hence, change to doing it that way. While at
it, rather than blithely assuming that the identity columns are non-null (with
catastrophic results if that's wrong), add assertion checks that they aren't
null. Possibly those should be actual test-and-elog, but I'll leave it like
this for now. In principle, this is a bug that's been there since this code
was introduced (in 9.4). In practice, the risk seems quite low, since we do
have a lock on the index's parent table, so concurrent changes to the index's
catalog entries seem unlikely. Given the precedent that commit 9c703c169
wasn't back-patched, I won't risk back-patching this further than v12. Per
report from Hadi Moshayedi. Discussion:
- Use data directory inode number, not port, to select SysV resource keys. This
approach provides a much tighter binding between a data directory and the
associated SysV shared memory block (and SysV or named-POSIX semaphores, if
we're using those). Key collisions are still possible, but only between data
directories stored on different filesystems, so the situation should be
negligible in practice. More importantly, restarting the postmaster with a
different port number no longer risks failing to identify a relevant shared
memory block, even when postmaster.pid has been removed. A standalone backend
is likewise much more certain to detect conflicting leftover backends. (In
the longer term, we might now think about deprecating the port as a
cluster-wide value, so that one postmaster could support sockets with varying
port numbers. But that's for another day.) The hazards fixed here apply only
on Unix systems; our Windows code paths already use identifiers derived from
the data directory path name rather than the port.
src/test/recovery/t/017_shm.pl, which intends to test key-collision cases, has
been substantially rewritten since it can no longer use two postmasters with
identical port numbers to trigger the case. Instead, use Perl's IPC::SharedMem
module to create a conflicting shmem segment directly. The test script will
be skipped if that module is not available. (This means that some older
buildfarm members won't run it, but I don't think that that results in any
meaningful coverage loss.) Patch by me; thanks to Noah Misch and Peter
Eisentraut for discussion and review. Discussion:
- Doc: tweak installation doc edits made by commit 76c2af926. We don't consider
that building with MinGW is deprecated, so adjust some places that gave that
impression. Per discussion with Peter Eisentraut. Discussion:
- Fix issues around strictness of SIMILAR TO. As a result of some long-ago quick
hacks, the SIMILAR TO operator and the corresponding flavor of substring()
interpreted "ESCAPE NULL" as selecting the default escape character '\'. This
is both surprising and not per spec: the standard is clear that these
functions should return NULL for NULL input. Additionally, because of
inconsistency of the strictness markings of 3-argument substring() and
similar_escape(), the planner could not inline the SQL definition of
substring(), resulting in a substantial performance penalty compared to the
underlying POSIX substring() function. The simplest fix for this would be to
change the strictness marking of similar_escape(), but if we do that we risk
breaking existing views that depend on that function. Hence, leave
similar_escape() as-is as a compatibility function, and instead invent a new
function similar_to_escape() that comes in two strict variants. There are a
couple of other behaviors in this area that are also not per spec, but they
are documented and seem generally at least as sane as the spec's definition,
so leave them alone. But improve the documentation to describe them fully.
Patch by me; thanks to Álvaro Herrera and Andrew Gierth for review and
discussion. Discussion: https://postgr.es/m/14047.1557708214@...
- Avoid using INFO elevel for what are fundamentally debug messages. Commit
6f6b99d13 stuck an INFO message into the fast path for checking partition
constraints, for no very good reason except that it made it easy for the
regression tests to verify that that path was taken. Assorted later patches
did likewise, increasing the unsuppressable-chatter level from ALTER TABLE
even more. This isn't good for the user experience, so let's drop these
messages down to DEBUG1 where they belong. So as not to have a loss of test
coverage, create a TAP test that runs the relevant queries with
client_min_messages = DEBUG1 and greps for the expected messages. This
testing method is a bit brute-force --- in particular, it duplicates the
execution of a fair amount of the core create_table and alter_table tests. We
experimented with other solutions, but running any significant amount of
standard testing with client_min_messages = DEBUG1 seems to have a lot of
output-stability pitfalls, cf commits bbb96c370 and 5655565c0. Possibly at
some point we'll look into whether we can reduce the amount of test
duplication. Backpatch into v12, because some of these messages are new in
v12 and we don't really want to ship it that way. Sergei Kornilov
Discussion: https://postgr.es/m/81911511895540@... Discussion:
Robert Haas pushed:
- Split tuptoaster.c into three separate files. detoast.c/h contain functions
required to detoast a datum, partially or completely, plus a few other utility
functions for examining the size of toasted datums. toast_internals.c/h
contain functions that are used internally to the TOAST subsystem but which
(mostly) do not need to be accessed from outside. heaptoast.c/h contains code
that is intrinsically specific to the heap AM, either because it operates on
HeapTuples or is based on the layout of a heap page. detoast.c and
toast_internals.c are placed in src/backend/access/common rather than
src/backend/access/heap. At present, both files still have dependencies on
the heap, but that will be improved in a future commit. Patch by me, reviewed
and tested by Prabhat Sabu, Thomas Munro, Andres Freund, and Álvaro Herrera.
- When performing a base backup, check for read errors. The old code didn't
differentiate between a read error and a concurrent truncation. fread reports
both of these by returning 0; you have to use feof() or ferror() to
distinguish between them, which this code did not do. It might be a better
idea to use read() rather than fread() here, so that we can display a
less-generic error message, but I'm not sure that would qualify as a
back-patchable bug fix, so just do this much for now. Jeevan Chalke, reviewed
by Jeevan Ladhe and by me. Discussion:
- Create an API for inserting and deleting rows in TOAST tables. This moves much
of the non-heap-specific logic from toast_delete and toast_insert_or_update
into a helper functions accessible via a new header, toast_helper.h. Using
the functions in this module, a table AM can implement creation and deletion
of TOAST table rows with much less code duplication than was possible
heretofore. Some table AMs won't want to use the TOAST logic at all, but for
those that do this will make that easier. Patch by me, reviewed and tested by
Prabhat Sabu, Thomas Munro, Andres Freund, and Álvaro Herrera. Discussion:
Fujii Masao pushed:
- Make pg_promote() detect postmaster death while waiting for promotion to end.
Previously even if postmaster died and WaitLatch() woke up with that event
while pg_promote() was waiting for the standby promotion to finish,
pg_promote() did nothing special and kept waiting until timeout occurred. This
could cause a busy loop. This patch make pg_promote() return false
immediately when postmaster dies, to avoid such a busy loop. Back-patch to
v12 where pg_promote() was added. Author: Fujii Masao Reviewed-by: Michael
Andrew Dunstan pushed:
- Always skip recovery SysV shared memory tests on Windows. The test for SysV
support currently involves looking for the perl modules IPC::SharedMem and
IPC::SysV. However, the perl on msys2 has these modules but the tests fail.
Therefore, force skipping the tests on Windows platforms unconditionally.
Alexander Korotkov pushed:
- Fix handling of NULL distances in KNN-GiST. In order to implement NULL LAST
semantic GiST previously assumed distance to the NULL value to be Inf.
However, our distance functions can return Inf and NaN for non-null values.
In such cases, NULL LAST semantic appears to be broken. This commit fixes
that by introducing separate array of null flags for distances. Backpatch to
all supported versions. Discussion:
Author: Alexander Korotkov Backpatch-through: 9.4
- Fix handling Inf and Nan values in GiST pairing heap comparator. Previously
plain float comparison was used in GiST pairing heap. Such comparison doesn't
provide proper ordering for value sets containing Inf and Nan values. This
commit fixes that by usage of float8_cmp_internal(). Note, there is remaining
problem with NULL distances, which are represented as Inf in pairing heap. It
would be fixes in subsequent commit. Backpatch to all supported versions.
Reported-by: Andrey Borodin Discussion:
Author: Alexander Korotkov Reviewed-by: Heikki Linnakangas Backpatch-through:
== Pending Patches ==
Alexander Korotkov sent in a patch to fix write visibility map during
Jonathan S. Katz sent in three more revisions of a patch to announce the
upcoming release of PostgreSQL 12.
Euler Taveira de Oliveira sent in two more revisions of a patch to implement row
filtering for logical replication.
Amit Kapila sent in two revisions of a patch to forbid Limit nodes from shutting
Swen Kooij sent in a patch to add a hook to copydir().
Alexander Korotkov sent in another revision of a patch to implement kNN for
Peter Geoghegan sent in another revision of a patch to add deduplication to
nbtree, and add pageinspect instrumentation for debugging same.
Fabien COELHO sent in another revision of a patch to improve the psql coverage.
Fabien COELHO sent in another revision of a patch to rework variable management
in pgbench to make it more efficient.
Tom Lane sent in a patch to prevent issuing a SIGQUIT to postmaster
Pierre Ducroquet sent in another revision of a patch to add a function to
pg_stat_statements which resets the cumulative statistics.
Fabien COELHO sent in two more revisions of a patch to share the str2*int64
Pierre Ducroquet sent in another revision of a patch to use a different
permission check path for indexes and relations in pg_stats.
Masahiko Sawada sent in a patch to fix the CLUSTER command progress monitor.
Peter Eisentraut sent in another revision of a patch to support Unix domain
sockets on Windows.
Tom Lane sent in another revision of a patch to lower the partition constraint
check ereport level.
Álvaro Herrera sent in another revision of a patch to fix a delay in PostgreSQL
shutdown caused by logical replication.
Masahiko Sawada sent in two more revisions of a patch to support atomic commit
among multiple foreign servers.
Thomas Munro sent in a patch to implement a simple readiness API that would
allow Append to start emitting tuples from whichever Foreign Scan has data
Andres Freund sent in a patch to fix the default JIT setting.
Juan José Santamaría Flecha sent in a WIP patch to support large files on Win32.
Álvaro Herrera sent in another revision of a patch to add progress report
support for ANALYZE.
fn_ln sent in three more revisions of a patch to fix some misbehaviors in
Rodrigo Ramírez Norambuena sent in three revisions of a patch to add a
connection time for psql's \conninfo.
Álvaro Herrera sent in two more revisions of a patch to introduce
heap_infomask_flags to decode infomask and infomask2.
Paul Guo sent in another revision of a patch to extract common functions from
pg_basebackup into separate files for pg_rewind use, add an option to write
recovery configuration information in pg_rewind, and ensure target clean
shutdown at the beginning of pg_rewind.
Dmitry Dolgov sent in another revision of a patch to implement index skip scan.
Quan Zongliang sent in a patch to add EXECUTE support to SPI.
Michaël Paquier sent in another revision of a patch to make vacuumlo and
oid2name use common/logging.h.
Amit Langote sent in three more revisions of a patch to use the root parent's
permissions when reading child table stats.
Jeff Davis sent in a patch to add a libpq parameter 'channel_binding'.
Robert Haas sent in two revisions of a patch to move AtEOXact_Snapshot() back to
Kirk Jamison sent in another revision of a patch to speed up truncates of
Jeremy Schneider sent in another revision of a patch to fix the multixact limit.
Robert Haas sent in two more revisions of a patch to allow TOAST tables to be
implemented using table AMs, and rename some toasting functions based on whether
they are heap-specific.
Vigneshwaran C sent in a patch to fix a issue that manifested as
FailedAssertion("!(logno == context->recovery_logno).
Kyotaro HORIGUCHI sent in another revision of a patch to move a callback call
from ReadPageInternal to XLogReadRecord, move the page-reader out of
XLogReadRecord, and change the policy of XLog read-buffer allocation by making
it the responsibility of the callers of XLogReadRecord.
Asim R P sent in another revision of a patch to implement a fault injection
Jehan-Guillaume de Rorthais sent in another revision of a patch to add functions
to get timeline.
Konstantin Knizhnik sent in another revision of a patch to add a built-in
Yuli Khodorkovskiy sent in another revision of a patch to add a MAC check for
Andrey Borodin and Peter Geoghegan traded patches to add a GiST amcheck.
Andrey Borodin sent in another revision of a patch to add sort support for point
gist_point_sortsupport, and implement GiST build using same.
Tom Lane sent in a patch to make VCRegress run all the TAP tests.
|Free forum by Nabble||Edit this page|