La PGConf India 2018 aura lieu les 22 & 23 février 2018 à Bengalore (État du Karnataka en Inde). Les propositions sont attendues via https://goo.gl/forms/F9hRjOIsaNasVOAz2 avant le 31 octobre 2017 : http://pgconf.in/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

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/20170918045902.GA4968@fetter.org

Submit news and announcements by Sunday at 3:00pm EST5EDT. Please send English language ones to david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org.

Correctifs appliqués

Peter Eisentraut pushed:

Tom Lane pushed:

  • Doc: update v10 release notes through today. Add item about number of times statement-level triggers will be fired. Rearrange the compatibility items into (what seems to me) a less random ordering. https://git.postgresql.org/pg/commitdiff/68ab9acd8557a9401a115a5369a14bf0a169e8e7
  • Allow rel_is_distinct_for() to look through RelabelType below OpExpr. This lets it do the right thing for, eg, varchar columns. Back-patch to 9.5 where this logic appeared. David Rowley, per report from Kim Rose Carlsen Discussion: https://postgr.es/m/VI1PR05MB17091F9A9876528055D6A827C76D0@VI1PR05MB1709.eurprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/6f44fe7f121ac7c29c1ac8553e4e209f9c3bfbcb
  • Fix possible dangling pointer dereference in trigger.c. AfterTriggerEndQuery correctly notes that the query_stack could get repalloc'd during a trigger firing, but it nonetheless passes the address of a query_stack entry to afterTriggerInvokeEvents, so that if such a repalloc occurs, afterTriggerInvokeEvents is already working with an obsolete dangling pointer while it scans the rest of the events. Oops. The only code at risk is its "delete_ok" cleanup code, so we can prevent unsafe behavior by passing delete_ok = false instead of true. However, that could have a significant performance penalty, because the point of passing delete_ok = true is to not have to re-scan possibly a large number of dead trigger events on the next time through the loop. There's more than one way to skin that cat, though. What we can do is delete all the "chunks" in the event list except the last one, since we know all events in them must be dead. Deleting the chunks is work we'd have had to do later in AfterTriggerEndQuery anyway, and it ends up saving rescanning of just about the same events we'd have gotten rid of with delete_ok = true. In v10 and HEAD, we also have to be careful to mop up any per-table after_trig_events pointers that would become dangling. This is slightly annoying, but I don't think that normal use-cases will traverse this code path often enough for it to be a performance problem. It's pretty hard to hit this in practice because of the unlikelihood of the query_stack getting resized at just the wrong time. Nonetheless, it's definitely a live bug of ancient standing, so back-patch to all supported branches. Discussion: https://postgr.es/m/2891.1505419542@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/27c6619e9c8ff80cd78c7f66443aa005734cda90
  • Prefer argument name over "$n" for the refname of a plpgsql argument. If a function argument has a name, use that as the "refname" of the PLpgSQL_datum representing the argument, instead of $n as before. This allows better error messages in some cases. Pavel Stehule, reviewed by Jeevan Chalke Discussion: https://postgr.es/m/CAFj8pRB9GyU2U1Sb2ssgP26DZ_yq-FYDfpvUvGQ=k4R=yOPVjg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b8060e41b5994a3cffb3ececaab10ed39b8d5dfd
  • Fix RecursiveCopy.pm to cope with disappearing files. When copying from an active database tree, it's possible for files to be deleted after we see them in a readdir() scan but before we can open them. (Once we've got a file open, we don't expect any further errors from it getting unlinked, though.) Tweak RecursiveCopy so it can cope with this case, so as to avoid irreproducible test failures. Back-patch to 9.6 where this code was added. In v10 and HEAD, also remove unused "use RecursiveCopy" in one recovery test script. Michael Paquier and Tom Lane Discussion: https://postgr.es/m/24621.1504924323@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e183530550dc1b73d24fb5ae7d84e85286e88ffb
  • Add psql variables to track success/failure of SQL queries. This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated after every query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE, which are updated only when a query fails. The expected usage of these is for scripting. Fabien Coelho, reviewed by Pavel Stehule Discussion: https://postgr.es/m/alpine.DEB.2.20.1704042158020.12290@lancre https://git.postgresql.org/pg/commitdiff/69835bc8988812c960f4ed5aeee86b62ac73602a
  • Distinguish selectivity of < from <= and > from >=. Historically, the selectivity functions have simply not distinguished < from <=, or > from >=, arguing that the fraction of the population that satisfies the "=" aspect can be considered to be vanishingly small, if the comparison value isn't any of the most-common-values for the variable. (If it is, the code path that executes the operator against each MCV will take care of things properly.) But that isn't really true unless we're dealing with a continuum of variable values, and in practice we seldom are. If "x = const" would estimate a nonzero number of rows for a given const value, then it follows that we ought to estimate different numbers of rows for "x < const" and "x <= const", even if the const is not one of the MCVs. Handling this more honestly makes a significant difference in edge cases, such as the estimate for a tight range (x BETWEEN y AND z where y and z are close together). Hence, split scalarltsel into scalarltsel/scalarlesel, and similarly split scalargtsel into scalargtsel/scalargesel. Adjust <= and >= operator definitions to reference the new selectivity functions. Improve the core ineq_histogram_selectivity() function to make a correction for equality. (Along the way, I learned quite a bit about exactly why that function gives good answers, which I tried to memorialize in improved comments.) The corresponding join selectivity functions were, and remain, just stubs. But I chose to split them similarly, to avoid confusion and to prevent the need for doing this exercise again if someone ever makes them less stubby. In passing, change ineq_histogram_selectivity's clamp for extreme probability estimates so that it varies depending on the histogram size, instead of being hardwired at 0.0001. With the default histogram size of 100 entries, you still get the old clamp value, but bigger histograms should allow us to put more faith in edge values. Tom Lane, reviewed by Aleksander Alekseev and Kuntal Ghosh Discussion: https://postgr.es/m/12232.1499140410@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7d08ce286cd5854d58152e428c28636a616bdc42
  • Update contrib/seg for new scalarlesel/scalargesel selectivity functions. I somehow missed this module in commit 7d08ce286. https://git.postgresql.org/pg/commitdiff/44ba2920644903d7dfceda810e5facdbcbab58a8
  • Adjust unstable regression test case. Test queries added by commit 69835bc89 are giving unexpected results on some smaller buildfarm critters. I think probably the seqscan logic is kicking in to cause the scans to not start at the beginning of the table. Add ORDER BY to make them be indexscans instead. Per buildfarm member chipmunk. https://git.postgresql.org/pg/commitdiff/76e134fefd7de0554536e1b8d45a1878f96cf9c0
  • Avoid duplicate typedef for SharedRecordTypmodRegistry. This isn't our usual solution for such problems, and older compilers (not terribly old, either) don't like it. Per buildfarm and local testing. https://git.postgresql.org/pg/commitdiff/fba366555659fc1dc66a825196be3cc68640d289
  • Don't use anonymous unions. Commit cc5f81366c36b3dd8f02bd9be1cf75b2cc8482bd introduced a language feature that is not acceptable to strict C89 compilers. Thomas Munro Per buildfarm. https://git.postgresql.org/pg/commitdiff/eaa4070543c2e36f0521f831d051265139875254
  • Get rid of shared_record_typmod_registry_worker_detach; it doesn't work. This code is unsafe, as proven by buildfarm failures, because it tries to access shared memory that might already be gone. It's also unnecessary, because we're about to exit the process anyway and so the record type cache should never be accessed again. The idea was to lay some foundations for someday recycling workers --- which would require attaching to a different shared tupdesc registry --- but that will require considerably more thought. In the meantime let's save some bytes by just removing the nonfunctional code. Problem identification, and proposal to fix by removing functionality from the detach function, by Thomas Munro. I went a bit further by removing the function altogether. Discussion: https://postgr.es/m/E1dsguX-00056N-9x@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/71aa4801a8184eb422c6bf51631bda76f1011278
  • Fix SQL-spec incompatibilities in new transition table feature. The standard says that all changes of the same kind (insert, update, or delete) caused in one table by a single SQL statement should be reported in a single transition table; and by that, they mean to include foreign key enforcement actions cascading from the statement's direct effects. It's also reasonable to conclude that if the standard had wCTEs, they would say that effects of wCTEs applying to the same table as each other or the outer statement should be merged into one transition table. We weren't doing it like that. Hence, arrange to merge tuples from multiple update actions into a single transition table as much as we can. There is a problem, which is that if the firing of FK enforcement triggers and after-row triggers with transition tables is interspersed, we might need to report more tuples after some triggers have already seen the transition table. It seems like a bad idea for the transition table to be mutable between trigger calls. There's no good way around this without a major redesign of the FK logic, so for now, resolve it by opening a new transition table each time this happens. Also, ensure that AFTER STATEMENT triggers fire just once per statement, or once per transition table when we're forced to make more than one. Previous versions of Postgres have allowed each FK enforcement query to cause an additional firing of the AFTER STATEMENT triggers for the referencing table, but that's certainly not per spec. (We're still doing multiple firings of BEFORE STATEMENT triggers, though; is that something worth changing?) Also, forbid using transition tables with column-specific UPDATE triggers. The spec requires such transition tables to show only the tuples for which the UPDATE trigger would have fired, which means maintaining multiple transition tables or else somehow filtering the contents at readout. Maybe someday we'll bother to support that option, but it looks like a lot of trouble for a marginal feature. The transition tables are now managed by the AfterTriggers data structures, rather than being directly the responsibility of ModifyTable nodes. This removes a subtransaction-lifespan memory leak introduced by my previous band-aid patch 3c4359521. In passing, refactor the AfterTriggers data structures to reduce the management overhead for them, by using arrays of structs rather than several parallel arrays for per-query-level and per-subtransaction state. I failed to resist the temptation to do some copy-editing on the SGML docs about triggers, above and beyond merely documenting the effects of this patch. Back-patch to v10, because we don't want the semantics of transition tables to change post-release. Patch by me, with help and review from Thomas Munro. Discussion: https://postgr.es/m/20170909064853.25630.12825@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/0f79440fb0b4c5a9baa9a95570c01828a9093802
  • Doc: add example of transition table use in a trigger. I noticed that there were exactly no complete examples of use of a transition table in a trigger function, and no clear description of just how you'd do it either. Improve that. https://git.postgresql.org/pg/commitdiff/936df5ba80a46fb40bfc93da49a709cbc0aafe5e
  • Fix bogus size calculation introduced by commit cc5f81366. The elements of RecordCacheArray are TupleDesc, not TupleDesc *. Those are actually the same size, so that this error is harmless, but it's still wrong --- and it might bite us someday, if TupleDesc ever became a struct, say. Per Coverity. https://git.postgresql.org/pg/commitdiff/cad22075bc2ce9c1fbe61e8d3969d4dbdb5bc1f3
  • Ensure that BEFORE STATEMENT triggers fire the right number of times. Commit 0f79440fb introduced mechanism to keep AFTER STATEMENT triggers from firing more than once per statement, which was formerly possible if more than one FK enforcement action had to be applied to a given table. Add a similar mechanism for BEFORE STATEMENT triggers, so that we don't have the unexpected situation of firing BEFORE STATEMENT triggers more often than AFTER STATEMENT. As with the previous patch, back-patch to v10. Discussion: https://postgr.es/m/22315.1505584992@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/fd31f9f033213e2ebf00b57ef837e1828c338fc4

Andres Freund pushed:

Bruce Momjian pushed:

Michael Meskes pushed:

Stephen Frost pushed:

  • Fix ordering in pg_dump of GRANTs. The order in which GRANTs are output is important as GRANTs which have been GRANT'd by individuals via WITH GRANT OPTION GRANTs have to come after the GRANT which included the WITH GRANT OPTION. This happens naturally in the backend during normal operation as we only change existing ACLs in-place, only add new ACLs to the end, and when removing an ACL we remove any which depend on it also. Also, adjust the comments in acl.h to make this clear. Unfortunately, the updates to pg_dump to handle initial privileges involved pulling apart ACLs and then combining them back together and could end up putting them back together in an invalid order, leading to dumps which wouldn't restore. Fix this by adjusting the queries used by pg_dump to ensure that the ACLs are rebuilt in the same order in which they were originally. Back-patch to 9.6 where the changes for initial privileges were done. https://git.postgresql.org/pg/commitdiff/d2e40b310aea1050fd499f62f391329f2c331f6a

Robert Haas pushed:

Álvaro Herrera pushed:

Correctifs en attente

Yuto Hayamizu sent in a patch to mitigate the fact that filter cost is estimated to be too high.

Etsuro Fujita sent in another revision of a patch to add support for tuple routing to foreign partitions.

Fabien COELHO sent in two more revisions of a patch to pgbench which fixes meta command only scripts.

Konstantin Knizhnik sent in two more revisions of a patch to implement autoprepare.

Nathan Bossart sent in four more revisions of a patch to allow VACUUM to take multiple tables and error out on duplicate columns in ANALYZE calls.

Michael Banck sent in two more revisions of a patch to add an option to create a replication slot in pg_basebackup if not yet present.

Andrey Borodin sent in two more revisions of a patch to allow GiST opcalsses without (de)compression functions.

Peter Geoghegan sent in another revision of a patch to remove replacement selection sort from the code.

Amit Kapila sent in a patch to change metapage usage for hash and b-tree indexes.

Michaël Paquier sent in a patch to remove wal_keep_segments as the default configuration in PostgresNode.pm.

Tom Lane sent in another revision of a patch to improve OR conditions on joined columns.

Tom Lane sent in another revision of a patch to fix an infelicity between eval_const_expressions and ScalarArrayOpExpr.

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

Yura Sokolov sent in another revision of a patch to improve compactify_tuples by implementing a bucket sort with one pass of stable prefix sort on high 8 bits of offset and insertion sort for buckets larger than 1 element, and simplify PageRepairFragmentation.

Simon Riggs sent in another revision of a patch to add toast_tuple_target().

Tomas Vondra sent in another revision of a patch to implement multivariate histograms and multivariate MCV lists.

Haribabu Kommi sent in two more revisions of a patch to implement a pg_stat_wal_write statistics view.

Elvis Pranskevichus sent in a patch to add a session_read_only GUC.

Fabien COELHO sent in a patch to fix a pgbench regression test failure.

Thomas Munro sent in another revision of a patch to support huge pages on Windows.

Kyotaro HORIGUCHI sent in two more revisions of a patch to fix wal_level_minimal.

Pavel Stěhule sent in another revision of a patch to enable PL/pgsql functions to take composite types as arguments.

Hadi Moshayedi sent in a patch to call RelationDropStorage() for broader range of object drops.

Jing Wang sent in two more revisions of a patch to support to COMMENT ON DATABASE CURRENT_DATABASE.

Thomas Munro sent in another revision of a patch to allow custom search filters to be configured for LDAP auth, add LDAP authentication test suite, and add tests for ldapsearchfilter functionality.

Jeevan Ladhe and Amit Langote traded patches to optimize default partition scanning while adding new partition.

Ashutosh Bapat sent in a patch to ensure that the correct range table entry is being used in the planner for joins between declaratively partitioned tables.

Amit Langote sent in four more revisions of a patch to set pd_lower correctly in the GIN metapage, BRIN metapage, and SP-GiST metapage.

Tom Lane sent in another revision of a patch to enable making arrays of domains.

Amit Langote sent in another revision of a patch to add some enhancments for \d+ output of partitioned tables.

Pavel Stěhule sent in another revision of a patch to add some new optional checks to PL/pgsql.

Vaishnavi Prabakaran sent in another revision of a patch to add batch/pipelining support to libpq.

Kyotaro HORIGUCHI sent in two more revisions of a patch to enable restricting maximum keep segments by replication slots.

Victor Drobny sent in another revision of a patch to add queryto_tsquery().

Thomas Munro sent in a patch to define LDAP_NO_ATTRS if necessary.

Peter Moser sent in another revision of a patch to add temporal processing primitives.

David Steele sent in another revision of a patch to refactor OpenFile() permissions.

Adrian Escoms sent in two revisions of a patch to fix some inconsistencies between pg_settings and postgresql.conf.

Pierre Ducroquet sent in another revision of a patch to allow a pg_basebackup when a tablespace is shared between two versions.

Andres Freund and Beena Emerson traded patches to allow setting the default WAL segment size at initdb time.

Pierre Ducroquet sent in another revision of a patch to port most calls of atoi(optarg) to strcol.

Kyotaro HORIGUCHI sent in another revision of a patch to fix a race between SELECT and ALTER TABLE NO INHERIT.

Amit Khandekar sent in two more revisions of a patch to implement hash partitioning.

Amul Sul sent in another revision of a patch to set the ctid.ip_blkid to InvalidBlockNumber while moving a tuple to the another partition.

Thomas Munro sent in two more revisions of a patch to improve the LDAP cleanup code in error paths and log diagnostic messages if errors occur during LDAP auth.

Ashutosh Bapat sent in a patch to reparamterize partial nestloop paths in response to a bug report.

Mark Rofail sent in another revision of a patch to implement foreign key arrays.

Peter Eisentraut and Masahiko Sawada traded patches to fix an issue where DROP SUBSCRIPTION hangs if sub is disabled in the same transaction.

Amit Kapila sent in another revision of a patch to make parallel paths include tlist cost.

Amit Kapila sent in another revision of a patch to ensure that GatherMerge pushes target list.

Andres Freund sent in a patch to improve catcache/syscache performance.

Andres Freund sent in a patch to fix a binary search bottleneck in fmgr_isbuiltin() by replacing it with a hash table.

Amit Langote sent in a patch to move some of the partitioning code to the executor.

Alexey Chernyshov sent in another revision of a patch to add citext_pattern_ops to the citext contrib module.

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

Amit Langote sent in a patch to teach ATExecAttachPartition to skip validation in more cases.

Alexander Korotkov sent in two more revisions of a patch to implement 64-bit XIDs.

Haribabu Kommi sent in another revision of a patch to add the infrastructure for pluggable storage by changing the Create Access method to include storage handler, adding storage AM API hooks and related functions, addadding a storageam handler to the relation structure, adding a tuple visibility function, slot hooks, a tuple insert API and scan functions to the storage AM, and moving HeapScanDesc usage outside the heap.

Amul Sul sent in two more revisions of a patch to add hash partitioning.

Konstantin Knizhnik sent in three more revisions of a patch to add projection autotuning.

Andres Freund sent in two revisions of a patch to speed up pgstat_report_activity by moving multibyte-aware truncation to the read side.

Andres Freund sent in a patch to fix an issue where SendRowDescriptionMessage() was slow for queries with a lot of columns by adding more efficient functions to pqformat API, improving the performance of SendRowDescriptionMessage, adding an inline murmurhash32(int32) function, replacing the binary search in fmgr_isbuiltin with hashtable, adding a pg_noinline macro to c.h, improving sys/catcache performance, and improving the getBaseTypeAndTypemod() performance for builtin types.

Alexander Korotkov sent in two more revisions of a patch to implement incremental sort.

Ashutosh Bapat sent in two more revisions of a patch to implement partition-wise join for join between (declaratively) partitioned tables.

Peter Eisentraut sent in a patch to use stdbool.h instead of rolling our own.

Jeff Janes sent in a patch to fix issues around superuser as it pertains to foreign tables.

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

Ivan Kartyshov sent in another revision of a patch to deal with long-running transactions on subscriber nodes.

Amit Langote sent in a patch to add some optimizer data structures for partitioned rels, make some planner-side changes for partition-pruning, make some interface changes for partition_bound_{cmp/bsearch}, implement get_partitions_for_keys(), and add more tests for the new partitioning-related planning code.

Dmitry Dolgov sent in another revision of a patch to implement generic type subscripting.

Thomas Munro sent in a patch to add coverage tests for partition-wise join for join between (declaratively) partitioned tables.

Chen Huajun sent in a patch to make pg_rewind to not copy useless WAL files.

Nikita Glukhov sent in another revision of a patch to add the SQL standard SQL/JSON feature.

David Rowley sent in a patch to fix join removal in subqueries.

Peter Geoghegan sent in a patch to allow ICU to use SortSupport on Windows with UTF-8.

Andres Freund sent in a patch to fix an issue which manifested on crash and restart.

Dilip Kumar sent in another revision of a patch to improve bitmap_cost.

Andreas Karlsson sent in another revision of a patch to support GnuTLS for SSL.

Rosser Schwarz sent in another revision of a patch to add --if-exists to pg_recvlogical.

Amit Kapila sent in a patch to fix worker startup failures.

Dilip Kumar sent in another revision of a patch to improve bitmap costing for lossy pages.