Vous pouvez bénéficier de 50% de remise pour le SCALE 15x avec le code PGDAY : https://www.socallinuxexpo.org/scale/15x

[ndt: Meetup à Lyon le 21 février :https://www.meetup.com/fr-FR/PostgreSQL-Lyon-User-Group/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en janvier

PostgreSQL Local

PostgreSQL dans les média

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/20170129225258.GC17445@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:

Tom Lane pushed:

  • Volatile-ize some plperl variables that must survive into PG_CATCH blocks. This appears to be necessary to fix a failure seen on buildfarm member sittella. It shouldn't be necessary according to the letter of the C standard, because we don't change the values of these variables within the PG_TRY blocks; but somehow gcc 4.7.2 is dropping the ball. Discussion: https://postgr.es/m/17555.1485179975@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/c0ef456b9714215cae0ce3561c7c83629a7301cf
  • Fix example plan in optimizer/README. Joining three tables only takes two join nodes. I think when I (tgl) wrote this, I was envisioning possible additional joins; but since the example doesn't show any fourth table, it's just confusing to write a third join node. Etsuro Fujita Discussion: https://postgr.es/m/e6cfbaa3-af02-1abc-c25e-8fa5c6bc4e21@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/3c821466abcdb8120ab0dfbce02f3bbae3b62025
  • Allow password file name to be specified as a libpq connection parameter. Formerly an alternate password file could only be selected via the environment variable PGPASSFILE; now it can also be selected via a new connection parameter "passfile", corresponding to the conventions for most other connection parameters. There was some concern about this creating a security weakness, but it was agreed that that argument was pretty thin, and there are clear use-cases for handling password files this way. Julian Markwort, reviewed by Fabien Coelho, some adjustments by me Discussion: https://postgr.es/m/a4b4f4f1-7b58-a0e8-5268-5f7db8e8ccaa@uni-muenster.de http://git.postgresql.org/pg/commitdiff/ba005f193d88a8404e81db3df223cf689d64d75e
  • Use non-conflicting table names in new regression test case. Commit 587cda35c added a test to updatable_views.sql that created tables named the same as tables used by the concurrent inherit.sql script. Unsurprisingly, this results in random failures. Pick different names. Per buildfarm. http://git.postgresql.org/pg/commitdiff/7fa7bf18e493e130147e62cf7dc33010f164126c
  • Improve speed of contrib/postgres_fdw regression tests. Commit 7012b132d added some tests that consumed an excessive amount of time, more than tripling the time needed for "make installcheck" for this module. Add filter conditions to reduce the number of rows scanned, bringing the runtime down to within hailing distance of what it was before. Jeevan Chalke and Ashutosh Bapat, per a gripe from me Discussion: https://postgr.es/m/16565.1478104765@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/aa7f593b1ffa9717bd5570174944c06c482d1c1f
  • Change unknown-type literals to type text in SELECT and RETURNING lists. Previously, we left such literals alone if the query or subquery had no properties forcing a type decision to be made (such as an ORDER BY or DISTINCT clause using that output column). This meant that "unknown" could be an exposed output column type, which has never been a great idea because it could result in strange failures later on. For example, an outer query that tried to do any operations on an unknown-type subquery output would generally fail with some weird error like "failed to find conversion function from unknown to text" or "could not determine which collation to use for string comparison". Also, if the case occurred in a CREATE VIEW's query then the view would have an unknown-type column, causing similar failures in queries trying to use the view. To fix, at the tail end of parse analysis of a query, forcibly convert any remaining "unknown" literals in its SELECT or RETURNING list to type text. However, provide a switch to suppress that, and use it in the cases of SELECT inside a set operation or INSERT command. In those cases we already had type resolution rules that make use of context information from outside the subquery proper, and we don't want to change that behavior. Also, change creation of an unknown-type column in a relation from a warning to a hard error. The error should be unreachable now in CREATE VIEW or CREATE MATVIEW, but it's still possible to explicitly say "unknown" in CREATE TABLE or CREATE (composite) TYPE. We want to forbid that because it's nothing but a foot-gun. This change creates a pg_upgrade failure case: a matview that contains an unknown-type column can't be pg_upgraded, because reparsing the matview's defining query will now decide that the column is of type text, which doesn't match the cstring-like storage that the old materialized column would actually have. Add a checking pass to detect that. While at it, we can detect tables or composite types that would fail, essentially for free. Those would fail safely anyway later on, but we might as well fail earlier. This patch is by me, but it owes something to previous investigations by Rahila Syed. Also thanks to Ashutosh Bapat and Michael Paquier for review. Discussion: https://postgr.es/m/CAH2L28uwwbL9HUM-WR=hromW1Cvamkn7O-g8fPY2m=_7muJ0oA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/1e7c4bb0049732ece651d993d03bb6772e5d281a
  • Make UNKNOWN into an actual pseudo-type. Previously, type "unknown" was labeled as a base type in pg_type, which perhaps had some sense to it because you were allowed to create tables with unknown-type columns. But now that we don't allow that, it makes more sense to label it a pseudo-type. This has the additional effects of forbidding use of "unknown" as a domain base type, cast source or target type, PL function argument or result type, or plpgsql local variable type; all of which seem like good holes to plug. Discussion: https://postgr.es/m/CAH2L28uwwbL9HUM-WR=hromW1Cvamkn7O-g8fPY2m=_7muJ0oA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/d8d32d9a56a3cecfb14e8f47ebd50b780edffe60
  • Remove vestigial resolveUnknown arguments from transformSortClause etc. There's really no situation where we don't want these unknown-to-text conversions to happen. The alternative is failure anyway, and the one caller that was passing "false" did so only because it expected the case could not arise. Might as well simplify the code. Discussion: https://postgr.es/m/CAH2L28uwwbL9HUM-WR=hromW1Cvamkn7O-g8fPY2m=_7muJ0oA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/aebeb4790c750dc808c1c5afb3cb435116244e36
  • Introduce convenience macros to hide JsonbContainer header accesses better. This improves readability a bit and may make future improvements easier. In passing, make sure that the JB_ROOT_IS_XXX macros deliver boolean (0/1) results; the previous coding was a bug hazard, though no actual bugs are known. Nikita Glukhov, extended a bit by me Discussion: https://postgr.es/m/9e21a39c-c1d7-b9b5-44a0-c5345a5029f6@postgrespro.ru http://git.postgresql.org/pg/commitdiff/f7c62462402972b13d10e43f104ca0c0fecb6d08
  • Ensure that a tsquery like '!foo' matches empty tsvectors. !foo means "the tsvector does not contain foo", and therefore it should match an empty tsvector. ts_match_vq() overenthusiastically supposed that an empty tsvector could never match any query, so it forcibly returned FALSE, the wrong answer. Remove the premature optimization. Our behavior on this point was inconsistent, because while seqscans and GIST index searches both failed to match empty tsvectors, GIN index searches would find them, since GIN scans don't rely on ts_match_vq(). That makes this certainly a bug, not a debatable definition disagreement, so back-patch to all supported branches. Report and diagnosis by Tom Dunstan (bug #14515); added test cases by me. Discussion: https://postgr.es/m/20170126025524.1434.97828@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/9d4ca01314ba0c571139c5560a40ef764ad0feee
  • Use castNode() in a bunch of statement-list-related code. When I wrote commit ab1f0c822, I really missed the castNode() macro that Peter E. had proposed shortly before. This back-fills the uses I would have put it to. It's probably not all that significant, but there are more assertions here than there were before, and conceivably they will help catch any bugs associated with those representation changes. I left behind a number of usages like "(Query *) copyObject(query_var)". Those could have been converted as well, but Peter has proposed another notational improvement that would handle copyObject cases automatically, so I let that be for now. http://git.postgresql.org/pg/commitdiff/7afd56c3c6d8360a5bfdfb2de30038b239fd756b
  • Orthography fixes for new castNode() macro. Clean up hastily-composed comment. Normalize whitespace. Erik Rijkers and myself http://git.postgresql.org/pg/commitdiff/fefb86b14776321ac153836398eadde867ff31af
  • Improve comments about ProcessUtility's queryString parameter. Per discussion with Craig Ringer. http://git.postgresql.org/pg/commitdiff/fde5c037925b01b937923606c39460d94965672e
  • Restructure hba.c to replace 3 parallel lists with single list of structs. tokenize_file() now returns a single list of TokenizedLine structs, carrying the same information as before. We were otherwise going to grow a fourth list to deal with error messages, and that was getting a bit silly. Haribabu Kommi, revised a bit by me Discussion: https://postgr.es/m/CAJrrPGfbgbKsjYp=bgZXhMcgxoaGSoBb9fyjrDoOW_YymXv1Kw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/350cb921ae2ced8674e098d0645f2493e5757ad1

Ãlvaro Herrera pushed:

Tatsuo Ishii pushed:

Fujii Masao pushed:

Robert Haas pushed:

Simon Riggs pushed:

Andres Freund pushed:

Correctifs en attente

Michaël Paquier sent in a patch to enable replication connections by default in pg_hba.conf.

Michaël Paquier sent in another revision of a patch to ensure that pg_dump and pg_dumpall sync their output to disk by default.

Michaël Paquier sent in a patch to ensure that launching replication workers is a NOOP when wal_level < logical, and to set the default number of such workers to 0.

Craig Ringer sent in three more revisions of a patch to fix a race between clog truncation and lookup, and introduce txid_status(bigint) to get status of an xact.

Petr Jelínek sent in two more revisions of a patch to use the asynchronous connect API in libpqwalreceiver, close the replication connection when slot creation gets canceled, ensure that stringinfo buffers in walsender are always initialized, fix after trigger execution in logical replication, and add RENAME support for PUBLICATIONs and SUBSCRIPTIONs.

Amit Kapila sent in another revision of a patch to implement parallel index scans.

Dilip Kumar sent in three more revisions of a patch to implement parallel bitmap heap scans.

Beena Emerson sent in another revision of a patch to enable increasing the default WAL segment size.

Corey Huinker sent in three more revisions of a patch to add \if and friends to psql.

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

Ivan Kartyshov sent in another revision of a patch to make async slave to wait for lsn to be replayed.

Dagfinn Ilmari Mannsåker sent in another revision of a patch to add GUCs for predicate lock promotion thresholds.

Craig Ringer sent in another revision of a patch to implement logical decoding on standby.

Nico Williams sent in a patch to implement an expanded version of materialized views and a contrib extension.

Nico Williams sent in a patch to implement pqasyncnotifier.c, a shell command client for LISTEN.

Amit Langote sent in a patch to add relkind checks to certain contrib modules.

Kyotaro HORIGUCHI sent in another revision of a patch to clean up the negative cache of pg_statistic when dropping a relation and of pg_class when dropping a schema.

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

Ashutosh Sharma sent in two more revisions of a patch to add pgstathashindex() to pgstattuple extension.

Jim Nasby sent in another revision of a patch to add faster methods for getting SPI results.

Claudio Freire sent in two more revisions of a patch to allow usage of more than 1GB of work mem in VACUUM.

Pavel Stěhule sent in two revisions of a patch to enable forcing a custom or generic plan in PL/pgsql.

Stas Kelvich and Nikhil Sontakke traded patches to speed up two-phase transactions.

Haribabu Kommi sent in three more revisions of a patch to implement a 64-bit (EUI-64) macaddr data type.

Haribabu Kommi and Tom Lane traded patches to implement a pg_hba_file_settings view.

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

Beena Emerson sent in a patch to add tab completion to ALTER in psql.

Daniel Vérité sent in another revision of a patch to improve psql hooks for variables.

Nikita Glukhov sent in another revision of a patch to make a recursive version of json_populate_record().

Ãlvaro Herrera sent in another revision of a patch to implement xmltable().

Fabien COELHO sent in two more revisions of a patch to add more functions and operators to pgbench.

Pavan Deolasee sent in another revision of a patch to implement WARM.

Michaël Paquier sent in three revisions of a patch to refactor the replication commands output.

Vladimir Rusinov sent in a patch to rename the sql-callable functions with xlog to have wal instead.

Etsuro Fujita sent in another revision of a patch to push down more UPDATEs/DELETEs in postgres_fdw.

Ashutosh Bapat sent in another revision of a patch to speed up aggregate pushdown tests.

Julian Markwort sent in a patch to extended the functionality of pg_stat_statements so it can track worst and best case execution plans.

Fabien COELHO sent in a patch to fix an infelicity between pgbench's --connect and --rate options.

Kyotaro HORIGUCHI sent in another revision of a patch to use a radix tree for character conversion.

Peter Eisentraut sent in two more revisions of a patch to add test coverage for sequences.

David Fetter and Corey Huinker traded patches to add copy_srf(), a set-returning function corresponding to COPY IN.

Masahiko Sawada sent in two more revisions of a patch to support 2PC in FDWs.

Michaël Paquier sent in two revisions of a patch to remove race conditions between the checkpointer and the init fork creations by making index init forks go through the shared buffers instead of having their empty() routines handle the flush of the page created.

Robert Haas sent in a patch to remove some hard-coded superuser checks.

Robert Haas sent in another revision of a patch to rename things *xlog* to the corresponding *wal*.

Mithun Cy sent in two more revisions of a patch to cache hash index meta pages.

David Rowley sent in three more revisions of a patch to improve performance where for joins where the outer side is unique.

Venkata B Nagothi sent in a patch to generate an error by aborting the recovery process instead of starting up the cluster if the intended recovery target point is not reached, and give an option to DBA to resume the recovery process from exactly where it stopped.

Ashutosh Sharma sent in another revision of a patch to add microvacuum support for hash indexes.

Noah Misch sent in another revision of a patch to remove link-time cross-module refs in contrib.

Christoph Berg sent in a patch to use \G to use expanded output for a query or current query buffer.

Robert Haas and Ãlvaro Herrera traded patches to add hash index support to to the pageinspect contrib extension.

Thomas Munro sent in another revision of a patch to implement parallel shared hash.

David Rowley sent in another revision of a patch to fix an infelicity between CONNECTION LIMIT and Parallel Query.

Tom Lane sent in a patch to create a separate test file for exercising system views.