== PostgreSQL Weekly News - July 08 2018 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - July 08 2018 == |
| Дата | |
| Msg-id | 20180708213441.GA3763@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - July 08 2018 == == PostgreSQL Product News == PostGIS 2.5.0beta1 the industry standard geographic information system package for PostgreSQL, released. https://git.osgeo.org/gitea/postgis/postgis/raw/tag/2.5.0beta1/NEWS pgquarrel 0.4.0, a tool which compares PostgreSQL database schemas and outputs a set of commands to turn a database schema into another one, released. http://eulerto.github.io/pgquarrel == PostgreSQL Jobs for July == http://archives.postgresql.org/pgsql-jobs/2018-07/ == PostgreSQL Local == PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. http://pgconf.com.br PostgreOpen Silicon Valley 2018 will be held in San Francisco on September 5-7, 2018. https://2018.postgresopen.org/ The Portland PostgreSQL Users Group will be holding a PGDay on September 10, 2018 in Portland, OR. The CfP is open at https://goo.gl/forms/E0CiUQGSZGMYwh922 https://pdx.postgresql.us/pdxpgday2018 PostgresConf South Africa 2018 will take place in Johannesburg on October 9, 2018 https://postgresconf.org/conferences/SouthAfrica2018 PostgreSQL Conference Europe 2018 will be held on October 23-26, 2018 at the Lisbon Marriott Hotel in Lisbon, Portugal. The CfP is open through August 6, 2018 midnight CET at https://2018.pgconf.eu/callforpapers https://2017.pgconf.eu/ 2Q PGConf will be on December 4-5, 2018 in Chicago, IL. The CfP is open through August 27, 2018 at midnight Pacific Time at http://www.2qpgconf.com/#cfp http://www.2qpgconf.com/ PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo, Japan. The CfP is open until midnight, July 31, 2018, Japan time at pgconf-asia-2018-submission(at)pgconf(dot)asia http://www.pgconf.asia/EN/2018/ == 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: - Correct function name in comment of logical decoding code. Reported-by: Dave Cramer Author: Euler Taveira Discussion: https://postgr.es/m/CADK3HHKnPGJDLhjOFBY6+70Wd14iEH8c2GKw7UrOuUHp_GNFrA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c072e80337e6b1e829141c9cd8ba0bc585f5c2ab - Add wait event for fsync of WAL segments. This has been visibly a forgotten spot in the first implementation of wait events for I/O added by 249cf07, and what has been missing is a fsync call for WAL segments which is a wrapper reacting on the value of GUC wal_sync_method. Reported-by: Konstantin Knizhnik Author: Konstantin Knizhnik Reviewed-by: Craig Ringer, Michael Paquier Discussion: https://postgr.es/m/4a243897-0ad8-f471-aa40-242591f2476e@postgrespro.ru https://git.postgresql.org/pg/commitdiff/c55de5e5123ce58ee19a47c08425949599285041 - Remove dead code for temporary relations in partition planning. Since recent commit 1c7c317c, temporary relations cannot be mixed with permanent relations within the same partition tree, and the same counts for temporary relations created by other sessions, which the planner simply discarded. Instead be paranoid and issue an error, as those should be blocked at definition time, at least for now. At the same time, a test case is added to stress what has been moved when expand_partitioned_rtentry gets called recursively but bumps on a partitioned relation with no partitions which should be handled the same way as the non-inheritance case. This code may be reworked in a close future, and covering this code path will limit surprises. Reported-by: David Rowley Author: David Rowley Reviewed-by: Amit Langote, Robert Haas, Michael Paquier Discussion: https://postgr.es/m/CAKJS1f_HyV1txn_4XSdH5EOhBMYaCwsXyAj6bHXk9gOu4JKsbw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fc057b2b8fc3c3556d9f8cc0195c622aaad92c9e - Prevent references to invalid relation pages after fresh promotion. If a standby crashes after promotion before having completed its first post-recovery checkpoint, then the minimal recovery point which marks the LSN position where the cluster is able to reach consistency may be set to a position older than the first end-of-recovery checkpoint while all the WAL available should be replayed. This leads to the instance thinking that it contains inconsistent pages, causing a PANIC and a hard instance crash even if all the WAL available has not been replayed for certain sets of records replayed. When in crash recovery, minRecoveryPoint is expected to always be set to InvalidXLogRecPtr, which forces the recovery to replay all the WAL available, so this commit makes sure that the local copy of minRecoveryPoint from the control file is initialized properly and stays as it is while crash recovery is performed. Once switching to archive recovery or if crash recovery finishes, then the local copy minRecoveryPoint can be safely updated. Pavan Deolasee has reported and diagnosed the failure in the first place, and the base fix idea to rely on the local copy of minRecoveryPoint comes from Kyotaro Horiguchi, which has been expanded into a full-fledged patch by me. The test included in this commit has been written by Álvaro Herrera and Pavan Deolasee, which I have modified to make it faster and more reliable with sleep phases. Backpatch down to all supported versions where the bug appears, aka 9.3 which is where the end-of-recovery checkpoint is not run by the startup process anymore. The test gets easily supported down to 10, still it has been tested on all branches. Reported-by: Pavan Deolasee Diagnosed-by: Pavan Deolasee Reviewed-by: Pavan Deolasee, Kyotaro Horiguchi Author: Michael Paquier, Kyotaro Horiguchi, Pavan Deolasee, Álvaro Herrera Discussion: https://postgr.es/m/CABOikdPOewjNL=05K5CbNMxnNtXnQjhTx2F--4p4ruorCjukbA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/3c64dcb1e35dccbfce531182fa9b9f48bec414ad - Add note in pg_rewind documentation about read-only files. When performing pg_rewind, the presence of a read-only file which is not accessible for writes will cause a failure while processing. This can cause the control file of the target data folder to be truncated, causing it to not be reusable with a successive run. Also, when pg_rewind fails mid-flight, there is likely no way to be able to recover the target data folder anyway, in which case a new base backup is the best option. A note is added in the documentation as well about. Reported-by: Christian H. Author: Michael Paquier Reviewed-by: Andrew Dunstan Discussion: https://postgr.es/m/20180104200633.17004.16377%40wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/eb270b00b2d60e751545d4b808642395c901b668 - Use access() to check file existence in GetNewRelFileNode(). Previous code used BasicOpenFile() and close() just to check for a file collision, while there is no need to hold open a file descriptor but that's an overkill here. Author: Paul Guo Reviewed-by: Peter Eisentraut, Michael Paquier Discussion: https://postgr.es/m/CABQrizcUtiHaquxK=d4etBX8GF9kbZB50Nt1gO9_aN-e9SptyQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/677da8c15d19c11465d78f18bfd5ceb5d6fc3af1 Peter Eisentraut pushed: - Correct comment. https://git.postgresql.org/pg/commitdiff/683707868723d2a798ff8dcb0513d89735591b66 - Add $Test::Builder::Level to pgbench test functions. same as c4309f4aeeae54e4c5281d68e29288af1d0d1ed2 https://git.postgresql.org/pg/commitdiff/2c059c86ba774930fa816278343ff30292db4e6c - doc: Reorganize CREATE TABLE / LIKE option documentation. This section once started out small but has now grown quite a bit and needs a bit of structure. Rewrite as list, add documentation of EXCLUDING, and improve the documentation of INCLUDING ALL instead of just listing all the options again. per report from Yugo Nagata that EXCLUDING was not documented, that part reviewed by Daniel Gustafsson, most of the rewrite was by me https://git.postgresql.org/pg/commitdiff/b46727e07a9f4b8e0c8de1f10bfd4986b02c154c - Fix typo. https://git.postgresql.org/pg/commitdiff/f61988d160c1af8c1ed495e5c547726e88a45036 - doc: Fix typos. Author: Justin Pryzby <pryzby@telsasoft.com> https://git.postgresql.org/pg/commitdiff/17411e0ffa1e2a9789756203019dce368ad4bf60 - doc: Reword old inheritance partitioning documentation. Prefer to use phrases like "child" instead of "partition" when describing the legacy inheritance-based partitioning. The word "partition" now has a fixed meaning for the built-in partitioning, so keeping it out of the documentation of the old method makes things clearer. Author: Justin Pryzby <pryzby@telsasoft.com> https://git.postgresql.org/pg/commitdiff/0c06534bd63b0bd23d7744a53f3b490a2adeea8a - Allow CALL with polymorphic type arguments. In order to be able to resolve polymorphic types, we need to set fn_expr before invoking the procedure. https://git.postgresql.org/pg/commitdiff/e34ec136201df07a05a83825ebff7fffb9043598 - Fix assert in nested SQL procedure call. When executing CALL in PL/pgSQL, we need to set a snapshot before invoking the to-be-called procedure. Otherwise, the to-be-called procedure might end up running without a snapshot. For LANGUAGE SQL procedures, this would result in an assertion failure. (For most other languages, this is usually not a problem, because those use SPI and SPI sets snapshots in most cases.) Setting the snapshot restores the behavior of how CALL worked when it was handled as a generic SQL statement in PL/pgSQL (exec_stmt_execsql()). This change revealed another problem: In SPI_commit(), we popped the active snapshot before committing the transaction, to avoid "snapshot %p still active" errors. However, there is no particular reason why only at most one snapshot should be on the stack. So change this to pop all active snapshots instead of only one. https://git.postgresql.org/pg/commitdiff/2e78c5b522a91c7893decd92d6f5b31fef0027bd - Add separate error message for procedure does not exist. While we probably don't want to split up all error messages into function and procedure variants, this one is a very prominent one, so it's helpful to be more specific here. https://git.postgresql.org/pg/commitdiff/0903bbdad24a8f51b18a6a54a41bbb36ad2ceee4 Fujii Masao pushed: - Improve the performance of relation deletes during recovery. When multiple relations are deleted at the same transaction, the files of those relations are deleted by one call to smgrdounlinkall(), which leads to scan whole shared_buffers only one time. OTOH, previously, during recovery, smgrdounlink() (not smgrdounlinkall()) was called for each file to delete, which led to scan shared_buffers multiple times. Obviously this could cause to increase the WAL replay time very much especially when shared_buffers was huge. To alleviate this situation, this commit changes the recovery so that it also calls smgrdounlinkall() only one time to delete multiple relation files. This is just fix for oversight of commit 279628a0a7, not new feature. So, per discussion on pgsql-hackers, we concluded to backpatch this to all supported versions. Author: Fujii Masao Reviewed-by: Michael Paquier, Andres Freund, Thomas Munro, Kyotaro Horiguchi, Takayuki Tsunakawa Discussion: https://postgr.es/m/CAHGQGwHVQkdfDqtvGVkty+19cQakAydXn1etGND3X0PHbZ3+6w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b41669118c00e25376a6c9ac991e0d074990484a Andres Freund pushed: - Check for interrupts inside the nbtree page deletion code. When deleting pages the nbtree code has to walk through siblings of a tree node. When those sibling links are corrupted that can lead to endless loops - which are currently not interruptible. This is especially problematic if autovacuum is repeatedly blocked on such indexes, as it can be hard to get out of that situation without resorting to single user mode. Thus add interrupt checks to appropriate places in such loops. Unfortunately in one of the cases it's it's not easy to do so. Between 9.3 and 9.4 the page deletion (and page split) code changed significantly. Before it was significantly less robust against interruptions. Therefore don't backpatch to 9.3. Author: Andres Freund Discussion: https://postgr.es/m/20180627191629.wkunw2qbibnvlz53@alap3.anarazel.de Backpatch: 9.4- https://git.postgresql.org/pg/commitdiff/3a01f68e35a3584431ac5381c6ed75b1b39aaf2a - Use context with correct lifetime in hypothetical_dense_rank_final. The query lifetime expression context created in hypothetical_dense_rank_final() was buggily allocated in the calling memory context. I (Andres) broke that in bf6c614a2f2. Reported-By: Rajkumar Raghuwanshi Author: Amit Langote Discussion: https://postgr.es/m/CAKcux6kmzWmur5HhA_aU6gYVFu0RLQdgJJ+aC9SLdcOvBSrpfA@mail.gmail.com Backpatch: 11- https://git.postgresql.org/pg/commitdiff/249126e761e13c4d8e7519569d483eaeca7dac25 Álvaro Herrera pushed: - Reduce cost of test_decoding's new oldest_xmin test. Change a whole-database VACUUM into doing just pg_attribute, which is the portion that verifies what we want it to do. The original formulation wastes a lot of CPU time, which leads the test to fail when runtime exceeds isolationtester timeout when it's super-slow, such as under CLOBBER_CACHE_ALWAYS. Per buildfarm member friarbird. It turns out that the previous shape of the test doesn't always detect the condition it is supposed to detect (on unpatched reorderbuffer code): the reason is that there is a good chance of encountering a xl_running_xacts record (logged every 15 seconds) before the checkpoint -- and because we advance the xmin when we receive that WAL record, and we *don't* advance the xmin twice consecutively without receiving a client message in between, that means the xmin is not advanced enough for the tuple to be pruned from pg_attribute by VACUUM. So the test would spuriously pass. The reason this test deficiency wasn't detected earlier is that HOT pruning removes the tuple anyway, even if vacuum leaves it in place, so the test correctly fails (detecting the coding mistake), but for the wrong reason. To fix this mess, run the s0_get_changes step twice before vacuum instead of once: this seems to cause the xmin to be advanced reliably, wreaking havoc with more certainty. Author: Arseny Sher Discussion: https://postgr.es/m/87h8lkuxoa.fsf@ars-thinkpad https://git.postgresql.org/pg/commitdiff/8d1c1ca70b012594932578a0994a5d45f29d9572 - logical decoding: beware of an unset specinsert change. Coverity complains that there is no protection in the code (at least in non-assertion-enabled builds) against speculative insertion failing to follow the expected protocol. Add an elog(ERROR) for the case. https://git.postgresql.org/pg/commitdiff/3ca966c06f91fb6ccc11d71d4094c1e297b8945d - Allow replication slots to be dropped in single-user mode. Starting with commit 9915de6c1cb2, replication slot drop uses a condition variable sleep to wait until the current user of the slot goes away. This is more user friendly than the previous behavior of erroring out if the slot is in use, but it fails with a not-for-user-consumption error message in single-user mode; plus, if you're using single-user mode because you don't want to start the server in the regular mode (say, disk is full and WAL won't recycle because of the slot), it's inconvenient. Fix by skipping the cond variable sleep in single-user mode, since there can't be anybody to wait for anyway. Reported-by: tushar <tushar.ahuja@enterprisedb.com> Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/3b2f809f-326c-38dd-7a9e-897f957a4eb1@enterprisedb.com https://git.postgresql.org/pg/commitdiff/0ce5cf2ef24f638ff05569d027135fa1c7683a71 Jeff Davis pushed: - Add test for partitionwise join involving default partition. Author: Rajkumar Raghuwanshi Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/CAKcux6ky5YeZAY74qSh-ayPZZEQchz092g71iXXbC0%2BE3xoscA%40mail.gmail.com Discussion: https://postgr.es/m/CAKcux6kOQ85Xtzxu3tM1mR7Vk%3D7Z2e4rG7dL1iMZqPgLMpxQYg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/4513d3a4be0bb7d0141f8b7eaf669a55c08e41b0 Andrew Dunstan pushed: - Print DEBUG2 like that rather than as DEBUG. DEBUG is an alias for DEBUG2, but we want DEBUG2 to show in the settings no matter how it was spelled. Takeshi Ideriha Discussion: https://postgr.es/m/4E72940DA2BF16479384A86D54D0988A5678EC03@G01JPEXMBKW04 https://git.postgresql.org/pg/commitdiff/8fb68aa2655762beed237ea04e2c6c490cf1ed10 Peter Geoghegan pushed: - Correct obsolete unique index insertion comment. Commit bc292937ae6 failed to update a comment about unique index checking. _bt_insertonpg() is no longer responsible for finding an insertion location while preventing conflicting insertions. https://git.postgresql.org/pg/commitdiff/e915fed291772d8d5fcc437ac777f7beca8c24ce == Pending Patches == Thomas Munro sent in another revision of a patch to add a synchronous replay mode for avoiding stale reads on hot standbys. Haribabu Kommi sent in a patch to document the fact that pg_stat_statements_reset() function can be executed by super and members of the role pg_read_all_stats. John Naylor sent in another revision of a patch to convert info for conversion functions into entries in pg_proc.dat and pg_conversion.dat and add a pg_language lookup. CCHsu sent in a patch to add tab completion to psql for SELECT. Pierre Ducroquet sent in a patch to introduce opt1 in LLVM/JIT, and force it with deforming. Nikita Glukhov sent in another revision of a patch to implement jsonpath. Ildus Kurbangaliev sent in another revision of a patch to add custom compression methods. Craig Ringer sent in a patch to add more statically defined tracepoints around xlog handling. Alexey Kryuchkov sent in a patch to make the intarray contrib extension consistent with the built-in array code by making the '&' array intersection operator return proper zero-dimensional empty arrays instead of one-dimensional, zero-length "empty" arrays. Peter Geoghegan sent in another revision of a patch to ensure nbtree leaf tuple keys are always unique. Daniel Gustafsson sent in two more revisions of a patch to order windows on partition/ordering prefix to reuse Sort nodes. Andrey V. Lepikhov sent in two more revisions of a patch to add a way to do retail IndexTuple deletion in B-trees. Masahiko Sawada sent in another revision of a patch to add functions for copying physical and logical replication slots. Andrew Gierth sent in four more revisions of a patch to make a way to include header files in the installations of EXTENSIONs. Haribabu Kommi sent in two more revisions of a patch to add a pg_stat_statements_reset() function to reset specific query/user/db statistics. Kyotaro HORIGUCHI sent in another revision of a patch to protect syscache from bloating with negative cache entries. Sergey Cherkashin sent in another revision of a patch to add backslash commands to psql for access methods. Nikita Glukhov sent in another revision of a patch to add SQL/JSON functions. Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE. Nikhil Sontakke sent in two more revisions of a patch to support logical decoding of two-phase transactions. Michaël Paquier sent in another revision of a patch to fix the slot advance comments to reflect the fact that the fast-forward does not Robert Haas sent in another revision of a patch to fix an infelicity between AtEOXact_ApplyLauncher() and subtransactions. Nikita Glukhov sent in another revision of a patch to implement k-Nearest-Neighbor for GiST indexes. Amit Langote sent in a patch to move PartitionDispatchData definition to execPartition.c. Amit Langote sent in another revision of a patch to fix opening/closing of partitioned tables in Append plans. Peter Eisentraut sent in a patch to prohibit transaction commands in security definer procedures. Pavel Raiskup sent in a patch to fix btree_gist "union" for variable length types. Andrew Dunstan sent in a patch add a GUC to auto_explain to set the level at which it logs. Masahiko Sawada sent in a patch to fix a faled assertion in the GROUPS mode of windowing functions. Simon Muller sent in another revision of a patch to allow COPY's 'text' format to output a header. Kyotaro HORIGUCHI sent in another revision of a patch to add a capability to limit the number of segments kept by replication slots by a GUC variable. Michaël Paquier sent in a patch to disable the TRUNCATE and COPY optimizations for two cases where it could cause data loss, and modifies ExecuteTruncateGuts so as the TRUNCATE optimization never runs for wal_level = minimal. Thomas Munro sent in two revisions of a patch to call AcceptInvalidationMessages() after authenticating. Ashutosh Bapat sent in another revision of a patch to implement TupleTableSlot abstraction. Andrey Borodin sent in another revision of a patch to implement covering indexes for GiST. David Rowley and Alexander Kuzmenkov traded patches to improve performance of tuple conversion map generation. Thomas Munro sent in a patch to use setproctitle_fast() to update the ps status, if available. Peter Eisentraut sent in a patch to cache pg_get_expr deparse context between calls. Amit Langote sent in a patch to remove the mention of locks taken on the catalog by CREATE COLLATION. Michaël Paquier sent in another revision of a patch to better show replication status in logical replication. Kyotaro HORIGUCHI sent in another revision of a patch to allow the generalized expression syntax for partition bounds. Kyotaro HORIGUCHI sent in a patch to fix the documentation of pg_create_physical_replication_slot. Taiki Kondo sent in a patch to fix a typo in the Japanese localization of psql. Antonin Houska sent in another revision of a patch to allow pushing aggregates below JOINs. Dave Cramer sent in a patch to document which signals effect logical replication slots and how. Amit Kapila sent in another revision of a patch to allow ExecShutdownNode to count stats. Andres Freund sent in a patch to hand code string to integer conversion for performance. Álvaro Herrera sent in a patch to put walsegsz before its output argument in XLogBytePosToRecPtr(). Markus Winand sent in a patch to fix some mistakes in XML/XPATH.
В списке pgsql-announce по дате отправления: