Jelenlegi hely

Címlap » Hírolvasó » Hírforrások

PostreSQL.org

Feliratkozás PostreSQL.org hírcsatorna csatornájára
PostgreSQL news
Webcím: https://www.postgresql.org/
Frissítve: 1 óra 52 perc

PostgreSQL Weekly News - January 17, 2021

2021, január 18 - 01:00
PostgreSQL Weekly News - January 17, 2021

Person of the week: https://postgresql.life/post/gunnar_bluth/

PostgreSQL Product News

pspg 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 January

https://archives.postgresql.org/pgsql-jobs/2021-01/

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Thomas Munro pushed:

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:

Álvaro Herrera pushed:

Michaël Paquier pushed:

Heikki Linnakangas pushed:

Magnus Hagander pushed:

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:

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:

Pending Patches

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.

Kategóriák: Informatika

DBConvert Studio 2.0 released. Database migration and synchronization

2021, január 17 - 01:00

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.

Follow us on Twitter, and Like us on Facebook.

Kategóriák: Informatika

PostgreSQL Weekly News - January 10, 2021

2021, január 11 - 01:00
PostgreSQL Weekly News - January 10, 2021 PostgreSQL Product News

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 January

https://archives.postgresql.org/pgsql-jobs/2021-01/

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Amit Kapila pushed:

Michaël Paquier pushed:

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:

Peter Geoghegan pushed:

Peter Eisentraut pushed:

Dean Rasheed pushed:

Bruce Momjian pushed:

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:

Pending Patches

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.

Kategóriák: Informatika

pg_back 1.10 released

2021, január 9 - 01:00

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

Kategóriák: Informatika

pgagroal 1.1.0

2021, január 5 - 01:00

The pgagroal community is happy to announce version 1.1.0.

New features

  • Transaction pooling enhancements
  • Allow reload of the configuration
  • Initial support for BSD based systems

pgagroal

pgagroal is a high-performance protocol-native connection pool for PostgreSQL.

Features

  • High performance
  • Connection pool
  • Limit connections for users and databases
  • Prefill support
  • Remove idle connections
  • Perform connection validation
  • Enable / disable database access
  • Graceful / fast shutdown
  • Prometheus support
  • Remote management
  • Authentication query support
  • Failover support
  • Transport Layer Security (TLS) v1.2+ support
  • Daemon mode
  • User vault

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.

Kategóriák: Informatika

Veil2 0.9.2 beta

2021, január 3 - 01:00

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.

Kategóriák: Informatika

PostgreSQL Weekly News - January 3, 2021

2021, január 3 - 01:00
PostgreSQL Weekly News - January 3, 2021

Happy New Year from the PostgreSQL Weekly News!

PostgreSQL Product News

Database 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 January

https://archives.postgresql.org/pgsql-jobs/2021-01/

PostgreSQL in the News

Planet PostgreSQL: https://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Jeff Davis pushed:

Bruce Momjian pushed:

Fujii Masao pushed:

  • postgres_fdw: Fix connection leak. In postgres_fdw, the cached connections to foreign servers will not be closed until the local session exits if the user mappings or foreign servers that those connections depend on are dropped. Those connections can be leaked. To fix that connection leak issue, after a change to a pg_foreign_server or pg_user_mapping catalog entry, this commit makes postgres_fdw close the connections depending on that entry immediately if current transaction has not used those connections yet. Otherwise, mark those connections as invalid and then close them at the end of current transaction, since they cannot be closed in the midst of the transaction using them. Closed connections will be remade at the next opportunity if necessary. Back-patch to all supported branches. Author: Bharath Rupireddy Reviewed-by: Zhihong Yu, Zhijie Hou, Fujii Masao Discussion: https://postgr.es/m/CALj2ACVNcGH_6qLY-4_tXz8JLvA+4yeBThRfxMz7Oxbk1aHcpQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e3ebcca843a4703938b9f40a4811f43c4b315753

Michaël Paquier pushed:

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:

Noah Misch pushed:

Amit Kapila pushed:

  • Extend the output plugin API to allow decoding of prepared xacts. This adds six methods to the output plugin API, adding support for streaming changes of two-phase transactions at prepare time. * begin_prepare * filter_prepare * prepare * commit_prepared * rollback_prepared * stream_prepare Most of this is a simple extension of the existing methods, with the semantic difference that the transaction is not yet committed and maybe aborted later. Until now two-phase transactions were translated into regular transactions on the subscriber, and the GID was not forwarded to it. None of the two-phase commands were communicated to the subscriber. This patch provides the infrastructure for logical decoding plugins to be informed of two-phase commands Like PREPARE TRANSACTION, COMMIT PREPARED and ROLLBACK PREPARED commands with the corresponding GID. This also extends the 'test_decoding' plugin, implementing these new methods. This commit simply adds these new APIs and the upcoming patch to "allow the decoding at prepare time in ReorderBuffer" will use these APIs. Author: Ajin Cherian and Amit Kapila based on previous work by Nikhil Sontakke and Stas Kelvich Reviewed-by: Amit Kapila, Peter Smith, Sawada Masahiko, and Dilip Kumar 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/0aa8a01d04c8fe200b7a106878eebc3d0af9105c

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

Pending Patches

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.

Kategóriák: Informatika

Database Lab Engine 2.1.0 released

2021, január 2 - 01:00

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:

  • Better data protection and security:
    • robust configuration defining how data is patched when snapshots are automatically created (both shell and SQL scripts are now supported),
    • new option: preserve passwords for the existing DB users.
  • [experimental] DLE API and the CLI tool are extended to have a new feature: “CI Observer” helping control DB schema changes a.k.a. DB migrations (see CLI reference, watch demo). This is a small step towards the big goal: have 100% coverage for testing DB migrations in CI using full-sized thin clones.

Links:

Any feedback is highly appreciated:

Kategóriák: Informatika

PostgreSQL Weekly News - December 27, 2020

2020, december 27 - 01:00
PostgreSQL Weekly News - December 27, 2020 PostgreSQL Product News

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 December

https://archives.postgresql.org/pgsql-jobs/2020-12/

PostgreSQL in the News

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Tom 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:

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:

Bruce Momjian pushed:

Noah Misch pushed:

Jeff Davis pushed:

Pending Patches

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.

Kategóriák: Informatika

Postgres Operator v1.6.0

2020, december 26 - 01:00

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 upgrade

This 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 PVCs

So 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 clusters

To 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 replicas

The 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 improvements

Many 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:

  • Full-spec NodeAffinity support
  • Configurable ExternalTrafficPolicy for service
  • Choosing alternative schedulers
  • Pod environment variables sourced from secrets
  • Ability to ship WAL files (wal-e) and logical backups to GCS
  • scram-sha-256 hash support for password encryption

Furthermore, our team provided the following additions:

  • Allow annotations to be inherited from the manifest to all major child resources
  • Setting search_path for default roles of databases in the manifest
  • Separate teams CRD to manage additional cluster members

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!

Kategóriák: Informatika

Pgpool-II 4.2.1 is now released.

2020, december 26 - 01:00

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:

  • connection pooling
  • load balancing
  • automatic fail over and more.

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.

Kategóriák: Informatika

PostgreSQL Weekly News - December 20, 2020

2020, december 20 - 01:00
PostgreSQL Weekly News - December 20, 2020

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 News

PoWA 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 December

http://archives.postgresql.org/pgsql-jobs/2020-12/

PostgreSQL in the News

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Michaël Paquier pushed:

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:

Pending Patches

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.

Kategóriák: Informatika

PoWA 4.1 is out!

2020, december 18 - 01:00

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!

Kategóriák: Informatika

New Trademark Policy and Recognised User Group Guidelines

2020, december 15 - 01:00

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).

Kategóriák: Informatika

PostgreSQL Weekly News - December 13, 2020

2020, december 14 - 01:00
PostgreSQL Weekly News - December 13, 2020

PostgreSQL Person of the Week: https://postgresql.life/post/carole_arnaud/

PostgreSQL Jobs for December

http://archives.postgresql.org/pgsql-jobs/2020-12/

PostgreSQL in the News

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

Applied Patches

Michaël Paquier pushed:

  • 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:

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:

Peter Eisentraut pushed:

Bruce Momjian pushed:

Noah Misch pushed:

Pending Patches

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.

Kategóriák: Informatika

New Member of the Community Code of Conduct Committee

2020, december 14 - 01:00

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:

  • Brazil: 1
  • France: 1
  • Israel: 1
  • Pakistan: 1
  • Russian Federation: 1
  • United Kingdom: 1
  • United States: 1

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.

Kategóriák: Informatika

PostgreSQL @ FOSDEM 2021 - Call for Papers

2020, december 14 - 01:00
Call for Proposals

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 Interest
  • Developing applications with or for PostgreSQL
  • Administering large scale PostgreSQL installations
  • Case studies and/or success stories of PostgreSQL deployments (or interesting failures)
  • Tools and utilities
  • PostgreSQL internals hacking
  • Community and local user groups
  • Tuning and performance improvements
  • Migration from other database systems
  • Replication, clustering and high availability
  • Recovery and backup strategies
  • Benchmarking and hardware
  • PostgreSQL related products
  • DevOps and continuous deployment/configuration/integration around PostgreSQL
  • Any other PostgreSQL related topic
Call for Papers Committee
  • Alicja Kucharczyk
  • Georgios Kokolatos
  • Andreas Scherbaum
About PostgreSQL

PostgreSQL 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 FOSDEM

Official 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:

  • Get in touch with other developers and projects
  • Be informed about the latest developments in the free software world
  • Be informed about the latest developments in the open source world
  • Attend interesting talks and presentations on various topics by project leaders and committers
  • To promote the development and benefits of free software and open source solutions
  • Participation and attendance is totally free, though the organisers gratefully accept donations and sponsorship
Essential Information

The devroom will be held on 6th of February 2021, online Submission link: https://penta.fosdem.org/submission/FOSDEM21 Talk format:

  • 25 min of content + 5 min of questions
  • 45 min of content + 10 min of questions

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 rules

The 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 Guidelines

If 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:

  • Your name
  • A speaker bio
  • Your contact information (for the organizers)

Then submit your talk (or event)

Pentabarf Notes
  • "talks" are named "events" in Pentabarf
  • Track must be "PostgreSQL Devroom"
  • Duration must be "00:25:00" or "00:50:00"
  • Event type must be "Lecture"
  • Abstract is the text that goes in the FOSDEM booklet
Volunteers

We 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

Organizers

You can reach out directly to the organizers if you have a specific request or question: contact@fosdempgday.org

Useful Links

PostgreSQL: 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.

Kategóriák: Informatika

Database .NET v31.5 released

2020, december 10 - 01:00

Database .NET v31 is an innovative, powerful and intuitive multiple database management tool.

Major New features from version 30.5 to 31.5:

  • Added support for the read-only mode of PostgreSQL
  • Added support for the SCRAM authentication of PostgreSQL
  • Added support for filtering schemas of PostgreSQL
  • Added support for the jsonpath and bitarray types of PostgreSQL
  • Added composite types of PostgreSQL in SQL Explorer
  • Added getting base table name and base column name in Data Browser
  • Improved user experience and user interface
  • Improved interrupt handling capability in Data Browser
  • Improved Connection Explorer
  • Improved SQL History
  • Improved Data Export and Import
  • Improved Data Browser and Editor
  • Improved AutoComplete and IntelliSense in SQL Editor
  • Performance improvements
  • Updated to Npgsql.dll 4.1.6
  • ...and more

The new version is immediately available for download.

Kategóriák: Informatika

pgAdmin 4 v4.29 Released

2020, december 10 - 01:00

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:

  • Ensure that folder rename should work properly in Storage Manager
  • Fixed an issue where the debugger's custom tab title not applied when opened in the new browser tab
  • Fixed an issue where dynamic tab title has not applied the first time for debugger panel
  • Fixed an issue where schema diff doesn't show the result of compare if source schema has tables with RLS
  • Fixed an issue where the process watcher dialog throws an error for the database server which is already removed
  • Ensure that code coverage should cover class and function declarations
  • Ensure that dirty indicator (*) should not be visible when renaming the tabs
  • Fixed an issue where shared servers import is failing
  • Improve code coverage and API test cases for Foreign Tables, Debugger, Views, and Materialized Views

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.

Kategóriák: Informatika

PostgreSQL Weekly News - December 6, 2020

2020, december 7 - 01:00
PostgreSQL Weekly News - December 6, 2020

Person of the week: https://postgresql.life/post/stephane_schildknecht/

PostgreSQL Product News

check_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 December

http://archives.postgresql.org/pgsql-jobs/2020-12/

PostgreSQL in the News

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org.

== Applied Patches ==

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:

Heikki Linnakangas pushed:

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:

Thomas Munro pushed:

Bruce Momjian pushed:

Stephen Frost pushed:

Dean Rasheed pushed:

  • Improve estimation of OR clauses using extended statistics. Formerly we only applied extended statistics to an OR clause as part of the clauselist_selectivity() code path for an OR clause appearing in an implicitly-ANDed list of clauses. This meant that it could only use extended statistics if all sub-clauses of the OR clause were covered by a single extended statistics object. Instead, teach clause_selectivity() how to apply extended statistics to an OR clause by handling its ORed list of sub-clauses in a similar manner to an implicitly-ANDed list of sub-clauses, but with different combination rules. This allows one or more extended statistics objects to be used to estimate all or part of the list of sub-clauses. Any remaining sub-clauses are then treated as if they are independent. Additionally, to avoid double-application of extended statistics, this introduces "extended" versions of clause_selectivity() and clauselist_selectivity(), which include an option to ignore extended statistics. This replaces the old clauselist_selectivity_simple() function which failed to completely ignore extended statistics when called from the extended statistics code. A known limitation of the current infrastructure is that an AND clause under an OR clause is not treated as compatible with extended statistics (because we don't build RestrictInfos for such sub-AND clauses). Thus, for example, "(a=1 AND b=1) OR (a=2 AND b=2)" will currently be treated as two independent AND clauses (each of which may be estimated using extended statistics), but extended statistics will not currently be used to account for any possible overlap between those clauses. Improving that is left as a task for the future. Original patch by Tomas Vondra, with additional improvements by me. Discussion: https://postgr.es/m/20200113230008.g67iyk4cs3xbnjju@development https://git.postgresql.org/pg/commitdiff/25a9e54d2db31b8031c2d8166114b187e8347098

Peter Eisentraut pushed:

Amit Kapila pushed:

  • Remove incorrect assertion in reorderbuffer.c. We start recording changes in ReorderBufferTXN even before we reach SNAPBUILD_CONSISTENT state so that if the commit is encountered after reaching that we should be able to send the changes of the entire transaction. Now, while recording changes if the reorder buffer memory has exceeded logical_decoding_work_mem then we can start streaming if it is allowed and we haven't yet streamed that data. However, we must not allow streaming to start unless the snapshot has reached SNAPBUILD_CONSISTENT state. In passing, improve the comments atop ReorderBufferResetTXN to mention the case when we need to continue streaming after getting an error. Author: Amit Kapila Reviewed-by: Dilip Kumar Discussion: https://postgr.es/m/CAA4eK1KoOH0byboyYY40NBcC7Fe812trwTa+WY3jQF7WQWZbQg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8ae4ef4fb0e0331f02c4615182600546c8e5c4d4

== 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.

Kategóriák: Informatika

Oldalak

Theme by me