Skip site navigation (1) Skip section navigation (2)

== PostgreSQL Weekly News - July 23 2017 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - July 23 2017 ==
Date: 2017-07-23 20:46:18
Message-ID: 20170723204617.GA22383@fetter.org (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-announce
== PostgreSQL Weekly News - July 23 2017 ==

PGDay Australia 2017 will be held on November 17 in Melbourne.  The CfP is open
through August 20, 2017 at https://goo.gl/5d9EbT
http://2017.pgday.com.au/

== PostgreSQL Product News ==

psqlODBC 09.06.0400 released.
https://odbc.postgresql.org/docs/release.html

barman 2.2, a backup and recovery manager for PostgreSQL, released.
https://www.2ndquadrant.com/en/about/news/announcing-release-barman-2-2/

psycopg2 2.7.2, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2017/07/22/psycopg-272-released/

pg_chameleon 1.5, a tool for replicating from MySQL to PostgreSQL, released.
https://pypi.python.org/pypi/pg_chameleon

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2017-07/

== PostgreSQL Local ==

PGConf Local: Seattle will be held August 11 - 12, 2017.
https://www.pgconf.us/#Seattle2017

PGDay Austin 2017 will be held on Saturday, August 26 2017.
https://pgdayaustin2017.postgresql.us

PostgresOpen will occur September 6-8, 2017 in San Francisco.
https://2017.postgresopen.org/

PGBR2017 will take place in Porto Alegre, Rio Grande do Sul,
Brazil on September 14-16 2017.
https://pgbr.postgresql.org.br/2017/

PGDay.IT 2017 will take place in October 13th, in Milan, Italy.
http://pgday.it

PostgreSQL Conference Europe 2017 will be held on October 24-27, 2017 in the
Warsaw Marriott Hotel, in Warsaw, Poland.  The CfP is open through August 7, 2017.
https://2017.pgconf.eu/

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
http://www.pgconf.asia/EN/2017/

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

Noah Misch pushed:

- MSVC: Don't link libpgcommon into pgcrypto.  Doing so was useful in
  273c458a2b3a0fb73968020ea5e9e35eb6928967 but became obsolete when
  818fd4a67d610991757b610755e3065fb99d80a5 caused postgres.exe to provide the
  relevant symbols.  No other loadable module links to libpgcommon directly.
  https://git.postgresql.org/pg/commitdiff/2f7f45a64badec18ce75e44ca35c078f08e2651e

Robert Haas pushed:

- hash: Fix write-ahead logging bugs related to init forks.  One, logging for
  CREATE INDEX was oblivious to the fact that when an unlogged table is created,
  *only* operations on the init fork should be logged.  Two, init fork buffers
  need to be flushed after they are written; otherwise, a filesystem-level copy
  following recovery may do the wrong thing.  (There may be a better fix for
  this issue than the one used here, but this is transposed from the similar
  logic already present in XLogReadBufferForRedoExtended, and a broader
  refactoring after beta2 seems inadvisable.) Amit Kapila, reviewed by Ashutosh
  Sharma, Kyotaro Horiguchi, and Michael Paquier Discussion:
  http://postgr.es/m/CAA4eK1JpcMsEtOL_J7WODumeEfyrPi7FPYHeVdS7fyyrCrgp4w@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/09c2e7cd2ff0b884625c37ce8249832820c58710

- Use a real RT index when setting up partition tuple routing.  Before, we
  always used a dummy value of 1, but that's not right when the partitioned
  table being modified is inside of a WITH clause rather than part of the main
  query.  Amit Langote, reported and reviewd by Etsuro Fujita, with a comment
  change by me.  Discussion:
  http://postgr.es/m/ee12f648-8907-77b5-afc0-2980bcb0aa37@lab.ntt.co.jp
  https://git.postgresql.org/pg/commitdiff/f81a91db4d1c2032632aa5df9fc14be24f5fe5ec

- Reverse-convert row types in ExecWithCheckOptions.  Just as we already do in
  ExecConstraints, and for the same reason: to improve the quality of error
  messages.  Etsuro Fujita, reviewed by Amit Langote Discussion:
  http://postgr.es/m/56e0baa8-e458-2bbb-7936-367f7d832e43@lab.ntt.co.jp
  https://git.postgresql.org/pg/commitdiff/c85ec643ff2586e2d144374f51f93bfa215088a2

- pg_rewind: Fix busted sanity check.  As written, the code would only fail the
  sanity check if none of the columns returned by the server were of the
  expected type, but we want it to fail if even one column is not of the
  expected type.  Discussion:
  http://postgr.es/m/CA+TgmoYuY5zW7JEs+1hSS1D=V5K8h1SQuESrq=bMNeo0B71Sfw@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/063ff9210c54928a2d19f9e826486621809e1b82

- pg_rewind: Fix some problems when copying files >2GB.  When incrementally
  updating a file larger than 2GB, the old code could either fail outright (if
  the client asked the server for bytes beyond the 2GB boundary) or fail to copy
  all the blocks that had actually been modified (if the server reported a file
  size to the client in excess of 2GB), resulting in data corruption.
  Generally, such files won't occur anyway, but they might if using a
  non-default segment size or if there the directory contains stray files
  unrelated to PostgreSQL.  Fix by a more prudent choice of data types.  Even
  with these improvements, this code still uses a mix of different types (off_t,
  size_t, uint64, int64) to represent file sizes and offsets, not all of which
  necessarily have the same width or signedness, so further cleanup might be in
  order here.  However, at least now they all have the potential to be 64 bits
  wide on 64-bit platforms.  Kuntal Ghosh and Michael Paquier, with a tweak by
  me.  Discussion:
  http://postgr.es/m/CAGz5QC+8gbkz=Brp0TgoKNqHWTzonbPtPex80U0O6Uh_bevbaA@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/a46fe6e8be02421ea7e80c5a6d5e388417904fd4

Andrew Dunstan pushed:

- Use usleep instead of select for timeouts in PostgresNode.pm.  select() for
  pure timeouts is not portable, and in particular doesn't work on Windows.
  Discussion:
  https://postgr.es/m/186943e0-3405-978d-b19d-9d3335427c86@2ndQuadrant.com
  https://git.postgresql.org/pg/commitdiff/6c6970a280a50434c28ccd461ba864798f5d2a04

- Improve legibility of numeric literal.
  https://git.postgresql.org/pg/commitdiff/cde11fa3c003407fc6c4ddc427d57e588ea17d1c
  Merge large_object.sql test into largeobject.source.  It seems pretty
  confusing to have tests named both largeobject and large_object.  The latter
  is of very recent vintage (commit ff992c074), so get rid of it in favor of
  merging into the former.  Also, enable the LO comment test that was added by
  commit 70ad7ed4e, since the later commit added the then-missing pg_upgrade
  functionality.  The large_object.sql test case is almost completely redundant
  with that, but not quite: it seems like creating a user-defined LO with an OID
  in the system range might be an interesting case for pg_upgrade, so let's keep
  it.  Like the earlier patch, back-patch to all supported branches.
  Discussion: https://postgr.es/m/18665.1500306372@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/a570feaf927b73c5036fa47ea949ca51f2687950

Tom Lane pushed:

- Merge large_object.sql test into largeobject.source.  It seems pretty
  confusing to have tests named both largeobject and large_object.  The latter
  is of very recent vintage (commit ff992c074), so get rid of it in favor of
  merging into the former.  Also, enable the LO comment test that was added by
  commit 70ad7ed4e, since the later commit added the then-missing pg_upgrade
  functionality.  The large_object.sql test case is almost completely redundant
  with that, but not quite: it seems like creating a user-defined LO with an OID
  in the system range might be an interesting case for pg_upgrade, so let's keep
  it.  Like the earlier patch, back-patch to all supported branches.
  Discussion: https://postgr.es/m/18665.1500306372@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/a570feaf927b73c5036fa47ea949ca51f2687950

- Doc: explain dollar quoting in the intro part of the pl/pgsql chapter.  We're
  throwing people into the guts of the syntax with not much context; let's back
  up one step and point out that this goes inside a literal in a CREATE FUNCTION
  command.  Per suggestion from Kurt Kartaltepe.  Discussion:
  https://postgr.es/m/CACawnnyWAmH+au8nfZhLiFfWKjXy4d0kY+eZWfcxPRnjVfaa_Q@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/533463307bf67e1bb7acc345ba7ea535c6aebb78

- Doc: fix thinko in v10 release notes.  s/log_destination/log_directory/, per
  Jov in bug #14749.  Report:
  https://postgr.es/m/20170718082444.9229.99690@wrigleys.postgresql.org
  https://git.postgresql.org/pg/commitdiff/fb9bd4b0469e06d96c8cfff86d231401b0916736

- Fix serious performance problems in json(b) to_tsvector().  In an off-list
  followup to bug #14745, Bob Jones complained that to_tsvector() on a 2MB jsonb
  value took an unreasonable amount of time and space --- enough to draw the
  wrath of the OOM killer on his machine.  On my machine, his example proved to
  require upwards of 18 seconds and 4GB, which seemed pretty bogus considering
  that to_tsvector() on the same data treated as text took just a couple hundred
  msec and 10 or so MB.  On investigation, the problem is that the
  implementation scans each string element of the json(b) and converts it to
  tsvector separately, then applies tsvector_concat() to join those separate
  tsvectors.  The unreasonable memory usage came from leaking every single one
  of the transient tsvectors --- but even without that mistake, this is an
  O(N^2) or worse algorithm, because tsvector_concat() has to repeatedly process
  the words coming from earlier elements.  We can fix it by accumulating all the
  lexeme data and applying make_tsvector() just once.  As a side benefit, that
  also makes the desired adjustment of lexeme positions far cheaper, because we
  can just tweak the running "pos" counter between JSON elements.  In passing,
  try to make the explanation of that tweak more intelligible.  (I didn't think
  that a barely-readable comment far removed from the actual code was helpful.)
  And do some minor other code beautification.
  https://git.postgresql.org/pg/commitdiff/b4c6d31c0be0f5c42a75d50afcf13bdd392db4a1

- Improve make_tsvector() to handle empty input, and simplify its callers.  It
  seemed a bit silly that each caller of make_tsvector() was laboriously
  special-casing the situation where no lexemes were found, when it would be
  easy and much more bullet-proof to make make_tsvector() handle that.
  https://git.postgresql.org/pg/commitdiff/04a2c7f412d01da8100de79b13df4fd39e15ce25

- Doc: add missing note about permissions needed to change log_lock_waits.
  log_lock_waits is PGC_SUSET, but config.sgml lacked the standard boilerplate
  sentence noting that.  Report:
  https://postgr.es/m/20170719100838.19352.16320@wrigleys.postgresql.org
  https://git.postgresql.org/pg/commitdiff/5752dcd45bd8b9a9115d4be12b9a391464884a39

- Add static assertions about pg_control fitting into one disk sector.  When
  pg_control was first designed, sizeof(ControlFileData) was small enough that a
  comment seemed like plenty to document the assumption that it'd fit into one
  disk sector.  Now it's nearly 300 bytes, raising the possibility that somebody
  would carelessly add enough stuff to create a problem.  Let's add a
  StaticAssertStmt() to ensure that the situation doesn't pass unnoticed if it
  ever occurs.  While at it, rename PG_CONTROL_SIZE to PG_CONTROL_FILE_SIZE to
  make it clearer what that symbol means, and convert the existing runtime
  comparisons of sizeof(ControlFileData) vs. PG_CONTROL_FILE_SIZE to be static
  asserts --- we didn't have that technology when this code was first written.
  Discussion: https://postgr.es/m/9192.1500490591@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/3cb29c42f990522131535eea75c691fb23191685

- Fix dumping of outer joins with empty qual lists.  Normally, a JoinExpr would
  have empty "quals" only if it came from CROSS JOIN syntax.  However, it's
  possible to get to this state by specifying NATURAL JOIN between two tables
  with no common column names, and there might be other ways too.  The code
  previously printed no ON clause if "quals" was empty; that's right for CROSS
  JOIN but syntactically invalid if it's some type of outer join.  Fix by
  printing ON TRUE in that case.  This got broken by commit 2ffa740be, which
  stopped using NATURAL JOIN syntax in ruleutils output due to its brittleness
  in the face of column renamings.  Back-patch to 9.3 where that commit
  appeared.  Per report from Tushar Ahuja.  Discussion:
  https://postgr.es/m/98b283cd-6dda-5d3f-f8ac-87db8c76a3da@enterprisedb.com
  https://git.postgresql.org/pg/commitdiff/eb145fdfea91ee5dc6d7aad0309527f810f7c90a

- Doc: clarify description of degenerate NATURAL joins.  Claiming that NATURAL
  JOIN is equivalent to CROSS JOIN when there are no common column names is only
  strictly correct if it's an inner join; you can't say e.g. CROSS LEFT JOIN.
  Better to explain it as meaning JOIN ON TRUE, instead.  Per a suggestion from
  David Johnston.  Discussion:
  https://postgr.es/m/CAKFQuwb+mYszQhDS9f_dqRrk1=Pe-S6D=XMkAXcDf4ykKPmgKQ@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/ed3dc224e5aabd3cb0a5c348107f973fe5b10b0d

- In v10 release notes, call out sequence changes as a compatibility item.  The
  previous description didn't make it clear that this change potentially breaks
  applications, partly because the entry wasn't even in the compatibility-hazard
  section.  Move and clarify.  Discussion:
  https://postgr.es/m/603f3f0a-f89d-ae8b-1da9-a92fac16086d@enterprisedb.com
  https://git.postgresql.org/pg/commitdiff/866f4a7c210857aa342bf901558d170325094dde

- Re-establish postgres_fdw connections after server or user mapping changes.
  Previously, postgres_fdw would keep on using an existing connection even if
  the user did ALTER SERVER or ALTER USER MAPPING commands that should affect
  connection parameters.  Teach it to watch for catcache invals on these
  catalogs and re-establish connections when the relevant catalog entries
  change.  Per bug #14738 from Michal Lis.  In passing, clean up some rather
  crufty decisions in commit ae9bfc5d6 about where fields of ConnCacheEntry
  should be reset.  We now reset all the fields whenever we open a new
  connection.  Kyotaro Horiguchi, reviewed by Ashutosh Bapat and myself.
  Back-patch to 9.3 where postgres_fdw appeared.  Discussion:
  https://postgr.es/m/20170710113917.7727.10247@wrigleys.postgresql.org
  https://git.postgresql.org/pg/commitdiff/8bf58c0d9bd336868e2d6489f11dc094cad9ad91

- Stabilize postgres_fdw regression tests.  The new test cases added in commit
  8bf58c0d9 turn out to have output that can vary depending on the lc_messages
  setting prevailing on the test server.  Hide the remote end's error messages
  to ensure stable output.  This isn't a terribly desirable solution; we'd
  rather know that the connection failed for the expected reason and not some
  other one.  But there seems little choice for the moment.  Per buildfarm.
  Discussion: https://postgr.es/m/18419.1500658570@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/88f48b57fd33289c26c78ecb28c5f2a1c7a4ac6c

- Doc: update versioning information in libpq.sgml.  The descriptions of
  PQserverVersion and PQlibVersion hadn't been updated for the new two-part
  version-numbering approach.  Fix that.  In passing, remove some trailing
  whitespace elsewhere in the file.
  https://git.postgresql.org/pg/commitdiff/e22efaabf16e3972b91893d89597407d142fb309

- Update expected results for collate.linux.utf8 regression test.  I believe
  this changed as a consequence of commit 54baa4813: trying to clone the "C"
  collation now produces a true clone with collencoding -1, hence the error
  message if it's duplicate no longer specifies an encoding.  Per buildfarm
  member crake, which apparently hadn't been running this test for the last few
  weeks.
  https://git.postgresql.org/pg/commitdiff/991c8b04fc5d61a308bb00ea34a7ff710051c53f

- Improve comments about partitioned hash table freelists.  While I couldn't
  find any live bugs in commit 44ca4022f, the comments seemed pretty far from
  adequate; in particular it was not made plain that "borrowing" entries from
  other freelists is critical for correctness.  Try to improve the commentary.
  A couple of very minor code style tweaks, as well.  Discussion:
  https://postgr.es/m/10593.1500670709@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/ab2324fd468c4bc6d8b012552550ed951d97339a

- Fix pg_dump's handling of event triggers.  pg_dump with the --clean option
  failed to emit DROP EVENT TRIGGER commands for event triggers.  In a closely
  related oversight, it also did not emit ALTER OWNER commands for event
  triggers.  Since only superusers can create event triggers, the latter
  oversight is of little practical consequence ... but if we're going to record
  an owner for event triggers, then surely pg_dump should preserve it.  Per
  complaint from Greg Atkins.  Back-patch to 9.3 where event triggers were
  introduced.  Discussion:
  https://postgr.es/m/20170722191142.yi4e7tzcg3iacclg@gmail.com
  https://git.postgresql.org/pg/commitdiff/93f039b4944fdf806f029ed46cf192bc9021d8e7

Dean Rasheed pushed:

- Use MINVALUE/MAXVALUE instead of UNBOUNDED for range partition bounds.
  Previously, UNBOUNDED meant no lower bound when used in the FROM list, and no
  upper bound when used in the TO list, which was OK for single-column range
  partitioning, but problematic with multiple columns. For example, an upper
  bound of (10.0, UNBOUNDED) would not be collocated with a lower bound of
  (10.0, UNBOUNDED), thus making it difficult or impossible to define contiguous
  multi-column range partitions in some cases.  Fix this by using MINVALUE and
  MAXVALUE instead of UNBOUNDED to represent a partition column that is
  unbounded below or above respectively. This syntax removes any ambiguity, and
  ensures that if one partition's lower bound equals another partition's upper
  bound, then the partitions are contiguous.  Also drop the constraint
  prohibiting finite values after an unbounded column, and just document the
  fact that any values after MINVALUE or MAXVALUE are ignored. Previously it was
  necessary to repeat UNBOUNDED multiple times, which was needlessly verbose.
  Note: Forces a post-PG 10 beta2 initdb.  Report by Amul Sul, original patch by
  Amit Langote with some additional hacking by me.  Discussion:
  https://postgr.es/m/CAAJ_b947mowpLdxL3jo3YLKngRjrq9+Ej4ymduQTfYR+8=YAYQ@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/d363d42bb9a4399a0207bd3b371c966e22e06bd3

- Make the new partition regression tests locale-independent.  The order of
  partitions listed by \d+ is in general locale-dependent.  Rename the
  partitions in the test added by d363d42bb9 to force them to be listed in a
  consistent order.
  https://git.postgresql.org/pg/commitdiff/68f785fd522bca9372cce965ac10cbd8c239c076

Teodor Sigaev pushed:

- Fix double shared memory allocation.  SLRU buffer lwlocks are allocated twice
  by oversight in commit fe702a7b3f9f2bc5bf6d173166d7d55226af82c8 where that
  locks were moved to separate tranche. The bug doesn't have user-visible
  effects except small overspending of shared memory.  Backpatch to 9.6 where it
  was introduced.  Alexander Korotkov with small editorization by me.
  https://git.postgresql.org/pg/commitdiff/7e1fb4c59e4ac86de2640d0f3453fde270ec1ff8

Álvaro Herrera pushed:

- Fix typo in comment.  Commit fd31cd265138 renamed the variable to
  skipping_blocks, but forgot to update this comment.  Noticed while inspecting
  code.
  https://git.postgresql.org/pg/commitdiff/de38489b926e3e5af84f22cf4788fe4498e13c72

== Pending Patches ==

Amit Kapila sent in another revision of a patch to parallelize queries
containing initplans.

Shubham Barai sent in another revision of a patch to add predicate locking for
GIN indexes.

Thomas Munro sent in two more revisions of a patch to add more flexible LDAP
filters.

Alik Khilazhev sent in another revision of a patch to add Zipfian distributions
to pgbench.

Andres Freund sent in two revisions of a patch to move ExecProcNode from
dispatch to callback based model.

Alexey Chernyshov sent in a patch to add citext_pattern_ops to the citext
contrib module.

Yura Sokolov sent in another revision of a patch to fix performance degradation
of contended LWLock on NUMA.

Yugo Nagata sent in a patch to ensure that all statistics are sent after a few
DML are performed.

Marina Polyakova sent in another revision of a patch to precalculate stable
functions.

Yura Sokolov sent in two revisions of a patch to increase the size of the vacuum
ring buffer.

Mark Rofail sent in another revision of a patch to add foreign key arrays.

Victor Drobny sent in a patch to add queryto_tsquery(), which takes a 'google
like' query string and translates it to tsquery.

Robins Tharakan sent in two revisions of a patch to add --no-comments to
pg_dump.

Mark Dilger sent in two revisions of a patch to remove abstime and friends from
the main line code base.

Fabien COELHO sent in a patch to fix the documentation of random_exponential.

Etsuro Fujita sent in two more revisions of a patch to fix the rewrite tlist.

Jeevan Ladhe sent in another revision of a patch to add support for default
partitions in declarative partitions.

Yugo Nagata sent in a patch to fix incorrect comments of XLByteToSeg() and
XLByteToPrevSeg().

Thomas Munro sent in three revisions of a patch to fix join selectivity for <>
comparisons.

Craig Ringer sent in two revisions of a patch to introduce heap_infomask_flags
to decode infomask and infomask2.

Michaël Paquier sent in a patch to check for objaddr nullness.

Etsuro Fujita sent in two revisions of a patch to fix mishandling of WITH
CHECK OPTION constraints in direct foreign table modification.

Yugo Nagata and Fabrízio de Royes Mello traded patches to add hooks for
session_start and session_end.

Yura Sokolov sent in another revision of a patch to make an improvement to
compactify_tuples by simplifyin PageRepairFragmentation.

Alexey Chernyshov sent in a patch to add functions on GIN and GiST indexes to
pageinspect.

Hadi Moshayedi move the declarations of ExplainBeginGroup()/ExplainEndGroup()
from explain.c to explain.h.

Marina Polyakova sent in another revision of a patch to pgbench to retry
transactions with serialization or deadlock errors.

Mengxing Liu sent in another revision of a patch to eliminate O(N^2) scaling
from rw-conflict tracking in serializable transactions using a skip list.

Dima Pavlov sent in a patch to improve perfomance for index search ANY(ARRAY[])
condition with single item.



pgsql-announce by date

Next:From: Kouhei SutouDate: 2017-07-24 07:45:51
Subject: PGroonga 1.2.3 - Make PostgreSQL fast full text search platform for all languages
Previous:From: Daniele VarrazzoDate: 2017-07-22 15:29:08
Subject: Psycopg 2.7.2 released

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group