Person of the week: https://postgresql.life/post/gunnar_bluth/
PostgreSQL Product Newspspg 4.0.0 a pager designed for PostgreSQL, released. https://github.com/okbob/pspg/releases/tag/4.0.0
DBConvert Studio 2.0, a database migration and synchronization suite that supports PostgreSQL, released. https://dbconvert.com/dbconvert-studio
PostgreSQL Jobs for Januaryhttps://archives.postgresql.org/pgsql-jobs/2021-01/
PostgreSQL in the NewsPlanet 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 david@fetter.org.
Applied PatchesThomas Munro pushed:
Provide pg_preadv() and pg_pwritev(). Provide synchronous vectored file I/O routines. These map to preadv() and pwritev(), with fallback implementations for systems that don't have them. Also provide a wrapper pg_pwritev_with_retry() that automatically retries on short writes. Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Reviewed-by: Andres Freund andres@anarazel.de Discussion: https://postgr.es/m/CA%2BhUKGJA%2Bu-220VONeoREBXJ9P3S94Y7J%2BkqCnTYmahvZJwM%3Dg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/13a021f3e8c99915b3cc0cb2021a948d9c71ff32
Use vectored I/O to fill new WAL segments. Instead of making many block-sized write() calls to fill a new WAL file with zeroes, make a smaller number of pwritev() calls (or various emulations). The actual number depends on the OS's IOV_MAX, which PG_IOV_MAX currently caps at 32. That means we'll write 256kB per call on typical systems. We may want to tune the number later with more experience. Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Reviewed-by: Andres Freund andres@anarazel.de Discussion: https://postgr.es/m/CA%2BhUKGJA%2Bu-220VONeoREBXJ9P3S94Y7J%2BkqCnTYmahvZJwM%3Dg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/ce6a71fa5300cf00adf32c9daee302c523609709
Fix function prototypes in dependency.h. Commit 257836a7 accidentally deleted a couple of redundant-but-conventional "extern" keywords on function prototypes. Put them back. Reported-by: Alvaro Herrera alvherre@alvh.no-ip.org https://git.postgresql.org/pg/commitdiff/f315205f3fafd6f6c7c479f480289fcf45700310
Don't use elog() in src/port/pwrite.c. Nothing broke because of this oversight yet, but it would fail to link if we tried to use pg_pwrite() in frontend code on a system that lacks pwrite(). Use an assertion instead. Also pgindent while here. Discussion: https://postgr.es/m/CA%2BhUKGL57RvoQsS35TVPnQoPYqbtBixsdRhynB8NpcUKpHTTtg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/df10ac625c1672edf839ff59cfcac9dcc097515c
Move our p{read,write}v replacements into their own files. macOS's ranlib issued a warning about an empty pread.o file with the previous arrangement, on systems new enough to require no replacement functions. Let's go back to using configure's AC_REPLACE_FUNCS system to build and include each .o in the library only if it's needed, which requires moving the *v() functions to their own files. Also move the _with_retry() wrapper to a more permanent home. Reported-by: Tom Lane tgl@sss.pgh.pa.us Discussion: https://postgr.es/m/1283127.1610554395%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0d56acfbaa799553c0c6ea350fd6e68d81025994
Minor header cleanup for the new iovec code. Remove redundant function declaration and improve header comment in pg_iovec.h. Move the new declaration in fd.h next to a group of more similar functions. https://git.postgresql.org/pg/commitdiff/fb29ab26b38f1647423bf6aa2991e5fdf9060f08
Tom Lane pushed:
In libpq, always append new error messages to conn->errorMessage. Previously, we had an undisciplined mish-mash of printfPQExpBuffer and appendPQExpBuffer calls to report errors within libpq. This commit establishes a uniform rule that appendPQExpBuffer[Str] should be used. conn->errorMessage is reset only at the start of an application request, and then accumulates messages till we're done. We can remove no less than three different ad-hoc mechanisms that were used to get the effect of concatenation of error messages within a sequence of operations. Although this makes things quite a bit cleaner conceptually, the main reason to do it is to make the world safer for the multiple-target-host feature that was added awhile back. Previously, there were many cases in which an error occurring during an individual host connection attempt would wipe out the record of what had happened during previous attempts. (The reporting is still inadequate, in that it can be hard to tell which host got the failure, but that seems like a matter for a separate commit.) Currently, lo_import and lo_export contain exceptions to the "never use printfPQExpBuffer" rule. If we changed them, we'd risk reporting an incidental lo_close failure before the actual read or write failure, which would be confusing, not least because lo_close happened after the main failure. We could improve this by inventing an internal version of lo_close that doesn't reset the errorMessage; but we'd also need a version of PQfn() that does that, and it didn't quite seem worth the trouble for now. Discussion: https://postgr.es/m/BN6PR05MB3492948E4FD76C156E747E8BC9160@BN6PR05MB3492.namprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/ffa2e4670123124b92f037d335a1e844c3782d3f
Allow pg_regress.c wrappers to postprocess test result files. Add an optional callback to regression_main() that, if provided, is invoked on each test output file before we try to compare it to the expected-result file. The main and isolation test programs don't need this (yet). In pg_regress_ecpg, add a filter that eliminates target-host details from "could not connect" error reports. This filter doesn't do anything as of this commit, but it will be needed by the next one. In the long run we might want to provide some more general, perhaps pattern-based, filtering mechanism for test output. For now, this will solve the immediate problem. Discussion: https://postgr.es/m/BN6PR05MB3492948E4FD76C156E747E8BC9160@BN6PR05MB3492.namprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/800d93f314b0f7c10193e48b259f87800cb85d84
Uniformly identify the target host in libpq connection failure reports. Prefix "could not connect to host-or-socket-path:" to all connection failure cases that occur after the socket() call, and remove the ad-hoc server identity data that was appended to a few of these messages. This should produce much more intelligible error reports in multiple-target-host situations, especially for error cases that are off the beaten track to any degree (because none of those provided any server identity info). As an example of the change, formerly a connection attempt with a bad port number such as "psql -p 12345 -h localhost,/tmp" might produce psql: error: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 12345? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 12345? could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.12345"? Now it looks like psql: error: could not connect to host "localhost" (::1), port 12345: Connection refused Is the server running on that host and accepting TCP/IP connections? could not connect to host "localhost" (127.0.0.1), port 12345: Connection refused Is the server running on that host and accepting TCP/IP connections? could not connect to socket "/tmp/.s.PGSQL.12345": No such file or directory Is the server running locally and accepting connections on that socket? This requires adjusting a couple of regression tests to allow for variation in the contents of a connection failure message. Discussion: https://postgr.es/m/BN6PR05MB3492948E4FD76C156E747E8BC9160@BN6PR05MB3492.namprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/52a10224e3cc1d706ba9800695f97cb163b747d5
Try next host after a "cannot connect now" failure. If a server returns ERRCODE_CANNOT_CONNECT_NOW, try the next host, if multiple host names have been provided. This allows dealing gracefully with standby servers that might not be in hot standby mode yet. In the wake of the preceding commit, it might be plausible to retry many more error cases than we do now, but I (tgl) am hesitant to move too aggressively on that --- it's not clear it'd be desirable for cases such as bad-password, for example. But this case seems safe enough. Hubert Zhang, reviewed by Takayuki Tsunakawa Discussion: https://postgr.es/m/BN6PR05MB3492948E4FD76C156E747E8BC9160@BN6PR05MB3492.namprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/c1d589571c497a952d7fbe40d9828655859d746f
Rethink SQLSTATE code for ERRCODE_IDLE_SESSION_TIMEOUT. Move it to class 57 (Operator Intervention), which seems like a better choice given that from the client's standpoint it behaves a heck of a lot like, e.g., ERRCODE_ADMIN_SHUTDOWN. In a green field I'd put ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT here as well. But that's been around for a few years, so it's probably too late to change its SQLSTATE code. Discussion: https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com https://git.postgresql.org/pg/commitdiff/4edf96846a02693e4416478b3302e5133d2e8e01
Make pg_dump's table of object-type priorities more maintainable. Wedging a new object type into this table has historically required manually renumbering a lot of existing entries. (Although it appears that some people got lazy and re-used the priority level of an existing object type, even if it wasn't particularly related.) We can let the compiler do the counting by inventing an enum type that lists the desired priority levels in order. Now, if you want to add or remove a priority level, that's a one-liner. This patch is not purely cosmetic, because I split apart the priorities of DO_COLLATION and DO_TRANSFORM, as well as those of DO_ACCESS_METHOD and DO_OPERATOR, which look to me to have been merged out of expediency rather than because it was a good idea. Shell types continue to be sorted interchangeably with full types, and opclasses interchangeably with opfamilies. https://git.postgresql.org/pg/commitdiff/d5ab79d815783fe60062cefc423b54e82fbb92ff
Dump ALTER TABLE ... ATTACH PARTITION as a separate ArchiveEntry. Previously, we emitted the ATTACH PARTITION command as part of the child table's ArchiveEntry. This was a poor choice since it complicates restoring the partition as a standalone table; you have to ignore the error from the ATTACH, which isn't even an option when restoring direct-to-database with pg_restore. (pg_restore will issue the whole ArchiveEntry as one PQexec, so that any error rolls back the table creation as well.) Hence, separate it out as its own ArchiveEntry, as indeed we already did for index ATTACH PARTITION commands. Justin Pryzby Discussion: https://postgr.es/m/20201023052940.GE9241@telsasoft.com https://git.postgresql.org/pg/commitdiff/9a4c0e36fbd671b5e7426a5a0670bdd7ba2714a0
Doc: fix description of privileges needed for ALTER PUBLICATION. Adding a table to a publication requires ownership of the table (in addition to ownership of the publication). This was mentioned nowhere. https://git.postgresql.org/pg/commitdiff/cc865c0f319fde22540625e02863f42e9853b3e4
pg_dump: label INDEX ATTACH ArchiveEntries with an owner. Although a partitioned index's attachment to its parent doesn't have separate ownership, the ArchiveEntry for it needs to be marked with an owner anyway, to ensure that the ALTER command is run by the appropriate role when restoring with --use-set-session-authorization. Without this, the ALTER will be run by the role that started the restore session, which will usually work but it's formally the wrong thing. Back-patch to v11 where this type of ArchiveEntry was added. In HEAD, add equivalent commentary to the just-added TABLE ATTACH case, which I'd made do the right thing already. Discussion: https://postgr.es/m/1094034.1610418498@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9eabfe300a22ad3d776dc293265e15379790bd9a
Doc: clarify behavior of back-half options in pg_dump. Options that change how the archive data is converted to SQL text are ignored when dumping to archive formats. The documentation previously said "not meaningful", which is not helpful. Discussion: https://postgr.es/m/161052021249.12228.9598689907884726185@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/06ed235adeb621a73cafd6ab35fa2405b3177329
Disallow a digit as the first character of a variable name in pgbench. The point of this restriction is to avoid trying to substitute variables into timestamp literal values, which may contain strings like '12:34'. There is a good deal more that should be done to reduce pgbench's tendency to substitute where it shouldn't. But this is sufficient to solve the case complained of by Jaime Soler, and it's simple enough to back-patch. Back-patch to v11; before commit 9d36a3866, pgbench had a slightly different definition of what a variable name is, and anyway it seems unwise to change long-stable branches for this. Fabien Coelho Discussion: https://postgr.es/m/alpine.DEB.2.22.394.2006291740420.805678@pseudo https://git.postgresql.org/pg/commitdiff/c21ea4d53e9404279273da800daa49b7b9a5e81e
Doc, more or less: uncomment tutorial example that was fixed long ago. Reverts a portion of commit 344190b7e. Apparently, back in the twentieth century we had some issues with multi-statement SQL functions, but they've worked fine for a long time. Daniel Westermann Discussion: https://postgr.es/m/GVAP278MB04242DCBF5E31F528D53FA18D2A90@GVAP278MB0424.CHEP278.PROD.OUTLOOK.COM https://git.postgresql.org/pg/commitdiff/dce62490818170b6479dfe08a28aae4bcdf7cc2d
Run reformat-dat-files to declutter the catalog data files. Things had gotten pretty messy here, apparently mostly but not entirely the fault of the multirange patch. No functional changes. https://git.postgresql.org/pg/commitdiff/8b411b8ff41566a1aa601d1f05aeebbebbdb4a54
Mark inet_server_addr() and inet_server_port() as parallel-restricted. These need to be PR because they access the MyProcPort data structure, which doesn't get copied to parallel workers. The very similar functions inet_client_addr() and inet_client_port() are already marked PR, but somebody missed these. Although this is a pre-existing bug, we can't readily fix it in the back branches since we can't force initdb. Given the small usage of these two functions, and the even smaller likelihood that they'd get pushed to a parallel worker anyway, it doesn't seem worth the trouble to suggest that DBAs should fix it manually. Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoAT4aHP0Uxq91qpD7NL009tnUYQe-b14R3MnSVOjtE71g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5a6f9bce8dabd371bdb4e3db5dda436f7f0a680f
pg_dump: label PUBLICATION TABLE ArchiveEntries with an owner. This is the same fix as commit 9eabfe300 applied to INDEX ATTACH entries, but for table-to-publication attachments. As in that case, even though the backend doesn't record "ownership" of the attachment, we still ought to label it in the dump archive with the role name that should run the ALTER PUBLICATION command. The existing behavior causes the ALTER to be done by the original role that started the restore; that will usually work fine, but there may be corner cases where it fails. The bulk of the patch is concerned with changing struct PublicationRelInfo to include a pointer to the associated PublicationInfo object, so that we can get the owner's name out of that when the time comes. While at it, I rewrote getPublicationTables() to do just one query of pg_publication_rel, not one per table. Back-patch to v10 where this code was introduced. Discussion: https://postgr.es/m/1165710.1610473242@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8e396a773b80c72e5d5a0ca9755dffe043c97a05
Improve our heuristic for selecting PG_SYSROOT on macOS. In cases where Xcode is newer than the underlying macOS version, asking xcodebuild for the SDK path will produce a pointer to the SDK shipped with Xcode, which may end up building code that does not work on the underlying macOS version. It appears that in such cases, xcodebuild's answer also fails to match the default behavior of Apple's compiler: assuming one has installed Xcode's "command line tools", there will be an SDK for the OS's own version in /Library/Developer/CommandLineTools, and the compiler will default to using that. This is all pretty poorly documented, but experimentation suggests that "xcrun --show-sdk-path" gives the sysroot path that the compiler is actually using, at least in some cases. Hence, try that first, but revert to xcodebuild if xcrun fails (in very old Xcode, it is missing or lacks the --show-sdk-path switch). Also, "xcrun --show-sdk-path" may give a path that is valid but lacks any OS version identifier. We don't really want that, since most of the motivation for wiring -isysroot into the build flags at all is to ensure that all parts of a PG installation are built against the same SDK, even when considering extensions built later and/or on a different machine. Insist on finding "N.N" in the directory name before accepting the result. (Adding "--sdk macosx" to the xcrun call seems to produce the same answer as xcodebuild, but usually more quickly because it's cached, so we also try that as a fallback.) The core reason why we don't want to use Xcode's default SDK in cases like this is that Apple's technology for introducing new syscalls does not play nice with Autoconf: for example, configure will think that preadv/pwritev exist when using a Big Sur SDK, even when building on an older macOS version where they don't exist. It'd be nice to have a better solution to that problem, but this patch doesn't attempt to fix that. Per report from Sergey Shinderuk. Back-patch to all supported versions. Discussion: https://postgr.es/m/ed3b8e5d-0da8-6ebd-fd1c-e0ac80a4b204@postgrespro.ru https://git.postgresql.org/pg/commitdiff/4823621db312a0597c40686c4c94d47428889fef
Add missing array-enlargement logic to test_regex.c. The stanza to report a "partial" match could overrun the initially allocated output array, so it needs its own copy of the array-resizing logic that's in the main loop. I overlooked the need for this in ca8217c10. Per report from Alexander Lakhin. Discussion: https://postgr.es/m/3206aace-50db-e02a-bbea-76d5cdaa2cb6@gmail.com https://git.postgresql.org/pg/commitdiff/0c7d3bb99f72d66ec6ac63aee4c5fe6d683eee86
Amit Kapila pushed:
Optimize DropRelFileNodeBuffers() for recovery. The recovery path of DropRelFileNodeBuffers() is optimized so that scanning of the whole buffer pool can be avoided when the number of blocks to be truncated in a relation is below a certain threshold. For such cases, we find the buffers by doing lookups in BufMapping table. This improves the performance by more than 100 times in many cases when several small tables (tested with 1000 relations) are truncated and where the server is configured with a large value of shared buffers (greater than equal to 100GB). This optimization helps cases (a) when vacuum or autovacuum truncated off any of the empty pages at the end of a relation, or (b) when the relation is truncated in the same transaction in which it was created. This commit introduces a new API smgrnblocks_cached which returns a cached value for the number of blocks in a relation fork. This helps us to determine the exact size of relation which is required to apply this optimization. The exact size is required to ensure that we don't leave any buffer for the relation being dropped as otherwise the background writer or checkpointer can lead to a PANIC error while flushing buffers corresponding to files that don't exist. Author: Kirk Jamison based on ideas by Amit Kapila Reviewed-by: Kyotaro Horiguchi, Takayuki Tsunakawa, and Amit Kapila Tested-By: Haiying Tang Discussion: https://postgr.es/m/OSBPR01MB3207DCA7EC725FDD661B3EDAEF660@OSBPR01MB3207.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/d6ad34f3410f101f9909c0918a49d6ce86fa216c
Fix relation descriptor leak. We missed closing the relation descriptor while sending changes via the root of partitioned relations during logical replication. Author: Amit Langote and Mark Zhao Reviewed-by: Amit Kapila and Ashutosh Bapat Backpatch-through: 13, where it was introduced Discussion: https://postgr.es/m/tencent_41FEA657C206F19AB4F406BE9252A0F69C06@qq.com Discussion: https://postgr.es/m/tencent_6E296D2F7D70AFC90D83353B69187C3AA507@qq.com https://git.postgresql.org/pg/commitdiff/044aa9e70e552d9adb4f6429a1b0e96dde946a92
Optimize DropRelFileNodesAllBuffers() for recovery. Similar to commit d6ad34f341, this patch optimizes DropRelFileNodesAllBuffers() by avoiding the complete buffer pool scan and instead find the buffers to be invalidated by doing lookups in the BufMapping table. This optimization helps operations where the relation files need to be removed like Truncate, Drop, Abort of Create Table, etc. Author: Kirk Jamison Reviewed-by: Kyotaro Horiguchi, Takayuki Tsunakawa, and Amit Kapila Tested-By: Haiying Tang Discussion: https://postgr.es/m/OSBPR01MB3207DCA7EC725FDD661B3EDAEF660@OSBPR01MB3207.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/bea449c635c0e68e21610593594c1e5d52842cdd
Fix memory leak in SnapBuildSerialize. The memory for the snapshot was leaked while serializing it to disk during logical decoding. This memory will be freed only once walsender stops streaming the changes. This can lead to a huge memory increase when master logs Standby Snapshot too frequently say when the user is trying to create many replication slots. Reported-by: funnyxj.fxj@alibaba-inc.com Diagnosed-by: funnyxj.fxj@alibaba-inc.com Author: Amit Kapila Backpatch-through: 9.5 Discussion: https://postgr.es/m/033ab54c-6393-42ee-8ec9-2b399b5d8cde.funnyxj.fxj@alibaba-inc.com https://git.postgresql.org/pg/commitdiff/ee1b38f65948cb09ecf3c39b58bd88aabc950e7c
Remove unnecessary pstrdup in fetch_table_list. The result of TextDatumGetCString is already palloc'ed so we don't need to allocate memory for it again. We decide not to backpatch it as there doesn't seem to be any case where it can create a meaningful leak. Author: Zhijie Hou Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/229fed2eb8c54c71a96ccb99e516eb12@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/c95765f47673b16ed36acbfe98e1242e3c3822a3
Álvaro Herrera pushed:
Fix thinko in comment. This comment has been wrong since its introduction in commit 2c03216d8311. Author: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/CAD21AoAzz6qipFJBbGEaHmyWxvvNDp8httbwLR9tUQWaTjUs2Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a3e51a36b77ab073df88860cc21b2a6094d699d4
Invent struct ReindexIndexInfo. This struct is used by ReindexRelationConcurrently to keep track of the relations to process. This saves having to obtain some data repeatedly, and has future uses as well. Reviewed-by: Dmitry Dolgov 9erthalion6@gmail.com Reviewed-by: Hamid Akhtar hamid.akhtar@gmail.com Reviewed-by: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/20201130195439.GA24598@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/c6c4b37395b22f7e649234453a0aafe74d61b7e6
Call out vacuum considerations in create index docs. Backpatch to pg12, which is as far as it goes without conflicts. Author: James Coleman jtc331@gmail.com Reviewed-by: "David G. Johnston" david.g.johnston@gmail.com Discussion: https://postgr.es/m/CAAaqYe9oEfbz7AxXq7OX+FFVi5w5p1e_Of8ON8ZnKO9QqBfmjg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/93c39f987e9c019cd28c450ece8a621b2d8ce28a
Prevent drop of tablespaces used by partitioned relations. When a tablespace is used in a partitioned relation (per commits ca4103025dfe in pg12 for tables and 33e6c34c3267 in pg11 for indexes), it is possible to drop the tablespace, potentially causing various problems. One such was reported in bug #16577, where a rewriting ALTER TABLE causes a server crash. Protect against this by using pg_shdepend to keep track of tablespaces when used for relations that don't keep physical files; we now abort a tablespace if we see that the tablespace is referenced from any partitioned relations. Backpatch this to 11, where this problem has been latent all along. We don't try to create pg_shdepend entries for existing partitioned indexes/tables, but any ones that are modified going forward will be protected. Note slight behavior change: when trying to drop a tablespace that contains both regular tables as well as partitioned ones, you'd previously get ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE and now you'll get ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST. Arguably, the latter is more correct. It is possible to add protecting pg_shdepend entries for existing tables/indexes, by doing ALTER TABLE ONLY some_partitioned_table SET TABLESPACE pg_default; ALTER TABLE ONLY some_partitioned_table SET TABLESPACE original_tablespace; for each partitioned table/index that is not in the database default tablespace. Because these partitioned objects do not have storage, no file needs to be actually moved, so it shouldn't take more time than what's required to acquire locks. This query can be used to search for such relations: SELECT ... FROM pg_class WHERE relkind IN ('p', 'I') AND reltablespace <> 0 Reported-by: Alexander Lakhin exclusion@gmail.com Discussion: https://postgr.es/m/16577-881633a9f9894fd5@postgresql.org Author: Álvaro Herrera alvherre@alvh.no-ip.org Reviewed-by: Michael Paquier michael@paquier.xyz https://git.postgresql.org/pg/commitdiff/ebfe2dbd6b624e2a428e14b7ee9322cc096f63f7
Avoid spurious wait in concurrent reindex. This is like commit c98763bf51bf, but for REINDEX CONCURRENTLY. To wit: this flags indicates that the current process is safe to ignore for the purposes of waiting for other snapshots, when doing CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY. This helps two processes doing either of those things not deadlock, and also avoids spurious waits. Author: Álvaro Herrera alvherre@alvh.no-ip.org Reviewed-by: Dmitry Dolgov 9erthalion6@gmail.com Reviewed-by: Hamid Akhtar hamid.akhtar@gmail.com Reviewed-by: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/20201130195439.GA24598@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/f9900df5f94936067e6fa24a9df609863eb08da2
Michaël Paquier pushed:
Fix routine name in comment of catcache.c. Author: Bharath Rupireddy Discussion: https://postgr.es/m/CALj2ACUDXLAkf_XxQO9tAUtnTNGi3Lmd8fANd+vBJbcHn1HoWA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fce7d0e6efbef304e81846c75eddf73099628d10
Rework refactoring of hex and encoding routines. This commit addresses some issues with c3826f83 that moved the hex decoding routine to src/common/: - The decoding function lacked overflow checks, so when used for security-related features it was an open door to out-of-bound writes if not carefully used that could remain undetected. Like the base64 routines already in src/common/ used by SCRAM, this routine is reworked to check for overflows by having the size of the destination buffer passed as argument, with overflows checked before doing any writes. - The encoding routine was missing. This is moved to src/common/ and it gains the same overflow checks as the decoding part. On failure, the hex routines of src/common/ issue an error as per the discussion done to make them usable by frontend tools, but not by shared libraries. Note that this is why ECPG is left out of this commit, and it still includes a duplicated logic doing hex encoding and decoding. While on it, this commit uses better variable names for the source and destination buffers in the existing escape and base64 routines in encode.c and it makes them more robust to overflow detection. The previous core code issued a FATAL after doing out-of-bound writes if going through the SQL functions, which would be enough to detect problems when working on changes that impacted this area of the code. Instead, an error is issued before doing an out-of-bound write. The hex routines were being directly called for bytea conversions and backup manifests without such sanity checks. The current calls happen to not have any problems, but careless uses of such APIs could easily lead to CVE-class bugs. Author: Bruce Momjian, Michael Paquier Reviewed-by: Sehrope Sarkuni Discussion: https://postgr.es/m/20201231003557.GB22199@momjian.us https://git.postgresql.org/pg/commitdiff/aef8948f38d9f3aa58bf8c2d4c6f62a7a456a9d1
Fix O(N^2) stat() calls when recycling WAL segments. The counter tracking the last segment number recycled was getting initialized when recycling one single segment, while it should be used across a full cycle of segments recycled to prevent useless checks related to entries already recycled. This performance issue has been introduced by b2a5545, and it was first implemented in 61b86142. No backpatch is done per the lack of field complaints. Reported-by: Andres Freund, Thomas Munro Author: Michael Paquier Reviewed-By: Andres Freund Discussion: https://postgr.es/m/20170621211016.eln6cxxp3jrv7m4m@alap3.anarazel.de Discussion: https://postgr.es/m/CA+hUKG+DRiF9z1_MU4fWq+RfJMxP7zjoptfcmuCFPeO4JM2iVg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5ae1572993ae8bf1f6c33a933915c07cc9bc0add
Remove PG_SHA*_DIGEST_STRING_LENGTH from sha2.h. The last reference to those variables has been removed in aef8948, so this cleans up a bit the code. Discussion: https://postgr.es/m/X//ggAqmTtt+3t7X@paquier.xyz https://git.postgresql.org/pg/commitdiff/ccf4e277a4de120a2f08db7e45399d87e1176bda
Heikki Linnakangas pushed:
Add functions to 'pageinspect' to inspect GiST indexes. Author: Andrey Borodin and me Discussion: https://www.postgresql.org/message-id/3E4F9093-A1B5-4DF8-A292-0B48692E3954%40yandex-team.ru https://git.postgresql.org/pg/commitdiff/756ab29124d7850d4392e2227b67b69b61576cd6
Fix portability issues in the new gist pageinspect test. 1. The raw bytea representation of the point-type keys used in the test depends on endianess. Remove the raw key_data column from the test. 2. The items stored on non-leftmost gist page depends on how many items git on the other pages. This showed up as a failure on 32-bit i386 systems. To fix, only test the gist_page_items() function on the leftmost leaf page. Per Andrey Borodin and the buildfarm. Discussion: https://www.postgresql.org/message-id/9FCEC1DC-86FB-4A57-88EF-DD13663B36AF%40yandex-team.ru https://git.postgresql.org/pg/commitdiff/6ecaaf810b8b9e3132f00549e630400f38a7ca37
Fix test failure with wal_level=minimal. The newly-added gist pageinspect test prints the LSNs of GiST pages, expecting them all to be 1 (GistBuildLSN). But with wal_level=minimal, they got updated by the whole-relation WAL-logging at commit. Fix by wrapping the problematic tests in the same transaction with the CREATE INDEX. Per buildfarm failure on thorntail. Discussion: https://www.postgresql.org/message-id/3B4F97E5-40FB-4142-8CAA-B301CDFBF982%40iki.fi https://git.postgresql.org/pg/commitdiff/5abca4b1cd7193d6a5c8235c97eb240312190bcb
Magnus Hagander pushed:
Remove incorrect markup. Seems 737d69ffc3c made a copy/paste or automation error resulting in two extra right-parenthesis. Reported-By: Michael Vastola Backpatch-through: 13 Discussion: https://postgr.es/m/161051035421.12224.1741822783166533529@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/e6eeb8d799c703eb829612ddcb4fd5ba82d3e880
Add pg_stat_database counters for sessions and session time. This add counters for number of sessions, the different kind of session termination types, and timers for how much time is spent in active vs idle in a database to pg_stat_database. Internally this also renames the parameter "force" to disconnect. This was the only use-case for the parameter before, so repurposing it to this mroe narrow usecase makes things cleaner than inventing something new. Author: Laurenz Albe Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at https://git.postgresql.org/pg/commitdiff/960869da0803427d14335bba24393f414b476e2c
Add --no-instructions parameter to initdb. Specifying this parameter removes the informational messages about how to start the server. This is intended for use by wrappers in different packaging systems, where those instructions would most likely be wrong anyway, but the other output from initdb would still be useful (and thus just redirecting everything to /dev/null would be bad). Author: Magnus Hagander Reviewed-By: Peter Eisentraut Discusion: https://postgr.es/m/CABUevEzo4t5bmTXF0_B9WzmuWpVbMpkNZZiGvzV8NZa-=fPqeQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e09155bd62f0ac5817cb3d736eb35adf4200549e
Add documentation chapter about checksums. Data checksums did not have a longer discussion in the docs, this adds a short section with an overview. Extracted from the larger patch for on-line enabling of checksums, which has many more authors and reviewers. Author: Daniel Gustafsson Reviewed-By: Magnus Hagander, Michael Banck (and others through the big patch) Discussion: https://postgr.es/m/5ff49fa4.1c69fb81.658f3.04ac@mx.google.com https://git.postgresql.org/pg/commitdiff/cf621d9d84db1e6edaff8ffa26bad93fdce5f830
Fujii Masao pushed:
Log long wait time on recovery conflict when it's resolved. This is a follow-up of the work done in commit 0650ff2303. This commit extends log_recovery_conflict_waits so that a log message is produced also when recovery conflict has already been resolved after deadlock_timeout passes, i.e., when the startup process finishes waiting for recovery conflict after deadlock_timeout. This is useful in investigating how long recovery conflicts prevented the recovery from applying WAL. Author: Fujii Masao Reviewed-by: Kyotaro Horiguchi, Bertrand Drouvot Discussion: https://postgr.es/m/9a60178c-a853-1440-2cdc-c3af916cff59@amazon.com https://git.postgresql.org/pg/commitdiff/39b03690b529935a3c33024ee68f08e2d347cf4f
Ensure that a standby is able to follow a primary on a newer timeline. Commit 709d003fbd refactored WAL-reading code, but accidentally caused WalSndSegmentOpen() to fail to follow a timeline switch while reading from a historic timeline. This issue caused a standby to fail to follow a primary on a newer timeline when WAL archiving is enabled. If there is a timeline switch within the segment, WalSndSegmentOpen() should read from the WAL segment belonging to the new timeline. But previously since it failed to follow a timeline switch, it tried to read the WAL segment with old timeline. When WAL archiving is enabled, that WAL segment with old timeline doesn't exist because it's renamed to .partial. This leads a primary to have tried to read non-existent WAL segment, and which caused replication to faill with the error "ERROR: requested WAL segment ... has already been removed". This commit fixes WalSndSegmentOpen() so that it's able to follow a timeline switch, to ensure that a standby is able to follow a primary on a newer timeline even when WAL archiving is enabled. This commit also adds the regression test to check whether a standby is able to follow a primary on a newer timeline when WAL archiving is enabled. Back-patch to v13 where the bug was introduced. Reported-by: Kyotaro Horiguchi Author: Kyotaro Horiguchi, tweaked by Fujii Masao Reviewed-by: Alvaro Herrera, Fujii Masao Discussion: https://postgr.es/m/20201209.174314.282492377848029776.horikyota.ntt@gmail.com https://git.postgresql.org/pg/commitdiff/fef5b47f6bfc9bfec619bb2e6e66b027e7ff21a3
Improve tab-completion for CLOSE, DECLARE, FETCH and MOVE. This commit makes CLOSE, FETCH and MOVE commands tab-complete the list of cursors. Also this commit makes DECLARE command tab-complete the options. Author: Shinya Kato, Sawada Masahiko, tweaked by Fujii Masao Reviewed-by: Shinya Kato, Sawada Masahiko, Fujii Masao Discussion: https://postgr.es/m/b0e4c5c53ef84c5395524f5056fc71f0@MP-MSGSS-MBX001.msg.nttdata.co.jp https://git.postgresql.org/pg/commitdiff/3f238b882c276a59f5d98224850e5aee2a3fec8c
Stabilize timeline switch regression test. Commit fef5b47f6b added the regression test to check whether a standby is able to follow a primary on a newer timeline when WAL archiving is enabled. But the buildfarm member florican reported that this test failed because the requested WAL segment was removed and replication failed. This is a timing issue. Since neither replication slot is used nor wal_keep_size is set in the test, checkpoint could remove the WAL segment that's still necessary for replication. This commit stabilizes the test by setting wal_keep_size. Back-patch to v13 where the regression test that this commit stabilizes was added. Author: Fujii Masao Discussion: https://postgr.es/m/X//PsenxcC50jDzX@paquier.xyz https://git.postgresql.org/pg/commitdiff/424d7a9b277c0da5ec638bf6344cda899a2e544a
postgres_fdw: Save foreign server OID in connection cache entry. The foreign server OID stored in the connection cache entry is used as a lookup key to directly get the server name. Previously since the connection cache entry did not have the server OID, postgres_fdw had to get the server OID at first from user mapping before getting the server name. So if the corresponding user mapping was dropped, postgres_fdw could raise the error "cache lookup failed for user mapping" while looking up user mapping and fail to get the server name even though the server had not been dropped yet. Author: Bharath Rupireddy Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CALj2ACVRZPUB7ZwqLn-6DY8C_UmPs6084gSpHA92YBv++1AJXA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5e5f4fcd89c082bba0239e8db1552834b4905c34
Fix calculation of how much shared memory is required to store a TOC. Commit ac883ac453 refactored shm_toc_estimate() but changed its calculation of shared memory size for TOC incorrectly. Previously this could cause too large memory to be allocated. Back-patch to v11 where the bug was introduced. Author: Takayuki Tsunakawa Discussion: https://postgr.es/m/TYAPR01MB2990BFB73170E2C4921E2C4DFEA80@TYAPR01MB2990.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/2ad78a87f018260d4474eee63187e1cc73c9b976
Peter Geoghegan pushed:
Pass down "logically unchanged index" hint. Add an executor aminsert() hint mechanism that informs index AMs that the incoming index tuple (the tuple that accompanies the hint) is not being inserted by execution of an SQL statement that logically modifies any of the index's key columns. The hint is received by indexes when an UPDATE takes place that does not apply an optimization like heapam's HOT (though only for indexes where all key columns are logically unchanged). Any index tuple that receives the hint on insert is expected to be a duplicate of at least one existing older version that is needed for the same logical row. Related versions will typically be stored on the same index page, at least within index AMs that apply the hint. Recognizing the difference between MVCC version churn duplicates and true logical row duplicates at the index AM level can help with cleanup of garbage index tuples. Cleanup can intelligently target tuples that are likely to be garbage, without wasting too many cycles on less promising tuples/pages (index pages with little or no version churn). This is infrastructure for an upcoming commit that will teach nbtree to perform bottom-up index deletion. No index AM actually applies the hint just yet. Author: Peter Geoghegan pg@bowt.ie Reviewed-By: Victor Yegorov vyegorov@gmail.com Discussion: https://postgr.es/m/CAH2-Wz=CEKFa74EScx_hFVshCOn6AA5T-ajFASTdzipdkLTNQQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9dc718bdf2b1a574481a45624d42b674332e2903
Enhance nbtree index tuple deletion. Teach nbtree and heapam to cooperate in order to eagerly remove duplicate tuples representing dead MVCC versions. This is "bottom-up deletion". Each bottom-up deletion pass is triggered lazily in response to a flood of versions on an nbtree leaf page. This usually involves a "logically unchanged index" hint (these are produced by the executor mechanism added by commit 9dc718bd). The immediate goal of bottom-up index deletion is to avoid "unnecessary" page splits caused entirely by version duplicates. It naturally has an even more useful effect, though: it acts as a backstop against accumulating an excessive number of index tuple versions for any given logical row. Bottom-up index deletion complements what we might now call "top-down index deletion": index vacuuming performed by VACUUM. Bottom-up index deletion responds to the immediate local needs of queries, while leaving it up to autovacuum to perform infrequent clean sweeps of the index. The overall effect is to avoid certain pathological performance issues related to "version churn" from UPDATEs. The previous tableam interface used by index AMs to perform tuple deletion (the table_compute_xid_horizon_for_tuples() function) has been replaced with a new interface that supports certain new requirements. Many (perhaps all) of the capabilities added to nbtree by this commit could also be extended to other index AMs. That is left as work for a later commit. Extend deletion of LP_DEAD-marked index tuples in nbtree by adding logic to consider extra index tuples (that are not LP_DEAD-marked) for deletion in passing. This increases the number of index tuples deleted significantly in many cases. The LP_DEAD deletion process (which is now called "simple deletion" to clearly distinguish it from bottom-up deletion) won't usually need to visit any extra table blocks to check these extra tuples. We have to visit the same table blocks anyway to generate a latestRemovedXid value (at least in the common case where the index deletion operation's WAL record needs such a value). Testing has shown that the "extra tuples" simple deletion enhancement increases the number of index tuples deleted with almost any workload that has LP_DEAD bits set in leaf pages. That is, it almost never fails to delete at least a few extra index tuples. It helps most of all in cases that happen to naturally have a lot of delete-safe tuples. It's not uncommon for an individual deletion operation to end up deleting an order of magnitude more index tuples compared to the old naive approach (e.g., custom instrumentation of the patch shows that this happens fairly often when the regression tests are run). Add a further enhancement that augments simple deletion and bottom-up deletion in indexes that make use of deduplication: Teach nbtree's bt_delitems_delete() function to support granular TID deletion in posting list tuples. It is now possible to delete individual TIDs from posting list tuples provided the TIDs have a tableam block number of a table block that gets visited as part of the deletion process (visiting the table block can be triggered directly or indirectly). Setting the LP_DEAD bit of a posting list tuple is still an all-or-nothing thing, but that matters much less now that deletion only needs to start out with the right _general idea about which index tuples are deletable. Bump XLOG_PAGE_MAGIC because xl_btree_delete changed. No bump in BTREE_VERSION, since there are no changes to the on-disk representation of nbtree indexes. Indexes built on PostgreSQL 12 or PostgreSQL 13 will automatically benefit from bottom-up index deletion (i.e. no reindexing required) following a pg_upgrade. The enhancement to simple deletion is available with all B-Tree indexes following a pg_upgrade, no matter what PostgreSQL version the user upgrades from. Author: Peter Geoghegan pg@bowt.ie Reviewed-By: Heikki Linnakangas hlinnaka@iki.fi Reviewed-By: Victor Yegorov vyegorov@gmail.com Discussion: https://postgr.es/m/CAH2-Wzm+maE3apHB8NOtmM=p-DO65j2V5GzAWCOEEuy3JZgb2g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d168b666823b6e0bcf60ed19ce24fb5fb91b8ccf
Tomáš Vondra pushed:
Disallow CREATE STATISTICS on system catalogs. Add a check that CREATE STATISTICS does not add extended statistics on system catalogs, similarly to indexes etc. It can be overriden using the allow_system_table_mods GUC. This bug exists since 7b504eb282c, adding the extended statistics, so backpatch all the way back to PostgreSQL 10. Author: Tomas Vondra Reported-by: Dean Rasheed Backpatch-through: 10 Discussion: https://postgr.es/m/CAEZATCXAPrrOKwEsyZKQ4uzzJQWBCt6QAvOcgqRGdWwT1zb%2BrQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/c9a0dc34865f2f82d8e186f534d9b605afaa4d11
psql \dX: list extended statistics objects. The new command lists extended statistics objects, possibly with their sizes. All past releases with extended statistics are supported. Author: Tatsuro Yamada Reviewed-by: Julien Rouhaud, Alvaro Herrera, Tomas Vondra Discussion: https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1 https://git.postgresql.org/pg/commitdiff/891a1d0bca262ca78564e0fea1eaa5ae544ea5ee
Revert "psql \dX: list extended statistics objects". Reverts 891a1d0bca, because the new psql command \dX only worked for users users who can read pg_statistic_ext_data catalog, and most regular users lack that privilege (the catalog may contain sensitive user data). Reported-by: Noriyoshi Shinoda Discussion: https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1 https://git.postgresql.org/pg/commitdiff/1db0d173a2201119f297ea35edfb41579893dd8c
Noah Misch pushed:
Fix pg_dump for GRANT OPTION among initial privileges. The context is an object that no longer bears some aclitem that it bore initially. (A user issued REVOKE or GRANT statements upon the object.) pg_dump is forming SQL to reproduce the object ACL. Since initdb creates no ACL bearing GRANT OPTION, reaching this bug requires an extension where the creation script establishes such an ACL. No PGXN extension does that. If an installation did reach the bug, pg_dump would have omitted a semicolon, causing a REVOKE and the next SQL statement to fail. Separately, since the affected code exists to eliminate an entire aclitem, it wants plain REVOKE, not REVOKE GRANT OPTION FOR. Back-patch to 9.6, where commit 23f34fa4ba358671adab16773e79c17c92cbc870 first appeared. Discussion: https://postgr.es/m/20210109102423.GA160022@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/f713ff7c646e5912e08089de74dacdfaaac3d03b
Prevent excess SimpleLruTruncate() deletion. Every core SLRU wraps around. With the exception of pg_notify, the wrap point can fall in the middle of a page. Account for this in the PagePrecedes callback specification and in SimpleLruTruncate()'s use of said callback. Update each callback implementation to fit the new specification. This changes SerialPagePrecedesLogically() from the style of asyncQueuePagePrecedes() to the style of CLOGPagePrecedes(). (Whereas pg_clog and pg_serial share a key space, pg_serial is nothing like pg_notify.) The bug fixed here has the same symptoms and user followup steps as 592a589a04bd456410b853d86bd05faa9432cbbb. Back-patch to 9.5 (all supported versions). Reviewed by Andrey Borodin and (in earlier versions) by Tom Lane. Discussion: https://postgr.es/m/20190202083822.GC32531@gust.leadboat.com https://git.postgresql.org/pg/commitdiff/6db992833c04c0322f7f34a486adece01651f929
Jeff Davis pushed:
Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins in a class of places where it is safe to do so.
Tom Lane sent in a patch intended to fix a bug that manifested As multiple hosts in connection string failed to failover in non-hot standby mode by fixing some of the retry and error logic for connecting.
David Fetter sent in another revision of a patch to surface popcount to SQL.
Andrey V. Lepikhov sent in another revision of a patch to add a bulk insert interface to the FDW API and use same in the PostgreSQL FDW. This should speed up bulk loads to tables with foreign partitions.
Masahiko Sawada and Bharath Rupireddy traded patches to avoid catalogue accesses in conversion_error_callback.
Konstantin Knizhnik and Tomáš Vondra traded patches to implement compression for libpq.
Ian Barwick and Greg Sabino Mullane traded patches to help psql tab-complete functions by including the data types of their arguments.
Mark Dilger sent in another revision of a patch to add contrib module pg_amcheck, a command line interface for running amcheck's verifications against tables and indexes.
Bharath Rupireddy sent in two more revisions of a patch to make it possible to use parallel inserts in CTAS.
Anastasia Lubennikova sent in two more revisions of a patch to set PD_ALL_VISIBLE and visibility map bits in COPY FREEZE.
Masahiko Sawada sent in a patch to implement buffer encryption to make sure the kms patch would be workable with other components using an encryption key managed by kmgr.
Simon Riggs sent in another revision of a patch to implement system-versioned temporal tables.
Ian Barwick sent in a patch to fix has_column_privilege() with attnums and non-existent columns by confirming the existence of a column even if the user has the table-level privilege, otherwise the function will happily report the user has privilege on a dropped or non-existent column if an invalid attnum is provided.
Yugo Nagata sent in another revision of a patch to implement incremental view maintenance.
Atsushi Torikoshi sent in another revision of a patch to add the plan type (generic or custom) to pg_stat_statements.
Peter Smith sent in two more revisions of a patch to make it possible to use background workers for tablesync.
Kyotaro HORIGUCHI sent in two more revisions of a patch to make it possible to change the persistence (LOGGED/UNLOGGED) of a table without incurring a heap rewrite.
Atsushi Torikoshi sent in another revision of a patch to make it possible to collect memory contexts of the specified process via a new function, pg_get_target_backend_memory_contexts().
John Naylor sent in a patch to remove references to the now-removed replication_timeout GUC.
Hou Zhijie sent in two more revisions of a patch to add a Nullif case for eval_const_expressions_mutator.
Justin Pryzby sent in another revision of a patch to pg_upgrade to add a test to exercise binary compatibility.
Álvaro Herrera sent in another revision of a patch to set PROC_IN_SAFE_IC during REINDEX CONCURRENTLY.
Tomáš Vondra sent in four more revisions of a patch to add bulk insert for foreign tables.
Li Japin and Bharath Rupireddy traded patches to fix ALTER PUBLICATION...DROP TABLE behaviour by arranging it so that when an entry is invalidated in rel_sync_cache_publication_cb(), mark the pubactions to false and let get_rel_sync_entry() recalculate the pubactions.
Takamichi Osumi sent in three more revisions of a patch to add a new wal_level to disable WAL logging which is designed to make bulk loads faster with the trade-off of leaving an unrecoverable cluster if it fails midway.
Bruce Momjian sent in three more revisions of a patch to implement key management.
DRU sent in three more revisions of a patch to add documentation about data page checksums, and support checksum enable/disable in a running cluster.
Heikki Linnakangas and Andrey Borodin traded patches to add functions to 'pageinspect' to inspect GiST indexes.
Dilip Kumar sent in another revision of a patch to support custom compression methods for tables.
Yuzuko Hosoya sent in a patch to make it possible to Release SPI plans for referential integrity with DISCARD ALL, which will among other things reduce the amount of memory used when creating or using foreign keys on tables with many partitions.
Stephen Frost sent in a patch to introduce an obsolete appendix to link old terms to new docs.
Stephen Frost sent in another revision of a patch to use pre-fetching for ANALYZE and bring the details logged for autoanalyze into line with those for autovacuum.
Michaël Paquier and Aleksey Kondratov traded patches to refactor the utility statement options.
Peter Eisentraut sent in another revision of a patch to pageinspect that change es the block number arguments to bigint to avoid possible overflows.
Tomáš Vondra sent in three more revisions of a patch to implement BRIN multi-range indexes.
Heikki Linnakangas sent in two more revisions of a patch to move a few ResourceOwnerEnlarge() calls for safety and clarity, and make resowners more easily extensible by using a single array and hash, rather than one for each type of object.
Kyotaro HORIGUCHI sent in a patch to fix some misuses of RelationNeedsWAL.
Dilip Kumar sent in another revision of a patch to ensure that pg_is_wal_replay_paused waits for recovery to pause.
Kyotaro HORIGUCHI sent in another revision of a patch to move the stats collector's temporary storage from files to shared memory.
Kyotaro HORIGUCHI sent in another revision of a patch to protect syscache from bloating with negative cache entries by adding a CatCache expiration feature.
Pavel Stěhule sent in another revision of a patch to implement schema variables.
Li Japin sent in a patch to fix a typo in a comment on WalSndPrepareWrite.
Simon Riggs sent in a patch to make it possible to change an index's uniqueness without validating it, and a way to do that validation separately.
Takayuki Tsunakawa sent in a patch to fix the size calculation for shmem TOC by changing a couple of incorrect += assignments to = .
Peter Geoghegan sent in a patch to lower vacuum_cost_page_miss's default to 3.
Ian Barwick sent in another revision of a patch to add lock acquisition wait start time to the pg_lock_status function.
Andy Fan sent in a patch to make cost_sort more accurate.
Masahiko Sawada sent in another revision of a patch to make it possible to do transactions involving multiple postgres foreign servers.
Fujii Masao and Bharath Rupireddy traded patches to add a postgres_fdw function to discard cached connections, along with both a postgres_fdw-specific and a system-wide GUC, keep_connections.
Hou Zhijie sent in a patch to remove a stray apostrophe from a comment in reorderbuffer.c.
Álvaro Herrera sent in a patch to have VACUUM ignore processes doing CIC and RC when computing the Xid horizon of tuples to remove.
Álvaro Herrera sent in a patch to increase the size of pg_commit_ts buffers.
David Zhang sent in a patch to update the tablespace documentation to keep it consistent with the new table access method option for pgbench.
Iwata Aya sent in another revision of a patch to enable tracing for libpq.
Tomáš Vondra sent in two more revisions of a patch to cover expressions with extended statistics.
Tom Lane sent in a patch to fix a wrong calculation in pull_varnos().
Thomas Munro sent in another revision of a patch to make it possible to get pgbench to delay queries till connections are established.
BRATISLAVA, Slovakia - Jan. 14, 2021 -- Slotix s.r.o. today announced the release of a new major version of DBConvert Studio 2.0
DBConvert Studio is the most suitable software solution for database administrators who need complex migrations and synchronization between PostgreSQL and other most popular DBMS.
DBConvert Studio supports the most popular on-premises databases and Database Cloud platforms. It becomes a brilliant all-in-one solution, combining robust database migration engines with an updated, user-friendly interface.
Find full details about the application at https://dbconvert.com/dbconvert-studio
"We are very pleased to provide our customers with a new version of our flagship product DBConvert Studio. Since version 2.0, it has fully 64-bit database migration engines that handle large database tables. Older 32-bit kernels limited RAM allocation for tables to 2 GB. Besides synchronization engines for PostgreSQL, MySQL, Microsoft SQL Server, SQLite, Firebird, and Oracle improved. Synchronization speed increased up to 30%." said Aleksander Lazarev, Lead developer of DBConvert Studio.
The user interface of DBConvert Studio has been improved to make it clear and convenient. It guides the user through several steps, from configuring database connections through flexible configuration steps to completing database migration or synchronization. A saved job can be scheduled to run multiple times, so there is no need to configure it each time.
Slotix s.r.o. is the company providing database migration solutions since 2000.
DBConvert Studio supports more than ten of the most popular on-premises databases, including PostgreSQL, SQL Server, MySQL, Oracle, Interbase, IBM DB2, and more.
For large amounts of storage, it might be wise to consider migrating your databases to one of the following Cloud platforms using DBConvert Studio: Amazon RDS / Aurora, Microsoft Azure SQL, Google Cloud SQL, and Heroku Postgres.
pg_back 1.10, a backup script for PostgreSQL, released. https://github.com/orgrim/pg_back
pgagroal 1.1.0, a high-performance protocol-native connection pool for PostgreSQL, released. https://agroal.github.io/pgagroal/release/announcement/2021/01/05/pgagroal-1.1.0.html
Veil2 0.9.2 beta, a database security add-on for Postgres that provides a framework for implementing Virtual Private Databases with row level security, released. https://marcmunro.github.io/veil2/html/index.html
PostgreSQL Jobs for Januaryhttps://archives.postgresql.org/pgsql-jobs/2021-01/
PostgreSQL in the NewsPlanet 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 david@fetter.org.
Applied PatchesAmit Kapila pushed:
Allow decoding at prepare time in ReorderBuffer. This patch allows PREPARE-time decoding of two-phase transactions (if the output plugin supports this capability), in which case the transactions are replayed at PREPARE and then committed later when COMMIT PREPARED arrives. Now that we decode the changes before the commit, the concurrent aborts may cause failures when the output plugin consults catalogs (both system and user-defined). We detect such failures with a special sqlerrcode ERRCODE_TRANSACTION_ROLLBACK introduced by commit 7259736a6e and stop decoding the remaining changes. Then we rollback the changes when rollback prepared is encountered. Author: Ajin Cherian and Amit Kapila based on previous work by Nikhil Sontakke and Stas Kelvich Reviewed-by: Amit Kapila, Peter Smith, Sawada Masahiko, Arseny Sher, and Dilip Kumar Tested-by: Takamichi Osumi Discussion: https://postgr.es/m/02DA5F5E-CECE-4D9C-8B4B-418077E2C010@postgrespro.ru https://postgr.es/m/CAMGcDxeqEpWj3fTXwqhSwBdXd2RS9jzwWscO-XbeCfso6ts3+Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a271a1b50e9bec07e2ef3a05e38e7285113e4ce6
Fix typo in reorderbuffer.c. Author: Zhijie Hou Reviewed-by: Sawada Masahiko Discussion: https://postgr.es/m/ba88bb58aaf14284abca16aec04bf279@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/9da2224ea2bb9801afc29bff6a325bf796868bdc
Fix typo in origin.c. Author: Peter Smith Discussion: https://postgr.es/m/CAHut+PsReyuvww_Fn1NN_Vsv0wBP1bnzuhzRFr_2=y1nNZrG7w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/cd357c76296e987298e4005b8053adda9e3fb417
Test decoding of two-phase transactions during the build of a consistent snapshot. Commit a271a1b50e added the capability to allow decoding at prepare time. This adds an isolation testcase to test that decoding happens at commit time when the consistent snapshot state is reached after prepare but before commit prepared. Author: Ajin Cherian Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/02DA5F5E-CECE-4D9C-8B4B-418077E2C010@postgrespro.ru https://postgr.es/m/CAMGcDxeqEpWj3fTXwqhSwBdXd2RS9jzwWscO-XbeCfso6ts3+Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5a3574d7b33480de51692962e6fd25d00c05718a
Fix typos in decode.c and logical.c. Per report by Ajin Cherian in email: https://postgr.es/m/CAFPTHDYnRKDvzgDxoMn_CKqXA-D0MtrbyJvfvjBsO4G=UHDXkg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e02e840ff7787d4798fca9f6a5b8b3657f45cf0c
Fix the test for decoding of two-phase transactions. Commit 5a3574d7b3 added the test for decoding of two-phase transactions during the build of a consistent snapshot. The test forgot to skip empty xacts which can lead to decoding of extra empty transactions due to background activity by autovacuum. Per report by buildfarm. Reported-by: Tom Lane Discussion: https://postgr.es/m/363512.1610171267@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e33d004900f76c35759293fdedd4861b198fbf5b
Michaël Paquier pushed:
Simplify some comments in xml.c. Author: Justin Pryzby Discussion: https://postgr.es/m/X/Ff7jfnvJUab013@paquier.xyz https://git.postgresql.org/pg/commitdiff/b49154b3b7a45523ce4081fdae8d65049342fcec
Promote --data-checksums to the common set of options in initdb --help. This was previously part of the section dedicated to less common options, but it is an option commonly used these days. Author: Michael Banck Reviewed-by: Stephen Frost, Michael Paquier Discussion: https://postgr.es/m/d7938aca4d4ea8e8c72c33bd75efe9f8218fe390.camel@credativ.de https://git.postgresql.org/pg/commitdiff/bc08f7971c03550ede43af43e73d4a47a7935c69
Fix allocation logic of cryptohash context data with OpenSSL. The allocation of the cryptohash context data when building with OpenSSL was happening in the memory context of the caller of pg_cryptohash_create(), which could lead to issues with resowner cleanup if cascading resources are cleaned up on an error. Like other facilities using resowners, move the base allocation to TopMemoryContext to ensure a correct cleanup on failure. The resulting code gets simpler with this commit as the context data is now hold by a unique opaque pointer, so as there is only one single allocation done in TopMemoryContext. After discussion, also change the cryptohash subroutines to return an error if the caller provides NULL for the context data to ease error detection on OOM. Author: Heikki Linnakangas Discussion: https://postgr.es/m/X9xbuEoiU3dlImfa@paquier.xyz https://git.postgresql.org/pg/commitdiff/55fe26a4b580b17d721c5accb842cc6a08295273
Fix and simplify some code related to cryptohashes. This commit addresses two issues: - In pgcrypto, MD5 computation called pg_cryptohash_{init,update,final} without checking for the result status. - Simplify pg_checksum_raw_context to use only one variable for all the SHA2 options available in checksum manifests. Reported-by: Heikki Linnakangas Discussion: https://postgr.es/m/f62f26bb-47a5-8411-46e5-4350823e06a5@iki.fi https://git.postgresql.org/pg/commitdiff/15b824da97afb45f47e51b6b5b7e5eca09e5d03d
Tom Lane pushed:
Add the ability for the core grammar to have more than one parse target. This patch essentially allows gram.y to implement a family of related syntax trees, rather than necessarily always parsing a list of SQL statements. raw_parser() gains a new argument, enum RawParseMode, to say what to do. As proof of concept, add a mode that just parses a TypeName without any other decoration, and use that to greatly simplify typeStringToTypeName(). In addition, invent a new SPI entry point SPI_prepare_extended() to allow SPI users (particularly plpgsql) to get at this new functionality. In hopes of making this the last variant of SPI_prepare(), set up its additional arguments as a struct rather than direct arguments, and promise that future additions to the struct can default to zero. SPI_prepare_cursor() and SPI_prepare_params() can perhaps go away at some point. Discussion: https://postgr.es/m/4165684.1607707277@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/844fe9f159a948377907a63d0ef3fb16dc51ce50
Re-implement pl/pgsql's expression and assignment parsing. Invent new RawParseModes that allow the core grammar to handle pl/pgsql expressions and assignments directly, and thereby get rid of a lot of hackery in pl/pgsql's parser. This moves a good deal of knowledge about pl/pgsql into the core code: notably, we have to invent a CoercionContext that matches pl/pgsql's (rather dubious) historical behavior for assignment coercions. That's getting away from the original idea of pl/pgsql as an arm's-length extension of the core, but really we crossed that bridge a long time ago. The main advantage of doing this is that we can now use the core parser to generate FieldStore and/or SubscriptingRef nodes to handle assignments to pl/pgsql variables that are records or arrays. That fixes a number of cases that had never been implemented in pl/pgsql assignment, such as nested records and array slicing, and it allows pl/pgsql assignment to support the datatype-specific subscripting behaviors introduced in commit c7aba7c14. There are cosmetic benefits too: when a syntax error occurs in a pl/pgsql expression, the error report no longer includes the confusing "SELECT" keyword that used to get prefixed to the expression text. Also, there seem to be some small speed gains. Discussion: https://postgr.es/m/4165684.1607707277@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c9d5298485b78a37923a23f9af9aa0ade06762db
Remove PLPGSQL_DTYPE_ARRAYELEM datum type within pl/pgsql. In the wake of the previous commit, we don't really need this anymore, since array assignment is primarily handled by the core code. The only way that that code could still be reached is that a GET DIAGNOSTICS target variable could be an array element. But that doesn't seem like a particularly essential feature. I'd added it in commit 55caaaeba, but just because it was easy not because anyone had actually asked for it. Hence, revert that patch and then remove the now-unreachable stuff. (If we really had to, we could probably reimplement GET DIAGNOSTICS using the new assignment machinery; but the cost/benefit ratio looks very poor, and it'd likely be a bit slower.) Note that PLPGSQL_DTYPE_RECFIELD remains. It's possible that we could get rid of that too, but maintaining the existing behaviors for RECORD-type variables seems like it might be difficult. Since there's not any functional limitation in those code paths as there was in the ARRAYELEM code, I've not pursued the idea. Discussion: https://postgr.es/m/4165684.1607707277@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1788828d33516809fa2d842bf6e273d78e21d957
Rethink the "read/write parameter" mechanism in pl/pgsql. Performance issues with the preceding patch to re-implement array element assignment within pl/pgsql led me to realize that the read/write parameter mechanism is misdesigned. Instead of requiring the assignment source expression to be such that all its references to the target variable could be passed as R/W, we really want to identify one reference to the target variable to be passed as R/W, allowing any other ones to be passed read/only as they would be by default. As long as the R/W reference is a direct argument to the top-level (hence last to be executed) function in the expression, there is no harm in R/O references being passed to other lower parts of the expression. Nor is there any use-case for more than one argument of the top-level function being R/W. Hence, rewrite that logic to identify one single Param that references the target variable, and make only that Param pass a read/write reference, not any other Params referencing the target variable. Discussion: https://postgr.es/m/4165684.1607707277@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1c1cbe279b3c6e8038c8f8079402f069bb4cea4c
Fix integer-overflow corner cases in substring() functions. If the substring start index and length overflow when added together, substring() misbehaved, either throwing a bogus "negative substring length" error on a case that should succeed, or failing to complain that a negative length is negative (and instead returning the whole string, in most cases). Unsurprisingly, the text, bytea, and bit variants of the function all had this issue. Rearrange the logic to ensure that negative lengths are always rejected, and add an overflow check to handle the other case. Also install similar guards into detoast_attr_slice() (nee heap_tuple_untoast_attr_slice()), since it's far from clear that no other code paths leading to that function could pass it values that would overflow. Patch by myself and Pavel Stehule, per bug #16804 from Rafi Shamim. Back-patch to v11. While these bugs are old, the common/int.h infrastructure for overflow-detecting arithmetic didn't exist before commit 4d6ad3125, and it doesn't seem like these misbehaviors are bad enough to justify developing a standalone fix for the older branches. Discussion: https://postgr.es/m/16804-f4eeeb6c11ba71d4@postgresql.org https://git.postgresql.org/pg/commitdiff/4bd3fad80e5c3bd107583dd9d32d4a47c045a3ec
Introduce a new GUC_REPORT setting "in_hot_standby". Aside from being queriable via SHOW, this value is sent to the client immediately at session startup, and again later on if the server gets promoted to primary during the session. The immediate report will be used in an upcoming patch to avoid an extra round trip when trying to connect to a primary server. Haribabu Kommi, Greg Nancarrow, Tom Lane; reviewed at various times by Laurenz Albe, Takayuki Tsunakawa, Peter Smith. Discussion: https://postgr.es/m/CAF3+xM+8-ztOkaV9gHiJ3wfgENTq97QcjXQt+rbFQ6F7oNzt9A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/bf8a662c9afad6fd07b42cdc5e71416c51f75d31
Allow psql's \dt and \di to show TOAST tables and their indexes. Formerly, TOAST objects were unconditionally suppressed, but since \d is able to print them it's not very clear why these variants should not. Instead, use the same rules as for system catalogs: they can be seen if you write the 'S' modifier or a table name pattern. (In practice, since hardly anybody would keep pg_toast in their search_path, it's really down to whether you use a pattern that can match pg_toast.*.) No docs change seems necessary because the docs already say that this happens for "system objects"; we're just classifying TOAST tables as being that. Justin Pryzby, reviewed by Laurenz Albe Discussion: https://postgr.es/m/20201130165436.GX24052@telsasoft.com https://git.postgresql.org/pg/commitdiff/7d80441d2c8de5cd5d593e302bd14e8b19ee92d4
Revert unstable test cases from commit 7d80441d2. I momentarily forgot that the "owner" column wouldn't be stable in the buildfarm. Oh well, these tests weren't very valuable anyway. Discussion: https://postgr.es/m/20201130165436.GX24052@telsasoft.com https://git.postgresql.org/pg/commitdiff/14d49f483d4c8a5a356e25d5e5ff5726ca43abff
Add a test module for the regular expression package. This module provides a function test_regex() that is functionally rather like regexp_matches(), but with additional debugging-oriented options and additional output. The debug options are somewhat obscure; they are chosen to match the API of the test harness that Henry Spencer wrote way-back-when for use in Tcl. With this, we can import all the test cases that Spencer wrote originally, even for regex functionality that we don't currently expose in Postgres. This seems necessary because we can no longer rely on Tcl to act as upstream and verify any fixes or improvements that we make. In addition to Spencer's tests, I added a few for lookbehind constraints (which we added in 2015, and Tcl still hasn't absorbed) that are modeled on his tests for lookahead constraints. After looking at code coverage reports, I also threw in a couple of tests to more fully exercise our "high colormap" logic. According to my testing, this brings the check-world coverage for src/backend/regex/ from 71.1% to 86.7% of lines. (coverage.postgresql.org shows a slightly different number, which I think is because it measures a non-assert build.) Discussion: https://postgr.es/m/2873268.1609732164@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ca8217c10138fa3ffe1e7d1def2484fd0eb78226
Add idle_session_timeout. This GUC variable works much like idle_in_transaction_session_timeout, in that it kills sessions that have waited too long for a new client query. But it applies when we're not in a transaction, rather than when we are. Li Japin, reviewed by David Johnston and Hayato Kuroda, some fixes by me Discussion: https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com https://git.postgresql.org/pg/commitdiff/9877374bef76ef03923f6aa8b955f2dbcbe6c2c7
Improve timeout.c's handling of repeated timeout set/cancel. A very common usage pattern is that we set a timeout that we don't expect to reach, cancel it after a little bit, and later repeat. With the original implementation of timeout.c, this results in one setitimer() call per timeout set or cancel. We can do a lot better by being lazy about changing the timeout interrupt request, namely: (1) never cancel the outstanding interrupt, even when we have no active timeout events; (2) if we need to set an interrupt, but there already is one pending at or before the required time, leave it alone. When the interrupt happens, the signal handler will reschedule it at whatever time is then needed. For example, with a one-second setting for statement_timeout, this method results in having to interact with the kernel only a little more than once a second, no matter how many statements we execute in between. The mainline code might never call setitimer() at all after the first time, while each time the signal handler fires, it sees that the then-pending request is most of a second away, and that's when it sets the next interrupt request for. Each mainline timeout-set request after that will observe that the time it wants is past the pending interrupt request time, and do nothing. This also works pretty well for cases where a few different timeout lengths are in use, as long as none of them are very short. But that describes our usage well. Idea and original patch by Thomas Munro; I fixed a race condition and improved the comments. Discussion: https://postgr.es/m/CA+hUKG+o6pbuHBJSGnud=TadsuXySWA7CCcPgCt2QE9F6_4iHQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/09cf1d52267644cdbdb734294012cf1228745aaa
Improve commentary in timeout.c. On re-reading I realized that I'd missed one race condition in the new timeout code. It's safe, but add a comment explaining it. Discussion: https://postgr.es/m/CA+hUKG+o6pbuHBJSGnud=TadsuXySWA7CCcPgCt2QE9F6_4iHQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9486e7b666fd113f043d5f091fd42bc1ef72acd8
Fix bogus link in test comments. I apparently copied-and-pasted the wrong link in commit ca8217c10. Point it where it was meant to go. https://git.postgresql.org/pg/commitdiff/f7a1a805cb178653ea2a6c8991ad73b035af953e
Further second thoughts about idle_session_timeout patch. On reflection, the order of operations in PostgresMain() is wrong. These timeouts ought to be shut down before, not after, we do the post-command-read CHECK_FOR_INTERRUPTS, to guarantee that any timeout error will be detected there rather than at some ill-defined later point (possibly after having wasted a lot of work). This is really an error in the original idle_in_transaction_timeout patch, so back-patch to 9.6 where that was introduced. https://git.postgresql.org/pg/commitdiff/b8d0cda53377515ac61357ec4a60e85ca873f486
Adjust createdb TAP tests to work on recent OpenBSD. We found last February that the error-case tests added by commit 008cf0409 failed on OpenBSD, because that platform doesn't really check locale names. At the time it seemed that that was only an issue for LC_CTYPE, but testing on a more recent version of OpenBSD shows that it's now equally lax about LC_COLLATE. Rather than dropping the LC_COLLATE test too, put back LC_CTYPE (reverting c4b0edb07), and adjust these tests to accept the different error message that we get if setlocale() doesn't reject a bogus locale name. The point of these tests is not really what the backend does with the locale name, but to show that createdb quotes funny locale names safely; so we're not losing test reliability this way. Back-patch as appropriate. Discussion: https://postgr.es/m/231373.1610058324@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9ffe2278372d7549547176c23564a5b3404d072e
Fix ancient bug in parsing of BRE-mode regular expressions. brenext(), when parsing a '*' quantifier, forgot to return any "value" for the token; per the equivalent case in next(), it should return value 1 to indicate that greedy rather than non-greedy behavior is wanted. The result is that the compiled regexp could behave like 'x*?' rather than the intended 'x*', if we were unlucky enough to have a zero in v->nextvalue at this point. That seems to happen with some reliability if we have '.*' at the beginning of a BRE-mode regexp, although that depends on the initial contents of a stack-allocated struct, so it's not guaranteed to fail. Found by Alexander Lakhin using valgrind testing. This bug seems to be aboriginal in Spencer's code, so back-patch all the way. Discussion: https://postgr.es/m/16814-6c5e3edd2bdf0d50@postgresql.org https://git.postgresql.org/pg/commitdiff/afcc8772edcec687d87b6f762ca6113229af7291
Fix plpgsql tests for debug_invalidate_system_caches_always. Commit c9d529848 resulted in having a couple more places where the error context stack for a failure varies depending on debug_invalidate_system_caches_always (nee CLOBBER_CACHE_ALWAYS). This is not very surprising, since we have to re-parse cached plans if the plan cache is clobbered. Stabilize the expected test output by hiding the context stack in these places, as we've done elsewhere in this test script. (Another idea worth considering, now that we have debug_invalidate_system_caches_always, is to force it to zero for these test cases. That seems like it'd risk reducing the coverage of cache-clobber testing, which might or might not be worth being able to verify that we get the expected error output in normal cases. For the moment I just stuck with the existing technique.) In passing, update comments that referred to CLOBBER_CACHE_ALWAYS. Per buildfarm member hyrax. https://git.postgresql.org/pg/commitdiff/39d4a153105f0693d93f593a23e5144e2bd031ef
Thomas Munro pushed:
Remove unused function prototypes. Cleanup for commit dee663f7. Reported-by: Tomas Vondra tomas.vondra@enterprisedb.com Discussion: https://postgr.es/m/CA+hUKGLJ=84YT+NvhkEEDAuUtVHMfQ9i-N7k_o50JmQ6Rpj_OQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/87c23d36a3bc81e57b813f13c403f74a67ff33a9
Rename "enum blacklist" to "uncommitted enums". We agreed to remove this terminology and use something more descriptive. Discussion: https://postgr.es/m/20200615182235.x7lch5n6kcjq4aue%40alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/c0d4f6d897492727d4812679e6a94d12edbe016f
pgindent: whitelist/blacklist -> additional/excluded. Author: Dagfinn Ilmari Mannsåker ilmari@ilmari.org Discussion: https://postgr.es/m/20200615182235.x7lch5n6kcjq4aue%40alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/fe05b6b620066aec313c43b6b4d6c169d0a346f7
Replace remaining uses of "whitelist". Instead describe the action that the list effects, or just use "list" where the meaning is obvious from context. Author: Dagfinn Ilmari Mannsåker ilmari@ilmari.org Discussion: https://postgr.es/m/20200615182235.x7lch5n6kcjq4aue%40alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/034510c820cd75e0410332d92b4967ef9b844936
Peter Geoghegan pushed:
Peter Eisentraut pushed:
doc: Document how to run regression tests with custom server settings. Author: Craig Ringer craig.ringer@2ndquadrant.com Discussion: https://www.postgresql.org/message-id/flat/CAMsr+YF=+ctXBZj3ywmvKNUjWpxmuTuUKuv-rgbHGX5i5pLstQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/854434c50a35e7cb2ae22588c80ebf36e889197a
Replace CLOBBER_CACHE_ALWAYS with run-time GUC. Forced cache invalidation (CLOBBER_CACHE_ALWAYS) has been impractical to use for testing in PostgreSQL because it's so slow and because it's toggled on/off only at build time. It is helpful when hunting bugs in any code that uses the sycache/relcache because causes cache invalidations to be injected whenever it would be possible for an invalidation to occur, whether or not one was really pending. Address this by providing run-time control over cache clobber behaviour using the new debug_invalidate_system_caches_always GUC. Support is not compiled in at all unless assertions are enabled or CLOBBER_CACHE_ENABLED is explicitly defined at compile time. It defaults to 0 if compiled in, so it has negligible effect on assert build performance by default. When support is compiled in, test code can now set debug_invalidate_system_caches_always=1 locally to a backend to test specific queries, functions, extensions, etc. Or tests can toggle it globally for a specific test case while retaining normal performance during test setup and teardown. For backwards compatibility with existing test harnesses and scripts, debug_invalidate_system_caches_always defaults to 1 if CLOBBER_CACHE_ALWAYS is defined, and to 3 if CLOBBER_CACHE_RECURSIVE is defined. CLOBBER_CACHE_ENABLED is now visible in pg_config_manual.h, as is the related RECOVER_RELATION_BUILD_MEMORY setting for the relcache. Author: Craig Ringer craig.ringer@2ndquadrant.com Discussion: https://www.postgresql.org/message-id/flat/CAMsr+YF=+ctXBZj3ywmvKNUjWpxmuTuUKuv-rgbHGX5i5pLstQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4656e3d66893f286767285cf74dabb3877068e49
Dean Rasheed pushed:
Fix numeric_power() when the exponent is INT_MIN. In power_var_int(), the computation of the number of significant digits to use in the computation used log(Abs(exp)), which isn't safe because Abs(exp) returns INT_MIN when exp is INT_MIN. Use fabs() instead of Abs(), so that the exponent is cast to a double before the absolute value is taken. Back-patch to 9.6, where this was introduced (by 7d9a4737c2). Discussion: https://postgr.es/m/CAEZATCVd6pMkz=BrZEgBKyqqJrt2xghr=fNc8+Z=5xC6cgWrWA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/bc43b7c2c06c32264efe79d0b86abd41236f1d5b
Add an explicit cast to double when using fabs(). Commit bc43b7c2c0 used fabs() directly on an int variable, which apparently requires an explicit cast on some platforms. Per buildfarm. https://git.postgresql.org/pg/commitdiff/fead67c24ada8c6a4b661dec6f159dca1447e3d8
Bruce Momjian pushed:
doc: improve NLS instruction wording. Reported-by: "Tang, Haiying" Discussion: https://postgr.es/m/bbbccf7a3c2d436e85d45869d612fd6b@G08CNEXMBPEKD05.g08.fujitsu.local Author: "Tang, Haiying" Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/47b2ed1d06c1218eee8869c8eca8bf002b9d43a6
doc: expand description of how non-SELECT queries are processed. The previous description of how the executor processes non-SELECT queries was very dense, causing lack of clarity. This expanded text spells it out more simply. Reported-by: fotis.koutoupas@gmail.com Discussion: https://postgr.es/m/160912275508.676.17469511338925622905@wrigleys.postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/01334c92fa09dc496a444a4f206854ef37247258
Fujii Masao pushed:
doc: Fix description about default behavior of recovery_target_timeline. The default value of recovery_target_timeline was changed in v12, but the description about the default behavior of that was not updated. Back-patch to v12 where the default behavior of recovery_target_timeline was changed. Author: Benoit Lobréau Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CAPE8EZ7c3aruEmM24GYkj8y8WmHKD1m9TtPtgCF0nQ3zw4LCkQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/25dde5835772a58356383bf1112c6c2a1a37df0e
Detect the deadlocks between backends and the startup process. The deadlocks that the recovery conflict on lock is involved in can happen between hot-standby backends and the startup process. If a backend takes an access exclusive lock on the table and which finally triggers the deadlock, that deadlock can be detected as expected. On the other hand, previously, if the startup process took an access exclusive lock and which finally triggered the deadlock, that deadlock could not be detected and could remain even after deadlock_timeout passed. This is a bug. The cause of this bug was that the code for handling the recovery conflict on lock didn't take care of deadlock case at all. It assumed that deadlocks involving the startup process and backends were able to be detected by the deadlock detector invoked within backends. But this assumption was incorrect. The startup process also should have invoked the deadlock detector if necessary. To fix this bug, this commit makes the startup process invoke the deadlock detector if deadlock_timeout is reached while handling the recovery conflict on lock. Specifically, in that case, the startup process requests all the backends holding the conflicting locks to check themselves for deadlocks. Back-patch to v9.6. v9.5 has also this bug, but per discussion we decided not to back-patch the fix to v9.5. Because v9.5 doesn't have some infrastructure codes (e.g., 37c54863cf) that this bug fix patch depends on. We can apply those codes for the back-patch, but since the next minor version release is the final one for v9.5, it's risky to do that. If we unexpectedly introduce new bug to v9.5 by the back-patch, there is no chance to fix that. We determined that the back-patch to v9.5 would give more risk than gain. Author: Fujii Masao Reviewed-by: Bertrand Drouvot, Masahiko Sawada, Kyotaro Horiguchi Discussion: https://postgr.es/m/4041d6b6-cf24-a120-36fa-1294220f8243@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/8900b5a9d59a645b3485f5b046c4c7871b2c4026
Add GUC to log long wait times on recovery conflicts. This commit adds GUC log_recovery_conflict_waits that controls whether a log message is produced when the startup process is waiting longer than deadlock_timeout for recovery conflicts. This is useful in determining if recovery conflicts prevent the recovery from applying WAL. Note that currently a log message is produced only when recovery conflict has not been resolved yet even after deadlock_timeout passes, i.e., only when the startup process is still waiting for recovery conflict even after deadlock_timeout. Author: Bertrand Drouvot, Masahiko Sawada Reviewed-by: Alvaro Herrera, Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/9a60178c-a853-1440-2cdc-c3af916cff59@amazon.com https://git.postgresql.org/pg/commitdiff/0650ff23038bc3eb8d8fd851744db837d921e285
Tomáš Vondra pushed:
Report progress of COPY commands. This commit introduces a view pg_stat_progress_copy, reporting progress of COPY commands. This allows rough estimates how far a running COPY progressed, with the caveat that the total number of bytes may not be available in some cases (e.g. when the input comes from the client). Author: Josef Šimánek Reviewed-by: Fujii Masao, Bharath Rupireddy, Vignesh C, Matthias van de Meent Discussion: https://postgr.es/m/CAFp7QwqMGEi4OyyaLEK9DR0+E+oK3UtA4bEjDVCa4bNkwUY2PQ@mail.gmail.com Discussion: https://postgr.es/m/CAFp7Qwr6_FmRM6pCO0x_a0mymOfX_Gg+FEKet4XaTGSW=LitKQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8a4f618e7ae3cb11b0b37d0f06f05c8ff905833f
Minor fixes in COPY progress docs. Author: Justin Pryzby Discussion: https://postgr.es/m/CAFp7Qwr6_FmRM6pCO0x_a0mymOfX_Gg+FEKet4XaTGSW=LitKQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ebb5457cfa514972847a2d03b5b4fd46f69bdc9b
Atsushi Torikoshi sent in another revision of a patch to implement pg_get_target_backend_memory_contexts() and make it possible to collect memory contexts of the specified process.
Atsushi Torikoshi sent in another revision of a patch to add a wait_start column to the pg_locks view.
Mark Zhao sent in a patch intended to fix a bug that manifested as logical replication on partitioned tables being very slow and consuming a lot of CPU by adding a missing RelationClose after RelationIdGetRelation in pgoutput.c.
Önder Kalacı sent in another revision of a patch to implement row filtering for logical replication.
Justin Pryzby sent in a patch to Allow errors in parameter values to be reported during the BIND phase.
Pavel Stěhule sent in another revision of a patch to make it possible to make it possible to write window functions in PLs, along with an implementation of same in PL/pgsql.
Bharath Rupireddy sent in three more revisions of a patch to make it possible to use parallel inserts in CTAS.
Kyotaro HORIGUCHI sent in four more revisions of a patch intended to fix a bug that manifested as failure of a standby to follow a timeline switch by ensuring that the Walsender tracks timeline switches while sending a historic timeline.
Peter Smith sent in four more revisions of a patch to make it possible to use multiple tablesync workers.
Dilip Kumar sent in another revision of a patch to add options for custom table compression methods.
Dmitry Dolgov sent in three more revisions of a patch to use the generic subscripting infrastructure for JSONB operations.
Justin Pryzby sent in another revision of a patch to support multiple compression methods and options for same in pg_dump.
Masahiko Sawada sent in a patch to introduce an IndexAM API for choosing index vacuum strategy, use same to choose index vacuum strategy, and skip btree bulkdelete if the index doesn't grow.
Thomas Munro sent in another revision of a patch to reduce the WaitEventSet syscall churn.
Pavel Stěhule sent in a patch to add an option to use a shorthand for argument and local variable references in PL/pgsql.
Dmitry Dolgov sent in another revision of a patch to Prevent jumbling of every element in ArrayExpr in order to keep pg_stat_statements from producing different entries for what are essentially similar queries.
Tom Lane sent in a PoC patch to deal with MacOS's SIP infrastructure works for dynamic libraries.
Amit Kapila sent in a patch to track replication origin progress for rollbacks for some cases the patch for tracking 2PC in logical replication missed.
Paul Martinez sent in a patch to add partial foreign key updates in referential integrity triggers.
Bruce Momjian sent in two more revisions of a patch to consolidate more of the hex functions in /common.
Shinya Kato, Masahiko Sawada, and Fujii Masao traded patches to fill out the implementation of CLOSE, FETCH, and MOVE tab completion in psql.
Daniel Gustafsson sent in two more revisions of a patch to support enabling and disabling checksums on running clusters.
Tsutomu Yamada and Tomáš Vondra traded patches to add a family of functions starting with \dX to psql which deals with extended statistics.
Bharath Rupireddy sent in two more revisions of a patch to add a postgres_fdw function to discard cached connections, add a postgres_fdw.keep_connections GUC to control whether connections are cached, and add a similar server-level keep_connection GUC.
Ryo Matsumura sent in a patch atop the libpq tracing patch to fix some oversights in same.
Kyotaro HORIGUCHI sent in another revision of a patch to intended to fix a bug that manifested as corruption during WAL replay by delaying checkpoint completion until_after truncation succeeds.
Greg Sabino Mullane sent in another revision of a patch to enable psql's \df to choose functions by input type.
Movead Li sent in another revision of a patch to fix the waldump size for wal switch.
Kirk Jamison sent in another revision of a patch to make dropping relation buffers more efficient with dlist.
Michaël Paquier sent in another revision of a patch to add SHA1 to the cryptohash infrastructure.
Julien Rouhaud sent in another revision of a patch to move pg_stat_statements query jumbling to core, expose queryid in pg_stat_activity and log_line_prefix, and expose query identifier in verbose explain.
Laurenz Albe sent in two more revisions of a patch to add session statistics to pg_stat_database.
Zeng Wenjing sent in a PoC patch to implement global indexes.
Bharath Rupireddy sent in three more revisions of a patch to implement EXPLAIN [ANALYZE] for REFRESH MATERIALIZED VIEW.
Masahiko Sawada sent in a patch intended to fix a bug that manifested as logical replication worker accesses catalogs in error context callback by storing both the local and the remote type names in SlotErrCallbackArg so that it's possible just to set the names in the error callback without a system cache lookup.
Vigneshwaran C sent in a patch to add schema level support for PUBLICATIONs.
Mark Dilger sent in two more revisions of a patch to add a new pg_amcheck contrib module, which is a command line interface for running amcheck's verifications against tables and indexes.
Thomas Munro sent in a patch to add FreeBSD to the list of platforms that have fdatasync.
Kyotaro HORIGUCHI sent in another revision of a patch to make the stats collector more efficient by replacing the files it used for temporary storage with shared memory.
Michaël Paquier sent in another revision of a patch to refactor HMAC implementations to reduce duplication.
Pavel Stěhule sent in another revision of a patch to reduce the overhead of execution of the CALL statement in no atomic mode from PL/pgSQL.
Kyotaro HORIGUCHI sent in another revision of a patch to make ALTER TABLE SET [UN]LOGGED avoid a heap rewrite, change SET LOGGED when wal_level > minimal so it emits WAL using XLOG_FPI instead of a massive number of HEAP_INSERTs, and allows for the cleanup of files left behind in the crash of the transaction that created it.
Pavel Stěhule sent in a patch to add a way to return the text value of variable content to the PL/pgsql debugging API.
Pavel Stěhule sent in a patch to make it possible to use a special pager for psql's \watch command.
Tomáš Vondra sent in another revision of a patch to make it possible to create extended statistics on expressions.
Simon Riggs sent in four more revisions of a patch to implement system-versioned tables.
Peter Eisentraut sent in another revision of a patch to pageinspect which changes the type of block number arguments to bigint in order to avert overflow.
Bruce Momjian sent in four more revisions of a patch to add tests for key management.
Álvaro Herrera and Tomáš Vondra traded patches to implement MERGE.
Pavel Stěhule and Erik Rijkers traded patches to implement schema variables.
Álvaro Herrera and Justin Pryzby traded patches to implement ALTER TABLE ... DETACH PARTITION CONCURRENTLY.
Noah Misch sent in a patch to fix pg_dump for GRANT OPTION among initial privileges.
Krasiyan Andreev sent in another revision of a patch to implement NULL treatment for window functions.
Michael Banck sent in a patch to fix an issue where psql's \watch is not working correctly in the case where the query in question doesn't return rows.
Thomas Munro sent in a patch to use pg_pwrite() in pg_test_fsync to maintain consistency with what PostgreSQL now does.
Justin Pryzby sent in another revision of a patch to fix some documentation and comments in the patch that implements pluggable compression in libpq.
Noah Misch sent in another revision of a patch intended to fix a bug that manifested as spurious "apparent wraparound" via SimpleLruTruncate() rounding.
Shenhao Wang sent in a patch intended to fix a bug that manifested as invalid data in file backup_label problem on Windows by setting text mode when reading backup_label and tablesapce_map.
Tatsuo Ishii sent in a patch to fix a missing acronym label in the documentation.
Tomáš Vondra sent in another revision of a patch to set PD_ALL_VISIBLE and visibility map bits in COPY FREEZE, making good the lack of page-level flag updates.
Tom Lane sent in a patch intended to fix a bug that manifested as multiple hosts in connection string failed to failover in non-hot standby mode.
pg_back is a simple (well kind of) bash shell script that can dump all your databases to files.
It can operate on standby server and takes care of pausing and resuming WAL replay.
The 1.10 release brings some bugfixes. See the CHANGELOG file in the project for more information.
pg_back is licensed under the classic 2 clauses BSD license.
Thanks to all the contributors!
More at https://github.com/orgrim/pg_back
The pgagroal community is happy to announce version 1.1.0.
New features
pgagroal
pgagroal is a high-performance protocol-native connection pool for PostgreSQL.
Features
Learn more on our web site or GitHub. Follow on Twitter.
pgagroal is released under the 3-clause BSD license, and is sponsored by Red Hat.
Announcing Veil2 0.9.2 beta.
Veil2 provides a framework for implementing Relational Security Systems in PostgreSQL.
Full documentation is available here: https://marcmunro.github.io/veil2/html/index.html
A description of Relational Security can be found here: https://marcmunro.github.io/veil2/html/ch01.html
This release cleans up documentation (particularly for pgxn), improves performance and fixes a small number of bugs.
This is the first release that is considered good enough for real use, and is considered a production release candidate.
Happy New Year from the PostgreSQL Weekly News!
PostgreSQL Product NewsDatabase Lab 2.1, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released: https://postgres.ai/blog/dle-2.1-release/
PostgreSQL Jobs for Januaryhttps://archives.postgresql.org/pgsql-jobs/2021-01/
PostgreSQL in the NewsPlanet 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 david@fetter.org.
Applied PatchesJeff Davis pushed:
Bruce Momjian pushed:
Revert "Add key management system" (978f869b99) & later commits. The patch needs test cases, reorganization, and cfbot testing. Technically reverts commits 5c31afc49d..e35b2bad1a (exclusive/inclusive) and 08db7c63f3..ccbe34139b. Reported-by: Tom Lane, Michael Paquier Discussion: https://postgr.es/m/E1ktAAG-0002V2-VB@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/3187ef7c46c5b884267a88f2d6119c9a05f1bbba
Update copyright for 2021. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/ca3b37487be333a1d241dab1bbdd17a211a88f43
Fujii Masao pushed:
Michaël Paquier pushed:
Fix inconsistent code with shared invalidations of snapshots. The code in charge of processing a single invalidation message has been using since 568d413 the structure for relation mapping messages. This had fortunately no consequence as both locate the database ID at the same location, but it could become a problem in the future if this area of the code changes. Author: Konstantin Knizhnik Discussion: https://postgr.es/m/8044c223-4d3a-2cdb-42bf-29940840ce94@postgrespro.ru Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/643428c54b95d472c6d949c3c3c11f347e371a2b
doc: Improve description of min_dynamic_shared_memory. While on it, fix one oversight in 90fbf7c, that introduced a reference to an incorrect value for the compression level of pg_dump. Author: Justin Pryzby Reviewed-by: Thomas Munro, Michael Paquier Discussion: https://postgr.es/m/CA+hUKGJRTLWWPcQfjm_xaOk98M8aROK903X92O0x-4vLJPWrrA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1b3433e25f81d6fc4f231887ab965e1ea1bcb47e
Remove references to libpq_srcdir in adminpack and old_snapshot. Those two modules included references to libpq's source path, without using anything from libpq. Some copy-pastos done when each module was created are likely at the origin of those useless references (aecf5ee for old_snapshot, fe59e56 for adminpack). Reviewed-by: Tom Lane, David Rowley Discussion: https://postgr.es/m/X+LQpfLyk7jgzUki@paquier.xyz https://git.postgresql.org/pg/commitdiff/107a2d4204ff4bf4ce05e3525f0d94fc0bd497ff
Sanitize IF NOT EXISTS in EXPLAIN for CTAS and matviews. IF NOT EXISTS was ignored when specified in an EXPLAIN query for CREATE MATERIALIZED VIEW or CREATE TABLE AS. Hence, if this clause was specified, the caller would get a failure if the relation already exists instead of a success with a NOTICE message. This commit makes the behavior of IF NOT EXISTS in EXPLAIN consistent with the non-EXPLAIN'd DDL queries, preventing a failure with IF NOT EXISTS if the relation to-be-created already exists. The skip is done before the SELECT query used for the relation is planned or executed, and a "dummy" plan is generated instead depending on the format used by EXPLAIN. Author: Bharath Rupireddy Reviewed-by: Zhijie Hou, Michael Paquier Discussion: https://postgr.es/m/CALj2ACVa3oJ9O_wcGd+FtHWZds04dEKcakxphGz5POVgD4wC7Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e665769e6d1e84b6650f53ed297058fc11152f7f
Tom Lane pushed:
Fix thinko in plpgsql memory leak fix. Commit a6b1f5365 intended to place the transient "target" list of a CALL statement in the function's statement-lifespan context, but I fat-fingered that and used get_eval_mcontext() instead of get_stmt_mcontext(). The eval_mcontext belongs to the "simple expression" infrastructure, which is destroyed at transaction end. The net effect is that a CALL in a procedure to another procedure that has OUT or INOUT parameters would fail if the called procedure did a COMMIT. Per report from Peter Eisentraut. Back-patch to v11, like the prior patch. Discussion: https://postgr.es/m/f075f7be-c654-9aa8-3ffc-e9214622f02a@enterprisedb.com https://git.postgresql.org/pg/commitdiff/ea80d8d9437e80de6506dbfe3765d834653312bf
Further fix thinko in plpgsql memory leak fix. There's a second call of get_eval_mcontext() that should also be get_stmt_mcontext(). This is actually dead code, since no interesting allocations happen before switching back to the original context, but we should keep it in sync with the other call to forestall possible future bugs. Discussion: https://postgr.es/m/f075f7be-c654-9aa8-3ffc-e9214622f02a@enterprisedb.com https://git.postgresql.org/pg/commitdiff/5f2e09bcccd771629fb7a2885f8c468ae0f7fb33
Expose the default for channel_binding in PQconndefaults(). If there's a static default value for a connection option, it should be shown in the PQconninfoOptions array. Daniele Varrazzo Discussion: https://postgr.es/m/CA+mi_8Zo8Rgn7p+6ZRY7QdDu+23ukT9AvoHNyPbgKACxwgGhZA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/cf61b0734c61d93c62827fe4e44fa2162a533b8e
Fix bugs in libpq's GSSAPI encryption support. The critical issue fixed here is that if a GSSAPI-encrypted connection is successfully made, pqsecure_open_gss() cleared conn->allow_ssl_try, as an admittedly-hacky way of preventing us from then trying to tunnel SSL encryption over the already-encrypted connection. The problem with that is that if we abandon the GSSAPI connection because of a failure during authentication, we would not attempt SSL encryption in the next try with the same server. This can lead to unexpected connection failure, or silently getting a non-encrypted connection where an encrypted one is expected. Fortunately, we'd only manage to make a GSSAPI-encrypted connection if both client and server hold valid tickets in the same Kerberos infrastructure, which is a relatively uncommon environment. Nonetheless this is a very nasty bug with potential security consequences. To fix, don't reset the flag, instead adding a check for conn->gssenc being already true when deciding whether to try to initiate SSL. While here, fix some lesser issues in libpq's GSSAPI code: * Use the need_new_connection stanza when dropping an attempted GSSAPI connection, instead of partially duplicating that code. The consequences of this are pretty minor: AFAICS it could only lead to auth_req_received or password_needed remaining set when they shouldn't, which is not too harmful. * Fix pg_GSS_error() to not repeat the "mprefix" it's given multiple times, and to notice any failure return from gss_display_status(). * Avoid gratuitous dependency on NI_MAXHOST in pg_GSS_load_servicename(). Per report from Mikael Gustavsson. Back-patch to v12 where this code was introduced. Discussion: https://postgr.es/m/e5b0b6ed05764324a2f3fe7acfc766d5@smhi.se https://git.postgresql.org/pg/commitdiff/ff6ce9a3a691a96e8e47ed449bc51c5a178e6931
Fix assorted issues in backend's GSSAPI encryption support. Unrecoverable errors detected by GSSAPI encryption can't just be reported with elog(ERROR) or elog(FATAL), because attempting to send the error report to the client is likely to lead to infinite recursion or loss of protocol sync. Instead make this code do what the SSL encryption code has long done, which is to just report any such failure to the server log (with elevel COMMERROR), then pretend we've lost the connection by returning errno = ECONNRESET. Along the way, fix confusion about whether message translation is done by pg_GSS_error() or its callers (the latter should do it), and make the backend version of that function work more like the frontend version. Avoid allocating the port->gss struct until it's needed; we surely don't need to allocate it in the postmaster. Improve logging of "connection authorized" messages with GSS enabled. (As part of this, I back-patched the code changes from dc11f31a1.) Make BackendStatusShmemSize() account for the GSS-related space that will be allocated by CreateSharedBackendStatus(). This omission could possibly cause out-of-shared-memory problems with very high max_connections settings. Remove arbitrary, pointless restriction that only GSS authentication can be used on a GSS-encrypted connection. Improve documentation; notably, document the fact that libpq now prefers GSS encryption over SSL encryption if both are possible. Per report from Mikael Gustavsson. Back-patch to v12 where this code was introduced. Discussion: https://postgr.es/m/e5b0b6ed05764324a2f3fe7acfc766d5@smhi.se https://git.postgresql.org/pg/commitdiff/622ae4621ece72a9f64b5602c74d7aaf373c1631
Improve log messages related to pg_hba.conf not matching a connection. Include details on whether GSS encryption has been activated; since we added "hostgssenc" type HBA entries, that's relevant info. Kyotaro Horiguchi and Tom Lane. Back-patch to v12 where GSS encryption was introduced. Discussion: https://postgr.es/m/e5b0b6ed05764324a2f3fe7acfc766d5@smhi.se https://git.postgresql.org/pg/commitdiff/3995c424984e991b1069a2869af972dc07574c0b
Suppress log spam from multiple reports of SIGQUIT shutdown. When the postmaster sends SIGQUIT to its children, there's no real need for all the children to log that fact; the postmaster already made a log entry about it, so adding perhaps dozens or hundreds of child-process log entries adds nothing of value. So, let's introduce a new ereport level to specify "WARNING, but never send to log" and use that for these messages. Such a change wouldn't have been desirable before commit 7e784d1dc, because if someone manually SIGQUIT's a backend, we do want to log that. But now we can tell the difference between a signal that was issued by the postmaster and one that was not with reasonable certainty. While we're here, also clear error_context_stack before ereport'ing, to prevent error callbacks from being invoked in the signal-handler context. This should reduce the odds of getting hung up while trying to notify the client. Per a suggestion from Andres Freund. Discussion: https://postgr.es/m/20201225230331.hru3u6obyy6j53tk@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/1f9158ba48122fa232db955a2ee324eec1848ba9
Doc: fix up PDF build warnings from over-width table columns. Addition of multirange info to tables 8.27 and 65.1 made them start throwing "exceed the available area" warnings in PDF docs builds. For 8.27, twiddling the existing column width hints was enough to fix this. For 65.1, I twiddled the widths a little, but to really fix it I had to insert a space after each comma in the table, to allow a line break to occur there. (This seemed easier to read and maintain than the alternative of inserting &zws;; entities.) Per buildfarm. https://git.postgresql.org/pg/commitdiff/f20dc2c8dd50a5c738d535205d5d44bff82d3f75
Fix up usage of krb_server_keyfile GUC parameter. secure_open_gssapi() installed the krb_server_keyfile setting as KRB5_KTNAME unconditionally, so long as it's not empty. However, pg_GSS_recvauth() only installed it if KRB5_KTNAME wasn't set already, leading to a troubling inconsistency: in theory, clients could see different sets of server principal names depending on whether they use GSSAPI encryption. Always using krb_server_keyfile seems like the right thing, so make both places do that. Also fix up secure_open_gssapi()'s lack of a check for setenv() failure --- it's unlikely, surely, but security-critical actions are no place to be sloppy. Also improve the associated documentation. This patch does nothing about secure_open_gssapi()'s use of setenv(), and indeed causes pg_GSS_recvauth() to use it too. That's nominally against project portability rules, but since this code is only built with --with-gssapi, I do not feel a need to do something about this in the back branches. A fix will be forthcoming for HEAD though. Back-patch to v12 where GSSAPI encryption was introduced. The dubious behavior in pg_GSS_recvauth() goes back further, but it didn't have anything to be inconsistent with, so let it be. Discussion: https://postgr.es/m/2187460.1609263156@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/860fe27ee1e2a4a1c36c2f874c37656533cccce9
Use setenv() in preference to putenv(). Since at least 2001 we've used putenv() and avoided setenv(), on the grounds that the latter was unportable and not in POSIX. However, POSIX added it that same year, and by now the situation has reversed: setenv() is probably more portable than putenv(), since POSIX now treats the latter as not being a core function. And setenv() has cleaner semantics too. So, let's reverse that old policy. This commit adds a simple src/port/ implementation of setenv() for any stragglers (we have one in the buildfarm, but I'd not be surprised if that code is never used in the field). More importantly, extend win32env.c to also support setenv(). Then, replace usages of putenv() with setenv(), and get rid of some ad-hoc implementations of setenv() wannabees. Also, adjust our src/port/ implementation of unsetenv() to follow the POSIX spec that it returns an error indicator, rather than returning void as per the ancient BSD convention. I don't feel a need to make all the call sites check for errors, but the portability stub ought to match real-world practice. Discussion: https://postgr.es/m/2065122.1609212051@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7ca37fb0406bc2cbbd864a2ffdbdb4479e338c0c
More fixups for pg_upgrade cross-version tests. Commit 7ca37fb04 removed regress_putenv from the regress.so library, so reloading a SQL function dependent on that would not work. Fix similarly to 52202bb39. Per buildfarm. https://git.postgresql.org/pg/commitdiff/091866724cb3ee7251fa56e2517248c4b7796ca8
Doc: spell out comparison behaviors for the date/time types. The behavior of cross-type comparisons among date/time data types was not really explained anywhere. You could probably infer it if you recognized the applicability of comments elsewhere about datatype conversions, but it seems worthy of explicit documentation. Per bug #16797 from Dana Burd. Discussion: https://postgr.es/m/16797-f264b0b980b53b8b@postgresql.org https://git.postgresql.org/pg/commitdiff/319f4d54e82d15d4a0c3f4cc1328c40dba024b5c
Doc: improve explanation of EXTRACT(EPOCH) for timestamp without tz. Try to be clearer about what computation is actually happening here. Per bug #16797 from Dana Burd. Discussion: https://postgr.es/m/16797-f264b0b980b53b8b@postgresql.org https://git.postgresql.org/pg/commitdiff/4d3f03f42227bb351c2021a9ccea2fff9c023cfc
Alexander Korotkov pushed:
Fix bugs in comparison functions for multirange_bsearch_match(). Two functions multirange_range_overlaps_bsearch_comparison() and multirange_range_contains_bsearch_comparison() contain bugs of returning -1 instead of 1. This commit fixes these bugs and adds corresponding regression tests. https://git.postgresql.org/pg/commitdiff/a5b81b6f0006ea0b502780ce7f73d295a225842c
Implement operators for checking if the range contains a multirange. We have operators for checking if the multirange contains a range but don't have the opposite. This commit improves completeness of the operator set by adding two new operators: @> (anyrange,anymultirange) and <@(anymultirange,anyrange). Catversion is bumped. https://git.postgresql.org/pg/commitdiff/4d7684cc754f312aee468abb83ca4f7da94b30a3
Improve the signature of internal multirange functions. There is a set of *_internal() functions exposed in include/utils/multirangetypes.h. This commit improves the signatures of these functions in two ways. * Add const qualifies where applicable. * Replace multirange typecache argument with range typecache argument. Multirange typecache was used solely to find the range typecache. At the same time, range typecache is easier for the caller to find. https://git.postgresql.org/pg/commitdiff/d1d61a8b23b604faf797695eeacaa5da4fe64762
Add support of multirange matching to the existing range GiST indexes. 6df7a9698b has introduced a set of operators between ranges and multiranges. Existing GiST indexes for ranges could easily support majority of them. This commit adds support for new operators to the existing range GiST indexes. New operators resides the same strategy numbers as existing ones. Appropriate check function is determined using the subtype. Catversion is bumped. https://git.postgresql.org/pg/commitdiff/db6335b5b1d6654b0e3104f36817800d127c1c91
Fix selectivity estimation @> (anymultirange, anyrange) operator. Attempt to get selectivity estimation for @> (anymultirange, anyrange) operator caused an error in buildfarm, because this operator was missed in switch() of calc_hist_selectivity(). Fix that and also make regression tests reliably check that selectivity estimation for (multi)ranges doesn't fall. Previously, whether we test selectivity estimation for (multi)ranges depended on whether autovacuum managed to gather concurrently to the test. Reported-by: Michael Paquier Discussion: https://postgr.es/m/X%2BwmgjRItuvHNBeV%40paquier.xyz https://git.postgresql.org/pg/commitdiff/62097a4cc8c725fa86d3170396a8f30609acd0d3
Refactor multirange_in(). This commit preserves the logic of multirange_in() but makes it more clear what's going on. Also, this commit fixes the compiler warning spotted by the buildfarm. Reported-by: Tom Lane Discussion: https://postgr.es/m/2246043.1609290699%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/16d531a30a120d13cc3b460fba6570024a1fcfa8
Noah Misch pushed:
In pg_upgrade cross-version test, handle lack of oldstyle_length(). This suffices for testing v12 -> v13; some other version pairs need more changes. Back-patch to v10, which removed the function. https://git.postgresql.org/pg/commitdiff/52202bb396b1e96c43bfd767d6e434b1c6fd2ae1
In pg_upgrade cross-version test, handle postfix operators. Commit 1ed6b895634ce0dc5fd4bd040e87252b32182cba eliminated support for them, so drop them from regression databases before upgrading. This is necessary but not sufficient for testing v13 -> v14 upgrades. Discussion: https://postgr.es/m/449144.1600439950@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/fa744697c79189a661f802d9a979d959b4454df0
Amit Kapila pushed:
Peter Geoghegan pushed:
Fix index deletion latestRemovedXid bug. The logic for determining the latest removed XID for the purposes of generating recovery conflicts in REDO routines was subtly broken. It failed to follow links from HOT chains, and so failed to consider all relevant heap tuple headers in some cases. To fix, expand the loop that deals with LP_REDIRECT line pointers to also deal with HOT chains. The new version of the loop is loosely based on a similar loop from heap_prune_chain(). The impact of this bug is probably quite limited, since the horizon code necessarily deals with heap tuples that are pointed to by LP_DEAD-set index tuples. The process of setting LP_DEAD index tuples (e.g. within the kill_prior_tuple mechanism) is highly correlated with opportunistic pruning of pointed-to heap tuples. Plus the question of generating a recovery conflict usually comes up some time after index tuple LP_DEAD bits were initially set, unlike heap pruning, where a latestRemovedXid is generated at the point of the pruning operation (heap pruning has no deferred "would-be page split" style processing that produces conflicts lazily). Only backpatch to Postgres 12, the first version where this logic runs during original execution (following commit 558a9165e08). The index latestRemovedXid mechanism has had the same bug since it first appeared over 10 years ago (in commit a760893d), but backpatching to all supported versions now seems like a bad idea on balance. Running the new improved code during recovery seems risky, especially given the lack of complaints from the field. Author: Peter Geoghegan pg@bowt.ie Discussion: https://postgr.es/m/CAH2-Wz=Eib393+HHcERK_9MtgNS7Ew1HY=RDC_g6GL46zM5C6Q@mail.gmail.com Backpatch: 12- https://git.postgresql.org/pg/commitdiff/422881744997417944634a7f84af7a66a608de9a
Get heap page max offset with buffer lock held. On further reflection it seems better to call PageGetMaxOffsetNumber() after acquiring a buffer lock on the page. This shouldn't really matter, but doing it this way is cleaner. Follow-up to commit 42288174. Backpatch: 12-, just like commit 42288174 https://git.postgresql.org/pg/commitdiff/32d6287d2eef6b6a4dde07e0513f3e4f321792ad
Noah Misch sent in a patch to merge similar algorithms into roles_is_member_of().
Vigneshwaran C sent in a patch atop the existing patch to parallelize parts of COPY which delegates the task of finding line boundaries to workers.
Bharath Rupireddy sent in a patch to implement EXPLAIN [ANALYZE] for REFRESH MATERIALIZED VIEW.
Masahiko Sawada sent in another revision of a patch to add new FDW APIs to support 2PC, introduce a global transaction manager, and implement those FDW APIs in the PostgreSQL FDW.
Peter Geoghegan sent in another revision of a patch to make the btvacuumstrategy() bottom-up index deletion changes.
Bharath Rupireddy sent in two more revisions of a patch to make it possible for CTAS to use parallel inserts.
Luc Vlaming sent in two revisions of a patch to generate JIT IR code lazily, which issue came up in the case where a lot of JIT IR code was being generated unecessarily for the case of partitioned tables where the IR for many of the partitions was never executed because those partitions got pruned.
Thomas Munro sent in another revision of a patch to make it possible to use parallel hashing to execute Full and Right JOINs.
Andrey Borodin sent in another revision of a patch to add LZ4 as a possible compression scheme for WALL FPIs.
David Rowley sent in two more revisions of a patch to reduce the number of special cases to build contrib modules on Windows.
Noah Misch sent in a patch to dump public schema ownership and security labels.
Paul Martinez sent in a patch to simplify the permission checking logic in user.c.
Bharath Rupireddy sent in another revision of a patch to allow parallel mode in the planning of REFRESH MATERIALIZED VIEW.
Thomas Munro sent in another revision of a patch to track relation sizes in shared memory, this capability being controlled by a new GUC, smgr_shared_relation, which limits the number of the newly introduced pool of SMgrSharedRelation objects, and provide a lock-free fast path for smgrnblocks().
Peter Smith sent in another revision of a patch to allow the table-sync worker to use multiple transactions.
Andrey V. Lepikhov sent in another revision of a patch to speed up COPY for the case where a table has foreign partitions by adding three new routines to the FDW API: BeginForeignCopy, EndForeignCopy, and ExecForeignCopy, and adding same to the PostgreSQL FDW.
Andrey Borodin sent in another revision of a patch to reorganize pglz compression code to make it more efficient by converting macro functions to regular functions for readability, Using a more compact hash table with uint16 indexes instead of pointers, avoiding prev pointer in hash tables, and using 4-byte comparisons in a search instead of 1-byte comparisons.
Justin Pryzby sent in another revision of a patch to implement CREATE TABLE (LIKE .. INCLUDING ACCESS METHOD).
Luc Vlaming sent in another revision of a patch to allow partial UNION ALL, improving parallel subquery costing thereby.
Rui Zhao sent in another revision of a patch to refactor the way is called RelationClose after RelationIdGetRelation.
Peter Eisentraut sent in another revision of a patch to implement dynamic result sets from procedures.
David Fetter sent in two revisions of a patch to surface popcount to SQL.
Joe Wildish sent in another revision of a patch to allow queries in WHEN expression of FOR EACH STATEMENT triggers.
David Fetter sent in another revision of a patch to make it possible to set pg_hba.conf parameters from initdb.
Greg Sabino Mullane sent in another revision of a patch to get psql's \df to help choose functions by input type.
David Fetter and Krasiyan Andreev traded patches to implement NULL treatement for window functions.
Dmitry Dolgov sent in three more revisions of a patch to use the generic type subscripting infrastructure for JSONB.
David Fetter sent in a WIP patch to document the hooks system.
Michael Banck sent in a patch to add a new PGC_ADMINSET guc context and pg_change_role_settings default role, creating a space between 'superuser' and 'user' for GUC contexts.
Álvaro Herrera sent in another revision of a patch to implement MERGE.
Peter Geoghegan sent in another revision of a patch to pass down a "logically unchanged index" hint, and use same to implement bottom-up index deletion.
Soumyadeep Chakraborty sent in another revision of a patch to add a routine to the table AM API that accepts column projection lists.
Bruce Momjian and Michaël Paquier traded patches to move the other hex functions to a common place in the source tree.
Etsuro Fujita sent in two more revisions of a patch to implement asynchronous append on postgres_fdw nodes.
David Fetter sent in another revision of a patch to implement range scans for TIDs.
Pavel Stěhule sent in another revision of a patch to reduce the execution overhead of CALL statements in no atomic mode from PL/pgSQL.
Pavel Stěhule sent in another revision of a patch to implement schema variables.
Josef Šimánek sent in another revision of a patch to add a pg_stat_progress_copy view with COPY progress report.
Pavel Stěhule sent in a patch to make it possible to write window functions in PLs, as they are currently limited to C.
Luc Vlaming sent in a patch to make bulk loading more efficient for heap tables by allocating to each backend a standalone set of blocks that are local to that backend, and reduces the time spent on locking the partition buffers by shifting around the logic to make each set of 128 blocks use the same buffer partition, and then adding a custom function to get buffer blocks specifically for extension, whilst keeping a previous partition lock, thereby reducing the amount of time we spent on futexes.
Michael Banck sent in a patch to move --data-checksums to the common options in initdb's --help output.
Thomas Munro sent in a patch to pgbench to add pthread barrier emulation for platforms that lack it.
Database Lab Engine is an open-source tool for building powerful development and testing environments based on thin cloning of PostgreSQL databases. Using Database Lab open-source CLI or API (and if you are using Database Lab SaaS, proprietary GUI), on a single machine with, say, a 1 TiB disk, you can easily create and destroy dozens of database copies of size 1 TiB each. All these copies are independently modifiable and created/destroyed in just a few seconds. This can become a game-changer in your development and testing workflow, improving time-to-market, and reducing costs of your non-production infrastructure.
In version 2.1 contains numerous improvements. Main new features are:
Links:
Any feedback is highly appreciated:
Pgpool-II 4.2.1, a connection pooler and statement replication system for PostgreSQL, released. https://www.pgpool.net/docs/42/en/html/release-4-2-1.html
Postgres Operator v1.6.0 released. https://github.com/zalando/postgres-operator
PostgreSQL Jobs for Decemberhttps://archives.postgresql.org/pgsql-jobs/2020-12/
PostgreSQL in the NewsPlanet PostgreSQL: http://planet.postgresql.org/
PostgreSQL Weekly News is brought to you this week by David Fetter
Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.
Applied PatchesTom Lane pushed:
Doc: fix description of how to use src/tutorial files. The separate "cd" command before invoking psql made sense (or at least I thought so) when it was added in commit ed1939332. But 4e3a61635 removed the supporting text that explained when to use it, making it just confusing. So drop it. Also switch from four-dot to three-dot filler for the unsupplied part of the path, since at least one person has read the four-dot filler as a typo for "../..". And fix these/those inconsistency. Discussion: https://postgr.es/m/160837647714.673.5195186835607800484@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/eea1e08cfc95bb7e1b7e9f515e73cf75adf147e3
Remove "invalid concatenation of jsonb objects" error case. The jsonb || jsonb operator arbitrarily rejected certain combinations of scalar and non-scalar inputs, while being willing to concatenate other combinations. This was of course quite undocumented. Rather than trying to document it, let's just remove the restriction, creating a uniform rule that unless we are handling an object-to-object concatenation, non-array inputs are converted to one-element arrays, resulting in an array-to-array concatenation. (This does not change the behavior for any case that didn't throw an error before.) Per complaint from Joel Jacobson. Back-patch to all supported branches. Discussion: https://postgr.es/m/163099.1608312033@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ff5d5611c01f60525c30b2c3ebc16d05edb7956d
Increase timeout in 021_row_visibility.pl. Commit 7b28913bc figured 30 seconds is long enough for anybody, but in contexts like valgrind runs, it isn't necessarily. https://git.postgresql.org/pg/commitdiff/08dde1b3dc78e90e01f3af8e89ab27ed37cd8963
Improve autoprewarm's handling of early-shutdown scenarios. Bad things happen if the DBA issues "pg_ctl stop -m fast" before autoprewarm finishes loading its list of blocks to prewarm. The current worker process successfully terminates early, but (if this wasn't the last database with blocks to prewarm) the leader process will just try to launch another worker for the next database. Since the postmaster is now in PM_WAIT_BACKENDS state, it ignores the launch request, and the leader just sits until it's killed manually. This is mostly the fault of our half-baked design for launching background workers, but a proper fix for that is likely to be too invasive to be back-patchable. To ameliorate the situation, fix apw_load_buffers() to check whether SIGTERM has arrived just before trying to launch another worker. That leaves us with only a very narrow window in each worker launch where SIGTERM could occur between the launch request and successful worker start. Another issue is that if the leader process does manage to exit, it unconditionally rewrites autoprewarm.blocks with only the blocks currently in shared buffers, thus forgetting any blocks that we hadn't reached yet while prewarming. This seems quite unhelpful, since the next database start will then not have the expected prewarming benefit. Fix it to not modify the file if we shut down before the initial load attempt is complete. Per bug #16785 from John Thompson. Back-patch to v11 where the autoprewarm code was introduced. Discussion: https://postgr.es/m/16785-c0207d8c67fb5f25@postgresql.org https://git.postgresql.org/pg/commitdiff/ff769831e081a4e69147019128a849ced43ec802
Improve client error messages for immediate-stop situations. Up to now, if the DBA issued "pg_ctl stop -m immediate", the message sent to clients was the same as for a crash-and-restart situation. This is confusing, not least because the message claims that the database will soon be up again, something we have no business predicting. Improve things so that we can generate distinct messages for the two cases (and also recognize an ad-hoc SIGQUIT, should somebody try that). To do that, add a field to pmsignal.c's shared memory data structure that the postmaster sets just before broadcasting SIGQUIT to its children. No interlocking seems to be necessary; the intervening signal-sending and signal-receipt should sufficiently serialize accesses to the field. Hence, this isn't any riskier than the existing usages of pmsignal.c. We might in future extend this idea to improve other postmaster-to-children signal scenarios, although none of them currently seem to be as badly overloaded as SIGQUIT. Discussion: https://postgr.es/m/559291.1608587013@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7e784d1dc191be24480a6b31a4ddc8e0e52be24d
Fix race condition between shutdown and unstarted background workers. If a database shutdown (smart or fast) is commanded between the time some process decides to request a new background worker and the time that the postmaster can launch that worker, then nothing happens because the postmaster won't launch any bgworkers once it's exited PM_RUN state. This is fine ... unless the requesting process is waiting for that worker to finish (or even for it to start); in that case the requestor is stuck, and only manual intervention will get us to the point of being able to shut down. To fix, cancel pending requests for workers when the postmaster sends shutdown (SIGTERM) signals, and similarly cancel any new requests that arrive after that point. (We can optimize things slightly by only doing the cancellation for workers that have waiters.) To fit within the existing bgworker APIs, the "cancel" is made to look like the worker was started and immediately stopped, causing deregistration of the bgworker entry. Waiting processes would have to deal with premature worker exit anyway, so this should introduce no bugs that weren't there before. We do have a side effect that registration records for restartable bgworkers might disappear when theoretically they should have remained in place; but since we're shutting down, that shouldn't matter. Back-patch to v10. There might be value in putting this into 9.6 as well, but the management of bgworkers is a bit different there (notably see 8ff518699) and I'm not convinced it's worth the effort to validate the patch for that branch. Discussion: https://postgr.es/m/661570.1608673226@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7519bd16d1a2a2007b3604f6740611c0b75a6cd0
Avoid time-of-day-dependent failure in log rotation test. Buildfarm members pogona and petalura have shown a failure when pg_ctl/t/004_logrotate.pl starts just before local midnight. The default rotate-at-midnight behavior occurs just before the Perl script examines current_logfiles, so it figures that the rotation it's already requested has occurred ... but in reality, that rotation happens just after it looks, so the expected new log data goes into a different file than the one it's examining. In HEAD, src/test/kerberos/t/001_auth.pl has acquired similar code that evidently has a related failure mode. Besides being quite new, few buildfarm critters run that test, so it's unsurprising that we've not yet seen a failure there. Fix both cases by setting log_rotation_age = 0 so that no time-based rotation can occur. Also absorb 004_logrotate.pl's decision to set lc_messages = 'C' into the kerberos test, in hopes that it will work in non-English prevailing locales. Report: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=pogona&dt=2020-12-24%2022%3A10%3A04 Report: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=petalura&dt=2020-02-01%2022%3A20%3A04 https://git.postgresql.org/pg/commitdiff/5c31afc49d0b62b357218b6f8b01782509ef8acd
Really fix the dummy implementations in cipher.c. 945083b2f wasn't enough to silence compiler warnings. https://git.postgresql.org/pg/commitdiff/0848cf4f553bda5a9f044c56b3bec19929ee24e6
Alexander Korotkov pushed:
Fix typalign in rangetypes statistics. 6df7a9698b introduces multirange types, whose typanalyze function shares infrastructure with range types typanalyze function. Since 6df7a9698b, information about type gathered by statistics is filled from typcache. But typalign is mistakenly always set to double. This commit fixes this oversight. https://git.postgresql.org/pg/commitdiff/4e1ee79e3182256d9c8ddbc1ce9c4e8419c611ff
Fix compiler warning in multirange_constructor0(). Discussion: https://postgr.es/m/X%2BBP8XE0UpIB6Yvh%40paquier.xyz Author: Michael Paquier https://git.postgresql.org/pg/commitdiff/29f8f546767ebb4253f6dc37815e2d95fff9acd0
Michaël Paquier pushed:
Refactor logic to check for ASCII-only characters in string. The same logic was present for collation commands, SASLprep and pgcrypto, so this removes some code. Author: Michael Paquier Reviewed-by: Stephen Frost, Heikki Linnakangas Discussion: https://postgr.es/m/X9womIn6rne6Gud2@paquier.xyz https://git.postgresql.org/pg/commitdiff/93e8ff8701a65a70ea8826bdde7fdbbd9c285477
Fix portability issues with parsing of recovery_target_xid. The parsing of this parameter has been using strtoul(), which is not portable across platforms. On most Unix platforms, unsigned long has a size of 64 bits, while on Windows it is 32 bits. It is common in recovery scenarios to rely on the output of txid_current() or even the newer pg_current_xact_id() to get a transaction ID for setting up recovery_target_xid. The value returned by those functions includes the epoch in the computed result, which would cause strtoul() to fail where unsigned long has a size of 32 bits once the epoch is incremented. WAL records and 2PC data include only information about 32-bit XIDs and it is not possible to have XIDs across more than one epoch, so discarding the high bits from the transaction ID set has no impact on recovery. On the contrary, the use of strtoul() prevents a consistent behavior across platforms depending on the size of unsigned long. This commit changes the parsing of recovery_target_xid to use pg_strtouint64() instead, available down to 9.6. There is one TAP test stressing recovery with recovery_target_xid, where a tweak based on pg_reset{xlog,wal} is added to bump the XID epoch so as this change gets tested, as per an idea from Alexander Lakhin. Reported-by: Alexander Lakhin Discussion: https://postgr.es/m/16780-107fd0c0385b1035@postgresql.org Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/6db27037b99358018353b8748719cfa9de484988
Fix typos and grammar in docs and comments. This fixes several areas of the documentation and some comments in matters of style, grammar, or even format. Author: Justin Pryzby Discussion: https://postgr.es/m/20201222041153.GK30237@telsasoft.com https://git.postgresql.org/pg/commitdiff/90fbf7c57df601c7e0b43ae7cf71f0f69908a7cc
Tomáš Vondra pushed:
Consider unsorted paths in generate_useful_gather_paths. generate_useful_gather_paths used to skip unsorted paths (without any pathkeys), but that is unnecessary - the later code actually can handle such paths just fine by adding a Sort node. This is clearly a thinko, preventing construction of useful plans. Backpatch to 13, where Incremental Sort was introduced. Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13 Discussion: https://postgr.es/m/CAAaqYe8cK3g5CfLC4w7bs=hC0mSksZC=H5M8LSchj5e5OxpTAg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f4a3c0b06250ddc8ae09b59b87cf68e9bc0d7ca1
Check parallel safety in generate_useful_gather_paths. Commit ebb7ae839d ensured we ignore pathkeys with volatile expressions when considering adding a sort below a Gather Merge. Turns out we need to care about parallel safety of the pathkeys too, otherwise we might try sorting e.g. on results of a correlated subquery (as demonstrated by a report from Luis Roberto). Initial investigation by Tom Lane, patch by James Coleman. Backpatch to 13, where the code was instroduced (as part of Incremental Sort). Reported-by: Luis Roberto Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13 Discussion: https://postgr.es/m/622580997.37108180.1604080457319.JavaMail.zimbra%40siscobra.com.br Discussion: https://postgr.es/m/CAAaqYe8cK3g5CfLC4w7bs=hC0mSksZC=H5M8LSchj5e5OxpTAg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/86b7cca72d4d0a4e043fac0a2cdd56218ff2f258
Disallow SRFs when considering sorts below Gather Merge. While we do allow SRFs in ORDER BY, scan/join processing should not consider such cases - such sorts should only happen via final Sort atop a ProjectSet. So make sure we don't try adding such sorts below Gather Merge, just like we do for expressions that are volatile and/or not parallel safe. Backpatch to PostgreSQL 13, where this code was introduced as part of the Incremental Sort patch. Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13 Discussion: https://postgr.es/m/CAAaqYe8cK3g5CfLC4w7bs=hC0mSksZC=H5M8LSchj5e5OxpTAg@mail.gmail.com Discussion: https://postgr.es/m/295524.1606246314%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/fac1b470a9f7e846534620b78ea4cf122ed432b2
Don't search for volatile expr in find_em_expr_usable_for_sorting_rel. While prepare_sort_from_pathkeys has to be concerned about matching up a volatile expression to the proper tlist entry, we don't need to do that in find_em_expr_usable_for_sorting_rel becausee such a sort will have to be postponed anyway. Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13 Discussion: https://postgr.es/m/CAAaqYe8cK3g5CfLC4w7bs%3DhC0mSksZC%3DH5M8LSchj5e5OxpTAg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/9aff4dc01fbb9e126efafd839ae8541362328f1a
Improve find_em_expr_usable_for_sorting_rel comment. Clarify the relationship between find_em_expr_usable_for_sorting_rel and prepare_sort_from_pathkeys, i.e. what restrictions need to be shared between those two places. Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13 Discussion: https://postgr.es/m/CAAaqYe8cK3g5CfLC4w7bs%3DhC0mSksZC%3DH5M8LSchj5e5OxpTAg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/1ca2eb103113d64930cf1120ba2beaaebd1076f4
Bruce Momjian pushed:
dummy commit. https://git.postgresql.org/pg/commitdiff/6ecf488d91ceb93a8be907a24c5d8a90358534ed
move hex_decode() to /common so it can be called from frontend. This allows removal of a copy of hex_decode() from ecpg, and will be used by the soon-to-be added pg_alterckey command. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/c3826f831e6e63e13a749fd3ab9fd7106707b549
revert removal of hex_decode() from ecpg from commit c3826f831e. ecpglib on certain platforms can't handle the pg_log_fatal calls from libraries. This was reported by the buildfarm. It needs a refactoring and return value change if it is later removed. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/558a6e8e21367dae9a294291337be10a8a4afd0a
Add key management system. This adds a key management system that stores (currently) two data encryption keys of length 128, 192, or 256 bits. The data keys are AES256 encrypted using a key encryption key, and validated via GCM cipher mode. A command to obtain the key encryption key must be specified at initdb time, and will be run at every database server start. New parameters allow a file descriptor open to the terminal to be passed. pg_upgrade support has also been added. Discussion: https://postgr.es/m/CA+fd4k7q5o6Nc_AaX6BcYM9yqTbC6_pnH-6nSD=54Zp6NBQTCQ@mail.gmail.com Discussion: https://postgr.es/m/20201202213814.GG20285@momjian.us Author: Masahiko Sawada, me, Stephen Frost https://git.postgresql.org/pg/commitdiff/978f869b992f9fca343e99d6fdb71073c76e869a
Fix return value and const declaration from commit 978f869b99. This fixes the non-OpenSSL compile case. Reported-by: buildfarm member sifaka Backpatch-through: master https://git.postgresql.org/pg/commitdiff/945083b2f7e6c19c8921c655cac6778acb1e3e03
remove uint128 requirement from patch 978f869b99 (CFE). Used char[16] instead. Reported-by: buildfarm member florican Backpatch-through: master https://git.postgresql.org/pg/commitdiff/e35b2bad1a10a8eef9c1ffb563847b9c9df0cfce
fixes docs and missing initdb help option for commit 978f869b99. Reported-by: Erik Rijkers Discussion: https://postgr.es/m/a27e7bb60fc4c4a1fe960f7b055ba822@xs4all.nl Backpatch-through: master https://git.postgresql.org/pg/commitdiff/26d60f2a6c1c93838a49ac4d685652b1bc92f547
fix no-return function call in cipher.c from commit 978f869b99. Reported-by: buildfarm member sifaka Backpatch-through: master https://git.postgresql.org/pg/commitdiff/8e59813e2287db90044537b1093c0869020ffa4a
remove missing reference to crypto test from patch 978f869b99. Reported-by: Tom Lane Discussion: https://postgr.es/m/1205031.1608925990@sss.pgh.pa.us Backpatch-through: master https://git.postgresql.org/pg/commitdiff/f234899353f8998bdbd265125ce4a505a312d910
Add pg_alterckey utility to change the cluster key. This can change the key that encrypts the data encryption keys used for cluster file encryption. Discussion: https://postgr.es/m/20201202213814.GG20285@momjian.us Backpatch-through: master https://git.postgresql.org/pg/commitdiff/62afb42a7f9f533efc6c19f462c3a848fa4ddb63
Allow ssl_passphrase_command to prompt the terminal. Previously the command could not access the terminal for a passphrase. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/300e430c76644b483f70bc833d6251a6a7fb84a6
Fix function call typo in frontend Win32 code, commit 978f869b99. Reported-by: buildfarm member walleye Backpatch-through: master https://git.postgresql.org/pg/commitdiff/7705f8ca03713457bf7cde0b859a1ab45e4f268b
pg_alterckey: adjust doc build and Win32 sleep/open build fails. Fix for commit 62afb42a7f. Reported-by: Tom Lane Discussion: https://postgr.es/m/1252111.1608953815@sss.pgh.pa.us Backpatch-through: master https://git.postgresql.org/pg/commitdiff/82f8c45be551a2940e3cbaf08aa7a521bdac8331
doc: fix SGML markup for pg_alterckey from commit 62afb42a7f. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/3d4843babce087ae8dc734aeec36db084001a0e4
Add scripts for retrieving the cluster file encryption key. Scripts are passphrase, direct, AWS, and two Yubikey ones. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/d7602afa2ef6d8b2018103dccd89e75b4985ac06
pg_alterckey: remove TAP check rules from Makefile. Reported-by: Pavel Stehule, Michael Paquier Discussion: https://postgr.es/m/CAFj8pRBRNo4co5bqCx4BLx1ZZ45Z_T-opPxA+u7SLp7gAtBpNA@mail.gmail.com Backpatch-through: master https://git.postgresql.org/pg/commitdiff/e174a6f1937248886e124cdb68408e727aeea278
auth commands: list specific commands to install in Makefile. Previously I used Makefile functions. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/ba6725df364cd30e068ccd962fa426d9b7b44066
initdb: document that -K requires an argument. Reported-by: "Shinoda, Noriyoshi" Discussion: https://postgr.es/m/TU4PR8401MB1152E92B4D44C81E496D6032EEDB0@TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM Author: "Shinoda, Noriyoshi" Backpatch-through: msater https://git.postgresql.org/pg/commitdiff/ccbe34139b7d9059f4af3405b2411f653cc2ce7c
Noah Misch pushed:
Jeff Davis pushed:
Fix bug #16784 in Disk-based Hash Aggregation. Before processing tuples, agg_refill_hash_table() was setting all pergroup pointers to NULL to signal to advance_aggregates() that it should not attempt to advance groups that had spilled. The problem was that it also set the pergroups for sorted grouping sets to NULL, which caused rescanning to fail. Instead, change agg_refill_hash_table() to only set the pergroups for hashed grouping sets to NULL; and when compiling the expression, pass doSort=false. Reported-by: Alexander Lakhin Discussion: https://postgr.es/m/16784-7ff169bf2c3d1588%40postgresql.org Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/05c0258966b502fae1bd63dcbe74d52f5c6f6948
Stabilize test introduced in 05c02589, per buildfarm. In passing, make the capitalization match the rest of the file. Reported-by: Tom Lane https://git.postgresql.org/pg/commitdiff/fa0fdf0510df1a21f42ac9f232f77a79b8577152
Kyotaro HORIGUCHI sent in another revision of a patch to make the stats collector use shared memory instead of files for intermediate storage.
Kyotaro HORIGUCHI sent in another revision of a patch to fix NaN handling for geometric data types.
Masahiko Sawada sent in a patch to ensure that a schema created when an extension is created is also dropped when the extension is dropped.
Justin Pryzby sent in two revisions of a patch to add zstd compression to pg_dump.
Peter Smith sent in five more revisions of a patch to allow the table-sync worker to use multiple transactions.
Bharath Rupireddy and Michaël Paquier traded patches to fail fast in CTAS/CMV if the relation already exists.
Konstantin Knizhnik and Pavel Stěhule traded patches to add a login event that can be used in an event trigger.
Bharath Rupireddy sent in four more revisions of a patch to use parallel inserts in CTAS.
Zeng Wenjing sent in another revision of a patch to implement global temporary tables.
Amit Langote sent in another revision of a patch to create foreign key triggers in partitioned tables, too, and use same to enforce foreign keys correctly during cross-partition updates.
Amit Langote sent in another revision of a patch to initialize the result relation information lazily.
Thomas Munro sent in another revision of a patch to provide synchronous scatter/gather I/O routines, pg_preadv() and pg_pwritev(), which correspond to preadv() and pwritev() on systems that have those, and use vectored I/O to zero WAL segments.
Peter Geoghegan sent in a patch to instrument heap_compute_xid_horizon_for_tuples().
David Rowley sent in another revision of a patch to reduce contrib build special cases on Windows.
Yugo Nagata sent in another revision of a patch to implement incremental maintenance of materialized views.
Michaël Paquier and Justin Pryzby traded patches to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.
Masahiko Sawada sent in a patch to add a new IndexAM API controlling index vacuum strategies.
Fujii Masao sent in two more revisions of a patch to fix a recovery_conflict_lock deadlock.
Amit Kapila and Kirk Jamison traded patches to make dropping relation buffers more efficient using dlist.
Pavel Stěhule sent in two more revisions of a patch to implement schema variables.
Dmitry Dolgov sent in two more revisions of a patch to use the new subscripting infrastructure for JSONB.
Michaël Paquier sent in a patch to clean up some -I$(libpq_srcdir) in makefiles.
Craig Ringer sent in a patch to add a $node->gdb_backends() method to PostgresNode instances.
Andrey V. Lepikhov sent in another revision of a patch to speed up COPY FROM in the case tables with foreign partitions by implementing some new methods in the FDW API: BeginForeignCopy, EndForeignCopy, and ExecForeignCopy.
Justin Pryzby sent in another revision of a patch to make pg_ls_*() show directories and shared filesets.
Simon Riggs sent in a patch to add a boolean transaction_cleanup GUC, defaulting to off, which is intended to replace uses of DISCARD ALL in things like connection poolers.
Ajin Cherian sent in another revision of a patch to implement logical decoding of two-phase transactions.
Fujii Masao and Bharath Rupireddy traded patches to intended to fix a bug that manifested as connection leaks from the postgres_fdw when the associated user mapping is dropped by closing invalid connections either in pgfdw_inval_callback() when they are not in a transaction, or in pgfdw_xact_callback(), which gets called at the end of every act once registered, in the current session.
Li Japin sent in a patch to document the streaming replication protocol better.
Thomas Munro sent in another revision of a patch to implement prefetch for WALs.
Bharath Rupireddy sent in another revision of a patch to add new table AMs for multi- and single inserts.
Kyotaro HORIGUCHI sent in another revision of a patch to make it possible to change table persistence without a heap rewrite, and add a new command: ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED.
Kyotaro HORIGUCHI sent in a patch to pg_waldump that limits the number of lines shown at the start.
Masahiro Ikeda sent in another revision of a patch to add WAL write/fsync statistics to the pg_stat_wal view.
Nikita Glukhov sent in another revision of a patch to add SQL/JSON functions.
Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE.
Dilip Kumar and Andrey Borodin traded patches to implement custom table compression methods.
Dmitry Dolgov sent in another revision of a patch to prevent jumbling of every element in ArrayExpr.
Andrey Borodin sent in another revision of a patch to reorganize the pglz compression code to make it more efficient. This gained a speedup of about 1.4x.
Julien Rouhaud sent in another revision of a patch to add a boolean column, toplevel, to pg_stat_statements.
Fabien COELHO sent in a patch to add table access method as an option to pgbench.
Justin Pryzby sent in another revision of a patch to add a pg_upgrade test for binary compatibility of core data types.
Alexander Korotkov sent in a patch to fix bugs in comparison functions for multirange_bsearch_match(), implement operators for checking if the range contains a multirange, improve the signatures of internal multirange functions by adding const qualifies where applicable and replacing the multirange typecache argument with range typecache argument, adding GiST indexes for multiranges, and adding support for multirange matching to the existing range GiST indexes.
Justin Pryzby sent in another revision of a patch to fix typos and do some wordsmithing for the latest documentation.
Like last Christmas, Zalando's Postgres as a Service team is closing the year 2020 with a new release of the Postgres Operator for Kubernetes. Is this a tradition now? Lets see what we have for 'ya.
Postgres 13 and in-place major version upgradeThis release includes support for the newest major version of PostgreSQL - Spilo 13 with Patroni 2.0 . With this new release and the new Spilo image we trial easy in place upgrade, currently manual execution. The upgrade has become a lot easier now. It can be initiated via changing the version number in the PostgreSQL cluster manifest and requires manually running an upgrade script on the master instance after pods are rotated. Not fully automatic yet, but still a lot faster than cloning existing clusters.
K8s-native volume resize via PVCsSo far, online volume resize (without failover) was only supported on AWS and required privileged containers to update the file system. With the new storage_resize_mode option, users can choose to update only the PersistentVolumeClaims and let Kubernetes handle the resize propagation to volumes. This is the new default and you can disable the spilo_privileged option if you still use it.
Delete protection for Postgres clustersTo avoid easy deletes of Postgres manifests which can have a big impact despite easy but timely restore from e.g. AWS S3 you can now enable delete protection. This safeguards you from unintentional deletions of the manifests. The delete is only executed if the manifest is marked for delete via annotations. Ideally you supplement this on K8s admission controller level.
Connection pooler for replicasThe connection pooler can now be enabled via the Postgres manifest for replica and master services alike, so that you can also easily scale your read workload to larger application connection counts.
More features and improvementsMany more improvements have been made since the last release in May. We are still very happy to see a lot of contributions coming from our developer and Postgres community. Among others it brought us:
Furthermore, our team provided the following additions:
Have a closer look on our detailed changelog and the docs. There are a few breaking changes to allow major version upgrades that will e.g. change your WAL backup path.
Enjoy this new release. We are happy for your feedback and further contribution!
Stay healthy!
Pgpool Global Development Group is pleased to announce the availability of Pgpool-II 4.2.1.
Pgpool-II is a tool to add useful features to PostgreSQL, including:
For more information, please see the website.
A critical issue of watchdog has been found in Pgpool-II 4.2.0. All Pgpool-II 4.2.0 users are strongly encouraged to upgrade to 4.2.1. This issue does not affect version 4.1.x and earlier versions.
You can download it from here.
Please take a look at release notes.
FOSDEM PGDay 2021,will be held on line, on Feb 6-7, 2021. https://fosdem.org/2021/
The Code of Conduct Committee welcomes new member Michael Goldberg https://www.postgresql.org/about/policies/coc_committee/
Person of the week: https://postgresql.life/post/keith_fiske/
PostgreSQL Product NewsPoWA 4.1, a performance tool for PostgreSQL, released. https://github.com/powa-team/powa-archivist/releases/tag/REL_4_1_0
Database .NET v31.5, a multi-database management tool, now with support for PostgreSQL, released. https://fishcodelib.com/Database.htm
pgAdmin4 4.29, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_29.html
PostgreSQL Jobs for Decemberhttp://archives.postgresql.org/pgsql-jobs/2020-12/
PostgreSQL in the NewsPlanet PostgreSQL: http://planet.postgresql.org/
PostgreSQL Weekly News is brought to you this week by David Fetter
Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.
Applied PatchesMichaël Paquier pushed:
Add some checkpoint/restartpoint status to ps display. This is done for end-of-recovery and shutdown checkpoints/restartpoints (end-of-recovery restartpoints don't exist) rather than all types of checkpoints, in cases where it may not be possible to rely on pg_stat_activity to get a status from the startup or checkpointer processes. For example, at the end of a crash recovery, this is useful to know if a checkpoint is running in the startup process, while previously the ps display may only show some information about "recovering" something, that can be confusing while a checkpoint runs. Author: Justin Pryzby Reviewed-by: Nathan Bossart, Kirk Jamison, Fujii Masao, Michael Paquier Discussion: https://postgr.es/m/20200818225238.GP17022@telsasoft.com https://git.postgresql.org/pg/commitdiff/df9274adf3096feafbbde2562311c8ab80405267
Improve some code around cryptohash functions. This adjusts some code related to recent changes for cryptohash functions: - Add a variable in md5.h to track down the size of a computed result, moved from pgcrypto. Note that pg_md5_hash() assumed a result of this size already. - Call explicit_bzero() on the hashed data when freeing the context for fallback implementations. For MD5, particularly, it would be annoying to leave some non-zeroed data around.
Clean up some code related to recent changes of uuid-ossp. .gitignore still included md5.c and a comment was incorrect. Discussion: https://postgr.es/m/X9HXKTgrvJvYO7Oh@paquier.xyz https://git.postgresql.org/pg/commitdiff/9b584953e7bf91e342af87ef44606acd6206cd1c
doc: Fix explanation related to pg_shmem_allocations. Offsets are shown as NULL only for anonymous allocations. Author: Benoit Lobréau Reviewed-by: Kyotaro Horiguchi Discussion: https://postgr.es/m/CAPE8EZ5Lnoyqoz7aZpvQM0E8sW+hw+k6G2NULe+m4arFRrA1aA@mail.gmail.com Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/bce641a2af7199f07ec2b0a84f8fec0f26b2adc0
Jeff Davis pushed:
Tom Lane pushed:
Improve hash_create()'s API for some added robustness. Invent a new flag bit HASH_STRINGS to specify C-string hashing, which was formerly the default; and add assertions insisting that exactly one of the bits HASH_STRINGS, HASH_BLOBS, and HASH_FUNCTION be set. This is in hopes of preventing recurrences of the type of oversight fixed in commit a1b8aa1e4 (i.e., mistakenly omitting HASH_BLOBS). Also, when HASH_STRINGS is specified, insist that the keysize be more than 8 bytes. This is a heuristic, but it should catch accidental use of HASH_STRINGS for integer or pointer keys. (Nearly all existing use-cases set the keysize to NAMEDATALEN or more, so there's little reason to think this restriction should be problematic.) Tweak hash_create() to insist that the HASH_ELEM flag be set, and remove the defaults it had for keysize and entrysize. Since those defaults were undocumented and basically useless, no callers omitted HASH_ELEM anyway. Also, remove memset's zeroing the HASHCTL parameter struct from those callers that had one. This has never been really necessary, and while it wasn't a bad coding convention it was confusing that some callers did it and some did not. We might as well save a few cycles by standardizing on "not". Also improve the documentation for hash_create(). In passing, improve reinit.c's usage of a hash table by storing the key as a binary Oid rather than a string; and, since that's a temporary hash table, allocate it in CurrentMemoryContext for neatness. Discussion: https://postgr.es/m/590625.1607878171@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b3817f5f774663d55931dd4fab9c5a94a15ae7ab
Fix varchar_2.out to match reality in cs_CZ locale. Seems to be a copy-and-pasteo in c06d6aa4c. Per buildfarm. https://git.postgresql.org/pg/commitdiff/88e014c149cc396fb218b08eda17c47d5b33e94f
Avoid memcpy() with same source and destination during relmapper init. A narrow reading of the C standard says that memcpy(x,x,n) is undefined, although it's hard to envision an implementation that would really misbehave. However, analysis tools such as valgrind might whine about this; accordingly, let's band-aid relmapper.c to not do it. See also 5b630501e, d3f4e8a8a, ad7b48ea0, and other similar fixes. Apparently, none of those folk tried valgrinding initdb? This has been like this for long enough that I'm surprised it hasn't been reported before. Back-patch, just in case anybody wants to use a back branch on a platform that complains about this; we back-patched those earlier fixes too. Discussion: https://postgr.es/m/161790.1608310142@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/53d4f5fef04653fc495ae385a9c2f78c77e5c7d9
Add a couple of missed .gitignore entries. Any subdirectory that's ignoring /output_iso/ should also ignore /tmp_check_iso/, which could be left behind by a failed pg_isolation_regress_check run. I think these have been wrong for awhile, but it doesn't seem important to fix in back branches. https://git.postgresql.org/pg/commitdiff/8afca702ecbf0730b6175afda0cecbbf0a1967e7
Remove now-useless ALWAYS_SUBDIRS entry in src/test/Makefile. Commit 257836a75 added the "locale" subdirectory to SUBDIRS, but neglected to remove it from ALWAYS_SUBDIRS. This oversight had no functional effect because the filter-out function would remove it anyway. Still, it's confusing to readers to list a subdirectory in both places, especially because it makes the associated comment into a partial lie. https://git.postgresql.org/pg/commitdiff/08b01d4dd982b491a2f9641804b368185b8f4c53
Avoid memcpy() with same source and destination in pgstat_recv_replslot. Same type of issue as in commit 53d4f5fef and earlier fixes; also found by apparently-more-picky-than-the-buildfarm valgrind testing. This one is an oversight in commit 986816750. Since that's new in HEAD, no need for a back-patch. https://git.postgresql.org/pg/commitdiff/ed6329cfa975a13c58c44676464585cda5d317b3
Doc: improve description of pgbench script weights. Point out the workaround to be used if you want to write a script file name that includes "@". Clean up the text a little. Fabien Coelho, additional wordsmithing by me Discussion: https://postgr.es/m/1c4e81550d214741827a03292222db8d@G08CNEXMBPEKD06.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/1990ce186ee64f24dcff885a87cea04e3dfd8166
Peter Eisentraut pushed:
Tomáš Vondra pushed:
Álvaro Herrera pushed:
Peter Geoghegan pushed:
Bruce Momjian pushed:
Fujii Masao pushed:
Revert "Get rid of the dedicated latch for signaling the startup process". Revert ac22929a26, as well as the followup fix 113d3591b8. Because it broke the assumption that the startup process waiting for the recovery conflict on buffer pin should be waken up only by buffer unpin or the timeout enabled in ResolveRecoveryConflictWithBufferPin(). It caused, for example, SIGHUP signal handler or walreceiver process to wake that startup process up unnecessarily frequently. Additionally, add the comments about why that dedicated latch that the reverted patch tried to get rid of should not be removed. Thanks to Kyotaro Horiguchi for the discussion. Author: Fujii Masao Discussion: https://postgr.es/m/d8c0c608-021b-3c73-fffd-3240829ee986@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/00f690a239932e477f25120d19b08aacdc30deb7
pg_stat_statements: Track time at which all statistics were last reset. This commit adds "stats_reset" column into the pg_stat_statements_info view. This column indicates the time at which all statistics in the pg_stat_statements view were last reset. Per discussion, this commit also changes pg_stat_statements_info code so that "dealloc" column is reset at the same time as "stats_reset" is reset, i.e., whenever all pg_stat_statements entries are removed, for the sake of consistency. Previously "dealloc" was reset only when pg_stat_statements_reset(0, 0, 0) is called and was not reset when pg_stat_statements_reset() with non-zero value argument discards all entries. This was confusing. Author: Naoki Nakamichi, Yuki Seino Reviewed-by: Yuki Seino, Kyotaro Horiguchi, Li Japin, Fujii Masao Discussion: https://postgr.es/m/c102cf3180d0ee73c1c5a0f7f8558322@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/2e0fedf0362cc964c4dae42258455b6391051e70
Amit Kapila pushed:
Alexander Korotkov pushed:
Multirange datatypes. Multiranges are basically sorted arrays of non-overlapping ranges with set-theoretic operations defined over them. Since v14, each range type automatically gets a corresponding multirange datatype. There are both manual and automatic mechanisms for naming multirange types. Once can specify multirange type name using multirange_type_name attribute in CREATE TYPE. Otherwise, a multirange type name is generated automatically. If the range type name contains "range" then we change that to "multirange". Otherwise, we add "_multirange" to the end. Implementation of multiranges comes with a space-efficient internal representation format, which evades extra paddings and duplicated storage of oids. Altogether this format allows fetching a particular range by its index in O(n). Statistic gathering and selectivity estimation are implemented for multiranges. For this purpose, stored multirange is approximated as union range without gaps. This field will likely need improvements in the future. Catversion is bumped. Discussion: https://postgr.es/m/CALNJ-vSUpQ_Y%3DjXvTxt1VYFztaBSsWVXeF1y6gTYQ4bOiWDLgQ%40mail.gmail.com Discussion: https://postgr.es/m/a0b8026459d1e6167933be2104a6174e7d40d0ab.camel%40j-davis.com#fe7218c83b08068bfffb0c5293eceda0 Author: Paul Jungwirth, revised by me Reviewed-by: David Fetter, Corey Huinker, Jeff Davis, Pavel Stehule Reviewed-by: Alvaro Herrera, Tom Lane, Isaac Morland, David G. Johnston Reviewed-by: Zhihong Yu, Alexander Korotkov https://git.postgresql.org/pg/commitdiff/6df7a9698bb036610c1e8c6d375e1be38cb26d5f
Fixes for pg_dump.c regarding multiranges. This commit fixes two wrong version number checks and one wrong check for null. https://git.postgresql.org/pg/commitdiff/8344d72cccae699e13884a5705b91dc1c4747c03
Fix compiler warning introduced in 6df7a9698b. https://git.postgresql.org/pg/commitdiff/11072e86939d2f5ca0a97c709b46f29ea8b57590
Michaël Paquier sent in another revision of a patch to introduce SHA1 cryptohash infrastructure.
Andrey Borodin sent in another revision of a patch to use a shared lock in GetMultiXactIdMembers for offsets and members, make the MultiXact local cache size configurable, add a condition variable to wait for next MultXact offset in a corner case, and add GUCs to tune MultiXact SLRUs.
Kyotaro HORIGUCHI sent in a patch to ensure that invalid-page-header error messages are cancelled only when appropriate, i.e. only on non-standby nodes.
Andrey V. Lepikhov sent in another revision of a patch to move the multi-insert decision logic into the executor, and use same along with three new methods to the FDW API: BeginForeignCopy, ExecForeignCopy, and EndForeignCopy, to speed up COPY FROM as applied to tables with remote partitions.
Fujii Masao sent in two more revisions of a patch to add a log_recovery_conflict_waits GUC that does what it says on the label.
Tom Lane sent in two revisions of a patch to fix the way PL/pgsql does assignments.
Bharath Rupireddy and Hou Zhijie traded patches to make it possible to use parallel INSERTs in CTAS.
Nathan Bossart and Kyotaro HORIGUCHI traded patches to intended to fix a bug that manifested as archive status ".ready" files may be created too early.
Kirk Jamison sent in another revision of a patch to make dropping relation buffers more efficient using dlist.
Amit Kapila sent in three more revisions of a patch to implement logical decoding of two-phase transactions.
Yuzuko Hosoya sent in another revision of a patch to make autoanalyze work better on partitioned tables.
Atsushi Torikoshi sent in a patch to add a wait_start colunmn to the pg_locks view.
Iwata Aya sent in another revision of a patch to add tracing to libpq.
Laurenz Albe sent in two more revisions of a patch to add session statistics to pg_stat_database.
Peter Eisentraut sent in another revision of a patch to change return type of EXTRACT to numeric.
Fabrízio de Royes Mello sent in another revision of a patch to implement minimal logical decoding on standbys.
Justin Pryzby sent in two more revisions of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.
Jeff Davis sent in a patch to enforce ASCII restore point names.
Bharath Rupireddy sent in a patch intended to fix a bug that manifested as - cached connection leaks from the postgres_fdw if the associated user mapping is dropped by moving the place to close all the invalid connections to pgfdw_xact_callback().
Peter Smith sent in two more revisions of a patch to make it possible to use multiple workers on tablesync.
Bruce Momjian and Neil Chen traded patches to implement key management.
Tomáš Vondra sent in another revision of a patch to consider unsorted paths in generate_useful_gather_paths, check parallel safety in generate_useful_gather_paths, disallow SRFs when considering sorts below Gather Merge, avoid searching for volatile expr in find_em_expr_usable_for_sorting_rel, and improve find_em_expr_usable_for_sorting_rel comment by clarifying the relationship between find_em_expr_usable_for_sorting_rel and prepare_sort_from_pathkeys, i.e. what restrictions need to be shared between those two places.
Laurenz Albe sent in another revision of a patch to fix a large violation of POLA in psql's \e family of functions, namely that if the editor was exited before any changes were made, the previous query would be executed. The fix is to clear the query buffer.
Heikki Linnakangas sent in another revision of a patch to make resowners more easily extensible by using a single array and hash instead of one for each object kind.
Justin Pryzby sent in another revision of a patch to pg_upgrade/test.sh which makes some changes needed to allow testing upgrade from v11.
Heikki Linnakangas sent in a patch to do COPY FROM encoding conversion/verification in larger chunks.
Fujii Masao sent in three revisions of a patch to intended to fix a bug that manifested as failure to detect a deadlock between backend and recovery by adding a check for that situation.
Michaël Paquier sent in two revisions of a patch to refactor the HMAC implementations.
Konstantin Knizhnik sent in two more revisions of a patch to add a way to fire triggers on login.
David Fetter sent in a patch to implement \gsetenv, analogous to \gset, only for environment variables in psql.
Önder Kalacı sent in another revision of a patch to implement row filtering for logical replication.
Denis Smirnov and Konstantin Knizhnik traded patches to implement compression for libpq.
Peter Eisentraut sent in a patch to hange seconds argument of make_*() functions to numeric.
Fujii Masao sent in a patch to intended to fix a bug that manifested as STANDBY_LOCK_TIMEOUT may not interrupt ProcWaitForSignal() by ensuring that StandbyLockTimeoutHandler() sets a latch.
Bharath Rupireddy sent in another revision of a patch to fail fast in CTAS/CMV if the relation already exists.
Gilles Darold sent in another revision of a patch to fix a race condition in DDL where a function's namespace can disappear while leaving the function in place.
Michaël Paquier sent in a patch to remove duplicate checks of ASCII-ness and make the one left more efficient.
Justin Pryzby sent in two more revisions of a patch to allow psql's \dti+ to look at pg_toast.*.
David CARLIER sent in a patch to implements SPIN_LOCK on ARM.
Michaël Paquier and Heikki Linnakangas traded patches to intended to fix a bug that manifested as incorrect allocation handling for cryptohash functions with OpenSSL.
Tom Lane and Alexander Lakhin traded patches to show examples of using Valgrind for TAP tests.
Craig Ringer sent in a patch to identify LWLocks in tracepoints by fixing a bogus tracepoint where an lwlock__acquire event would be fired from LWLockWaitForVar despite that function never actually acquiring the lock, adding the tranche id and lock pointer for each trace hit, adding a single-path tracepoint for all lock acquires and releases, and adding some comments on LWLock tranches to clarify matters for future readers.
Konstantin Knizhnik sent in a patch to reimplement BufferAlloc, removing double locking in the process.
Pavel Stěhule sent in another revision of a patch to implement schema variables.
Tom Lane sent in a patch to fix a corner case bug in jsonb_concat().
Thomas Munro sent in two revisions of a patch to add pg_preadv() and pg_pwritev(), which provide synchronous scatter/gather I/O routines, and then use same to use vectored I/O to zero WAL segments.
Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes.
PostgreSQL Workload Analyzer is a performance tool for PostgreSQL 9.4 and newer allowing to collect, aggregate and purge statistics on multiple PostgreSQL instances from various stats extensions.
Version 4.1 has just been released. It brings support for PostgreSQL 13, including all new fields in pg_stat_statements 1.8 (planning and WAL usage statistics).
It also add support for pg_stat_kcache version 2.2, which adds better support for nested queries and planning statistics (for PostgreSQL 13 and later).
You can see the full list of changes for each related project release:
Thanks to Andriy Bartash, github user mikecaat and all other contributors!
On the advice of the PostgreSQL Project’s legal counsel, the PostgreSQL Project has updated the PostgreSQL Trademark Policy covering the “Postgres” and “PostgreSQL” word marks and the “Slonik” elephant graphical trademark. You can view the updates here:
In order to support this change and to provide as much flexibility in the use of the marks to the community as possible, we have also introduced a new set of guidelines for Recognised User Groups, similar to those already in place for Non Profit organisations and Conferences:
As is the case with many Open Source projects, it is important that the PostgreSQL Project’s brand is used in alignment with the goals and best interests of the community. The PostgreSQL Core Team and the PostgreSQL Community Association of Canada, who administer the marks, have worked closely with our legal counsel to ensure that the policy is both open and fair when using the PostgreSQL brand.
If you have any questions about these changes, please feel free to reach out to the PostgreSQL Community Association of Canada (board@lists.postgres.ca).
PostgreSQL Person of the Week: https://postgresql.life/post/carole_arnaud/
PostgreSQL Jobs for Decemberhttp://archives.postgresql.org/pgsql-jobs/2020-12/
PostgreSQL in the NewsPlanet PostgreSQL: http://planet.postgresql.org/
PostgreSQL Weekly News is brought to you this week by David Fetter
Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.
Applied PatchesMichaël Paquier pushed:
Fix fd leak in pg_verifybackup. An error code path newly-introduced by 87ae969 forgot to close a file descriptor when verifying a file's checksum. Per report from Coverity, via Tom Lane. https://git.postgresql.org/pg/commitdiff/51c388987734cb318c8fa875f382ca75a34d0010
Avoid using tuple from syscache for update of pg_database.datfrozenxid. pg_database.datfrozenxid gets updated using an in-place update at the end of vacuum or autovacuum. Since 96cdeae, as pg_database has a toast relation, it is possible for a pg_database tuple to have toast values if there is a large set of ACLs in place. In such a case, the in-place update would fail because of the flattening of the toast values done for the catcache entry fetched. Instead of using a copy from the catcache, this changes the logic to fetch the copy of the tuple by directly scanning pg_database. Per the lack of complaints on the matter, no backpatch is done. Note that before 96cdeae, attempting to insert such a tuple to pg_database would cause a "row is too big" error, so the end-of-vacuum problem was not reachable. Author: Ashwin Agrawal, Junfeng Yang Discussion: https://postgr.es/m/DM5PR0501MB38800D9E4605BCA72DD35557CCE10@DM5PR0501MB3880.namprd05.prod.outlook.com https://git.postgresql.org/pg/commitdiff/947789f1f5fb61daf663f26325cbe7cad8197d58
pgcrypto: Detect errors with EVP calls from OpenSSL. The following routines are called within pgcrypto when handling digests but there were no checks for failures: - EVP_MD_CTX_size (can fail with -1 as of 3.0.0) - EVP_MD_CTX_block_size (can fail with -1 as of 3.0.0) - EVP_DigestInit_ex - EVP_DigestUpdate - EVP_DigestFinal_ex A set of elog(ERROR) is added by this commit to detect such failures, that should never happen except in the event of a processing failure internal to OpenSSL. Note that it would be possible to use ERR_reason_error_string() to get more context about such errors, but these refer mainly to the internals of OpenSSL, so it is not really obvious how useful that would be. This is left out for simplicity. Per report from Coverity. Thanks to Tom Lane for the discussion. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/28d1601ad988790c3c53d7ffd24ef6d2366d4457
Simplify code for getting a unicode codepoint's canonical class. Three places of unicode_norm.c use a similar logic for getting the combining class from a codepoint. Commit 2991ac5 has added the function get_canonical_class() for this purpose, but it was only called by the backend. This commit refactors the code to use this function in all the places where the combining class is retrieved from a given codepoint. Author: John Naylor Discussion: https://postgr.es/m/CAFBsxsHUV7s7YrOm6hFz-Jq8Sc7K_yxTkfNZxsDV-DuM-k-gwg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/16c302f51235eaec05a1f85a11c1df04ef3a6785
Refactor MD5 implementations according to new cryptohash infrastructure. This commit heavily reorganizes the MD5 implementations that exist in the tree in various aspects. First, MD5 is added to the list of options available in cryptohash.c and cryptohash_openssl.c. This means that if building with OpenSSL, EVP is used for MD5 instead of the fallback implementation that Postgres had for ages. With the recent refactoring work for cryptohash functions, this change is straight-forward. If not building with OpenSSL, a fallback implementation internal to src/common/ is used. Second, this reduces the number of MD5 implementations present in the tree from two to one, by moving the KAME implementation from pgcrypto to src/common/, and by removing the implementation that existed in src/common/. KAME was already structured with an init/update/final set of routines by pgcrypto (see original pgcrypto/md5.h) for compatibility with OpenSSL, so moving it to src/common/ has proved to be a straight-forward move, requiring no actual manipulation of the internals of each routine. Some benchmarking has not shown any performance gap between both implementations. Similarly to the fallback implementation used for SHA2, the fallback implementation of MD5 is moved to src/common/md5.c with an internal header called md5_int.h for the init, update and final routines. This gets then consumed by cryptohash.c. The original routines used for MD5-hashed passwords are moved to a separate file called md5_common.c, also in src/common/, aimed at being shared between all MD5 implementations as utility routines to keep compatibility with any code relying on them. Like the SHA2 changes, this commit had its round of tests on both Linux and Windows, across all versions of OpenSSL supported on HEAD, with and even without OpenSSL. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/20201106073434.GA4961@paquier.xyz https://git.postgresql.org/pg/commitdiff/b67b57a966af0c4a9547ac6fff334d3c256d9c2a
Fix compilation of uuid-ossp. This module had a dependency on pgcrypto's md5.c that got removed by b67b57a. Instead of the code from pgcrypto, this code can just use the new cryptohash routines for MD5 as a drop-in replacement, so let's just do this switch. This has also the merit to simplify a bit the compilation of uuid-ossp. This requires --with-uuid to be reproduced, and I have used e2fs as a way to reproduce the failure, then test this commit. Per reports from buildfarm members longfin, florican and sifaka. Discussion: https://postgr.es/m/X9GToVd3QmWeNvj8@paquier.xyz https://git.postgresql.org/pg/commitdiff/525e60b7429925d09fce1b5aa0bc2f23cfe6dd18
Tom Lane pushed:
pg_dump: Reorganize dumpBaseType(). Along the same lines as ed2c7f65b and daa9fe8a5, reduce code duplication by having just one copy of the parts of the query that are the same across all server versions; and make the conditionals control the smallest possible amount of code. This is in preparation for adding another dumpable field to pg_type. https://git.postgresql.org/pg/commitdiff/04732962462ba99cf8f8bcf6ac83932867cc96a8
Add a couple of regression test cases related to array subscripting. Exercise some error cases that were never reached in the existing regression tests. This is partly for code-coverage reasons, and partly to memorialize the current behavior in advance of planned changes for generic subscripting. Also, I noticed that type_sanity's check to verify that all standard types have array types was never extended when we added arrays for all system catalog rowtypes (f7f70d5e2), nor when we added arrays over domain types (c12d570fa). So do that. Also, since the query's expected output isn't empty, it seems like a good idea to add an ORDER BY to make sure the result stays stable. https://git.postgresql.org/pg/commitdiff/0a665bbc43c5a678331fb1b1f44274500eba6563
Doc: explain that the string types can't store \0 (ASCII NUL). This restriction was mentioned in connection with string literals, but it wasn't made clear that it's a general restriction not just a syntactic limitation in query strings. Per unsigned documentation comment. Discussion: https://postgr.es/m/160720552914.710.16625261471128631268@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/9a2641911aeaa7f6523dc2a465088051d4c85901
Doc: clarify that CREATE TABLE discards redundant unique constraints. The SQL standard says that redundant unique constraints are disallowed, but we long ago decided that throwing an error would be too user-unfriendly, so we just drop redundant ones. The docs weren't very clear about that though, as this behavior was only explained for PRIMARY KEY vs UNIQUE, not UNIQUE vs UNIQUE. While here, I couldn't resist doing some copy-editing and markup-fixing on the adjacent text about INCLUDE options. Per bug #16767 from Matthias vd Meent. Discussion: https://postgr.es/m/16767-1714a2056ca516d0@postgresql.org https://git.postgresql.org/pg/commitdiff/f2a69b352de1dffc534c4835010e736018aa94de
Remove operator_precedence_warning. This GUC was always intended as a temporary solution to help with finding 9.4-to-9.5 migration issues. Now that all pre-9.5 branches are out of support, and 9.5 will be too before v14 is released, it seems like it's okay to drop it. Doing so allows removal of several hundred lines of poorly-tested code in parse_expr.c, which have been a fertile source of bugs when people did use this. Discussion: https://postgr.es/m/2234320.1607117945@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a676386b58bf7cd2df81baa43eb1713d3a2ec055
Teach contain_leaked_vars that assignment SubscriptingRefs are leaky. array_get_element and array_get_slice qualify as leakproof, since they will silently return NULL for bogus subscripts. But array_set_element and array_set_slice throw errors for such cases, making them clearly not leakproof. contain_leaked_vars was evidently written with only the former case in mind, as it gave the wrong answer for assignment SubscriptingRefs (nee ArrayRefs). This would be a live security bug, were it not that assignment SubscriptingRefs can only occur in INSERT and UPDATE target lists, while we only care about leakproofness for qual expressions; so the wrong answer can't occur in practice. Still, that's a rather shaky answer for a security-related question; and maybe in future somebody will want to ask about leakproofness of a tlist. So it seems wise to fix and even back-patch this correction. (We would need some change here anyway for the upcoming generic-subscripting patch, since extensions might make different tradeoffs about whether to throw errors. Commit 558d77f20 attempted to lay groundwork for that by asking check_functions_in_node whether a SubscriptingRef contains leaky functions; but that idea fails now that the implementation methods of a SubscriptingRef are not SQL-visible functions that could be marked leakproof or not.) Back-patch to 9.6. While 9.5 has the same issue, the code's a bit different. It seems quite unlikely that we'd introduce any actual bug in the short time 9.5 has left to live, so the work/risk/reward balance isn't attractive for changing 9.5. Discussion: https://postgr.es/m/3143742.1607368115@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/62ee70331336161cb44733b6c3e0811696d962aa
Support subscripting of arbitrary types, not only arrays. This patch generalizes the subscripting infrastructure so that any data type can be subscripted, if it provides a handler function to define what that means. Traditional variable-length (varlena) arrays all use array_subscript_handler(), while the existing fixed-length types that support subscripting use raw_array_subscript_handler(). It's expected that other types that want to use subscripting notation will define their own handlers. (This patch provides no such new features, though; it only lays the foundation for them.) To do this, move the parser's semantic processing of subscripts (including coercion to whatever data type is required) into a method callback supplied by the handler. On the execution side, replace the ExecEvalSubscriptingRef* layer of functions with direct calls to callback-supplied execution routines. (Thus, essentially no new run-time overhead should be caused by this patch. Indeed, there is room to remove some overhead by supplying specialized execution routines. This patch does a little bit in that line, but more could be done.) Additional work is required here and there to remove formerly hard-wired assumptions about the result type, collation, etc of a SubscriptingRef expression node; and to remove assumptions that the subscript values must be integers. One useful side-effect of this is that we now have a less squishy mechanism for identifying whether a data type is a "true" array: instead of wiring in weird rules about typlen, we can look to see if pg_type.typsubscript == F_ARRAY_SUBSCRIPT_HANDLER. For this to be bulletproof, we have to forbid user-defined types from using that handler directly; but there seems no good reason for them to do so. This patch also removes assumptions that the number of subscripts is limited to MAXDIM (6), or indeed has any hard-wired limit. That limit still applies to types handled by array_subscript_handler or raw_array_subscript_handler, but to discourage other dependencies on this constant, I've moved it from c.h to utils/array.h. Dmitry Dolgov, reviewed at various times by Tom Lane, Arthur Zakirov, Peter Eisentraut, Pavel Stehule Discussion: https://postgr.es/m/CA+q6zcVDuGBv=M0FqBYX8DPebS3F_0KQ6OVFobGJPM507_SZ_w@mail.gmail.com Discussion: https://postgr.es/m/CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c7aba7c14efdbd9fc1bb44b4cb83bedee0c6a6fc
Allow ALTER TYPE to update an existing type's typsubscript value. This is essential if we'd like to allow existing extension data types to support subscripting in future, since dropping and recreating the type isn't a practical thing for an extension upgrade script, and direct manipulation of pg_type isn't a great answer either. There was some discussion about also allowing alteration of typelem, but it's less clear whether that's a good idea or not, so for now I forebore. Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8c15a297452e970d68529ee2ce6bd94d84598409
Provide an error cursor for "can't subscript" error messages. Commit c7aba7c14 didn't add this, but after more fooling with the feature I feel that it'd be useful. To make this possible, refactor getSubscriptingRoutines() so that the caller is responsible for throwing any error. (In clauses.c, I just chose to make the most conservative assumption rather than throwing an error. We don't expect failures there anyway really, so the code space for an error message would be a poor investment.) https://git.postgresql.org/pg/commitdiff/653aa603f501aa6e4865105a928cd13082ee7152
Allow subscripting of hstore values. This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's not a fundamentally new ability. However, there may be some value in the code as sample code, since it shows what's basically the minimum-complexity way to implement subscripting when one needn't consider nested container objects. Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0ec5f7e78231a621a1d96c4bfedc4a1849a6c6cc
Heikki Linnakangas pushed:
Andres Freund pushed:
jit: configure: Explicitly reference 'native' component. Until recently 'native' was implicitly included via 'orcjit', but a change included in LLVM 11 (not yet released) removed a number of such indirect component references. Reported-By: Fabien COELHO coelho@cri.ensmp.fr Reported-By: Andres Freund andres@anarazel.de Reported-By: Thomas Munro thomas.munro@gmail.com Author: Andres Freund andres@anarazel.de Discussion: https://postgr.es/m/20201201064949.mex6kvi2kygby3ni@alap3.anarazel.de Backpatch: 11-, where jit support was added https://git.postgresql.org/pg/commitdiff/9543f0861b1d9b566be88edae21f24fb1377f45c
jit: Correct parameter type for generated expression evaluation functions. clang only uses the 'i1' type for scalar booleans, not for pointers to booleans (as the pointer might be pointing into a larger memory allocation). Therefore a pointer-to-bool needs to the "storage" boolean. There's no known case of wrong code generation due to this, but it seems quite possible that it could cause problems (see e.g. 72559438f92). Author: Andres Freund Discussion: https://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de Backpatch: 11-, where jit support was added https://git.postgresql.org/pg/commitdiff/5da871bfa1ba41768ecd7786293d9b81dcf1b667
jit: Reference function pointer types via llvmjit_types.c. It is error prone (see 5da871bfa1b) and verbose to manually create function types. Add a helper that can reference a function pointer type via llvmjit_types.c and and convert existing instances of manual creation. Author: Andres Freund andres@anarazel.de Reviewed-By: Tom Lane tgl@sss.pgh.pa.us Discussion: https://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/df99ddc70b971a991c5111a33f2f08bd7945d5c2
Fujii Masao pushed:
Bump catversion for pg_stat_wal changes. Oversight in 01469241b2. Reported-by: Andres Freund Discussion: https://postgr.es/m/20201207185614.zzf63vggm5r4sozg@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/4e43ee88c28b725bb63e27609c1a717138fc7f39
Speed up rechecking if relation needs to be vacuumed or analyze in autovacuum. After autovacuum collects the relations to vacuum or analyze, it rechecks whether each relation still needs to be vacuumed or analyzed before actually doing that. Previously this recheck could be a significant overhead especially when there were a very large number of relations. This was because each recheck forced the statistics to be refreshed, and the refresh of the statistics for a very large number of relations could cause heavy overhead. There was the report that this issue caused autovacuum workers to have gotten “stuck” in a tight loop of table_recheck_autovac() that rechecks whether a relation needs to be vacuumed or analyzed. This commit speeds up the recheck by making autovacuum worker reuse the previously-read statistics for the recheck if possible. Then if that "stale" statistics says that a relation still needs to be vacuumed or analyzed, autovacuum refreshes the statistics and does the recheck again. The benchmark shows that the more relations exist and autovacuum workers are running concurrently, the more this change reduces the autovacuum execution time. For example, when there are 20,000 tables and 10 autovacuum workers are running, the benchmark showed that the change improved the performance of autovacuum more than three times. On the other hand, even when there are only 1000 tables and only a single autovacuum worker is running, the benchmark didn't show any big performance regression by the change. Firstly POC patch was proposed by Jim Nasby. As the result of discussion, we used Tatsuhito Kasahara's version of the patch using the approach suggested by Tom Lane. Reported-by: Jim Nasby Author: Tatsuhito Kasahara Reviewed-by: Masahiko Sawada, Fujii Masao Discussion: https://postgr.es/m/3FC6C2F2-8A47-44C0-B997-28830B5716D0@amazon.com https://git.postgresql.org/pg/commitdiff/e2ac3fed3b1c3281281eb530c220634030cd8084
Dean Rasheed pushed:
Improve estimation of OR clauses using multiple extended statistics. When estimating an OR clause using multiple extended statistics objects, treat the estimates for each set of clauses for each statistics object as independent of one another. The overlap estimates produced for each statistics object do not apply to clauses covered by other statistics objects. Dean Rasheed, reviewed by Tomas Vondra. Discussion: https://postgr.es/m/CAEZATCW=J65GUFm50RcPv-iASnS2mTXQbr=CfBvWRVhFLJ_fWA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/88b0898fe35a5a0325fca21bd4f3ed6dffb364c1
Improve estimation of ANDs under ORs using extended statistics. Formerly, extended statistics only handled clauses that were RestrictInfos. However, the restrictinfo machinery doesn't create sub-AND RestrictInfos for AND clauses underneath OR clauses. Therefore teach extended statistics to handle bare AND clauses, looking for compatible RestrictInfo clauses underneath them. Dean Rasheed, reviewed by Tomas Vondra. Discussion: https://postgr.es/m/CAEZATCW=J65GUFm50RcPv-iASnS2mTXQbr=CfBvWRVhFLJ_fWA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4f5760d4afa9423fe4d38e4cbec48bf5e793e7e5
Peter Eisentraut pushed:
Change get_constraint_index() to use pg_constraint.conindid. It was still using a scan of pg_depend instead of using the conindid column that has been added since. Since it is now just a catalog lookup wrapper and not related to pg_depend, move from pg_depend.c to lsyscache.c. Reviewed-by: Matthias van de Meent boekewurm+postgres@gmail.com Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Reviewed-by: Michael Paquier michael@paquier.xyz Discussion: https://www.postgresql.org/message-id/flat/4688d55c-9a2e-9a5a-d166-5f24fe0bf8db%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/8b069ef5dca97cd737a5fd64c420df3cd61ec1c9
pg_dump: Don't use enums for defining bit mask values. This usage would mean that values of the enum type are potentially not one of the enum values. Use macros instead, like everywhere else. Discussion: https://www.postgresql.org/message-id/14dde730-1d34-260e-fa9d-7664df2d6313@enterprisedb.com https://git.postgresql.org/pg/commitdiff/d2a2808eb444986d2fe716a48e21993329142f3d
Bruce Momjian pushed:
initdb: properly alphabetize getopt_long options in C string. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/39f3a9d2ffcaafeb8ff25e0dbe9f28a657efb48e
initdb: complete getopt_long alphabetization. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/d6abfdf84ef7c01d98d04f486fb467a4aec83804
Noah Misch pushed:
Correct behavior descriptions in comments, and correct a test name. https://git.postgresql.org/pg/commitdiff/73aae4522b35125c4f9a506842a69336ec5d0f1b
Use HASH_BLOBS for xidhash. This caused BufFile errors on buildfarm member sungazer, and SIGSEGV was possible. Conditions for reaching those symptoms were more frequent on big-endian systems. Discussion: https://postgr.es/m/20201129214441.GA691200@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/a1b8aa1e4eec520ed8f11c3d134a7a866358d39a
Bharath Rupireddy sent in a patch to allow parallel mode in REFRESH MATERIALIZED VIEW planning.
James Coleman sent in a patch to allow parallel LATERAL subqueries with LIMIT/OFFSET.
Peter Eisentraut sent in a WIP patch to fix temp-install tests to work with macOS SIP.
Pavel Stěhule sent in another revision of a patch to add a unistr function which evaluates various forms of unicode-escaped characters.
Bruce Momjian sent in two more revisions of a patch to implement key management for PostgreSQL.
Justin Pryzby sent in two more revisions of a patch to pg_dump which causes it to output separate "object" for ALTER TABLE..ATTACH PARTITION.
Peter Eisentraut sent in a patch to convert elog(LOG) calls to ereport() where appropriate.
David Zhang sent in two more revisions of a patch to add table access method as an option to pgbench.
Ajin Cherian and Peter Smith traded patches to add two-phase transactions to logical decoding.
Amit Langote, Kyotaro HORIGUCHI, and Keisuke Kuroda traded patches to fix a bug than manifested as huge memory consumption on partitioned tables with foreign keys.
Dean Rasheed sent in two more revisions of a patch to make OR clauses use extended statistics.
Peter Eisentraut sent in two revisions of a patch to remove gratuitous uses of deprecated SELECT INTO, and clarify status of SELECT INTO on the reference page.
Takayuki Tsunakawa sent in two revisions of a patch to fix a bug that manifested as ALTER TABLE SET LOGGED/UNLOGGED on a partitioned table does nothing silently.
Takamichi Osumi sent in another revision of a patch to add an option to disable WAL logging to speed up data loading.
Julien Rouhaud sent in a patch to add a new COLLATION option to REINDEX, which can be used to filter the list of indexes to rebuild. This is handy for times when system collation gets updated.
Daniel Gustafsson sent in another revision of a patch to support page checksum enable/disable in a running cluster.
Peter Eisentraut sent in a patch to remove unnecessary grammar symbols.
Thomas Munro sent in a WIP patch to use Github Actions for CI.
Bharath Rupireddy sent in three more revisions of a patch to two add new functions: pg_terminate_backend(pid, wait, timeout), which terminates and waits or times out for a given backend, and pg_wait_backend(pid, timeout), which checks the existence of the backend with a given PID and waits or times out until it goes away.
Pavel Borisov sent in two more revisions of a patch to implement covering SPGiST index.
Jeff Davis sent in a patch to ensure that the format is sent along with the type in walsender.
Craig Ringer sent in another revision of a patch to replace CLOBBER_CACHE_ALWAYS with a new GUC, debug_clobber_cache_depth.
Amit Kapila and Peter Smith traded patches to speed up throughput in logical replication's tablesync.
Kirk Jamison sent in two more revisions of a patch to prevent invalidating blocks in smgrextend() during recovery, add a bool param in smgrnblocks() for cached blocks to ensures that we return a reliable value from smgrnblocks, make DropRelFileNodeBuffers() more efficient during recovery by avoiding scanning the whole buffer pool when the relation is small enough, or the the total number of blocks to be invalidated is below the threshold of full scanning, and make DropRelFileNodesAllBuffers() more efficient in recovery by skipping the time-consuming scan of the whole buffer pool during recovery when the relation is small enough, or when the number of blocks to be invalidated is below the full scan threshold.
Tomáš Vondra sent in two more revisions of a patch to make it possible to use extended statistics on expressions.
Nathan Bossart and Michaël Paquier traded patches to add the checkpoint/restartpoint status to ps display.
Laurenz Albe sent in two more revisions of a patch to add session statistics to pg_stat_database.
Julien Rouhaud sent in two revisions of a patch to add a bool toplevel column to pg_stat_statements.
Tom Lane sent in a patch to remove operator_precedence_warning.
Antonin Houska sent in another revision of a patch to clean up orphaned files using undo logs.
Joel Jacobson sen in three revisions of a patch to add support for leading/trailing bytea trim()ing.
David Rowley sent in two more revisions of a patch to allow estimate_num_groups() to pass back further details about the estimation, allow users of simplehash.h to perform direct deletions, add a Result Cache executor node, remove some code duplication in nodeResultCache.c, and use a Result Cache node to cache results from subplans.
Greg Nancarrow sent in two more revisions of a patch to add new configuration parameter "disable_event_triggers", and add a new "client_connection" event, supporting a "logon trigger".
Bharath Rupireddy sent in another revision of a patch to add a postgres_fdw function to discard cached connections, add a add keep_connections GUC to the postgres_fdw to avoid caching connections, and add a postgres_fdw server-level option, keep_connection to not cache connections.
Peter Eisentraut sent in a patch to change the definitions of bitmap flags to bit-shifting style.
Nathan Bossart sent in three more revisions of a patch to add a SPREAD option to checkpoint.
Justin Pryzby sent in a patch to make the changes to pg_upgrade/test.sh that are needed to allow testing upgrade from v11, and adds a pg_upgrade test to exercise binary compatibility.
Amit Langote sent in another revision of a patch to set ForeignScanState.resultRelInfo lazily, set ResultRelInfo.ri_PartitionRoot in all result relations, not just those that are targets of tuple routing, and initialize result relation information lazily.
Vigneshwaran C sent in a patch to add some copy-related data structures to typedefs.list which were were added during the split of copy into smaller files.
Peter Eisentraut sent in another revision of a patch to add primary keys and unique constraints to system catalogs.
Vigneshwaran C sent in another revision of a patch to make it possible to run COPY FROM with multiple workers.
Andrey Borodin sent in two more revisions of a patch to add functions to 'pageinspect' to inspect GiST indexes.
Bharath Rupireddy sent in three more revisions of a patch to make it possible to use parallel inserts in CTAS, and make some adjustments to tuple costs for this case.
Stephen Frost sent in two revisions of a patch to change the default checkpoint_completion_target to 0.9.
Greg Nancarrow sent in three more revisions of a patch to make it possible to parallelize parts of INSERT ... SELECT.
Rémi Lapeyre sent in another revision of a patch to add header support to the "COPY" text format.
Alexander Korotkov sent in another revision of a patch to support multiranges.
Takamichi Osumi sent in another revision of a patch to implement a stronger safeguard for archive recovery to ensure that it does not miss data.
Vigneshwaran C sent in another revision of a patch to print backtraces of postgres process that are part of the instance current instance.
Gilles Darold sent in a patch to add three hooks at the XactCommand level: start_xact_command_hook, called at end of start_xact_command(), finish_xact_command, called in finish_xact_command() just before CommitTransactionCommand(), and abort_current_transaction_hook, called after an error is encountered at end of AbortCurrentTransaction().
Masahiro Ikeda sent in two revisions of a patch to to add WAL write/fsync statistics to the pg_stat_wal view.
Denis Smirnov sent in a PoC patch to refactor the AM analyse API.
Bharath Rupireddy sent in a patch to add table Access Methods for Multi and Single Inserts.
Andrey Borodin and Gilles Darold traded patches to use a shared lock rather than an exclusive lock in GetMultiXactIdMembers for offsets and members, make MultiXact local cache size configurable, add a condition variable to wait for the next MultXact offset in a corner case, and add GUCs to tune MultiXact SLRUs.
Hou Zhijie sent in a patch to fix a typo about generate_gather_paths.
Shinya Kato sent in a patch to improve psql's tab completion for CLOSE, FETCH, and MOVE.
Michaël Paquier sent in a patch intended to fix a bug that manifested as occasional tablespace.sql failures in check-world -jnn by arranging for pg_regress to clean out the test tablespace dir, or create it if it doesn't exist on all platforms.
Kyotaro HORIGUCHI sent in a patch to add a new test to detect a replication bug, and ensure that WalSndSegmentOpen tracks a timeline switch while sending a historic timeline running physical replication.
Andrey Borodin sent in another revision of a patch to make it possible to disallow cancelation of syncronous commit.
Zeng Wenjing sent in another revision of a patch to implement global temporary tables.
Dilip Kumar sent in another revision of a patch to implement custom compression methods for tables, including ways to change them on the fly.
Fujii Masao sent in another revision of a patch to add a stats_reset time to pg_stat_statements.
Justin Pryzby sent in another revision of a patch to make pg_ls_*() show directories and shared filesets.
Justin Pryzby sent in a patch to add an INCLUDING ACCESS METHOD option to CREATE TABLE LIKE.
Chen Hujaun sent in another revision of a patch to make it possible to compress pages for OLTP.
Andrey Borodin sent in another revision of a patch to speed up the pglz compression code by converting some macro-functions to regular functions and using a more compact hash table, along with some other optimizations.
Atsushi Torikoshi sent in another revision of a patch to implement pg_get_target_backend_memory_contexts(), which is able to collect arbitrary backend process's memory contexts.
Peter Geoghegan sent in another revision of a patch to pass down a "logically unchanged index" hint, and use same to add bottom-up index deletion.
Dilip Kumar sent in another revision of a patch to ensure that pg_is_wal_replay_paused waits for recovery to pause.
Daniel Gustafsson sent in a patch to move the information callback earlier in TLS negotiation to capture the connection. The callback for retrieving state change information during connection setup was only installed when the connection was mostly set up, and thus didn't provide much information. This also extends the callback with printing detailed information about the state change.
Peter Eisentraut sent in a patch to allow a GRANTED BY clause in normal GRANT and REVOKE statements, per the SQL standard.
Stephen Frost sent in a patch intended to fix a bug that manifested as autovacuum worker doesn't immediately exit on postmaster death by replacing some system calls with a WaitLatch.
Bharath Rupireddy sent in a patch to fail fast in CTAS/CMV if relation already exists.
Lukas Meisegeier sent in a patch to add an ssltermination parameter for SNI-based load balancing.
Amit Kapila sent in a patch to speed up xor'ing two gist index signatures for tsvectors by using popcount64 on each of the chunks, and avoid a function pointer dereference for calls to pg_popcount32/64().
Kyotaro HORIGUCHI sent in another revision of a patch to rework the stats collector to use shared memory instead of files for temporary storage.
Kyotaro HORIGUCHI sent in a patch to fix the documentation for pg_shmem_allocations by mentioning that it's NULL for anonymous allocations.
Peter Eisentraut sent in a patch to clean up an ancient test style. Tests were written similar to SELECT '' AS two, i.* FROM INT2_TBL where the first column indicated the number of expected result rows. To clean this up, remove all those extra columns.
Tom Lane sent in two revisions of a patch to rework PL/pgsql's assignment implementation to use more of what's in core.
Peter Eisentraut and Justin Pryzby traded patches to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.
Today, the PostgreSQL Community Code of Conduct Committee welcomes Michael Goldberg as our newest member.
Michael 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 current term runs through September 30, 2021.
The continuing Committee members are:
Stacey Haysler (Chair) is the CFO and COO of PostgreSQL Experts, Inc., in Alameda, California. She is a PostgreSQL Global Development Group Contributor, and the President of the United States PostgreSQL Association (PgUS). She has worked as an organizer and conference staff for a number of conferences, both in North America and Europe, and is also a principal organizer of the San Francisco PostgreSQL Users’ Group.
Carole Arnaud is an executive board member of Dalibo and team manager. She lives in Nantes, France. She was part of the PGDay France committee from 2015 to 2018, and has been co-organizer of pgDay Paris since 2018.
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.
Anastasia Lubennikova is a Senior Developer at Postgres Professional 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.
Fábio Telles Rodriguez is a Database Consultant and co-founder of Timbira, in Brazil. He is a co-organizer of both PGConf.Brazil and Brazilian PUG. He organizes and speaks in many others conferences in his country.
Umair Shahid is a Member of the Marketing Leadership at EDB and is based in Islamabad, Pakistan. He serves as a member of PgUS User Group committee, along with being the organizer for Islamabad and Dubai PostgreSQL Users' Groups.
The Committee is comprised of 4 women, 3 men, and 0 nonbinary individuals.
No two members work at the same company.
We have again expanded our geographical diversity, which now includes our first member from Israel. The specific country representation is:
The full list of Committee members is always available at: https://www.postgresql.org/about/policies/coc_committee/
The Committee can be contacted at coc@postgresql.org.
We are happy to announce that FOSDEM is hosting a virtual PostgreSQL Devroom at FOSDEM 2021. Next year’s conference will take place on the 6th and 7th of February, with the PostgreSQL Devroom being on Saturday 6th.
Information about FOSDEM is available at the official website at https://www.fosdem.org/. The in-person events in previous years attracted more than 8000 participants, expect more people joining for an online event.
We are now looking for PostgreSQL related talks from both experienced and new speakers.
Topics of InterestPostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.
PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extension. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.
About FOSDEMOfficial website: https://fosdem.org/2021/ FOSDEM Code of Conduct: https://fosdem.org/2021/practical/conduct/ FOSDEM is a free and non-commercial event organised by the community for the community. The goal is to provide free and open source software developers and communities a place to meet to:
The devroom will be held on 6th of February 2021, online Submission link: https://penta.fosdem.org/submission/FOSDEM21 Talk format:
If you have submitted for previous FOSDEM editions, remember to use your already existing account on Pentabarf. Make sure to fill out the 'person' details. We need a name, photo, biography and contact information.
Online rulesThe reference time will be Brussels local lime (CET).
Talks must be pre-recorded in advance, and will be streamed during the event. This is a hard requirement from the FOSDEM organizers for which we can not make an exception.
Q/A session will be live.
A facility will be provided for attendees to chat between themselves.
A facility will be provided for attendees to submit questions.
Submission GuidelinesIf you would like to give a talk, present a project or show off some coding skills, we are looking forward to receiving your application.
Submission platform: https://penta.fosdem.org/submission/FOSDEM21
Deadline: 26th of December 2020
Announcement of selected talks: 31st of December 2020
Be sure to properly fill your Pentabarf profile (Person) with:
Then submit your talk (or event)
Pentabarf NotesWe will also call for volunteers to help us run the event and help us with the devroom operation. You can get in touch with the organizers at: contact@fosdempgday.org
OrganizersYou can reach out directly to the organizers if you have a specific request or question: contact@fosdempgday.org
Useful LinksPostgreSQL: https://www.postgresql.org/ PostgreSQL Europe: https://www.postgresql.eu/ FOSDEM 2021: https://fosdem.org/2021/
If you want to keep informed for this edition, you can follow our twitter accounts @fosdempgday and @postgresqleu.
Database .NET v31 is an innovative, powerful and intuitive multiple database management tool.
Major New features from version 30.5 to 31.5:
The new version is immediately available for download.
The pgAdmin Development Team is pleased to announce pgAdmin 4 version 4.29. 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 for PostgreSQL. For more information, please see the website.
Notable changes in this release include:
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.
Person of the week: https://postgresql.life/post/stephane_schildknecht/
PostgreSQL Product Newscheck_pgactivity 2.5, a Nagios remote agent for monitoring PostgreSQL, released. https://github.com/OPMDG/check_pgactivity/releases/latest
WAL-G 0.2.19, a backup management system for PostgreSQL written in Go, released. https://github.com/wal-g/wal-g/releases
PostgreSQL Jobs for Decemberhttp://archives.postgresql.org/pgsql-jobs/2020-12/
PostgreSQL in the NewsPlanet PostgreSQL: http://planet.postgresql.org/
PostgreSQL Weekly News is brought to you this week by David Fetter
Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.
== Applied Patches ==
Tom Lane pushed:
Fix recently-introduced breakage in psql's \connect command. Through my misreading of what the existing code actually did, commits 85c54287a et al. broke psql's behavior for the case where "\c connstring" provides a password in the connstring. We should use that password in such a case, but as of 85c54287a we ignored it (and instead, prompted for a password). Commit 94929f1cf fixed that in HEAD, but since I thought it was cleaning up a longstanding misbehavior and not one I'd just created, I didn't back-patch it. Hence, back-patch the portions of 94929f1cf having to do with password management. In addition to fixing the introduced bug, this means that "\c -reuse-previous=on connstring" will allow re-use of an existing connection's password if the connstring doesn't change user/host/port. That didn't happen before, but it seems like a bug fix, and anyway I'm loath to have significant differences in this code across versions. Also fix an error with the same root cause about whether or not to override a connstring's setting of client_encoding. As of 85c54287a we always did so; restore the previous behavior of overriding only when stdin/stdout are a terminal and there's no environment setting of PGCLIENTENCODING. (I find that definition a bit surprising, but right now doesn't seem like the time to revisit it.) Per bug #16746 from Krzysztof Gradek. As with the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16746-44b30e2edf4335d4@postgresql.org https://git.postgresql.org/pg/commitdiff/7e5e1bba034ee02245e8a3833aa1f6ea7253b584
Fix miscomputation of direct_lateral_relids for join relations. If a PlaceHolderVar is to be evaluated at a join relation, but its value is only needed there and not at higher levels, we neglected to update the joinrel's direct_lateral_relids to include the PHV's source rel. This causes problems because join_is_legal() then won't allow joining the joinrel to the PHV's source rel at all, leading to "failed to build any N-way joins" planner failures. Per report from Andreas Seltenreich. Back-patch to 9.5 where the problem originated. Discussion: https://postgr.es/m/87blfgqa4t.fsf@aurora.ydns.eu https://git.postgresql.org/pg/commitdiff/b1738ff6ab73203cbbc02d7fb82941dbc061d301
Prevent parallel index build in a standalone backend. This can't work if there's no postmaster, and indeed the code got an assertion failure trying. There should be a check on IsUnderPostmaster gating the use of parallelism, as the planner has for ordinary parallel queries. Commit 40d964ec9 got this right, so follow its model of checking IsUnderPostmaster at the same place where we check for max_parallel_maintenance_workers == 0. In general, new code implementing parallel utility operations should do the same. Report and patch by Yulin Pei, cosmetically adjusted by me. Back-patch to v11 where this code came in. Discussion: https://postgr.es/m/HK0PR01MB22747D839F77142D7E76A45DF4F50@HK0PR01MB2274.apcprd01.prod.exchangelabs.com https://git.postgresql.org/pg/commitdiff/275b3411d9189b3974687766db06727d64d22979
Remove configure-time probe for DocBook DTD. Checking for DocBook being installed was valuable when we were on the OpenSP docs toolchain, because that was rather hard to get installed fully. Nowadays, as long as you have xmllint and xsltproc installed, you're good, because those programs will fetch the DocBook files off the net at need. Moreover, testing this at configure time means that a network access may well occur whether or not you have any interest in building the docs later. That can be slow (typically 2 or 3 seconds, though much higher delays have been reported), and it seems not very nice to be doing an off-machine access without warning, too. Hence, drop the PGAC_CHECK_DOCBOOK probe, and adjust related documentation. Without that macro, there's not much left of config/docbook.m4 at all, so I just removed it. Back-patch to v11, where we started to use xmllint in the PGAC_CHECK_DOCBOOK probe. Discussion: https://postgr.es/m/E2EE6B76-2D96-408A-B961-CAE47D1A86F0@yesql.se Discussion: https://postgr.es/m/A55A7FC9-FA60-47FE-98B5-139CDC57CE6E@gmail.com https://git.postgresql.org/pg/commitdiff/4823c4f6ac770ebd49b63b0ce64ecfe82987af57
Fix missing outfuncs.c support for IncrementalSortPath. For debugging purposes, Path nodes are supposed to have outfuncs support, but this was overlooked in the original incremental sort patch. While at it, clean up a couple other minor oversights, as well as bizarre choice of return type for create_incremental_sort_path(). (All the existing callers just cast it to "Path *" immediately, so they don't care, but some future caller might care.) outfuncs.c fix by Zhijie Hou, the rest by me Discussion: https://postgr.es/m/324c4d81d8134117972a5b1f6cdf9560@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/8286223f3d820c39f2d5f14222f7ccde53bdf502
Ensure that expandTableLikeClause() re-examines the same table. As it stood, expandTableLikeClause() re-did the same relation_openrv call that transformTableLikeClause() had done. However there are scenarios where this would not find the same table as expected. We hold lock on the LIKE source table, so it can't be renamed or dropped, but another table could appear before it in the search path. This explains the odd behavior reported in bug #16758 when cloning a table as a temp table of the same name. This case worked as expected before commit 502898192 introduced the need to open the source table twice, so we should fix it. To make really sure we get the same table, let's re-open it by OID not name. That requires adding an OID field to struct TableLikeClause, which is a little nervous-making from an ABI standpoint, but as long as it's at the end I don't think there's any serious risk. Per bug #16758 from Marc Boeren. Like the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16758-840e84a6cfab276d@postgresql.org https://git.postgresql.org/pg/commitdiff/f7f83a55bf6051818a0e4387d718867ecfa8561b
Fix missed step in removal of useless RESULT RTEs in the planner. Commit 4be058fe9 forgot that the append_rel_list would already be populated at the time we remove useless result RTEs, and it might contain PlaceHolderVars that need to be adjusted like the ones in the main parse tree. This could lead to "no relation entry for relid N" failures later on, when the planner tries to do something with an unadjusted PHV. Per report from Tom Ellis. Back-patch to v12 where the bug came in. Discussion: https://postgr.es/m/20201205173056.GF30712@cloudinit-builder https://git.postgresql.org/pg/commitdiff/e98c900993e89ad9278cdfbf0ba5495381a1faac
Fujii Masao pushed:
Improve log message about termination of background workers. Previously the shutdown of a background worker that uses die() as SIGTERM signal handler produced the log message "terminating connection due to administrator command". This log message was confusing because a background worker is not a connection. This commit improves that log message to "terminating background worker XXX due to administrator command" (XXX is replaced with the name of the background worker). This is the same log message as another SIGTERM signal handler bgworker_die() for a background worker reports. Author: Bharath Rupireddy Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/3f292fbb-f155-9a01-7cb2-7ccc9007ab3f@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/98e2d58d66c81d9f1e49813c1acd4d7312089f4a
Fix typo in comment. Author: Haiying Tang tanghy.fnst@cn.fujitsu.com Discussion: https://postgr.es/m/48a0928ac94b497d9c40acf1de394c15@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/6742e14959a3033d946ab3d67f5ce4c99367d332
doc: Add additional index entries for progress reporting views. In the docs, the index entries for progress reporting views link to the "Viewing Statistics" section, but previously they did not link to the dedicated section (e.g., "ANALYZE Progress Reporting") for each view. This was inconvenient when finding the section describing the detailed information about each view, from the index. This commit adds additional index entries linking to those dedicated sections. Author: Fujii Masao Reviewed-by: Shinya Kato Discussion: https://postgr.es/m/e49c2768-65d2-188a-5424-270fa29ccc84@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/0a4db67b5ed05c4013ea968930af36853f088404
Allow restore_command parameter to be changed with reload. This commit changes restore_command from PGC_POSTMASTER to PGC_SIGHUP. As the side effect of this commit, restore_command can be reset to empty during archive recovery. In this setting, archive recovery tries to replay only WAL files available in pg_wal directory. This is the same behavior as when the command that always fails is specified in restore_command. Note that restore_command still must be specified (not empty) when starting archive recovery, even after applying this commit. This is necessary as the safeguard to prevent users from forgetting to specify restore_command and starting archive recovery. Thanks to Peter Eisentraut, Michael Paquier, Andres Freund, Robert Haas and Anastasia Lubennikova for discussion. Author: Sergei Kornilov Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/2317771549527294@sas2-985f744271ca.qloud-c.yandex.net https://git.postgresql.org/pg/commitdiff/942305a36365433eff3c1937945758f2dbf1662b
Track total number of WAL records, FPIs and bytes generated in the cluster. Commit 6b466bf5f2 allowed pg_stat_statements to track the number of WAL records, full page images and bytes that each statement generated. Similarly this commit allows us to track the cluster-wide WAL statistics counters. New columns wal_records, wal_fpi and wal_bytes are added into the pg_stat_wal view, and reports the total number of WAL records, full page images and bytes generated in the , respectively. Author: Masahiro Ikeda Reviewed-by: Amit Kapila, Movead Li, Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/35ef960128b90bfae3b3fdf60a3a860f@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/01469241b2ff6f17fc0b3f20f01559145ffab29e
Heikki Linnakangas pushed:
Remove leftover comments, left behind by removal of WITH OIDS. Author: Amit Langote Discussion: https://www.postgresql.org/message-id/CA%2BHiwqGaRoF3XrhPW-Y7P%2BG7bKo84Z_h%3DkQHvMh-80%3Dav3wmOw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/2bc588798bdd0cdaa8f6cb6713ba8c8cc039fcb1
docs: ulink all references to RFC's. Make sure that the first mentions of RFC's are ulinked to their ietf.org entry, and subsequent ones are marked as acronyms. This makes references to RFC's consistent across the documentation. Author: Daniel Gustafsson Discussion: https://www.postgresql.org/message-id/2C697878-4D01-4F06-8312-2FEDE931E973%40yesql.se https://git.postgresql.org/pg/commitdiff/415dc2009683904f337a1837b6b4eb7f31c4dc55
Fix pg_rewind bugs when rewinding a standby server. If the target is a standby server, its WAL doesn't end at the last checkpoint record, but at minRecoveryPoint. We must scan all the WAL from the last common checkpoint all the way up to minRecoveryPoint for modified pages, and also consider that portion when determining whether the server needs rewinding. Backpatch to all supported versions. Author: Ian Barwick and me Discussion: https://www.postgresql.org/message-id/CABvVfJU-LDWvoz4-Yow3Ay5LZYTuPD7eSjjE4kGyNZpXC6FrVQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/2b4f3130382fe2f8705863e4d38589d4d69cd695
Fix race conditions in newly-added test. Buildfarm has been failing sporadically on the new test. I was able to reproduce this by adding a random 0-10 s delay in the walreceiver, just before it connects to the primary. There's a race condition where node_3 is promoted before it has fully caught up with node_1, leading to diverged timelines. When node_1 is later reconfigured as standby following node_3, it fails to catch up: LOG: primary server contains no more WAL on requested timeline 1 LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/30000A0 That's the situation where you'd need to use pg_rewind, but in this case it happens already when we are just setting up the actual pg_rewind scenario we want to test, so change the test so that it waits until node_3 is connected and fully caught up before promoting it, so that you get a clean, controlled failover. Also rewrite some of the comments, for clarity. The existing comments detailed what each step in the test did, but didn't give a good overview of the situation the steps were trying to create. For reasons I don't understand, the test setup had to be written slightly differently in 9.6 and 9.5 than in later versions. The 9.5/9.6 version needed node 1 to be reinitialized from backup, whereas in later versions it could be shut down and reconfigured to be a standby. But even 9.5 should support "clean switchover", where primary makes sure that pending WAL is replicated to standby on shutdown. It would be nice to figure out what's going on there, but that's independent of pg_rewind and the scenario that this test tests. Discussion: https://www.postgresql.org/message-id/b0a3b95b-82d2-6089-6892-40570f8c5e60%40iki.fi https://git.postgresql.org/pg/commitdiff/36a4ac20fcf31361bd42b63b1b3390b28827a69e
Michaël Paquier pushed:
Refactor parsing rules for option lists of EXPLAIN, VACUUM and ANALYZE. Those three commands have been using the same grammar rules to handle a a list of parenthesized options. This refactors the code so as they use the same parsing rules, shaving some code. A future commit will make use of those option parsing rules for more utility commands, like REINDEX and CLUSTER. Author: Alexey Kondratov, Justin Pryzby Discussion: https://postgr.es/m/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru https://git.postgresql.org/pg/commitdiff/873ea9ee692e7829614f913685db540b17998ba6
doc: Remove more notes about compatibilities with past versions. This is a follow-up of the work done in fa42c2e, that did not include all the fixes previously agreed on. The contents removed here can be confusing to the reader as they refer to rather old server versions. Author: Stephen Frost, Tom Lane, Heikki Linnakangas, Yaroslav Schekin Discussion: https://postgr.es/m/CAB8KJ=jYHgnxLLZSNJz7gBTck4TxomngCmGkw3nEMSNF0yL6wA@mail.gmail.com Discussion: https://postgr.es/m/1599765595731-0.post@n3.nabble.com https://git.postgresql.org/pg/commitdiff/8a17f44c1e7a9f3d2a9da97dc3eba4184a2a453c
Move SHA2 routines to a new generic API layer for crypto hashes. Two new routines to allocate a hash context and to free it are created, as these become necessary for the goal behind this refactoring: switch the all cryptohash implementations for OpenSSL to use EVP (for FIPS and also because upstream does not recommend the use of low-level cryptohash functions for 20 years). Note that OpenSSL hides the internals of cryptohash contexts since 1.1.0, so it is necessary to leave the allocation to OpenSSL itself, explaining the need for those two new routines. This part is going to require more work to properly track hash contexts with resource owners, but this not introduced here. Still, this refactoring makes the move possible. This reduces the number of routines for all SHA2 implementations from twelve (SHA{224,256,386,512} with init, update and final calls) to five (create, free, init, update and final calls) by incorporating the hash type directly into the hash context data. The new cryptohash routines are moved to a new file, called cryptohash.c for the fallback implementations, with SHA2 specifics becoming a part internal to src/common/. OpenSSL specifics are part of cryptohash_openssl.c. This infrastructure is usable for more hash types, like MD5 or HMAC. Any code paths using the internal SHA2 routines are adapted to report correctly errors, which are most of the changes of this commit. The zones mostly impacted are checksum manifests, libpq and SCRAM. Note that e21cbb4 was a first attempt to switch SHA2 to EVP, but it lacked the refactoring needed for libpq, as done here. This patch has been tested on Linux and Windows, with and without OpenSSL, and down to 1.0.1, the oldest version supported on HEAD. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/20200924025314.GE7405@paquier.xyz https://git.postgresql.org/pg/commitdiff/87ae9691d25379785f8c0f81b06a14818cfd8c56
Fix compilation warnings in cryptohash_openssl.c. These showed up with -O2. Oversight in 87ae969. Author: Fujii Masao Discussion: https://postgr.es/m/cee3df00-566a-400c-1252-67c3701f918a@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/91624c2ff8809145880383b0fa84be0ee98f55b5
Refactor CLUSTER and REINDEX grammar to use DefElem for option lists. This changes CLUSTER and REINDEX so as a parenthesized grammar becomes possible for options, while unifying the grammar parsing rules for option lists with the existing ones. This is a follow-up of the work done in 873ea9e for VACUUM, ANALYZE and EXPLAIN. This benefits REINDEX for a potential backend-side filtering for collatable-sensitive indexes and TABLESPACE, while CLUSTER would benefit from the latter. Author: Alexey Kondratov, Justin Pryzby Discussion: https://postgr.es/m/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru https://git.postgresql.org/pg/commitdiff/b5913f6120792465f4394b93c15c2e2ac0c08376
Change SHA2 implementation based on OpenSSL to use EVP digest routines. The use of low-level hash routines is not recommended by upstream OpenSSL since 2000, and pgcrypto already switched to EVP as of 5ff4a67. This takes advantage of the refactoring done in 87ae969 that has introduced the allocation and free routines for cryptographic hashes. Since 1.1.0, OpenSSL does not publish the contents of the cryptohash contexts, forcing any consumers to rely on OpenSSL for all allocations. Hence, the resource owner callback mechanism gains a new set of routines to track and free cryptohash contexts when using OpenSSL, preventing any risks of leaks in the backend. Nothing is needed in the frontend thanks to the refactoring of 87ae969, and the resowner knowledge is isolated into cryptohash_openssl.c. Note that this also fixes a failure with SCRAM authentication when using FIPS in OpenSSL, but as there have been few complaints about this problem and as this causes an ABI breakage, no backpatch is done. Author: Michael Paquier Reviewed-by: Daniel Gustafsson, Heikki Linnakangas Discussion: https://postgr.es/m/20200924025314.GE7405@paquier.xyz Discussion: https://postgr.es/m/20180911030250.GA27115@paquier.xyz https://git.postgresql.org/pg/commitdiff/4f48a6fbe2b28d8281dbbfa2d334fa2ed8472734
Rename cryptohashes.c to cryptohashfuncs.c. 87ae969 has created two new files called cryptohash{_openssl}.c in src/common/, whose names overlap with the existing backend file called cryptohashes.c dedicated to the SQL wrappers for SHA2 and MD5. This file is renamed to cryptohashfuncs.c to be more consistent with the surroundings and reduce the confusion with the new cryptohash interface of src/common/. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/X8hHhaQgbMbW+aGU@paquier.xyz https://git.postgresql.org/pg/commitdiff/bd94a9c04e04bb3b626e88981a50fcca2bd99d60
Álvaro Herrera pushed:
Document concurrent indexes waiting on each other. Because regular CREATE INDEX commands are independent, and there's no logical data dependency, it's not immediately obvious that transactions held by concurrent index builds on one table will block the second phase of concurrent index creation on an unrelated table, so document this caveat. Backpatch this all the way back. In branch master, mention that only some indexes are involved. Author: James Coleman jtc331@gmail.com Reviewed-by: David Johnston david.g.johnston@gmail.com Discussion: https://postgr.es/m/CAAaqYe994=PUrn8CJZ4UEo_S-FfRr_3ogERyhtdgHAb2WG_Ufg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/58ebe967f8a1e34a824d6a0a35728027272041c6
Avoid memcpy() with a NULL source pointer and count == 0. When memcpy() is called on a pointer, the compiler is entitled to assume that the pointer is not null, which can lead to optimizing nearby code in potentially undesirable ways. We still want such optimizations (gcc's -fdelete-null-pointer-checks) in cases where they're valid. Related: commit 13bba02271dc. Backpatch to pg11, where this particular instance appeared. Reported-by: Ranier Vilela ranier.vf@gmail.com Reported-by: Zhihong Yu zyu@yugabyte.com Discussion: https://postgr.es/m/CAEudQApUndmQkr5fLrCKXQ7+ib44i7S+Kk93pyVThS85PnG3bQ@mail.gmail.com Discussion: https://postgr.es/m/CALNJ-vSdhwSM5f4tnNn1cdLHvXMVe_S+V3nR5GwNrmCPNB2VtQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/677f74e5bb8360122ebf2f473d7169ed65ce4dba
Thomas Munro pushed:
Free disk space for dropped relations on commit. When committing a transaction that dropped a relation, we previously truncated only the first segment file to free up disk space (the one that won't be unlinked until the next checkpoint). Truncate higher numbered segments too, even though we unlink them on commit. This frees the disk space immediately, even if other backends have open file descriptors and might take a long time to get around to handling shared invalidation events and closing them. Also extend the same behavior to the first segment, in recovery. Back-patch to all supported releases. Bug: #16663 Reported-by: Denis Patron denis.patron@previnet.it Reviewed-by: Pavel Borisov pashkin.elfe@gmail.com Reviewed-by: Neil Chen carpenter.nail.cz@gmail.com Reviewed-by: David Zhang david.zhang@highgo.ca Discussion: https://postgr.es/m/16663-fe97ccf9932fc800%40postgresql.org https://git.postgresql.org/pg/commitdiff/9f35f943732eaf9121a107f54ea043925e9a5d7b
Use truncate(2) where appropriate. When truncating files by name, use truncate(2). Windows hasn't got it, so keep our previous coding based on ftruncate(2) as a fallback. Discussion: https://postgr.es/m/16663-fe97ccf9932fc800%40postgresql.org https://git.postgresql.org/pg/commitdiff/57faaf376e1961fa48866c6e5d6926463c6671b1
Bruce Momjian pushed:
pg_checksums: data_checksum_version is unsigned so use %u not %d. While the previous behavior didn't generate a warning, we might as well use an accurate *printf specification. Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/888671a8cda5896d36d3ec523e201ab6a11e1855
docs: list single-letter options first in command-line summary. In a few places, the long-version options were listed before the single-letter ones in the command summary of a few commands. This didn't match other commands, and didn't match the option ordering later in the same reference page. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/a659e789b743f1a4b977a1ffc7402ab618a9f68c
doc: remove unnecessary blank before command option text. Backpatch-through: 11 https://git.postgresql.org/pg/commitdiff/3f8971d92e767acf6e3d6e27c4cab7bfd88b71f4
Stephen Frost pushed:
Dean Rasheed pushed:
Peter Eisentraut pushed:
Small code simplifications. strVal() can be used in a couple of places instead of coding the same thing by hand. https://git.postgresql.org/pg/commitdiff/6114040711affa2b0bcf47fa2791187daf8455fb
Remove unnecessary grammar symbols. Instead of publication_name_list, we can use name_list. We already refer to publications everywhere else by the 'name' or 'name_list' symbols, so this only improves consistency. Reviewed-by: https://www.postgresql.org/message-id/flat/3e3ccddb-41bd-ecd8-29fe-195e34d9886f%40enterprisedb.com Discussion: Tom Lane tgl@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a6964bc1bb0793e20636ccb573cd2a5ad3ef7667
Convert elog(LOG) calls to ereport() where appropriate. User-visible log messages should go through ereport(), so they are subject to translation. Many remaining elog(LOG) calls are really debugging calls. Reviewed-by: Alvaro Herrera alvherre@alvh.no-ip.org Reviewed-by: Michael Paquier michael@paquier.xyz Reviewed-by: Noah Misch noah@leadboat.com Discussion: https://www.postgresql.org/message-id/flat/92d6f545-5102-65d8-3c87-489f71ea0a37%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/eb93f3a0b633ad6afb0f37391b87f460c4b0663b
Amit Kapila pushed:
== Pending Patches ==
Tatsuro Yamada sent in another revision of a patch to add \dX (extended statistics) to psql.
Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins.
David G. Johnston sent in another revision of a patch to make some minor cleanups and rewording of plpgsql docs.
Seino Yuki sent in another revision of a patch to add a new column, reset-time, to pg_stat_statements_info.
Justin Pryzby sent in a patch to make \dt+ pg_toast.* work in psql.
Justin Pryzby sent in another revision of a patch to make EXPLAIN show JIT details in non-text format, even if zero.
Peter Eisentraut sent in another revision of a patch to pause recovery for insufficient parameter settings.
Michaël Paquier sent in another revision of a patch to fix a bug that manifested as vac_update_datfrozenxid will raise "wrong tuple length" if pg_database tuple contains toast attribute.
Michaël Paquier sent in another revision of a patch to remove some references to long-obsolete behavior.
Peter Geoghegan sent in another revision of a patch to teach heapam to support bottom-up index deletion, pass down "logically unchanged index" hint, and teach nbtree to use bottom-up index deletion.
Bertrand Drouvot sent in two revisions of a patch to prevent functions from becoming orphaned by dropping their containing schemas.
Andrey V. Lepikhov sent in a patch to fix a wild overestimation of the cost of a foreign join.
Konstantin Knizhnik sent in another revision of a patch to implement custom compression for libpq.
Álvaro Herrera sent in a patch to improve operations spelled like INDEX CONCURRENTLY.
Peter Eisentraut sent in another revision of a patch to pageinspect to change the block number arguments to bigint, as this actually fits uint32.
Aleksey Kondratov, Michaël Paquier, and Justin Pryzby traded patches to allow CLUSTER and VACUUM FULL to change tablespace on the fly.
Bertrand Drouvot and Fujii Masao traded patches to log the standby recovery conflict waits.
Justin Pryzby sent in two more revisions of a patch to make it possible for INSERT SELECT to use a BulkInsertState.
Bharath Rupireddy sent in two more revisions of a patch to make it possible to use parallel inserts in CREATE TABLE AS.
James Coleman sent in another revision of a patch to error if gather merge paths aren't sufficiently sorted.
James Coleman sent in another revision of a patch to ensure that generate_useful_gather_paths doesn't skip unsorted subpaths, enforce parallel safety of pathkeys in generate_useful_gather_paths, disallow SRFs in proactive sort, remove volatile expr target search, and document find_em_expr_usable_for_sorting_rel in prepare_sort_from_pathkeys.
Craig Ringer sent in a patch to implement a TAP test utility module PG_LSN.pm to test things that involve logical sequence numbers.
Greg Nancarrow sent in another revision of a patch to add an in_hot_standby reportable GUC and make transaction_read_only GUC reportable, and enhance the connection parameter target_session_attrs to support new values: read-only/primary/standby/prefer-standby.
Jürgen Purtz sent in another revision of a patch to put more explicit JOINs in the tutorial.
Zhihong Yu sent in another revision of a patch to check nparts for defining index.
Hou Zhejie sent in a patch to replace lcons and list_delete_first in plan_union_children(), with lappend and list_delete_last.
Laurenz Albe sent in a patch to discard the query buffer if editor is quit in psql's \e.
Andres Freund sent in a patch to fix a JIT problem that came up with LLVM 12.
Fujii Masao sent in another revision of a patch to add basic statistics to the pg_stat_wal view.
Theme by me