== PostgreSQL Weekly News - June 04 2017 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - June 04 2017 ==
Date: 2017-06-04 21:41:08
Message-ID: 20170604214108.GA1663@fetter.org
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-announce

== PostgreSQL Weekly News - June 04 2017 ==

PostgreSQL 10 Beta 1 released. Test!

Security releases 9.6.3, 9.5.7, 9.4.12, 9.3.17 and 9.2.21 are out.
Upgrade at the next available downtime.

PGBR2017 will take place in Porto Alegre, Rio Grande do Sul,
Brazil on September 14-16 2017. The CfP is open until June 30, 2017.

PGConf.ASIA 2017 will take place on December 4-6 2017 in Akihabara, Tokyo,
Japan. The CfP is open until July 31, 2017. Send submissions to
pgconf-asia-2017-submission AT pgconf DOT asia

== PostgreSQL Product News ==

pgFormatter 2.0, a formatter/beautifier for SQL code, released.

Pgpool-II 3.6.4 and 3.5.8 released.

psqlODBC 09.06.0310 released.

pitrery 1.13, a set of Bash scripts to manage PITR backups for PostgreSQL, released.

== PostgreSQL Jobs for June ==


== PostgreSQL Local ==

PGDay Argentina 2017 will be held in Santa Fe on June 9, 2017.

Postgres Vision will take place in Boston, June 26 - 28, 2017.

Swiss PGDay in Rapperswil will take place June 30, 2017.
The schedule is online and registration is open.

PostgresOpen will occur September 6-8, 2017 in San Francisco.

PGDay UK 2017 will take place July 4, 2017 in London, England, UK.

PGDay Austin 2017 will be held on Saturday, August 26 2017. The Call For

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

Heikki Linnakangas pushed:

- Fix reference to RFC specifying SCRAM. Noted by Peter Eisentraut

Magnus Hagander pushed:

- Fix typo in comment. Masahiko Sawada

- Generate pg_basebackup temporary slot name using backend pid. Using the
client pid can easily be non-unique when used on different hosts. Using the
backend pid should be guaranteed unique, since the temporary slot gets removed
when the client disconnects so it will be gone even if the pid is renewed.
Reported by Ludovic Vaugeois-Pepin

- Fix typo in comment. Masahiko Sawada

- Fix copy/paste mistake in comment. Amit Langote

Tom Lane pushed:

- More code review for get_qual_for_list(). Avoid trashing the input
PartitionBoundSpec; while that might be safe for current callers, it's
certainly trouble waiting to happen. In the same vein, make sure that all of
the result data structure is freshly palloc'd, rather than some of it being
pointers into the input data structures (which we don't know the lifespans
of). Simplify the logic for tacking on IS NULL or IS NOT NULL conditions some
more; commit 85c2b9a15 left a lot on the table there. And rearrange the
construction of the nodes into (what seems to me) a more logical order. In
passing, make sure that get_qual_for_range() also returns a freshly palloc'd
structure, since there's no value in having that guarantee for only one kind
of partitioning. And improve some comments there. Jeevan Ladhe, with further
tweaking by me Discussion:

- Allow NumericOnly to be "+ FCONST". The NumericOnly grammar production
accepted ICONST, + ICONST, - ICONST, FCONST, and - FCONST, but for some reason
not + FCONST. This led to strange inconsistencies like regression=# set
random_page_cost = +4; SET regression=# set random_page_cost = 4000000000; SET
regression=# set random_page_cost = +4000000000; ERROR: syntax error at or
near "4000000000" (because 4000000000 is too large to be an ICONST). While
there's no actual functional reason to need to write a "+", if we allow it for
integers it seems like we should allow it for numerics too. It's been like
that forever, so back-patch to all supported branches. Discussion:

- Prevent running pg_resetwal/pg_resetxlog against wrong-version data dirs.
pg_resetwal (formerly pg_resetxlog) doesn't insist on finding a matching
version number in pg_control, and that seems like an important thing to
preserve since recovering from corrupt pg_control is a prime reason to need to
run it. However, that means you can try to run it against a data directory of
a different major version, which is at best useless and at worst disastrous.
So as to provide some protection against that type of pilot error, inspect
PG_VERSION at startup and refuse to do anything if it doesn't match.
PG_VERSION is read-only after initdb, so it's unlikely to get corrupted, and
even if it were corrupted it would be easy to fix by hand. This hazard has
been there all along, so back-patch to all supported branches. Michael
Paquier, with some kibitzing by me Discussion:

- Make edge-case behavior of jsonb_populate_record match json_populate_record.
json_populate_record throws an error if asked to convert a JSON scalar or
array into a composite type. jsonb_populate_record was returning a record
full of NULL fields instead. It seems better to make it throw an error for
this case as well. Nikita Glukhov Discussion:

- Fix thinko in JsObjectSize() macro. The macro gave the wrong answers for a
JsObject with is_json == 0: it would return 1 if jsonb_cont == NULL, or if
that wasn't NULL, it would return 1 for any non-zero size. We could fix that,
but the only use of this macro at present is in the JsObjectIsEmpty() macro,
so it seems simpler and clearer to get rid of JsObjectSize() and put corrected
logic into JsObjectIsEmpty(). Thinko in commit cf35346e8, so no need for
back-patch. Nikita Glukhov Discussion:

- Fix improper quoting of format_type_be() output. Per our message style
guidelines, error messages incorporating the results of format_type_be() and
its siblings should not add quotes around those results, because those
functions already add quotes at need. Fix a few places that hadn't gotten
that memo.

- Code review focused on new node types added by partitioning support. Fix
failure to check that we got a plain Const from const-simplification of a
coercion request. This is the cause of bug #14666 from Tian Bing: there is an
int4 to money cast, but it's only stable not immutable (because of dependence
on lc_monetary), resulting in a FuncExpr that the code was miserably
unequipped to deal with, or indeed even to notice that it was failing to deal
with. Add test cases around this coercion behavior. In view of the above,
sprinkle the code liberally with castNode() macros, in hope of catching the
next such bug a bit sooner. Also, change some functions that were randomly
declared to take Node* to take more specific pointer types. And change some
struct fields that were declared Node* but could be given more specific types,
allowing removal of assorted explicit casts. Place PARTITION_MAX_KEYS check a
bit closer to the code it's protecting. Likewise check
only-one-key-for-list-partitioning restriction in a less random place. Avoid
not-per-project-style usages like !strcmp(...). Fix assorted failures to
avoid scribbling on the input of parse transformation. I'm not sure how
necessary this is, but it's entirely silly for these functions to be expending
cycles to avoid that and not getting it right. Add guards against
partitioning on system columns. Put backend/nodes/ support code into an order
that matches handling of these node types elsewhere. Annotate the fact that
somebody added location fields to PartitionBoundSpec and PartitionRangeDatum
but forgot to handle them in outfuncs.c/readfuncs.c. This is fairly harmless
for production purposes (since readfuncs.c would just substitute -1 anyway)
but it's still bogus. It's not worth forcing a post-beta1 initdb just to fix
this, but if we have another reason to force initdb before 10.0, we should go
back and clean this up. Contrariwise, somebody added location fields to
PartitionElem and PartitionSpec but forgot to teach exprLocation() about them.
Consolidate duplicative code in transformPartitionBound(). Improve a couple
of error messages. Improve assorted commentary. Re-pgindent the files
touched by this patch; this affects a few comment blocks that must have been
added quite recently. Report:

- Fix omission of locations in outfuncs/readfuncs partitioning node support. We
could have limped along without this for v10, which was my intention when I
annotated the bug in commit 76a3df6e5. But consensus is that it's better to
fix it now and take the cost of a post-beta1 initdb (which is needed because
these node types are stored in pg_class.relpartbound). Since we're forcing
initdb anyway, take the opportunity to make the node type identification
strings match the node struct names, instead of being randomly different from
them. Discussion:

- Sort syscache identifiers into alphabetical order. Not much point in having a
convention about this if we don't enforce it. Mark Dilger Discussion:

- Avoid -Wconversion warnings from direct use of GET_n_BYTES macros. The
GET/SET_n_BYTES macros are meant to be infrastructure for the
DatumGetFoo/FooGetDatum macros, which include a cast to the intended target
type. Using them directly without a cast, as DatumGetFloat4 and friends
previously did, can yield warnings when -Wconversion is on. This is of little
significance when building Postgres proper, because there are such a huge
number of such warnings in the server that nobody would think -Wconversion is
of any use. But some extensions build with -Wconversion due to outside
constraints. Commit 14cca1bf8 did a disservice to those extensions by moving
DatumGetFloat4 et al into postgres.h, where they can now cause warnings in
extension builds. To fix, use DatumGetInt32 and friends in place of the
low-level macros. This is arguably a bit cleaner anyway. Chapman Flack
Discussion: https://postgr.es/m/592E4D04.1070609@anastigmatix.net

- Always use -fPIC, not -fpic, when building shared libraries with gcc. On some
platforms, -fpic fails for sufficiently large shared libraries. We've mostly
not hit that boundary yet, but there are some extensions such as Citus and
pglogical where it's becoming a problem. A bit of research suggests that the
penalty for -fPIC is small, in the single-digit-percentage range --- and
there's none at all on popular platforms such as x86_64. So let's just
default to -fPIC everywhere and provide one less thing for extension
developers to worry about. Per complaint from Christoph Berg. Back-patch to
all supported branches. (I did not bother to touch the recently-removed
Makefiles for sco and unixware in the back branches, though. We'd have no way
to test that it doesn't break anything on those platforms.) Discussion:

- Fix old corner-case logic error in final_cost_nestloop(). When costing a
nestloop with stop-at-first-inner-match semantics, and a non-indexscan inner
path, final_cost_nestloop() wants to charge the full scan cost of the inner
rel at least once, with additional scans charged at inner_rescan_run_cost
which might be less. However the logic for doing this effectively assumed
that outer_matched_rows is at least 1. If it's zero, which is not unlikely
for a small outer rel, we ended up charging inner_run_cost plus N times
inner_rescan_run_cost, as much as double the correct charge for an outer rel
with only one row that we're betting won't be matched. (Unless the inner rel
is materialized, in which case it has very small inner_rescan_run_cost and the
cost is not so far off what it should have been.) The upshot of this was that
the planner had a tendency to select plans that failed to make effective use
of the stop-at-first-inner-match semantics, and that might have Materialize
nodes in them even when the predicted number of executions of the Materialize
subplan was only 1. This was not so obvious before commit 9c7f5229a, because
the case only arose in connection with semi/anti joins where there's not
freedom to reverse the join order. But with the addition of unique-inner
joins, it could result in some fairly bad planning choices, as reported by
Teodor Sigaev. Indeed, some of the test cases added by that commit have plans
that look dubious on closer inspection, and are changed by this patch. Fix
the logic to ensure that we don't charge for too many inner scans. I chose to
adjust it so that the full-freight scan cost is associated with an unmatched
outer row if possible, not a matched one, since that seems like a better model
of what would happen at runtime. This is a longstanding bug, but given the
lesser impact in back branches, and the lack of field complaints, I won't risk
a back-patch. Discussion:

- Fix <> and pattern-NOT-match estimators to handle nulls correctly. These
estimators returned 1 minus the corresponding equality/match estimate, which
is incorrect: we need to subtract off the fraction of nulls in the column,
since those are neither equal nor not equal to the comparison value. The
error only becomes obvious if the nullfrac is large, but it could be very bad
in a mostly-nulls column, as reported in bug #14676 from Marko Tiikkaja. To
fix the <> case, refactor eqsel() and neqsel() to call a common support
routine, which can be made to account for nullfrac correctly. The
pattern-match cases were already factored that way, and it was simply an
oversight that patternsel() wasn't subtracting off nullfrac. neqjoinsel() has
a similar problem, but since we're elsewhere discussing changing its behavior
entirely, I left it alone for now. This is a very longstanding bug, but I'm
hesitant to back-patch a fix for it. Given the lack of prior complaints, such
cases must not come up often, so it's probably not worth the risk of
destabilizing plans in stable branches. Discussion:

- Add some missing backslash commands to psql's tab-completion knowledge. \if
and related commands were overlooked here, as were \dRp and \dRs from the
logical-replication patch, as was \?. While here, reformat the list to put
each new first command letter on a separate line; perhaps that will limit the
need to reflow the whole list when we add more commands in future. Masahiko
Sawada (reformatting by me) Discussion:

- Remove dead variables. Commit 512c7356b left a couple of variables unused
except for being set. My compiler didn't whine about this, but some buildfarm
members did.

- Disallow CREATE INDEX if table is already in use in current session. If we
allow this, whatever outer command has the table open will not know about the
new index and may fail to update it as needed, as shown in a report from
Laurenz Albe. We already had such a prohibition in place for ALTER TABLE, but
the CREATE INDEX syntax missed the check. Fixing it requires an API change
for DefineIndex(), which conceivably would break third-party extensions if we
were to back-patch it. Given how long this problem has existed without being
noticed, fixing it in the back branches doesn't seem worth that risk.

- #ifdef out assorted unused GEQO code. I'd always assumed that
backend/optimizer/geqo/'s remarkably poor showing on code coverage metrics was
because we weren't exercising it much in the regression tests. But it turns
out that a good chunk of the problem is that there's a bunch of code that is
physically unreachable (because the calls to it are #ifdef'd out in
geqo_main.c) but is being built anyway. Making the called code have #if
guards similar to the calling code saves a couple of kilobytes of executable
size and should make the coverage numbers more reflective of reality. It's
arguable that we should just delete all the unused recombination mechanisms
altogether, but I didn't feel a need to go that far today.

Peter Eisentraut pushed:

- doc: Fix ALTER PUBLICATION details. Some of the text was made nonsensical by
commit e9500240661c03750923e6f539bfa2d75cfaa32a. Fix that and make some other
minor changes. Reported-by: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>

- doc: Add another migration item to release notes.

- psql: Fix display of whether table is part of publication. If a FOR ALL
TABLES publication was present, \d of a table would claim for each table that
it was part of the publication, even for tables that are ignored for this
purpose, such as system tables and unlogged tables. Fix the query by using
the function pg_get_publication_tables(), which was intended for this purpose.
Reported-by: tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> Reviewed-by: Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> Reviewed-by: Kuntal Ghosh

- Reorganize logical replication worker disconnect code. Move the
walrcv_disconnect() calls into the before_shmem_exit handler. This makes sure
the call is always made even during exit by signal, it saves some duplicate
code, and it makes the logic more similar to walreceiver.c. Author: Petr
Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>

- doc: Add note that DROP SUBSCRIPTION drops replication slot. Add some
information about what to do when this fails.

- Remove replication slot name check from ReplicationSlotAcquire(). When trying
to access a replication slot that is supposed to already exist, we don't need
to check the naming rules again. If the slot does not exist, we will then get
a "does not exist" error message, which is generally more useful from the
perspective of an end user.

- Fix signal handling in logical replication workers. The logical replication
worker processes now use the normal die() handler for SIGTERM and
CHECK_FOR_INTERRUPTS() instead of custom code. One problem before was that
the apply worker would not exit promptly when a subscription was dropped,
which could lead to deadlocks. Author: Petr Jelinek
<petr(dot)jelinek(at)2ndquadrant(dot)com> Reported-by: Masahiko Sawada

- Make tablesync worker exit when apply dies while it was waiting for it. This
avoids "orphaned" sync workers. This was caused by a thinko in
wait_for_sync_status_change. Author: Petr Jelinek
<petr(dot)jelinek(at)2ndquadrant(dot)com> Reported-by: Masahiko Sawada

- Receive invalidation messages correctly in tablesync worker. We didn't accept
any invalidation messages until the whole sync process had finished (because
it flattens all the remote transactions in the single one). So the sync
worker didn't learn about subscription changes/drop until it has finished.
This could lead to "orphaned" sync workers. Author: Petr Jelinek
<petr(dot)jelinek(at)2ndquadrant(dot)com> Reported-by: Masahiko Sawada

Álvaro Herrera pushed:

- Fix wording in amvalidate error messages. Remove some gratuituous message
differences by making the AM name previously embedded in each message be a %s
instead. While at it, get rid of terminology that's unclear and unnecessary
in one message. Discussion:

- brin: Don't crash on auto-summarization. We were trying to free a pointer
into a shared buffer, which never works; and we were failing to release the
buffer lock appropriately. Fix those omissions. While at it, improve
documentation for brinGetTupleForHeapBlock, the inadequacy of which evidently
caused these bugs in the first place. Reported independently by Zhou Digoal
(bug #14668) and Alexander Sosna. Discussion:

- Fix typo. Reported by: Tim Goodaire Discussion:

- Assorted translatable string fixes. Mark our rusage reportage string
translatable; remove quotes from type names; unify formatting of very similar

Robert Haas pushed:

- Restore accidentally-removed line. Commit
88e66d193fbaf756b3cc9bf94cad116aacbb355b is to blame. Masahiko Sawada

Andres Freund pushed:

- Make ALTER SEQUENCE, including RESTART, fully transactional. Previously the
changes to the "data" part of the sequence, i.e. the one containing the
current value, were not transactional, whereas the definition, including
minimum and maximum value were. That leads to odd behaviour if a schema
change is rolled back, with the potential that out-of-bound sequence values
can be returned. To avoid the issue create a new relfilenode fork whenever
ALTER SEQUENCE is executed, similar to how TRUNCATE ... RESTART IDENTITY
already is already handled. This commit also makes ALTER SEQUENCE RESTART
transactional, as it seems to be too confusing to have some forms of ALTER
SEQUENCE behave transactionally, some forms not. This way setval() and
nextval() are not transactional, but DDL is, which seems to make sense. This
commit also rolls back parts of the changes made in 3d092fe540 and f8dc1985f
as they're now not needed anymore. Author: Andres Freund Discussion:
Backpatch: Bug is in master/v10 only

- Modify sequence catalog tuple before invoking post alter hook. This seems to
have been broken in the commit (1753b1b027035029) that moved the sequence
definition into pg_sequence. Author: Andres Freund Discussion:
Backpatch: Bug is in master/v10 only

- Allow parallelism in COPY (query) TO ...; Previously this was not allowed, as
copy.c didn't set the CURSOR_OPT_PARALLEL_OK flag when planning the query.
Set it. While the lack of parallel query for COPY isn't strictly speaking a
bug, it does prevent parallelism from being used in a facility commonly used
to run long running queries. Thus backpatch to 9.6. Author: Andres Freund
Backpatch: 9.6, where parallelism was introduced.

== Pending Patches ==

Ashutosh Bapat sent in a patch to add some convenience macros for declaratively
partitioned tables.

Andrew Borodin sent in another revision of a patch to allow uncompressed GiST

Michaël Paquier sent in a patch to add a pgdump TAP test for empty opclasses.

Masahiko Sawada sent in a patch to fix an off-by-one mistake in a comment in

Thomas Munro sent in another revision of a patch to enable sharing record
typmods among backends.

Dang Minh Huong sent in another revision of a patch to fix
generate_unaccent_rules.py so that it recursively removes all accents rather
than stopping when it has removed the first accent.

Mithun Cy sent in three more revisions of a patch to add auto_prewarm.

Jeevan Ladhe sent in four more revisions of a patch to add support for a default
partition in declarative partitioning.

Kyotaro HORIGUCHI sent in a patch to fix walsender timeouts by timeout.

Fabien COELHO sent in another revision of a patch to add pgbench TAP tests.

Craig Ringer sent in a patch to allow overriding PostgresNode in get_new_node.

Masahiko Sawada sent in a patch to fix ALTER SUBSCRIPTION REFRESH and table

Peter Eisentraut sent in a patch to split background worker name into type and

Neha Khatri sent in another revision of a patch to fix a typo in xlogfuncs.c.

David Rowley and Tom Lane traded patches to fix a performance regression in 10.

Dilip Kumar and Peter Eisentraut traded patches to fix an issue where creating
subscription fails when the server is running in single user mode.

Peter Eisentraut sent in a patch to prevent parallel query in walsender.

Masahiko Sawada sent in a patch to fix signal handling in logical workers, fake
tablesync worker exit when apply dies while it was waiting for it, receive
invalidation messages correctly in tablesync worker, and wait for table sync
worker to finish when apply worker exits.

Petr Jelínek sent in two revisions of a patch to improve handover logic between
sync and apply workers.

Beena Emerson and Rafia Sabih traded patches to add a default partition for
range partitions.

Emre Hasegeli sent in another revision of a patch to refactor geometric
functions and operators code, provide a header file for built-in float
datatypes, use the built-in float datatype to implement geometric types, and fix
obvious problems around the line datatype.

Tom Lane sent in a patch to respond to inner selectivity even without stats.

Sandro Santilli sent in a patch to ensure that the check Makefile rule imported
by PGXS returns failure code when it is unsupported.

Amit Langote sent in a patch to check the partition constraint even after
tuple-routing in order to ensure that BEFORE ROW triggers not violate same.

Heikki Linnakangas sent in a patch to fix double-free bug in GSS authentication.

Alexander Korotkov sent in a patch to add a SET STATISTICS option to ALTER

Rafia Sabih and Robert Haas traded patches to improve PostgreSQL FDW abort

Fabrízio de Royes Mello sent in a patch to ensure that pg_replslot is properly
cleaned when subtransactions are happening.

Andres Freund sent in a patch to revert "Prevent panic during shutdown
checkpoint", have walsenders participate in procsignal infrastructure, prevent
the possibility of panics during shutdown checkpoint, and wire up query cancel
interrupt for walsender backends.

Peter Eisentraut sent in a patch to fix an ALTER SUBSCRIPTION grammar ambiguity.

Petr Jelínek sent in two revisions of a patch to use virtual transaction instead
of normal ones in exported snapshots and not assign xids to logical decoding

Amit Kapila sent in another revision of a patch to try to reattach shared memory
segments under Cygwin.

Dmitry Dolgov sent in a patch to give a more useful hint when people attempt to
use DROP SUBSCRIPTION to disassociate the subscription from a slot.

Thomas Munro sent in a patch to reject wCTEs with transition tables.

Tom Lane sent in a patch to fix an issue where indexes created in a BEFORE
trigger were not updated during INSERT.

Browse pgsql-announce by date

  From Date Subject
Next Message Thomas Reiss 2017-06-06 10:02:51 check_pgactivity 2.2
Previous Message Tatsuo Ishii 2017-06-02 08:06:07 Call for Papers - PGConf.ASIA 2017