PostgreSQL Database Server

The pgAdmin Development Team is pleased to announce pgAdmin 4 version 6.2. This release of pgAdmin 4 includes 22 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: Features: Added support for Aggregate and Operator nodes in view-only mode. Ensure that users should be able to modify the REMOTE_USER environment variable as per their environment by introducing the new config parameter WEBSERVER_REMOTE_USER. Bugs/Housekeeping: Fixed pgAdmin freezing issue by providing the error message for the operation that can't perform due to a lock on the particular table. Fixed an issue where pgAdmin is not opening properly. Ensure that internal authentication, when combined with other authentication providers, the order of internal sources should not matter while picking up the provider. Ensure that the user should be able to navigate browser tree objects using arrow keys from the keyboard. Fixed an issue where database nodes are not getting loaded behind a reverse proxy with SSL. Fixed an issue where JSON editor preview colours had inappropriate contrast in dark mode. Fixed JSON Editor scrolling issue in code mode. Ensure that changing themes should work on Windows when system high contrast mode is enabled. Ensure that the Binary path for PG14 should be visible in the preferences. Fixed an issue where textarea should be allowed to resize and have more than 255 chars. Note: We regret that once again we have been unable to publish an updated Python package to PyPi. This is due to the need for a quota increase for pgAdmin on PyPi that is currently awaiting action from the PyPi team. 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. [Less]

StackGres 1.0.0 is an Open Source Postgres-as-a-Service that runs on any Kubernetes environment. StackGres is the Postgres platform with more Postgres extensions available: 120 as of today. Many more to come in the future. A feature-rich Postgres ... [More] Platform StackGres contains the full Stack of Postgres components needed for production: High availability and automated failover, using Patroni. Built-in, enabled by default connection-pooling. Configurable automated backups with retention policies. Prometheus metrics exporter, with auto-binding. Customized Postgres metrics, Grafana dashboards and Postgres alerts. Expertly tuned default configurations --which you can optionally override. StackGres: innovating the Postgres Platform StackGres has also introduced significant innovations: Using Envoy to proxy all Postgres traffic, thanks to the Postgres filter developed by OnGres in collaboration with the Envoy Community. A fully-featured Web Console. Distributed logs: push all Postgres and Patroni logs from all pods to a central server, which is a separate, StackGres-managed Postgres database with Timescale. Fully automated "Day 2" DBA operations, including: minor and major version upgrades, controlled cluster restart, container version upgrades, vacuum, repack and even benchmarks! A system to dynamically load Postgres extensions into the containers. This allows StackGres to ship lighter containers, while supporting potentially hundreds of extensions: 120 as of today. Getting started Demo/Quickstart. Tutorial. StackGres 1.0.0 announcement blog post Join the StackGres Community Slack and/or Discord channels for feedback, ideas, contributions or getting help. StackGres, open source developed with love by OnGres. [Less]

Grenoble, France - November 15, 2021 Ora2Pg 23.0 released Version 23.0 of Ora2Pg, a free and reliable tool used to migrate an Oracle database to PostgreSQL, has been officially released and is publicly available for download. This release fix ... [More] several issues reported since last release and adds several new features and improvements. New features and improvements: Add data validation feature consisting in comparing data retrieved from a foreign table pointing to the source Oracle table and a local PostgreSQL table resulting from the data export. Add replacement of UTL_RAW.CAST_TO_RAW with encode(). Add rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')). Add detection of XML function for migration assessment cost. Add DBMS_RANDOM to the list of Oraclism handled by Orafce. Add support to mysql_fdw foreign data wrapper to export data PostgreSQL tables. Allow to transform all NUMBER(,scale) to an other data type by a redefinition like NUMBER(\,2):decimal in the DATA_TYPE configuration directive. Add information on how to use SSL encrypted connection to documentation. Add TEST_COUNT action to just report the row count diff between Oracle and PostgreSQL tables. Allow multiprocess for TEST_DATA action to validate data migration. Use -P or PARALLEL_TABLES to set the number of parallel tables checked. New command line options: Add new option --blob_to_lo that can be used to export BLOB as large objects. When used with TABLE action, the BLOB column will be translated into oid PostgreSQL data type. When used with the INSERT export action BLOB data will be stored as large objects in the pg_largeobjects table and the oid referencing this large object will be stored in the main table instead of a bytea. Add command line option -W | --where clause to set the WHERE clauses to apply to the Oracle query to retrieve data. It can be used multiple time. It will override the WHERE configuration directive. New configuration directives: Add DATA_VALIDATION_ORDERING configuration directive enabled by default. Order of rows between both sides are different once the data have been modified. In this case data must be ordered using a primary key or a unique index, that mean that a table without such object can not be compared. If the validation is done just after data import in mode single process and without any data modification the validation can be done on all tables without any ordering. Add DATA_VALIDATION_ERROR to stop validating data from a table after a certain amount of row mismatch. Default is to stop after 10 rows validation errors. Add VARCHAR_TO_TEXT configuration directive. By default VARCHAR2 without size constraint are tranlated into text PG data type. If you want to use varchar instead, disable this directive. Backward compatibility changes: Add FORCE_PLSQL_ENCODING configuration directive. In previous version Ora2Pg was encoding all functions code to ut8, this is no more the case because it could result in double encoding. To recover the old behavior (not recommanded) enable this directive. Change behavior regarding RAW columns. Now RAW(16) and RAW(32) columns or a RAW columns with "SYS_GUID()" as default value are now automatically translated into uuid. Data will be automatically migrated as PostgreSQL uuid data type provided by the "uuid-ossp" extension. For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog Thanks to all contributors, they are all cited in the changelog file. Links & Credits I would like to thank all users who submitted patches and users who reported bugs and feature requests, they are all cited the ChangeLog file. Ora2Pg 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: Website: https://www.ora2pg.com/ Download1: https://github.com/darold/ora2pg/releases Download2: https://sourceforge.net/projects/ora2pg/ Development: https://github.com/darold/ora2pg Changelog: https://github.com/darold/ora2pg/blob/master/changelog Documentation: https://www.ora2Pg.com/documentation.html About Ora2Pg : Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL. It is developed since 2001 and can export most of the Oracle objects and data into PostgreSQL compatible code. Ora2Pg works on any platform and is available under the GPL v3 licence. Docs, Download & Support at http://www.ora2pg.com/ [Less]

PostgreSQL Weekly News - November 14, 2021 PostgreSQL 14.1, 13.5, 12.9, 11.14, 10.19, and 9.6.24 released. This is the final release in the 9.6 series, so put those upgrade plans in action if you haven't already. PostgreSQL Product News Pgpool-II ... [More] 4.3 beta1 a connection pooler and statement replication system for PostgreSQL, released Odyssey 1.2, a multi-threaded connection pooler for PostgreSQL, released. https://github.com/yandex/odyssey/releases pgbouncer 1.16.1, a connection pooler and more for PostgreSQL, released PostgreSQL Jobs for November https://archives.postgresql.org/pgsql-jobs/2021-11/ 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 David Rowley pushed: Fix incorrect hash equality operator bug in Memoize. In v14, because we don't have a field in RestrictInfo to cache both the left and right type's hash equality operator, we just restrict the scope of Memoize to only when the left and right types of a RestrictInfo are the same. In master we add another field to RestrictInfo and cache both hash equality operators. Reported-by: Jaime Casanova Author: David Rowley Discussion: https://postgr.es/m/20210929185544.GB24346%40ahch-to Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/39a3105678a247bbfdc132cd95db5b515b8cd7f6 Tom Lane pushed: Reject extraneous data after SSL or GSS encryption handshake. The server collects up to a bufferload of data whenever it reads data from the client socket. When SSL or GSS encryption is requested during startup, any additional data received with the initial request message remained in the buffer, and would be treated as already-decrypted data once the encryption handshake completed. Thus, a man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could be abused to send faked SQL commands to the server, although that would only work if the server did not demand any authentication data. (However, a server relying on SSL certificate authentication might well not do so.) To fix, throw a protocol-violation error if the internal buffer is not empty after the encryption handshake. Our thanks to Jacob Champion for reporting this problem. Security: CVE-2021-23214 https://git.postgresql.org/pg/commitdiff/28e24125541545483093819efae9bca603441951 libpq: reject extraneous data after SSL or GSS encryption handshake. libpq collects up to a bufferload of data whenever it reads data from the socket. When SSL or GSS encryption is requested during startup, any additional data received with the server's yes-or-no reply remained in the buffer, and would be treated as already-decrypted data once the encryption handshake completed. Thus, a man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could probably be abused to inject faked responses to the client's first few queries, although other details of libpq's behavior make that harder than it sounds. A different line of attack is to exfiltrate the client's password, or other sensitive data that might be sent early in the session. That has been shown to be possible with a server vulnerable to CVE-2021-23214. To fix, throw a protocol-violation error if the internal buffer is not empty after the encryption handshake. Our thanks to Jacob Champion for reporting this problem. Security: CVE-2021-23222 https://git.postgresql.org/pg/commitdiff/160c0258802d10b0600d7671b1bbea55d8e17d45 Fix incorrect format placeholder. Per buildfarm warnings. https://git.postgresql.org/pg/commitdiff/b0cf5444f9a8d915b2e9b44790025f17a7dc107f Fix instability in 026_overwrite_contrecord.pl test. We've seen intermittent failures in this test on slower buildfarm machines, which I think can be explained by assuming that autovacuum emitted some additional WAL. Disable autovacuum to stabilize it. In passing, use stringwise not numeric comparison to compare WAL file names. Doesn't matter at present, but they are hex strings not decimal ... Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/b66767b56b1cd082f3499a7e5a21b480dd004f51 Doc: improve protocol spec for logical replication Type messages. protocol.sgml documented the layout for Type messages, but completely dropped the ball otherwise, failing to explain what they are, when they are sent, or what they're good for. While at it, do a little copy-editing on the description of Relation messages. In passing, adjust the comment for apply_handle_type() to make it clearer that we choose not to do anything when receiving a Type message, not that we think it has no use whatsoever. Per question from Stefen Hillman. Discussion: https://postgr.es/m/CAPgW8pMknK5pup6=T4a_UG=Cz80Rgp=KONqJmTdHfaZb0RvnFg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c3b33698cf88550b017620f73b94b53029897f39 Fall back to unsigned int, not int, for socklen_t. It's a coin toss which of these is a better default assumption. However, of the machines we have in the buildfarm, the only ones relying on the fallback socklen_t definition are ancient HPUX, and on that platform unsigned int is the right choice. Minor tweak to ee3a1a5b6. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/01ec41a5fe4aa590dde18a2c551432aa1925caea postgres_fdw: suppress casts on constants in limited cases. When deparsing an expression of the form "remote_var OP constant", we'd normally apply a cast to the constant to make sure that the remote parser thinks it's of the same type we do. However, doing so is often not necessary, and it causes problems if the user has intentionally declared the local column as being of a different type than the remote column. A plausible use-case for that is using text to represent a type that's an enum on the remote side. A comparison on such a column will get shipped as "var = 'foo'::text", which blows up on the remote side because there's no enum = text operator. But if we simply leave off the explicit cast, the comparison will do exactly what the user wants. It's possible to do this without major risk of semantic problems, by relying on the longstanding parser heuristic that "if one operand of an operator is of type unknown, while the other one has a known type, assume that the unknown operand is also of that type". Hence, this patch leaves off the cast only if (a) the operator inputs have the same type locally; (b) the constant will print as a string literal or NULL, both of which are initially taken as type unknown; and (c) the non-Const input is a plain foreign Var. Rule (c) guarantees that the remote parser will know the type of the non-Const input; moreover, it means that if this cast-omission does cause any semantic surprises, that can only happen in cases where the local column has a different type than the remote column. That wasn't guaranteed to work anyway, and this patch should represent a net usability gain for such cases. One point that I (tgl) remain slightly uncomfortable with is that we will ignore an implicit RelabelType when deciding if the non-Const input is a plain Var. That makes it a little squishy to argue that the remote should resolve the Const as being of the same type as its Var, because then our Const is not the same type as our Var. However, if we don't do that, then this hack won't work as desired if the user chooses to use varchar rather than text to represent some remote column. That seems useful, so do it like this for now. We might have to give up the RelabelType-ignoring bit if any problems surface. Dian Fay, with review and kibitzing by me Discussion: https://postgr.es/m/C9LU294V7K4F.34LRRDU449O45@lamia https://git.postgresql.org/pg/commitdiff/f8abb0f5e114d8c309239f0faa277b97f696d829 Make psql's \password default to CURRENT_USER, not PQuser(conn). The documentation says plainly that \password acts on "the current user" by default. What it actually acted on, or tried to, was the username used to log into the current session. This is not the same thing if one has since done SET ROLE or SET SESSION AUTHENTICATION. Aside from the possible surprise factor, it's quite likely that the current role doesn't have permissions to set the password of the original role. To fix, use "SELECT CURRENT_USER" to get the role name to act on. (This syntax works with servers at least back to 7.0.) Also, in hopes of reducing confusion, include the role name that will be acted on in the password prompt. The discrepancy from the documentation makes this a bug, so back-patch to all supported branches. Patch by me; thanks to Nathan Bossart for review. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d6eb5a0c258d3da5471814bcc6ed6498129fee16 Robert Haas pushed: Minimal fix for unterminated tar archive problem. Commit 23a1c6578c87fca0e361c4f5f9a07df5ae1f9858 improved pg_basebackup's ability to parse tar archives, but also arranged to parse them only when we need to make some modification to the contents of the archive. That's a problem, because the server doesn't actually terminate tar archives. When the new parsing logic was engaged, pg_basebackup would properly terminate the tar file, but when it was skipped, pg_basebackup would just write whatever it got from the server, meaning that the terminator was missing. Most versions of tar are willing to overlook the missing terminator, but the AIX buildfarm animals were not. Fix by inventing a new kind of bbstreamer that just blindly adds a terminator, and using it whenever we don't parse the tar archive. Discussion: http://postgr.es/m/CA+TgmoZbNzsWwM4BE5Jb_qHncY817DYZwGf+2-7hkMQ27ZwsMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/57b5a9646d97a3e8a5b6b6d86b375cc8da6ac85c Have the server properly terminate tar archives. Earlier versions of PostgreSQL featured a version of pg_basebackup that wanted to edit tar archives but was too dumb to parse them properly. The server made things easier for the client by failing to add the two blocks of zero bytes that ought to end a tar file, leaving it up to the client to do that. But since commit 23a1c6578c87fca0e361c4f5f9a07df5ae1f9858, we don't need this hack any more, because pg_basebackup is now smarter and can parse tar files even if they are properly terminated! So change the server to always properly terminate the tar files. Older versions of pg_basebackup can't talk to new servers anyway, so there's no compatibility break. On the pg_basebackup side, we see still need to add the terminating zero bytes if we're talking to an older server, but not when the server is v15+. Hopefully at some point we'll be able to remove some of this compatibility cruft, but it seems best to hang on to it for now. In passing, add a file header comment to bbstreamer_tar.c, to make it clearer what's going on here. Discussion: http://postgr.es/m/CA+TgmoZbNzsWwM4BE5Jb_qHncY817DYZwGf+2-7hkMQ27ZwsMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5a1007a5088cd6ddf892f7422ea8dbaef362372f More cleanup of 'ThisTimeLineID'. In XLogCtlData, rename the structure member ThisTimeLineID to InsertTimeLineID and update the comments to make clear that it's only expected to be set after recovery is complete. In StartupXLOG, replace the local variables ThisTimeLineID and PrevTimeLineID with new local variables replayTLI and newTLI. In the old scheme, ThisTimeLineID was the replay TLI until we created a new timeline, and after that the replay TLI was in PrevTimeLineID. Now, replayTLI is the TLI from which we last replayed WAL throughout the entire function, and newTLI is either that, or the new timeline created upon promotion. Remove some misleading comments from the comment block just above where recoveryTargetTimeLineGoal and friends are declared. It's become incorrect, not only because ThisTimeLineID as a variable is now gone, but also because the rmgr code does not care about ThisTimeLineID and has not since what used to be the TLI field in the page header was repurposed to store the page checksum. Add a comment GetFlushRecPtr that it's only supposed to be used in normal running, and an assertion to verify that this is so. Per some ideas from Michael Paquier and some of my own. Review by Michael Paquier also. Discussion: http://postgr.es/m/CA+TgmoY1a2d1AnVR3tJcKmGGkhj7GGrwiNwjtKr21dxOuLBzCQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a27048cbcb582056bfbf15aa2f898b4a3ec74304 Fix thinko in assertion in basebackup.c. Commit 5a1007a5088cd6ddf892f7422ea8dbaef362372f tried to introduce an assertion that the block size was at least twice the size of a tar block, but I got the math wrong. My error was reported to me off-list. https://git.postgresql.org/pg/commitdiff/10eae82b27cebbb9586cda8baf8e3226496891d0 Improve performance of pgarch_readyXlog() with many status files. Presently, the archive_status directory was scanned for each file to archive. When there are many status files, say because archive_command has been failing for a long time, these directory scans can get very slow. With this change, the archiver remembers several files to archive during each directory scan, speeding things up. To ensure timeline history files are archived as quickly as possible, XLogArchiveNotify() forces the archiver to do a new directory scan as soon as the .ready file for one is created. Nathan Bossart, per a long discussion involving many people. It is not clear to me exactly who out of all those people reviewed this particular patch. Discussion: http://postgr.es/m/CA+TgmobhAbs2yabTuTRkJTq_kkC80-+jw=pfpypdOJ7+gAbQbw@mail.gmail.com Discussion: http://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/beb4e9ba1652a04f66ff20261444d06f678c0b2d Amit Kapila pushed: Rename some enums to use TABLE instead of REL. Commit 5a2832465f introduced some enums to represent all tables in schema publications and used REL in their names. Use TABLE instead of REL in those enums to avoid confusion with other objects like SEQUENCES that can be part of a publication in the future. In the passing, (a) Change one of the newly introduced error messages to make it consistent for Create and Alter commands, (b) add missing alias in one of the SQL Statements that is used to print publications associated with the table. Reported-by: Tomas Vondra, Peter Smith Author: Vignesh C Reviewed-by: Hou Zhijie, Peter Smith Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/b3812d0b9bcf00e8478186fc287940e17912248a Fujii Masao pushed: doc: Add index entries for pg_stat_statements configuration parameters. Author: Ken Kato Reviewed-by: Julien Rouhaud, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/ec21779a5847ed89fab19431abbdba794d4a998e Michaël Paquier pushed: Make some comments use the term "ProcSignal" for consistency. The surroundings in procsignal.c prefer using "ProcSignal" rather than "procsignal". Author: Bharath Rupireddy Discussion: https://postgr.es/m/CALj2ACX99ghPmm1M_O4r4g+YsXFjCn=qF7PeDXntLwMpht_Gdg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4cd046c203bbca2955182f78eabc06e831ffdbb1 Improve error messages for some callers of XLogReadRecord(). A couple of code paths related to logical decoding (WAL sender, slot advancing, etc.) use XLogReadRecord(), feeding on error messages generated by walreader.c on a failure. All those messages have no context, making it harder to spot from where an error could come even if these should not happen. All the other callers of XLogReadRecord() do that already. Reviewed-by: Kyotaro Horiguchi Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/c9c401a5e13accc4a3a775e3feeabdc5940c9178 Clean up compilation warnings coming from PL/Perl with clang-12~. clang-12 has introduced -Wcompound-token-split-by-macro, that is causing a large amount of warnings when building PL/Perl because of its interactions with upstream Perl. This commit adds one -Wno to CFLAGS at ./configure time if the flag is supported by the compiler to silence all those warnings. Upstream perl has fixed this issue, but it is going to take some time before this is spread across the buildfarm, and we have noticed that some animals would be useful with an extra -Werror to help with the detection of incorrect placeholders (see b0cf544), dangomushi being one. Reviewed-by: Tom Lane Discussion: https://postgr.es/m/YYr3qYa/[email protected] Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/9ff47ea414c4e6ace347fc4e59866e38b9bbceaf Fix buffer overrun in unicode string normalization with empty input. PostgreSQL 13 and newer versions are directly impacted by that through the SQL function normalize(), which would cause a call of this function to write one byte past its allocation if using in input an empty string after recomposing the string with NFC and NFKC. Older versions (v10~v12) are not directly affected by this problem as the only code path using normalization is SASLprep in SCRAM authentication that forbids the case of an empty string, but let's make the code more robust anyway there so as any out-of-core callers of this function are covered. The solution chosen to fix this issue is simple, with the addition of a fast-exit path if the decomposed string is found as empty. This would only happen for an empty string as at its lowest level a codepoint would be decomposed as itself if it has no entry in the decomposition table or if it has a decomposition size of 0. Some tests are added to cover this issue in v13~. Note that an empty string has always been considered as normalized (grammar "IS NF[K]{C,D} NORMALIZED", through the SQL function is_normalized()) for all the operations allowed (NFC, NFD, NFKC and NFKD) since this feature has been introduced as of 2991ac5. This behavior is unchanged but some tests are added in v13~ to check after that. I have also checked "make normalization-check" in src/common/unicode/, while on it (works in 13~, and breaks in older stable branches independently of this commit). The release notes should just mention this commit for v13~. Reported-by: Matthijs van der Vleuten Discussion: https://postgr.es/m/[email protected] Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/098c134556664d37b78ae87853a82f4a9d23a2c8 Fix memory overrun when querying pg_stat_slru. pg_stat_get_slru() in pgstatfuncs.c would point to one element after the end of the array PgStat_SLRUStats when finishing to scan its entries. This had no direct consequences as no data from the extra memory area was read, but static analyzers would rightfully complain here. So let's be clean. While on it, this adds one regression test in the area reserved for system views. Reported-by: Alexander Kozhemyakin, via AddressSanitizer Author: Kyotaro Horiguchi Discussion: https://postgr.es/m/[email protected] Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/a45ed975c58fde7303eeae488b313bf0314383f7 Peter Eisentraut pushed: Remove check for accept() argument types. This check was used to accommodate a staggering variety in particular in the type of the third argument of accept(). This is no longer of concern on currently supported systems. We can just use socklen_t in the code and put in a simple check that substitutes int for socklen_t if it's missing, to cover the few stragglers. Reviewed-by: Andres Freund [email protected] Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/ee3a1a5b636b69dde33d68c428dd56b3389a4538 Fix incorrect format placeholders. https://git.postgresql.org/pg/commitdiff/733e0391536dad99a2677ca5e19291854da5730f doc: Add referential actions to CREATE/ALTER TABLE synopsis. The general constraint synopsis references "referential_action", but this was not further defined in the synopsis section. Compared to the level of detail that the synopsis gives to other subclauses, this should surely be there. extracted from a patch by Paul Martinez [email protected] Discussion: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV=njbSMxf+rbDHpx=W=B7AEaMKn8dWn9OZJY7w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/db9f287711ac49d9799f93f664d6d101ff8f5891 Jeff Davis pushed: Add pg_checkpointer predefined role for CHECKPOINT command. Any user with the privileges of pg_checkpointer can issue a CHECKPOINT command. Reviewed-by: Stephen Frost Discussion: https://postgr.es/m/67a1d667e8ec228b5e07f232184c80348c5d93f4.camel%40j-davis.com https://git.postgresql.org/pg/commitdiff/4168a4745492cd54a0ffffc271b452525ef4dc60 Álvaro Herrera pushed: Restore lock level to set vacuum flags. Commit 27838981be9d mistakenly reduced the lock level from exclusive to shared that is acquired to set PGPROC->statusFlags; this was reverted by dcfff74fb166, but failed to do so in one spot. Fix it. Backpatch to 14. Noted by Andres Freund. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/0726c764bc4ec337a0216a546ce41c758d81600d Peter Geoghegan pushed: Update another obsolete reference in vacuumlazy.c. Addresses an oversight in commit 7ab96cf6. https://git.postgresql.org/pg/commitdiff/eb9baef8e92adf81c6adbe44f1d67878d850bff7 Update heap_page_prune() free space map comments. It is up to the heap_page_prune() caller to decide what to do about updating the FSM for a page following pruning. Update old comments that address what we might want to do as if it was the responsibility of heap_page_prune() itself. heap_page_prune() doesn't have enough high-level context to make a sensible choice. https://git.postgresql.org/pg/commitdiff/42f9427aa98a2245d29737e0f3b8aaf39a7f57ec Explain pruning pgstats accounting subtleties. Add a comment explaining why the pgstats accounting used during opportunistic heap pruning operations (to maintain the current number of dead tuples in the relation) needs to compensate by subtracting away the number of new LP_DEAD items. This is needed so it can avoid completely forgetting about tuples that become LP_DEAD items during pruning -- they should still count. It seems more natural to discuss this issue at the only relevant call site (opportunistic pruning), since the same issue does not apply to the only other caller (the VACUUM call site). Move everything there too. Author: Peter Geoghegan [email protected] Discussion: https://postgr.es/m/CAH2-Wzm7f+A6ej650gi_ifTgbhsadVW5cujAL3punpupHff5Yg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b0f7425ec2445678f32381de8bd3174d3cc2167e Noah Misch pushed: Report any XLogReadRecord() error in XlogReadTwoPhaseData(). Buildfarm members kittiwake and tadarida have witnessed errors at this site. The site discarded key facts. Back-patch to v10 (all supported versions). Reviewed by Michael Paquier and Tom Lane. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/335474691054a74d771f0e7c24d25e800e3a37b6 Andrew Dunstan pushed: Report found versions of required perl modules. Configure tests for the presence of perl modules required for TAP tests, and that they meet specified minimum version requirements. This patch makes it report the version of the module that's actually found rather than just an 'ok' message. This will help in deciding if we can upgrade minimum requirements for these modules. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/1593998ae858902e805eb0f8bf3b019399044471 Daniel Gustafsson pushed: Document PG_TEST_NOCLEAN in TAP test README. Commit 90627cf98 added support for retaining the data directory even on successful tests, but failed to document the environment variable which controls retention. This adds a small note to the TAP test README about PG_TEST_NOCLEAN which when set skips removing the data directories from successful tests. Reviewed-by: Tom Lane [email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/05d8785af2a192d436df5b7734aacb4e0bab5da8 [Less]

Hi, PGroonga 2.3.4 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 We implemented big features in PGroonga 2.3.3. Therefore, we also announce features of PGroonga 2.3.3 in this announcement. Here are highlights in PGroonga 2.3.3 and 2.3.4: Added support for PostgreSQL's RLS(Row Level security) Dropped support for PostgreSQL 9.6. Added support for applying PGroonga's WAL automatically in the standby server when we use stream replication. Added support for AlmaLinux 8. Fix a crash bug when EXPLAIN ANALYZE is executed with seqscan. This bug only occures in PGroonga 2.3.3. Added support for crash safe. (Experimental feature) See the following release note URL for details: https://groonga.org/en/blog/2021/11/09/pgroonga-2.3.4.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]

Announcement EDB announces the General Availability (GA) of BigAnimal, a fully managed PostgreSQL database in the cloud. What is BigAnimal? BigAnimal runs in your Microsoft Azure account, provides compatibility with Oracle databases, and is operated ... [More] and managed by EDB. You can deploy PostgreSQL or EDB Postgres Advanced Server and take advantage of many of our other enterprise tools. BigAnimal is now available on the Microsoft Azure Marketplace. What’s being offered? For DBAs, we’ve designed BigAnimal for flexibility and resiliency. We’ve included native multi-zone availability to deploy BigAnimal anywhere, continuous backups and monitoring provide peace of mind, fine-grained configuration control along with superuser access, and the choice to work with pure PostgreSQL or extend the database with EDB’s Advanced Server enterprise capabilities. Because BigAnimal is a cloud service, developers can rapidly provision the database without waiting for admins to set up servers or install additional software. We’ve provided developers full interface control via REST API and the CLI and the ability to connect to many popular software tools including Apache Superset, pgAdmin, and more. BigAnimal is also integrated with an extensive set of Microsoft Azure services, including core platform services, Azure Monitor, Azure Billing, and Azure Active Directory. For enterprise admins, we placed an emphasis on security and visibility into user activity within the database. BigAnimal can connect to your enterprise identity manager for SSO federation via Azure Active Directory and, within the portal, admins can easily manage permissions and RBAC settings. We’ve also included an Activity Log for admins to search, filter, and view auditable actions and keep an close eye on how the database is being accessed and queried. Sign up for a 30 day trial and try it out. You’ll get to use BigAnimal with zero subscription costs for a whole month, plus have access to our onboarding team and other PostgreSQL experts. To learn more, please visit: BigAnimal product page BigAnimal documentation Sign up for a 30 day free trial For any questions, please reach out to: [email protected] [Less]

Odyssey team is pleased to announce the release of Odyssey 1.2, a scalable multi-threaded connection pooler for PostgreSQL\GreenplumDB designed for the cloud. In this release: Reject extraneous data after SSL negotiation. This prevents attacks like ... [More] in CVE-2021-23214 and CVE-2021-23222. Support for PAM and LDAP. Prometheus integration. Better transaction and query time quantiles computation. A lot of small bug fixes and improvements. We appreciate any kind of feedback and contribution to the project. Odyssey 1.2 release can be found at the releases page. Thanks! [Less]

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 14.1, 13.5, 12.9, 11.14, 10.19, and 9.6.24. This release closes two security vulnerabilities and fixes over 40 bugs reported ... [More] over the last three months. Additionally, this is the final release of PostgreSQL 9.6. If you are running PostgreSQL 9.6 in a production environment, we suggest that you make plans to upgrade. For the full list of changes, please review the release notes. Security Issues CVE-2021-23214: Server processes unencrypted bytes from man-in-the-middle Versions Affected: 9.6 - 14. The security team typically does not test unsupported versions, but this problem is quite old. When the server is configured to use trust authentication with a clientcert requirement or to use cert authentication, a man-in-the-middle attacker can inject arbitrary SQL queries when a connection is first established, despite the use of SSL certificate verification and encryption. The PostgreSQL project thanks Jacob Champion for reporting this problem. CVE-2021-23222: libpq processes unencrypted bytes from man-in-the-middle Versions Affected: 9.6 - 14. The security team typically does not test unsupported versions, but this problem is quite old. A man-in-the-middle attacker can inject false responses to the client's first few queries, despite the use of SSL certificate verification and encryption. If more preconditions hold, the attacker can exfiltrate the client's password or other confidential data that might be transmitted early in a session. The attacker must have a way to trick the client's intended server into making the confidential data accessible to the attacker. A known implementation having that property is a PostgreSQL configuration vulnerable to CVE-2021-23214. As with any exploitation of CVE-2021-23214, the server must be using trust authentication with a clientcert requirement or using cert authentication. To disclose a password, the client must be in possession of a password, which is atypical when using an authentication configuration vulnerable to CVE-2021-23214. The attacker must have some other way to access the server to retrieve the exfiltrated data (a valid, unprivileged login account would be sufficient). The PostgreSQL project thanks Jacob Champion for reporting this problem. Bug Fixes and Improvements This update fixes over 40 bugs that were reported in the last several months. The issues listed below affect PostgreSQL 14. Some of these issues may also affect other supported versions of PostgreSQL. Some of these fixes include: Fix physical replication for cases where the primary crashes after shipping a WAL segment that ends with a partial WAL record. When applying this update, update your standby servers before the primary so that they will be ready to handle the fix if the primary happens to crash. Fix parallel VACUUM so that it will process indexes below the min_parallel_index_scan_size threshold if the table has at least two indexes that are above that size. This problem does not affect autovacuum. If you are affected by this issue, you should reindex any manually-vacuumed tables. Fix causes of CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY writing corrupt indexes. You should reindex any concurrently-built indexes. Fix for attaching/detaching a partition that could allow certain INSERT/UPDATE queries to misbehave in active sessions. Fix for creating a new range type with CREATE TYPE that could cause problems for later event triggers or subsequent executions of the CREATE TYPE command. Fix updates of element fields in arrays of a domain that is a part of a composite. Disallow the combination of FETCH FIRST WITH TIES and FOR UPDATE SKIP LOCKED. Fix corner-case loss of precision in the numeric power() function. Fix restoration of a Portal's snapshot inside a subtransaction, which could lead to a crash. For example, this could occur in PL/pgSQL when a COMMIT is immediately followed by a BEGIN ... EXCEPTION block that performs a query. Clean up correctly if a transaction fails after exporting its snapshot. This could occur if a replication slot was created then rolled back, and then another replication slot was created in the same session. Fix for "overflowed-subtransaction" wraparound tracking on standby servers that could lead to performance degradation. Ensure that prepared transactions are properly accounted for during promotion of a standby server. Ensure that the correct lock level is used when renaming a table. Avoid crash when dropping a role that owns objects being dropped concurrently. Disallow setting huge_pages to on when shared_memory_type is sysv Fix query type checking in the PL/pgSQL RETURN QUERY. Several fixes for pg_dump, including the ability to dump non-global default privileges correctly. Use the CLDR project's data to map Windows time zone names to IANA time zones. This update also contains tzdata release 2021e for DST law changes in Fiji, Jordan, Palestine, and Samoa, plus historical corrections for Barbados, Cook Islands, Guyana, Niue, Portugal, and Tonga. Also, the Pacific/Enderbury zone has been renamed to Pacific/Kanton. Also, the following zones have been merged into nearby, more-populous zones whose clocks have agreed with them since 1970: Africa/Accra, America/Atikokan, America/Blanc-Sablon, America/Creston, America/Curacao, America/Nassau, America/Port_of_Spain, Antarctica/DumontDUrville, and Antarctica/Syowa. In all these cases, the previous zone name remains as an alias. For the full list of changes available, please review the release notes. PostgreSQL 9.6 is EOL This is the final release of PostgreSQL 9.6. If you are running PostgreSQL 9.6 in a production environment, we suggest that you make plans to upgrade to a newer, supported version of PostgreSQL. Please see our versioning policy for more information. Updating All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shutdown PostgreSQL and update its binaries. Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details. For more details, please see the release notes. Links Download Release Notes Security Versioning Policy Follow @postgresql on Twitter [Less]

PgBouncer 1.16.1 has been released. This is a minor release with a security fix. See https://www.pgbouncer.org/2021/11/pgbouncer-1-16-1 for more information, the detailed changelog, and download links. PgBouncer is a lightweight connection pooler for PostgreSQL.

Pgpool Global Development Group is pleased to announce the availability of Pgpool-II 4.3 beta1. This is not intended to be used in production but is close to the release version. So users are encouraged to test it out. Pgpool-II is a tool to add ... [More] useful features to PostgreSQL, including: connection pooling load balancing automatic fail over and more. For more information, please see the website. V4.3 contains new features and enhancements, including: A new membership mechanism is introduced to Watchdog to allow to keep quorum/VIP when some of watchdog nodes are removed. Allow to choose the least replication delay standby node when selecting the load balance node. Allow to specify the node id to be promoted in pcp_promote_node. Allow to configure to not trigger failover when PostgreSQL is shutdown by admin or killed by pg_terminate_backend. Add new fields to pcp_proc_info, SHOW POOL_PROCESSES and SHOW POOL_POOLS command to display more useful information to admin. Allow pcp_node_info to list all backend nodes information. Add new fields showing actual PostgreSQL status to SHOW POOL NODES command and friends. Add a new parameter which represents the recovery source hostname to recovery_1st_stage_command and recovery_2nd_stage_command. Add support for log time stamp with milliseconds. Import PostgreSQL 14's SQL parser. Support include directive in pgppol.conf file. You can have separate sub-config file to be included in pgpool.conf. pgpool.conf sample files are unified into single sample file for easier configuration. All configuration parameters in pgpool.conf sample file are commented out to clarify which parameter is needed to be changed. You can download it from here. Please take a look at release notes. [Less]