== PostgreSQL Weekly News - February 11 2018 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - February 11 2018 ==
Date: 2018-02-11 19:59:13
Message-ID: 20180211195912.GA14885@fetter.org
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-announce

== PostgreSQL Weekly News - February 11 2018 ==

PostgreSQL security releases 10.2, 9.6.7, 9.5.11, 9.4.16, 9.3.21, and 9.2.25 are out. Please read
the announcement below and upgrade at the next available downtime.

Swiss PGDay 2018 will take place in Rapperswil (near Zurich) on June 29, 2018.
The CfP is open February 6, 2018 through April 14, 2018, and registration is
open February 6, 2018 through June 28, 2018.

== PostgreSQL Product News ==

pgwatch2 1.3.0, a monitoring tool for PostgreSQL, released.

TablePlus, a modern, mac native tool for relational databases with support for
PostgreSQL, released.

psycopg2 2.7.4, a Python connector for PostgreSQL, released.

== PostgreSQL Jobs for February ==


== PostgreSQL Local ==

Prague PostgreSQL Developer Day 2018 (P2D2 2018) is a two-day
conference that will be held on February 14-15 2018 in Prague, Czech Republic.

PGConf India 2018 will be on February 22-23, 2018 in Bengaluru, Karnataka.

PostgreSQL(at)SCaLE is a two day, two track event which takes place on
March 8-9, 2018, at Pasadena Convention Center, as part of SCaLE 16X.

Nordic PGDay 2018 will be held in Oslo, Norway, at the Radisson Blu Hotel
Nydalen, on March 13, 2018. Registration is open and the schedule is posted.

pgDay Paris 2018 will be held in Paris, France at the Espace Saint-Martin, on
March 15 2018. Registration is open.

PGConf APAC 2018 will be held in Singapore March 22-23, 2018.

The German-speaking PostgreSQL Conference 2018 will take place on April 13th,
2018 in Berlin.

PGConfNepal 2018 will be held May 4-5, 2018 at Kathmandu University, Dhulikhel,
Nepal. The CfP is open through February 15, 2018.

PGCon 2018 will take place in Ottawa on May 29 - June 1, 2018.

PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. The
CfP is open until February 28, 2018.

== 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 EST5EDT. Please send English
language ones to david(at)fetter(dot)org, German language to pwn(at)pgug(dot)de, Italian
language to pwn(at)itpug(dot)org(dot)

== Applied Patches ==

Peter Eisentraut pushed:

- doc: Update mentions of MD5 in the documentation. Reported-by: Shay Rojansky

- Add more information_schema columns - table_constraints.enforced -
triggers.action_order - triggers.action_reference_old_table -
triggers.action_reference_new_table. Reviewed-by: Michael Paquier

- Make new triggers tests more robust. Add explicit collation on the trigger
name to avoid locale dependencies. Also restrict the tables selected, to
avoid interference from concurrently running tests.

- Refine SSL tests test name reporting. Instead of using the psql/libpq
connection string as the displayed test name and relying on "notes" and source
code comments to explain the tests, give the tests self-explanatory names,
like we do elsewhere. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> Reviewed-by: Daniel Gustafsson <daniel(at)yesql(dot)se>

Tom Lane pushed:

- Skip setting up shared instrumentation for Hash node if not needed. We don't
need to set up the shared space for hash join instrumentation data if
instrumentation hasn't been requested. Let's follow the example of the
similar Sort node code and save a few cycles by skipping that when we can.
This reverts commit d59ff4ab3 and instead allows us to use the safer choice of
passing noError = false to shm_toc_lookup in ExecHashInitializeWorker, since
if we reach that call there should be a TOC entry to be found. Thomas Munro
Discussion: https://postgr.es/m/E1ehkoZ-0005uW-43%40gemulon.postgresql.org

- Ensure that all temp files made during pg_upgrade are non-world-readable.
pg_upgrade has always attempted to ensure that the transient dump files it
creates are inaccessible except to the owner. However, refactoring in commit
76a7650c4 broke that for the file containing "pg_dumpall -g" output; since
then, that file was protected according to the process's default umask. Since
that file may contain role passwords (hopefully encrypted, but passwords
nonetheless), this is a particularly unfortunate oversight. Prudent users of
pg_upgrade on multiuser systems would probably run it under a umask tight
enough that the issue is moot, but perhaps some users are depending only on
pg_upgrade's umask changes to protect their data. To fix this in a
future-proof way, let's just tighten the umask at process start. There are no
files pg_upgrade needs to write at a weaker security level; and if there were,
transiently relaxing the umask around where they're created would be a safer
approach. Report and patch by Tom Lane; the idea for the fix is due to Noah
Misch. Back-patch to all supported branches. Security: CVE-2018-1053

- Doc: move info for btree opclass implementors into main documentation. Up to
now, useful info for writing a new btree opclass has been buried in the
backend's nbtree/README file. Let's move it into the SGML docs, in
preparation for extending it with info about "in_range" functions in the
upcoming window RANGE patch. To do this, I chose to create a new chapter for
btree indexes in Part VII (Internals), parallel to the chapters that exist for
the newer index AMs. This is a pretty short chapter as-is. At some point
somebody might care to flesh it out with more detail about btree internals,
but that is beyond the scope of my ambition for today. Discussion:

- Support all SQL:2011 options for window frame clauses. This patch adds the
ability to use "RANGE offset PRECEDING/FOLLOWING" frame boundaries in window
functions. We'd punted on that back in the original patch to add window
functions, because it was not clear how to do it in a reasonably
data-type-extensible fashion. That problem is resolved here by adding the
ability for btree operator classes to provide an "in_range" support function
that defines how to add or subtract the RANGE offset value. Factoring it this
way also allows the operator class to avoid overflow problems near the ends of
the datatype's range, if it wishes to expend effort on that. (In the
committed patch, the integer opclasses handle that issue, but it did not seem
worth the trouble to avoid overflow failures for datetime types.) The patch
includes in_range support for the integer_ops opfamily (int2/int4/int8) as
well as the standard datetime types. Support for other numeric types has been
requested, but that seems like suitable material for a follow-on patch. In
addition, the patch adds GROUPS mode which counts the offset in ORDER-BY peer
groups rather than rows, and it adds the frame_exclusion options specified by
SQL:2011. As far as I can see, we are now fully up to spec on window framing
options. Existing behaviors remain unchanged, except that I changed the
errcode for a couple of existing error reports to meet the SQL spec's
expectation that negative "offset" values should be reported as SQLSTATE
22013. Internally and in relevant parts of the documentation, we now
consistently use the terminology "offset PRECEDING/FOLLOWING" rather than
"value PRECEDING/FOLLOWING", since the term "value" is confusingly vague.
Oliver Ford, reviewed and whacked around some by me Discussion:

- Fix RelationBuildPartitionKey's processing of partition key expressions.
Failure to advance the list pointer while reading partition expressions from a
list results in invoking an input function with inappropriate data, possibly
leading to crashes or, with carefully crafted input, disclosure of arbitrary
backend memory. Bug discovered independently by Álvaro Herrera and David
Rowley. This patch is by Álvaro but owes something to David's proposed fix.
Back-patch to v10 where the issue was introduced. Security: CVE-2018-1052

- Last-minute updates for release notes. Security: CVE-2018-1052, CVE-2018-1053

- Fix oversight in CALL argument handling, and do some minor cleanup. CALL
statements cannot support sub-SELECTs in the arguments of the called
procedure, since they just use ExecEvalExpr to evaluate such arguments. Teach
transformSubLink() to reject the case, as it already does for other contexts
in which subqueries are not supported. In passing,
s/EXPR_KIND_CALL/EXPR_KIND_CALL_ARGUMENT/ to make that enum symbol line up
more closely with the phrasing of the error messages it is associated with.
And fix someone's weak grasp of English grammar in the preceding
EXPR_KIND_PARTITION_EXPRESSION addition. Also update an incorrect comment in
resolve_unique_index_expr (possibly it was correct when written, but nowadays
transformExpr definitely does reject SRFs here). Per report from Pavel
Stehule --- but this resolves only one of the bugs he mentions. Discussion:

- Avoid premature free of pass-by-reference CALL arguments. Prematurely freeing
the EState used to evaluate CALL arguments led, in some cases, to passing
dangling pointers to the procedure. This was masked in trivial cases because
the argument pointers would point to Const nodes in the original expression
tree, and in some other cases because the result value would end up in the
standalone ExprContext rather than in memory belonging to the EState --- but
that wasn't exactly high quality programming either, because the standalone
ExprContext was never explicitly freed, breaking assorted API contracts. In
addition, using a separate EState for each argument was just silly. So let's
use just one EState, and one ExprContext, and make the latter belong to the
former rather than be standalone, and clean up the EState (and hence the
ExprContext) post-call. While at it, improve the function's commentary a bit.
Discussion: https://postgr.es/m/29173.1518282748@sss.pgh.pa.us

- Fix assorted errors in pg_dump's handling of extended statistics objects.
pg_dump supposed that a stats object necessarily shares the same schema as its
underlying table, and that it doesn't have a separate owner. These things may
have been true during early development of the feature, but they are not true
as of v10 release. Failure to track the object's schema separately turns out
to have only limited consequences, because pg_get_statisticsobjdef() always
schema- qualifies the target object name in the generated CREATE STATISTICS
command (a decision out of step with the rest of ruleutils.c, but I digress).
Therefore the restored object would be in the right schema, so that the only
problem is that the TOC entry would be mislabeled as to schema. That could
lead to wrong decisions for schema-selective restores, for example. The
ownership issue is a bit more serious: not only was the TOC entry potentially
mislabeled as to owner, but pg_dump didn't bother to issue an ALTER OWNER
command at all, so that after restore the stats object would continue to be
owned by the restoring superuser. A final point is that decisions as to
whether to dump a stats object or not were driven by whether the underlying
table was dumped or not. While that's not wrong on its face, it won't scale
nicely to the planned future extension to cross-table statistics. Moreover,
that design decision comes out of the view of stats objects as being auxiliary
to a particular table, like a rule or trigger, which is exactly where the
above problems came from. Since we're now treating stats objects more like
independent objects in their own right, they ought to behave like standalone
objects for this purpose too. So change to using the generic
selectDumpableObject() logic for them (which presently amounts to "dump if
containing schema is to be dumped"). Along the way to fixing this,
restructure so that getExtendedStatistics collects the identity info (only)
for all extended stats objects in one query, and then for each object actually
being dumped, we retrieve the definition in dumpStatisticsExt. This is
necessary to ensure that schema-qualification in the generated CREATE
STATISTICS command happens with respect to the search path that pg_dump will
now be using at restore time (ie, the schema the stats object is in, not that
of the underlying table). It's probably also significantly faster in the
typical scenario where only a minority of tables have extended stats.
Back-patch to v10 where extended stats were introduced. Discussion:

Robert Haas pushed:

- Fix possible crash in partition-wise join. The previous code assumed that
we'd always succeed in creating child-joins for a joinrel for which
partition-wise join was considered, but that's not guaranteed, at least in the
case where dummy rels are involved. Ashutosh Bapat, with some wordsmithing by
me. Discussion:

- Avoid valgrind complaint about write() of uninitalized bytes.
LogicalTapeFreeze() may write out its first block when it is dirty but not
full, and then immediately read the first block back in from its BufFile as a
BLCKSZ-width block. This can only occur in rare cases where very few tuples
were written out, which is currently only possible with parallel external
tuplesorts. To avoid valgrind complaints, tell it to treat the tail of
logtape.c's buffer as defined. Commit
9da0cc35284bdbe8d442d732963303ff0e0a40bc exposed this problem but did not
create it. LogicalTapeFreeze() has always tended to write out some amount of
garbage bytes, but previously never wrote less than one block of data in
total, so the problem was masked. Per buildfarm members lousyjack and skink.
Peter Geoghegan, based on a suggestion from Tom Lane and me. Some comment
revisions by me.

- Fix incorrect grammar. Etsuro Fujita Discussion:

- Remove prototype for fmgr() function, which no longer exists. Commit
5ded4bd21403e143dd3eb66b92d52732fdac1945 removed the code for this function,
but neglected to remove the prototype and associated comments. Dagfinn Ilmari
Mannsåker Discussion: http://postgr.es/m/d8j4lmuxjzk.fsf@dalvik.ping.uio.no

- Update out-of-date comment in StartupXLOG. Commit
4b0d28de06b28e57c540fca458e4853854fbeaf8 should have updated this comment, but
did not. Thomas Munro Discussion:

- postgres_fdw: Push down UPDATE/DELETE joins to remote servers. Commit
0bf3ae88af330496517722e391e7c975e6bad219 allowed direct foreign table
modification; instead of fetching each row, updating it locally, and then
pushing the modification back to the remote side, we would instead do all the
work on the remote server via a single remote UPDATE or DELETE command.
However, that commit only enabled this optimization when join tree consisted
only of the target table. This change allows the same optimization when an
UPDATE statement has a FROM clause or a DELETE statement has a USING clause.
This works much like ordinary foreign join pushdown, in that the tables must
be on the same remote server, relevant parts of the query must be
pushdown-safe, and so forth. Etsuro Fujita, reviewed by Ashutosh Bapat,
Rushabh Lathia, and me. Some formatting corrections by me. Discussion:
http://postgr.es/m/5A57193A.2080003@lab.ntt.co.jp Discussion:

- postgres_fdw: Remove CTID output from some tests. Commit
1bc0100d270e5bcc980a0629b8726a32a497e788 added these tests, but they're not
stable enough to survive in the buildfarm. Remove CTIDs from the output in
the hopes of fixing that.

- Fix possible infinite loop with Parallel Append. When the previously-chosen
plan was non-partial, all pa_finished flags for partial plans are now set, and
pa_next_plan has not yet been set to INVALID_SUBPLAN_INDEX, the previous code
could go into an infinite loop. Report by Rajkumar Raghuwanshi. Patch by
Amit Khandekar and me. Review by Kyotaro Horiguchi. Discussion:

- Avoid listing the same ResultRelInfo in more than one EState list. Doing so
causes EXPLAIN ANALYZE to show trigger statistics multiple times. Commit
2f178441044be430f6b4d626e4dae68a9a6f6cec seems to be to blame for this. Amit
Langote, revieed by Amit Khandekar, Etsuro Fujita, and me.

- Fix incorrect method name in comment. Atsushi Torikoshi Discussion:

- postgres_fdw: Attmempt to stabilize regression tests. Even after commit
882ea509fe7a4711fe25463427a33262b873dfa1, some buildfarm members are still
failing in the postgres_fdw tests. Try to fix that by disabling use of remote
statistics for some test cases. Etsuro Fujita Discussion:

- Clear stmt_timeout_active if we disable_all_timeouts. Otherwise, we can end
up with the flag set when the timeout is actually disabled, leading to
misbehavior. Commit f8e5f156b30efee5d0038b03e38735773abcb7ed introduced this
bug. Reported by Peter Eisentraut. Analysis and fix by Thomas Munro, tweaked
by me. Discussion:

- Mark assorted GUC variables as PGDLLIMPORT. This makes life easier for
extension authors. Metin Doslu Discussion:

Magnus Hagander pushed:

- Change default git repo URL to https. Since we now support the server side
handler for git over https (so we're no longer using the "dumb protocol"),
make https the primary choice for cloning the repository, and the git protocol
the secondary choice. In passing, also change the links to git-scm.com from
http to https. Reviewed by Stefan Kaltenbrunner and David G. Johnston

Álvaro Herrera pushed:

- Mention partitioned indexes in "Data Definition" chapter. We can now create
indexes more easily than before, so update this chapter to use the simpler
instructions. After an idea of Amit Langote. I (Álvaro) opted to do more
invasive surgery and remove the previous suggestion to create per-partition
indexes, which his patch left in place. Discussion:
https://postgr.es/m/eafaaeb1-f0fd-d010-dd45-07db0300f645@lab.ntt.co.jp Author:
Amit Langote, Álvaro Herrera

== Pending Patches ==

Mark Rofail sent in two more revisions of a patch to implement foreign key

Takayuki Tsunakawa sent in another revision of a patch to fix an ECPG bug where
freeing memory for pgtypes crashes on Windows.

Michaël Paquier sent in a patch to use base backup exclusion filters to reduce
data transferred with pg_rewind.

Michaël Paquier sent in a patch to fix a typo in a comment in
pg_multixact/offset in multixact.c.

Kyotaro HORIGUCHI sent in another revision of a patch to allow booleans to be
partition bounds.

Tomas Vondra sent in another revision of a patch to implement BRIN multi-range

Takayuki Tsunakawa sent in a patch to reset temp schema on connect.

Amit Langote sent in two more revisions of a patch to refactor the partition
tuple conversion maps handling code and initialize per-partition objects lazily
during tuple-routing.

Pierre Ducroquet sent in another revision of a patch atop the JIT patch to
support LLVM 9.1.

Ildus Kurbangaliev sent in another revision of a patch to implement custom
compression methods.

Nikhil Sontakke sent in another revision of a patch to fix some faulty Logical
Decoding and HeapTupleSatisfiesVacuum assumptions.

Thomas Munro sent in another revision of a patch to fix a comment in

Peter Geoghegan sent in a patch to mark logtape.c buffer's tail as defined to

Claudio Freire sent in four more revisions of a patch to enable VACUUM to use
more than 1GB of work mem.

Nikhil Sontakke sent in another revision of a patch to implement logical
decoding of two-phase transactions.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Artur Zakirov sent in another revision of a patch to implement shared Ispell

Nathan Bossart sent in a patch to allow users to change the WAL segment size of
a cluster with pg_resetwal.

Peter Eisentraut sent in a patch to fix some confusing SSL test names.

Ashutosh Bapat sent in two more revisions of a patch to do better partition
matching for partition-wise joins.

Pavan Deolasee sent in another revision of a patch to implement MERGE.

Pierre Ducroquet sent in another revision of a patch atop the JIT patch to
support JIT compiling with LLVM v10.0.

Jeevan Chalke sent in another revision of a patch to implement partition-wise

Ildus Kurbangaliev sent in a patch to add 'autovacuum_table_priority' to the
current list of automatic vacuuming settings.

Peter Geoghegan sent in another revision of a patch to add a Bloom filter data
structure implementation and use same to add amcheck verification of indexes
against the heap.

Claudio Freire sent in another revision of a patch to vacuum the FSM more

Masahiko Sawada sent in another revision of a patch to keep track of writing on
non-temporary relations, support atomic commits involving multiple foreign
servers, and add postgres_fdw support for atomic distributed transaction commit.

Michaël Paquier sent in a patch to disable src/test/[ssl|ldap] when not building
with SSL/LDAP support.

Kyotaro HORIGUCHI sent in a patch to let plan_create_index_workers

Kyotaro HORIGUCHI sent in a patch to increase the minimum allowable value of
max_connections from 10 to 20.

Konstantin Knizhnik sent in a patch for PL/pgsql which reports an error in the
case of an empty attribute list for SELECT INTO.

Amit Langote sent in another revision of a patch to implement faster partition

Konstantin Knizhnik sent in another revision of a patch to implement a built-in
connection pooler.

Peter Eisentraut sent in a patch to add ldapi support.

Andrew Dunstan sent in another revision of a patch to implement a faster ALTER

Etsuro Fujita sent in a patch to add some regression tests for the PostgreSQL

Andrey Borodin sent in a patch to enable using ICU as the default collation

Thomas Munro sent in a patch to register and document

Browse pgsql-announce by date

  From Date Subject
Next Message Monica Real Amores 2018-02-13 08:45:59 OmniDB 2.5 Now Available
Previous Message Tatsuo Ishii 2018-02-09 01:13:33 Re: PostgreSQL 2018-02-08 Security Update Release