== PostgreSQL Weekly News - January 21 2018 ==

== PostgreSQL Weekly News - January 21 2018 ==

== PostgreSQL Product News ==

pg_back 1.4, a backup script for PostgreSQL, released.
https://github.com/orgrim/pg_back

PostGIS 2.4.3 and 2.3.6, the industry standard geographic information
system package for PostgreSQL, released.
http://postgis.net/2018/01/17/postgis-patches/

OmniDB 2.4.1, a browser-based database management tool, released.
https://www.2ndquadrant.com/en/resources/omnidb/

repmgr 4.0.2, a replication manager for PostgreSQL, released.
https://repmgr.org/docs/4.0/release-4.0.2.html

Database Designer for PostgreSQL 1.12.2 released.
http://microolap.com/products/database/postgresql-designer/news/

== PostgreSQL Jobs for January ==

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

== PostgreSQL Local ==

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

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.
http://www.p2d2.cz/

PGConf India 2018 will be on February 22-23, 2018 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL(at)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.
http://www.socallinuxexpo.org/scale/16x/cfp

Nordic PGDay 2018 will be held in Oslo, Norway, at the Radisson Blu Hotel
Nydalen, on March 13, 2018. The CfP is open through December 31, 2017 at
https://2018.nordicpgday.org/cfp/

pgDay Paris 2018 will be held in Paris, France at the Espace Saint-Martin, on
March 15 2018. The CfP is open until December 31, 2017.
http://2018.pgday.paris/callforpapers/

PGConf APAC 2018 will be held in Singapore March 22-23, 2018.
http://2018.pgconfapac.org/

The German-speaking PostgreSQL Conference 2018 will take place on April 13th,
2018 in Berlin.
http://2018.pgconf.de/

PGConfNepal 2018 will be held May 4-5, 2018 at Kathmandu University, Dhulikhel,
Nepal. The CfP is open until February 1, 2018 at
https://postgresconf.org/conferences/Nepal2018/program/proposals
https://postgresconf.org/conferences/Nepal2018

PGCon 2018 will take place in Ottawa on May 29 - June 1, 2018.
https://www.pgcon.org/2018/

PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. The
CfP will open soon.
http://pgconf.com.br

== 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 david(at)fetter(dot)org, German language to pwn(at)pgug(dot)de, Italian
language to pwn(at)itpug(dot)org(dot)

== Applied Patches ==

Peter Eisentraut pushed:

- Remove useless use of bit-masking macros. In this case, the macros
SET_8_BYTES(), GET_8_BYTES(), SET_4_BYTES(), GET_4_BYTES() are no-ops, so we
can just remove them. The plan is to perhaps remove them from the source code
altogether, so we'll start here. Discussion:
https://www.postgresql.org/message-id/5d51721a-69ef-2053-9172-599b539f0628@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/d91da5ecedc8f8965bd35de66b09feb79c26e5ca

- doc: Expand documentation of session_replication_role.
https://git.postgresql.org/pg/commitdiff/a063d842f8f48e197f5a9bfb892210ce219c5556

- Add tests for session_replication_role. This was hardly tested at all. The
trigger case was lightly tested by the logical replication tests, but rules
and event triggers were not tested at all.
https://git.postgresql.org/pg/commitdiff/77216cae47e3ded13f36361f60ce04ec0a709e2a

- Fix typo and improve punctuation.
https://git.postgresql.org/pg/commitdiff/958c7ae0b7ca4ee9d422271c2ffbef4e3a6d1c47

- Update comment. The "callback" that this comment was referring to was removed
by commit c0a15e07cd718cb6e455e68328f522ac076a0e4b, so update to match the
current code.
https://git.postgresql.org/pg/commitdiff/a228e44ce4a2bfd1de3764763039cfcb009d7864

- Replace GrantObjectType with ObjectType. There used to be a lot of different
*Type and *Kind symbol groups to address objects within different commands,
most of which have been replaced by ObjectType, starting with
b256f2426433c56b4bea3a8102757749885b81ba. But this conversion was never done
for the ACL commands until now. This change ends up being just a plain
replacement of the types and symbols, without any code restructuring needed,
except deleting some now redundant code. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> Reviewed-by: Stephen Frost <sfrost(at)snowman(dot)net>
https://git.postgresql.org/pg/commitdiff/2c6f37ed62114bd5a092c20fe721bd11b3bcb91e

- Replace AclObjectKind with ObjectType. AclObjectKind was basically just
another enumeration for object types, and we already have a preferred one for
that. It's only used in aclcheck_error. By using ObjectType instead, we can
also give some more precise error messages, for example "index" instead of
"relation". Reviewed-by: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/8b9e9644dc6a9bd4b7a97950e6212f63880cf18b

- PL/Python: Simplify PLyLong_FromInt64. We don't actually need two code paths,
one for 32 bits and one for 64 bits. Since the existing code already assumed
that "long long" is available, we can just use PyLong_FromLongLong() for 64
bits as well. In Python 2.5 and later, PyLong_FromLong() and
PyLong_FromLongLong() use the same code, so there will be no difference for
64-bit platforms. In Python 2.4, the code is different, but performance
testing showed no noticeable difference in PL/Python, and that Python version
is ancient anyway. Discussion:
https://www.postgresql.org/message-id/0a02203c-e157-55b2-464e-6087066a1849@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/eee50a8d4c389171ad5180568a7221f7e9b28f09

- Improve type conversion of SPI_processed in Python. The previous code
converted SPI_processed to a Python float if it didn't fit into a Python int.
But Python longs have unlimited precision, so use that instead in all cases.
As in eee50a8d4c389171ad5180568a7221f7e9b28f09, we use the Python LongLong API
unconditionally for simplicity. Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
https://git.postgresql.org/pg/commitdiff/918e02a221db1ee40d545cb05dc9d8d392b4b743

Andrew Dunstan pushed:

- Fix compiler warnings due to commit cc4feded.
https://git.postgresql.org/pg/commitdiff/585e166e46a1572b59eb9fdaffc2d4b785000f9e

- Centralize json and jsonb handling of datetime types. The creates a single
function JsonEncodeDateTime which will format these data types in an efficient
and consistent manner. This will be all the more important when we come to
jsonpath so we don't have to implement yet more code doing the same thing in
two more places. This also extends the code to handle time and timetz types
which were not previously handled specially. This requires exposing the
time2tm and timetz2tm functions. Patch from Nikita Glukhov
https://git.postgresql.org/pg/commitdiff/cc4feded0a31d2b732d4ea68613115cb720e624e

Simon Riggs pushed:

- Ability to advance replication slots. Ability to advance both physical and
logical replication slots using a new user function
pg_replication_slot_advance(). For logical advance that means records are
consumed as fast as possible and changes are not given to output plugin for
sending. Makes 2nd phase (after we reached SNAPBUILD_FULL_SNAPSHOT) of
replication slot creation faster, especially when there are big transactions
as the reorder buffer does not have to deal with data changes and does not
have to spill to disk. Author: Petr Jelinek Reviewed-by: Simon Riggs
https://git.postgresql.org/pg/commitdiff/9c7d06d60680c7f00d931233873dee81fdb311c6

- Fix typo in recent commit. Typo in 9c7d06d60680c7f00d931233873dee81fdb311c6
Reported-by: Masahiko Sawada
https://git.postgresql.org/pg/commitdiff/4e54dd2e0a750352ce2a5c45d1cc9183e887eec3

Tom Lane pushed:

- Remove useless lookup of root partitioned rel in ExecInitModifyTable().
node->partitioned_rels is only set in UPDATE/DELETE cases, but
ExecInitModifyTable only uses its "rel" variable in INSERT cases, so the extra
logic to find the root rel is just a waste of complexity and cycles. Etsuro
Fujita, reviewed by Amit Langote Discussion:
https://postgr.es/m/93cf9816-2f7d-0f67-8ed2-4a4e497a6ab8@lab.ntt.co.jp
https://git.postgresql.org/pg/commitdiff/dca48d145e0e757f0549430ec48687d12c6b6751

- Extend configure's __int128 test to check for a known gcc bug. On Sparc64,
use of __attribute__(aligned(8)) with __int128 causes faulty code generation
in gcc versions at least through 5.5.0. We can work around that by disabling
use of __int128, so teach configure to test for the bug. This solution
doesn't fix things for the case of cross-compiling with a buggy compiler; to
support that nicely, we'd need to add a manual disable switch. Unless more
such cases turn up, it doesn't seem worth the work. Affected users could
always edit pg_config.h manually. In passing, fix some typos in the existing
configure test for __int128. They're harmless because we only compile that
code not run it, but they're still confusing for anyone looking at it closely.
This is needed in support of commit 751804998, so back-patch to 9.5 as that
was. Marina Polyakova, Victor Wagner, Tom Lane Discussion:
https://postgr.es/m/0d3a9fa264cebe1cb9966f37b7c06e86@postgrespro.ru
https://git.postgresql.org/pg/commitdiff/2082b3745a7165d10788d55c5b6c609a8d39d729

- Suppress possibly-uninitialized-variable warnings. Apparently, Peter's
compiler has faith that the switch test values here could never not be valid
values of their enums. Mine does not, and I tend to agree with it.
https://git.postgresql.org/pg/commitdiff/96102a32a374c3b81ba9c2b24bcf1943a87a9ef6

Robert Haas pushed:

- postgres_fdw: Avoid 'outer pathkeys do not match mergeclauses' error. When
pushing down a join to a foreign server, postgres_fdw constructs an
alternative plan to be used for any EvalPlanQual rechecks that prove to be
necessary. This plan is stored as the outer subplan of the Foreign Scan
implementing the pushed-down join. Previously, this alternative plan could
have a different nominal sort ordering than its parent, which seemed OK since
there will only be one tuple per base table anyway in the case of an
EvalPlanQual recheck. Actually, though, it caused a problem if that path was
used as a building block for the EvalPlanQual recheck plan of a higher-level
foreign join, because we could end up with a merge join one of whose inputs
was not labelled with the correct sort order. Repair by injecting an extra
Sort node into the EvalPlanQual recheck plan whenever it would otherwise fail
to be sorted at least as well as its parent Foreign Scan. Report by Jeff
Janes. Patch by me, reviewed by Tom Lane, who also provided the test case and
comment text. Discussion:
http://postgr.es/m/CAMkU=1y2G8VOVBHv3iXU2TMAj7-RyBFFW1uhkr5sm9LQ2=X35g@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4bbf6edfbd5d03743ff82dda2f00c738fb3208f5

- Transfer state pertaining to pending REINDEX operations to workers. This will
allow the pending patch for parallel CREATE INDEX to work on system catalogs,
and to provide the same level of protection against use of user indexes while
they are being rebuilt that we have for non-parallel CREATE INDEX. Patch by
me, reviewed by Peter Geoghegan. Discussion:
http://postgr.es/m/CA+TgmoYN-YQU9JsGQcqFLovZ-C+Xgp1_xhJQad=cunGG-_p5gg@mail.gmail.com
Discussion:
http://postgr.es/m/CAH2-Wzkv4UNkXYhqQRqk-u9rS7h5c-4cCW+EqQ8K_WSeS43aZg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/29d58fd3adae9057c3fd502393b2f131bc96eaf9

- Allow UPDATE to move rows between partitions. When an UPDATE causes a row to
no longer match the partition constraint, try to move it to a different
partition where it does match the partition constraint. In essence, the
UPDATE is split into a DELETE from the old partition and an INSERT into the
new one. This can lead to surprising behavior in concurrency scenarios
because EvalPlanQual rechecks won't work as they normally did; the known
problems are documented. (There is a pending patch to improve the situation
further, but it needs more review.) Amit Khandekar, reviewed and tested by
Amit Langote, David Rowley, Rajkumar Raghuwanshi, Dilip Kumar, Amul Sul,
Thomas Munro, Álvaro Herrera, Amit Kapila, and me. A few final revisions by
me. Discussion:
http://postgr.es/m/CAJ3gD9do9o2ccQ7j7+tSgiE1REY65XRiMb=yJO3u3QhyP8EEPQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2f178441044be430f6b4d626e4dae68a9a6f6cec

Bruce Momjian pushed:

- Reorder C includes. Reorder header files in joinrels.c and pathnode.c in
alphabetical order, removing unnecessary ones. Author: Etsuro Fujita
https://git.postgresql.org/pg/commitdiff/f033462d8f77c40b7d6b33c5116e50118fb4699d

- doc: update intermediate certificate instructions. Document how to properly
create root and intermediate certificates using v3_ca extensions and where to
place intermediate certificates so they are properly transferred to the remote
side with the leaf certificate to link to the remote root certificate. This
corrects docs that used to say that intermediate certificates must be stored
with the root certificate. Also add instructions on how to create root,
intermediate, and leaf certificates. Discussion:
https://postgr.es/m/20180116002238.GC12724@momjian.us Reviewed-by: Michael
Paquier Backpatch-through: 9.3
https://git.postgresql.org/pg/commitdiff/815f84aa166de294b80e80cc456b79128592720e

Álvaro Herrera pushed:

- Fix StoreCatalogInheritance1 to use 32bit inhseqno. For no apparent reason,
this function was using a 16bit-wide inhseqno value, rather than the correct
32 bit width which is what is stored in the pg_inherits catalog. This becomes
evident if you try to create a table with more than 65535 parents, because
this error appears: ERROR: duplicate key value violates unique constraint
«pg_inherits_relid_seqno_index» DETAIL: Key (inhrelid, inhseqno)=(329371, 0)
already exists. Needless to say, having so many parents is an uncommon
situations, which explains why this error has never been reported despite
being having been introduced with the Postgres95 1.01 sources in commit
d31084e9d111:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/creatinh.c;hb=d31084e9d111#l349
Backpatch all the way back. David Rowley noticed this while reviewing a patch
of mine. Discussion:
https://postgr.es/m/CAKJS1f8Dn7swSEhOWwzZzssW7747YB=2Hi+T7uGud40dur69-g@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/1ef61ddce9086c30a18a6ecc48bc3ce0ef62cb39

- Fix regression tests for better stability. Per buildfarm
https://git.postgresql.org/pg/commitdiff/189d0ff588f54b9641c6684d7c668ef85ea4dfbd

- Fix pg_dump version comparison. I missed a '0' in the version number string
... Per buildfarm member crake.
https://git.postgresql.org/pg/commitdiff/42b5856038a5af6bb4ec3c09b62d9d9a3ab43172

- Local partitioned indexes. When CREATE INDEX is run on a partitioned table,
create catalog entries for an index on the partitioned table (which is just a
placeholder since the table proper has no data of its own), and recurse to
create actual indexes on the existing partitions; create them in future
partitions also. As a convenience gadget, if the new index definition matches
some existing index in partitions, these are picked up and used instead of
creating new ones. Whichever way these indexes come about, they become
attached to the index on the parent table and are dropped alongside it, and
cannot be dropped on isolation unless they are detached first. To support
pg_dump'ing these indexes, add commands CREATE INDEX ON ONLY <table> (which
creates the index on the parent partitioned table, without recursing) and
ALTER INDEX ATTACH PARTITION (which is used after the indexes have been
created individually on each partition, to attach them to the parent index).
These reconstruct prior database state exactly. Reviewed-by: (in alphabetical
order) Peter Eisentraut, Robert Haas, Amit Langote, Jesper Pedersen, Simon
Riggs, David Rowley Discussion:
https://postgr.es/m/20171113170646.gzweigyrgg6pwsg4@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/8b08f7d4820fd7a8ef6152a9dd8c6e3cb01e5f99

- Fix CompareIndexInfo's attnum comparisons. When an index column is an
expression, it makes no sense to compare its attribute numbers. This seems to
account for remaining buildfarm fallout from 8b08f7d4820f. At least, it
solves the issue in my local 32bit VM -- let's see what the rest thinks.
https://git.postgresql.org/pg/commitdiff/7f17fd6fc7125b41218bc99ccfa8165e2d730cd9

Magnus Hagander pushed:

- Fix wording of "hostaddrs". The field is still called "hostaddr", so make
sure references use "hostaddr values" instead. Author: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/5c15a54e851ecdd2b53e6d6a84f8ec0802ffc3cb

- Support huge pages on Windows. Add support for huge pages (called large pages
on Windows) to the Windows build. This (probably) breaks compatibility with
Windows versions prior to Windows 2003 or Windows Vista. Authors: Takayuki
Tsunakawa and Thomas Munro Reviewed by: Magnus Hagander, Amit Kapila
https://git.postgresql.org/pg/commitdiff/1cc4f536ef86928a241126ca70d121873594630e

== Pending Patches ==

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

Masahiko Sawada sent in a patch to fix a README in nbtree to reflect current
realities.

Michaël Paquier sent in a patch to remove WITH clause support in CREATE FUNCTION
for isCachable and isStrict.

Fabien COELHO sent in another revision of a patch to add \if to pgbench.

Joe Wildish sent in another revision of a patch to implement SQL ASSERTIONs.

Haribabu Kommi sent in another revision of a patch to enhance
pg_stat_wal_receiver view to display connected host.

Robert Haas sent in another revision of a patch to make "quit" and "exit" Do The
Right Thing™ in psql.

Marco Nenciarini sent in two more revisions of a patch to implement logical
decoding of TRUNCATE.

Shubham Barai sent in another revision of a patch to predicate Locking in hash
index.

Marina Polyakova sent in another revision of a patch to add the infrastructure
for pre-calculating STABLE functions.

Alexander Korotkov sent in another revision of a patch to count tuples correctly
during GiST VACUUM of partial indexes.

Andrew Dunstan sent in another revision of a patch to speed up adding a column
with a DEFAULT to a table.

Amit Langote sent in a patch to ensure that partitioned tables do not have TOAST
tables created for them.

Geoff Winkless sent in a patch to psql to handle EOF correctly when readline is
enabled.

Ildar Musin sent in two more revisions of a patch to create general purpose
hashing functions in pgbench.

Peter Eisentraut sent in another revision of a patch to implement GENERATED
columns per the SQL standard.

Yuto Hayamizu sent in another revision of a patch to mitigate filter cost
overestimation.

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

Peter Eisentraut sent in another revision of a patch to implement transaction
control in procedures.

Yoshimi Ichiyanagi sent in a patch to apply PMDK to WAL operations for
persistent memory.

Peter Geoghegan sent in two more revisions of a patch to add parallel B-tree
index build sorting.

Andrey Borodin sent in two more revisions of a patch to implement a GiST vacuum.

Anastasia Lubennikova sent in two more revisions of a patch to implement
covering + unique indexes.

Claudio Freire sent in another revision of a patch to vacuum which makes it
update the FSM more frequently.

Claudio Freire sent in another revision of a patch to enable VACUUM to use more
than 1GB of work_mem.

Nikolay Shaplov sent in another revision of a patch to skip setting toast.*
reloptions when a TOAST table does not exist.

Konstantin Knizhnik sent in three revisions of a patch to implement built-in
connection pooling.

Robert Haas sent in another revision of a patch to sort epq path if needed.

Robert Haas sent in a patch to propagate REINDEX state.

David Gould sent in a patch to fix a bug where ANALYZE can cause
pg_class.reltuples to be wildly exaggerated.

Etsuro Fujita sent in another revision of a patch to fix WCO handling in the
PostgreSQL FDW.

Fabien COELHO sent in another revision of a patch to add a pgbench progress
test.

Jeevan Chalke sent in another revision of a patch to implement partition-wise
aggregation/grouping.

Antonin Houska sent in a patch to fix possible gaps/garbage in the output of
the XLOG reader.

Simon Riggs sent in another revision of a patch to implement MERGE.

Fabien COELHO sent in another revision of a patch to add --random-seed to
pgbench.

Kyotaro HORIGUCHI sent in two more revisions of a patch to fix an issue where an
index-only scan would return incorrect results when using a composite GIST index
with a gist_trgm_ops column.

Peter Eisentraut sent in a patch to create constraint triggers to fire ALWAYS,
which ensures that all constraints are also enforced on logical replicas.

Álvaro Herrera sent in a patch to add tests for pg_dump for local indexes on
partitoned tables.

Robert Haas sent in another revision of a patch to handle parallel worker fork
failures better.

Amit Langote sent in two revisions of a patch to add assorted partition
reporting functions.

Haribabu Kommi sent in another revision of a patch to refactor handling of
database attributes between pg_dump and pg_dumpall.

Marco Nenciarini sent in another revision of a patch to TRUNCATE to make it
ignore foreign keys in ROLE_REPLICA.

Liudmila Mantrova sent in another revision of a patch to fix some documentation
inconsistencies in pg_trgm word_similarity.

Jörg Westheide sent in a patch to make "make check" work on MacOS when SIP is
enabled.

Konstantin Knizhnik sent in another revision of a patch to optimize secondary
index access.

Amit Langote sent in another revision of a patch to teach CopyFrom to use
ModifyTableState for tuple-routing, refactor ExecFindPartition and
ExecSetupPartitionTupleRouting, and ensure that during tuple-routing,
per-partition objects are initialized lazily.

Tomas Vondra sent in two more revisions of a patch to add logical_work_mem and
use same to constrain memory usage for logical replication.

Peter Eisentraut sent in another revision of a patch to implement GNU TLS
support.

Etsuro Fujita sent in a patch to update some regression tests for the PostgreSQL
FDW.

David Steele sent in another revision of a patch to make a configurable file
mode mask for the PostgreSQL directories.

Jing Wang sent in another revision of a patch to support COMMENT ON DATABASE
CURRENT_DATABASE.

Tom Lane sent in a patch to pg_dump to make TOC tags reliably distinguishable
and dump comments in a more uniform order.

Tom Lane sent in another revision of a patch to refactor handling of database
attributes between pg_dump and pg_dumpall.