Une info meta-précocio-aprilienne remplace les habituels chahuts précocio-apriliens dans les nouvelles du jour.

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en avril

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/20180401215905.GA23905@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:

  • initdb: Further polishing of --wal-segsize option. Extend documentation. Improve option parsing in case no argument was specified. https://git.postgresql.org/pg/commitdiff/8ad8d916f99d19e0be7800992c828c3c1a01b693
  • pg_resetwal: Allow users to change the WAL segment size. This adds a new option --wal-segsize (analogous to initdb) that changes the WAL segment size in pg_control. Author: Nathan Bossart <bossartn@amazon.com> https://git.postgresql.org/pg/commitdiff/bf4a8676c316c177f395b54d3305ea4ccc838a66
  • libpq: PQhost to return active connected host or hostaddr. Previously, PQhost didn't return the connected host details when the connection type was CHT_HOST_ADDRESS (i.e., via hostaddr). Instead, it returned the complete host connection parameter (which could contain multiple hosts) or the default host details, which was confusing and arguably incorrect. Change this to return the actually connected host or hostaddr irrespective of the connection type. When hostaddr but no host was specified, hostaddr is now returned. Never return the original host connection parameter, and document that PQhost cannot be relied on before the connection is established. PQport is similarly changed to always return the active connection port and never the original connection parameter. Author: Hari Babu <kommi.haribabu@gmail.com> Reviewed-by: Michael Paquier <michael@paquier.xyz> Reviewed-by: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> https://git.postgresql.org/pg/commitdiff/1944cdc98273dbb8439ad9b387ca2858531afcf0
  • Fix jsonb_plpython tests on older Python versions. Rewrite one test to avoid a case where some Python versions have output format differences (Decimal('1') vs Decimal("1")). https://git.postgresql.org/pg/commitdiff/e81fc9b9dbf9d744dcc9fb210e4353a350be1e22
  • Attempt to fix jsonb_plpython build on Windows. https://git.postgresql.org/pg/commitdiff/75e95dd79ba22e18687a069d2ff2fd29afab5798
  • PL/pgSQL: Nested CALL with transactions. So far, a nested CALL or DO in PL/pgSQL would not establish a context where transaction control statements were allowed. This fixes that by handling CALL and DO specially in PL/pgSQL, passing the atomic/nonatomic execution context through and doing the required management around transaction boundaries. Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com> https://git.postgresql.org/pg/commitdiff/d92bc83c48bdea9888e64cf1e2edbac9693099c9
  • Allow committing inside cursor loop. Previously, committing or aborting inside a cursor loop was prohibited because that would close and remove the cursor. To allow that, automatically convert such cursors to holdable cursors so they survive commits or rollbacks. Portals now have a new state "auto-held", which means they have been converted automatically from pinned. An auto-held portal is kept on transaction commit or rollback, but is still removed when returning to the main loop on error. This supports all languages that have cursor loop constructs: PL/pgSQL, PL/Python, PL/Perl. Reviewed-by: Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> https://git.postgresql.org/pg/commitdiff/056a5a3f63f1a29d9266165ee6e25c6a51ddd63c
  • Transforms for jsonb to PL/Python. Add a new contrib module jsonb_plpython that provide a transform between jsonb and PL/Python. jsonb values are converted to appropriate Python types such as dicts and lists, and vice versa. Author: Anthony Bykov <a.bykov@postgrespro.ru> Reviewed-by: Aleksander Alekseev <a.alekseev@postgrespro.ru> Reviewed-by: Nikita Glukhov <n.gluhov@postgrespro.ru> https://git.postgresql.org/pg/commitdiff/3f44e3db72ad4097aae078c075a9b3cb3d6b761b

Tom Lane pushed:

  • Fix unsafe extraction of the OID part of a relation filename. Commit 8694cc96b did this randomly differently from other callers of parse_filename_for_nontemp_relation(). Perhaps unsurprisingly, the randomly different way is wrong; it fails to ensure the extracted string is null-terminated. Per buildfarm member skink. Discussion: https://postgr.es/m/14453.1522001792@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/d0c0c894533f906b13b79813f02b2982ac675074
  • Doc: add example of type resolution in nested UNIONs. Section 10.5 didn't say explicitly that multiple UNIONs are resolved pairwise. Since the resolution algorithm is described as taking any number of inputs, readers might well think that a query like "select x union select y union select z" would be resolved by considering x, y, and z in one resolution step. But that's not what happens (and I think that behavior is per SQL spec). Add an example clarifying this point. Per bug #15129 from Philippe Beaudoin. Discussion: https://postgr.es/m/152196085023.32649.9916472370480121694@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/c515ff8d0a979fb553136a71388017c97785acda
  • Allow memory contexts to have both fixed and variable ident strings. Originally, we treated memory context names as potentially variable in all cases, and therefore always copied them into the context header. Commit 9fa6f00b1 rethought this a little bit and invented a distinction between fixed and variable names, skipping the copy step for the former. But we can make things both simpler and more useful by instead allowing there to be two parts to a context's identification, a fixed "name" and an optional, variable "ident". The name supplied in the context create call is now required to be a compile-time-constant string in all cases, as it is never copied but just pointed to. The "ident" string, if wanted, is supplied later. This is needed because typically we want the ident to be stored inside the context so that it's cleaned up automatically on context deletion; that means it has to be copied into the context before we can set the pointer. The cost of this approach is basically just an additional pointer field in struct MemoryContextData, which isn't much overhead, and is bought back entirely in the AllocSet case by not needing a headerSize field anymore, since we no longer have to cope with variable header length. In addition, we can simplify the internal interfaces for memory context creation still further, saving a few cycles there. And it's no longer true that a custom identifier disqualifies a context from participating in aset.c's freelist scheme, so possibly there's some win on that end. All the places that were using non-compile-time-constant context names are adjusted to put the variable info into the "ident" instead. This allows more effective identification of those contexts in many cases; for example, subsidary contexts of relcache entries are now identified by both type (e.g. "index info") and relname, where before you got only one or the other. Contexts associated with PL function cache entries are now identified more fully and uniformly, too. I also arranged for plancache contexts to use the query source string as their identifier. This is basically free for CachedPlanSources, as they contained a copy of that string already. We pay an extra pstrdup to do it for CachedPlans. That could perhaps be avoided, but it would make things more fragile (since the CachedPlanSource is sometimes destroyed first). I suspect future improvements in error reporting will require CachedPlans to have a copy of that string anyway, so it's not clear that it's worth moving mountains to avoid it now. This also changes the APIs for context statistics routines so that the context-specific routines no longer assume that output goes straight to stderr, nor do they know all details of the output format. This is useful immediately to reduce code duplication, and it also allows for external code to do something with stats output that's different from printing to stderr. The reason for pushing this now rather than waiting for v12 is that it rethinks some of the API changes made by commit 9fa6f00b1. Seems better for extension authors to endure just one round of API changes not two. Discussion: https://postgr.es/m/CAB=Je-FdtmFZ9y9REHD7VsSrnCkiBhsA4mdsLKSPauwXtQBeNA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/442accc3fe0cd556de40d9d6c776449e82254763
  • Update pgindent's typedefs blacklist, and make it easier to adjust. It seems that all buildfarm members are now using the <stdbool.h> code path, so that none of them report "bool" as a typedef. We still need it to be treated that way, so adjust pgindent to force that whether or not it's in the given list. Also, the recent introduction of LLVM infrastructure has caused the appearance of some typedef names that we definitely *don't* want treated as typedefs, such as "string" and "abs". Extend the existing blacklist to include these. (Additions based on comparing v10's typedefs list to what the buildfarm is currently emitting.) Rearrange the code so that the lists of whitelisted/blacklisted names are a bit easier to find and modify. Andrew Dunstan and Tom Lane Discussion: https://postgr.es/m/28690.1521912334@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ef1978d6ed1e4defe18d250226460409e6cd5447
  • Fix actual and potential double-frees around tuplesort usage. tuplesort_gettupleslot() passed back tuples allocated in the tuplesort's own memory context, even when the caller was responsible to free them. This created a double-free hazard, because some callers might destroy the tuplesort object (via tuplesort_end) before trying to clean up the last returned tuple. To avoid this, change the API to specify that the tuple is allocated in the caller's memory context. v10 and HEAD already did things that way, but in 9.5 and 9.6 this is a live bug that can demonstrably cause crashes with some grouping-set usages. In 9.5 and 9.6, this requires doing an extra tuple copy in some cases, which is unfortunate. But the amount of refactoring needed to avoid it seems excessive for a back-patched change, especially since the cases where an extra copy happens are less performance-critical. Likewise change tuplesort_getdatum() to return pass-by-reference Datums in the caller's context not the tuplesort's context. There seem to be no live bugs among its callers, but clearly the same sort of situation could happen in future. For other tuplesort fetch routines, continue to allocate the memory in the tuplesort's context. This is a little inconsistent with what we now do for tuplesort_gettupleslot() and tuplesort_getdatum(), but that's preferable to adding new copy overhead in the back branches where it's clearly unnecessary. These other fetch routines provide the weakest possible guarantees about tuple memory lifespan from v10 on, anyway, so this actually seems more consistent overall. Adjust relevant comments to reflect these API redefinitions. Arguably, we should change the pre-9.5 branches as well, but since there are no known failure cases there, it seems not worth the risk. Peter Geoghegan, per report from Bernd Helmle. Reviewed by Kyotaro Horiguchi; thanks also to Andreas Seltenreich for extracting a self-contained test case. Discussion: https://postgr.es/m/1512661638.9720.34.camel@oopsware.de https://git.postgresql.org/pg/commitdiff/c2d4eb1b1fa252fd8c407e1519308017a18afed1
  • While vacuuming a large table, update upper-level FSM data every so often. VACUUM updates leaf-level FSM entries immediately after cleaning the corresponding heap blocks. fsmpage.c updates the intra-page search trees on the leaf-level FSM pages when this happens, but it does not touch the upper-level FSM pages, so that the released space might not actually be findable by searchers. Previously, updating the upper-level pages happened only at the conclusion of the VACUUM run, in a single FreeSpaceMapVacuum() call. This is bad because the VACUUM might get canceled before ever reaching that point, so that from the point of view of searchers no space has been freed at all, leading to table bloat. We can improve matters by updating the upper pages immediately after each cycle of index-cleaning and heap-cleaning, processing just the FSM pages corresponding to the range of heap blocks we have now fully cleaned. This adds a small amount of extra work, since the FSM pages leading down to each range boundary will be touched twice, but it's pretty negligible compared to everything else going on in a large VACUUM. If there are no indexes, VACUUM doesn't work in cycles but just cleans each heap page on first visit. In that case we just arbitrarily update upper FSM pages after each 8GB of heap. That maintains the goal of not letting all this work slide until the very end, and it doesn't seem worth expending extra complexity on a case that so seldom occurs in practice. In either case, the FSM is fully up to date before any attempt is made to truncate the relation, so that the most likely scenario for VACUUM cancellation no longer results in out-of-date upper FSM pages. When we do successfully truncate, adjusting the FSM to reflect that is now fully handled within FreeSpaceMapTruncateRel.
  • Remove UpdateFreeSpaceMap(), use FreeSpaceMapVacuumRange() instead. FreeSpaceMapVacuumRange has the same effect, is more efficient if many pages are involved, and makes fewer assumptions about how it's used. Notably, Claudio Freire pointed out that UpdateFreeSpaceMap could fail if the specified freespace value isn't the maximum possible. This isn't a problem for the single existing user, but the function represents an attractive nuisance IMO, because it's named as though it were a general-purpose update function and its limitations are undocumented. In any case we don't need multiple ways to get the same result. In passing, do some code review and cleanup in RelationAddExtraBlocks. In particular, I see no excuse for it to omit the PageIsNew safety check that's done in the mainline extension path in RelationGetBufferForTuple. Discussion: https://postgr.es/m/CAGTBQpYR0uJCNTt3M5GOzBRHo+-GccNO1nCaQ8yEJmZKSW5q1A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a063baaced273e955e088ba5979dcc6ec5cd92e6
  • Remove unnecessary BufferGetPage() calls in fsm_vacuum_page(). Just noticed that these were quite redundant, since we're holding the page address in a local variable anyway, and we have pin on the buffer throughout. Also improve a comment. https://git.postgresql.org/pg/commitdiff/2b1759e2675fc01d6945c9a5fa65c7d7121212f7
  • Do index FSM vacuuming sooner. In btree and SP-GiST indexes, move the responsibility for calling IndexFreeSpaceMapVacuum from the vacuumcleanup phase to the bulkdelete phase, and do it if and only if we found some pages that could be put into FSM. As in commit 851a26e26, the idea is to make free pages visible to FSM searchers sooner when vacuuming very large tables (large enough to need multiple bulkdelete scans). This adds more redundant work than that commit did, since we have to scan the entire index FSM each time rather than being able to localize what needs to be updated; but it still seems worthwhile. However, we can buy something back by not touching the FSM at all when there are no pages that can be put in it. That will result in slower recovery from corrupt upper FSM pages in such a scenario, but it doesn't seem like that's a case we need to optimize for. Hash indexes don't use FSM at all. GIN, GiST, and bloom indexes update FSM during the vacuumcleanup phase not bulkdelete, so that doing something comparable to this would be a much more invasive change, and it's not clear it's worth it. BRIN indexes do things sufficiently differently that this change doesn't apply to them, either. Claudio Freire, reviewed by Masahiko Sawada and Jing Wang, some additional tweaks by me Discussion: https://postgr.es/m/CAGTBQpYR0uJCNTt3M5GOzBRHo+-GccNO1nCaQ8yEJmZKSW5q1A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c79f6df75dd381dbc387326f8155402992524124
  • Improve out-of-memory error reports by including memory context name. Add the target context's name to the errdetail field of "out of memory" errors in mcxt.c. Per discussion, this seems likely to be useful to help narrow down the cause of a reported failure, and it costs little. Also, now that context names are required to be compile-time constants in all cases, there's little reason to be concerned about security issues from exposing these names to users. (Because of such concerns, we are *not* including the context "ident" field.) In passing, add unlikely() markers to the allocation-failed tests, just to be sure the compiler is on the right page about that. Also, in palloc and friends, copy CurrentMemoryContext into a local variable, as that's almost surely cheaper to reference than a global. Discussion: https://postgr.es/m/1099.1522285628@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1bb9e731e17b79f5b1c6713159812adfcf4f6495
  • Remove obsolete SLRU wrapping and warnings from predicate.c. When SSI was developed, slru.c was limited to segment files with names in the range 0000-FFFF. This didn't allow enough space for predicate.c to store every possible XID when spilling old transactions to disk, so it would wrap around sooner and print warnings. Since commits 638cf09e and 73c986ad increased the number of segment files slru.c could manage, that behavior is unnecessary. Therefore remove that code. Also remove the macro OldSerXidSegment, which has been unused since 4cd3fb6e. Thomas Munro, reviewed by Anastasia Lubennikova Discussion: https://postgr.es/m/CAEepm=3XfsTSxgEbEOmxu0QDiXy0o18NUg2nC89JZcCGE+XFPA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e5eb4fa87331821423b362be5ea4b18e873d5b27
  • Ensure that WAL pages skipped by a forced WAL switch are zero-filled. In the previous coding, skipped pages were mostly zeroes, but they still had valid WAL page headers. That makes them very much less compressible than an unbroken string of zeroes would be --- about 10X worse for bzip2 compression, for instance. We don't need those headers, so tweak the logic so that we zero them out. Chapman Flack, reviewed by Daniel Gustafsson Discussion: https://postgr.es/m/579297F8.7020107@anastigmatix.net https://git.postgresql.org/pg/commitdiff/4a33bb59dfc33566f04e18ab5e1f90b8e7461052
  • Fix bogus provolatile/proparallel markings on a few built-in functions. Richard Yen reported that pg_upgrade failed if the target cluster had force_parallel_mode = on, because binary_upgrade_create_empty_extension() is marked parallel restricted, allowing it to be executed in parallel mode, which complains because it tries to acquire an XID. In general, no function that might try to modify database data should be considered parallel safe or restricted, since execution of it might force XID acquisition. We found several other examples of this mistake. Furthermore, functions that execute user-supplied SQL queries or query fragments, or pull data from user-supplied cursors, had better be marked both volatile and parallel unsafe, because we don't know what the supplied query or cursor might try to do. There were several tsquery and XML functions that had the wrong proparallel marking for this, and some of them were even mislabeled as to volatility. All these bugs are old, dating back to 9.6 for the proparallel mistakes and much further for the provolatile mistakes. We can't force a catversion bump in the back branches, but we can at least ensure that installations initdb'd in future have the right values. Thomas Munro and Tom Lane Discussion: https://postgr.es/m/CAEepm=2sNDScSLTfyMYu32Q=ob98ZGW-vM_2oLxinzSABGQ6VA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/11002f8afa551f4673aa6a7b62c1872c233e6052
  • Fix portability and translatability issues in commit 64f85894a. Compilation failed for lack of an #ifdef on builds without pg_strong_random(). Also fix relevant error messages to meet project style guidelines. Fabien Coelho, further adjusted by me Discussion: https://postgr.es/m/32390.1522464534@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/5635c7aa678804292fe47bceab5b3f4dae5f39f9
  • Fix assorted issues in parallel vacuumdb. Avoid storing the result of PQsocket() in a pgsocket variable; it's declared as int, and the no-socket test is properly written as "x < 0" not "x == PGINVALID_SOCKET". This accidentally had no bad effect because we never got to init_slot() with a bad connection, but it's still wrong. Actually, it seems like we should avoid storing the result for a long period at all. The function's not so expensive that it's worth avoiding, and the existing coding technique here would fail if anyone tried to PQreset the connection during the life of the program. Hence, just re-call PQsocket every time we construct a select(2) mask. Speaking of select(), GetIdleSlot imagined that it could compute the select mask once and continue to use it over multiple calls to select_loop(), which is pretty bogus since that would stomp on the mask on return. This could only matter if the function's outer loop iterated more than once, which is unlikely (it'd take some connection receiving data, but not enough to complete its command). But if it did happen, we'd acquire "tunnel vision" and stop watching the other connections for query termination, with the effect of losing parallelism. Another way in which GetIdleSlot could lose parallelism is that once PQisBusy returns false, it would lock in on that connection and do PQgetResult until that returns NULL; in some cases that could result in blocking. (Perhaps this can never happen in vacuumdb due to the limited set of commands that it can issue, but I'm not quite sure of that, and even if true today it's not a future-proof assumption.) Refactor the code to do that properly, so that it risks blocking in PQgetResult only in cases where we need to wait anyway. Another loss-of-parallelism problem, which *is* easily demonstrable, is that any setup queries issued during prepare_vacuum_command() were always issued on the last-to-be-created connection, whether or not that was idle. Long-running operations on that connection thus prevented issuance of additional operations on the other ones, except in the limited cases where no preparatory query was needed. Instead, wait till we've identified a free connection and use that one. Also, avoid core dump due to undersized malloc request in the case that no tables are identified to be vacuumed. The bogus no-socket test was noted by CharSyam, the other problems identified in my own code review. Back-patch to 9.5 where parallel vacuumdb was introduced. Discussion: https://postgr.es/m/CAMrLSE6etb33-192DTEUGkV-TsvEcxtBDxGWG1tgNOMnQHwgDA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/94173d3eeb445ae49020c5e2d9e166856d923eaf
  • Portability fix for commit 9a895462d. So far as I can find, NI_MAXHOST isn't actually required anywhere by POSIX. Nonetheless, commit 9a895462d supposed that it could rely on having that symbol without any ceremony at all. We do have a hack for providing it if the platform doesn't, in getaddrinfo.h, so fix the problem by #including that file. Per buildfarm. https://git.postgresql.org/pg/commitdiff/b0c90c85fc93f37107365dd8c7f47ba5e00544de
  • Fix a boatload of typos in C comments. Justin Pryzby Discussion: https://postgr.es/m/20180331105640.GK28454@telsasoft.com https://git.postgresql.org/pg/commitdiff/0b11a674fb11cc1571326c861ecdd7773d9e587f

Andrew Dunstan pushed:

Álvaro Herrera pushed:

Andres Freund pushed:

Teodor Sigaev pushed:

Simon Riggs pushed:

  • Use pg_stat_get_xact* functions within xacts. Resolve build farm failures from c203d6cf81b4d7e43, diagnosed by Tom Lane. The output of pg_stat_get_xact_tuples_hot_updated() and friends is not guaranteed to show anything after the transaction completes. Data is flushed slowly to stats collector, so using them can give timing issues. https://git.postgresql.org/pg/commitdiff/5b0d7f6996abfc1e3e51bac62af6076903635dc8
  • Allow HOT updates for some expression indexes. If the value of an index expression is unchanged after UPDATE, allow HOT updates where previously we disallowed them, giving a significant performance boost in those cases. Particularly useful for indexes such as JSON->>field where the JSON value changes but the indexed value does not. Submitted as "surjective indexes" patch, now enabled by use of new "recheck_on_update" parameter. Author: Konstantin Knizhnik Reviewer: Simon Riggs, with much wordsmithing and some cleanup https://git.postgresql.org/pg/commitdiff/c203d6cf81b4d7e43edb2b75ec1b741ba48e04e0
  • Store 2PC GID in commit/abort WAL recs for logical decoding. Store GID of 2PC in commit/abort WAL records when wal_level = logical. This allows logical decoding to send the SAME gid to subscribers across restarts of logical replication. Track replica origin replay progress for 2PC. (Edited from patch 0003 in the logical decoding 2PC series.) Authors: Nikhil Sontakke, Stas Kelvich Reviewed-by: Simon Riggs, Andres Freund https://git.postgresql.org/pg/commitdiff/1eb6d6527aae264b3e0b9c95aa70bb7a594ad1cf

Fujii Masao pushed:

  • Fix handling of files that source server removes during pg_rewind is running. After processing the filemap to build the list of chunks that will be fetched from the source to rewing the target server, it is possible that a file which was previously processed is removed from the source. A simple example of such an occurence is a WAL segment which gets recycled on the target in-between. When the filemap is processed, files not categorized as relation files are first truncated to prepare for its full copy of which is going to be taken from the source, divided into a set of junks. However, for a recycled WAL segment, this would result in a segment which has a zero-byte size. With such an empty file, post-rewind recovery thinks that records are saved but they are actually not because of the truncation which happened when processing the filemap, resulting in data loss. In order to fix the problem, make sure that files which are found as removed on the source when receiving chunks of them are as well deleted on the target server for consistency. Back-patch to 9.5 where pg_rewind was added. Author: Tsunakawa Takayuki Reviewed-by: Michael Paquier Reported-by: Tsunakawa Takayuki Discussion: https://postgr.es/m/0A3221C70F24FB45833433255569204D1F8DAAA2%40G01JPEXMBYT05 https://git.postgresql.org/pg/commitdiff/09e96b3f35627a2939e2effd8b98aaa934f59b32
  • Make pg_rewind skip files and directories that are removed during server start. The target cluster that was rewound needs to perform recovery from the checkpoint created at failover, which leads it to remove or recreate some files and directories that may have been copied from the source cluster. So pg_rewind can skip synchronizing such files and directories, and which reduces the amount of data transferred during a rewind without changing the usefulness of the operation. Author: Michael Paquier Reviewed-by: Anastasia Lubennikova, Stephen Frost and me Discussion: https://postgr.es/m/20180205071022.GA17337@paquier.xyz https://git.postgresql.org/pg/commitdiff/266b6acb312fc440c1c1a2036aa9da94916beac6
  • Enhance pg_stat_wal_receiver view to display host and port of sender server. Previously there was no way in the standby side to find out the host and port of the sender server that the walreceiver was currently connected to when multiple hosts and ports were specified in primary_conninfo. For that purpose, this patch adds sender_host and sender_port columns into pg_stat_wal_receiver view. They report the host and port that the active replication connection currently uses. Bump catalog version. Author: Haribabu Kommi Reviewed-by: Michael Paquier and me Discussion: https://postgr.es/m/CAJrrPGcV_aq8=cdqkFhVDJKEnDQ70yRTTdY9RODzMnXNrCz2Ow@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9a895462d940c0694042059f90e5f63a0a517ded

Bruce Momjian pushed:

Magnus Hagander pushed:

Robert Haas pushed:

  • Teach create_projection_plan to omit projection where possible. We sometimes insert a ProjectionPath into a plan tree when projection is not strictly required. The existing code already arranges to avoid emitting a Result node when the ProjectionPath's subpath can perform the projection itself, but previously it didn't consider the possibility that the parent node might not actually require the projection to be performed at all. Skipping projection when it's not required can not only avoid Result nodes that aren't needed, but also avoid losing the "physical tlist" optimization unneccessarily. Patch by me, reviewed by Amit Kapila. Discussion: http://postgr.es/m/CA+TgmoakT5gmahbPWGqrR2nAdFOMAOnOXYoWHRdVfGWs34t6_A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d7c19e62a8e0a634eb6b29f8f1111d944e57081f
  • Postpone generate_gather_paths for topmost scan/join rel. Don't call generate_gather_paths for the topmost scan/join relation when it is initially populated with paths. Instead, do the work in grouping_planner. By itself, this gains nothing; in fact it loses slightly because we end up calling set_cheapest() for the topmost scan/join rel twice rather than once. However, it paves the way for a future commit which will postpone generate_gather_paths for the topmost scan/join relation even further, allowing more accurate costing of parallel paths. Amit Kapila and Robert Haas. Earlier versions of this patch (which different substantially) were reviewed by Dilip Kumar, Amit Khandekar, Marina Polyakova, and Ashutosh Bapat. https://git.postgresql.org/pg/commitdiff/3f90ec8597c3515e0d3190613b31491686027e4b
  • Remove 'target' from GroupPathExtraData. It's not needed. Jeevan Chalke Discussion: http://postgr.es/m/CAM2+6=XPWujjmj5zUaBTGDoB38CemwcPmjkRy0qOcsQj_V+2sQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c1de1a3a8b93a61e8264484a10a482156026d12c
  • Rewrite the code that applies scan/join targets to paths. If the toplevel scan/join target list is parallel-safe, postpone generating Gather (or Gather Merge) paths until after the toplevel has been adjusted to return it. This (correctly) makes queries with expensive functions in the target list more likely to choose a parallel plan, since the cost of the plan now reflects the fact that the evaluation will happen in the workers rather than the leader. The original complaint about this problem was from Jeff Janes. If the toplevel scan/join relation is partitioned, recursively apply the changes to all partitions. This sometimes allows us to get rid of Result nodes, because Append is not projection-capable but its children may be. It also cleans up what appears to be incorrect SRF handling from commit e2f1eb0ee30d144628ab523432320f174a2c8966: the old code had no knowledge of SRFs for child scan/join rels. Because we now use create_projection_path() in some cases where we formerly used apply_projection_to_path(), this changes the ordering of columns in some queries generated by postgres_fdw. Update regression outputs accordingly. Patch by me, reviewed by Amit Kapila and by Ashutosh Bapat. Other fixes for this problem (substantially different from this version) were reviewed by Dilip Kumar, Amit Khandekar, and Marina Polyakova. Discussion: http://postgr.es/m/CAMkU=1ycXNipvhWuweUVpKuyu6SpNjF=yHWu4c4US5JgVGxtZQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/11cf92f6e2e13c0a6e3f98be3e629e6bd90b74d5
  • Don't call IS_DUMMY_REL() when cheapest_total_path might be junk. Unlike the previous coding, this might result in a Gather per Append subplan when the target list is parallel-restricted, but such a plan is probably worth considering in that case, since a single Gather on top of the entire Append is impossible. Per Andres Freund and the buildfarm. Discussion: http://postgr.es/m/20180330050351.bmxx4cdtz67czjda@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/96030f9a481a78483945447e122a387902a4549b

Tatsuo Ishii pushed:

Correctifs en attente

Kyotaro HORIGUCHI sent in another revision of a patch to fix a bug in ECPG where freeing memory for pgtypes crashes on Windows.

Craig Ringer sent in a patch to add pg_xact_commit_timestamp_origin().

Pavan Deolasee sent in a patch to fix and off-by-one error in basebackup.c.

Nikita Glukhov sent in another revision of a patch to add missing type conversion functions for PL/PythonU.

Amit Langote sent in two more revisions of a patch to implement ON CONFLICT DO UPDATE for partitioned tables.

Doug Rady sent in another revision of a patch to add an option to pgbench to build using ppoll() for larger connection counts.

Peter Eisentraut sent in another revision of a patch to pL/pgSQL to allow committing inside cursor loop.

Pavan Deolasee sent in three more revisions of a patch to implement MERGE.

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

Ildus Kurbangaliev sent in two more revisions of a patch to implement custom compression.

David Rowley and Tom Lane traded patches to add parallel aggregates for string_agg and array_agg.

John Naylor sent in three more revisions of a patch to rationalize the treatment of bootstrap data.

Robert Haas and Amit Kapila traded patches to enable parallel seq scan for slow functions.

Pavel Stěhule sent in two more revisions of a patch to add a csv output format to psql.

Stephen Frost sent in two more revisions of a patch to add a pg_access_server_files default role.

Thomas Munro, Dmitry Ivanov, and Aleksandr Parfenov traded patches to add a websearch_to_tsquery() function.

Fabien COELHO sent in another revision of a patch to add a function to pgbench to test for existence of a variable.

Daniel Gustafsson sent in two revisions of a patch to ensure that maxlen is an integer value in dict_int configuration.

Markus Winand sent in a patch to accept TEXT and CDATA nodes in XMLTABLE's column_expression and set the correct context for XPath evaluation.

Nikita Glukhov sent in another revision of a patch to implement JSONPATH.

Etsuro Fujita sent in a patch to fix an issue where COPY was not looking at a partition's constraints and could result in having added non-compliant data to it.

Edmund Horner sent in a patch to allocate enough shared string memory for the stats of auxiliary processes.

Jeevan Chalke sent in two more revisions of a patch to fix partition-wise aggregation/grouping.

Fabien COELHO sent in a patch to fix some typos in the pgbench docs.

Marina Polyakova sent in two more revisions of a patch to fix pgbench errors and serialization/deadlock retries.

David Steele sent in two more revisions of a patch to implement a configurable file mode mask.

Thomas Munro sent in two revisions of a patch to mark binary_upgrade_create_empty_extension PARALLEL UNSAFE.

Michaël Paquier and Kyotaro HORIGUCHI traded patches to fix an issue with setting the full page writes using SIGHUP.

Tomáš Vondra sent in three more revisions of a patch to implement multivariate histograms and MCV lists.

Konstantin Knizhnik sent in another revision of a patch to allow HOT updates for some expression indexes.

Amit Langote sent in another revision of a patch to reorganize the partitioning code.

Konstantin Knizhnik sent in three revisions of a patch to implement libpq compression.

Dmitry Ivanov, Teodor Sigaev, and Alexander Korotkov traded patches to implement predicate locking for GIN indexes.

Alexander Kuzmenkov and Tomáš Vondra traded patches to implement incremental sort.

Artur Zakirov sent in two more revisions of a patch to enable shared ISpell dictionaries.

Anastasia Lubennikova and Alexander Korotkov traded patches to implement covering + unique indexes, and Andrey Borodin added tests for same.

Ildus Kurbangaliev sent in another revision of a patch to implement a prefix operator for text and spgist support.

Haribabu Kommi sent in another revision of a patch to implement pluggable storage.

Daniel Gustafsson sent in another revision of a patch to support an optional message in backend cancel/terminate.

Peter Eisentraut sent in another revision of a patch to implement SET TRANSACTION in PL/pgsql.

Jesper Pedersen sent in a patch to clarify the JIT README.

Craig Ringer sent in a patch to fix an issue where PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS.

Kyotaro HORIGUCHI sent in another revision of a patch to fix a WAL logging issue.

Kyotaro HORIGUCHI and Amit Langote traded patches to fix a broken ATTACH PARTITION behavior.

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

Fujii Masao sent in a patch to speed up relation deletes during recovery.

Matheus de Oliveira sent in another revision of a patch to btree_gin to add support for uuid, bool, name, bpchar and anyrange types.

Álvaro Herrera sent in another revision of a patch to allow partitioned tables to have foreign keys that point to non-partitioned tables.

CharSyam sent in four revisions of a patch to add a sock check for Windows.

David Rowley sent in another revision of a patch to implement runtime partition pruning.

Magnus Hagander sent in three more revisions of a patch to make it possible to enable checksums online.

Michael Banck sent in another revision of a patch to verify checksums during pgbasebackups.

Peter Geoghegan sent in another revision of a patch to add amcheck verification of heap relations.

Pavel Stěhule sent in a patch to fix some breakage in PL/pgsql's plan cache.

Pavel Stěhule sent in a patch to fix tab completion in psql for the \sf and \ef.

Simon Riggs sent in a patch to ignore lazy vacuums in RunningTransactionData.

Dmitry Dolgov sent in a patch to rationalize the behavior of json[b]_to_tsvector with respect to numeric values.

Yura Sokolov sent in a patch to make a hash table for xip in XidInMVCCSnapshot.