== PostgreSQL Weekly News - December 29, 2019 ==

== PostgreSQL Weekly News - December 29, 2019 ==

== PostgreSQL Local ==

Postgres@CERN will be on January 17, 2020.
https://indico.cern.ch/e/PGCERN2020

PGDay SF will take place on January 21, 2020 at the Swedish American Hall in San
Francisco.
https://2020.pgdaysf.org/

pgDay Israel 2020 will take place on March 19, 2020 in Tel Aviv.
The CfP is open through January 15, 2020.
http://pgday.org.il/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace
Saint-Martin. The CfP is open through December 31, 2019 at midnight, Paris time
at https://2020.pgday.paris/callforpapers/
https://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020.  The CfP is open through December 31, 2019 at
https://2020.nordicpgday.org/cfp/

PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL@SCaLE is a two day, two track event which takes place on
March 5-6, 2020, at Pasadena Convention Center, as part of SCaLE 18X.
https://www.socallinuxexpo.org/scale/18x/postgresscale

The German-speaking PostgreSQL Conference 2020 will take place on May 15, 2019
in Stuttgart.

== 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@fetter.org.

== Applied Patches ==

Peter Geoghegan pushed:

- Update nbtree LP_DEAD item deletion comments. Comments about the consequences
  of clearing the BTP_HAS_GARBAGE page flag bit that apply only to VACUUM were
  added to code that deals with opportunistic deletion of LP_DEAD items by
  commit a760893d.  The same comment block was added to both
  _bt_delitems_vacuum() and _bt_delitems_delete().  Correct
  _bt_delitems_delete()'s copy of the comment block.  _bt_delitems_delete()
  reliably deletes items that were found by caller to have their LP_DEAD bit
  set.  There is no question about whether or not unsetting the BTP_HAS_GARBAGE
  bit can miss some LP_DEAD items that were set recently.  Also tweak a related
  section of the nbtree README.
  https://git.postgresql.org/pg/commitdiff/fe97c61c8777858cc1a271e657a7d812e100ef00

- Normalize _bt_finish_split() argument names. Make a function prototype
  argument's name match the function definition's argument name.
  https://git.postgresql.org/pg/commitdiff/696cc3a0cabd5f11d0c8a187b7561f6d0d39c5e0

Álvaro Herrera pushed:

- GetPublicationByName: Don't repeat ourselves. Use get_publication_oid()
  instead of reimplementing it.  Discussion:
  https://postgr.es/m/20191220201017.GA17292@alvherre.pgsql
  https://git.postgresql.org/pg/commitdiff/0fd8cfb20d2d41d4c2df021a5f355965fd8d21bc

- Avoid splitting C string literals with \-newline. Using \ is unnecessary and
  ugly, so remove that.  While at it, stitch the literals back into a single
  line: we've long discouraged splitting error message literals even when they
  go past the 80 chars line limit, to improve greppability.  Leave
  contrib/tablefunc alone.  Discussion:
  https://postgr.es/m/20191223195156.GA12271@alvherre.pgsql
  https://git.postgresql.org/pg/commitdiff/c4dcd9144ba64946c9f9466748bdb2c51719c8a3

- Add pg_dump test for triggers on partitioned tables. This currently works, but
  add this test to ensure it continues to work. Lack of this test became evident
  after a recent bugfix submission that would have inadvertently broken it, in
  https://postgr.es/m/CA+HiwqFM2=i+uHB9o4OkLbE2S3sjPHoVe2wXuAD1GLJ4+Pk9eg@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/27a3b2ad836c9e7dd243bfebc760a9df9d6fd5a3

Tom Lane pushed:

- Prevent a rowtype from being included in itself via a range. We probably
  should have thought of this case when ranges were added, but we didn't.  (It's
  not the fault of commit eb51af71f, because ranges didn't exist then.)  It's an
  old bug, so back-patch to all supported branches.  Discussion:
  https://postgr.es/m/7782.1577051475@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/fc7695891d357a54f0258142de85f88520796b9b

- Disallow partition key expressions that return pseudo-types. This wasn't
  checked originally, but it should have been, because in general pseudo-types
  can't be stored to and retrieved from disk. Notably, partition bound values of
  type "record" would not be interpretable by another session.  In v12 and HEAD,
  add another flag to CheckAttributeType's repertoire so that it can produce a
  specific error message for this case.  That's infeasible in older branches
  without an ABI break, so fall back to a slightly-less-nicely-worded error
  message in v10 and v11.  Problem noted by Amit Langote, though this patch is
  not his initial solution.  Back-patch to v10 where partitioning was
  introduced.  Discussion:
  https://postgr.es/m/CA+HiwqFUzjfj9HEsJtYWcr1SgQ_=iCAvQ=O2Sx6aQxoDu4OiHw@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/39ebb943de9dd64e305d17329b8989e3061d03a5

- Load relcache entries' partitioning data on-demand, not immediately. Formerly
  the rd_partkey and rd_partdesc data structures were always populated
  immediately when a relcache entry was built or rebuilt. This patch changes
  things so that they are populated only when they are first requested.  (Hence,
  callers *must* now always use RelationGetPartitionKey or
  RelationGetPartitionDesc; just fetching the pointer directly is no longer
  acceptable.)  This seems to have some performance benefits, but the main
  reason to do it is that it eliminates a recursive-reload failure that occurs
  if the partkey or partdesc expressions contain any references to the
  relation's rowtype (as discovered by Amit Langote).  In retrospect, since
  loading these data structures might result in execution of nearly-arbitrary
  code via eval_const_expressions, it was a dumb idea to require that to happen
  during relcache entry rebuild.  Also, fix things so that old copies of a
  relcache partition descriptor will be dropped when the cache entry's refcount
  goes to zero.  In the previous coding it was possible for such copies to
  survive for the lifetime of the session, as I'd complained of in a previous
  discussion. (This management technique still isn't perfect, but it's better
  than before.)  Improve the commentary explaining how that works and why it's
  safe to hand out direct pointers to these relcache substructures.  In passing,
  improve RelationBuildPartitionDesc by using the same
  memory-context-parent-swap approach used by RelationBuildPartitionKey, thereby
  making it less dependent on strong assumptions about what
  partition_bounds_copy does.  Avoid doing get_rel_relkind in the critical
  section, too.  Patch by Amit Langote and Tom Lane; Robert Haas deserves some
  credit for prior work in the area, too.  Although this is a pre-existing
  problem, no back-patch: the patch seems too invasive to be safe to back-patch,
  and the bug it fixes is a corner case that seems relatively unlikely to cause
  problems in the field.  Discussion:
  https://postgr.es/m/CA+HiwqFUzjfj9HEsJtYWcr1SgQ_=iCAvQ=O2Sx6aQxoDu4OiHw@mail.gmail.com
  Discussion:
  https://postgr.es/m/CA+TgmoY3bRmGB6-DUnoVy5fJoreiBJ43rwMrQRCdPXuKt4Ykaw@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/5b9312378e2f8fb35ef4584aea351c3319a10422

- Remove equalPartitionDescs(). This is dead code in the wake of the previous
  commit. We can always add it back if we need it again someday.  Discussion:
  https://postgr.es/m/CA+HiwqFUzjfj9HEsJtYWcr1SgQ_=iCAvQ=O2Sx6aQxoDu4OiHw@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/42f74f49367bee1d3da28c4b383faec29364f320

- Allow whole-row Vars to be used in partitioning expressions. In the wake of
  commit 5b9312378, there's no particular reason for this restriction
  (previously, it was problematic because of the implied rowtype reference).  A
  simple constraint on a whole-row Var probably isn't that useful, but
  conceivably somebody would want to pass one to a function that extracts a
  partitioning key.  Besides which, we're expending much more code to enforce
  the restriction than we save by having it, since the latter quantity is now
  zero. So drop the restriction.  Amit Langote  Discussion:
  https://postgr.es/m/CA+HiwqFUzjfj9HEsJtYWcr1SgQ_=iCAvQ=O2Sx6aQxoDu4OiHw@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/bb4114a4e2c65f27931cc074214c051dba2876c3

- Refactor parser's generation of Var nodes. Instead of passing around a pointer
  to the RangeTblEntry that provides the desired column, pass a pointer to the
  associated ParseNamespaceItem.  The RTE is trivially reachable from the
  nsitem, and having the ParseNamespaceItem allows access to additional
  information.  As proof of concept for that, add the rangetable index to
  ParseNamespaceItem, and use that to get rid of RTERangeTablePosn searches.  (I
  have in mind to teach the parser to generate some different representation for
  Vars that are nullable by outer joins, and keeping the necessary information
  in ParseNamespaceItems seems like a reasonable approach to that.  But whether
  that ever happens or not, this seems like good cleanup.)  Also refactor the
  code around scanRTEForColumn so that the "fuzzy match" stuff does not leak out
  of parse_relation.c.  Discussion:
  https://postgr.es/m/26144.1576858373@sss.pgh.pa.us
  https://git.postgresql.org/pg/commitdiff/b541e9accb28c90656388a3f827ca3a68dd2a308

- Improve comments in utils/rel.h. Mark the fields that should be accessed via
  partitioning-related functions, as we already did for some other fields.  Amit
  Langote  Discussion:
  https://postgr.es/m/CA+HiwqFnK6LbVMACMCaqwWrvoSFTecZzufKRahg2qGvLPYMX=g@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/fbe0232358c14792e1b94b902d0f7a9b55154275

- Fix possible loss of sync between rectypeid and underlying PLpgSQL_type. When
  revalidate_rectypeid() acts to update a stale record type OID in plpgsql's
  data structures, it fixes the active PLpgSQL_rec struct as well as the
  PLpgSQL_type struct it references.  However, the latter is shared across
  function executions while the former is not.  In a later function execution,
  the PLpgSQL_rec struct would be reinitialized by copy_plpgsql_datums and would
  then contain a stale type OID, typically leading to "could not open relation
  with OID NNNN" errors. revalidate_rectypeid() can easily fix this,
  fortunately, just by treating typ->typoid as authoritative.  Per report and
  diagnosis from Ashutosh Sharma, though this is not his suggested fix.
  Back-patch to v11 where this code came in.  Discussion:
  https://postgr.es/m/CAE9k0Pkd4dZwt9J5pS9xhJFWpUtqs05C9xk_GEwPzYdV=GxwWg@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/4ba4bfaf256c17fe4c8f497924b74eb98241b84f

- Micro-optimize AllocSetFreeIndex() by reference to pg_bitutils code. Use
  __builtin_clz() where available.  Where it isn't, we can still win a little by
  using the pg_leftmost_one_pos[] lookup table instead of having a private
  table.  Also drop the initial right shift by ALLOC_MINBITS in favor of
  subtracting ALLOC_MINBITS from the leftmost-one-pos result.  This is a win
  because the compiler can fold that adjustment into other constants it'd have
  to add anyway, making the shift-removal free.  Also, we can explain this
  coding as an unrolled form of pg_leftmost_one_pos32(), even though that's a
  bit ahistorical since it long predates pg_bitutils.h.  John Naylor, with some
  cosmetic adjustments by me  Discussion:
  https://postgr.es/m/CACPNZCuNUGMxjK7WTn_=WZnRbfASDdBxmjsVf2+m9MdmeNw_sg@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/0ce38730ac72029f3f2c95ae80b44f5b9060cbcc

Joe Conway pushed:

- Disallow null category in crosstab_hash. While building a hash map of
  categories in load_categories_hash, resulting category names have not thus far
  been checked to ensure they are not null. Prior to pg12 null category names
  worked to the extent that they did not crash on some platforms. This is
  because those system libraries have an snprintf which can deal with being
  passed a null pointer argument for a string. But even in those cases null
  categories did nothing useful. And on some platforms it crashed. As of pg12,
  our own version of snprintf gets called, and it does not deal with null
  pointer arguments at all, and crashes consistently.  Fix that by disallowing
  null categories. They never worked usefully, and no one has ever asked for
  them to work previously. Back-patch to all supported branches.  Reported-By:
  Ireneusz Pluta Discussion:
  https://postgr.es/m/16176-7489719b05e4303c@postgresql.org
  https://git.postgresql.org/pg/commitdiff/d5b9c2baff662aac22cd2a497d5bcd3b5a916fd0

Thomas Munro pushed:

- Rotate instead of shifting hash join batch number. Our algorithm for choosing
  batch numbers turned out not to work effectively for multi-billion key inner
  relations.  We would use more hash bits than we have, and effectively
  concentrate all tuples into a smaller number of batches than we intended.
  While ideally we should switch to wider hashes, for now, change the algorithm
  to one that effectively gives up bits from the bucket number when we don't
  have enough bits.  That means we'll finish up with longer bucket chains than
  would be ideal, but that's better than having batches that don't fit in
  work_mem and can't be divided.  Batch-patch to all supported releases.
  Author: Thomas Munro Reviewed-by: Tom Lane, thanks also to Tomas Vondra,
  Alvaro Herrera, Andres Freund for testing and discussion Reported-by: James
  Coleman Discussion:
  https://postgr.es/m/16104-dc11ed911f1ab9df%40postgresql.org
  https://git.postgresql.org/pg/commitdiff/e69d644547785cc9f079650d29118a3688bc5039

Michaël Paquier pushed:

- Replace use of strerror() with %s by %m in pg_waldump. Since d6c55de1,
  src/port/snprintf.c is able to use %m instead of strerror().  A couple of
  utilities in src/bin/ have already done the switch, and do it now for
  pg_waldump as this reduces the workload for translators.  Note that more could
  be done, particularly with pgbench.  Thanks to Kyotaro Horiguchi for the
  discussion.  Discussion: https://postgr.es/m/20191129065115.GM2505@paquier.xyz
  https://git.postgresql.org/pg/commitdiff/cce64a51cabc1e59d202d95bb0b92ed22bac73cf

- Rename files and headers related to index AM. The following renaming is done
  so as source files related to index access methods are more consistent with
  table access methods (the original names used for index AMs ware too generic,
  and could be confused as including features related to table AMs): - amapi.h
  -> indexam.h. - amapi.c -> indexamapi.c.  Here we have an equivalent with
  backend/access/table/tableamapi.c. - amvalidate.c -> indexamvalidate.c. -
  amvalidate.h -> indexamvalidate.h. - genam.c -> indexgenam.c. - genam.h ->
  indexgenam.h.  This has been discussed during the development of v12 when
  table AM was worked on, but the renaming never happened.  Author: Michael
  Paquier Reviewed-by: Fabien Coelho, Julien Rouhaud Discussion:
  https://postgr.es/m/20191223053434.GF34339@paquier.xyz
  https://git.postgresql.org/pg/commitdiff/8ce3aa9b5914d1ac45ed3f9bc484f66b3c4850c7

- Refactor code dedicated to index vacuuming in vacuumlazy.c. The part in charge
  of doing the vacuum on all the indexes of a relation was duplicated, with the
  same handling for progress reporting done. While on it, update the progress
  reporting for heap vacuuming in the subroutine doing the actual work, keeping
  the status update local.  This way, any future caller of lazy_vacuum_heap()
  does not have to worry about doing any progress reporting update.  Author:
  Justin Pryzby, Michael Paquier Discussion:
  https://postgr.es/m/20191120210600.GC30362@telsasoft.com
  https://git.postgresql.org/pg/commitdiff/1ab41a3c8edcf5d7751e61d6ab83bf43b494668b

- Fix some comments related to logical repslot advancing. confirmed_flush is
  part of a replication slot's information, but not confirmed_lsn.  Author:
  Kyotaro Horiguchi Discussion:
  https://postgr.es/m/20191226.175919.17237335658671970.horikyota.ntt@gmail.com
  Backpatch-through: 11
  https://git.postgresql.org/pg/commitdiff/044b319cd77c589507291f9591994093ad30931d

- Revert "Rename files and headers related to index AM". This follows multiple
  complains from Peter Geoghegan, Andres Freund and Alvaro Herrera that this
  issue ought to be dug more before actually happening, if it happens.
  Discussion: https://postgr.es/m/20191226144606.GA5659@alvherre.pgsql
  https://git.postgresql.org/pg/commitdiff/7854e07f25be3a3dfa9c94011a30767eca3c10ba

- Forbid DROP SCHEMA on temporary namespaces. This operation was possible for
  the owner of the schema or a superuser. Down to 9.4, doing this operation
  would cause inconsistencies in a session whose temporary schema was dropped,
  particularly if trying to create new temporary objects after the drop.  A more
  annoying consequence is a crash of autovacuum on an assertion failure when
  logging information about an orphaned temp table dropped.  Note that because
  of 246a6c8 (present in v11~), which has made the removal of orphaned temporary
  tables more aggressive, the failure could be triggered more easily, but it is
  possible to reproduce down to 9.4.  Reported-by: Mahendra Singh, Prabhat Sahu
  Author: Michael Paquier Reviewed-by: Kyotaro Horiguchi, Mahendra Singh
  Discussion:
  https://postgr.es/m/CAKYtNAr9Zq=1-ww4etHo-VCC-k120YxZy5OS01VkaLPaDbv2tg@mail.gmail.com
  Backpatch-through: 9.4
  https://git.postgresql.org/pg/commitdiff/a052f6cbb84e5630d50b68586cecc127e64be639

Fujii Masao pushed:

- Add note about how each partition's default value is treated, into the doc.
  Column defaults may be specified separately for each partition. But INSERT via
  a partitioned table ignores those partition's default values. The former is
  documented, but the latter restriction not. This commit adds the note about
  that restriction into the document.  Author: Fujii Masao Reviewed-by: Amit
  Langote Discussion:
  https://postgr.es/m/CAHGQGwEs-59omrfGF7hOHz9iMME3RbKy5ny+iftDx3LHTEn9sA@mail.gmail.com
  https://git.postgresql.org/pg/commitdiff/1ab029d528dcb409fb371d5ad826a67ec78b2b4e

Bruce Momjian pushed:

- docs:  clarify infinite range values from data-type infinities. The previous
  docs referenced these distinct ideas confusingly.  Reported-by: Eugen Konkov
  Discussion: https://postgr.es/m/376945611.20191026161529@yandex.ru
  Backpatch-through: 9.4
  https://git.postgresql.org/pg/commitdiff/650692a18d29c44c8019545c02ba42c3efd2c964

- doc:  add examples of creative use of unique expression indexes. Unique
  expression indexes can constrain data in creative ways, so show two examples.
  Reported-by: Tuomas Leikola  Discussion:
  https://postgr.es/m/156760275564.1127.12321702656456074572@wrigleys.postgresql.org
  Backpatch-through: 9.4
  https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1

== Pending Patches ==

John Naylor sent in a patch to reduce the size of the fmgr_builtins array.

Amit Kapila and Masahiko Sawada traded patches to implement block-level parallel
VACUUM.

Thomas Munro sent in another revision of a patch to not reset latch in
ConditionVariablePrepareToSleep().

Konstantin Knizhnik sent in another revision of a patch to implicitly add
multicolumn statistics to the auto_explain extension and use it in optimizer for
more precise estimation of join selectivity.

Suraj Kharage sent in another revision of a patch to implement backup manifests.

Noah Misch and Kyotaro HORIGUCHI traded patches to rework the WAL-skipping
optimization.

Kyotaro HORIGUCHI sent in another revision of a patch to add a WAL relief vent
for replication slots.

Anastasia Lubennikova sent in another revision of a patch to include the concept
of bitwise equality in opclasses.

Justin Pryzby and Michaël Paquier traded patches to rename buf to avoid
shadowing buf of another type.

Fabien COELHO sent in a patch to pgbench to make it use pg logging capabilities.

Aleksey Kondratov and Kyotaro HORIGUCHI traded patches to Make physical replslot
advance persistent.

Fujii Masao sent in a patch to add pg_file_sync() to adminpack.

Pavel Stěhule sent in two more revisions of a patch to implement schema
variables.

Nikita Glukhov sent in another revision of a patch to avoid full GIN index scans
where possible.

Julien Rouhaud sent in three revisions of a patch to expose the lock group
leader pid in pg_stat_activity.

Amit Khandekar sent in another revision of a patch to implement minimal logical
decoding on standbys.

Kyotaro HORIGUCHI sent in a patch to fix confirmed flush in comments.

Anastasia Lubennikova sent in another revision of a patch to support node
initialization from backup with tablespaces, add tests to replay create database
operation on standby, and fix replay of create database records on standby.

Tom Lane sent in another revision of a patch to rearrange ALTER TABLE to avoid
multi-operations bugs.

Peter Eisentraut sent in another revision of a patch to add support for
automatically updating Unicode derived files.

Justin Pryzby sent in another revision of a patch to add planner support
functions for GROUP BY f(), and pass ndistinct and minmax to allow good
estimates even with timestamps of granularity other than 1sec.

Takuma Hoshiai sent in another revision of a patch to implement incremental view
maintenance.

Maksim Milyutin sent in another revision of a patch to disallow cancellation of
synchronous commit.

Pierre Ducroquet sent in two revisions of a patch to fix a performance issue
with multiple logical-decoding walsenders.

Yuzuko Hosoya sent in another revision of a patch to enable setting autovacuum
on partitioned tables.

KaiGai Kohei sent in another revision of a patch to fix a crash with asymmetric
partition-wise JOIN.

Vigneshwaran C sent in another revision of a patch to fix a reorder buffer crash
while aborting old transactions.

Peter Eisentraut sent in a patch to remove libpq.rc, instead using win32ver.rc
for libpq.

Teodor Sigaev sent in a patch to fix a crash caused by some aggregation
scenarios.

Peter Eisentraut sent in a patch to implement a new auth designation: localowner.

Tom Lane sent in two more revisions of a patch to fix a bug wherein
tab-completion of filenames in COPY commands removed required quotes.

Álvaro Herrera sent in a patch to change xact.c to avoid updating transaction
start timestamps for walsenders.

Justin Pryzby sent in a patch to allow ALTER INDEX SET () on partitioned
indexes.

Jeff Davis sent in another revision of a patch to implement memory-bounded hash
aggregation.

David Fetter sent in two revisions of a patch to allow WHEN in INSTEAD OF
triggers.

Peter Eisentraut sent in another revision of a patch to implement ALTER TABLE
... ALTER COLUMN ... DROP EXPRESSION...

Abdul Yadi sent in a patch to add a private_modify option to tables wherein even
superuser can not insert/update/delete the table outside SQL or SPI-based
function where complex data validation takes place.

Justin Pryzby sent in two more revisions of a patch to enable pg_ls_tmpdir to
show directories.

Tomáš Vondra sent in another revision of a patch to fix an infelicity between
logical_work_mem and logical streaming of large in-progress transactions.

Vik Fearing sent in a patch to recognize superuser in pg_hba.conf.

Vik Fearing sent in two revisions of a patch to add a gcd (greatest common
divisor) function.

Tom Lane sent in a patch to add TAP testing for psql's tab completion code.

Noah Misch sent in a patch to deal with some Valgrind complaints in qunique.h.

Peter Eisentraut sent in a patch to replace STATUS_FOUND with a boolean and
replace STATUS_WAITING with a eparate enum for use in the locking APIs.