Les nouveautés des produits dérivés

• pg_back 1.4, un script de sauvegarde pour PostgreSQL : https://github.com/orgrim/pg_back
• PostGIS versions 2.4.3 et 2.3.6, le système d'information géographique de référence pour PostgreSQL : http://postgis.net/2018/01/17/postgis-patches/
• OmniDB 2.4.1, un outil de gestion de BDD via navigateur web : https://www.2ndquadrant.com/en/resources/omnidb/
• repmgr 4.0.2, un gestionnaire de réplication pour PostgreSQL : https://repmgr.org/docs/4.0/release-4.0.2.html
• Database Designer pour PostgreSQL 1.12.2 : http://microolap.com/products/database/postgresql-designer/news/

Offres d'emplois autour de PostgreSQL en janvier

• Internationales : http://archives.postgresql.org/pgsql-jobs/2018-01/
• Francophones : http://forums.postgresql.fr/viewforum.php?id=4

PostgreSQL Local

• FOSDEM PGDay 2018, une conférence d'une journée tenue avant l'événement principal, sera tenue à Bruxelles (Belgique) le 2 février 2018 : https://2018.fosdempgday.org/
• Prague PostgreSQL Developer Day 2018 (P2D2 2018) est une série de conférences sur deux jours qui aura lieu les 14 & 15 février 2018 à Prague (République Tchèque) : http://www.p2d2.cz/
• La PGConf India 2018 aura lieu les 22 & 23 février 2018 à Bengalore (État du Karnataka en Inde) : http://pgconf.in/
• [ndt: MeetUp à Lyon le 28 février : https://www.meetup.com/fr-FR/PostgreSQL-Lyon-User-Group/]
• PostgreSQL@SCaLE est un événement de 2 jours à double programmes qui aura lieu les 8 & 9 mars 2018 au centre de convention de Pasadena, intégré au SCaLE 16X : http://www.socallinuxexpo.org/scale/16x/cfp
• Le PGDay nordique 2018 se tiendra à Oslo (Norvège) à l'hôtel Radisson Blu le 13 mars 2018. L'appel à conférenciers s'éteint le 31 décembre 2017 : https://2018.nordicpgday.org/cfp/
• Le pgDay Paris 2018 aura lieu à l'espace Saint Martin (Paris, France) le 15 mars 2018. L'appel à conférenciers court jusqu'au 31 décembre 2017 : http://2018.pgday.paris/callforpapers/
• PGConf APAC 2018 se tiendra à Singapour du 22 au 24 mars 2018 : http://2018.pgconfapac.org/
• La conférence germanophone PostgreSQL Conference 2018 aura lieu le 13 avril 2018 à Berlin : http://2018.pgconf.de/
• La PGConfNepal 2018 se tiendra les 4 & 5 mai 2018 à l'université de Katmandou, Dulikhel, Népal. L'appel à conférenciers court jusqu'au 1er février 2018 : https://postgresconf.org/conferences/Nepal2018/program/proposals https://postgresconf.org/conferences/Nepal2018
• La PGCon 2018 se tiendra à Ottawa du 29 mai au 1er juin 2018 : https://www.pgcon.org/2018/
• La PGConf.Brazil 2018 aura lieu à São Paulo (Brésil) les 3 & 4 août 2018. L'appel à conférenciers sera lancé prochainement : http://pgconf.com.br

PostgreSQL dans les média

• Planet PostgreSQL : http://planet.postgresql.org/
• Planet PostgreSQLFr : http://planete.postgresql.fr/

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20180121200607.GA4938@fetter.org

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

Correctifs appliqués

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.paquier@gmail.com> Reviewed-by: Stephen Frost <sfrost@snowman.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.paquier@gmail.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@sss.pgh.pa.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.paquier@gmail.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

Correctifs en attente

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.