PostgreSQL Database Server

PostgreSQL Weekly News - October 10, 2021 PostgreSQL Product News pgCluu 3.2, a Perl program to audit PostgreSQL performance, released. PGroonga 2.3.2 a full text search platform for all languages, released. PostgreSQL Jobs for October ... [More] https://archives.postgresql.org/pgsql-jobs/2021-10/ PostgreSQL in the News Planet PostgreSQL: https://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 [email protected]. Applied Patches Michaël Paquier pushed: Fix snapshot builds during promotion of hot standby node with 2PC. Some specific logic is done at the end of recovery when involving 2PC transactions: 1) Call RecoverPreparedTransactions(), to recover the state of 2PC transactions into memory (re-acquire locks, etc.). 2) ShutdownRecoveryTransactionEnvironment(), to move back to normal operations, mainly cleaning up recovery locks and KnownAssignedXids (including any 2PC transaction tracked previously). 3) Switch XLogCtl->SharedRecoveryState to RECOVERY_STATE_DONE, which is the tipping point for any process calling RecoveryInProgress() to check if the cluster is still in recovery or not. Any snapshot taken between steps 2) and 3) would be empty, causing any transaction relying on a snapshot at this point to potentially corrupt data as there could still be some 2PC transactions to track, with RecentXmin moving backwards on successive calls to GetSnapshotData() in the same transaction. As SharedRecoveryState is the point to take into account to know if it is safe to discard KnownAssignedXids, this commit moves step 2) after step 3), so as we can never finish with empty snapshots. This exists since the introduction of hot standby, so backpatch all the way down. The window with incorrect snapshots is extremely small, but I have seen it when running 023_pitr_prepared_xact.pl, as did buildfarm member fairywren. Thomas Munro also found it independently. Special thanks to Andres Freund for taking the time to analyze this issue. Reported-by: Thomas Munro, Michael Paquier Analyzed-by: Andres Freund Discussion: https://postgr.es/m/[email protected] Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/8a4237908c0fe73dd41d4d7c7a6314f17dfd7a6f Fix warning in TAP test of pg_verifybackup. Oversight in a3fcbcd. Reported-by: Thomas Munro Discussion: https://postgr.es/m/CA+hUKGKnajZEwe91OTjro9kQLCMGGFHh2vvFn8tgHgbyn4bF9w@mail.gmail.com Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/ec2133a447318ac6d78887e91940d69e6d92a435 Refactor per-destination file rotation in logging collector. stderr and csvlog have been using duplicated code when it came to the rotation of their file by size, age or if forced by a user request (pg_ctl logrotate or the SQL function pg_rotate_logfile). The main difference between both is that stderr requires its file to always be opened, so as it is possible to have a redirection route if the logging collector is not ready yet to do its work if alternate destinations are enabled. Also, if csvlog gets disabled, we need to close properly its meta-data stored in the logging collector (last file name for current_logfiles and fd currently open for business). Except for those points, the code is the same in terms of error handling and if a file should be created or just continued. This change makes the code simpler overall, and it will help in the introduction of more file-based log destinations. This refactoring is similar to the work done in 5b0b699. Most of the duplication originates from fd801f4. Some of the TAP tests of pg_ctl check the case of a forced log rotation, but this is somewhat limited as there is no coverage for log_rotation_age or log_rotation_size (these may not be worth the extra resources to run either), and no coverage for reload of log_destination with different combinations of stderr and csvlog. I have tested all those cases separately for this refactoring. Author: Michael Paquier Discussion: https://postgr.es/m/CAH7T-aqswBM6JWe4pDehi1uOiufqe06DJWaU5=X7dDLyqUExHg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5c6e33f071537d9831db57471a06d39a175b535a Fix compilation warning in syslogger.c. Oversight in 5c6e33f. Author: Nathan Bossart Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/05c4248ad1bf0c2721ce9445f6908da9ece36ff8 Refactor fallback to stderr for csvlog to handle better WIN32 service case. send_message_to_server_log() would force a redirection of a log entry to stderr in some cases for csvlog, like the syslogger not being available yet. If this happens, csvlog would fall back to stderr to log some information rather than nothing. The code was organized so as stderr is done before csvlog, with csvlog checking that stderr did not happen yet with a reversed condition. With this code organization, it could be possible to lose some messages if running Postgres as a service on WIN32, as there is no usable stderr, and the handling of the StringInfoData holding the message for stderr was rather confusing because of that. This commit moves the csvlog handling to be before stderr, as as we are able to track down if it is necessary to log something to stderr. The reduces the handling of stderr to be in a single code path, adding a fallback to event logs for a WIN32 service. This also simplifies the way we handle the StringInfoData for stderr, making easier the integration of new file-based log destinations. I got to play with services and event logs on Windows while checking this change. Reviewed-by: Chris Bandy Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/8b76f89c37973082b3d64f5a27937efcca9d65f6 Daniel Gustafsson pushed: Replace occurrences of InvalidXid with InvalidTransactionId. While Xid is a known shortening of TransactionId, InvalidXid is not defined in the code. Fix comments which mistakenly were using the shorter version. Author: Bharath Rupireddy [email protected] Discussion: https://postgr.es/m/CALj2ACUQzdigML868nV4cojfELPkEzNLNOk7b91Pho4JB90fng@mail.gmail.com https://git.postgresql.org/pg/commitdiff/941921b875c7710e2b070c02c7819f2510808fdd Provide error hint if TAP tests are not enabled. The error message for trying to run the TAP tests in a tree not configured with --enable-tap-tests is quite terse, and could be made more helpful to new developers onboarding to postgres. This adds a small hint on how to get the tests running in such cases. Author: Kevin Burke [email protected] Discussion: https://postgr.es/m/CAKcy5ejKVYwUXguQcd6i9KHDm7cM7FzjQ+aayaPveoa_woyQpQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b5cb4db91327c2cef66207bde9cbcb592b91f93c Provide error hint on exit() check when building libpq. Commit dc227eb82 introduced a restriction on libpq that no functions which invoke exit() are allowed to be called. This was further refined and fixed in e45b0dfa1f and 2f7bae2f92 and 792259591. While this is well documented in the Makefile, the error message emitted when the check failed was terse, without hints for new developers without prior context. This adds an error hint to assist new developers onboarding to postgres. Author: Rachel Heaton [email protected] Co-authored-by: Jacob Champion [email protected] Discussion: https://postgr.es/m/CADJcwiVL20955HCNzDqz9BEDr6A77pz6-nac5sbZVvhAEMijLg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e9bc0441f1446f6614fa6712841acec91890e089 Fix duplicate words in comments. Remove accidentally duplicated words in code comments. Author: Dagfinn Ilmari Mannsåker [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7111e332c57ddb562d0ce26a4e08761a0baafb65 Fix check for trapping exit() calls in libpq. Commit e9bc0441f added an errorhint on the exit() check for libpq, but accidentally changed the nm commandline to use -a instead of -A. These options are similar enough to hide it in testing, but -a can also show debugger symbols which isn't what we want. Fix by reverting the check back to using -A again. Reported-by: Anton Voloshin [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/de744e9efbc55288572d1e81168c74ea85a4b90a Peter Eisentraut pushed: Update Unicode map text files. A couple of newer ones are available. There are no functional differences, but let's get them in anyway, so that there is no surprise diff next time someone wants to do some actual work in this area. https://git.postgresql.org/pg/commitdiff/ce27c8953e8e48c69c690c0e5795cde40ed59fd2 Make Unicode makefile parallel-safe. Fix the rules so that each rule is parallel safe, using the same trickery that we use elsewhere in the tree for rules that produce more than one output file. Refactor the whole makefile so that there is less repetition. Discussion: https://www.postgresql.org/message-id/18e34084-aab1-1b4c-edd1-c4f9fb04f714%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/e752727195798c324e769cfebf9dc4baa1c6bb0c Fix loop variable signedness. https://git.postgresql.org/pg/commitdiff/ba216d3b54ac334729c505ec8a725db3826290a2 Improve order in file. Move support functions for new PublicationTable node to more sensible locations in the files. https://git.postgresql.org/pg/commitdiff/d942887039a608c91084a942fe10571c6f6be35a Tom Lane pushed: Doc: fix minor issues in GiST support function documentation. gist.sgml and xindex.sgml hadn't been fully updated for the addition of a sortsupport support function (commit 16fa9b2b3). xindex.sgml also missed that the compress and decompress support functions are optional, an apparently far older oversight. In passing, fix gratuitous inconsistencies in wording and capitalization. Noted by E. Rogov. Back-patch to v14; the residual issues before that aren't significant enough to bother with. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/36d1a848a86afd2855215af2a112b9bde999354a Update our mapping of Windows time zone names some more. Per discussion, let's just follow CLDR's default zone mappings faithfully. There are two changes here that are clear improvements: * Mapping "Greenwich Standard Time" to Atlantic/Reykjavik is actually a better fit than using London, because Iceland hasn't observed DST since 1968, so this is more nearly what people might expect. * Since the "Samoa" zone is specified to be UTC+13:00, we must map it to Pacific/Apia not Pacific/Samoa; the latter refers to American Samoa which is now on the other side of the date line. The rest of these changes look like they're choosing the most populous IANA zone as representative. Whatever the details, we're just going to say "if you don't like this mapping, complain to CLDR". Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/c1aa3b3c0d2125cb04df8ed0387448d8aeb9519c Doc: improve description of UNION/INTERSECT/EXCEPT syntax. queries.sgml failed to mention the rather important point that INTERSECT binds more tightly than UNION or EXCEPT. I thought it could also use more discussion of the role of parentheses in these constructs. Per gripe from Christopher Painter-Wakefield. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/f3fec23dbdead113700fb1b401b681fa24f1e4f4 Doc: improve timezone/README's recipe for tracking Windows zones. We should now cite CLDR as primary reference for the zone name mapping. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/db692b0c84908b4ef5ea4c15fa2d742582ad2cf9 Fix null-pointer crash in postgres_fdw's conversion_error_callback. Commit c7b7311f6 adjusted conversion_error_callback to always use information from the query's rangetable, to avoid doing catalog lookups in an already-failed transaction. However, as a result of the utterly inadequate documentation for make_tuple_from_result_row, I failed to realize that fsstate could be NULL in some contexts. That led to a crash if we got a conversion error in such a context. Fix by falling back to the previous coding when fsstate is NULL. Improve the commentary, too. Per report from Andrey Borodin. Back-patch to 9.6, like the previous patch. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/3071bbfe44f36019710190a9273ad2bd4a947878 plperl: update ppport.h to Perl 5.34.0. Also apply the changes suggested by running perl ppport.h --compat-version=5.8.0 And remove some no-longer-required NEED_foo declarations. Dagfinn Ilmari Mannsåker Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/05798c9f7f08908bdd06c82d934da67535b72005 Adjust configure to insist on Perl version >= 5.8.3. Previously it only checked for version >= 5.8.0, although the documentation has said that the minimum version is 5.8.3 since commit dea6ba939. Per the discussion leading up to that commit, I (tgl) left it that way intentionally because you could, at the time, do some bare-bones stuff with 5.8.0. But we aren't actually testing against anything older than 5.8.3, so who knows if that's still true. It's pretty unlikely that anyone would care anyway, so let's just make configure's version check match the docs. Dagfinn Ilmari Mannsåker Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/92e6a98c3636948e7ece9a3260f9d89dd60da278 Update test/perl/README to insist on Perl version >= 5.8.3, too. Oversight in previous commit, noted by Daniel Gustafsson. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/93fb39eca643a33dd6e3c8818fc7899aa67a8103 Doc: update our claims about the minimum recommended AIX version. We currently have buildfarm members testing back to AIX 7.1, but not before, and older AIX versions are long out of support from IBM. So say that 7.1 is the oldest supported version. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/08e2daf06c71881415ebd19105a8fe53f6eb2f8f Doc: improve documentation for ^@ starts-with operator. This operator wasn't formally documented anywhere. To give it a natural home, relabel the functions-string-other table as "Other String Functions and Operators", which is more parallel to the functions-string-sql table anyway. While here, add cross-references to the pattern match and text search sections. It seems moderately likely that people would come to this section looking for those (but I don't want to actually list them in these tables). Discussion: https://postgr.es/m/CADT4RqB13KQHOJqqQ+WXmYtJrukS2UiFdtfTvT-XA3qYLyB6Cw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2ae5d72f004f599c351ee31e8da5fb3e40303760 Andres Freund pushed: windows: Define WIN32_LEAN_AND_MEAN to make compilation faster. windows.h includes a lot of other headers, slowing down compilation significantly. WIN32_LEAN_AND_MEAN reduces that a bit. It'd be better to remove the include of windows.h (as well as indirect inclusions of it) from such a central place, but until then... Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/8162464a25e5314e753c580389f76a9b7f69445b Fix TestLib::slurp_file() with offset on windows. 3c5b0685b921 used setFilePointer() to set the position of the filehandle, but passed the wrong filehandle, always leaving the position at 0. Instead of just fixing that, remove use of setFilePointer(), we have a perl fd at this point, so we can just use perl's seek(). Additionally, the perl filehandle wasn't closed, just the windows filehandle. Reviewed-By: Andrew Dunstan [email protected] Author: Andres Freund [email protected] Discussion: https://postgr.es/m/[email protected] Backpatch: 9.6-, like 3c5b0685b921 https://git.postgresql.org/pg/commitdiff/2f74db1236fe83e6665e5b0ddad4454c69495614 Bruce Momjian pushed: doc: remove URL for ICU explorer/locexp. The old URL was HTTP 404 and the git link didn't build. Also update two other ICU links. If we ever get a good link we will add it back. Reported-by: Anton Voloshin Author: Laurenz Albe Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/e8259439066c5fa4f3266f30434d5a52b8347bd1 Fujii Masao pushed: psql: Improve tab-completion for LOCK TABLE. This commit makes psql support the tab-completion for ONLY and NOWAIT keywords of LOCK TABLE command. Author: Koyu Tanigawa Reviewed-by: Shinya Kato, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/0b0d277c35533baecc8d1a9356f71de5f2ee0bd8 doc: Document pg_encoding_to_char() and pg_char_to_encoding(). Previously both functions were not described anywhere in the docs. But since they have been around since 7.0 and mentioned in the description for system catalog like pg_database, it's reasonable to add short descriptions for them. Author: Ian Lawrence Barwick Reviewed-by: Laurenz Albe, Fujii Masao Discussion: https://postgr.es/m/CAB8KJ=infievn4q1N4X7Vx8w4_RMPPG0pLvxhSDjy5WQOSHW9g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f6b5d05ba9a4ac7c5ebec76045c6e0afcf7c9eec Make recovery report error message when invalid page header is found. Commit 0668719801 changed XLogPageRead() so that it validated the page header, if invalid page header was found reset the error message and retried reading the page, to fix the scenario where streaming standby got stuck at a continuation record. This change hid the error message about invalid page header, which would make it harder for users to investigate what the actual issue was found in WAL. To fix the issue, this commit makes XLogPageRead() report the error message when invalid page header is found. When not in standby mode, an invalid page header should cause recovery to end, not retry reading the page, so XLogPageRead() doesn't need to validate the page header for the retry. Instead, ReadPageInternal() should be responsible for the validation in that case. Therefore this commit changes XLogPageRead() so that if not in standby mode it doesn't validate the page header for the retry. Reported-by: Yugo Nagata Author: Yugo Nagata, Kyotaro Horiguchi Reviewed-by: Ranier Vilela, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/68601985e699adeb267636fd19d3d6113554bd1f Amit Kapila pushed: Remove obsolete comment in snapbuild.c. Commits 955a684e04 and a975ff4980 removed the usage of running xacts information from serialized snapshots but forgot to remove the corresponding comment. Author: Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoBifOr7RS=jRe7YCavc646y9omChv6zkWXvJeZcjS9mXA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/826584fa5284341c601f3c49804dfa9c02295554 Robert Haas pushed: Flexible options for BASE_BACKUP. Previously, BASE_BACKUP used an entirely hard-coded syntax, but that's hard to extend. Instead, adopt the same kind of syntax we've used for SQL commands such as VACUUM, ANALYZE, COPY, and EXPLAIN, where it's not necessary for all of the option names to be parser keywords. In the new syntax, most of the options now take an optional Boolean argument. To match our practice in other in places, the options which the old syntax called NOWAIT and NOVERIFY_CHECKSUMS options are in the new syntax called WAIT and VERIFY_CHECKUMS, and the default value is false. In the new syntax, the FAST option has been replaced by a CHECKSUM option whose value may be 'fast' or 'spread'. This commit does not remove support for the old syntax. It just adds the new one as an additional option, and makes pg_basebackup prefer the new syntax when the server is new enough to support it. Patch by me, reviewed and tested by Fabien Coelho, Sergei Kornilov, Fujii Masao, and Tushar Ahuja. Discussion: http://postgr.es/m/CA+TgmobAczXDRO_Gr2euo_TxgzaH1JxbNxvFx=HYvBinefNH8Q@mail.gmail.com Discussion: http://postgr.es/m/CA+TgmoZGwR=ZVWFeecncubEyPdwghnvfkkdBe9BLccLSiqdf9Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0ba281cb4bf9f5f65529dfa4c8282abb734dd454 Flexible options for CREATE_REPLICATION_SLOT. Like BASE_BACKUP, CREATE_REPLICATION_SLOT has historically used a hard-coded syntax. To improve future extensibility, adopt a flexible options syntax here, too. In the new syntax, instead of three mutually exclusive options EXPORT_SNAPSHOT, USE_SNAPSHOT, and NOEXPORT_SNAPSHOT, there is now a single SNAPSHOT option with three possible values: 'export', 'use', and 'nothing'. This commit does not remove support for the old syntax. It just adds the new one as an additional option, makes pg_receivewal, pg_recvlogical, and walreceiver processes use it. Patch by me, reviewed by Fabien Coelho, Sergei Kornilov, and Fujii Masao. Discussion: http://postgr.es/m/CA+TgmobAczXDRO_Gr2euo_TxgzaH1JxbNxvFx=HYvBinefNH8Q@mail.gmail.com Discussion: http://postgr.es/m/CA+TgmoZGwR=ZVWFeecncubEyPdwghnvfkkdBe9BLccLSiqdf9Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0266e98c6b865246c3031bbf55cb15f330134e30 Dean Rasheed pushed: Fix corner-case loss of precision in numeric_power(). This fixes a loss of precision that occurs when the first input is very close to 1, so that its logarithm is very small. Formerly, during the initial low-precision calculation to estimate the result weight, the logarithm was computed to a local rscale that was capped to NUMERIC_MAX_DISPLAY_SCALE (1000). However, the base may be as close as 1e-16383 to 1, hence its logarithm may be as small as 1e-16383, and so the local rscale needs to be allowed to exceed 16383, otherwise all precision is lost, leading to a poor choice of rscale for the full-precision calculation. Fix this by removing the cap on the local rscale during the initial low-precision calculation, as we already do in the full-precision calculation. This doesn't change the fact that the initial calculation is a low-precision approximation, computing the logarithm to around 8 significant digits, which is very fast, especially when the base is very close to 1. Patch by me, reviewed by Alvaro Herrera. Discussion: https://postgr.es/m/CAEZATCV-Ceu%2BHpRMf416yUe4KKFv%3DtdgXQAe5-7S9tD%3D5E-T1g%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/e54a758d24dab056bb7f50d26c57a3c8761cc44a Etsuro Fujita pushed: Add missing word to comment in joinrels.c. Author: Amit Langote Backpatch-through: 13 Discussion: https://postgr.es/m/CA%2BHiwqGQNbtamQ_9DU3osR1XiWR4wxWFZurPmN6zgbdSZDeWmw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/700c73312841bd1a89263f238556ce8d8d916258 postgres_fdw: Fix comments in connection.c. Commit 27e1f1456 missed updating some comments. Reviewed-by: Bharath Rupireddy Backpatch-through: 14 Discussion: https://postgr.es/m/CAPmGK15Q2Nm6U%2Ba_GwskrWFEVBZ9_3VKOvRrprGufpx91M_3Sw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/972c7c6567fbb02a59b94ede80b17805de1bc03c [Less]

The pgAdmin Development Team are pleased to announce pgAdmin 4 version 6.0. This release of pgAdmin 4 includes 18 bug fixes and new features. For more details please see the release notes. pgAdmin is the leading Open Source graphical management tool ... [More] for PostgreSQL. For more information, please see the website. Notable changes in this release include: Introduction to ReactJS Framework in pgAdmin 4: ReactJS has a strong developer community and popularity. It has grown to a level where its performance is comparable to that of desktop applications. Migrating pgAdmin to use ReactJS has proven to boost pgAdmin’s performance and user experience. In this release we have ported the browser Tree, all the object dialogs, and the Grant Wizard. Features: Added support for OWNED BY Clause for sequences. This feature adds the OWNED BY clause for the sequences. The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. Bugs/Housekeeping: Port object nodes and properties dialogs to React. Port browser tree to React. Port Grant Wizard to react. Remove GPDB support completely. Added support to create the Partitioned table using COLLATE and opclass. Fixed keyerror issue in schema diff for 'attnum' and 'edit_types' parameter. Fixed an issue where the Execute button of the query tool gets disabled once we change anything in the data grid. Ensure that SQL help should work for EPAS servers. Fixed an issue where the grant wizard is unresponsive if the database size is huge. Builds for Windows and macOS are available now, along with a Python Wheel, Docker Container, RPM, DEB Package, and source code tarball from the tarball area. Note: The publication of the python package to PyPi has been delayed and will be completed as soon as possible. [Less]

The PostgreSQL Community Code of Conduct Committee welcomes three new members: Ilaria Battiston is a Data Engineering student at Technical University of Munich with a huge passion for Databases and Open Source, planning to dedicate her upcoming ... [More] years in Database research and development. She is an administrative committee of the Italian Postgres User Group, part of the organizing team of PGConfEU and Google Summer of Code participant. Lucie Šimečková is a Developer Marketing Manager at Timescale. She lives in the Czech Republic. She is a diversity and inclusion and women empowerment advocate. From enforcing the Code of Conduct at events, through localization efforts, to supporting communities that work with marginalized groups, her goal is to ensure that all activities are inclusive of developers of all kinds. Stefan Fercot is a Database Backup Architect at EDB. Former PostgreSQL DBA and Linux system administrator, he lives in Mons, Belgium. He is part of the contributing team behind pgBackRest and also helps organizing community events with the FR and EU PostgreSQL Users' Groups." Stacey Haysler, who served as the chair since the inception of the Committee, has now completed her 3-year term. She has set the foundations for the success of this committee, and we are grateful for her tremendous efforts. Thank you, Stacey! Carole Arnaud and Fábio Telles Rodriguez, who served on the Committee for 2 years and 1 year respectively, are stepping down this year. We appreciate their involvement in the Committee. There will be a short transition period, ending October 31, 2021 to allow the transfer of information and responsibilities. The new term runs through September 30, 2022. The continuing Committee members are: Umair Shahid (taking over as Chair) is the Head of PostgreSQL at Percona and is based in Islamabad, Pakistan. He is a veteran of the PostgreSQL Global Development Group, contributing to the community as well as driving commercial success. He is the principal organizer for Islamabad and Dubai PostgreSQL Users' Groups. Anastasia Lubennikova is a Senior Developer at Zenith and a PostgreSQL Global Development Group Contributor. She contributes to the PostgreSQL community as a core developer and speaker. As a part of her job, she also helps with mentoring and onboarding new developers. Jeanette Bromage is a Principal DBA at Kira Systems. Prior to this, she took a three year break from managing databases to concentrate on AWS/Linux system administration. Jeanette has worked with various database technologies across different platforms for over twenty years, including a three year stint as a C programmer. Michael Goldberg is a co-organizer of pgDay Israel conferences since 2017, co-founder of PostgreSQL Israeli Community, PostgreSQL translator, part of the translation team for the press releases, and maintainer of the PostgreSQL Israel and pgDay Israel websites. Michael is the Director of Infrastructure at Shelfy. The Committee is comprised of 4 women, 3 men, and 0 nonbinary individuals. No two members work at the same company. No two members reside in the same country. The countries represented are: Belgium Czech Republic Germany Israel Pakistan Russian Federation United Kingdom The full list of Committee members is always available at: https://www.postgresql.org/about/policies/coc_committee/ The Committee can be contacted at [email protected]. [Less]

Hi, PGroonga 2.3.2 has been released! About PGroonga About PGroonga : https://pgroonga.github.io/ PGroonga is a PostgreSQL extension that makes PostgreSQL fast full text search platform for all languages! It's released under PostgreSQL license. ... [More] There are some PostgreSQL extensions that improves full text search feature of PostgreSQL such as pg_trgm ^1. pg_trgm doesn't support languages that use non-alphanumerics characters such as Japanese and Chinese. PGroonga supports all languages, provides rich full text search related features and is very fast. Because PGroonga uses Groonga^2 that is a full-fledged full text search engine as backend. See the following benchmark results for performance: Benchmark result for PGroonga, textsearch and pg_trgm with English Wikipedia https://pgroonga.github.io/reference/pgroonga-versus-textsearch-and-pg-trgm.html Benchmark result for PGroonga and pg_bigm with Japanese Wikipedia https://pgroonga.github.io/reference/pgroonga-versus-pg-bigm.html PGroonga also supports JSON search. You can use each value for condition. You can also perform full text search against all texts in JSON like textsearch in PostgreSQL 10 does. Users Here are PGroonga users: Zulip: https://zulipchat.com/ Powerful open source group chat https://pgroonga.github.io/users/ Changes Here are highlights of PGroonga 2.3.2: Support PostgreSQL 14 See the following release note URL for details: https://groonga.org/en/blog/2021/10/04/pgroonga-2.3.2.html Usage You can use PGroonga without full text search knowledge. You just create an index and puts a condition into WHERE: CREATE INDEX index_name ON table USING pgroonga (column); SELECT * FROM table WHERE column &@~ 'PostgreSQL'; You can also use LIKE to use PGroonga. PGroonga provides a feature that performs LIKE with index. LIKE with PGroonga index is faster than LIKE without index. It means that you can improve performance without changing your application that uses the following SQL: SELECT * FROM table WHERE column LIKE '%PostgreSQL%'; Are you interested in PGroonga? Please install^4 and try tutorial^5. You can know all PGroonga features. You can install PGroonga easily. Because PGroonga provides packages for major platforms. There are binaries for Windows. Thanks, [Less]

PostgreSQL Weekly News - October 3, 2021 PostgreSQL 14 released! https://www.postgresql.org/about/news/postgresql-14-released-2318/ PostgreSQL Product News pgtt 2.6, an extension to implement global temporary tables, released. ... [More] https://github.com/darold/pgtt/releases/tag/v2.6 oracle_fdw 2.4.0 released. https://laurenz.github.io/oracle_fdw pgFormatter 5.1, a formatter/beautifier for SQL code, released. https://github.com/darold/pgFormatter/blob/master/ChangeLog PostgreSQL Jobs for October https://archives.postgresql.org/pgsql-jobs/2021-10/ PostgreSQL in the News Planet PostgreSQL: https://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 [email protected]. Applied Patches Thomas Munro pushed: Track LLVM 14 API changes. Only done on the master branch for now to fix build farm animal seawasp (which tests bleeeding edge PostgreSQL with bleeding edge LLVM). We can back-patch a consolidated fix closer to LLVM 14's release, once its API has stopped moving around. Discussion: https://postgr.es/m/CA%2BhUKGL%3Dyg6qqgg6W6SAuvRQejditeoDNy-X3b9H_6Fnw8j5Wg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/e6a7600202105919bffd62b3dfd941f4a94e082b Peter Geoghegan pushed: Remove unneeded nbtree latestRemovedXid comments. Discussing the low level issue of nbtree VACUUM and recovery conflicts in btvacuumpage() now seems inappropriate. The same issue is discussed in nbtxlog.h, as well as in a comment block above _bt_delitems_vacuum(). The comment block made more sense when it was part of a broader discussion of nbtree VACUUM "pin scans". These were removed by commit 9f83468b. https://git.postgresql.org/pg/commitdiff/895267a3266484440c0b2f42f613bcff28844cc1 Enable deduplication in system catalog indexes. The "equality implies image equality" opclass infrastructure disallowed deduplication in system catalog indexes and TOAST indexes before now. That seemed like the right approach back when the infrastructure was added by commit 612a1ab7, since ALTER INDEX cannot set deduplicate_items to 'off' (due to an old implementation restriction). But that decision now seems arbitrary at best. Remove special case handling implementing this policy. No catversion bump, since existing catalog indexes will still work. Author: Peter Geoghegan [email protected] Discussion: https://postgr.es/m/CAH2-Wz=rYQHFaJ3WYBdK=xgwxKzaiGMSSrh-ZCREa-pS-7Zjew@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2903f1404df37e11ecc303dbc164826c4717194b Michaël Paquier pushed: Fix typos and grammar in code comments. Several mistakes have piled in the code comments over the time, including incorrect grammar, function names and simple typos. This commit takes care of a portion of these. No backpatch is done as this is only cosmetic. Author: Justin Pryzby Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/e767ddcd354b51fc4c12d6b02e268861bd871fbc Refactor output file handling when forking syslogger under EXEC_BACKEND. A forked logging collector in EXEC_BACKEND builds passes down file descriptors (or HANDLEs in WIN32) through a command for files to be reopened (for stderr and csvlog). Some of its logic was duplicated, and this commit refactors the code with some wrapper routines for file reopening after forking and fd grabbing when building the command for the fork. While on it, this simplifies a use of "long" in the code, introduced by ab0ba6e to take care of a warning related to MinGW-W64 when mapping a intptr_t to a printed value. "long" is 32-bit long on Windows, and interoperability of Win32 and Win64 ensures that handles are always 32-bit significant, so we can just use "int" for the same result. This also makes the new routines more symmetric. This change makes easier the introduction of new log destinations in the logging collector, and this is not the only piece of refactoring planned. I have tested this change with EXEC_BACKEND on linux, macos, and of course MSVC (both Win32 and Win64), but not MinGW so the buildfarm may have something to say here. Author: Sehrope Sarkuni, Michael Paquier Discussion: https://postgr.es/m/CAH7T-aqswBM6JWe4pDehi1uOiufqe06DJWaU5=X7dDLyqUExHg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5b0b699f748ead1b7414c58aaa7cf0ea83808147 doc: Fix some typos and markups. Author: Ekaterina Kiryanova Discussion: https://postgr.es/m/[email protected] Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/c8dd2cb49405d2a39a714bd5adc31d39b8372a4e Clarify use of "statistics objects" in the code. The code inconsistently used "statistic object" or "statistics" where the correct term, as discussed, is actually "statistics object". This improves the state of the code to be more consistent. While on it, fix an incorrect error message introduced in a4d75c8. This error should never happen, as the code states, but it would be misleading. Author: Justin Pryzby Reviewed-by: Álvaro Herrera, Michael Paquier Discussion: https://postgr.es/m/[email protected] Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/070d2e19e40897d857f570f24888fc30727ed9c0 pg_stat_statements: Add some tests for older versions still usable. When the newest version is loaded, the backend would load objects from the oldest complete SQL file (here 1.4) and then update to the latest version with transition scripts (up to 1.9 currently). This provides some coverage for upgrades of pg_stat_statements, but there is no test to show how things have changed across each version. This adds a couple of tests for the upgrade paths using objects from each version supported, stressing the objects whose behaviors have changed across each version supported. Author: Erica Zhang Reviewed-by: Julien Rouhaud, Michael Paquier Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/2b0da0365bec6c62cc9c5c317bab6cbee3d52ef4 Tom Lane pushed: Re-enable contrib/bloom's TAP tests. These tests were disabled back in 2018 (commit d3c09b9b1) because of failures observed in the buildfarm. I've not been able to reproduce any failure on longfin's host, though, so I'm curious whether or to what extent we've fixed the problem. Let's re-enable it (in HEAD only) and see what blows up. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7d1aa6bf1c27bf7438179db446f7d1e72ae093d0 Fix instability in contrib/bloom TAP tests. It turns out that the instability complained of in commit d3c09b9b1 has an embarrassingly simple explanation. The test script waits for the standby to flush incoming WAL to disk, but it should wait for the WAL to be replayed, since we are testing for the effects of that to be visible. While at it, use wait_for_catchup instead of reinventing that logic, and adjust $Test::Builder::Level to improve future error reports. Back-patch to v12 where the necessary infrastructure came in (cf. aforesaid commit). Also back-patch 7d1aa6bf1 so that the test will actually get run. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/6bc6bd47cf715c8717a8af3f617957045772d38b Treat ETIMEDOUT as indicating a non-recoverable connection failure. Add ETIMEDOUT to ALL_CONNECTION_FAILURE_ERRNOS' list of "errnos that identify hard failure of a previously-established network connection". While one could imagine that this is sometimes recoverable, the same could be said of other entries such as ENETDOWN. In support of this, handle ETIMEDOUT on par with other socket errors in relevant infrastructure, such as TranslateSocketError(). (I made a couple of cosmetic adjustments in TranslateSocketError(), too.) The code now assumes that ETIMEDOUT is defined everywhere, which it should be given that POSIX has required it since SUSv2. Perhaps this should be back-patched, but I'm hesitant to do so given the lack of previous complaints, and the hazard that there's a small ABI break on Windows from redefining the symbol. Even if we decide to do that, it'd be prudent to let this bake awhile in HEAD first. Jelte Fennema Discussion: https://postgr.es/m/AM5PR83MB01782BFF2978505F6D6C559AF7AA9@AM5PR83MB0178.EURPRD83.prod.outlook.com https://git.postgresql.org/pg/commitdiff/b484ddf4d2eb81736512efa35ed3e5d2a72993d8 Remove gratuitous environment dependency in 002_types.pl test. Computing related timestamps by subtracting "N days" is sensitive to the prevailing timezone, since we interpret that as "same local time on the N'th prior day". Even though the intervals in question are only two to four days, through remarkable bad luck they managed to cross the end of Ramadan in 2014, causing the test's output to change if timezone is set to Africa/Casablanca. (Maybe in other Muslim areas as well; I didn't check.) There's absolutely no reason for this test to exercise interval subtraction, so just get rid of that and use plain timestamptz constants representing the intended values. Per report from Andres Freund. Back-patch to v10 where this test script came in. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/20f8671ef69b864c25ffa59471814102c1260d78 Fix Portal snapshot tracking to handle subtransactions properly. Commit 84f5c2908 forgot to consider the possibility that EnsurePortalSnapshotExists could run inside a subtransaction with lifespan shorter than the Portal's. In that case, the new active snapshot would be popped at the end of the subtransaction, leaving a dangling pointer in the Portal, with mayhem ensuing. To fix, make sure the ActiveSnapshot stack entry is marked with the same subtransaction nesting level as the associated Portal. It's certainly safe to do so since we won't be here at all unless the stack is empty; hence we can't create an out-of-order stack. Let's also apply this logic in the case where PortalRunUtility sets portalSnapshot, just to be sure that path can't cause similar problems. It's slightly less clear that that path can't create an out-of-order stack, so add an assertion guarding it. Report and patch by Bertrand Drouvot (with kibitzing by me). Back-patch to v11, like the previous commit. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7b5d4c29ed0262e537026cb3a85161d6cf98abcc Avoid believing incomplete MCV-only stats in get_variable_range(). get_variable_range() would incautiously believe that statistics containing only an MCV list are sufficient to derive a range estimate. That's okay for an enum-like column that contains only MCVs, but otherwise the estimate could be pretty bad. Make it report that the range is indeterminate unless the MCVs plus nullfrac account for the whole table. I don't think this needs a dedicated test case, since a quick code coverage check verifies that the existing regression tests traverse all the alternatives. There is room to doubt that a future-proof test case could be built anyway, given that the submitted example accidentally doesn't fail before v11. Per bug #17207 from Simon Perepelitsa. Back-patch to v10. In principle this has been broken all along, but I'm hesitant to make such changes in 9.6, since if anyone is unhappy with 9.6.24's behavior there will be no second chance to fix it. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/8c1144ba73478b818d9cebe8ecd64a14b7d45bde Re-alphabetize the win32_tzmap[] array. The original intent seems to have been to sort case-insensitively by the Windows zone name, but various changes over the years did not get that memo. This commit just moves a few entries to restore exact alphabetic order, to ease comparison to the outputs of processing scripts. Back-patch to all supported branches, as is our usual practice for time zone data updates. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/ad740067aea5b643ca2f79da086808573d35b5f4 Update our mapping of Windows time zone names using CLDR info. This corrects a bunch of entries in win32_tzmap[], and adds a few new ones, based on the CLDR project's windowsZones.xml file. Non-cosmetic changes fall into four main categories: * Flat-out errors: US/Aleutan doesn't exist America/Salvador doesn't exist Asia/Baku is wrong for Yerevan Asia/Dhaka (Bangladesh) is wrong for Astana (Kazakhstan) Europe/Bucharest is wrong for Chisinau America/Mexico_City is wrong for Chetumal America/Buenos_Aires is wrong for Cayenne America/Caracas has its own zone, so poor fit for La Paz US/Eastern is wrong for Haiti US/Eastern is wrong for Indiana (East) Asia/Karachi is wrong for Tashkent Etc/UTC+12 doesn't exist Signs of Etc/GMT zones were backwards * Judgment calls: (These changes follow CLDR's choices, except for the first one) Use Europe/London for "Greenwich Standard Time", since that seems much more likely than Africa/Casablanca to be what people will think that zone name means. CLDR has Atlantic/Reykjavik here, but that's no better. Asia/Shanghai seems a better fit than Hong Kong for "China Standard Time". Europe/Sarajevo is now a link to Belgrade, ie "Central Europe Standard Time"; so use Warsaw for "Central European Standard Time". America/Sao_Paulo seems more representative than Araguaina for "E. South America Standard Time". Africa/Johannesburg seems more representative than Harare for "South Africa Standard Time". * New Windows zone names: "Israel Standard Time" "Kaliningrad Standard Time" "Russia Time Zone N" for various N "Singapore Standard Time" "South Sudan Standard Time" "W. Central Africa Standard Time" "West Bank Standard Time" "Yukon Standard Time" Some of these replace older spellings, but I kept the older spellings too in case our code runs on a machine with the older data. * Replace aliases (tzdb Links) with underlying city-named zones: (This tracks tzdb's longstanding practice, and reduces inconsistency with the rest of the entries, as well as with CLDR.) US/Alaska Asia/Kuwait Asia/Muscat Canada/Atlantic Australia/Canberra Canada/Saskatchewan US/Central US/Eastern US/Hawaii US/Mountain Canada/Newfoundland US/Pacific Back-patch to all supported branches, as is our usual practice for time zone data updates. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/9b8d68cc6589814d121344f59e927a7e4506fb8c Fix checking of query type in plpgsql's RETURN QUERY command. Prior to v14, we insisted that the query in RETURN QUERY be of a type that returns tuples. (For instance, INSERT RETURNING was allowed, but not plain INSERT.) That happened indirectly because we opened a cursor for the query, so spi.c checked SPI_is_cursor_plan(). As a consequence, the error message wasn't terribly on-point, but at least it was there. Commit 2f48ede08 lost this detail. Instead, plain RETURN QUERY insisted that the query be a SELECT (by checking for SPI_OK_SELECT) while RETURN QUERY EXECUTE failed to check the query type at all. Neither of these changes was intended. The only convenient place to check this in the EXECUTE case is inside _SPI_execute_plan, because we haven't done parse analysis until then. So we need to pass down a flag saying whether to enforce that the query returns tuples. Fortunately, we can squeeze another boolean into struct SPIExecuteOptions without an ABI break, since there's padding space there. (It's unlikely that any extensions would already be using this new struct, but preserving ABI in v14 seems like a smart idea anyway.) Within spi.c, it seemed like _SPI_execute_plan's parameter list was already ridiculously long, and I didn't want to make it longer. So I thought of passing SPIExecuteOptions down as-is, allowing that parameter list to become much shorter. This makes the patch a bit more invasive than it might otherwise be, but it's all internal to spi.c, so that seems fine. Per report from Marc Bachmann. Back-patch to v14 where the faulty code came in. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/a0558cfa395b47adb245972f5eba7978461e7baa Peter Eisentraut pushed: Support amcheck of sequences. Sequences were left out of the list of relation kinds that verify_heapam knew how to check, though it is fairly trivial to allow them. Doing that, and while at it, updating pg_amcheck to include sequences in relations matched by table and relation patterns. Author: Mark Dilger [email protected] Discussion: https://www.postgresql.org/message-id/flat/81ad4757-92c1-4aa3-7bee-f609544837e3%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/c3b011d9918100c6ec2d72297fb51635bce70e80 Fix incorrect format placeholder. https://git.postgresql.org/pg/commitdiff/0b947c3101d1d05c55531731d6b778f82cb21350 psql: Add various tests. Add tests for psql features - AUTOCOMMIT - ON_ERROR_ROLLBACK - ECHO errors Reviewed-by: Fabien COELHO [email protected] Discussion: https://www.postgresql.org/message-id/6954328d-96f2-77f7-735f-7ce493a40949%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/14d755b00037ce04b9e24504f4b540d9e731c29e Magnus Hagander pushed: Properly schema-prefix reference to pg_catalog.pg_get_statisticsobjdef_columns. Author: Tatsuro Yamada Backpatch-through: 14 Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/07f8a9e784236a3baf707c59cf80d0f015594ffc Fujii Masao pushed: pgbench: Correct log level of message output when socket wait method fails. The failure of socket wait method like "select()" doesn't terminate pgbench. So the log level of error message when that failure happens should be ERROR. But previously FATAL was used in that case. Back-patch to v13 where pgbench started using common logging API. Author: Yugo Nagata, Fabien COELHO Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d33674708948e10806480ee628b072a2ef8ecba1 pgbench: Fix handling of socket errors during benchmark. Previously socket errors such as invalid socket or socket wait method failures during benchmark caused pgbench to exit with status 0. Instead, errors during the run should result in exit status 2. Back-patch to v12 where pgbench started reporting exit status. Original complaint and patch by Hayato Kuroda. Author: Yugo Nagata, Fabien COELHO Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/TYCPR01MB5870057375ACA8A73099C649F5349@TYCPR01MB5870.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/2acb7cc6b56c2b80029c202217e19553578456e9 Álvaro Herrera pushed: Fix WAL replay in presence of an incomplete record. Physical replication always ships WAL segment files to replicas once they are complete. This is a problem if one WAL record is split across a segment boundary and the primary server crashes before writing down the segment with the next portion of the WAL record: WAL writing after crash recovery would happily resume at the point where the broken record started, overwriting that record ... but any standby or backup may have already received a copy of that segment, and they are not rewinding. This causes standbys to stop following the primary after the latter crashes: LOG: invalid contrecord length 7262 at A8/D9FFFBC8 because the standby is still trying to read the continuation record (contrecord) for the original long WAL record, but it is not there and it will never be. A workaround is to stop the replica, delete the WAL file, and restart it -- at which point a fresh copy is brought over from the primary. But that's pretty labor intensive, and I bet many users would just give up and re-clone the standby instead. A fix for this problem was already attempted in commit 515e3d84a0b5, but it only addressed the case for the scenario of WAL archiving, so streaming replication would still be a problem (as well as other things such as taking a filesystem-level backup while the server is down after having crashed), and it had performance scalability problems too; so it had to be reverted. This commit fixes the problem using an approach suggested by Andres Freund, whereby the initial portion(s) of the split-up WAL record are kept, and a special type of WAL record is written where the contrecord was lost, so that WAL replay in the replica knows to skip the broken parts. With this approach, we can continue to stream/archive segment files as soon as they are complete, and replay of the broken records will proceed across the crash point without a hitch. Because a new type of WAL record is added, users should be careful to upgrade standbys first, primaries later. Otherwise they risk the standby being unable to start if the primary happens to write such a record. A new TAP test that exercises this is added, but the portability of it is yet to be seen. This has been wrong since the introduction of physical replication, so backpatch all the way back. In stable branches, keep the new XLogReaderState members at the end of the struct, to avoid an ABI break. Author: Álvaro Herrera [email protected] Reviewed-by: Kyotaro Horiguchi [email protected] Reviewed-by: Nathan Bossart [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/ff9f111bce24fd9bbca7a20315586de877d74923 Repair two portability oversights of new test. First, as pointed out by Tom Lane and Michael Paquier, I failed to realize that Windows' PostgresNode needs an extra pg_hba.conf line (added by PostgresNode->set_replication_conf, called internally by ->init() when 'allows_streaming=>1' is given -- but I purposefully omitted that). I think a good fix should be to have nodes with only 'has_archiving=>1' set up for replication too, but that's a bigger discussion. Fix it by calling ->set_replication_conf, which is not unprecedented, as pointed out by Andrew Dunstan. I also forgot to uncomment a ->finish() call for a pumpable IPC::Run file descriptor. Apparently this is innocuous in almost all platforms. Backpatch to 14. The older branches were added this file too, but not this particular part of the test. Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d03bca4d70c29cca4f09e3a0e78a56cf97e237f3 Remove unstable, unnecessary test; fix typo. Commit ff9f111bce24 added some test code that's unportable and doesn't add meaningful coverage. Remove it rather than try and get it to work everywhere. While at it, fix a typo in a log message added by the aforementioned commit. Backpatch to 14. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d186d233dfde4afb9dff346e13c8adaf4deec6b3 Error out if SKIP LOCKED and WITH TIES are both specified. Both bugs #16676[1] and #17141[2] illustrate that the combination of SKIP LOCKED and FETCH FIRST WITH TIES break expectations when it comes to rows returned to other sessions accessing the same row. Since this situation is detectable from the syntax and hard to fix otherwise, forbid for now, with the potential to fix in the future. [1] https://postgr.es/m/[email protected] [2] https://postgr.es/m/[email protected] Backpatch-through: 13, where WITH TIES was introduced Author: David Christensen [email protected] Discussion: https://postgr.es/m/CAOxo6XLPccCKru3xPMaYDpa+AXyPeWFs+SskrrL+HKwDjJnLhg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c6bc655ee2ef09449da7ff688a8be19a13db5c4a David Rowley pushed: Ensure interleaved_parts field is always initialized. This field was recently added in db632fbca, however that commit missed one place where it should have initialized the new field to NULL. The missed location is where the PartitionBoundInfo is created for partition-wise join relations. Technically there could be interleaved partitions in a partition-wise join relation, but currently the only optimization we use this field for only does so for base rels and other member rels. So just document that we don't populate this field for join rels. Reported-by: Amit Langote Author: Amit Langote, David Rowley Reviewed-by: Amit Langote, David Rowley Discussion: https://postgr.es/m/CA+HiwqE76Rps24kwHsd2Cr82Ua07tJC9t9reG0c7ScX9n_xrEA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/16239c5fdf6e457f8274c49209d1fbdeab472703 Amit Kapila pushed: Doc: Move pg_stat_replication_slots view to "Collected Statistics Views" section. Commit 9868167500 added pg_stat_replication_slots view to monitor ReorderBuffer stats but mistakenly added it under "Dynamic Statistics Views" section in the docs whereas it belongs to "Collected Statistics Views" section. Author: Amit Kapila Reviewed-by: Masahiko Sawada Backpatch-through: 14, where it was introduced Discussion: https://postgr.es/m/CAA4eK1Kb5ur=OC-G4cAsqPOjoVe+S8LNw1WmUY8Owasjk8o5WQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2d44dee0281a1abf0dcb1548c910fae067f1d34d Daniel Gustafsson pushed: Fix memory leak in pg_hmac. The intermittent h buffer was not freed, causing it to leak. Backpatch through 14 where HMAC was refactored to the current API. Author: Sergey Shinderuk [email protected] Discussion: https://postgr.es/m/[email protected] Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/0ded7039fab314afb7cbaf36b52209f253c05539 Andres Freund pushed: Reference test binary using TESTDIR in 001_libpq_pipeline.pl. The previous approach didn't really work on windows, due to the PATH separator being ';' not ':'. Instead of making the PATH change more complicated, reference the binary using the TESTDIR environment. Reported-By: Andres Freund [email protected] Suggested-By: Andrew Dunstan [email protected] Discussion: https://postgr.es/m/[email protected] Backpatch: 14-, where the test was introduced. https://git.postgresql.org/pg/commitdiff/795862c280c5949bafcd8c44543d887fd32b590a [Less]

Grenoble, France - September 30th, 2021 pgCluu, PostgreSQL Cluster Utilization pgCluu is a Perl program used to perform a full audit of a PostgreSQL Cluster performances. It is divided in two parts, a collector used to grab statistics on the ... [More] PostgreSQL server using psql and sar, a reports builder that will generate all HTML and charts output. New or enhanced reports/features: Add collect and report of unused trigger functions. Add system statistics dedicated to PostgreSQL process through stats reported by pidstat. Add report of CPU scaling_governor to check if it set to powersave, setting it to performances can improve performances up to 20% following the PostgreSQL load. For more information on this kernel parameter see https://wiki.archlinux.org/index.php/CPU_frequency_scaling#Scaling_governors New command line options: Add command line option -t | --lock-timeout with default to 3 seconds to self terminate a SQL query that could be lock by the activity of an other session like a drop table for example. New configuration directives : Add STATS_REPORT_OUTDIR configuration directive to pgcluu.conf to set the directory where the pgCluu reports will be saved. Default to /var/lib/pgcluu/report/. Backward compatibility: Change default retention time to 30 days instead of no storage limit to avoid possible out of memory on uncontrolled installation. See ChangeLog for a complete list of changes and bug fixes. About The goal of this project is to provide a complete PostgreSQL auditing tool that don't need any dependency to be run easily to audit a local or remote server. If you just have a sar output file, pgCluu can also be use to draw graphs about the system utilization from this file. pgCluu is a collection of tools published under the PostgreSQL License to be shared, any one is welcome to contribute. For more information take a look at http://pgcluu.darold.net/ View a sample report at http://pgcluu.darold.net/example/index.html Links pgCluu is created and maintained by Gilles Darold. Web/Demo site: http://pgcluu.darold.net/ Download: https://github.com/darold/pgcluu/releases Development: https://github.com/darold/pgcluu Documentation: https://github.com/darold/pgcluu/blob/master/README [Less]

The PostgreSQL Global Development Group today announced the release of PostgreSQL 14, the latest version of the world’s most advanced open source database. PostgreSQL 14 brings a variety of features that help developers and administrators deploy ... [More] their data-backed applications. PostgreSQL continues to add innovations on complex data types, including more convenient access for JSON and support for noncontiguous ranges of data. This latest release adds to PostgreSQL's trend on improving high performance and distributed data workloads, with advances in connection concurrency, high-write workloads, query parallelism and logical replication. "This latest release of PostgreSQL advances our users' ability to manage data workloads at scale, enhances observability, and contains new features that help application developers," said Magnus Hagander, a PostgreSQL Core Team member. "PostgreSQL 14 is a testament to the dedication of the global PostgreSQL community in addressing feedback and continuing to deliver innovative database software that is deployed by organizations large and small." PostgreSQL, an innovative data management system known for its reliability and robustness, benefits from over 25 years of open source development from a global developer community and has become the preferred open source relational database for organizations of all sizes. JSON Conveniences and Multiranges PostgreSQL has supported manipulating JSON data since the release of PostgreSQL 9.2, though retrieval of values used a unique syntax. PostgreSQL 14 now lets you access JSON data using subscripts, e.g. a query like SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release']; now works. This aligns PostgreSQL with syntax that is commonly recognized for retrieving information from JSON data. The subscripting framework added to PostgreSQL 14 can be generally extended to other nested data structures, and is also applied to the hstore data type in this release. Range types, also first released in PostgreSQL 9.2, now have support for noncontiguous ranges through the introduction of the "multirange" data type. A multirange is an ordered list of ranges that are nonoverlapping, which lets developers write simpler queries for dealing with complex sequences of ranges. The range types native to PostgreSQL (dates, times, numbers) support multiranges, and other data types can be extended to use multirange support. Performance Improvements for Heavy Workloads PostgreSQL 14 provides a significant throughput boost on workloads that use many connections, with some benchmarks showing a 2x speedup. This release continues on the recent improvements to the management of B-tree indexes by reducing index bloat on tables with frequently updated indexes. PostgreSQL 14 introduces the ability to pipeline queries to a database, which can significantly improve performance over high latency connections or for workloads with many small write (INSERT/UPDATE/DELETE) operations. As this is a client-side feature, you can use pipeline mode with any modern PostgreSQL database with the version 14 client or a client driver built with version 14 of libpq. Enhancements for Distributed Workloads Distributed PostgreSQL databases stand to benefit from PostgreSQL 14. When using logical replication, PostgreSQL can now stream in-progress transactions to subscribers, with significant performance benefits for applying large transactions on subscribers. PostgreSQL 14 also adds several other performance enhancements to the logical decoding system that powers logical replication. Foreign data wrappers, which are used for working with federated workloads across PostgreSQL and other databases, can now leverage query parallelism in PostgreSQL 14. This release implements this ability in the postgres_fdw, the foreign data wrapper that interfaces with other PostgreSQL databases. In addition to supporting query parallelism, postgres_fdw can now bulk insert data on foreign tables and import table partitions with the IMPORT FOREIGN SCHEMA directive. Administration and Observability PostgreSQL 14 extends its performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees. This release also adds a vacuum "emergency mode" that is designed to prevent transaction ID wraparound. ANALYZE, used to collect database statistics, now runs significantly faster in PostgreSQL 14 due to its own performance improvements. Compression for PostgreSQL's TOAST system, which is used to store larger data like blocks of text or geometries, can now be configured. PostgreSQL 14 adds LZ4 compression for TOAST columns while retaining support for pglz compression. PostgreSQL 14 adds several new features to help with monitoring and observability, including the ability to track the progress of COPY commands, write-ahead-log (WAL) activity, and statistics on replication slots. Enabling compute_query_id lets you uniquely track a query through several PostgreSQL features, including pg_stat_activity, EXPLAIN VERBOSE, and more. SQL Performance, Conformance, and Convenience Query planning and execution benefit from enhancements in PostgreSQL 14. This release includes several improvements to PostgreSQL's query parallelism support, including better performance of parallel sequential scans, the ability for PL/pgSQL to execute parallel queries when using the RETURN QUERY command, and enabling REFRESH MATERIALIZED VIEW to execute parallel queries. Additionally, queries that use nested loop joins may see performance benefits through additional caching that is added in PostgreSQL 14. Extended statistics can now be used in PostgreSQL 14 for expressions. Additionally, window functions can now benefit from incremental sorts, a feature introduced in PostgreSQL 13. Stored procedures, which allow for transaction control in a block of code, can now return data by using OUT parameters. PostgreSQL 14 introduces the ability to "bin", or align, timestamps to a particular interval using the date_bin function. This release also adds the SQL conforming SEARCH and CYCLE clauses to help with ordering and cycle detection for recursive common table expressions. Security Enhancements PostgreSQL 14 makes it convenient to assign read-only and write-only privileges to users on tables, views, and schemas using the pg_read_all_data and pg_write_all_data predefined roles. Additionally, this release now makes the standards-compliant SCRAM-SHA-256 password management and authentication system the default on new PostgreSQL instances. About PostgreSQL PostgreSQL is the world's most advanced open source database, with a global community of thousands of users, contributors, companies and organizations. Built on over 30 years of engineering, starting at the University of California, Berkeley, PostgreSQL has continued with an unmatched pace of development. PostgreSQL's mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security, and stability. Links Download Release Notes Press Kit Security Page Versioning Policy Follow @postgresql on Twitter [Less]

Grenoble - September 28th, 2021 PostgreSQL Global Temporary Tables pgtt is a PostgreSQL extension to create, manage and use Oracle-style Global Temporary Tables. The main interest of this extension is to reproduce Oracle behavior with GTT when you ... [More] can not or don't want to rewrite the application code when migrating to PostgreSQL. In all other case best is to rewrite the code to use standard PostgreSQL temporary tables. This is a maintenance release to add support for upcomming PostgreSQL 14 and fixed some issues reported in the past three months. Add support to PostgreSQL 14. Thanks to Devrim Gunduz for the report and MigOps Inc for the patch. Remove support to PG 9.5 which obviously was not working. Minimal PostgreSQL version for this extension is 9.6. Fix documentation menu. Fix creation of GTT when there is a CHECK constraint with string constant. For the complete list of changes, please checkout the release note on https://github.com/darold/pgtt/blob/master/ChangeLog Links & Credits pgtt is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to [email protected]. Links Download: https://github.com/darold/pgtt/releases/ Support: use GitHub report tool at https://github.com/darold/pgtt/issues [Less]

Grenoble - September 28th, 2021 pgFormatter 5.1 released Version 5.1 of pgFormatter, a free and reliable tool used to format SQL and PLPGSQL code, has been officially released and is publicly available for download. A demonstration site is available ... [More] online at http://sqlformat.darold.net/ pgFormatter is the most advanced SQL and PlPgsql code formatter and beautifier dedicated to PostgreSQL. It is provided as a CLI or a CGI program. This is a maintenance release to fix issues reported by users since the last six months. As usual there is also some improvements and new features. Alternate format type (option -t) now keep enumeration in GROUP BY clause on a single line. Add new option -k or --keep-newline to preserve empty line in plpgsql code. Look for .pg_format configuration file in local directory first then in the $HOME directory. Force UTF8 encoding for all I/O to solve unicode character in object name parsing issues. For the complete list of changes see: https://github.com/darold/pgFormatter/blob/master/ChangeLog Links & Credits Thank to the developers who submitted patches and users who reported bugs and feature requests, they are all cited in the ChangeLog file. pgFormatter is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools. Links: Website: http://sqlformat.darold.net Download1: https://github.com/darold/pgFormatter/releases Download2: http://sourceforge.net/projects/pgformatter/ Development: https://github.com/darold/pgFormatter Changelog: https://github.com/darold/pgFormatter/blob/master/ChangeLog About pgFormatter pgFormatter is a SQL and PlPgsql formatter/beautifier that supports keywords from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011 and PostgreSQL specifics keywords. May works with any other databases too. It shares the same code with pgBadger, so any improvement made in the parser is reversed to pgBadger. Tool created and maintained by Gilles Darold. pgFormatter works on any platform and is available under the PostgreSQL licence. [Less]

PostgreSQL Weekly News - September 26, 2021 PostgreSQL 14 Release Candidate 1 released. Test! PostgreSQL Product News JDBC 42.2.24 released https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.24 check_pgbackrest 2.1, a ... [More] Nagios-compatible monitor for pgBackRest, released. https://github.com/dalibo/check_pgbackrest/releases sqlite_fdw 2.1.0 released. PostgreSQL Jobs for September https://archives.postgresql.org/pgsql-jobs/2021-09/ PostgreSQL in the News Planet PostgreSQL: https://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 [email protected]. Applied Patches Tomáš Vondra pushed: Disallow extended statistics on system columns. Since introduction of extended statistics, we've disallowed references to system columns. So for example CREATE STATISTICS s ON ctid FROM t; would fail. But with extended statistics on expressions, it was possible to work around this limitation quite easily CREATE STATISTICS s ON (ctid::text) FROM t; This is an oversight in a4d75c86bf, fixed by adding a simple check. Backpatch to PostgreSQL 14, where support for extended statistics on expressions was introduced. Backpatch-through: 14 Discussion: https://postgr.es/m/20210816013255.GS10479%40telsasoft.com https://git.postgresql.org/pg/commitdiff/c9eeef2a15c02ff7dd2bf3251dbee925b050fc0f Free memory after building each statistics object. Until now, all extended statistics on a given relation were built in the same memory context, without resetting. Some of the memory was released explicitly, but not all of it - for example memory allocated while detoasting values is hard to free. This is how it worked since extended statistics were introduced in PostgreSQL 10, but adding support for extended stats on expressions made the issue somewhat worse as it increases the number of statistics to build. Fixed by adding a memory context which gets reset after building each statistics object (all the statistics kinds included in it). Resetting it after building each statistics kind would be even better, but it would require more invasive changes and copying of results, making it harder to backpatch. Backpatch to PostgreSQL 10, where extended statistics were introduced. Author: Justin Pryzby Reported-by: Justin Pryzby Reviewed-by: Tomas Vondra Backpatch-through: 10 Discussion: https://www.postgresql.org/message-id/20210915200928.GP831%40telsasoft.com https://git.postgresql.org/pg/commitdiff/83772cc78e0392a247231ba510c61b6612b93b3f Release memory allocated by dependency_degree. Calculating degree of a functional dependency may allocate a lot of memory - we have released mot of the explicitly allocated memory, but e.g. detoasted varlena values were left behind. That may be an issue, because we consider a lot of dependencies (all combinations), and the detoasting may happen for each one again. Fixed by calling dependency_degree() in a dedicated context, and resetting it after each call. We only need the calculated dependency degree, so we don't need to copy anything. Backpatch to PostgreSQL 10, where extended statistics were introduced. Backpatch-through: 10 Discussion: https://www.postgresql.org/message-id/20210915200928.GP831%40telsasoft.com https://git.postgresql.org/pg/commitdiff/ad8a166ca86846ab691bd6dafc695e0f7dd96012 Tom Lane pushed: Doc: minor improvements for "Formatting" section. Add more-specific links into the source tree. https://git.postgresql.org/pg/commitdiff/5577cd571ad3528471152f68636ac03c80576977 Fix misevaluation of STABLE parameters in CALL within plpgsql. Before commit 84f5c2908, a STABLE function in a plpgsql CALL statement's argument list would see an up-to-date snapshot, because exec_stmt_call would push a new snapshot. I got rid of that because the possibility of the snapshot disappearing within COMMIT made it too hard to manage a snapshot across the CALL statement. That's fine so far as the procedure itself goes, but I forgot to think about the possibility of STABLE functions within the CALL argument list. As things now stand, those'll be executed with the Portal's snapshot as ActiveSnapshot, keeping them from seeing updates more recent than Portal startup. (VOLATILE functions don't have a problem because they take their own snapshots; which indeed is also why the procedure itself doesn't have a problem. There are no STABLE procedures.) We can fix this by pushing a new snapshot transiently within ExecuteCallStmt itself. Popping the snapshot before we get into the procedure proper eliminates the management problem. The possibly-useless extra snapshot-grab is slightly annoying, but it's no worse than what happened before 84f5c2908. Per bug #17199 from Alexander Nawratil. Back-patch to v11, like the previous patch. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/4476bcb8773b306b9ca84bf2fadcf30acfa2c687 Doc: extend warnings about collation-mismatch hazards in postgres_fdw. Be a little more vocal about the risks of remote collations not matching local ones. Actually fixing these risks seems hard, and I've given up on the idea that it might be back-patchable. So the best we can do for the back branches is add documentation. Per discussion of bug #16583 from Jiří Fejfar. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7b0be9fb2dddb214db2bed0e137b9b42c1479455 Avoid unnecessary division in interval_cmp_value(). Splitting the time field into days and microseconds is pretty useless when we're just going to recombine those values. It's unclear if anyone will notice the speedup in real-world cases, but a cycle shaved is a cycle earned. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/e94c1a55dada49772622d2be2d17a2a9973b2661 Álvaro Herrera pushed: Doc: add glossary term for "auxiliary process". Add entries for existing processes not documented, too, and adjust existing definitions for consistency. Per question from Bharath Rupireddy. Author: Justin Pryzby [email protected] Author: Alvaro Herrera [email protected] Discussion: https://postgr.es/m/CALj2ACVpYCT0M+k8zqrAa4ZQZV+ce5s6G=yajwoS1m=h-jj8NQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d3014fff4cd4dcaf4b2764d96ad038f3be7413b0 Document XLOG_INCLUDE_XID a little better. I noticed that commit 0bead9af484c left this flag undocumented in XLogSetRecordFlags, which led me to discover that the flag doesn't actually do what the one comment on it said it does. Improve the situation by adding some more comments. Backpatch to 14, where the aforementioned commit appears. Author: Álvaro Herrera [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/ade24dab97a20dae74fb57c0106dfe0e0303541b Andres Freund pushed: pgstat: Split out relation stats handling from AtEO[Sub]Xact_PgStat() etc. An upcoming patch will add additional work to these functions. To avoid the functions getting too complicated / doing too many things at once, split out sub-tasks into their own functions. Author: Andres Freund [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/e1f958d759ff71a264973d13e9bc86c7db822928 pgstat: Prepare to use mechanism for truncated rels also for droppped rels. The upcoming shared memory stats patch drops stats for dropped objects in a transactional manner, rather than removing them later as part of vacuum. This means that stats for DROP inside a transaction needs to handle aborted (sub-)transactions similar to TRUNCATE: The stats up to the DROP should be restored. Rename the existing infrastructure in preparation. Author: Andres Freund [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/6b9501660c9384476ca9a04918f5cf94379e419e Peter Geoghegan pushed: Remove overzealous index deletion assertion. A broken HOT chain is not an unexpected condition, even when the offset number points past the end of the page's line pointer array. heap_prune_chain() does not (and never has) treated this condition as unexpected, so derivative code in heap_index_delete_tuples() shouldn't do so either. Oversight in commit 4228817449. The assertion can probably only fail on Postgres 14 and master. Earlier releases don't have commit 3c3b8a4b, which taught VACUUM to truncate the line pointer array of heap pages. Backpatch all the same, just to be consistent. Author: Peter Geoghegan [email protected] Reported-By: Alexander Lakhin [email protected] Discussion: https://postgr.es/m/[email protected] Backpatch: 12-, just like commit 4228817449. https://git.postgresql.org/pg/commitdiff/5e6716cde5749aea506dd3f30b099b6e9b4c5af8 Fix "single value strategy" index deletion issue. It is not appropriate for deduplication to apply single value strategy when triggered by a bottom-up index deletion pass. This wastes cycles because later bottom-up deletion passes will overinterpret older duplicate tuples that deduplication actually just skipped over "by design". It also makes bottom-up deletion much less effective for low cardinality indexes that happen to cross a meaningless "index has single key value per leaf page" threshold. To fix, slightly narrow the conditions under which deduplication's single value strategy is considered. We already avoided the strategy for a unique index, since our high level goal must just be to buy time for VACUUM to run (not to buy space). We'll now also avoid it when we just had a bottom-up pass that reported failure. The two cases share the same high level goal, and already overlapped significantly, so this approach is quite natural. Oversight in commit d168b666, which added bottom-up index deletion. Author: Peter Geoghegan [email protected] Discussion: https://postgr.es/m/CAH2-WznaOvM+Gyj-JQ0X=JxoMDxctDTYjiEuETdAGbF5EUc3MA@mail.gmail.com Backpatch: 14-, where bottom-up deletion was introduced. https://git.postgresql.org/pg/commitdiff/dd94c2852e6e3a246b9fd64bf2d9c7fc01020905 Document issue with heapam line pointer truncation. Checking that an offset number isn't past the end of a heap page's line pointer array was just a defensive sanity check for HOT-chain traversal code before commit 3c3b8a4b. It's etrictly necessary now, though. Add comments that reference the issue to code in heapam that needs to get it right. Per suggestion from Alexander Lakhin. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/c7aeb775df895db240dcd6f47242f7e08899adfb nbtree README: Add note about latestRemovedXid. Point out that index tuple deletion generally needs a latestRemovedXid value for the deletion operation's WAL record. This is bound to be the most expensive part of the whole deletion operation now that it takes place up front, during original execution. This was arguably an oversight in commit 558a9165e08, which moved the work required to generate these values from index deletion REDO routines to original execution of index deletion operations. https://git.postgresql.org/pg/commitdiff/48064a8d330db259076fb7b2300544fbf65f4109 vacuumlazy.c: Remove obsolete 'onecall' comment. Remove obsolete reference to lazy_vacuum()'s onecall argument. The function argument was removed by commit 3499df0dee. Also remove adjoining comment block that introduces the wraparound failsafe concept. Talking about the failsafe here no longer makes sense, since lazy_vacuum() (and related functions) are no longer the only place where the failsafe might be triggered. This has been the case since commit c242baa4a8 taught VACUUM to consider triggering the failsafe mechanism during its initial heap scan. https://git.postgresql.org/pg/commitdiff/c1a47dfe2e9f814e61377f47aa79a113a4c73a63 Update obsolete nbtree deletion comments. _bt_delitems_delete() is no longer the high-level entry point used by index tuple deletion driven by index tuples whose LP_DEAD bits are set (now called "simple index tuple deletion"). It became a lower level routine that's only called by _bt_delitems_delete_check() following commit d168b66682. https://git.postgresql.org/pg/commitdiff/ce2a86053380f7e82dc8318ac48a22a7ab266398 Michaël Paquier pushed: Introduce GUC shared_memory_size_in_huge_pages. This runtime-computed GUC shows the number of huge pages required for the server's main shared memory area, taking advantage of the work done in 0c39c29 and 0bd305e. This is useful for users to estimate the amount of huge pages required for a server as it becomes possible to do an estimation without having to start the server and potentially allocate a large chunk of shared memory. The number of huge pages is calculated based on the existing GUC huge_page_size if set, or by using the system's default by looking at /proc/meminfo on Linux. There is nothing new here as this commit reuses the existing calculation methods, and just exposes this information directly to the user. The routine calculating the huge page size is refactored to limit the number of files with platform-specific flags. This new GUC's name was the most popular choice based on the discussion done. This is only supported on Linux. I have taken the time to test the change on Linux, Windows and MacOS, though for the last two ones large pages are not supported. The first one calculates correctly the number of pages depending on the existing GUC huge_page_size or the system's default. Thanks to Andres Freund, Robert Haas, Kyotaro Horiguchi, Tom Lane, Justin Pryzby (and anybody forgotten here) for the discussion. Author: Nathan Bossart Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/43c1c4f65eab77bcfc4f535a7e9ac0421e0cf2a5 Fix places in TestLib.pm in need of adaptation to the output of Msys perl. Contrary to the output of native perl, Msys perl generates outputs with CRLFs characters. There are already places in the TAP code where CRLFs (\r\n) are automatically converted to LF (\n) on Msys, but we missed a couple of places when running commands and using their output for comparison, that would lead to failures. This problem has been found thanks to the test added in 5adb067 using TestLib::command_checks_all(), but after a closer look more code paths were missing a filter. This is backpatched all the way down to prevent any surprises if a new test is introduced in stable branches. Reviewed-by: Andrew Dunstan, Álvaro Herrera Discussion: https://postgr.es/m/[email protected] Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/0d91c52a8fc71bfe5664a13368e42e1dabc5fe78 Fix some issues with TAP tests for postgres -C. This addresses two issues with the tests added in 0c39c292 for runtime GUCs: - Re-enable the test on Msys. The test could fail because of \r\n generated by Msys perl. 0d91c52a has taken care of this issue. - Allow the test to run in the context of a privileged account. CIs running under privileged accounts would fail on permission failures, as reported by Andres Freund. This issue is fixed by wrapping the postgres command within pg_ctl as the latter will take care of any permissions needed. The test checking a failure of postgres -C for a runtime parameter with an instance running is removed, as pg_ctl produces an unstable error code (no need for a CI to reproduce that). Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/1a9d802828110c87a207785aaf6b00d8917a86ad doc: Add missing markup in CREATE EVENT TRIGGER page. Reported-by: rir Discussion: https://postgr.es/m/20210924183658.3syyitp3yuxjv2fp@localhost Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/1ab70b11e6425c955c24aa301188de32356bebb8 doc: Improve description of index vacuuming with GUCs. Index vacuums may happen multiple times depending on the number of dead tuples stored, as of maintenance_work_mem for a manual VACUUM. For autovacuum, this is controlled by autovacuum_work_mem instead, if set. The documentation mentioned the former, but not the latter in the context of autovacuum. Reported-by: Nikolai Berkoff Author: Laurenz Albe, Euler Taveira Discussion: https://postgr.es/m/[email protected] Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/1ba841072ebeb1a6605395950a51c869de42a104 Fix typos in docs. Author: Justin Pryzby Discussion: https://postgr.es/m/[email protected] Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/7c1d8a243f8bd46604c9b292f392aab170eed821 Amit Kapila pushed: Add parent table name in an error in reorderbuffer.c. This can help in troubleshooting the cause of a particular error that can occur during decoding. Author: Jeremy Schneider Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/5e77625b260a781316bb94ea9750dc66c50152bf Invalidate all partitions for a partitioned table in publication. Updates/Deletes on a partition were allowed even without replica identity after the parent table was added to a publication. This would later lead to an error on subscribers. The reason was that we were not invalidating the partition's relcache and the publication information for partitions was not getting rebuilt. Similarly, we were not invalidating the partitions' relcache after dropping a partitioned table from a publication which will prohibit Updates/Deletes on its partition without replica identity even without any publication. Reported-by: Haiying Tang Author: Hou Zhijie and Vignesh C Reviewed-by: Vignesh C and Amit Kapila Backpatch-through: 13 Discussion: https://postgr.es/m/OS0PR01MB6113D77F583C922F1CEAA1C3FBD29@OS0PR01MB6113.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/4548c76738b368a11a5dad052f9653a349eeb52c Peter Eisentraut pushed: Make use of PG_INT64_MAX/PG_INT64_MIN. This code was written before those symbols were introduced, but now we can simplify it. https://git.postgresql.org/pg/commitdiff/f9ea2960310c235a7ae97847c0757eba9f6f9a85 Add missing $Test::Builder::Level settings. One of these was accidentally removed by c50624c. The others are added by analogy. Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/73aa5e0cafd0d577fe464ed1d9ac317103f27ea4 Fujii Masao pushed: postgres_fdw: Refactor transaction rollback code to avoid code duplication. In postgres_fdw, pgfdw_xact_callback() and pgfdw_subxact_callback() callback functions do almost the same thing to rollback remote toplevel- and sub-transaction. But previously their such rollback logics were implemented separately in each function and in different way. Which could decrease the readability and maintainability of the code. To fix the issue, this commit creates the common function to rollback remote transactions, and makes those callback functions use it. Which allows us to avoid unnecessary code duplication. Author: Fujii Masao Reviewed-by: Zhihong Yu, Bharath Rupireddy Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/85c69611288f4096b7460d980bedaa777f824d24 Alexander Korotkov pushed: Split macros from visibilitymap.h into a separate header. That allows to include just visibilitymapdefs.h from file.c, and in turn, remove include of postgres.h from relcache.h. Reported-by: Andres Freund Discussion: https://postgr.es/m/20210913232614.czafiubr435l6egi%40alap3.anarazel.de Author: Alexander Korotkov Reviewed-by: Andres Freund, Tom Lane, Alvaro Herrera Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/b92f9f74436c48054e7743548edc8ca50e97c6b1 John Naylor pushed: Add exception for unicode_east_asian_fw_table.h to cpluspluscheck. unicode_east_asian_fw_table.h should not be compiled standalone, similarly to unicode_combining_table.h, but cpluspluscheck did not get the memo. Oversight in bab982161. Per report from Tom Lane https://git.postgresql.org/pg/commitdiff/a315b19cceeb2ccbe17c7ddd6e7c90911b325f9b Add exception for unicode_east_asian_fw_table.h to headerscheck also. Followup to a315b19cc https://git.postgresql.org/pg/commitdiff/88b0ae15bc099df6192a3b69b853f86fb015339a [Less]