Jelenlegi hely

Hírolvasó

PostgreSQL Weekly News - January 17, 2021

PostreSQL.org - 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

PostreSQL.org - 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

Leáll az ikonikus security levelezési lista, a Bugtraq

HUP.hu - 2021, január 16 - 08:58

#RIP #Bugtraq security mailing list - https://t.co/eLoblGqvAd

— HUP (@huphu) January 16, 2021 #onhup

A Bugtraq 1993-ban indult hiánypótlóként. A számítógépes biztonság iparágban dolgozó jelentős része rajta nőtt fel. A HUP is gyakran használta referenciaként az elmúlt 2 évtizedben. Most úgy tűnik, hogy végleg bezár:

Kategóriák: Informatika

In-Display ujjlenyomat-olvasót tesztel az Apple az iPhone 13-hoz

HUP.hu - 2021, január 16 - 08:43

Apple Testing In-Display Fingerprint Sensor for iPhone 13, Foldable iPhone Also in the Works https://t.co/dIG0qNYLI1 by @julipuli pic.twitter.com/QoqHLatCkF

— MacRumors.com (@MacRumors) January 15, 2021

Részletek itt.

Kategóriák: Informatika

Reprodukálva: Csúnya NTFS bugot találhattak

HUP.hu - 2021, január 15 - 13:47

Előzmények itt.

Kategóriák: Informatika

Wine 6.0

HUP.hu - 2021, január 15 - 09:13

Wine 6.0 Officially Released with Vulkan Backend for WineD3D, This Is What’s New https://t.co/ngF9nAtDEg

— LXer (@lxer_feed) January 15, 2021

Hivatalosan megjelent a Windows alkalmazások GNU/Linux-on (és egyéb rendszereken) való futtatását lehetővé tevő, nyílt forrású kompatibilitási réteg, a Wine 6.0-s kiadása. A kiadás a projekt egy évnyi fejlesztési erőfeszítéseit összegzi, benne több mint 8300 önálló változtatással. Főbb változások az alábbi területen érhetők tetten:

Kategóriák: Informatika

Proton 5.13-5

HUP.hu - 2021, január 15 - 09:05

Proton 5.13-5 just released, promoting some good stuff from Experimental and adding fresh new fixes! pic.twitter.com/iguerldHNK

— Pierre-Loup Griffais (@Plagman2) January 15, 2021

A Valve alkalmazásában álló fejlesztő, Pierre-Loup Griffais (@Plagman2) bejelentette, hogy elérhető a Wine-ra épülő, Proton nevű kompatibilitási rétegük 5.13-5-ös kiadása. Benne számos hiba- és játékösszeomlás-javítás kapott helyet. Páldául:

Kategóriák: Informatika

Aktívan kihasznált, távoli kódfuttatást lehetővé tevő sebezhetőséget javított a Microsoft a Defender-ben

HUP.hu - 2021, január 13 - 08:18

~~ Patch Tuesday, Jan 2021 ~~#Microsoft releases Windows updates to patch a total of 83 newly discovered security flaws, including an actively exploited zero-day RCE #vulnerability affecting Defender (CVE-2021-1647) application.https://t.co/IuW3s7ubiF#infosec #cybersecurity

— The Hacker News (@TheHackersNews) January 13, 2021

A Microsoft 83 sebezhetőséget javított a januári "Patch Kedd" keretében. Köztük a CVE-2021-1647 alatt futó, jelenleg is aktívan kihasznált, Microsoft Defender-t érintő, távoli kódfuttatást lehetővé tevő sérülékenységet.

Kategóriák: Informatika

Ubuntu 20.04 bootolása iPhone 7-en

HUP.hu - 2021, január 13 - 08:07

Miközben azon fáradoznak, hogy a régebbi iPhone-okhoz is elérhetővé tegyenek aftermarket operációs rendszert, az egyik hackernek sikerült ARM64-es Ubuntu 20.04-et grafikus felülettel (GNOME) bootolnia. A rendszer betöltését egyelőre hálózati bootolással oldotta meg. A lépéseket dokumentálta a Reddit-en.

Kategóriák: Informatika

[Videó] Windows Server 2019: a RAID tömb már régen a múlté?

HUP.hu - 2021, január 12 - 19:01

Réczi Gábor (IQrec) a HWSW free! meetup sorozaton elhangzott előadásának felvétele.

Bár az előadás apropója egy pár éves cikk, illetve ezerféle megoldás van az adatok redundánsan és/vagy nagy sebességgel történő tárolására, ebben a témában sosem lehetünk eléggé tájékozottak. Áttekintjük, hogy a Microsoft milyen lehetőségeket nyújtott és nyújt, illetve miket fog nyújtani ezen a területen, amennyiben helyi tárolásban kell gondolkodnunk.

Kategóriák: Informatika

Illetéktelen hozzáférés a Ubiquiti rendszereihez

HUP.hu - 2021, január 12 - 10:07

Imént az alábbi levelet kaptuk a Ubiquiti rendszeréből:

Dear Customer,

We recently became aware of unauthorized access to certain of our information technology systems hosted by a third party cloud provider. We have no indication that there has been unauthorized activity with respect to any user’s account.

We are not currently aware of evidence of access to any databases that host user data, but we cannot be certain that user data has not been exposed. This data may include your name, email address, and the one-way encrypted password to your account (in technical terms, the passwords are hashed and salted). The data may also include your address and phone number if you have provided that to us.

As a precaution, we encourage you to change your password. We recommend that you also change your password on any website where you use the same user ID or password. Finally, we recommend that you enable two-factor authentication on your Ubiquiti accounts if you have not already done so.
 
Change Password    Enable Two-Factor Authentication
 
We apologize for, and deeply regret, any inconvenience this may cause you. We take the security of your information very seriously and appreciate your continued trust.

Thank you,
Ubiquiti Team

A Ubiquiti eddig nem nyilatkozott nyilvánosan, de a biztonság kedvéért érdemes jelszót változtatni és bekapcsolni a kétfaktoros azonosítást a Ubiquiti online rendszereit használóknak.

Kategóriák: Informatika

Slackware-current újdonságok

HUP.hu - 2021, január 12 - 10:06

Mi történt december óta a Slackware-current ágában:

  • bekerült a KDE Plasma az eddig alapértelmezett KDE4 helyére, január 5-én már 5.20.5-ös verzióval
  • bekerült az XFCE 4.14 az eddigi XFCE 4.12 helyére
  • A kernel az LTS-nek megfelelően 5.10.x-re váltott (jelen pillanatban 5.10.5)
  • Az XFCE 4.14 frissült 4.16-ra
  • Feleslegessé vált csomagok lettek törölve
  • A csomagok nagy része át lett újra nézve és konfigurációs állományok mozgatásra kerültek a /etc alá
  • A Pure-Alsa-System eltávolításra került, már csak a Pulseaudio és újdonságként a pipewire támogatott
  • Systemd továbbra sincs kivéve az elogind

Teljes changelog megtekinthető itt: http://www.slackware.com/changelog/current.php?cpu=x86_64

Kategóriák: Informatika

Megfizethető fitnesz karkötőt dob piacra a OnePlus

HUP.hu - 2021, január 11 - 12:10

OnePlus launches an affordable fitness tracker to compete with Xiaomi https://t.co/Ha2lriZRLa

— XDA (@xdadevelopers) January 11, 2021

Specifikáció:

Kategóriák: Informatika

Fut a VirtualBox 2.1.0 VT-x támogatással ReactOS-en

HUP.hu - 2021, január 11 - 11:56

It's true. #ReactOS runs VirtualBox 2.1.0 with VT-x working... and #Windows 8 PE x64 on top of it!!!

To make the dream real sooner, we need #developers, especially whose are specialized on the #kernel development. Join us here:https://t.co/bg2pYwWBnt

Test: Illen@virtualbox pic.twitter.com/yZALM5TvG6

— ReactOS (@reactos) January 9, 2021

FYI: újabb ReactOS mérföldkő!

Kategóriák: Informatika

Gyorsan lecsapnak a BProf végzős diákjaira a cégek (x)

HUP.hu - 2021, január 11 - 08:58

Már javában töltik a céges gyakorlati évüket azok az informatikus hallgatók, akik elsőként szereznek diplomát a BME 2018-ban indult, hat szemeszteres alapképzésén. A képzésben résztvevők iránt komoly munkaerőpiaci kereslet mutatkozik.

Kategóriák: Informatika

PostgreSQL Weekly News - January 10, 2021

PostreSQL.org - 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

Csúnya NTFS bugot találhattak

HUP.hu - 2021, január 9 - 20:02

The vulnerability can be remotely triggered if having any kind of service allowing file opens of specific names to happen.
Its embeddable in HTML, sharred folders etc.
Until now only consequence have been running chkdsk on boot- but now the MFT have corrupted

— Jonas L (@jonasLyk) January 9, 2021

Jelentője szerint a speciálisan fabrikált nevű fájl(?), folder(?) (nem derül ki) megnyitása után a rendszer azonnal komoly fájlrendszer hibákat logol az eseménynaplóba. Jelenleg ismert következmény egyelőre "csak" az volt, hogy bootoláskor lefutott a chkdsk, de NTFS MFT sérülés is lehetséges.

Kategóriák: Informatika

Linux Mint 20.1 “Ulyssa” Cinnamon | MATE | Xfce

HUP.hu - 2021, január 9 - 16:48

Linux Mint 20.1 “Ulyssa” Cinnamon released!https://t.co/qiaKjlMAO5 pic.twitter.com/Mvlww7tdcM

— Linux Mint (@Linux_Mint) January 8, 2021

Cinnamon bejelentés itt.

Kategóriák: Informatika

Trey papa levelesládája: Linuxos archívum

HUP.hu - 2021, január 9 - 15:25

Folytatás innen. A második levél:

Kategóriák: Informatika

pg_back 1.10 released

PostreSQL.org - 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

Oldalak

Theme by me