Quick Links

== PostgreSQL Weekly News - December 9, 2018 ==

From:	David Fetter <david(at)fetter(dot)org>
To:	PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject:	== PostgreSQL Weekly News - December 9, 2018 ==
Date:	2018-12-09 20:35:13
Message-ID:	20181209203513.GA31096@fetter.org
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-announce

PGCon 2019 will take place in Ottawa on May 28-31, 2019. The CfP is open
through January 19, 2019 at http://www.pgcon.org/2019/papers.php
https://www.pgcon.org/2018/schedule/

Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019.
The CfP is open January 17, 2019 through April 18, 2019, and registration will
open January 17, 2019.
http://www.pgday.ch/2019/

== PostgreSQL Product News ==

pgsql_tweaks 0.3.0, an extension that adds some functions and views, released.
https://gitlab.com/sjstoelting/pgsql-tweaks

InfluxDB fdw 0.1 released
https://github.com/pgspider/influxdb_fdw

== PostgreSQL Jobs for December ==

http://archives.postgresql.org/pgsql-jobs/2018-12/

== PostgreSQL Local ==

PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo,
Japan.
http://www.pgconf.asia/EN/2018/

FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 1st, 2019.
https://2019.fosdempgday.org/

Prague PostgreSQL Developer Day 2019 (P2D2 2019) is a two-day
conference that will be held on February 13-14, 2019 in Prague, Czech Republic.
The CfP is open until January 4, 2018 at https://p2d2.cz/callforpapers
http://www.p2d2.cz/

PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka.
The CfP at the site below is open through December 15, 2018.
http://pgconf.in/

pgDay Paris 2019 will be held in Paris, France on March 12, 2019
at 199bis rue Saint-Martin.
http://2019.pgday.paris/

PGConf APAC 2019 will be held in Singapore March 19-21, 2019.
http://2019.pgconfapac.org/

PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. Both the
CfP https://2019.pgday.it/en/blog/cfp and the Call for Workshops
https://2019.pgday.it/en/blog/cfw are openuntil January 15, 2019.
https://2019.pgday.it/en/

== 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 david(at)fetter(dot)org(dot)
== PostgreSQL Weekly News - December 9, 2018 ==

pgsql_tweaks 0.3.0, an extension that adds some functions and views, released.
https://gitlab.com/sjstoelting/pgsql-tweaks

PGCon 2019 will take place in Ottawa on May 28-31, 2019. The CfP is open
through January 19, 2019 at http://www.pgcon.org/2019/papers.php
https://www.pgcon.org/2018/schedule/

InfluxDB fdw 0.1 released
https://github.com/pgspider/influxdb_fdw

== PostgreSQL Product News ==

== PostgreSQL Jobs for December ==

http://archives.postgresql.org/pgsql-jobs/2018-12/

== PostgreSQL Local ==

PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo,
Japan.
http://www.pgconf.asia/EN/2018/

FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 1st, 2019.
https://2019.fosdempgday.org/

PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka.
The CfP at the site below is open through December 15, 2018.
http://pgconf.in/

pgDay Paris 2019 will be held in Paris, France on March 12, 2019
at 199bis rue Saint-Martin.
http://2019.pgday.paris/

PGConf APAC 2019 will be held in Singapore March 19-21, 2019.
http://2019.pgconfapac.org/

== 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 david(at)fetter(dot)org(dot)

== Applied Patches ==

Michaël Paquier pushed:

- Add PGXS options to control TAP and isolation tests, take two The following
options are added for extensions: - TAP_TESTS, to allow an extention to run
TAP tests which are the ones present in t/*.pl. A subset of tests can always
be run with the existing PROVE_TESTS for developers. - ISOLATION, to define a
list of isolation tests. - ISOLATION_OPTS, to pass custom options to
isolation_tester. A couple of custom Makefile rules have been accumulated
across the tree to cover the lack of facility in PGXS for a couple of releases
when using those test suites, which are all now replaced with the new flags,
without reducing the test coverage. Note that tests of contrib/bloom/ are not
enabled yet, as those are proving unstable in the buildfarm. Author: Michael
Paquier Reviewed-by: Adam Berlin, Álvaro Herrera, Tom Lane, Nikolay Shaplov,
Arthur Zakirov Discussion:
https://postgr.es/m/20180906014849.GG2726@paquier.xyz
https://git.postgresql.org/pg/commitdiff/d3c09b9b1307e022883801000ae36bcb5eef71e8

- Add some missing schema qualifications. This does not improve the security
and reliability of the touched areas, but it makes the style more consistent.
Author: Michael Paquier Reviewed-by- Noah Misch Discussion:
https://postgr.es/m/20180309075538.GD9376@paquier.xyz
https://git.postgresql.org/pg/commitdiff/ee2b37ae044f34851baba69e9ba737077326414e

- Fix invalid value of synchronous_commit in description of flush_lag.
"remote_flush" has never been a valid user-facing value, but "on" is. Author:
Maksim Milyutin Discussion:
https://postgr.es/m/27b3b80c-3615-2d76-02c5-44566b53136c@gmail.com
https://git.postgresql.org/pg/commitdiff/99f9ccee510366a560df88088835e7e8d5066118

- Fix some errhint and errdetail strings missing a period. As per the error
message style guide of the documentation, those should be full sentences.
Author: Daniel Gustafsson Reviewed-by: Michael Paquier, Álvaro Herrera
Discussion: https://1E8D49B4-16BC-4420-B4ED-58501D9E076B(at)yesql(dot)se
https://git.postgresql.org/pg/commitdiff/730422afcdb6784bbe20efc65de72156d470b0c4

- Add timestamp of last received message from standby to pg_stat_replication.
The timestamp generated by the standby at message transmission has been
included in the protocol since its introduction for both the status update
message and hot standby feedback message, but it has never appeared in
pg_stat_replication. Seeing this timestamp does not matter much with a
cluster which has a lot of activity, but on a mostly-idle cluster, this makes
monitoring able to react faster than the configured timeouts. Author:
MyungKyu LIM Reviewed-by: Michael Paquier, Masahiko Sawada Discussion:
https://postgr.es/m/1657809367.407321.1533027417725.JavaMail.jboss@ep2ml404
https://git.postgresql.org/pg/commitdiff/7fee252f6fbf78ca5e50ee591573d59f98e75d37

Tom Lane pushed:

- Refactor documentation about privileges to centralize the info. Expand
section 5.6 "Privileges" to include the full definition of each privilege
type, and an explanation of aclitem privilege displays, along with some
helpful summary tables. Most of this material came out of the GRANT reference
page, although some of it is new. Adjust a bunch of links that were pointing
to GRANT to point to 5.6. Fabien Coelho and Tom Lane, reviewed by Bradley
DeJong Discussion:
https://postgr.es/m/alpine.DEB.2.21.1807311735200.20743@lancre
https://git.postgresql.org/pg/commitdiff/afc4a78a30146a0db415c5c2bbf460e5a576d70f

- Improve our response to invalid format strings, and detect more cases. Places
that are testing for *printf failure ought to include the format string in
their error reports, since bad-format-string is one of the more likely causes
of such failure. This both makes it easier to find and repair the mistake,
and provides at least some useful info to the user who stumbles across such a
problem. Also, tighten snprintf.c to report EINVAL for an invalid flag or
final character in a format %-spec (including the case where the %-spec is
missing a final character altogether). This seems like better project policy,
and it also allows removing an instruction or two from the hot code path.
Back-patch the error reporting change in pvsnprintf, since it should be
harmless and may be helpful; but not the snprintf.c change. Per discussion of
bug #15511 from Ertuğrul Kahveci, which reported an invalid translated format
string. These changes don't fix that error, but they should improve matters
next time we make such a mistake. Discussion:
https://postgr.es/m/15511-1d8b6a0bc874112f@postgresql.org
https://git.postgresql.org/pg/commitdiff/d2b0b60e71931997455afd5da72ca29148f1ca51

- Doc: make cross-reference to format() function more specific. Jeff Janes
Discussion:
https://postgr.es/m/CAMkU=1w7Tn2M9BhK+rt8Shtz1AkU+ty7By8gj5C==z65=U4vyQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/eeee62d8052ccd3fb8e26be1392a93167f83d937

- Clean up sloppy coding in publicationcmds.c's OpenTableList(). Remove dead
code (which would be incorrect if it weren't dead), per report from Pan Bian.
Add a CHECK_FOR_INTERRUPTS in the inner loop over child relations, because
there's little point in having one in the outer loop if there's not one here
too. Minor stylistic adjustments and comment improvements. Seems to be
aboriginal to this code (cf commit 665d1fad9). Back-patch to v10 where that
came in, not because any of this is significant, but just to keep the branches
looking similar. Discussion:
https://postgr.es/m/15539-06d00ef6b1e2e1bb@postgresql.org
https://git.postgresql.org/pg/commitdiff/9286ef8e914c69fbf6087d6339215fd10836e25a

- Fix misapplication of pgstat_count_truncate to wrong relation. The stanza of
ExecuteTruncate[Guts] that truncates a target table's toast relation re-used
the loop local variable "rel" to reference the toast rel. This was safe
enough when written, but commit d42358efb added code below that that supposed
"rel" still pointed to the parent table. Therefore, the stats counter update
was applied to the wrong relcache entry (the toast rel not the user rel); and
if we were unlucky and that relcache entry had been flushed during
reindex_relation, very bad things could ensue. (I'm surprised that
CLOBBER_CACHE_ALWAYS testing hasn't found this. I'm even more surprised that
the problem wasn't detected during the development of d42358efb; it must not
have been tested in any case with a toast table, as the incorrect stats counts
are very obvious.) To fix, replace use of "rel" in that code branch with a
more local variable. Adjust test cases added by d42358efb so that some of
them use tables with toast tables. Per bug #15540 from Pan Bian. Back-patch
to 9.5 where d42358efb came in. Discussion:
https://postgr.es/m/15540-01078812338195c0@postgresql.org
https://git.postgresql.org/pg/commitdiff/5deadfef282761aeb27b18706dcff295e9430c39

- In PQprint(), write HTML table trailer before closing the output pipe. This
is an astonishingly ancient bit of silliness, dating AFAICS to commit
edb519b14 of 27-Jul-1996 which added the pipe close stanza in the wrong place.
It happens to be harmless given that the code above this won't enable the
pager if html3 output mode is selected. Still, somebody might try to relax
that restriction someday, and in any case it could confuse readers and static
analysis tools, so let's fix it in HEAD. Per bug #15541 from Pan Bian.
Discussion: https://postgr.es/m/15541-c835d8b9a903f7ad@postgresql.org
https://git.postgresql.org/pg/commitdiff/b90e6cef12662ccad8679be24cd650c6b49feb1c

- Doc: document that we expect CHECK constraint conditions to be immutable.
This restriction is implicit in the check-only-once implementation we use for
table and domain constraints, but it wasn't spelled out anywhere, nor was
there any advice about how to alter a constraint's behavior safely. Improve
that. I was also dissatisfied with the documentation of ALTER DOMAIN VALIDATE
CONSTRAINT, which entirely failed to explain the use of that feature; and
thence decided that ALTER TABLE VALIDATE CONSTRAINT could be documented better
as well. Perhaps we should back-patch this, along with the related commit
36d442a25, but for now I refrained. Discussion:
https://postgr.es/m/12539.1544107316@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/1f66c657f2b4eb55c68593d5c14256115aa6a0ea

- Doc: remove obsolete statements about system OID columns in ALTER TABLE.
Missed in commit 578b22971.
https://git.postgresql.org/pg/commitdiff/1464755fc490a9911214817fe83077a3689250ab

Etsuro Fujita pushed:

- postgres_fdw: Improve cost and size estimation for aggregate pushdown. In
commit 7012b132d07c2b4ea15b0b3cb1ea9f3278801d98, which added aggregate
pushdown to postgres_fdw, we didn't account for the evaluation cost and the
selectivity of HAVING quals attached to ForeignPaths performing aggregate
pushdown, as core had never accounted for that for AggPaths and GroupPaths.
And we didn't set these values of the locally-checked quals (ie, fpinfo's
local_conds_cost and local_conds_sel), which were initialized to zeros, but
since estimate_path_cost_size factors in these to estimate the result size and
the evaluation cost of such a ForeignPath when the use_remote_estimate option
is enabled, this caused it to produce underestimated results in that case. By
commit 7b6c07547190f056b0464098bb5a2247129d7aa2 core was changed so that it
accounts for the evaluation cost and the selectivity of HAVING quals in
aggregation paths, so change the postgres_fdw's aggregate pushdown code as
well as such. This not only fixes the underestimation issue mentioned above,
but improves the estimation using local statistics in that function when that
option is disabled. This would be a bug fix rather than an improvement, but
apply it to HEAD only to avoid destabilizing existing plan choices. Author:
Etsuro Fujita Discussion: https://postgr.es/m/5BFD3EAD.2060301%40lab.ntt.co.jp
https://git.postgresql.org/pg/commitdiff/f8f6e44676ef38fee7a5bbe4f256a34ea7799ac1

Stephen Frost pushed:

- Fix typo. Backends don't typically exist uncleanly, but they can certainly
exit uncleanly, and it's exiting uncleanly that's being discussed here.
https://git.postgresql.org/pg/commitdiff/f502fc88b3c8b4c619a4bcbb86c3225a699c1d45

- Cleanup comments in xlog compression. Skipping over the "hole" in full page
images in the XLOG code was described as being a form of compression, but this
got a bit confusing since we now have PGLZ-based compression happening, so
adjust the wording to discuss "removing" the "hole" and keeping the talk about
compression to where we're talking about using PGLZ-based compression of the
full page images. Reviewed-By: Kyotaro Horiguchi Discussion:
https://postgr.es/m/20181127234341.GM3415@tamriel.snowman.net
https://git.postgresql.org/pg/commitdiff/a243c55326686b284fbe2c7b70f06d8a022b7dcc

- Cleanup minor pg_dump memory leaks. In dumputils, we may have successfully
parsed the acls when we discover that we can't parse the reverse ACLs and then
return- check and free aclitems if that happens. In dumpTableSchema, move
ftoptions and srvname under the relkind != RELKIND_VIEW branch (since they're
only used there) and then check if they've been allocated and, if so, free
them at the end of that block. Pointed out by Pavel Raiskup, though I didn't
use those patches. Discussion:
https://postgr.es/m/2183976.vkCJMhdhmF@nb.usersys.redhat.com
https://git.postgresql.org/pg/commitdiff/369d494a4f5c1494a46adcf5c56f5462a83ef20d

- Improve planner stats documentation. It was pointed out that in the planner
stats documentation under Extended Statistics, one of the sentences was a bit
awkward. Improve that by rewording it slightly. Discussion:
https://postgr.es/m/154409976780.14137.2785644488950047100@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/7a55ccc477b58863f6c73c243c7adb79c9717eda

Álvaro Herrera pushed:

- Don't mark partitioned indexes invalid unnecessarily. When an indexes is
created on a partitioned table using ONLY (don't recurse to partitions), it
gets marked invalid until index partitions are attached for each table
partition. But there's no reason to do this if there are no partitions ...
and moreover, there's no way to get the index to become valid afterwards,
because all partitions that get created/attached get their own index partition
already attached to the parent index, so there's no chance to do ALTER INDEX
... ATTACH PARTITION that would make the parent index valid. Fix by not
marking the index as invalid to begin with. This is very similar to
9139aa19423b, but the pg_dump aspect does not appear to be relevant until we
add FKs that can point to PKs on partitioned tables. (I tried to cause the
pg_upgrade test to break by leaving some of these bogus tables around, but
wasn't able to.) Making this change means that an index that was supposed to
be invalid in the insert_conflict regression test is no longer invalid;
reorder the DDL so that the test continues to verify the behavior we want it
to. Author: Álvaro Herrera Reviewed-by: Amit Langote Discussion:
https://postgr.es/m/20181203225019.2vvdef2ybnkxt364@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/71a05b22326088ce59c7ed12cc4160adec634940

Tatsuo Ishii pushed:

- Change true/false to on/off. We prefer to use on/off than true/false for
boolean configuration parameters in the documentation, but there were a few
places where true/false were still used. Dicussion:
https://postgr.es/m/20181202.072508.618341295047874293.t-ishii%40sraoss.co.jp
https://git.postgresql.org/pg/commitdiff/228b0485f471480cf825b7b30ffa3d66bd692c57

== Pending Patches ==

Noah Misch sent in a patch to replace an obsolete errhint() about manually
removing shared memory blocks with a recommendation to kill processes and fix
some tests around shared memory.

Tatsuo Ishii sent in a patch to document the fact that in "prepared" mode
pgbench reuses the parse analysis result for the second and subsequent query
iteration.

Alexander Lakhin sent in another revision of a patch to make 'make installcheck'
not fail in some corner cases.

David Cramer sent in another revision of a patch to respect client initiated
CopyDone and add a client-initiated CopyDone during transaction decoding, both
in walsender, and make pg_recvlogical able to stop replication from client side.

David Rowley sent in a patch to allow lock acquisitions for partitions to be
delayed.

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

Liudmila Mantrova sent in another revision of a patch to document SQL/JSON.

didier sent in three revisions of a patch to add a sqlstate output mode to
psql's VERBOSITY.

Nikita Glukhov sent in another revision of a patch to implement JSONPATH.

Peter Geoghegan sent in another revision of a patch to add dynamic prefix
truncation to nbtree.

Peter Geoghegan sent in another revision of a patch to make all nbtree entries
unique by having heap TIDs participate in comparisons.

Dilip Kumar sent in a patch to implement an UNDO worker and transaction
rollback both with it and directly from the backend.

Amit Langote sent in a patch to give a more specific error for partitioned
tables in CheckSubscriptionRelkind().

Álvaro Herrera sent in three revisions of a patch to fix psql's display of FKs
on partitioned tables.

Michaël Paquier sent in two more revisions of a patch to fix a situation where
archive status files are not durably removed after a system crash by removing
the orphan status file.

Peter Eisentraut sent in a patch to add test cases for foreign keys: ON DELETE
NO ACTION/RESTRICT, and increase test coverage in RI_FKey_pk_upd_check_required(),
RI_FKey_fk_upd_check_required(), and RI_Initial_Check().

Dilip Kumar and Amit Kapila traded patches to add an UNDO log manager, provide
access to undo log data via the buffer manager, and provide an interface for
prepare, insert, or fetch the undo records.

Michaël Paquier sent in another revision of a patch to add a reply_time column
to pg_stat_replication.

Nikita Glukhov sent in another revision of a patch to implement the SQL/JSON
functions.

Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE.

Tom Lane sent in another revision of a patch to fix the issues that caused
queries over information_schema.tables to be slow.

Amit Langote sent in a patch to improve the documents for runtime pruning.

Amit Langote sent in two revisions of a patch to error out correctly when
attempting to replicate foreign and partitioned tables.

Evgeniy Efimkin sent in another revision of a patch to add a table filter for
CREATE SUBSCRIPTION.

Nikita Glukhov sent in another revision of a patch to add parameters to Opclass.

Tom Lane sent in a patch to drop CoerceToDomain nodes from plan trees if their
domains have no constraints that need to be checked.

Álvaro Herrera sent in a patch to optimize CancelVirtualTransaction a little
bit.

Paul Ramsey sent in another revision of a patch to add a code path to do a
partial decompression of the TOAST entry when the requested slice is at the
start of the object.

Elvis Pranskevichus sent in a patch to allow anonymous rowtypes in function
return column definitions.

Álvaro Herrera sent in a patch to avoid creating storage when not needed.

Amit Langote sent in another revision of a patch to speed up planning with
partitions.

Ryohei Nagaura sent in two more revisions of a patch to add a tcp_user_timeout
GUC.

Amit Langote sent in a patch to add child EC members for only the non-dummy
children.

Álvaro Herrera sent in a patch to rewrite ExecPartitionCheckEmitError.

Andrey V. Lepikhov sent in another revision of a patch to print all dependencies
on errors in DROP.

John Naylor and Amit Kapila traded patches to avoid creation of the free space
map for small tables.

Michaël Paquier sent in a patch to add pg_partition_root to get top-most parent
of a partition tree.

Iwata Aya sent in another revision of a patch to implement a debug log for
libpq.

Lætitia Avrot sent in another revision of a patch to make the documentation for
ON UPDATE clearer in FOREIGN KEYs.

Samuel Cochran sent in a patch to fix `make check` on Darwin.

Álvaro Herrera sent in a patch to implement statement-level rollback.

Peter Eisentraut sent in another revision of a patch to implement REINDEX
CONCURRENTLY.

Michaël Paquier sent in two revisions of a patch to fix an issue where
pg_partition_tree crashes for a non-defined relation.

Andrey Borodin sent in a patch to always take a root cleanup lock before
deleting pages in GIN indexes, and check for concurrent splits after scanning
each page.

Stephen Frost sent in a patch to remove soem dead code from
toast_fetch_datum_slice.

Stephen Frost sent in another revision of a patch to add partition tests.

Stephen Frost sent in a patch to fix a bug when dumping "empty" operator
classes.

Pavel Stěhule sent in a patch to implement new polymorphic types: commontype and
commontypearray.

Alexander Korotkov sent in a patch to fix a deadlock in GIN vacuum introduced by
218f51584d5.

Noah Misch sent in a patch to raise the timeouts to 180 seconds in test code.

David Rowley sent in another revision of a patch to allow newly created
partitions to inherit their parent's tablespace.

John Naylor sent in another revision of a patch to automatically assign catalog
toast OIDs.

Marti Raudsepp sent in a patch to clean up the BRIN indexing code.