== PostgreSQL Weekly News - September 8, 2019 ==

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

== PostgreSQL Weekly News - September 8, 2019 ==

David Fetter
== 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
  using OpenSSL.

- 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
  <[hidden email]>

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:
  Backpatch-through: 10

- Doc: Replace mention to "K bytes" by "kilobytes" in textsearch.sgml. "kB" or
  "kilobyte" is used in the documentation.  Author: Daniel Gustafsson
  Discussion: https://postgr.es/m/32BA3AF7-37E9-4334-A226-98B844ADCC4E@...

- 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:
  Backpatch-through: 10

- 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:
  Backpatch-through: 9.6

- 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
  Paquier Discussion:

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
down resources.

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
chained transactions.

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
relation forks.

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
connection pooler.

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.