== PostgreSQL Weekly News - February 04 2018 ==

== PostgreSQL Product News ==

pgsodium 1.0.16, an extention which adds libsodium encryption to PostgreSQL,

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

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 will open soon.

== 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 da...@fetter.org, German language to p...@pgug.de, Italian
language to p...@itpug.org.

== Applied Patches ==

Tom Lane pushed:

- Avoid misleading psql password prompt when username is multiply specified.
  When a password is needed, cases such as psql -d
  "postgresql://alice@localhost/testdb" -U bob would incorrectly prompt for
  "Password for user bob: ", when actually the connection will be attempted with
  username alice.  The priority order of which name to use isn't that important
  here, but the misleading prompt is.  When we are prompting for a password
  after initial connection failure, we can fix this reliably by looking at
  PQuser(conn) to see how libpq interpreted the connection arguments.  But when
  we're doing a forced password prompt because of a -W switch, we can't use that
  solution.  Fortunately, because the main use of -W is for noninteractive
  situations, it's less critical to produce a helpful prompt in such cases.  I
  made the startup prompt for -W just say "Password: " all the time, rather than
  expending extra code on trying to identify which username to use.  In the case
  of a \c command (after -W has been given), there's already logic in do_connect
  that determines whether the "dbname" is a connstring or URI, so we can avoid
  lobotomizing the prompt except in cases that are actually dubious.  (We could
  do similarly in startup.c if anyone complains, but for now it seems not
  worthwhile, especially since that would still be only a partial solution.) Per
  bug #15025 from Akos Vandra.  Although this is arguably a bug fix, it doesn't
  seem worth back-patching.  The case where it matters seems like a very
  corner-case usage, and someone might complain that we'd changed the behavior
  of -W in a minor release.  Discussion:

- Save a few bytes by removing useless last argument to SearchCatCacheList.
  There's never any value in giving a fully specified cache key to
  SearchCatCacheList: you might as well call SearchCatCache instead, since there
  could be only one match.  So the maximum useful number of key arguments is one
  less than the supported number of key columns.  We might as well remove the
  useless extra argument and save some few bytes per call site, as well as a
  cycle or so per call.  I believe the reason it was coded like this is that
  originally, callers had to write out all the dummy arguments in each call, and
  so it seemed less confusing if SearchCatCache and SearchCatCacheList took the
  same number of key arguments.  But since commit e26c539e9, callers only write
  their live arguments explicitly, making that a non-factor; and there's surely
  been enough time for third-party modules to adapt to that coding style.  So
  this is only an ABI break not an API break for callers.  Per discussion with
  Oliver Ford, this might also make it less confusing how to use
  SearchCatCacheList correctly.  Discussion:

- First-draft release notes for 10.2.  As usual, the release notes for other
  branches will be made by cutting these down, but put them up for community
  review first.

- Be more wary about shm_toc_lookup failure.  Commit 445dbd82a basically missed
  the point of commit d46633506, which was that we shouldn't allow
  shm_toc_lookup() failure to lead to a core dump or assertion crash, because
  the odds of such a failure should never be considered negligible.  It's
  correct that we can't expect the PARALLEL_KEY_ERROR_QUEUE TOC entry to be
  there if we have no workers.  But if we have no workers, we're not going to do
  anything in this function with the lookup result anyway, so let's just skip
  it.  That lets the code use the easy-to-prove-safe noError=false case, rather
  than anything requiring effort to review.  Back-patch to v10, like the
  previous commit.  Discussion:

- Fix another instance of unsafe coding for shm_toc_lookup failure.  One or
  another author of commit 5bcf389ec seems to have thought that computing an
  offset from a NULL pointer would yield another NULL pointer.  There may
  possibly be architectures where that works, but common machines don't work
  like that.  Per a quick code review of places calling shm_toc_lookup and not
  using noError = false.

- Minor copy-editing for 10.2 release notes.  Second pass after taking a break

- Doc: minor clarifications in xindex.sgml.  I noticed some slightly confusing
  or out-of-date verbiage here while working on the window RANGE patch.  Seems
  worth committing separately.

- Release notes for 10.2, 9.6.7, 9.5.11, 9.4.16, 9.3.21.

Andres Freund pushed:

- Improve bit perturbation in TupleHashTableHash.  The changes in
  b81b5a96f424531b97cdd1dba97d9d1b9c9d372e did not fully address the issue,
  because the bit-mixing of the IV into the final hash-key didn't prevent
  clustering in the input-data survive in the output data.  This didn't cause a
  lot of problems because of the additional growth conditions added
  d4c62a6b623d6eef88218158e9fa3cf974c6c7e5. But as we want to rein those in due
  to explosive growth in some edges, this needs to be fixed.  Author: Andres
  Freund Discussion:
  Backpatch: 10, where simplehash was introduced

- Prevent growth of simplehash tables when they're "too empty".  In cases where
  simplehash tables where filled with either a lot of conflicting hash-values,
  or values that hash to consecutive values (i.e. build "chains") the growth
  heuristics in d4c62a6b623d6eef88218158e9fa3cf974c6c7e5 could trigger rather
  explosively.  To fix that, address some of the reasons (see previous commit)
  of why the growth heuristics where needed, and only allow growth when the
  table isn't too empty. While that means there's a few cases of bad input that
  can be slower, that seems a lot better than running very quickly out of
  memory.  Author: Tomas Vondra and Andres Freund, with additional input by
  Thomas Munro, Tom Lane Todd A. Cook Reported-By: Todd A. Cook, Tomas Vondra,
  Thomas Munro Discussion:
  Backpatch: 10, where simplehash was introduced

- Introduce ExecQualAndReset() helper.  It's a common task to evaluate a qual
  and reset the corresponding expression context. Currently that requires
  storing the result of the qual eval, resetting the context, and then reacting
  on the result. As that's awkward several places only reset the context next
  time through a node. That's not great, so introduce a helper that evaluates
  and resets.  It's a bit ugly that it currently uses MemoryContextReset()
  instead of ResetExprContext(), but that seems easier than reordering all of
  executor.h.  Author: Andres Freund Discussion:

- Initialize unused ExprEvalStep fields.  ExecPushExprSlots didn't initialize
  ExprEvalStep's resvalue/resnull steps as it didn't use them. That caused wrong
  valgrind warnings for an upcoming patch, so zero-intialize.  Also
  zero-initialize all scratch ExprEvalStep's allocated on the stack, to avoid
  issues with similar future omissions of non-critial data.

Peter Eisentraut pushed:

- doc: Clarify pg_upgrade documentation.  Clarify that the restriction against
  reg* types only applies to table columns using these types, not to the type
  appearing in any other way, for example as a function argument.

- Silence complaint about dead assignment.  The preferred place for "placate
  compiler" assignments is after elog(ERROR), not before it.  Otherwise,
  scan-build complains about a dead assignment.

- Add some noreturn attributes to help static analyzers.

- Remove dead assignment.  per scan-build

- Fix up references to scram-sha-256.  pg_hba_file_rules erroneously reported
  this as scram-sha256.  Fix that.  To avoid future errors and confusion, also
  adjust documentation links and internal symbols to have a separator between
  "sha" and "256".  Reported-by: Christophe Courtois
  <christophe.court...@dalibo.com> Author: Michael Paquier

- Refactor client-side SSL certificate checking code.  Separate the parts
  specific to the SSL library from the general logic.  The previous code
  structure was open_client_SSL() calls verify_peer_name_matches_certificate()
  calls verify_peer_name_matches_certificate_name() calls
  wildcard_certificate_match() and was completely in fe-secure-openssl.c.  The
  new structure is open_client_SSL() [openssl] calls
  pq_verify_peer_name_matches_certificate() [generic] calls
  pgtls_verify_peer_name_matches_certificate_guts() [openssl] calls
  openssl_verify_peer_name_matches_certificate_name() [openssl] calls
  pq_verify_peer_name_matches_certificate_name() [generic] calls
  wildcard_certificate_match() [generic] Move the generic functions into a new
  file fe-secure-common.c, so the calls generally go fe-connect.c -> fe-secure.c
  -> fe-secure-${impl}.c -> fe-secure-common.c, although there is a bit of
  back-and-forth between the last two.  Reviewed-by: Michael Paquier

- Fix application of identity values in some cases.  Investigation of
  2d2d06b7e27e3177d5bef0061801c75946871db3 revealed that identity values were
  not applied in some further cases, including logical replication subscribers,
  VALUES RTEs, and ALTER TABLE ... ADD COLUMN.  To fix all that, apply the
  identity column expression in build_column_default() instead of repeating the
  same logic at each call site.  For ALTER TABLE ... ADD COLUMN ... IDENTITY,
  the previous coding completely ignored that existing rows for the new column
  should have values filled in from the identity sequence.  The coding using
  build_column_default() fails for this because the sequence ownership isn't
  registered until after ALTER TABLE, and we can't do it before because we don't
  have the column in the catalog yet.  So we specially remember in ColumnDef the
  sequence name that we decided on and build a custom NextValueExpr using that.
  Reviewed-by: Michael Paquier <michael.paqu...@gmail.com>

- doc: Fix index link.  The index entry was pointing to a slightly wrong

- doc: Clarify psql --list documentation a bit more.

- doc: Fix name in release notes.  Author: Alexander Lakhin

Robert Haas pushed:

- Fix test case for 'outer pathkeys do not match mergeclauses' fix.  Commit
  4bbf6edfbd5d03743ff82dda2f00c738fb3208f5 added a test case, but it turns out
  that the test case doesn't reliably test for the bug, and in the context of
  the regression test suite did not because ANALYZE had not been run.  Report
  and patch by Etsuro Fujita.  I added a comment along lines previously
  suggested by Tom Lane.  Discussion:

- pg_prewarm: Add missing LWLockRegisterTranche call.  Commit
  79ccd7cbd5ca44bee0191d12e9e65abf702899e7, which added automatic prewarming,
  neglected this.  Kyotaro Horiguchi, reviewed by me.  Discussion:

- Fix list partition constraints for partition keys of array type.  The old code
  generated always generated a constraint of the form col = ANY(ARRAY[val1,
  val2, ...]), but that's invalid when col is an array type.  Instead, generate
  col = val when there's only one value, col = val1 OR col = val2 OR ... when
  there are multiple values and col is of array type, and the old form when
  there are multiple values and col is not of an array type.  As a side benefit,
  this makes constraint exclusion able to prune a list partition declared to
  accept a single Boolean value, which didn't work before.  Amit Langote,
  reviewed by Etsuro Fujita Discussion:

- pgcrypto's encrypt() supports AES-128, AES-192, and AES-256.  Previously, only
  128 was mentioned, but the others are also supported.  Thomas Munro, reviewed
  by Michael Paquier and extended a bit by me.  Discussion:

- Fix typo: colums -> columns.  Along the way, also fix code indentation.
  Alexander Lakhin, reviewed by Michael Paquier Discussion:

- Fix possible failure to mark hash metapage dirty.  Report and suggested fix by
  Lixian Zou.  Amit Kapila put it in the form of a patch and reviewed.

- Add new function WaitForParallelWorkersToAttach.  Once this function has been
  called, we know that all workers have started and attached to their error
  queues -- so if any of them subsequently exit uncleanly, we'll be sure to
  throw an ERROR promptly.  Otherwise, users of the ParallelContext machinery
  must be careful not to wait forever for a worker that has failed to start.
  Parallel query manages to work without needing this for reasons explained in
  new comments added by this patch, but it's a useful primitive for other
  parallel operations, such as the pending patch to make creating a btree index
  run in parallel.  Amit Kapila, revised by me.  Additional review by Peter
  Geoghegan.  Discussion:

- Refactor code for partition bound searching.  Remove partition_bound_cmp() and
  partition_bound_bsearch(), whose void * argument could be, depending on the
  situation, of any of three different types: PartitionBoundSpec *,
  PartitionRangeBound *, Datum *.  Instead, introduce separate bound-searching
  functions for each situation: partition_list_bsearch, partition_range_bsearch,
  partition_range_datum_bsearch, and partition_hash_bsearch.  This requires
  duplicating the code for binary search, but it makes the code much more type
  safe, involves fewer branches at runtime, and at least in my opinion, is much
  easier to understand.  Along the way, add an option to
  partition_range_datum_bsearch allowing the number of keys to be specified, so
  that we can search for partitions based on a prefix of the full list of
  partition keys.  This is important for pending work to improve partition
  pruning.  Amit Langote, per a suggestion from me.  Discussion:

- Support parallel btree index builds.  To make this work, tuplesort.c and
  logtape.c must also support parallelism, so this patch adds that
  infrastructure and then applies it to the particular case of parallel btree
  index builds.  Testing to date shows that this can often be 2-3x faster than a
  serial index build.  The model for deciding how many workers to use is fairly
  primitive at present, but it's better than not having the feature.  We can
  refine it as we get more experience.  Peter Geoghegan with some help from
  Rushabh Lathia.  While Heikki Linnakangas is not an author of this patch, he
  wrote other patches without which this feature would not have been possible,
  and therefore the release notes should possibly credit him as an author of
  this feature.  Reviewed by Claudio Freire, Heikki Linnakangas, Thomas Munro,
  Tels, Amit Kapila, me.  Discussion:

Bruce Momjian pushed:

- doc:  mention datadir locations are actually config locations.  Technically,
  pg_upgrade's --old-datadir and --new-datadir are configuration directories,
  not necessarily data directories.  This is reflected in the 'postgres' manual
  page, so do the same for pg_upgrade.  Reported-by: Yves Goergen Bug: 14898
  Backpatch-through: 10

- doc: Improve pg_upgrade rsync examples to use clusterdir.  Commit
  9521ce4a7a1125385fb4de9689f345db594c516a from Sep 13, 2017 and backpatched
  through 9.5 used rsync examples with datadir.  The reporter has pointed out,
  and testing has verified, that clusterdir must be used, so update the docs
  accordingly.  Reported-by: Don Seiler Discussion:
  Backpatch-through: 9.5

- doc:  in contrib-spi, mention and link to the meaning of SPI.  Also remove
  outdated comment about SPI subtransactions.  Reported-by: greg...@arenius.com
  Backpatch-through: 9.3

- doc:  clearify trigger behavior for inheritance.  The previous wording added
  in PG 10 wasn't specific enough about the behavior of statement and row
  triggers when using inheritance.  Reported-by: i...@thepathcentral.com
  Backpatch-through: 10

- doc:  clarify major/minor pg_upgrade versions with examples.  The previous
  docs added in PG 10 were not clear enough for someone who didn't understand
  the PG 10 version change, so give more specific examples.  Reported-by:
  j...@room118solutions.com Discussion:
  Backpatch-through: 10

- doc:  fix trigger inheritance wording.  Fix wording from commit
  1cf1112990cff432b53a74a0ac9ca897ce8a7688 Reported-by: Robert Haas
  Backpatch-through: 10

- psql:  Add quit/help behavior/hint, for other tool portability.  Issuing
  'quit'/'exit' in an empty psql buffer exits psql.  Issuing 'quit'/'exit' in a
  non-empty psql buffer alone on a line with no prefix whitespace issues a hint
  on how to exit.  Also add similar 'help' hints for 'help' in a non-empty psql
  buffer.  Reported-by: Everaldo Canuto Discussion:
  Author: original author Robert Haas, modified by me

Stephen Frost pushed:

- Improve ALTER TABLE synopsis.  Add into the ALTER TABLE synopsis the
  definition of partition_bound_spec, column_constraint, index_parameters and
  exclude_element.  Initial patch by Lætitia Avrot, with further improvements by
  Amit Langote and Thomas Munro.  Discussion:

== Pending Patches ==

Yuto Hayamizu sent in another revision of a patch to fix overestimated filter

Amit Langote sent in two revisions of a patch to include the new local
partitioned indexes in the CREATE INDEX documentation.

Kyotaro HORIGUCHI sent in two more revisions of a patch to add a WAL relief
valve for replication slots, add monitoring for replication slots, add a TAP
test for the slot limit feature, and document same.

Ildar Musin sent in another revision of a patch to add general purpose hashing
functions to pgbench.

Ildus Kurbangaliev sent in another revision of a patch to add custom compression

Konstantin Knizhnik sent in two more revisions of a patch to optimize secondary
index access.

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

David Steele sent in another revision of a patch to implement a configurable
file mode mask.

Andres Freund sent in a patch to deconflict datetime.h macro names.

Etsuro Fujita sent in another revision of a patch to change how a list partition
constraint is emitted.

David Rowley sent in another revision of a patch to allow deciding whether

Amit Langote sent in a patch to fix what had appeared to be a bug in local
indexes on partitioned tables.

Robert Haas sent in two more revisions of a patch to account for parallel paths
tlist cost.

Jeevan Chalke sent in two more revisions of a patch to implement partition-wise

Yugo Nagata sent in three more revisions of a patch to implement lockable views.

Amit Langote sent in another revision of a patch to create a syntax for boolean

Simon Riggs sent in two more revisions of a patch to implement MERGE.

Oliver Ford and Tom Lane traded patches to implement RANGE with values and
exclusions clauses to windowing functions.

John Naylor sent in another revision of a patch to refactor bootstrap data for
sanity and legibility.

Doug Rady sent in a patch to refactor all of the connection state PQfinish() and
NULL’ing into a single function, except for PQfinish() in doConnect().

Anastasia Lubennikova sent in another revision of a patch to implement covering
and unique indexes.

Dmitry Dolgov sent in another revision of a patch to implement generic type
subscripting and use same for arrays and JSONB.

Daniel Vérité sent in a patch to add a CSV output format to psql.

Peter Eisentraut sent in another revision of a patch to support GnuTLS.

Kyotaro HORIGUCHI, Amit Langote and David Rowley traded patches to speed up
partition pruning.

Haozhou Wang sent in a patch to add missing type conversion functions for

Michail Nikolaev sent in a patch to optimize OFFSET with IndexScan using the
visibility map.

Yugo Nagata sent in two revisions of a patch to fix an infelicity between
CURRENT OF and IndexOnlyScan.

Michaël Paquier sent in another revision of a patch to refactor the syscache
routines to get attribute name, extend the lookup routines for FDW and foreign
server with NULL handling, refactor the routines for subscription and
publication lookups, and eliminate user-visible cache lookup errors for objaddr
SQL functions.

Anton Dignös sent in a patch to fix an IndexJoin memory problem using SP-GiST
and boxes.

Amit Langote sent in a patch to disregard nulls in SAOP rightarg array/list
during CE.

Kyotaro HORIGUCHI sent in a patch to fix line_perp() (?-|).

Amit Langote sent in another revision of a patch to teach
operator_predicate_proof() to strip RelabelType.

Peter Eisentraut sent in a patch to fix a bug which caused cancelling parallel
query to lead to a segfault.

Amit Kapila sent in a patch to ensure that MarkBufferDirty is called at the
appropriate place in the hash index code.

Michael Banck sent in a patch to allow pg_dump to /dev/null.

Artur Zakirov sent in another revision of a patch to fix a bug in
to_timestamp() where formats weren't checked correctly.

Dean Rasheed sent in a patch to improve MCV lists for highly skewed

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

Takayuki Tsunakawa sent in a patch to fix a bug in ECPG where freeing memory for
pgtypes would crash on Windows.

Michaël Paquier sent in another revision of a patch to create backup history
files for backups taken from standbys.

Aleksandr Parfenov sent in another revision of a patch to create a more flexible
configuration for full-text search.

Ildus Kurbangaliev sent in a patch to implement a prefix operator, ^@, for text
and give it SP-GiST support.

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

Peter Geoghegan sent in a patch to add logtape.c Valgrind suppression.

Pavel Stěhule and David G. Johnson traded patches to implement schema variables.

Pierre Ducroquet sent in a patch to add support for LLVM 4 and LLVM 5 atop the
JIT patch.

Pierre Ducroquet sent in a patch to fix support for LLVM 3.9 atop the JIT patch.

Amul Sul sent in another revision of a patch to restrict concurrent
UPDATEs and DELETEs during the UPDATE of a partition key.

Reply via email to