== PostgreSQL Weekly News - January 19, 2020 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - January 19, 2020 == |
| Дата | |
| Msg-id | 20200119192555.GA806@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - January 19, 2020 == PGDay.IT 2020 will take place June 11-12 in Bergamo, Italy. The CfP is open until February 8th, 2020 at midnight, as is the Call for Workshops. https://2020.pgday.it/en/ == PostgreSQL Product News == gbouncer_fdw 0.2.0 released. https://info.crunchydata.com/blog/making-pgbouncer-easier-to-monitor check_pgbackrest 1.7, a Nagios-compatible monitor for pgBackRest, released. https://github.com/dalibo/check_pgbackrest/releases PostgreSQL TDE 12, a fork of PostgreSQL which offers transparent data encryption, released. https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption pgbouncer_fdw 0.2.0, a foreign data wrapper around pgbouncer, released. https://info.crunchydata.com/blog/making-pgbouncer-easier-to-monitor pgbouncer_wrapper 1.1.0, a system to enable SQL-based access to pgbouncer, released. https://github.com/davidfetter/pgbouncer_wrapper == PostgreSQL Jobs for January == http://archives.postgresql.org/pgsql-jobs/2020-01/ == PostgreSQL Local == PGDay SF will take place on January 21, 2020 at the Swedish American Hall in San Francisco. https://2020.pgdaysf.org/ pgDay Israel 2020 will take place on March 19, 2020 in Tel Aviv. http://pgday.org.il/ pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace Saint-Martin. https://2020.pgday.paris/ Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki Strand Hotel on March 24, 2020. PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka. http://pgconf.in/ PostgreSQL@SCaLE is a two day, two track event which takes place on March 5-6, 2020, at Pasadena Convention Center, as part of SCaLE 18X. https://www.socallinuxexpo.org/scale/18x/postgresscale The German-speaking PostgreSQL Conference 2020 will take place on May 15, 2019 in Stuttgart. PGCon 2019 will take place in Ottawa on May 26-29, 2020. The CfP is open through January 19, 2020 at http://www.pgcon.org/2020/papers.php https://www.pgcon.org/2020/ == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org. == Applied Patches == Tom Lane pushed: - Fix edge-case crashes and misestimation in range containment selectivity. When estimating the selectivity of "range_var <@ range_constant" or "range_var @> range_constant", if the upper (or respectively lower) bound of the range_constant was above the last bin of the range_var's histogram, the code would access uninitialized memory and potentially crash (though it seems the probability of a crash is quite low). Handle the endpoint cases explicitly to fix that. While at it, be more paranoid about the possibility of getting NaN or other silly results from the range type's subdiff function. And improve some comments. Ordinarily we'd probably add a regression test case demonstrating the bug in unpatched code. But it's too hard to get it to crash reliably because of the uninitialized-memory dependence, so skip that. Per bug #16122 from Adam Scott. It's been broken from the beginning, apparently, so backpatch to all supported branches. Diagnosis by Michael Paquier, patch by Andrey Borodin and Tom Lane. Discussion: https://postgr.es/m/16122-eb35bc248c806c15@postgresql.org https://git.postgresql.org/pg/commitdiff/652686a334b437f57f9bd0e3baa5dbd245a9e15d - Reduce size of backend scanner's tables. Previously, the core scanner's yy_transition[] array had 37045 elements. Since that number is larger than INT16_MAX, Flex generated the array to contain 32-bit integers. By reimplementing some of the bulkier scanner rules, this patch reduces the array to 20495 elements. The much smaller total length, combined with the consequent use of 16-bit integers for the array elements reduces the binary size by over 200kB. This was accomplished in two ways: 1. Consolidate handling of quote continuations into a new start condition, rather than duplicating that logic for five different string types. 2. Treat Unicode strings and identifiers followed by a UESCAPE sequence as three separate tokens, rather than one. The logic to de-escape Unicode strings is moved to the filter code in parser.c, which already had the ability to provide special processing for token sequences. While we could have implemented the conversion in the grammar, that approach was rejected for performance and maintainability reasons. Performance in microbenchmarks of raw parsing seems equal or slightly faster in most cases, and it's reasonable to expect that in real-world usage (with more competition for the CPU cache) there will be a larger win. The exception is UESCAPE sequences; lexing those is about 10% slower, primarily because the scanner now has to be called three times rather than one. This seems acceptable since that feature is very rarely used. The psql and epcg lexers are likewise modified, primarily because we want to keep them all in sync. Since those lexers don't use the space-hogging -CF option, the space savings is much less, but it's still good for perhaps 10kB apiece. While at it, merge the ecpg lexer's handling of C-style comments used in SQL and in C. Those have different rules regarding nested comments, but since we already have the ability to keep track of the previous start condition, we can use that to handle both cases within a single start condition. This matches the core scanner more closely. John Naylor Discussion: https://postgr.es/m/CACPNZCvaoa3EgVWm5yZhcSTX6RAtaLgniCPcBVOCwm8h3xpWkw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7f380c59f800f7e0fb49f45a6ff7787256851a59 - Restructure ALTER TABLE execution to fix assorted bugs. We've had numerous bug reports about how (1) IF NOT EXISTS clauses in ALTER TABLE don't behave as-expected, and (2) combining certain actions into one ALTER TABLE doesn't work, though executing the same actions as separate statements does. This patch cleans up all of the cases so far reported from the field, though there are still some oddities associated with identity columns. The core problem behind all of these bugs is that we do parse analysis of ALTER TABLE subcommands too soon, before starting execution of the statement. The root of the bugs in group (1) is that parse analysis schedules derived commands (such as a CREATE SEQUENCE for a serial column) before it's known whether the IF NOT EXISTS clause should cause a subcommand to be skipped. The root of the bugs in group (2) is that earlier subcommands may change the catalog state that later subcommands need to be parsed against. Hence, postpone parse analysis of ALTER TABLE's subcommands, and do that one subcommand at a time, during "phase 2" of ALTER TABLE which is the phase that does catalog rewrites. Thus the catalog effects of earlier subcommands are already visible when we analyze later ones. (The sole exception is that we do parse analysis for ALTER COLUMN TYPE subcommands during phase 1, so that their USING expressions can be parsed against the table's original state, which is what we need. Arguably, these bugs stem from falsely concluding that because ALTER COLUMN TYPE must do early parse analysis, every other command subtype can too.) This means that ALTER TABLE itself must deal with execution of any non-ALTER-TABLE derived statements that are generated by parse analysis. Add a suitable entry point to utility.c to accept those recursive calls, and create a struct to pass through the information needed by the recursive call, rather than making the argument lists of AlterTable() and friends even longer. Getting this to work correctly required a little bit of fiddling with the subcommand pass structure, in particular breaking up AT_PASS_ADD_CONSTR into multiple passes. But otherwise it's mostly a pretty straightforward application of the above ideas. Fixing the residual issues for identity columns requires refactoring of where the dependency link from an identity column to its sequence gets set up. So that seems like suitable material for a separate patch, especially since this one is pretty big already. Discussion: https://postgr.es/m/10365.1558909428@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1281a5c907b41e992a66deb13c3aa61888a62268 - Minor code beautification in regexp.c. Remove duplicated code (apparently introduced by commit c8ea87e4b). Also get rid of some PG_USED_FOR_ASSERTS_ONLY variables we don't really need to have. Li Japin, Tom Lane Discussion: https://postgr.es/m/PS1PR0601MB3770A5595B6E5E3FD6F35724B6360@PS1PR0601MB3770.apcprd06.prod.outlook.com https://git.postgresql.org/pg/commitdiff/0db7c67051806f28a9129d50695efc19372d3af2 - Move wchar.c and encnames.c to src/common/. Formerly, various frontend directories symlinked these two sources and then built them locally. That's an ancient, ugly hack, and we now have a much better way: put them into libpgcommon. So do that. (The immediate motivation for this is the prospect of having to introduce still more symlinking if we don't.) This commit moves these two files absolutely verbatim, for ease of reviewing the git history. There's some follow-on work to be done that will modify them a bit. Robert Haas, Tom Lane Discussion: https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e6afa8918c461c1dd80c5063a950518fa4e950cd - Update header comments for wchar.c and encnames.c. Bring these into common style (including having proper copyright notices) and adjust their self-declaration of where they live. Discussion: https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/3d4cb5d6c180e38ebf84f6403cfa18c09d0b738e - Rationalize code placement between wchar.c, encnames.c, and mbutils.c. Move all the backend-only code that'd crept into wchar.c and encnames.c into mbutils.c. To remove the last few #ifdef dependencies from wchar.c and encnames.c, also make the following changes: * Adjust get_encoding_name_for_icu to return NULL, not throw an error, for unsupported encodings. Its sole caller can perfectly well throw an error instead. (While at it, I also made this function and its sibling is_encoding_supported_by_icu proof against out-of-range encoding IDs.) * Remove the overlength-name error condition from pg_char_to_encoding. It's completely silly not to treat that just like any other the-name-is-not-in-the-table case. Also, get rid of pg_mic_mblen --- there's no obvious reason why conv.c shouldn't call pg_mule_mblen instead. Other than that, this is just code movement and comment-polishing with no functional changes. Notably, I reordered declarations in pg_wchar.h to show which functions are frontend-accessible and which are not. Discussion: https://postgr.es/m/CA+TgmoYO8oq-iy8E02rD8eX25T-9SmyxKWqqks5OMHxKvGXpXQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5afaa2e42655811461044c4216e2f821cadc766d - Repair more failures with SubPlans in multi-row VALUES lists. Commit 9b63c13f0 turns out to have been fundamentally misguided: the parent node's subPlan list is by no means the only way in which a child SubPlan node can be hooked into the outer execution state. As shown in bug #16213 from Matt Jibson, we can also get short-lived tuple table slots added to the outer es_tupleTable list. At this point I have little faith that there aren't other possible connections as well; the long time it took to notice this problem shows that this isn't a heavily-exercised situation. Therefore, revert that fix, returning to the coding that passed a NULL parent plan pointer down to the transiently-built subexpressions. That gives us a pretty good guarantee that they won't hook into the outer executor state in any way. But then we need some other solution to make SubPlans work. Adopt the solution speculated about in the previous commit's log message: do expression initialization at plan startup for just those VALUES rows containing SubPlans, abandoning the goal of reclaiming memory intra-query for those rows. In practice it seems unlikely that queries containing a vast number of VALUES rows would be using SubPlans in them, so this should not give up much. (BTW, this test case also refutes my claim in connection with the prior commit that the issue only arises with use of LATERAL. That was just wrong: some variants of SubLink always produce SubPlans.) As with previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16213-871ac3bc208ecf23@postgresql.org https://git.postgresql.org/pg/commitdiff/41c6f9db25b5e3a8bb8afbb7d6715cff541fd41e - Doc: rearrange the documentation of binary-string functions. Rather than intermixing the discussion of text-string and binary-string functions, make a clean break, moving all discussion of binary-string operations into section 9.5. This involves some duplication of function descriptions between 9.4 and 9.5, but it seems cleaner on the whole since the individual descriptions are clearer (and on the other side of the coin, it gets rid of some duplicated descriptions, too). Move the convert*/encode/decode functions to a separate table, because they don't quite seem to fit under the heading of "binary string functions". Also provide full documentation of the textual formats supported by encode() and decode() (which was the original goal of this patch series, many moons ago). Also move the table of built-in encoding conversions out of section 9.4, where it no longer had any relevance whatsoever, and put it into section 23.3 about character sets. I chose to put both that and table 23.2 (multibyte-translation-table) into a new <sect2> so as not to break up the flow of discussion in 23.3.3. Also do a bunch of minor copy-editing on the function descriptions in 9.4 and 9.5. Karl Pinc, reviewed by Fabien Coelho, further hacking by me Discussion: https://postgr.es/m/20190304163347.7bca4897@slate.meme.com https://git.postgresql.org/pg/commitdiff/34a0a81bfb388504deaa51b16a8bb531b827e519 Tomáš Vondra pushed: - Apply all available functional dependencies. When considering functional dependencies during selectivity estimation, it's not necessary to bother with selecting the best extended statistic object and then use just dependencies from it. We can simply consider all applicable functional dependencies at once. This means we need to deserialie all (applicable) dependencies before applying them to the clauses. This is a bit more expensive than picking the best statistics and deserializing dependencies for it. To minimize the additional cost, we ignore statistics that are not applicable. Author: Tomas Vondra Reviewed-by: Mark Dilger Discussion: https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development https://git.postgresql.org/pg/commitdiff/aaa6761876ba5b06a5c3fa914b2951ace1e31dee - Apply multiple multivariate MCV lists when possible. Until now we've only used a single multivariate MCV list per relation, covering the largest number of clauses. So for example given a query SELECT * FROM t WHERE a = 1 AND b =1 AND c = 1 AND d = 1 and extended statistics on (a,b) and (c,d), we'd only pick and use one of them. This commit improves this by repeatedly picking and applying the best statistics (matching the largest number of remaining clauses) until no additional statistics is applicable. This greedy algorithm is simple, but may not be optimal. A different choice of statistics may leave fewer clauses unestimated and/or give better estimates for some other reason. This can however happen only when there are overlapping statistics, and selecting one makes it impossible to use the other. E.g. with statistics on (a,b), (c,d), (b,c,d), we may pick either (a,b) and (c,d) or (b,c,d). But it's not clear which option is the best one. We however assume cases like this are rare, and the easiest solution is to define statistics covering the whole group of correlated columns. In the future we might support overlapping stats, using some of the clauses as conditions (in conditional probability sense). Author: Tomas Vondra Reviewed-by: Mark Dilger, Kyotaro Horiguchi Discussion: https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development https://git.postgresql.org/pg/commitdiff/eae056c19ee8f5ebc45ac0fe13181f91c8791e00 - Allocate freechunks bitmap as part of SlabContext. The bitmap used by SlabCheck to cross-check free chunks in a block used to be allocated for each SlabCheck call, and was never freed. The memory leak could be fixed by simply adding a pfree call, but it's actually a bad idea to do any allocations in SlabCheck at all as it assumes the state of the memory management as a whole is sane. So instead we allocate the bitmap as part of SlabContext, which means we don't need to do any allocations in SlabCheck and the bitmap goes away together with the SlabContext. Backpatch to 10, where the Slab context was introduced. Author: Tomas Vondra Reported-by: Andres Freund Reviewed-by: Tom Lane Backpatch-through: 10 Discussion: https://www.postgresql.org/message-id/20200116044119.g45f7pmgz4jmodxj%40alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/543852fd8bf0adc56192aeb25ff83f1a12c30368 Amit Kapila pushed: - Delete empty pages in each pass during GIST VACUUM. Earlier, we use to postpone deleting empty pages till the second stage of vacuum to amortize the cost of scanning internal pages. However, that can sometimes (say vacuum is canceled or errored between first and second stage) delay the pages to be recycled. Another thing is that to facilitate deleting empty pages in the second stage, we need to share the information about internal and empty pages between different stages of vacuum. It will be quite tricky to share this information via DSM which is required for the upcoming parallel vacuum patch. Also, it will bring the logic to reclaim deleted pages closer to nbtree where we delete empty pages in each pass. Overall, the advantages of deleting empty pages in each pass outweigh the advantages of postponing the same. Author: Dilip Kumar, with changes by Amit Kapila Reviewed-by: Sawada Masahiko and Amit Kapila Discussion: https://postgr.es/m/CAA4eK1LGr+MN0xHZpJ2dfS8QNQ1a_aROKowZB+MPNep8FVtwAA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4e514c6180fbf71cf7a0171867c828c63afd1c37 - Fix typo. Reported-by: Antonin Houska Author: Antonin Houska Backpatch-through: 11, where it was introduced Discussion: https://postgr.es/m/2246.1578900133@antos https://git.postgresql.org/pg/commitdiff/23d0dfa8fa016f7f8af25b1040d7a55ba77da6fc - Revert test added by commit d207038053. This test was trying to test the mechanism to release kernel FDs as needed to get us under the max_safe_fds limit in case of spill files. To do that, it needs to set max_files_per_process to a very low value which doesn't even permit starting of the server in the case when there are a few already opened files. This test also won't work on platforms where we use one FD per semaphore. Backpatch-through: 10, till where this test was added Discussion: https://postgr.es/m/CAA4eK1LHhERi06Q+MmP9qBXBBboi+7WV3910J0aUgz71LcnKAw@mail.gmail.com https://postgr.es/m/6485.1578583522@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ed7bb5c311ce059294807cf4abfa9406d95feec0 - Introduce IndexAM fields for parallel vacuum. Introduce new fields amusemaintenanceworkmem and amparallelvacuumoptions in IndexAmRoutine for parallel vacuum. The amusemaintenanceworkmem tells whether a particular IndexAM uses maintenance_work_mem or not. This will help in controlling the memory used by individual workers as otherwise, each worker can consume memory equal to maintenance_work_mem. The amparallelvacuumoptions tell whether a particular IndexAM participates in a parallel vacuum and if so in which phase (bulkdelete, vacuumcleanup) of vacuum. Author: Masahiko Sawada and Amit Kapila Reviewed-by: Dilip Kumar, Amit Kapila, Tomas Vondra and Robert Haas Discussion: https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com https://postgr.es/m/CAA4eK1LmcD5aPogzwim5Nn58Ki+74a6Edghx4Wd8hAskvHaq5A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4d8a8d0c738410ec02aab46b1ebe1835365ad384 Andrew Dunstan pushed: - Only superuser can set sslcert/sslkey in postgres_fdw user mappings. Othrwise there is a security risk. Discussion: https://postgr.es/m/20200109103014.GA4192@msg.df7cb.de https://git.postgresql.org/pg/commitdiff/cebf9d6e6ee13cbf9f1a91ec633cf96780ffc985 - Add a non-strict version of jsonb_set. jsonb_set_lax() is the same as jsonb_set, except that it takes and extra argument that specifies what to do if the value argument is NULL. The default is 'use_json_null'. Other possibilities are 'raise_exception', 'return_target' and 'delete_key', all these behaviours having been suggested as reasonable by various users. Discussion: https://postgr.es/m/375873e2-c957-3a8d-64f9-26c43c2b16e7@2ndQuadrant.com Reviewed by: Pavel Stehule https://git.postgresql.org/pg/commitdiff/a83586b5543b948f9e621462537a7303b113c482 - bump catalog version as should have been done for jsonb_set_lax. https://git.postgresql.org/pg/commitdiff/4b0e0f67f2f16c077b4e5c8a3d70c6af4355db09 Michaël Paquier pushed: - Fix comment in heapam.c. Improvement per suggestion from Tom Lane. Author: Daniel Gustafsson Discussion: https://postgr.es/m/FED18699-4270-4778-8DA8-10F119A5ECF3@yesql.se https://git.postgresql.org/pg/commitdiff/7689d907bbb177fa2a8f5aca3f968761dd16bf28 - Fix buggy logic in isTempNamespaceInUse(). The logic introduced in this routine as of 246a6c8 would report an incorrect result when a session calls it to check if the temporary namespace owned by the session is in use or not. It is possible to optimize more the routine in this case to avoid a PGPROC lookup, but let's keep the logic simple. As this routine is used only by autovacuum for now, there were no live bugs, still let's be correct for any future code involving it. Author: Michael Paquier Reviewed-by: Julien Rouhaud Discussion: https://postgr.es/m/20200113093703.GA41902@paquier.xyz Backpatch-through: 11 https://git.postgresql.org/pg/commitdiff/ac5bdf62617507b1942f6124a2696c04a16fca04 - Move OpenSSL routines for min/max protocol setting to src/common/. Two routines have been added in OpenSSL 1.1.0 to set the protocol bounds allowed within a given SSL context: - SSL_CTX_set_min_proto_version - SSL_CTX_set_max_proto_version As Postgres supports OpenSSL down to 1.0.1 (as of HEAD), equivalent replacements exist in the tree, which are only available for the backend. A follow-up patch is planned to add control of the SSL protocol bounds for libpq, so move those routines to src/common/ so as libpq can use them. Author: Daniel Gustafsson Discussion: https://postgr.es/m/4F246AE3-A7AE-471E-BD3D-C799D3748E03@yesql.se https://git.postgresql.org/pg/commitdiff/f7cd5896a69621818189fbdd209fb2e1fc008102 - Add GUC checks for ssl_min_protocol_version and ssl_max_protocol_version. Mixing incorrect bounds set in the SSL context leads to confusing error messages generated by OpenSSL which are hard to act on. New checks are added within the GUC machinery to improve the user experience as they apply to any SSL implementation, not only OpenSSL, and doing the checks beforehand avoids the creation of a SSL during a reload (or startup) which we know will never be used anyway. Backpatch down to 12, as those parameters have been introduced by e73e67c. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/20200114035420.GE1515@paquier.xyz Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/41aadeeb124ee5f8e7d154a16a74d53286882b74 - Doc: Improve description of connection strings with Percent-encoding. Clarify the description related to the use of characters which can be encoded, and add an example. Author: Jobin Augustine Reviewed-by: Peter Eisentraut, Alvaro Herrera, Heikki Linnakangas, Michael Paquier, Alex Shulgin Discussion: https://postgr.es/m/CANaTPsrYgSgE2fuj3=4x=Jmx1c+NgkEDzftNknZbrMuqL+aBhQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e0ed6817c0ee218a3681920807404603e042ff04 Peter Eisentraut pushed: - Fix base backup with database OIDs larger than INT32_MAX. The use of pg_atoi() for parsing a string into an Oid fails for values larger than INT32_MAX, since OIDs are unsigned. Instead, use atooid(). While this has less error checking, the contents of the data directory are expected to be trustworthy, so we don't need to go out of our way to do full error checking. Discussion: https://www.postgresql.org/message-id/flat/dea47fc8-6c89-a2b1-07e3-754ff1ab094b%402ndquadrant.com https://git.postgresql.org/pg/commitdiff/259bbe177808986e5d226ea7ce5a1ebb74657791 - ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION. Add an ALTER TABLE subcommand for dropping the generated property from a column, per SQL standard. Reviewed-by: Sergei Kornilov <sk@zsrv.org> Discussion: https://www.postgresql.org/message-id/flat/2f7f1d9c-946e-0453-d841-4f38eb9d69b6%402ndquadrant.com https://git.postgresql.org/pg/commitdiff/f595117e24a79db6072979ab5a757431fd17232f - walreceiver uses a temporary replication slot by default. If no permanent replication slot is configured using primary_slot_name, the walreceiver now creates and uses a temporary replication slot. A new setting wal_receiver_create_temp_slot can be used to disable this behavior, for example, if the remote instance is out of replication slots. Reviewed-by: Masahiko Sawada <masahiko.sawada@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/CA%2Bfd4k4dM0iEPLxyVyme2RAFsn8SUgrNtBJOu81YqTY4V%2BnqZA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/329730827848f61eb8d353d5addcbd885fa823da - Expose PQbackendPID() through walreceiver API. This will be used by a subsequent patch. Reviewed-by: Masahiko Sawada <masahiko.sawada@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/CA%2Bfd4k4dM0iEPLxyVyme2RAFsn8SUgrNtBJOu81YqTY4V%2BnqZA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/ee4ac46c8eb289bc200d0fd682e75d11b4c55b5a - Fix compiler warning about format on Windows. On 64-bit Windows, pid_t is long long int, so a %d format isn't enough. https://git.postgresql.org/pg/commitdiff/fe233366f2e3ca44609c805ce0604c1e2122c3f9 - Remove libpq.rc, use win32ver.rc for libpq. For historical reasons, libpq used a separate libpq.rc file for the Windows builds while all other components use a common file win32ver.rc. With a bit of tweaking, the libpq build can also use the win32ver.rc file. This removes a bit of duplicative code. Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Reviewed-by: Michael Paquier <michael@paquier.xyz> Discussion: https://www.postgresql.org/message-id/flat/ad505e61-a923-e114-9f38-9867d161073f@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/16a4a3d59cd5574fdc697ea16ef5692ce34c54d5 Dean Rasheed pushed: - Make rewriter prevent auto-updates on views with conditional INSTEAD rules. A view with conditional INSTEAD rules and no unconditional INSTEAD rules or INSTEAD OF triggers is not auto-updatable. Previously we relied on a check in the executor to catch this, but that's problematic since the planner may fail to properly handle such a query and thus return a particularly unhelpful error to the user, before reaching the executor check. Instead, trap this in the rewriter and report the correct error there. Doing so also allows us to include more useful error detail than the executor check can provide. This doesn't change the existing behaviour of updatable views; it merely ensures that useful error messages are reported when a view isn't updatable. Per report from Pengzhou Tang, though not adopting that suggested fix. Back-patch to all supported branches. Discussion: https://postgr.es/m/CAG4reAQn+4xB6xHJqWdtE0ve_WqJkdyCV4P=trYr4Kn8_3_PEA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d751ba523546df2b2709c1ffd4d12d6a25e25bf6 Bruce Momjian pushed: - tools/copyright.pl: skip copyright changes for *.key files. Reported-by: Alvaro Herrera Discussion: https://postgr.es/m/20200102184059.GA25435@alvherre.pgsql Backpatch-through: master https://git.postgresql.org/pg/commitdiff/7316f11be03863858fa7acb5eee97ec35c91fca4 - Revert copyright script changes to binary *.key files. This reverts part of commit 7559d8ebfa. The copyright script has already been updated to skip *.key files. Reported-by: Alvaro Herrera Discussion: https://postgr.es/m/20200102184059.GA25435@alvherre.pgsql Backpatch-through: master https://git.postgresql.org/pg/commitdiff/344c26915136fa54c4a1bf54074ba15090b32721 - docs: change "default role" wording to "predefined role". The new wording was determined to be more accurate. Also, update release note links that reference these sections. Reported-by: rirans@comcast.net Discussion: https://postgr.es/m/157742545062.1149.11052653770497832538@wrigleys.postgresql.org Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/0e936a2148472e6c364aee8c3e298dc16dc4240a Álvaro Herrera pushed: - Report progress of ANALYZE commands. This uses the progress reporting infrastructure added by c16dc1aca5e0, adding support for ANALYZE. Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Co-authored-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> Reviewed-by: Julien Rouhaud, Robert Haas, Anthony Nowocien, Kyotaro Horiguchi, Vignesh C, Amit Langote https://git.postgresql.org/pg/commitdiff/a166d408eb0b35023c169e765f4664c3b114b52e - Set ReorderBufferTXN->final_lsn more eagerly. ... specifically, set it incrementally as each individual change is spilled down to disk. This way, it is set correctly when the transaction disappears without trace, ie. without leaving an XACT_ABORT wal record. (This happens when the server crashes midway through a transaction.) Failing to have final_lsn prevents ReorderBufferRestoreCleanup() from working, since it needs the final_lsn in order to know the endpoint of its iteration through spilled files. Commit df9f682c7bf8 already tried to fix the problem, but it didn't set the final_lsn in all cases. Revert that, since it's no longer needed. Author: Vignesh C Reviewed-by: Amit Kapila, Dilip Kumar Discussion: https://postgr.es/m/CALDaNm2CLk+K9JDwjYST0sPbGg5AQdvhUt0jbKyX_HdAE0jk3A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/15cac3a523cc06dba1331635f3f67445fa202a44 Robert Haas pushed: - Fix problems with "read only query" checks, and refactor the code. Previously, check_xact_readonly() was responsible for determining which types of queries could not be run in a read-only transaction, standard_ProcessUtility() was responsibility for prohibiting things which were allowed in read only transactions but not in recovery, and utility commands were basically prohibited in bulk in parallel mode by calls to CommandIsReadOnly() in functions.c and spi.c. This situation was confusing and error-prone. Accordingly, move all the checks to a new function ClassifyUtilityCommandAsReadOnly(), which determines the degree to which a given statement is read only. In the old code, check_xact_readonly() inadvertently failed to handle several statement types that actually should have been prohibited, specifically T_CreatePolicyStmt, T_AlterPolicyStmt, T_CreateAmStmt, T_CreateStatsStmt, T_AlterStatsStmt, and T_AlterCollationStmt. As a result, thes statements were erroneously allowed in read only transactions, parallel queries, and standby operation. Generally, they would fail anyway due to some lower-level error check, but we shouldn't rely on that. In the new code structure, future omissions of this type should cause ClassifyUtilityCommandAsReadOnly() to complain about an unrecognized node type. As a fringe benefit, this means we can allow certain types of utility commands in parallel mode, where it's safe to do so. This allows ALTER SYSTEM, CALL, DO, CHECKPOINT, COPY FROM, EXPLAIN, and SHOW. It might be possible to allow additional commands with more work and thought. Along the way, document the thinking process behind the current set of checks, as per discussion especially with Peter Eisentraut. There is some interest in revising some of these rules, but that seems like a job for another patch. Patch by me, reviewed by Tom Lane, Stephen Frost, and Peter Eisentraut. Discussion: http://postgr.es/m/CA+TgmoZ_rLqJt5sYkvh+JpQnfX0Y+B2R+qfi820xNih6x-FQOQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2eb34ac369741c110b593e2dc2195c57d29ab8e8 Alexander Korotkov pushed: - Avoid full scan of GIN indexes when possible. The strategy of GIN index scan is driven by opclass-specific extract_query method. This method that needed search mode is GIN_SEARCH_MODE_ALL. This mode means that matching tuple may contain none of extracted entries. Simple example is '!term' tsquery, which doesn't need any term to exist in matching tsvector. In order to handle such scan key GIN calculates virtual entry, which contains all TIDs of all entries of attribute. In fact this is full scan of index attribute. And typically this is very slow, but allows to handle some queries correctly in GIN. However, current algorithm calculate such virtual entry for each GIN_SEARCH_MODE_ALL scan key even if they are multiple for the same attribute. This is clearly not optimal. This commit improves the situation by introduction of "exclude only" scan keys. Such scan keys are not capable to return set of matching TIDs. Instead, they are capable only to filter TIDs produced by normal scan keys. Therefore, each attribute should contain at least one normal scan key, while rest of them may be "exclude only" if search mode is GIN_SEARCH_MODE_ALL. The same optimization might be applied to the whole scan, not per-attribute. But that leads to NULL values elimination problem. There is trade-off between multiple possible ways to do this. We probably want to do this later using some cost-based decision algorithm. Discussion: https://postgr.es/m/CAOBaU_YGP5-BEt5Cc0%3DzMve92vocPzD%2BXiZgiZs1kjY0cj%3DXBg%40mail.gmail.com Author: Nikita Glukhov, Alexander Korotkov, Tom Lane, Julien Rouhaud Reviewed-by: Julien Rouhaud, Tomas Vondra, Tom Lane https://git.postgresql.org/pg/commitdiff/4b754d6c16e16cc1a1adf12ab0f48603069a0efd Heikki Linnakangas pushed: - Remove separate files for the initial contents of pg_(sh)description. This data was only in separate files because it was the most convenient way to handle it with a shell script. Now that we use a general-purpose programming language, it's easy to assemble the data into the same format as the rest of the catalogs and output it into postgres.bki. This allows removal of some special-purpose code from initdb.c. Discussion: https://www.postgresql.org/message-id/CACPNZCtVFtjHre6hg9dput0qRPp39pzuyA2A6BT8wdgrRy%2BQdA%40mail.gmail.com Author: John Naylor https://git.postgresql.org/pg/commitdiff/7aaefadaac6452b2e813fae4ea531cb12d022531 == Pending Patches == Justin Pryzby sent in a patch to clarify the way VACUUM VERBOSE is logged. Justin Pryzby sent in another revision of a patch to use the correlation statistic in costing bitmap scans. KaiGai Kohei sent in another revision of a patch to add a path_removal_decision_hook. Krasiyan Andreev sent in another revision of a patch to allow distinct aggregates within a window function. Anastasia Lubennikova sent in another revision of a patch to add support functions for btreeopclasses. Álvaro Herrera sent in another revision of a patch to pg_dump to add FOREIGN to ALTER statements, if appropriate. Juan José Santamaría Flecha sent in two more revisions of a patch to allow to_date() and to_timestamp() to accept localized names. Michaël Paquier sent in a patch to fix isTempNamespaceInUse()'s handling of MyBackendId. Amit Kapila and Masahiko Sawada traded patches to implement block-level parallel vacuum. Tom Lane sent in another revision of a patch to fix psql's tab completion of filenames. Kyotaro HORIGUCHI sent in another revision of a patch to add a catcache expiration feature. Dilip Kumar sent in another revision of a patch to fix an infelicity between logical_work_mem and logical streaming of large in-progress transactions. Tomáš Vondra sent in a patch to improve the estimates of OR clauses, support estimating clauses (Var op Var), and support extended statistics on expressions. Nikita Glukhov sent in another revision of a patch to add support for JSON_TABLE. Andres Freund sent in a patch to update stringinfo to move more functions inline, provide initStringInfoEx(), and remove in-core use of appendStringInfoCharMacro(), update pqformat by moving functions for type sending inline, and adding pq_begintypsend_ex(), annotating palloc() with malloc and other compiler attributes, change {int4,int8}send to use pq_begintypsend_ex, and make copy use appendBinaryStringInfoNT() for sending binary data. Peter Eisentraut sent in another revision of a patch to fail if the PITR recovery target is not reached. Tom Lane sent in two revisions of a patch to remove the restriction that the server encoding must be UTF-8 in order to write any Unicode escape with a value outside the ASCII range. Daniel Gustafsson and Michaël Paquier traded patches to set min/max TLS protocol in clientside libpq. KaiGai Kohei sent in another revision of a patch to implement TRUNCATE on foreign tables. Alexander Korotkov and Tom Lane traded patches to avoid full scan of GIN indexes when possible. Michaël Paquier sent in two revisions of a patch to improve errors when setting incorrect bounds for SSL protocols. David Fetter sent in three revisions of a patch to use compiler intrinsics for bit ops in hash. Kyotaro HORIGUCHI sent in three more revisions of a patch to rework the WAL-skipping optimization. Peter Geoghegan sent in another revision of a patch to add deduplication to nbtree. Paul Guo sent in another revision of a patch to support node initialization from backup with tablespaces, add tests to replay create database operations on standbys, and fix the replay of create database records on standbys. Imai Yoshikazu sent in another revision of a patch to add pg_stat_waitaccum view and change the measuring method of wait event time from INSTR_TIME to rdtsc. Etsuro Fujita sent in another revision of a patch to improve the partition-matching algorithm for partition-wise joins. Peter Eisentraut sent in another revision of a patch to make the base backup client an auxiliary backend process. Maciek Sakrejda sent in another revision of a patch to merge explain worker output. Robert Haas sent in two revisions of a patch to make the backend JSON parser work with front-end code by adjusting src/include/utils/jsonapi.h so it's not backend-only, splitting the JSON lexer/parser from 'json' data type support, removing jsonapi.c's lex_accept(), and returning errors rather than using ereport(). Andres Freund sent in another revision of a patch to fix an aggregate crash. Michaël Paquier sent in another revision of a patch to adjust dropping temp schemas. Justin Pryzby sent in a patch to add a doc review for ANALYZE progress. Antonin Houska sent in another revision of a patch to introduce a RelInfoList structure and use same to implement aggregate push-down. Aleksey Kondratov sent in another revision of a patch to make physical slot advance persistent. Amit Khandekar sent in another revision of a patch to implement minimal logical decoding on standbys. Fujii Masao sent in another revision of a patch to ignore invalid pages. Fujii Masao sent in another revision of a patch to add pg_file_sync() to adminpack. Asim R P sent in two revisions of a patch to fix an unnecessary delay in streaming replication due to replay lag. Tom Lane sent in another revision of a patch to fix for hashed subplan in VALUES. Heikki Linnakangas sent in another revision of a patch to remove the page-read callback from XLogReaderState. David Fetter sent in another revision of a patch to enable setting pg_hba.conf permissions from initdb. Melanie Plageman sent in a patch to show parallel leader stats in EXPLAIN output. Pavel Stěhule sent in another revision of a patch to introduce schema variables. Paul A Jungwirth sent in another revision of a patch to implement multirange and friends. Nikita Glukhov sent in another revision of a patch to implement SQL/JSON functions. Alexander Korotkov sent in another revision of a patch to pg_rewind to add options to restore WAL files from archives. Justin Pryzby sent in a patch to document that checkpoint_flush_after applies to end-of-recovery checkpoint, and avoid an ambiguous "that". Daniel Gustafsson sent in another revision of a patch to make it possible to enable checksums online. Richard Guo sent in another revision of a patch to fix up partition-wise join. Richard Guo sent in another revision of a patch to implement parallel grouping sets. Tomáš Vondra sent in a patch to add views of SLRU statistics. 曾文旌(义从) sent in two more revisions of a patch to implement global temporary tables.
В списке pgsql-announce по дате отправления: