== PostgreSQL Weekly News - September 1, 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 1, 2019 ==

David Fetter
== PostgreSQL Weekly News - September  1, 2019 ==

== PostgreSQL Product News ==

pg_dumpbinary 1.0, a program used to dump a PostgreSQL database in binary
format, released.
https://github.com/darold/pg_dumpbinary/

Slony 2.2.8, a trigger-based logical replication system for PostgreSQL,
released.
http://www.slony.info/

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

pgbouncer 1.11.0, a connection pooler and more for PostgreSQL, released.
https://pgbouncer.github.io/2019/08/pgbouncer-1-11-0

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

The first Austrian pgDay, will take place September 6, 2019 at the Hilton Garden
Inn in Wiener Neustadt.
https://pgday.at/en/

PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the
Rosen Centre Hotel.
https://2019.postgresopen.org/

PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019
https://postgresconf.org/conferences/SouthAfrica2019

PostgreSQL Conference Europe 2019 will be held on October 15-18, 2019 in Milan,
Italy.
https://2019.pgconf.eu/

2Q PGConf 2019 will be held December 4 & 5 in Chicago.
The CFP is open through August 30, 2019.
https://www.2qpgconf.com/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020
at Espace Saint-Martin.
http://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 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 ==

Michaël Paquier pushed:

- Fix error handling of vacuumdb and reindexdb when running out of fds. When
  trying to use a high number of jobs, vacuumdb (and more recently reindexdb)
  has only checked for a maximum number of jobs used, causing confusing failures
  when running out of file descriptors when the jobs open connections to
  Postgres.  This commit changes the error handling so as we do not check
  anymore for a maximum number of allowed jobs when parsing the option value
  with FD_SETSIZE, but check instead if a file descriptor is within the
  supported range when opening the connections for the jobs so as this is
  detected at the earliest time possible.  Also, improve the error message to
  give a hint about the number of jobs recommended, using a wording given by the
  reviewers of the patch.  Reported-by: Andres Freund Author: Michael Paquier
  Reviewed-by: Andres Freund, Álvaro Herrera, Tom Lane Discussion:
  https://postgr.es/m/20190818001858.ho3ev4z57fqhs7a5@...
  Backpatch-through: 9.5
  https://git.postgresql.org/pg/commitdiff/71d84efba714db3b8a330a54be15c4d385719ad6

- Fix failure of --jobs with reindexdb and vacuumdb on Windows. FD_SETSIZE needs
  to be declared before winsock2.h, or it is possible to run into buffer
  overflow issues when using --jobs.  This is similar to pgbench's solution done
  in a23c641.  This has been introduced by 71d84ef, and older versions have been
  using the default value of FD_SETSIZE, defined at 64.  Per buildfarm member
  jacana, but this impacts all Windows animals running the TAP tests.  I have
  reproduced the failure locally to check the patch.  Author: Michael Paquier
  Reviewed-by: Andrew Dunstan Discussion:
  https://postgr.es/m/20190826054000.GE7005@... Backpatch-through: 9.5
  https://git.postgresql.org/pg/commitdiff/9acda731184c1ebdf99172cbb19d0404b7eebc37

- Disable timeouts when running pg_rewind with online source cluster. In this
  case, the transfer uses a libpq connection, which is subject to the timeout
  parameters set at system level, and this can make the rewind operation
  suddenly canceled which is not good for automation.  One workaround to such
  issues would be to use PGOPTIONS to enforce the wanted timeout parameters, but
  that's annoying, and for example pg_dump, which can run potentially
  long-running queries disables all types of timeouts.  lock_timeout and
  statement_timeout are the ones which can cause problems now.  Note that
  pg_rewind does not use transactions, so disabling
  idle_in_transaction_session_timeout is optional, but it feels safer to do so
  for the future.  This is back-patched down to 9.5.
  idle_in_transaction_session_timeout is only present since 9.6.  Author:
  Alexander Kukushkin Discussion:
  https://postgr.es/m/CAFh8B=krcVXksxiwVQh1SoY+ziJ-JC=6FcuoBL3yce_40Es5_g@...
  Backpatch-through: 9.5
  https://git.postgresql.org/pg/commitdiff/be182e4f9e899a531094bee83b14fd434e52f7cb

- Improve coverage of utils/float.h. check_float4_val() checks after underflow
  and overflow of values converted from float8 to float4, but there has never
  been any regression tests for that.  This brings the coverage of float.h to
  100%.  Author: Movead Li Discussion:
  https://postgr.es/m/20190822174636998766188@...
  https://git.postgresql.org/pg/commitdiff/80d0e5ba3fe03890831b425e85d10150e226239e

Andrew Dunstan pushed:

- Treat MINGW and MSYS the same in pg_upgrade test script. On msys2, 'uname -s'
  reports a string starting MSYS instead on MINGW as happens on msys1. Treat
  these both the same way. This reverts 608a710195a4b in favor of a more general
  solution.  Backpatch to all live branches.
  https://git.postgresql.org/pg/commitdiff/acb96eb7d294a003a9392cdd445630ef137d9918

- Adjust to latest Msys2 kernel release number. Previously 'uname -r' on Msys2
  reported a kernele release starting with 2. The latest version starts with 3.
  In commit 1638623f we specifically looked for one starting with 2. This is now
  changed to look for any digit between 2 and 9.  backpatch to release 10.
  https://git.postgresql.org/pg/commitdiff/c62b84437f52f0e23924268938916e90b8e5b9e6

Peter Eisentraut pushed:

- Fix gettext triggers specification. In
  cc8d41511721d25d557fc02a46c053c0a602fed0, the arguments of
  warn_or_exit_horribly() were changed but this was not updated.
  https://git.postgresql.org/pg/commitdiff/f2690338814738967d75cc1e35cc1cfac1a40710

- Error out on too many command-line arguments. Fix up oid2name, pg_upgrade, and
  pgbench to error out on too many command-line arguments.  This makes it match
  the behavior of other PostgreSQL programs.  Author: Peter Eisentraut, Ibrar
  Ahmed Discussion:
  https://www.postgresql.org/message-id/flat/f2554627-04e7-383a-ef01-ab99bb6a291c%402ndquadrant.com
  https://git.postgresql.org/pg/commitdiff/9684e426954921e8b2bfa367f9e6a4cbbf4ce5ff

Tom Lane pushed:

- Make comment in fmgr.h match the one in fmgr.c. Incompletely quoting an API
  spec does nobody any good.  Noted by Paul Jungwirth.  Looks like the
  discrepancy was my fault originally :-(  Discussion:
  https://postgr.es/m/CA+renyU_J8TU_d3Kr0PkuOgFbpypextendu7a+_d5NOfVdvDeA@...
  https://git.postgresql.org/pg/commitdiff/348778ddbc75eddaa7f9c7ce5b6adad8ada564e4

- Fix postmaster state machine to handle dead_end child crashes better. A report
  from Alvaro Herrera shows that if we're in PM_STARTUP state, and we spawn a
  dead_end child to reject some incoming connection request, and that child dies
  with an unexpected exit code, the postmaster does not respond well.  We
  correctly send SIGQUIT to the startup process, but then:  * if the startup
  process exits with nonzero exit code, as expected, we thought that that
  indicated a crash and aborted startup.  * if the startup process exits with
  zero exit code, which is possible due to the inherent race condition, we'd
  advance to PM_RUN state which is fine --- but the code forgot that
  AbortStartTime would be nonzero in this situation.  We'd either die on the
  Asserts saying that it was zero, or perhaps misbehave later on.  (A quick look
  suggests that the only misbehavior might be busy-waiting due to
  DetermineSleepTime doing the wrong thing.)  To fix the first point, adjust the
  state-machine logic to recognize that a nonzero exit code is expected after
  sending SIGQUIT, and have it transition to a state where we can restart the
  startup process. To fix the second point, change the Asserts to clear the
  variable rather than just claiming it should be clear already.  Perhaps we
  could improve this further by not treating a crash of a dead_end child as a
  reason for panic'ing the database.  However, since those child processes are
  connected to shared memory, that seems a bit risky.  There are few good
  reasons for a dead_end child to report failure anyway (the cause of this in
  Alvaro's report is quite unclear).  On balance, therefore, a minimal fix seems
  best.  This is an oversight in commit 45811be94.  While that was back-patched,
  I'm hesitant to back-patch this change.  The lack of reasons for a dead_end
  child to fail suggests that the case should be very rare in the field, which
  squares with the lack of reports; so it seems like this might not be worth the
  risk of introducing new issues.  In any case we can let it bake awhile in HEAD
  before considering a back-patch.  Discussion:
  https://postgr.es/m/20190615160950.GA31378@...
  https://git.postgresql.org/pg/commitdiff/ee3278239550ff0ec9df72dd798a480c82c2b723

- Fix 007_sync_rep.pl to notice failures in ALTER SYSTEM SET. If a test case
  tried to set an invalid value of synchronous_standby_names, the test script
  didn't detect that, which seems like a bad idea. Noticed while testing a
  proposed patch that broke some of these test cases.
  https://git.postgresql.org/pg/commitdiff/fb57f40eec503d637bf01c298f5cb2472f0d4fdb

- Reject empty names and recursion in config-file include directives. An empty
  file name or subdirectory name leads join_path_components() to just produce
  the parent directory name, which leads to weird failures or recursive
  inclusions.  Let's throw a specific error for that.  It takes only slightly
  more code to detect all-blank names, so do so.  Also, detect direct recursion,
  ie a file calling itself.  As coded this will also detect recursion via
  "include_dir '.'", which is perhaps more likely than explicitly including the
  file itself.  Detecting indirect recursion would require API changes for
  guc-file.l functions, which seems not worth it since extensions might call
  them. The nesting depth limit will catch such cases eventually, just not with
  such an on-point error message.  In passing, adjust the example usages in
  postgresql.conf.sample to perhaps eliminate the problem at the source: there's
  no reason for the examples to suggest that an empty value is valid.  Per a
  trouble report from Brent Bates.  Back-patch to 9.5; the issue is old, but the
  code in 9.4 is enough different that the patch doesn't apply easily, and it
  doesn't seem worth the trouble to fix there.  Ian Barwick and Tom Lane
  Discussion:
  https://postgr.es/m/8c8bcbca-3bd9-dc6e-8986-04a5abdef142@...
  https://git.postgresql.org/pg/commitdiff/6e42130568ad28bed857948f5a3192dbf01624dc

- Add missing newline in help output. Daniel Gustafsson  Discussion:
  https://postgr.es/m/F2FB03F2-B112-4E51-842E-12C50DCA2F4A@...
  https://git.postgresql.org/pg/commitdiff/d4b2425441b7ab298300142d64bb8020d38b290f

- Doc: clarify behavior of standard aggregates for null inputs. Section 4.2.7
  says that unless otherwise specified, built-in aggregates ignore rows in which
  any input is null.  This is not true of the JSON aggregates, but it wasn't
  documented. Fix that.  Of the other entries in table 9.55, some were explicit
  about ignoring nulls, and some weren't; for consistency and
  self-contained-ness, make them all say it explicitly.  Per bug #15884 from Tim
  Möhlmann.  Back-patch to all supported branches.  Discussion:
  https://postgr.es/m/15884-c32d848f787fcae3@...
  https://git.postgresql.org/pg/commitdiff/da1b51ecc52ffdb3f8fb1cda1e15dc60ff00cd11

- Improve what pg_strsignal prints if we haven't got strsignal(3). Turns out
  that returning "unrecognized signal" is confusing. Make it explicit that the
  platform lacks any support for signal names. (At least of the machines in the
  buildfarm, only HPUX lacks it.)  Back-patch to v12 where we invented this
  function.  Discussion: https://postgr.es/m/3067.1566870481@...
  https://git.postgresql.org/pg/commitdiff/c9bd7f4f2b0fc3b8291914a45cbf2a5270877ee6

- Doc: improve documentation of pg_signal_backend default role. Give it an
  explanatory para like the other default roles have. Don't imply that it can
  send any signal whatever.  In passing, reorder the table entries and
  explanatory paras for the default roles into some semblance of consistency.
  Ian Barwick, tweaked a bit by me.  Discussion:
  https://postgr.es/m/89907e32-76f3-7282-a89c-ea19c722fe5d@...
  https://git.postgresql.org/pg/commitdiff/458f01e254e0f0b1ccab8e4589aa8495781a933a

- Set application_name per-test in isolation and ecpg tests. Commit a4327296d
  taught pg_regress proper to do this, but missed the opportunity to do likewise
  in the isolationtester and ecpg variants of pg_regress.  Seems like this might
  be helpful for tracking down issues exposed by those tests.
  https://git.postgresql.org/pg/commitdiff/b1907d6882205319822c7452ce6b5e71ee2867be

- Add .gitignore file forgotten in commit bde7493d1.
  https://git.postgresql.org/pg/commitdiff/744c848dce64fd55970fcf7e9039008cbff2627e

- Doc: remove some long-obsolete information from installation.sgml. Section
  16.2 pointed to platform-specific FAQ files that we removed way back in 8.4.
  Section 16.7 contained a bunch of information about AIX and HPUX bugs that
  were squashed decades ago, plus discussions of old compiler versions that are
  certainly moot now that we require C99 support.  Since we're obviously not
  maintaining this stuff carefully, just remove it.  The HPUX sub-section seems
  like it can go away entirely, since everything it said that was still
  applicable was redundant with material elsewhere in the chapter.  In passing,
  I couldn't resist the temptation to do a small amount of copy-editing on
  nearby text.  Back-patch to v12, since this stuff is surely obsolete in any
  branch that requires C99.  Discussion:
  https://postgr.es/m/15538.1567042743@...
  https://git.postgresql.org/pg/commitdiff/76c2af92666ea46bf893680e96079ecdc4e0e45d

- Doc: restructure documentation of the configure script's options. The list of
  configure options has grown long, and there was next to no organization to it,
  never mind any indication of which options were interesting to most people.
  Break it into several sub-sections to provide a bit of structure, and add some
  introductory text where it seems helpful to point people to particular
  options.  I failed to resist the temptation to do a small amount of
  word-smithing on some of the option descriptions, too. But mostly this is
  reorganization and addition of intro text.  Discussion:
  https://postgr.es/m/6384.1559917369@...
  https://git.postgresql.org/pg/commitdiff/137b03b862c21b90a86732120d0c98480daf22de

- Cosmetic improvements for options-handling code in ECPGconnect(). The comment
  describing the string format was a lie.  Make it agree with reality,
  add/improve some other comments, fix coding style for loops with empty bodies.
  Also add an Assert that we counted parameters correctly, because the
  spread-out logic for that looks pretty fragile.  No actual bugs fixed here, so
  no need to back-patch.  Discussion:
  https://postgr.es/m/848B1649C8A6274AA527C4472CA11EDD5FC70CBE@G01JPEXMBYT02
  https://git.postgresql.org/pg/commitdiff/b61a5e6a1f8d4d9e0bfe5d26bebfbb0687353c08

- Doc: describe the "options" allowed in an ECPG connection target string. These
  have been there a long time, but their format was never explained in the docs.
  Per complaint from Yusuke Egashira.  Discussion:
  https://postgr.es/m/848B1649C8A6274AA527C4472CA11EDD5FC70CBE@G01JPEXMBYT02
  https://git.postgresql.org/pg/commitdiff/756349c87b853f83c21726c2029f840643485f7f

Peter Geoghegan pushed:

- Remove obsolete nbtree page deletion comment. Commit efada2b8e92, which made
  the nbtree page deletion algorithm more robust, removed the concept of a
  half-dead internal page.  Remove a comment about half dead parent pages that
  was overlooked.
  https://git.postgresql.org/pg/commitdiff/b8b3a276d453f5d561341021c93ec05b158f4c65

Thomas Munro pushed:

- Avoid catalog lookups in RelationAllowsEarlyPruning().
  RelationAllowsEarlyPruning() performed a catalog scan, but is used in two
  contexts where that was a bad idea:  1.  In heap_page_prune_opt(), which runs
  very frequently in some large     scans.  This caused major performance
  problems in a field report     that was easy to reproduce.  2.  In
  TestForOldSnapshot(), which runs while we hold a buffer content     lock.
  It's not clear if this was guaranteed to be free of buffer     deadlock risk.
  The check was introduced in commit 2cc41acd8 and defended against a real
  problem: 9.6's hash indexes have no page LSN and so we can't allow early
  pruning (ie the snapshot-too-old feature).  We can remove the check from all
  later releases though: hash indexes are now logged, and there is no way to
  create UNLOGGED indexes on regular logged tables.  If a future release allows
  such a combination, it might need to put a similar check in place, but it'll
  need some more thought.  Back-patch to 10.  Author: Thomas Munro Reviewed-by:
  Tom Lane, who spotted the second problem Discussion:
  https://postgr.es/m/CA%2BhUKGKT8oTkp5jw_U4p0S-7UG9zsvtw_M47Y285bER6a2gD%2Bg%40mail.gmail.com
  Discussion:
  https://postgr.es/m/CAA4eK1%2BWy%2BN4eE5zPm765h68LrkWc3Biu_8rzzi%2BOYX4j%2BiHRw%40mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/720b59b55b84c8a346098cdbf3d34c7e554b0bf5

Heikki Linnakangas pushed:

- Fix overflow check and comment in GIN posting list encoding. The comment did
  not match what the code actually did for integers with the 43rd bit set. You
  get an integer like that, if you have a posting list with two adjacent TIDs
  that are more than 2^31 blocks apart. According to the comment, we would store
  that in 6 bytes, with no continuation bit on the 6th byte, but in reality, the
  code encodes it using 7 bytes, with a continuation bit on the 6th byte as
  normal.  The decoding routine also handled these 7-byte integers correctly,
  except for an overflow check that assumed that one integer needs at most 6
  bytes. Fix the overflow check, and fix the comment to match what the code
  actually does. Also fix the comment that claimed that there are 17 unused bits
  in the 64-bit representation of an item pointer. In reality, there are
  64-32-11=21.  Fitting any item pointer into max 6 bytes was an important
  property when this was written, because in the old pre-9.4 format, item
  pointers were stored as plain arrays, with 6 bytes for every item pointer. The
  maximum of 6 bytes per integer in the new format guaranteed that we could
  convert any page from the old format to the new format after upgrade, so that
  the new format was never larger than the old format. But we hardly need to
  worry about that anymore, and running into that problem during upgrade, where
  an item pointer is expanded from 6 to 7 bytes such that the data doesn't fit
  on a page anymore, is implausible in practice anyway.  Backpatch to all
  supported versions.  This also includes a little test module to test these
  large distances between item pointers, without requiring a 16 TB table. It is
  not backpatched, I'm including it more for the benefit of future development
  of new posting list formats.  Discussion:
  https://www.postgresql.org/message-id/33bfc20a-5c86-f50c-f5a5-58e9925d05ff%40iki.fi
  Reviewed-by: Masahiko Sawada, Alexander Korotkov
  https://git.postgresql.org/pg/commitdiff/bde7493d10898831100a0c6c233a5f3030bfcecd

Etsuro Fujita pushed:

- Fix typos in regression test comments.
  https://git.postgresql.org/pg/commitdiff/317b3d7ae271eca64d49c9c0fddccc3b1e2cb1f6

== Pending Patches ==

Sergei Kornilov sent in another revision of a patch to change the ereport level
for QueuePartitionConstraintValidation.

Gareth Palmer sent in another revision of a patch to implement INSERT ... SET.

Magnus Hagander sent in another revision of a patch to implement online
checksums, this time with global barriers.

Peter Eisentraut sent in another revision of a patch to prevent getting errors
of the form "Could not read from file ...: Success."

Anastasia Lubennikova sent in a patch to improve the BTScanPos comments.

Fabien COELHO sent in another revision of a patch to enable pgbench to create
partitioned tables.

Movead Li sent in two more revisions of a patch to improve the test coverage.

Andrey Borodin sent in two revisions of a patch to build GiST indexes faster
using z-order sorting.

Alexander Korotkov sent in another revision of a patch to add support for the
jsonpath .datetime() method.

Asim R P sent in another revision of a patch to implement a fault injection
framework.

Kyotaro HORIGUCHI sent in two more revisions of a patch to fix the WAL-skipping
feature.

Dmitry Dolgov sent in a patch to add copy freeze and maintenance_work_mem
options to pgbench.

Thomas Munro sent in a patch to stop performing catalog lookups in
TestForOldSnapshot().

Euler Taveira de Oliveira and Aleksey Kondratov traded patches to make it
possible to do row filtering for logical replication.

Sergei Kornilov sent in another revision of a patch to make it possible to
change primary_conninfo online.

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

Heikki Linnakangas sent in a patch to fix a bug in BRIN inclusion
memory_handling.

Fabien COELHO sent in a patch to increase the psql test coverage from 41% to
88%.

Dmitry Dolgov sent in another revision of a patch to implement index skip scans.

Sergei Kornilov sent in a patch to implement pg_get_databasebyid(oid).

Joe Conway sent in a patch to add support for seccomp-bpf.

fn_ln sent in three more revisions of a patch to fix some inconsistent behavior
in chained transactions.

Michaël Paquier and Tom Lane traded patches to improve error detection in TAP
tests.

Taylor Vesely sent in another revision of a patch to implement memory-bounded
hash aggregation.

Jeevan Chalke sent in two revisions of a patch to fix a bug where basebackup.c's
sendFile() ignores read errors.

Aleksey Kondratov sent in another revision of a patch to ensure that the
BGWorkers pool for streamed transactions applies without spilling to disk.

Fabien COELHO and Michaël Paquier traded patches to recombine the str2*int64
functions to be in one place.

Masahiko Sawada sent in another revision of a patch to add a RESUME option to
VACUUM and autovacuum.

Moon Insung sent in a patch to fix a bug in GIN metapages.

Richard Guo sent in another revision of a patch to create any EC-derived
joinclauses of form 'outer_em = inner_em'.

Andres Freund sent in a WIP patch to implement generic functions for Node types
using generated metadata.

Anastasia Lubennikova sent in another revision of a patch to compress storage of
duplicates in B-tree indexes.

Ian Barwick sent in a patch to update the documents for the PL/pgSQL sample
function.

Thomas Munro sent in a patch to consolidate code that makes a sorted array
unique.

Ian Barwick sent in a patch to psql which adds tab completion for \df slash
command suffixes.

Masahiko Sawada sent in another revision of a patch to add a CLUSTER command
progress monitor.

Nikhil Sontakke sent in another revision of a patch to add batch and pipelining
support to libpq.

Fabien COELHO sent in a patch to add a TAP test for the bug fix to pg_upgrade
which ensured that it errors out on too many command-line arguments.

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

Rajkumar Raghuwanshi sent in a patch to add some TAP tests for pg_combinebackup.

Aleksey Kondratov sent in another revision of a patch to enable CLUSTER, VACUUM
FULL and REINDEX to change tablespaces on the fly.

Noah Misch sent in a patch to add some tests for AIX's atomic fetches.

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

Aleksey Kondratov sent in a patch to write the visibility map during
CLUSTER/VACUUM FULL.

Pierre Ducroquet sent in a patch to add a reset_computed_values function to
pg_stat_statements.