== 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 [hidden email].
== 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:
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.
- 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
- 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/20190807132422.GC15695@... 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
- 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:
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 <[hidden email]> 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:
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
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
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
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,
Tom Lane sent in a patch to add a shell program which checks headers for
|Free forum by Nabble||Edit this page|