Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

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/20170306025346.GA11640@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

Andres Freund pushed:

  • Add "Slab" MemoryContext implementation for efficient equal-sized allocations. The default general purpose aset.c style memory context is not a great choice for allocations that are all going to be evenly sized, especially when those objects aren't small, and have varying lifetimes. There tends to be a lot of fragmentation, larger allocations always directly go to libc rather than have their cost amortized over several pallocs. These problems lead to the introduction of ad-hoc slab allocators in reorderbuffer.c. But it turns out that the simplistic implementation leads to problems when a lot of objects are allocated and freed, as aset.c is still the underlying implementation. Especially freeing can easily run into O(n^2) behavior in aset.c. While the O(n^2) behavior in aset.c can, and probably will, be addressed, custom allocators for this behavior are more efficient both in space and time. This allocator is for evenly sized allocations, and supports both cheap allocations and freeing, without fragmenting significantly. It does so by allocating evenly sized blocks via malloc(), and carves them into chunks that can be used for allocations. In order to release blocks to the OS as early as possible, chunks are allocated from the fullest block that still has free objects, increasing the likelihood of a block being entirely unused. A subsequent commit uses this in reorderbuffer.c, but a further allocator is needed to resolve the performance problems triggering this work. There likely are further potentialy uses of this allocator besides reorderbuffer.c. There's potential further optimizations of the new slab.c, in particular the array of freelists could be replaced by a more intelligent structure - but for now this looks more than good enough. Author: Tomas Vondra, editorialized by Andres Freund Reviewed-By: Andres Freund, Petr Jelinek, Robert Haas, Jim Nasby Discussion: https://postgr.es/m/d15dff83-0b37-28ed-0809-95a5cc7292ad@2ndquadrant.com http://git.postgresql.org/pg/commitdiff/58b25e98106dbe062cec0f3d31d64977bffaa4af
  • Use the new "Slab" context for some allocations in reorderbuffer.h. Note that this change alone does not yet fully address the performance problems triggering this work, a large portion of the slowdown is triggered by the tuple allocator, which isn't converted to the new allocator. It would be possible to do so, but using evenly sized objects, like both the current implementation in reorderbuffer.c and slab.c, wastes a fair amount of memory. A later patch by Tomas will introduce a better approach. Author: Tomas Vondra Reviewed-By: Andres Freund Discussion: https://postgr.es/m/d15dff83-0b37-28ed-0809-95a5cc7292ad@2ndquadrant.com http://git.postgresql.org/pg/commitdiff/9fab40ad32efa4038d19eaed975bb4c1713ccbc0
  • Make useful infrastructure from aset.c generally available. An upcoming patch introduces a new type of memory context. To avoid duplicating debugging infrastructure within aset.c, move useful pieces to memdebug.[ch]. While touching aset.c, fix printf format code in AllocFree* debug macros. Author: Tomas Vondra Reviewed-By: Andres Freund Discussion: https://postgr.es/m/b3b2245c-b37a-e1e5-ebc4-857c914bc747@2ndquadrant.com http://git.postgresql.org/pg/commitdiff/bfd12cccbd72c1846bfa3e4031155c9bd479d70a
  • Overhaul memory management README. The README was written as a "historical account", and that style hasn't aged particularly well. Rephrase it to describe the current situation, instead of having various version specific comments. This also updates the description of how allocated chunks are associated with their corresponding context, the method of which has changed in the preceding commit. Author: Andres Freund Discussion: https://postgr.es/m/20170228074420.aazv4iw6k562mnxg@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/f4e2d50cd7483a068c0a32e56b2d40f980cdea72
  • Reduce size of common allocation header. The new slab allocator needs different per-allocation information than the classical aset.c. The definition in 58b25e981 wasn't sufficiently careful on 32 platforms with 8 byte alignment, leading to buildfarm failures. That's not entirely easy to fix by just adjusting the definition. As slab.c doesn't actually need the size part(s) of the common header, all chunks are equally sized after all, it seems better to instead reduce the header to the part needed by all allocators, namely which context an allocation belongs to. That has the advantage of reducing the overhead of slab allocations, and also allows for more flexibility in future allocators. To avoid spreading the logic about accessing a chunk's context around, centralize it in GetMemoryChunkContext(), which allows to delete a good number of lines. A followup commit will revise the mmgr/README portion about StandardChunkHeader, and more. Author: Andres Freund Discussion: https://postgr.es/m/20170228074420.aazv4iw6k562mnxg@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/7e3aa03b418d604d33040ed8fb866857dae82a02
  • Fix assertion failure due to over-eager code deduplication. In the previous commit I'd made MemoryContextContains() use GetMemoryChunkContext(), but that causes trouble when the passed pointer isn't allocated in any memory context - that's probably something we shouldn't do, but the previous commit isn't a place for a "policy" change. http://git.postgresql.org/pg/commitdiff/123ccbe58309d08e42009e99a4b34a3a1aef7798
  • Fix s/ITERTOR/ITERATOR/ typo in simplehash.h. This could lead to problem when simplehash.h is used to define two different types of hashtable visible in the same translation unit. Reported-By: Josh Soref Discussion: https://postgr.es/m/CACZqfqCC7WdBAY=rQePb9-qW1rjdaTdHsV5KoVejHkDb6qrtOg@mail.gmail.com http://git.postgresql.org/pg/commitdiff/8f7277dfb5e703a034dbce7b155d998e577a6bc3
  • Fix two recently introduced grammar errors in mmgr/README. These were introduced by me in f4e2d50c. Reported-By: Tomas Vondra Discussion: https://postgr.es/m/11adca69-be28-44bc-a801-64e6d53851e3@2ndquadrant.com http://git.postgresql.org/pg/commitdiff/1309375e706440e1e2fe2a5bb74effbc639261ef

Peter Eisentraut pushed:

Tom Lane pushed:

  • Remove PL/Tcl's "module" facility. PL/Tcl has long had a facility whereby Tcl code could be autoloaded from a database table named "pltcl_modules". However, nobody is using it, as evidenced by the recent discovery that it's never been fixed to work with standard_conforming_strings turned on. Moreover, it's rather shaky from a security standpoint, and the table design is very old and crufty (partly because it dates from before we had TOAST). A final problem is that because the table-population scripts depend on the Tcl client library Pgtcl, which we removed from the core distribution in 2004, it's impossible to create a self-contained regression test for the feature. Rather than try to surmount these problems, let's just remove it. A follow-on patch will provide a way to execute user-defined initialization code, similar to features that exist in plperl and plv8. With that, it will be possible to implement this feature or similar ones entirely in userspace, which is where it belongs. Discussion: https://postgr.es/m/22067.1488046447@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/817f2a586342767d3289a320bb1dac5dcbb76979
  • Allow index AMs to return either HeapTuple or IndexTuple format during IOS. Previously, only IndexTuple format was supported for the output data of an index-only scan. This is fine for btree, which is just returning a verbatim index tuple anyway. It's not so fine for SP-GiST, which can return reconstructed data that's much larger than a page. To fix, extend the index AM API so that index-only scan data can be returned in either HeapTuple or IndexTuple format. There's other ways we could have done it, but this way avoids an API break for index AMs that aren't concerned with the issue, and it costs little except a couple more fields in IndexScanDescs. I changed both GiST and SP-GiST to use the HeapTuple method. I'm not very clear on whether GiST can reconstruct data that's too large for an IndexTuple, but that seems possible, and it's not much of a code change to fix. Per a complaint from Vik Fearing. Reviewed by Jason Li. Discussion: https://postgr.es/m/49527f79-530d-0bfe-3dad-d183596afa92@2ndquadrant.fr http://git.postgresql.org/pg/commitdiff/9b88f27cb42fe8ff59ddc75e29c005624b8850a2
  • Update documentation of tsquery_phrase(). Missed in commit 028350f61. Noted by Eiji Seki. http://git.postgresql.org/pg/commitdiff/d99706ed5178d7f37ac322e02e8c56e4e5e0e99a
  • In rebuild_relation(), don't access an already-closed relcache entry. This reliably fails with -DRELCACHE_FORCE_RELEASE, as reported by Andrew Dunstan, and could sometimes fail in normal operation, resulting in a wrong persistence value being used for the transient table. It's not immediately clear to me what effects that might have beyond the risk of a crash while accessing OldHeap->rd_rel->relpersistence, but it's probably not good. Bug introduced by commit f41872d0c, and made substantially worse by commit 85b506bbf, which added a second such access significantly later than the heap_close. I doubt the first reference could fail in a production scenario, but the second one definitely could. Discussion: https://postgr.es/m/7b52f900-0579-cda9-ae2e-de5da17090e6@2ndQuadrant.com http://git.postgresql.org/pg/commitdiff/dbca84f04ed5debe748029699aa44fa86beca32d

Robert Haas pushed:

  • hash: Refactor bucket squeeze code. In preparation for adding write-ahead logging to hash indexes, refactor _hash_freeovflpage and _hash_squeezebucket so that all related page modifications happen in a single section of code. The previous coding assumed that it would be fine to move tuples one at a time, and also that the various operations involved in freeing an overflow page didn't necessarily all need to be done together, all of which is true if you don't care about write-ahead logging. Amit Kapila, with slight changes by me. http://git.postgresql.org/pg/commitdiff/b0f18cb77f50a54e997d857d592f6a511617f52c
  • hash: Refactor overflow page allocation. As with commit b0f18cb77f50a54e997d857d592f6a511617f52c, the goal here is to move all of the related page modifications to a single section of code, in preparation for adding write-ahead logging. Amit Kapila, with slight changes by me. The larger patch series of which this is a part has been reviewed and tested by Ãlvaro Herrera, Ashutosh Sharma, Mark Kirkwood, Jeff Janes, and Jesper Pedersen, all of whom should also have been credited in the previous commit message. http://git.postgresql.org/pg/commitdiff/30df93f698d016d086e8961aa6c6076b37ea0ef4
  • hash: Refactor and clean up bucket split code. As with commit 30df93f698d016d086e8961aa6c6076b37ea0ef4 and commit b0f18cb77f50a54e997d857d592f6a511617f52c, the goal here is to move all of the related page modifications to a single section of code, in preparation for adding write-ahead logging. Amit Kapila, with slight changes by me. The larger patch series of which this is a part has been reviewed and tested by Ãlvaro Herrera, Ashutosh Sharma, Mark Kirkwood, Jeff Janes, and Jesper Pedersen. http://git.postgresql.org/pg/commitdiff/21a3cf41284c08307ef9abe3400be5dc53723519
  • Update comments overlooked by 2f5c9d9c9cec436e55847ec580606d7e88067df6. Tomas Vondra http://git.postgresql.org/pg/commitdiff/fa42b2005f0cd825fe5a5fd4db93a7c30b5fe883
  • Don't uselessly rewrite, truncate, VACUUM, or ANALYZE partitioned tables. Also, recursively perform VACUUM and ANALYZE on partitions when the command is applied to a partitioned table. In passing, some related documentation updates. Amit Langote, reviewed by Michael Paquier, Ashutosh Bapat, and by me. Discussion: http://postgr.es/m/47288cf1-f72c-dfc2-5ff0-4af962ae5c1b@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/3c3bb99330aa9b4c2f6258bfa0265d806bf365c3
  • Refactor bitmap heap scan in preparation for parallel support. The final patch will be less messy if the prefetching support is a bit better isolated, so do that. Dilip Kumar, with some changes by me. The larger patch set of which this is a part has been reviewed and tested by (at least) Andres Freund, Amit Khandekar, Tushar Ahuja, Rafia Sabih, Haribabu Kommi, and Thomas Munro. http://git.postgresql.org/pg/commitdiff/9e0fe09fc5dc1135479b532d1806e28cbc5a35aa
  • Improve error reporting for tuple-routing failures. Currently, the whole row is shown without column names. Instead, adopt a style similar to _bt_check_unique() in ExecFindPartition() and show the failing key: (key1, ...) = (val1, ...). Amit Langote, per a complaint from Simon Riggs. Reviewed by me; I also adjusted the grammar in one of the comments. Discussion: http://postgr.es/m/9f9dc7ae-14f0-4a25-5485-964d9bfc19bd@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/5a73e17317e91912b2755f7960d5bf31d374cf31
  • Notify bgworker registrant after freeing worker slot. Tom Lane observed buildfarm failures caused by the select_parallel regression test trying to launch new parallel queries before the worker slots used by the previous ones were freed. Try to fix this by having the postmaster free the worker slots before it sends the SIGUSR1 notifications to the registering process. This doesn't completely eliminate the possibility that the user backend might (correctly) observe the worker as dead before the slot is free, but I believe it should make the window significantly narrower. Patch by me, per complaint from Tom Lane. Reviewed by Amit Kapila. Discussion: http://postgr.es/m/30673.1487310734@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/aea5d298362e881b13d95a48c5ae116879237389
  • Add pg_current_logfile() function. The syslogger will write out the current stderr and csvlog names, if it's running and there are any, to a new file in the data directory called "current_logfiles". We take care to remove this file when it might no longer be valid (but not at shutdown). The function pg_current_logfile() can be used to read the entries in the file. Gilles Darold, reviewed and modified by Karl O. Pinc, Michael Paquier, and me. Further review by Ãlvaro Herrera and Christoph Berg. http://git.postgresql.org/pg/commitdiff/19dc233c32f2900e57b8da4f41c0f662ab42e080

Magnus Hagander pushed:

Ãlvaro Herrera pushed:

Noah Misch pushed:

  • Handle unaligned SerializeSnapshot() buffer. Likewise in RestoreSnapshot(). Do so by copying between the user buffer and a stack buffer of known alignment. Back-patch to 9.6, where this last applies cleanly. In master, the select_parallel test dies with SIGBUS on "Oracle Solaris 10 1/13 s10s_u11wos_24a SPARC", building 32-bit with gcc 4.9.2. In 9.6 and 9.5, the buffers in question happen to be sufficiently-aligned, and this change is mere insurance against future 9.6 changes or extension code compromising that. http://git.postgresql.org/pg/commitdiff/7f3112135eb67e5df56cd34b8ce662bf6a2390e9

Simon Riggs pushed:

Correctifs en attente

Thomas Munro sent in two revisions of a patch to add a test for SSI SLRU wraparound.

Beena Emerson sent in another revision of a patch to allow setting the default WAL segment size at initdb time.

Kyotaro HORIGUCHI and Peter Eisentraut traded patches to fix an issue in the logical replication protocol.

Amit Langote sent in a patch to allow dropping declaratively partitioned table without CASCADE.

Noah Misch sent in a patch to use wrappers of PG_DETOAST_DATUM_PACKED() more.

Etsuro Fujita sent in a patch to add support for parameterized foreign joins.

Dagfinn Ilmari Mannsåker sent in a patch to add max_pred_locks_per_{relation,page} reloptions.

Alexander Korotkov sent in another revision of a patch to add incremental sort.

Haribabu Kommi sent in another revision of a patch to add infrastructure which will be used to parallelize DML and utility commands.

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

Ãlvaro Herrera sent in a patch to make a BRIN page range unsummarized.

Andrew Dunstan sent in another revision of a patch to enable tree_gin and btree_gist for enums.

Simon Riggs sent in a patch to reduce the amount of bloat resulting from running CREATE INDEX CONCURRENTLY by destroying the snapshot taken in the first phase, before entering the second phase.

Kyotaro HORIGUCHI sent in a patch to make slotSegNo a XLogSegNo, which it already should have been.

Daisuke Higuchi sent in a patch to enable walsender for async to wait until walsender for sync confirms that WAL is written to disk, controlled by a new boolean GUC, async_walsender_delay.

Robert Haas, Amit Langote, and Ashutosh Bapat traded patches to fix an infelicity between declaratively partitioned tables and relfilenode.

Kyotaro HORIGUCHI sent in two more revisions of a patch to implement character conversion with a radix tree.

Takayuki Tsunakawa sent in a PoC patch to implement statement-level ROLLBACK.

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

Surafel Temesgen sent in a patch to disallow multiple queries per PQexec().

Tom Lane sent in two revisions of a patch to remove PL/Tcl's unused modules infrastructure.

Kuntal Ghosh sent in a patch to implement WAL consistency checking for hash indexes.

David Steele sent in two revisions of a patch to make pg_stop_backup() archive wait optional.

Dmitry Dolgov sent in a patch to add some full text search functions for JSONB.

Etsuro Fujita sent in a patch to the PostgreSQL FDW to enable evaluating placeholdervars on the remote server.

Simon Riggs sent in a patch to avoid bloat in CREATE INDEX CONCURRENTLY.

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

David Steele sent in a patch to implement a configurable file mode mask.

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

Peter Eisentraut sent in another revision of a patch to implement IDENTITY columns.

Peter Eisentraut sent in another revision of a patch to implement background sessions.

Peter Eisentraut sent in a patch to use the SQL standard error code for nextval.

Peter Eisentraut sent in another revision of a patch to cast result of copyObject() to the correct type.

Venkata B Nagothi sent in two more revisions of a patch to add recovery_start_point and recovery_incomplete.

Amit Kapila sent in another revision of a patch to add WAL logging for hash indexes.

Peter Moser sent in another revision of a patch to add temporal query processing with range types.

Peter Eisentraut sent in another revision of a patch to do some of the infrastructure for making PostgreSQL's source C++-compatible.

Peter Eisentraut sent in a patch to refactor dblink, replacing some macros with static functions.

Peter Eisentraut sent in a patch to make much of the Perl code perlcritic-clean.

Andres Freund sent in a patch to move contrib/seg to only use V1 calling conventions and remove support for version-0 calling conventions.

KaiGai Kohei sent in two more revisions of a patch to add a PassDownLimitBound for ForeignScan/CustomScan.

Tomas Vondra sent in a patch to update comments about CatalogUpdateIndexes.

Dilip Kumar sent in another revision of a patch to implement parallel bitmap heap scan.

Jim Nasby sent in another revision of a patch to add SPI_execute_callback() and callback-based DestReceiver and minimally use same in PL/PythonU.

Ãlvaro Herrera sent in a patch to add autovacuum work items for BRIN summarization.

Brandur Leach sent in another revision of a patch to add SortSupport for the macaddr type.

Rahila Syed sent in a patch to add support for default partition in declarative partitioning.

Peter Eisentraut sent in another revision of a patch to enable referring to functions without arguments when the name is unique.

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

Yugo Nagata and Amul Sul traded different implementations of patches to implement hash partitioning for declaratively partitioned tables.

Vinayak Pokale sent in a patch to add a progress checker for ANALYZE.

Oleg Bartunov sent in a patch to implement SQL/JSON.

Pavan Deolasee sent in a patch to skip all-visible pages during second HeapScan of CREATE INDEX CONCURRENTLY.

Andres Freund sent in a patch to make simplehash.h grow hashtable in additional cases.

Dilip Kumar and Robert Haas traded patches to implement parallel merge join.

Lucas Fitti sent in a patch to use $ parameters as replacement characters for pg_stat_statements.

Jan Michálek sent in two more revisions of a patch to implement markdown, rst and mediawiki output formats in psql.

Andreas Karlsson sent in another revision of a patch to implement REINDEX CONCURRENTLY.

Anastasia Lubennikova sent in another revision of a patch to add covering + unique indexes.

Ãlvaro Herrera sent in another revision of a patch to improve BRIN cost estimates.

Dagfinn Ilmari Mannsåker sent in a patch to address all the ## no critic annotations except RequireFilenameMatchesPackage.

David Rowley sent in a patch to improve performance of replay of AccessExclusiveLock.

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

Michaël Paquier sent in two more revisions of a patch to implement SCRAM authentication.

Ãlvaro Herrera sent in another revision of a patch to optimize memory allocation in the 'bringetbitmap' function.

Tomas Vondra sent in two more revisions of a patch to implement extended statistics, formerly multivariate statistics.

Jim Mlodgenski sent in two revisions of a patch to track stats for materialized views.

Thomas Munro sent in another revision of a patch to measure replay lag.

Petr Jelínek sent in another revision of a patch to reserve global xmin for create slot snasphot export, not use on disk snapshots for snapshot export in logical decoding, prevent snapshot builder xmin from going backwards, fix xl_running_xacts usage in snapshot builder, and skip unnecessary snapshot builds.

Amit Langote sent in another revision of a patch to improve the documents for partitioning.

Ronan Dunklau sent in a patch to fix mergeappend costsort estimates.

Masahiko Sawada sent in a patch to implement 2PC in ECPG.

Rushabh Lathia sent in a patch to print the correct startup cost for the group aggregate.

Ãlvaro Herrera and Pavel StÄ›hule traded patches to implement xmltable().

Kyotaro HORIGUCHI sent in a patch to remove NamedLWLockTrancheArray.

Petr Jelínek sent in a patch to reorder the asynchronous libpq calls for replication connection.

Tomas Vondra sent in another revision of a patch to add page_checksum and bt_page_items(bytea).

Ashutosh Sharma sent in another revision of a patch to fix an issue where parallel seq. plan was not coming against inheritance or partition table.

David Rowley sent in a patch to fix an issue where foreign join pushdowns not working properly for outer joins.