== PostgreSQL Weekly News - August 18, 2019 ==

От: David Fetter
Тема: == PostgreSQL Weekly News - August 18, 2019 ==
Дата: ,
Msg-id: 20190818185737.GA27102@fetter.org
(см: обсуждение, исходный текст)
Список: pgsql-announce

== PostgreSQL Weekly News - August 18, 2019 ==

== PostgreSQL Product News ==

Pgpool-II 4.0.6, 3.7.11, 3.6.18, 3.5.22 and 3.4.25 released.

PostGIS 3.0.0alpaha4, 2.5.3, 2.4.8, and 2.3.10, the industry standard
geographic information system package for PostgreSQL, released.

temboard 4.0, a management tool for PostgreSQL, released.

== PostgreSQL Jobs for August ==


== PostgreSQL Local ==

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

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

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

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

== Applied Patches ==

Alexander Korotkov pushed:

- Adjust string comparison in jsonpath. We have implemented jsonpath string
  comparison using default database locale. However, standard requires us to
  compare Unicode codepoints.  This commit implements that, but for performance
  reasons we still use per-byte comparison for "==" operator.  Thus, for
  consistency other comparison operators do per-byte comparison if Unicode
  codepoints appear to be equal.  In some edge cases, when same Unicode
  codepoints have different binary representations in database encoding, we
  diverge standard to achieve better performance of "==" operator.  In future to
  implement strict standard conformance, we can do normalization of input JSON
  strings.  Original patch was written by Nikita Glukhov, rewritten by me.
  Reported-by: Markus Winand Discussion:
  https://postgr.es/m/8B7FA3B4-328D-43D7-95A8-37B8891B8C78%40winand.at Author:
  Nikita Glukhov, Alexander Korotkov Backpatch-through: 12

- Fix string comparison in jsonpath. Take into account pg_server_to_any() may
  return input string "as is".  Reported-by: Andrew Dunstan, Thomas Munro
  Author: Alexander Korotkov, Thomas Munro Backpatch-through: 12

Tom Lane pushed:

- Partially revert "Insert temporary debugging output in regression tests.".
  This reverts much of commit f03a9ca4366d064d89b7cf7ed75d4e43f2ed0667, but
  leaves the relpages/reltuples probe in select_parallel.sql. The
  pg_stat_all_tables probes are unstable enough to be annoying, and it no longer
  seems likely that they will teach us anything more about the underlying
  problem.  I'd still like some more confirmation though that the observed plan
  instability is caused by VACUUM leaving relpages/reltuples as zero for one of
  these tables.  Discussion:

- Rationalize use of list_concat + list_copy combinations. In the wake of commit
  1cff1b95a, the result of list_concat no longer shares the ListCells of the
  second input.  Therefore, we can replace "list_concat(x, list_copy(y))" with
  just "list_concat(x, y)".  To improve call sites that were list_copy'ing the
  first argument, or both arguments, invent "list_concat_copy()" which produces
  a new list sharing no ListCells with either input.  (This is a bit faster than
  "list_concat(list_copy(x), y)" because it makes the result list the right size
  to start with.)  In call sites that were not list_copy'ing the second
  argument, the new semantics mean that we are usually leaking the second List's
  storage, since typically there is no remaining pointer to it.  We considered
  inventing another list_copy variant that would list_free the second input, but
  concluded that for most call sites it isn't worth worrying about, given the
  relative compactness of the new List representation. (Note that in cases where
  such leakage would happen, the old code already leaked the second List's
  header; so we're only discussing the size of the leak not whether there is
  one.  I did adjust two or three places that had been troubling to free that
  header so that they manually free the whole second List.)  Patch by me; thanks
  to David Rowley for review.  Discussion:

- Remove EState.es_range_table_array. Now that list_nth is O(1), there's no good
  reason to maintain a separate array of RTE pointers rather than indexing into
  estate->es_range_table.  Deleting the array doesn't save all that much either;
  but just on cleanliness grounds, it's better not to have duplicate
  representations of the identical information.  Discussion:

- Fix planner's test for case-foldable characters in ILIKE with ICU. As coded,
  the ICU-collation path in pattern_char_isalpha() failed to consider regular
  ASCII letters to be case-varying.  This led to like_fixed_prefix treating too
  much of an ILIKE pattern as being a fixed prefix, so that indexscans derived
  from an ILIKE clause might miss entries that they should find.  Per bug #15892
  from James Inform.  This is an oversight in the original ICU patch (commit
  eccfef81e), so back-patch to v10 where that came in.  Discussion:

- Un-break pg_dump for pre-8.3 source servers. Commit 07b39083c inserted an
  unconditional reference to pg_opfamily, which of course fails on servers
  predating that catalog.  Fortunately, the case it's trying to solve can't
  occur on such old servers (AFAIK). Hence, just skip the additional code when
  the source predates 8.3.  Per bug #15955 from sly.  Back-patch to all
  supported branches, like the previous patch.  Discussion:

- Fix ALTER SYSTEM to cope with duplicate entries in postgresql.auto.conf. ALTER
  SYSTEM itself normally won't make duplicate entries (although up till this
  patch, it was possible to confuse it by writing case variants of a GUC's
  name).  However, if some external tool has appended entries to the file, that
  could result in duplicate entries for a single GUC name.  In such a situation,
  ALTER SYSTEM did exactly the wrong thing, because it replaced or removed only
  the first matching entry, leaving the later one(s) still there and hence still
  determining the active value.  This patch fixes that by making ALTER SYSTEM
  sweep through the file and remove all matching entries, then (if not ALTER
  SYSTEM RESET) append the new setting to the end.  This means entries will be
  in order of last setting rather than first setting, but that shouldn't hurt
  anything.  Also, make the comparisons case-insensitive so that the right
  things happen if you do, say, ALTER SYSTEM SET "TimeZone" = 'whatever'.  This
  has been broken since ALTER SYSTEM was invented, so back-patch to all
  supported branches.  Ian Barwick, with minor mods by me  Discussion:

- Doc: improve documentation about postgresql.auto.conf. Clarify what external
  tools can do to this file, and add a bit of detail about what ALTER SYSTEM
  itself does.  Discussion:

- Use a hash table to de-duplicate NOTIFY events faster. Previously, async.c got
  rid of duplicate notifications by scanning the list of pending events to
  compare each one to the proposed new event.  This works okay for very small
  numbers of distinct events, but degrades as O(N^2) for many events.  We can
  improve matters by using a hash table to probe for duplicates.  So as not to
  add a lot of overhead for the simple cases that the code did handle well
  before, create the hash table only once a (sub)transaction has queued more
  than 16 distinct notify events.  A downside is that we now have to do
  per-event work to propagate a successful subtransaction's notify events up to
  its parent. (But this isn't significant unless the subtransaction had many
  events, in which case the O(N^2) behavior would have been in play already, so
  we still come out ahead.)  We can make some lemonade out of this lemon,
  though: since we must examine each event anyway, it's now possible to
  de-duplicate events fully, rather than skipping that for events merged up from
  subtransactions.  Hence, remove the old weasel wording in notify.sgml about
  whether de-duplication happens or not, and adjust the test case in
  async-notify.spec that exhibited the old behavior.  While at it, rearrange the
  definition of struct Notification to make it more compact and require just one
  palloc per event, rather than two or three.  This saves space when there are a
  lot of events, in fact more than enough to buy back the space needed for the
  hash table.  Patch by me, based on discussions around a different patch
  submitted by Filip Rembiałkowski.  Discussion:

- Fix plpgsql to re-look-up composite type names at need. Commit 4b93f5799
  rearranged things in plpgsql to make it cope better with composite types
  changing underneath it intra-session.  However, I failed to consider the case
  of a composite type being dropped and recreated entirely. In my defense, the
  previous coding didn't consider that possibility at all either --- but it
  would accidentally work so long as you didn't change the type's field list,
  because the built-at-compile-time list of component variables would then still
  match the type's new definition.  The new coding, however, occasionally tries
  to re-look-up the type by OID, and then fails to find the dropped type.  To
  fix this, we need to save the TypeName struct, and then redo the type OID
  lookup from that.  Of course that's expensive, so we don't want to do it every
  time we need the type OID.  This can be fixed in the same way that 4b93f5799
  dealt with changes to composite types' definitions: keep an eye on the type's
  typcache entry to see if its tupledesc has been invalidated. (Perhaps, at some
  point, this mechanism should be generalized so it can work for non-composite
  types too; but for now, plpgsql only tries to cope with intra-session
  redefinitions of composites.)  I'm slightly hesitant to back-patch this into
  v11, because it changes the contents of struct PLpgSQL_type as well as the
  signature of plpgsql_build_datatype(), so in principle it could break code
  that is poking into the innards of plpgsql.  However, the only popular
  extension of that ilk is pldebugger, and it doesn't seem to be affected.
  Since this is a regression for people who were relying on the old behavior, it
  seems worth taking the small risk of causing compatibility issues.  Per bug
  #15913 from Daniel Fiori.  Back-patch to v11 where 4b93f5799 came in.
  Discussion: https://postgr.es/m/

- Prevent possible double-free when update trigger returns old tuple. This is a
  variant of the problem fixed in commit 25b692568, which unfortunately we
  failed to detect at the time.  If an update trigger returns the "old" tuple,
  as it's entitled to do, then a subsequent iteration of the loop in
  ExecBRUpdateTriggers would have "oldtuple" equal to "trigtuple" and would fail
  to notice that it shouldn't free that.  In addition to fixing the code, extend
  the test case added by 25b692568 so that it covers multiple-trigger-iterations
  cases.  This problem does not manifest in v12/HEAD, as a result of the
  relevant code having been largely rewritten for slotification. However,
  include the test case into v12/HEAD anyway, since this is clearly an area that
  someone could break again in future.  Per report from Piotr Gabriel Kosinski.
  Back-patch into all supported branches, since the bug seems quite old.
  Diagnosis and code fix by Thomas Munro, test case by me.  Discussion:

- Make deadlock-parallel isolation test more robust. This test failed fairly
  reproducibly on some CLOBBER_CACHE_ALWAYS buildfarm animals.  The cause seems
  to be that if a parallel worker is slow enough to reach its lock wait, it may
  not be released by the first deadlock check run, and then later deadlock
  checks might decide to unblock the d2 session instead of the d1 session,
  leaving us in an undetected deadlock state (since the isolationtester client
  is waiting for d1 to complete first).  Fix by introducing an additional lock
  wait at the end of the d2a1 step, ensuring that the deadlock checker will
  recognize that d1 has to be unblocked before d2a1 completes.  Also reduce
  max_parallel_workers_per_gather to 3 in this test.  With the default
  max_worker_processes value, we were only getting one parallel worker for the
  d2a1 step, which is not the case I hoped to test.  We should get 3 for d1a2
  and 2 for d2a1, as the code stands; and maybe 3 for d2a1 if somebody figures
  out why the last parallel worker slot isn't free already.  Discussion:

Peter Geoghegan pushed:

- amcheck: Skip unlogged relations during recovery. contrib/amcheck failed to
  consider the possibility that unlogged relations will not have any main
  relation fork files when running in hot standby mode.  This led to low-level
  "can't happen" errors that complain about the absence of a relfilenode file.
  To fix, simply skip verification of unlogged index relations during recovery.
  In passing, add a direct check for the presence of a main fork just before
  verification proper begins, so that we cleanly verify the presence of the main
  relation fork file.  Author: Andrey Borodin, Peter Geoghegan Reported-By:
  Andrey Borodin Diagnosed-By: Andrey Borodin Discussion:
  Backpatch: 10-, where amcheck was introduced.

- Use PageIndexTupleOverwrite() within nbtree. Use the PageIndexTupleOverwrite()
  bufpage.c routine within nbtree instead of deleting a tuple and re-inserting
  its replacement.  This makes the intent of affected code slightly clearer.  It
  also makes CREATE INDEX slightly faster, since there is no longer a need to
  shift every leaf page's line pointer array back and forth during index builds.
  Author: Peter Geoghegan, Anastasia Lubennikova Reviewed-By: Anastasia
  Lubennikova Discussion:

- Remove obsolete nbtree README commentary. Commit d2086b08b02 removed almost
  all cases where nbtree must release a read buffer lock and acquire a write
  buffer lock instead, so remaining cases in which that's still necessary are
  not notable enough to appear in the nbtree README.  More importantly, holding
  on to a buffer pin in cases where nbtree must trade a read lock for a write
  lock is very unlikely to save any I/O. This seems to have been a long
  overlooked throwback to a time when nbtree cared about write-ordering
  dependencies, and performed synchronous buffer writes.  It hasn't worked that
  way in many years.

- Remove block number field from nbtree stack. The initial value of the nbtree
  stack downlink block number field recorded during an initial descent of the
  tree wasn't actually used. Both _bt_getstackbuf() callers overwrote the value
  with their own value.  Remove the block number field from the stack struct,
  and add a child block number argument to _bt_getstackbuf() in its place.  This
  makes the overall design of _bt_getstackbuf() clearer.  Author: Peter
  Geoghegan Reviewed-By: Anastasia Lubennikova Discussion:

Michaël Paquier pushed:

- Fix random regression failure in test case "temp". This test case could fail
  because of an incorrect result ordering when looking up at pg_class entries.
  This commit adds an ORDER BY to the culprit query.  The cause of the failure
  was likely caused by a plan switch.  By default, the planner would likely
  choose an index-only scan or an index scan, but even a small change in the
  startup cost could have caused a bitmap heap scan to be chosen, causing the
  failure.  While on it, switch some filtering quals to a regular expression as
  per an idea of Tom Lane.  As previously shaped, the quals would have selected
  any relations whose name begins with "temp".  And that could cause failures if
  another test running in parallel began to use similar relation names.  Per
  report from buildfarm member anole, though the failure was very rare.  This
  test has been introduced by 319a810, so backpatch down to v10.  Discussion:
  https://postgr.es/m/ Backpatch-through: 10

- Fix inconsistencies and typos in the tree, take 10. This addresses some issues
  with unnecessary code comments, fixes various typos in docs and comments, and
  removes some orphaned structures and definitions.  Author: Alexander Lakhin
  Discussion: https://postgr.es/m/

- Fix random regression failure in test case "collate.icu.utf8". This is a fix
  similar to 2d7d67cc, where slight plan alteration can cause a random failure
  of this regression test because of an incorect tuple ordering, except that
  this one involves lookups of pg_type. Similarly to the other case, add ORDER
  BY clauses to ensure the output order.  The failure has been seen at least
  once on buildfarm member skink.  Reported-by: Thomas Munro Discussion:
  Backpatch-through: 12

Peter Eisentraut pushed:

- Update to DocBook 4.5. This moves us to the latest minor version of DocBook 4.
  It requires no markup changes.

- initdb: Remove obsolete locale handling. The method of passing LC_COLLATE and
  LC_CTYPE to the backend during initdb is obsolete as of
  61d967498802ab86d8897cb3c61740d7e9d712f6. This can all be removed.
  Reviewed-by: Tom Lane <> Discussion:

- Improve Assert output. If an assertion expression contained a macro, the
  failed assertion message would print the expanded macro, which is usually
  unhelpful and confusing.  Restructure the Assert macros to not expand any
  macros when constructing the failure message.  This also fixes that the
  existing output for Assert et al. shows the *inverted* condition, which is
  also confusing and not how assertions usually work.  Discussion:

- Remove obsolete reference to Irix.

Álvaro Herrera pushed:

- Don't constraint-exclude partitioned tables as much. We only need to invoke
  constraint exclusion on partitioned tables when they are a partition, and they
  themselves contain a default partition; it's not necessary otherwise, and it's
  expensive, so avoid it.  Also, we were trying once for each clause separately,
  but we can do it for all the clauses at once.  While at it, centralize setting
  of RelOptInfo->partition_qual instead of computing it in slightly different
  ways in different places.  Per complaints from Simon Riggs about 4e85642d935e;
  reviewed by Yuzuko Hosoya, Kyotaro Horiguchi.  Author: Amit Langote.  I
  (Álvaro) again mangled the patch somewhat. Discussion:

Etsuro Fujita pushed:

- Remove useless bms_free() calls in build_child_join_rel(). These seem to be
  leftovers from the original partitionwise-join patch, perhaps.  Discussion:

Andres Freund pushed:

- Remove redundant prototypes for SQL callable functions. These aren't needed
  after 352a24a1f9d6. The remaining prototypes are not defined on the SQL level.
  Author: Andres Freund Discussion:

- Don't include utils/array.h from acl.h. For most uses of acl.h the details of
  how "Acl" internally looks like are irrelevant. It might make sense to move a
  lot of the implementation details into a separate header at a later point.
  The main motivation of this change is to avoid including fmgr.h (via array.h,
  which needs it for exposed structs) in a lot of files that otherwise don't
  need it. A subsequent commit will remove the fmgr.h include from a lot of
  files.  Directly include utils/array.h and utils/expandeddatum.h from the
  files that need them, but previously included them indirectly, via acl.h.
  Author: Andres Freund Discussion:

- Remove fmgr.h includes from headers that don't really need it. Most of the
  fmgr.h includes were obsoleted by 352a24a1f9d6f7d4abb1. A few others can be
  obsoleted using the underlying struct type in an implementation detail.
  Author: Andres Freund Discussion:

- Add missing fmgr.h include.

- Add default_table_access_method to postgresql.conf.sample. Reported-By: Heikki
  Linnakangas Author: Michael Paquier Discussion:
  https://postgr.es/m/ Backpatch:
  12-, where pluggable table access methods were introduced

== Pending Patches ==

Alexander Lakhin sent in another revision of a patch to fix typos and

KaiGai Kohei sent in a patch to add a hook which allows extension to provide an
extra decision whether add_path() retains the dominated path by other new path,
from different dimensions.

Alexander Korotkov sent in a patch to improve checking for missing parent links
by traversing from one downlink to subsequent using rightlinks instead of
collecting lossy bitmap.

Kasahara Tatsuhito sent in a patch to shorten the elapsed time for truncating
the heap during VACUUM.

Peter Eisentraut sent in another revision of a patch to add backtrace support.

Fabien COELHO sent in a patch to rework variable management in pgbench.

Peter Eisentraut sent in another revision of a patch to add UNIX domain socket
support to Windows.

Dmitry Igrishin sent in another revision of a patch to fix the build on Windows.

Anastasia Lubennikova sent in another revision of a patch to add a
wait_for_archive option to pg_stop_backup().

Peter Eisentraut sent in another revision of a patch to add explicit_bzero().

Konstantin Knizhnik sent in two more revisions of a patch to add a built-in
connection pooler.

Anastasia Lubennikova sent in two more revisions of a patch to compress and
deduplicate in nbtree.

Dilip Kumar sent in another revision of a patch to create an UNDO interface

Konstantin Knizhnik sent in another revision of a patch to implement global
temporary tables.

David Rowley sent in a patch to call table_finish_bulk_insert less often.

Yuli Khodorkovskiy sent in a patch to add a hook to allow extensions to set
worker metadata and refactor child process startup using same.

Tom Lane sent in another revision of a patch to detect duplicate NOTIFYs by
hashing and make a better notification structure.

Tom Lane sent in a patch to use the data directory inode for IPC keys.

Peter Eisentraut sent in a patch to enable the cluster owner to bypass

Heikki Linnakangas sent in another revision of a patch to implement Zedstore.

Andrey Borodin sent in a patch to do rightlink verification with lock coupling
in amcheck nbtree.

Etsuro Fujita sent in a patch to remove some useless bms_free calls.

Masahiko Sawada sent in another revision of a patch to implement table-level
transparent data encryption.

PSS sent in another revision of a patch to add type info support functions for
functions that use the "any" type.

Etsuro Fujita sent in another revision of a patch to modify the
partition-matching algorithm.

Jeevan Chalke sent in another revision of a patch to add support for incremental

Álvaro Herrera sent in a WIP patch to patch to use huge pallocs on encoding

Gareth Palmer sent in another revision of a patch to implement the INSERT ...
SET syntax, which pairs column names with their values.

Binguo Bao sent in another revision of a patch to de-TOAST using an iterator.

Peter Eisentraut sent in a patch to clean up SCRAM attribute processing by
correcting the comment for read_any_attr(), and giving a clearer error message
when parsing at the end of the string when the client-final-message does not
contain a "p" attribute (for some reason).

Daniel Migowski sent in a patch to add a new GUC, prepared_statement_limit, to
limit the memory used by prepared statements.

Tara sent in a patch to clarify the docs for creating a role.

Noah Misch sent in another revision of a patch to to fix a WAL-logging issue
that dates back to 9.4.3.

Juan José Santamaría Flecha sent in a patch to make it possible to use localized
month names in to_date() and to_timestamp().

Fabien COELHO sent in a PoC patch to make it possible to create a full set of
partitions at creation time of a partitioned table.  This is especially helpful
with hash partitions, which don't really exist in isolation.

Nino Floris sent in a patch to add binary protocol support for ltree, lquery,
and ltxtquery.

Tom Lane sent in a patch to add a shell program which checks headers for

В списке pgsql-announce по дате сообщения:

От: David Fetter
Сообщение: == PostgreSQL Weekly News - August 18, 2019 ==
От: Pavan Deolasee
Сообщение: Announcing PGConf India 2020