== PostgreSQL Weekly News - December 30, 2018 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - December 30, 2018 == |
| Дата | |
| Msg-id | 20181230222149.GA26469@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - December 30, 2018 == == PostgreSQL Product News == pgBadger v10.2, a PostgreSQL log analyzer and graph tool written in Perl, released. https://github.com/dalibo/pgbadger/releases/ == PostgreSQL Jobs for December == http://archives.postgresql.org/pgsql-jobs/2018-12/ == PostgreSQL Local == FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will be held in Brussels, Belgium, on Feb 1st, 2019. https://2019.fosdempgday.org/ Prague PostgreSQL Developer Day 2019 (P2D2 2019) is a two-day conference that will be held on February 13-14, 2019 in Prague, Czech Republic. The CfP is open until January 4, 2018 at https://p2d2.cz/callforpapers http://www.p2d2.cz/ PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka. http://pgconf.in/ pgDay Paris 2019 will be held in Paris, France on March 12, 2019 at 199bis rue Saint-Martin. http://2019.pgday.paris/ PGConf APAC 2019 will be held in Singapore March 19-21, 2019. http://2019.pgconfapac.org/ The German-speaking PostgreSQL Conference 2019 will take place on May 10, 2019 in Leipzig. The CfP is open until February 26, 2019 at http://2019.pgconf.de/cfp http://2019.pgconf.de/ PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. Both the CfP https://2019.pgday.it/en/blog/cfp and the Call for Workshops https://2019.pgday.it/en/blog/cfw are openuntil January 15, 2019. https://2019.pgday.it/en/ PGCon 2019 will take place in Ottawa on May 28-31, 2019. The CfP is open through January 19, 2019 at http://www.pgcon.org/2019/papers.php https://www.pgcon.org/2018/schedule/ Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019. The CfP is open January 17, 2019 through April 18, 2019, and registration will open January 17, 2019. http://www.pgday.ch/2019/ == 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 == Michaël Paquier pushed: - Prioritize history files when archiving. At the end of recovery for the post-promotion process, a new history file is created followed by the last partial segment of the previous timeline. Based on the timing, the archiver would first try to archive the last partial segment and then the history file. This can delay the detection of a new timeline taken, particularly depending on the time it takes to transfer the last partial segment as it delays the moment the history file of the new timeline gets archived. This can cause promoted standbys to use the same timeline as one already taken depending on the circumstances if multiple instances look at archives at the same location. This commit changes the order of archiving so as history files are archived in priority over other file types, which reduces the likelihood of the same timeline being taken (still not reducing the window to zero), and it makes the archiver behave more consistently with the startup process doing its post-promotion business. Author: David Steele Reviewed-by: Michael Paquier, Kyotaro Horiguchi Discussion: https://postgr.es/m/929068cf-69e1-bba2-9dc0-e05986aed471@pgmasters.net Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/b981df4cc09aca978c5ce55e437a74913d09cccc - Improve tab completion of ALTER INDEX/TABLE with SET STATISTICS in psql. This fixes two issues with the completion of ALTER TABLE and ALTER INDEX after SET STATISTICS is typed, trying to suggest schema objects while the grammar only allows integers. The tab completion of ALTER INDEX is made smarter by handling properly more patterns. COLUMN is an optional keyword, but as no column numbers can be suggested yet as possible input simply adjust the completion so as no incorrect queries are generated. Author: Michael Paquier Reviewed-by: Tatsuro Yamada Discussion: https://postgr.es/m/20181219092255.GC680@paquier.xyz https://git.postgresql.org/pg/commitdiff/f89ae34ab8b4d9e9ce8af6bd889238b0ccff17cb - Ignore inherited temp relations from other sessions when truncating. Inheritance trees can include temporary tables if the parent is permanent, which makes possible the presence of multiple temporary children from different sessions. Trying to issue a TRUNCATE on the parent in this scenario causes a failure, so similarly to any other queries just ignore such cases, which makes TRUNCATE work transparently. This makes truncation behave similarly to any other DML query working on the parent table with queries which need to be work on the children. A set of isolation tests is added to cover basic cases. Reported-by: Zhou Digoal Author: Amit Langote, Michael Paquier Discussion: https://postgr.es/m/15565-ce67a48d0244436a@postgresql.org Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/1e504f01da11db0181d7b28bb30cb5eeb0767184 - Clarify referential actions in docs of CREATE/ALTER TABLE. The documentation of ON DELETE and ON UPDATE uses the term "action", which is also used on the ALTER TABLE page for other purposes. This commit renames the term to "referential_action", which is more consistent with the SQL specification. The new term is now used on the documentation of both CREATE TABLE and ALTER TABLE for consistency. Reported-by: Brigitte Blanc-Lafay Author: Lætitia Avrot Reviewed-by: Álvaro Herrera Discussion: https://postgr.es/m/CAB_COdiHEVVs0uB+uYCjjYUwQ4YFFekppq+Xqv6qAM8+cd42gA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f7ea1a42337f45efed7c4d44f50f62a55e60d30c - Improve description of DEFAULT_XLOG_SEG_SIZE in pg_config.h. This was incorrectly referring to --walsegsize, and its description is rewritten in a clearer way. Author: Ian Barwick, Tom Lane Reviewed-by: Álvaro Herrera, Michael Paquier Discussion: https://postgr.es/m/08534fc6-119a-c498-254e-d5acc4e6bf85@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/0a5a493f042b30afe350763820dc79bb3e6df91a - Trigger stmt_beg and stmt_end for top-level statement blocks of PL/pgSQL. PL/pgSQL provides a set of callbacks which can be used for extra instrumentation of functions written in this language called at function setup, begin and end, as well as statement begin and end. When calling a routine, a trigger, or an event trigger, statement callbacks are not getting called for the top-level statement block leading to an inconsistent handling compared to the other statements. This inconsistency can potentially complicate extensions doing instrumentation work on top of PL/pgSQL, so this commit makes sure that all statement blocks, including the top-level one, go through the correct corresponding callbacks. Author: Pavel Stehule Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/CAFj8pRArEANsaUjo5in9_iQt0vKf9ecwDAmsdN_EBwL13ps12A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e0ef136d5235f39f5652c209c08a5d4322560e9c Tom Lane pushed: - Fix portability failure introduced in commits d2b0b60e7 et al. I made a frontend fprintf() format use %m, forgetting that that's only safe in HEAD not the back branches; prior to 96bf88d52 and d6c55de1f, it would work on glibc platforms but not elsewhere. Revert to using %s ... strerror(errno) as the code did before. We could have left HEAD as-is, but for code consistency across branches, I chose to apply this patch there too. Per Coverity and a few buildfarm members. https://git.postgresql.org/pg/commitdiff/e9fcfed3fb6b15e6b33bea9bb8504b14560857a8 - Fix failure to check for open() or fsync() failures. While it seems OK to not be concerned about fsync() failure for a pre-existing signal file, it's not OK to not even check for open() failure. This at least causes complaints from static analyzers, and I think on some platforms passing -1 to fsync() or close() might trigger assertion-type failures. Also add (void) casts to make clear that we're ignoring fsync's result intentionally. Oversights in commit 2dedf4d9a, noted by Coverity. https://git.postgresql.org/pg/commitdiff/8528e3d849a896f8711c56fb41eae56f8c986729 - Fix latent problem with pg_jrand48(). POSIX specifies that jrand48() returns a signed 32-bit value (in the range [-2^31, 2^31)), but our code was returning an unsigned 32-bit value (in the range [0, 2^32)). This doesn't actually matter to any existing call site, because they all cast the "long" result to int32 or uint32; but it will doubtless bite somebody in the future. To fix, cast the arithmetic result to int32 explicitly before the compiler widens it to long (if widening is needed). While at it, upgrade this file's far-short-of-project-style comments. Had there been some peer pressure to document pg_jrand48() properly, maybe this thinko wouldn't have gotten committed to begin with. Backpatch to v10 where pg_jrand48() was added, just in case somebody back-patches a fix that uses it and depends on the standard behavior. Discussion: https://postgr.es/m/17235.1545951602@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e09046641114dc5ab038530019bc35b04c2976e7 - Marginal performance hacking in erand48.c. Get rid of the multiplier and addend variables in favor of hard-wired constants. Do the multiply-and-add using uint64 arithmetic, rather than manually combining several narrower multiplications and additions. Make _dorand48 return the full-width new random value, and have its callers use that directly (after suitable masking) rather than reconstructing what they need from the unsigned short[] representation. On my machine, this is good for a nearly factor-of-2 speedup of pg_erand48(), probably mostly from needing just one call of ldexp() rather than three. The wins for the other functions are smaller but measurable. While none of the existing call sites are really performance-critical, a cycle saved is a cycle earned; and besides the machine code is smaller this way (at least on x86_64). Patch by me, but the original idea to optimize this by switching to int64 arithmetic is from Fabien Coelho. Discussion: https://postgr.es/m/1551.1546018192@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/6b9bba2df8d469a13bf8f0b9eb6933c7eaaff2c1 - Use pg_strong_random() to select each server process's random seed. Previously we just set the seed based on process ID and start timestamp. Both those values are directly available within the session, and can be found out or guessed by other users too, making the session's series of random(3) values fairly predictable. Up to now, our backend-internal uses of random(3) haven't seemed security-critical, but commit 88bdbd3f7 added one that potentially is: when using log_statement_sample_rate, a user might be able to predict which of his SQL statements will get logged. To improve this situation, upgrade the per-process seed initialization method to use pg_strong_random() if available, greatly reducing the predictability of the initial seed value. This adds a few tens of microseconds to process start time, but since backend startup time is at least a couple of milliseconds, that seems an acceptable price. This means that pg_strong_random() needs to be able to run without reliance on any backend infrastructure, since it will be invoked before any of that is up. It was safe for that already, but adjust comments and #include commands to make it clearer. Discussion: https://postgr.es/m/3859.1545849900@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4203842a1cd06a7c30fd3eaae1c111928909e539 - Use a separate random seed for SQL random()/setseed() functions. Previously, the SQL random() function depended on libc's random(3), and setseed() invoked srandom(3). This results in interference between these functions and backend-internal uses of random(3). We'd never paid too much mind to that, but in the wake of commit 88bdbd3f7 which added log_statement_sample_rate, the interference arguably has a security consequence: if log_statement_sample_rate is active then an unprivileged user could probably control which if any of his SQL commands get logged, by issuing setseed() at the right times. That seems bad. To fix this reliably, we need random() and setseed() to use their own private random state variable. Standard random(3) isn't amenable to such usage, so let's switch to pg_erand48(). It's hard to say whether that's more or less "random" than any particular platform's version of random(3), but it does have a wider seed value and a longer period than are required by POSIX, so we can hope that this isn't a big downgrade. Also, we should now have uniform behavior of random() across platforms, which is worth something. While at it, upgrade the per-process seed initialization method to use pg_strong_random() if available, greatly reducing the predictability of the initial seed value. (I'll separately do something similar for the internal uses of random().) In addition to forestalling the possible security problem, this has a benefit in the other direction, which is that we can now document setseed() as guaranteeing a reproducible sequence of random() values. Previously, because of the possibility of internal calls of random(3), we could not promise any such thing. Discussion: https://postgr.es/m/3859.1545849900@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/6645ad6bdd81e7d5a764e0d94ef52fae053a9e13 - Teach eval_const_expressions to constant-fold LEAST/GREATEST expressions. Doing this requires an assumption that the invoked btree comparison function is immutable. We could check that explicitly, but in other places such as contain_mutable_functions we just assume that it's true, so we may as well do likewise here. (If the comparison function's behavior isn't immutable, the sort order in indexes built with it would be unstable, so it seems certainly wrong for it not to be so.) Vik Fearing Discussion: https://postgr.es/m/c6e8504c-4c43-35fa-6c8f-3c0b80a912cc@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/6f19a8c41f976236310a272bb646d3411759e18d - Support parameterized TidPaths. Up to now we've not worried much about joins where the join key is a relation's CTID column, reasoning that storing a table's CTIDs in some other table would be pretty useless. However, there are use-cases for this sort of query involving self-joins, so that argument doesn't really hold water. This patch allows generating plans for joins on CTID that use a nestloop with inner TidScan, similar to what we might do with an index on the join column. This is the most efficient way to join when the outer side of the nestloop is expected to yield relatively few rows. This change requires upgrading tidpath.c and the generated TidPaths to work with RestrictInfos instead of bare qual clauses, but that's long-postponed technical debt anyway. Discussion: https://postgr.es/m/17443.1545435266@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b5415e3c2187ab304390524f5ae66b4bd2c58279 - Add a hash opclass for type "tid". Up to now we've not worried much about joins where the join key is a relation's CTID column, reasoning that storing a table's CTIDs in some other table would be pretty useless. However, there are use-cases for this sort of query involving self-joins, so that argument doesn't really hold water. With larger relations, a merge or hash join is desirable. We had a btree opclass for type "tid", allowing merge joins on CTID, but no hash opclass so that hash joins weren't possible. Add the missing infrastructure. This also potentially enables hash aggregation on "tid", though the use-cases for that aren't too clear. Discussion: https://postgr.es/m/1853.1545453106@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0a6ea4001a9dff64e9ba66f68855a59a1bf69bc9 Alexander Korotkov pushed: - Remove entry tree root conflict checking from GIN predicate locking. According to README we acquire predicate locks on entry tree leafs and posting tree roots. However, when ginFindLeafPage() is going to lock leaf in exclusive mode, then it checks root for conflicts regardless whether it's a entry or posting tree. Assuming that we never place predicate lock on entry tree root (excluding corner case when root is leaf), this check is redundant. This commit removes this check. Now, root conflict checking is controlled by separate argument of ginFindLeafPage(). Discussion: https://postgr.es/m/CAPpHfdv7rrDyy%3DMgsaK-L9kk0AH7az0B-mdC3w3p0FSb9uoyEg%40mail.gmail.com Author: Alexander Korotkov Backpatch-through: 11 https://git.postgresql.org/pg/commitdiff/b450abd2551ee30b5bc289c662f5728d87e13a39 - Reduce length of GIN predicate locking isolation test suite. Isolation test suite of GIN predicate locking was criticized for being too slow, especially under Valgrind. This commit is intended to accelerate it. Tests are simplified in the following ways. 1) Amount of data is reduced. We're now close to the minimal amount of data, which produces at least one posting tree and at least two pages of entry tree. 2) Three isolation tests are merged into one. 3) Only one tuple is queried from posting tree. So, locking of index is the same, but tuple locks are not propagated to relation lock. Also, it is faster. 4) Test cases itself are simplified. Now each test case run just one INSERT and one SELECT involving GIN, which either conflict or not. Discussion: https://postgr.es/m/20181204000740.ok2q53nvkftwu43a%40alap3.anarazel.de Reported-by: Andres Freund Tested-by: Andrew Dunstan Author: Alexander Korotkov Backpatch-through: 11 https://git.postgresql.org/pg/commitdiff/0c6f4f9212cf3155b258910acf80fec89f49b767 Peter Eisentraut pushed: - pg_dump: Add missing newline to error message. https://git.postgresql.org/pg/commitdiff/5c828307973366f424438b848d4cca6ef98c032e - Remove obsolete IndexIs* macros. Remove IndexIsValid(), IndexIsReady(), IndexIsLive() in favor of accessing the index structure directly. These macros haven't been used consistently, and the original reason of maintaining source compatibility with PostgreSQL 9.2 is gone. Discussion: https://www.postgresql.org/message-id/flat/d419147c-09d4-6196-5d9d-0234b230880a%402ndquadrant.com https://git.postgresql.org/pg/commitdiff/ae4472c619341ff0517254d395d74796277622e6 - Remove redundant translation markers. psql_error() already handles that itself. https://git.postgresql.org/pg/commitdiff/e3299d36a938c7af386b240f318c7b9e55bdc92d - pg_rewind: Add missing newline to error message. https://git.postgresql.org/pg/commitdiff/1a4eba4e246480466c04feb81f76866e7dcb2827 - Change "checkpoint starting" message to use "wal". This catches up with the recent renaming of all user-facing mentions of "xlog" to "wal". Discussion: https://www.postgresql.org/message-id/flat/20181129084708.GA9562%40msg.credativ.de https://git.postgresql.org/pg/commitdiff/60d99797bfb07f8042a8b82c161500f6e4a500ec Álvaro Herrera pushed: - Rewrite ExecPartitionCheckEmitError for clarity. The original was hard to follow and failed to comply with DRY principle. Discussion: https://postgr.es/m/20181206222221.g5witbsklvqthjll@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/e8b0e6b82dbdb4459ec19f9871f691dfa7d1902c - Fix thinko in previous commit. https://git.postgresql.org/pg/commitdiff/4ed6c071b82a80fb7d8396477a5d698de3c93bf3 == Pending Patches == Alexander Kuzmenkov sent in another revision of a patch to remove unneeded self-joins. Noah Misch sent in another revision of a patch to fix a race to build pg_isolation_regress in "make -j check-world". Aleksey Kondratov sent in two more revisions of a patch to pg_rewind which adds options to use restore_command from postgresql.conf or from the command line. Ian Barwick sent in a patch to fill in some missing doc references on array and string functions. Aleksey Kondratov sent in a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly. Mitar sent in six revisions of a patch to add triggers to materialized views. David Rowley and Tomáš Vondra traded patches to speed up foreign-key-aware join estimation. Michaël Paquier sent in a patch to add flag to format_type_extended to enforce NULL-ness, refactor the format procedure and operator APIs to be more modular, and eliminate user-visible cache lookup errors for objaddr SQL functions. Iwata Aya sent in another revision of a patch to add a trace log for libpq. Corey Huinker sent in a patch to refactor per-row unique key deferred constraint triggers into per-statement triggers. Michael Banck sent in another revision of a patch to implement progress reporting for pg_verify_checksums. Yoshikazu Imai and Amit Langote traded patches to speed up planning with partitions. Fabien COELHO sent in a patch to make pg_dumpall's output more verbose. Mitar sent in four revisions of a patch to implement temporary materialized views. Michaël Paquier sent in a patch to clarify a comment in src/backend/access/transam/xlog.c in WaitForWALToBecomeAvailable(). Tomáš Vondra sent in another revision of a patch to implement multivariate histograms and MCV lists. Ryohei Nagaura sent in another revision of a patch to add TCP timeout parameters to both the backend and to libpq. Etsuro Fujita sent in two more revisions of a patch to the postgres_fdw which makes it perform both the UPPERREL_ORDERED and UPPERREL_FINAL steps remotely. Thomas Munro sent in a patch to add a shared_memory_type GUC and use it to support huge pages on AIX. Michaël Paquier sent in a patch to fix a failure to check for open() or fsync() failures. Hayato Kuroda sent in another revision of a patch to add DECLARE STATEMENT to ecpg. Evgeniy Efimkin sent in another revision of a patch to add a filter to CREATE SUBSCRIPTION. Dmitry Dolgov sent in another revision of a patch to implement generic type subscripting. Peter Eisentraut sent in two more revisions of a patch to add value 'current' for recovery_target_timeline and change the default of recovery_target_timeline to 'latest'. John Naylor sent in two more revisions of a patch to use offset-based keyword lookup and dispatch keyword lookup on the first character. Masahiko Sawada sent in another revision of a patch to implement block-level parallel VACUUM. Thomas Munro sent in another revision of a patch to implement a synchronous replay mode for avoiding stale reads on hot standbys. Tatsuro Yamada sent in another revision of a patch to implement a CLUSTER command progress monitor. Etsuro Fujita sent in a patch to fix the PostgreSQL FDW grouping path cost estimation. Peter Eisentraut sent in another revision of a patch to implement collations with nondeterministic comparison, one use case being case-insensitive collations. Surafel Temesgen sent in another revision of a patch to enable pg_dump's --inserts option to use multi-values INSERTs. Dimitri Fontaine sent in two revisions of a patch to add Prepare Transaction support for ON COMMIT DROP temporary tables. Peter Eisentraut sent in a patch to use atexit() in initdb and pg_basebackup. legrand legrand sent in a patch to add planning counters to pg_stat_statements. Michaël Paquier sent in another revision of a patch to clean up some elog messages and comments for do_pg_stop_backup and do_pg_start_backup. James Coleman sent in a patch to teach Btree to provide sorting on suffix keys with LIMIT. Noah Misch sent in a patch to augment every test postgresql.conf. Michaël Paquier sent in two revisions of a patch to remove --disable-strong-random from the code. Petr Jelínek sent in two revisions of a patch to move slot restart_lsn/catalog_xmin more aggressively from SQL slot functions. Peter Eisentraut sent in a patch to create a unified logging system for command-line programs. Heikki Linnakangas sent in a patch to create a new session in postmaster by calling setsid(). Petr Jelínek sent in a patch to synchronize logical replication slots from primary to standby and add an option for filtering which slots get synchronized.
В списке pgsql-announce по дате отправления: