Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1koj4c-0003MY-4e for pgsql-announce@arkaria.postgresql.org; Mon, 14 Dec 2020 08:19:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1koj4Z-0004pv-9N for pgsql-announce@arkaria.postgresql.org; Mon, 14 Dec 2020 08:19:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1koj4Y-0004pn-1s for pgsql-announce@lists.postgresql.org; Mon, 14 Dec 2020 08:19:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1koj4T-0007cq-1c for pgsql-announce@lists.postgresql.org; Mon, 14 Dec 2020 08:19:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:From:To:Subject: MIME-Version:Content-Type:Sender:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:In-Reply-To:References; bh=JZqHZXboRufmcTGETskDdX8QO0H4s1/6TR7xdxZGmmg=; b=bq5naMAsvkfHGOtWU2rZ0OEwpo tlqeQxEPLPNTHgZ1TI/bys/IYpk1Kxtq2OKtj71zNbwdseC3flMmiJhQT9XafpHQ3mQPv2aI6zNZv zHJk6L//rfEWmYsJpEJXM/4KaHT7URJ65HEoF9oUa031kWXZtGYOIR9c/PeZAPZAYdk9YT+UNa+RP Fyw4vu3OGM2VKFxVd0qBXWtFhxNgC90bUoaPLFitcBYMzfIBu21ioG0nZwzgctgiHTNPGNbjARvqW HnsK5Om2ra4QkmnKgRRqS+aZF9F39Ob8QlJJa6XoJ00enwgvKz/8ecPJXaC5bE3kworslwbm4cyJ+ a3RX/10A==; Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by mahout.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1koj4S-0000Vo-9M for pgsql-announce@lists.postgresql.org; Mon, 14 Dec 2020 08:19:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1koj4Q-0005Fd-5H for pgsql-announce@lists.postgresql.org; Mon, 14 Dec 2020 08:19:02 +0000 Content-Type: multipart/mixed; boundary="===============7213167665063166033==" MIME-Version: 1.0 Subject: PostgreSQL Weekly News - December 13, 2020 To: PostgreSQL Announce From: PWN via PostgreSQL Announce Reply-To: david@fetter.org Date: Mon, 14 Dec 2020 08:18:35 +0000 Message-ID: <160793391502.7969.9748902057326961933@wrigleys.postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated X-pglister-tags: community,pgsql,proprietary,pwn X-pglister-tagsig: b4ac2f4208db7169db40d930092d9944e361e98be2ce1b0d7fc04f4923050f4c List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============7213167665063166033== Content-Type: multipart/alternative; boundary="===============8586630223231515067==" MIME-Version: 1.0 --===============8586630223231515067== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable # PostgreSQL Weekly News - December 13, 2020 PostgreSQL Person of the Week: [https://postgresql.life/post/carole_arnaud/= ](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/](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.o= rg. # Applied Patches Micha=C3=ABl Paquier pushed: - Fix fd leak in pg_verifybackup. An error code path newly-introduced by 87= ae969 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/51c388987734cb318c8fa875f382ca7= 5a34d0010](https://git.postgresql.org/pg/commitdiff/51c388987734cb318c8fa87= 5f382ca75a34d0010) - 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 la= rge set of ACLs in place. In such a case, the in-place update would fail bec= ause 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 fetc= h 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: Ashw= in Agrawal, Junfeng Yang Discussion: [https://postgr.es/m/DM5PR0501MB38800D9E4605BCA72DD35557CCE10@DM5PR0501MB= 3880.namprd05.prod.outlook.com](https://postgr.es/m/DM5PR0501MB38800D9E4605= BCA72DD35557CCE10@DM5PR0501MB3880.namprd05.prod.outlook.com) [https://git.postgresql.org/pg/commitdiff/947789f1f5fb61daf663f26325cbe7c= ad8197d58](https://git.postgresql.org/pg/commitdiff/947789f1f5fb61daf663f26= 325cbe7cad8197d58) - pgcrypto: Detect errors with EVP calls from OpenSSL. The following routin= es 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 t= his commit to detect such failures, that should never happen except in the ev= ent of a processing failure internal to OpenSSL. Note that it would be possi= ble to use ERR_reason_error_string() to get more context about such errors, b= ut these refer mainly to the internals of OpenSSL, so it is not really obvio= us how useful that would be. This is left out for simplicity. Per report f= rom Coverity. Thanks to Tom Lane for the discussion. Backpatch-through: 9.5 [https://git.postgresql.org/pg/commitdiff/28d1601ad988790c3c53d7ffd24ef6d= 2366d4457](https://git.postgresql.org/pg/commitdiff/28d1601ad988790c3c53d7f= fd24ef6d2366d4457) - Simplify code for getting a unicode codepoint's canonical class. Three pl= aces of unicode_norm.c use a similar logic for getting the combining class fro= m a codepoint. Commit 2991ac5 has added the function get_canonical_class() f= or this purpose, but it was only called by the backend. This commit refacto= rs 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://postgr.es/m/CAFBsxsHUV7s7YrOm6hFz-Jq8Sc7K_yxTkfNZxs= DV-DuM-k-gwg@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/16c302f51235eaec05a1f85a11c1df0= 4ef3a6785](https://git.postgresql.org/pg/commitdiff/16c302f51235eaec05a1f85= a11c1df04ef3a6785) - 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 re= duces 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 remov= ing the implementation that existed in src/common/. KAME was already structu= red with an init/update/final set of routines by pgcrypto (see original pgcrypto/md5.h) for compatibility with OpenSSL, so moving it to src/commo= n/ has proved to be a straight-forward move, requiring no actual manipulatio= n 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, u= pdate and final routines. This gets then consumed by cryptohash.c. The origin= al 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 tes= ts 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://postgr.es/m/20201106073434.GA4961@paquier.xyz) [https://git.postgresql.org/pg/commitdiff/b67b57a966af0c4a9547ac6fff334d3= c256d9c2a](https://git.postgresql.org/pg/commitdiff/b67b57a966af0c4a9547ac6= fff334d3c256d9c2a) - 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 cod= e 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, an= d 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://postgr.es/m/X9= GToVd3QmWeNvj8@paquier.xyz) [https://git.postgresql.org/pg/commitdiff/525e60b7429925d09fce1b5aa0bc2f2= 3cfe6dd18](https://git.postgresql.org/pg/commitdiff/525e60b7429925d09fce1b5= aa0bc2f23cfe6dd18) 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 o= f the query that are the same across all server versions; and make the conditio= nals 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/04732962462ba99cf8f8bcf6ac83932= 867cc96a8](https://git.postgresql.org/pg/commitdiff/04732962462ba99cf8f8bcf= 6ac83932867cc96a8) - Add a couple of regression test cases related to array subscripting. Exer= cise 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 resu= lt stays stable. [https://git.postgresql.org/pg/commitdiff/0a665bbc43c5a678331fb1b1f442745= 00eba6563](https://git.postgresql.org/pg/commitdiff/0a665bbc43c5a678331fb1b= 1f44274500eba6563) - 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 limitatio= n in query strings. Per unsigned documentation comment. Discussion: [https://postgr.es/m/160720552914.710.16625261471128631268@wrigleys.postg= resql.org](https://postgr.es/m/160720552914.710.16625261471128631268@wrigle= ys.postgresql.org) [https://git.postgresql.org/pg/commitdiff/9a2641911aeaa7f6523dc2a46508805= 1d4c85901](https://git.postgresql.org/pg/commitdiff/9a2641911aeaa7f6523dc2a= 465088051d4c85901) - Doc: clarify that CREATE TABLE discards redundant unique constraints. The= SQL standard says that redundant unique constraints are disallowed, but we lo= ng ago decided that throwing an error would be too user-unfriendly, so we ju= st drop redundant ones. The docs weren't very clear about that though, as t= his behavior was only explained for PRIMARY KEY vs UNIQUE, not UNIQUE vs UNIQ= UE. While here, I couldn't resist doing some copy-editing and markup-fixing o= n the adjacent text about INCLUDE options. Per bug #16767 from Matthias vd Mee= nt. Discussion: [https://postgr.es/m/16767-1714a2056ca516d0@postgresql.org](h= ttps://postgr.es/m/16767-1714a2056ca516d0@postgresql.org) [https://git.postgresql.org/pg/commitdiff/f2a69b352de1dffc534c4835010e736= 018aa94de](https://git.postgresql.org/pg/commitdiff/f2a69b352de1dffc534c483= 5010e736018aa94de) - 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 b= een a fertile source of bugs when people did use this. Discussion: [https://postgr.es/m/2234320.1607117945@sss.pgh.pa.us](https://postgr.es/= m/2234320.1607117945@sss.pgh.pa.us) [https://git.postgresql.org/pg/commitdiff/a676386b58bf7cd2df81baa43eb1713= d3a2ec055](https://git.postgresql.org/pg/commitdiff/a676386b58bf7cd2df81baa= 43eb1713d3a2ec055) - Teach contain_leaked_vars that assignment SubscriptingRefs are leaky. array_get_element and array_get_slice qualify as leakproof, since they wi= ll 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 assignme= nt SubscriptingRefs can only occur in INSERT and UPDATE target lists, while = we only care about leakproofness for qual expressions; so the wrong answer c= an't occur in practice. Still, that's a rather shaky answer for a security-re= lated question; and maybe in future somebody will want to ask about leakproofne= ss 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 t= hrow errors. Commit 558d77f20 attempted to lay groundwork for that by asking check_functions_in_node whether a SubscriptingRef contains leaky function= s; but that idea fails now that the implementation methods of a Subscripting= Ref 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 differ= ent. It seems quite unlikely that we'd introduce any actual bug in the short t= ime 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://postgr.es/= m/3143742.1607368115@sss.pgh.pa.us) [https://git.postgresql.org/pg/commitdiff/62ee70331336161cb44733b6c3e0811= 696d962aa](https://git.postgresql.org/pg/commitdiff/62ee70331336161cb44733b= 6c3e0811696d962aa) - 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 s= upport subscripting use `raw_array_subscript_handler()`. It's expected that othe= r 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 call= back 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 req= uired 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 w= eird rules about typlen, we can look to see if pg_type.typsubscript =3D=3D `F_ARRAY_SUBSCRIPT_HANDLER`. For this to be bulletproof, we have to forb= id user-defined types from using that handler directly; but there seems no g= ood reason for them to do so. This patch also removes assumptions that the n= umber of subscripts is limited to MAXDIM (6), or indeed has any hard-wired limi= t. That limit still applies to types handled by `array_subscript_handler` or `raw_array_subscript_handler`, but to discourage other dependencies on th= is constant, I've moved it from c.h to utils/array.h. Dmitry Dolgov, review= ed at various times by Tom Lane, Arthur Zakirov, Peter Eisentraut, Pavel Stehule Discussion: [https://postgr.es/m/CA+q6zcVDuGBv=3DM0FqBYX8DPebS3F_0KQ6OVFobGJPM507_SZ_= w@mail.gmail.com](https://postgr.es/m/CA+q6zcVDuGBv=3DM0FqBYX8DPebS3F_0KQ6O= VFobGJPM507_SZ_w@mail.gmail.com) Discussion: [https://postgr.es/m/CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA@= mail.gmail.com](https://postgr.es/m/CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuU= juuDsyHjOcVA@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/c7aba7c14efdbd9fc1bb44b4cb83bed= ee0c6a6fc](https://git.postgresql.org/pg/commitdiff/c7aba7c14efdbd9fc1bb44b= 4cb83bedee0c6a6fc) - 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://postgr.es/= m/3724341.1607551174@sss.pgh.pa.us) [https://git.postgresql.org/pg/commitdiff/8c15a297452e970d68529ee2ce6bd94= d84598409](https://git.postgresql.org/pg/commitdiff/8c15a297452e970d68529ee= 2ce6bd94d84598409) - Provide an error cursor for "can't subscript" error messages. Commit c7ab= a7c14 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 tha= t the caller is responsible for throwing any error. (In clauses.c, I just chos= e 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 messa= ge would be a poor investment.) [https://git.postgresql.org/pg/commitdiff/653aa603f501aa6e4865105a928cd13= 082ee7152](https://git.postgresql.org/pg/commitdiff/653aa603f501aa6e4865105= a928cd13082ee7152) - Allow subscripting of hstore values. This is basically a finger exercise = to prove that it's possible for an extension module to add subscripting abil= ity. Subscripted fetch from an hstore is not different from the existing "hsto= re -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's n= ot 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:= //postgr.es/m/3724341.1607551174@sss.pgh.pa.us) [https://git.postgresql.org/pg/commitdiff/0ec5f7e78231a621a1d96c4bfedc4a1= 849a6c6cc](https://git.postgresql.org/pg/commitdiff/0ec5f7e78231a621a1d96c4= bfedc4a1849a6c6cc) Heikki Linnakangas pushed: - Fix more race conditions in the newly-added pg_rewind test. pg_rewind loo= ks at the control file to check what timeline a server is on. But promotion doe= sn't immediately write a checkpoint, it merely writes an end-of-recovery WAL record. If pg_rewind runs immediately after promotion, before the checkpo= int has completed, it will think think that the server is still on the earlier timeline. We ran into this issue a long time ago already, see commit 484a848a73f. It's a bit bogus that pg_rewind doesn't determine the timel= ine correctly until the end-of-recovery checkpoint has completed. We probably should fix that. But for now work around it by waiting for the checkpoint= to complete before running pg_rewind, like we did in commit 484a848a73f. In= the passing, tidy up the new test a little bit. Rerder the INSERTs so that the comments make more sense, remove a spurious CHECKPOINT call after pg_rewi= nd has already run, and add --debug option, so that if this fails again, we'= ll have more data. Per buildfarm failure at [https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=3Drorqual&= dt=3D2020-12-06%2018%3A32%3A19&stg=3Dpg_rewind-check.](https://buildfarm.po= stgresql.org/cgi-bin/show_stage_log.pl?nm=3Drorqual&dt=3D2020-12-06%2018%3A= 32%3A19&stg=3Dpg_rewind-check.) Backpatch to all supported versions. Discussion: [https://www.postgresql.org/message-id/1713707e-e318-761c-d287-5b6a4aa807= e8@iki.fi](https://www.postgresql.org/message-id/1713707e-e318-761c-d287-5b= 6a4aa807e8@iki.fi) [https://git.postgresql.org/pg/commitdiff/6ba581cf1104261f506377823a25795= f0849ce62](https://git.postgresql.org/pg/commitdiff/6ba581cf1104261f5063778= 23a25795f0849ce62) Andres Freund pushed: - jit: configure: Explicitly reference 'native' component. Until recently 'native' was implicitly included via 'orcjit', but a change included in L= LVM 11 (not yet released) removed a number of such indirect component referen= ces. Reported-By: Fabien COELHO Reported-By: Andres Freu= nd Reported-By: Thomas Munro Author: Andres Freund Discussion: [https://postgr.es/m/20201201064949.mex6kvi2kygby3ni@alap3.anarazel.de](h= ttps://postgr.es/m/20201201064949.mex6kvi2kygby3ni@alap3.anarazel.de) Backpatch: 11-, where jit support was added [https://git.postgresql.org/pg/commitdiff/9543f0861b1d9b566be88edae21f24f= b1377f45c](https://git.postgresql.org/pg/commitdiff/9543f0861b1d9b566be88ed= ae21f24fb1377f45c) - jit: Correct parameter type for generated expression evaluation functions. clang only uses the 'i1' type for scalar booleans, not for pointers to booleans (as the pointer might be pointing into a larger memory allocatio= n). Therefore a pointer-to-bool needs to the "storage" boolean. There's no k= nown case of wrong code generation due to this, but it seems quite possible th= at it could cause problems (see e.g. 72559438f92). Author: Andres Freund Discussion: [https://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de](h= ttps://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de) Backpatch: 11-, where jit support was added [https://git.postgresql.org/pg/commitdiff/5da871bfa1ba41768ecd7786293d9b8= 1dcf1b667](https://git.postgresql.org/pg/commitdiff/5da871bfa1ba41768ecd778= 6293d9b81dcf1b667) - jit: Reference function pointer types via llvmjit_types.c. It is error pr= one (see 5da871bfa1b) and verbose to manually create function types. Add a he= lper that can reference a function pointer type via llvmjit_types.c and and co= nvert existing instances of manual creation. Author: Andres Freund Reviewed-By: Tom Lane Discussion: [https://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de](h= ttps://postgr.es/m/20201207212142.wz5tnbk2jsaqzogb@alap3.anarazel.de) [https://git.postgresql.org/pg/commitdiff/df99ddc70b971a991c5111a33f2f08b= d7945d5c2](https://git.postgresql.org/pg/commitdiff/df99ddc70b971a991c5111a= 33f2f08bd7945d5c2) 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](h= ttps://postgr.es/m/20201207185614.zzf63vggm5r4sozg@alap3.anarazel.de) [https://git.postgresql.org/pg/commitdiff/4e43ee88c28b725bb63e27609c1a717= 138fc7f39](https://git.postgresql.org/pg/commitdiff/4e43ee88c28b725bb63e276= 09c1a717138fc7f39) - Speed up rechecking if relation needs to be vacuumed or analyze in autova= cuum. After autovacuum collects the relations to vacuum or analyze, it rechecks whether each relation still needs to be vacuumed or analyzed before actua= lly doing that. Previously this recheck could be a significant overhead espec= ially 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 overhea= d. There was the report that this issue caused autovacuum workers to have go= tten =E2=80=9Cstuck=E2=80=9D in a tight loop of table_recheck_autovac() that r= echecks whether a relation needs to be vacuumed or analyzed. This commit speeds up the rec= heck 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 statisti= cs and does the recheck again. The benchmark shows that the more relations = exist and autovacuum workers are running concurrently, the more this change red= uces 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 w= orker is running, the benchmark didn't show any big performance regression by t= he 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](htt= ps://postgr.es/m/3FC6C2F2-8A47-44C0-B997-28830B5716D0@amazon.com) [https://git.postgresql.org/pg/commitdiff/e2ac3fed3b1c3281281eb530c220634= 030cd8084](https://git.postgresql.org/pg/commitdiff/e2ac3fed3b1c3281281eb53= 0c220634030cd8084) Dean Rasheed pushed: - Improve estimation of OR clauses using multiple extended statistics. When estimating an OR clause using multiple extended statistics objects, treat= the estimates for each set of clauses for each statistics object as independe= nt of one another. The overlap estimates produced for each statistics object do= not apply to clauses covered by other statistics objects. Dean Rasheed, revi= ewed by Tomas Vondra. Discussion: [https://postgr.es/m/CAEZATCW=3DJ65GUFm50RcPv-iASnS2mTXQbr=3DCfBvWRVhFLJ_= fWA@mail.gmail.com](https://postgr.es/m/CAEZATCW=3DJ65GUFm50RcPv-iASnS2mTXQ= br=3DCfBvWRVhFLJ_fWA@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/88b0898fe35a5a0325fca21bd4f3ed6= dffb364c1](https://git.postgresql.org/pg/commitdiff/88b0898fe35a5a0325fca21= bd4f3ed6dffb364c1) - Improve estimation of ANDs under ORs using extended statistics. Formerly, extended statistics only handled clauses that were RestrictInfos. However= , the restrictinfo machinery doesn't create sub-AND RestrictInfos for AND claus= es underneath OR clauses. Therefore teach extended statistics to handle bare= AND clauses, looking for compatible RestrictInfo clauses underneath them. De= an Rasheed, reviewed by Tomas Vondra. Discussion: [https://postgr.es/m/CAEZATCW=3DJ65GUFm50RcPv-iASnS2mTXQbr=3DCfBvWRVhFLJ_= fWA@mail.gmail.com](https://postgr.es/m/CAEZATCW=3DJ65GUFm50RcPv-iASnS2mTXQ= br=3DCfBvWRVhFLJ_fWA@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/4f5760d4afa9423fe4d38e4cbec48bf= 5e793e7e5](https://git.postgresql.org/pg/commitdiff/4f5760d4afa9423fe4d38e4= cbec48bf5e793e7e5) Peter Eisentraut pushed: - Change get_constraint_index() to use pg_constraint.conindid. It was still using a scan of pg_depend instead of using the conindid column that has b= een added since. Since it is now just a catalog lookup wrapper and not relat= ed to pg_depend, move from pg_depend.c to lsyscache.c. Reviewed-by: Matthias v= an de Meent Reviewed-by: Tom Lane Reviewed-by: Michael Paquier Discussion: [https://www.postgresql.org/message-id/flat/4688d55c-9a2e-9a5a-d166-5f24f= e0bf8db%40enterprisedb.com](https://www.postgresql.org/message-id/flat/4688= d55c-9a2e-9a5a-d166-5f24fe0bf8db%40enterprisedb.com) [https://git.postgresql.org/pg/commitdiff/8b069ef5dca97cd737a5fd64c420df3= cd61ec1c9](https://git.postgresql.org/pg/commitdiff/8b069ef5dca97cd737a5fd6= 4c420df3cd61ec1c9) - pg_dump: Don't use enums for defining bit mask values. This usage would m= ean that values of the enum type are potentially not one of the enum values. = Use macros instead, like everywhere else. Discussion: [https://www.postgresql.org/message-id/14dde730-1d34-260e-fa9d-7664df2d63= 13@enterprisedb.com](https://www.postgresql.org/message-id/14dde730-1d34-26= 0e-fa9d-7664df2d6313@enterprisedb.com) [https://git.postgresql.org/pg/commitdiff/d2a2808eb444986d2fe716a48e21993= 329142f3d](https://git.postgresql.org/pg/commitdiff/d2a2808eb444986d2fe716a= 48e21993329142f3d) Bruce Momjian pushed: - initdb: properly alphabetize getopt_long options in C string. Backpatch-through: 9.5 [https://git.postgresql.org/pg/commitdiff/39f3a9d2ffcaafeb8ff25e0dbe9f28a= 657efb48e](https://git.postgresql.org/pg/commitdiff/39f3a9d2ffcaafeb8ff25e0= dbe9f28a657efb48e) - initdb: complete getopt_long alphabetization. Backpatch-through: 9.5 [https://git.postgresql.org/pg/commitdiff/d6abfdf84ef7c01d98d04f486fb467a= 4aec83804](https://git.postgresql.org/pg/commitdiff/d6abfdf84ef7c01d98d04f4= 86fb467a4aec83804) Noah Misch pushed: - Correct behavior descriptions in comments, and correct a test name. [https://git.postgresql.org/pg/commitdiff/73aae4522b35125c4f9a506842a6933= 6ec5d0f1b](https://git.postgresql.org/pg/commitdiff/73aae4522b35125c4f9a506= 842a69336ec5d0f1b) - Use HASH_BLOBS for xidhash. This caused BufFile errors on buildfarm member sungazer, and SIGSEGV was possible. Conditions for reaching those sympto= ms were more frequent on big-endian systems. Discussion: [https://postgr.es/m/20201129214441.GA691200@rfd.leadboat.com](https://po= stgr.es/m/20201129214441.GA691200@rfd.leadboat.com) [https://git.postgresql.org/pg/commitdiff/a1b8aa1e4eec520ed8f11c3d134a7a8= 66358d39a](https://git.postgresql.org/pg/commitdiff/a1b8aa1e4eec520ed8f11c3= d134a7a866358d39a) # Pending Patches Bharath Rupireddy sent in a patch to allow parallel mode in REFRESH MATERIA= LIZED 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=C4=9Bhule sent in another revision of a patch to add a unistr func= tion which evaluates various forms of unicode-escaped characters. Bruce Momjian sent in two more revisions of a patch to implement key manage= ment 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() wh= ere appropriate. David Zhang sent in two more revisions of a patch to add table access metho= d 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 forei= gn 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 manif= ested as ALTER TABLE SET LOGGED/UNLOGGED on a partitioned table does nothing sile= ntly. Takamichi Osumi sent in another revision of a patch to add an option to dis= able WAL logging to speed up data loading. Julien Rouhaud sent in a patch to add a new COLLATION option to REINDEX, wh= ich can be used to filter the list of indexes to rebuild. This is handy for tim= es when system collation gets updated. Daniel Gustafsson sent in another revision of a patch to support page check= sum 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 w= aits 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 S= PGiST 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_A= LWAYS 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() f= or cached blocks to ensures that we return a reliable value from smgrnblocks, = make DropRelFileNodeBuffers() more efficient during recovery by avoiding scannin= g the whole buffer pool when the relation is small enough, or the the total numbe= r 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 relat= ion is small enough, or when the number of blocks to be invalidated is below the full scan threshold. Tom=C3=A1=C5=A1 Vondra sent in two more revisions of a patch to make it pos= sible to use extended statistics on expressions. Nathan Bossart and Micha=C3=ABl 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 statistic= s to pg_stat_database. Julien Rouhaud sent in two revisions of a patch to add a bool toplevel colu= mn 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 fil= es 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, al= low users of simplehash.h to perform direct deletions, add a Result Cache execu= tor node, remove some code duplication in nodeResultCache.c, and use a Result C= ache node to cache results from subplans. Greg Nancarrow sent in two more revisions of a patch to add new configurati= on 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 t= he postgres_fdw to avoid caching connections, and add a postgres_fdw server-le= vel 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 opti= on to checkpoint. Justin Pryzby sent in a patch to make the changes to pg_upgrade/test.sh tha= t are needed to allow testing upgrade from v11, and adds a pg_upgrade test to exe= rcise binary compatibility. Amit Langote sent in another revision of a patch to set ForeignScanState.resultRelInfo lazily, set ResultRelInfo.ri_PartitionRoot i= n 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 f= iles. 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 r= un 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 possib= le 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=C3=A9mi 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 multirang= es. 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 afte= r 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 f= or the next MultXact offset in a corner case, and add GUCs to tune MultiXact S= LRUs. 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, FET= CH, and MOVE. Micha=C3=ABl 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 tempor= ary tables. Dilip Kumar sent in another revision of a patch to implement custom compres= sion 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 C= REATE TABLE LIKE. Chen Hujaun sent in another revision of a patch to make it possible to comp= ress 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 "logical= ly 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 GRA= NT 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 alre= ady 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 f= or 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. --===============8586630223231515067== Content-Type: text/html; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable PostgreSQL Weekly News - December 13, 2020
 

PostgreSQL Weekly News - December 13, 2020

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/2= 020-12/

PostgreSQL in the News

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

PostgreSQL Weekly News is brought to you th= is week by David Fetter

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

Applied Patches

Micha=C3=ABl Paquier pushed:

  • Fix fd leak in pg_verifybackup. An error co= de path newly-introduced by 87ae969 forgot to close a file descriptor when verifying a file's checksum. Per report from Coverity, via Tom Lane. h= ttps://git.postgresql.org/pg/commitdiff/51c388987734cb318c8fa875f382ca75a34= d0010

  • 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 la= rge set of ACLs in place. In such a case, the in-place update would fail bec= ause 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 fetc= h 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: Ashw= in Agrawal, Junfeng Yang Discussion: https://postgr.es/m/DM5PR0501MB38800D9E4605BCA72DD35557= CCE10@DM5PR0501MB3880.namprd05.prod.outlook.com htt= ps://git.postgresql.org/pg/commitdiff/947789f1f5fb61daf663f26325cbe7cad8197= d58

  • 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 t= his commit to detect such failures, that should never happen except in the ev= ent of a processing failure internal to OpenSSL. Note that it would be possi= ble to use ERR_reason_error_string() to get more context about such errors, b= ut these refer mainly to the internals of OpenSSL, so it is not really obvio= us how useful that would be. This is left out for simplicity. Per report f= rom Coverity. Thanks to Tom Lane for the discussion. Backpatch-through: 9.5 h= ttps://git.postgresql.org/pg/commitdiff/28d1601ad988790c3c53d7ffd24ef6d2366= d4457

  • Simplify code for getting a unicode codepoi= nt's canonical class. Three places of unicode_norm.c use a similar logic for getting the combining class fro= m a codepoint. Commit 2991ac5 has added the function get_canonical_class() f= or this purpose, but it was only called by the backend. This commit refacto= rs 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 htt= ps://git.postgresql.org/pg/commitdiff/16c302f51235eaec05a1f85a11c1df04ef3a6= 785

  • Refactor MD5 implementations according to n= ew 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 re= duces 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 remov= ing the implementation that existed in src/common/. KAME was already structu= red with an init/update/final set of routines by pgcrypto (see original pgcrypto/md5.h) for compatibility with OpenSSL, so moving it to src/commo= n/ has proved to be a straight-forward move, requiring no actual manipulatio= n 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, u= pdate and final routines. This gets then consumed by cryptohash.c. The origin= al 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 tes= ts 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 htt= ps://git.postgresql.org/pg/commitdiff/b67b57a966af0c4a9547ac6fff334d3c256d9= c2a

  • Fix compilation of uuid-ossp. This module h= ad a dependency on pgcrypto's md5.c that got removed by b67b57a. Instead of the code from pgcrypto, this cod= e 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, an= d 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/X9GToVd3QmWeNv= j8@paquier.xyz htt= ps://git.postgresql.org/pg/commitdiff/525e60b7429925d09fce1b5aa0bc2f23cfe6d= d18

Tom Lane pushed:

  • pg_dump: Reorganize dumpBaseType(). Along t= he same lines as ed2c7f65b and daa9fe8a5, reduce code duplication by having just one copy of the parts o= f the query that are the same across all server versions; and make the conditio= nals control the smallest possible amount of code. This is in preparation for adding another dumpable field to pg_type. h= ttps://git.postgresql.org/pg/commitdiff/04732962462ba99cf8f8bcf6ac83932867c= c96a8

  • Add a couple of regression test cases relat= ed 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 resu= lt stays stable. h= ttps://git.postgresql.org/pg/commitdiff/0a665bbc43c5a678331fb1b1f44274500eb= a6563

  • Doc: explain that the string types can't st= ore \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 limitatio= n in query strings. Per unsigned documentation comment. Discussion: h= ttps://postgr.es/m/160720552914.710.16625261471128631268@wrigleys.postgresq= l.org htt= ps://git.postgresql.org/pg/commitdiff/9a2641911aeaa7f6523dc2a465088051d4c85= 901

  • Doc: clarify that CREATE TABLE discards red= undant unique constraints. The SQL standard says that redundant unique constraints are disallowed, but we lo= ng ago decided that throwing an error would be too user-unfriendly, so we ju= st drop redundant ones. The docs weren't very clear about that though, as t= his behavior was only explained for PRIMARY KEY vs UNIQUE, not UNIQUE vs UNIQ= UE. While here, I couldn't resist doing some copy-editing and markup-fixing o= n the adjacent text about INCLUDE options. Per bug #16767 from Matthias vd Mee= nt. Discussion: https://postg= r.es/m/16767-1714a2056ca516d0@postgresql.org htt= ps://git.postgresql.org/pg/commitdiff/f2a69b352de1dffc534c4835010e736018aa9= 4de

  • Remove operator_precedence_warning. This GU= C 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 b= een a fertile source of bugs when people did use this. Discussion: https://postgr.es/m/2234320.16= 07117945@sss.pgh.pa.us htt= ps://git.postgresql.org/pg/commitdiff/a676386b58bf7cd2df81baa43eb1713d3a2ec= 055

  • Teach contain_leaked_vars that assignment S= ubscriptingRefs are leaky. array_get_element and array_get_slice qualify as leakproof, since they wi= ll 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 assignme= nt SubscriptingRefs can only occur in INSERT and UPDATE target lists, while = we only care about leakproofness for qual expressions; so the wrong answer c= an't occur in practice. Still, that's a rather shaky answer for a security-re= lated question; and maybe in future somebody will want to ask about leakproofne= ss 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 t= hrow errors. Commit 558d77f20 attempted to lay groundwork for that by asking check_functions_in_node whether a SubscriptingRef contains leaky function= s; but that idea fails now that the implementation methods of a Subscripting= Ref 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 differ= ent. It seems quite unlikely that we'd introduce any actual bug in the short t= ime 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.16= 07368115@sss.pgh.pa.us htt= ps://git.postgresql.org/pg/commitdiff/62ee70331336161cb44733b6c3e0811696d96= 2aa

  • Support subscripting of arbitrary types, no= t 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 t= ypes that support subscripting use raw_array_subscript_handler(). It's expecte= d 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 call= back supplied by the handler. On the execution side, replace the ExecEvalSubscriptingRef* layer of functions with direct call= s 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 req= uired 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 w= eird rules about typlen, we can look to see if pg_type.typsubscript =3D=3D F_ARRAY_SUBSCRIPT_HANDLER. For this to be bulletproof, we h= ave to forbid user-defined types from using that handler directly; but there seems no g= ood reason for them to do so. This patch also removes assumptions that the n= umber of subscripts is limited to MAXDIM (6), or indeed has any hard-wired limi= t. That limit still applies to types handled by array_subscript_handle= r or raw_array_subscript_handler, but to discourage other depende= ncies on this constant, I've moved it from c.h to utils/array.h. Dmitry Dolgov, review= ed at various times by Tom Lane, Arthur Zakirov, Peter Eisentraut, Pavel Stehule Discussion: https://postgr.es/m/CA+q6zcVDuGBv=3DM0FqBYX8DPebS3F_0KQ6OVFobGJPM507_= SZ_w@mail.gmail.com Discussion: https://postgr.es/m/CA+q6zcVovR+XY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA= @mail.gmail.com htt= ps://git.postgresql.org/pg/commitdiff/c7aba7c14efdbd9fc1bb44b4cb83bedee0c6a= 6fc

  • 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.16= 07551174@sss.pgh.pa.us htt= ps://git.postgresql.org/pg/commitdiff/8c15a297452e970d68529ee2ce6bd94d84598= 409

  • Provide an error cursor for "can't subscrip= t" 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 tha= t the caller is responsible for throwing any error. (In clauses.c, I just chos= e 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 messa= ge would be a poor investment.) h= ttps://git.postgresql.org/pg/commitdiff/653aa603f501aa6e4865105a928cd13082e= e7152

  • Allow subscripting of hstore values. This i= s basically a finger exercise to prove that it's possible for an extension module to add subscripting abil= ity. Subscripted fetch from an hstore is not different from the existing "hsto= re -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's n= ot 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 htt= ps://git.postgresql.org/pg/commitdiff/0ec5f7e78231a621a1d96c4bfedc4a1849a6c= 6cc

Heikki Linnakangas pushed:

Andres Freund pushed:

Fujii Masao pushed:

  • Bump catversion for pg_stat_wal changes. Ov= ersight in 01469241b2. Reported-by: Andres Freund Discussion: https://postg= r.es/m/20201207185614.zzf63vggm5r4sozg@alap3.anarazel.de htt= ps://git.postgresql.org/pg/commitdiff/4e43ee88c28b725bb63e27609c1a717138fc7= f39

  • 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 actua= lly doing that. Previously this recheck could be a significant overhead espec= ially 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 overhea= d. There was the report that this issue caused autovacuum workers to have go= tten =E2=80=9Cstuck=E2=80=9D in a tight loop of table_recheck_autovac() that r= echecks whether a relation needs to be vacuumed or analyzed. This commit speeds up the rec= heck 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 statisti= cs and does the recheck again. The benchmark shows that the more relations = exist and autovacuum workers are running concurrently, the more this change red= uces 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 w= orker is running, the benchmark didn't show any big performance regression by t= he 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 htt= ps://git.postgresql.org/pg/commitdiff/e2ac3fed3b1c3281281eb530c220634030cd8= 084

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 para= llel LATERAL subqueries with LIMIT/OFFSET.

Peter Eisentraut sent in a WIP patch to fix= temp-install tests to work with macOS SIP.

Pavel St=C4=9Bhule 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 Keisuk= e Kuroda traded patches to fix a bug than manifested as huge memory consumption on partitioned tables with forei= gn 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 sile= ntly.

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 tim= es 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 Git= hub Actions for CI.

Bharath Rupireddy sent in three more revisi= ons of a patch to two add new functions: pg_terminate_backend(pid, wait, timeout), which terminates and w= aits 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 t= he 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() f= or cached blocks to ensures that we return a reliable value from smgrnblocks, = make DropRelFileNodeBuffers() more efficient during recovery by avoiding scannin= g the whole buffer pool when the relation is small enough, or the the total numbe= r 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 relat= ion is small enough, or when the number of blocks to be invalidated is below the full scan threshold.

Tom=C3=A1=C5=A1 Vondra sent in two more rev= isions of a patch to make it possible to use extended statistics on expressions.

Nathan Bossart and Micha=C3=ABl Paquier tra= ded 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 p= atch 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 p= atch 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, al= low users of simplehash.h to perform direct deletions, add a Result Cache execu= tor node, remove some code duplication in nodeResultCache.c, and use a Result C= ache node to cache results from subplans.

Greg Nancarrow sent in two more revisions o= f 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 t= he postgres_fdw to avoid caching connections, and add a postgres_fdw server-le= vel 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 c= hanges to pg_upgrade/test.sh that are needed to allow testing upgrade from v11, and adds a pg_upgrade test to exe= rcise binary compatibility.

Amit Langote sent in another revision of a = patch to set ForeignScanState.resultRelInfo lazily, set ResultRelInfo.ri_PartitionRoot i= n 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 f= iles.

Peter Eisentraut sent in another revision o= f 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 o= f a patch to add functions to 'pageinspect' to inspect GiST indexes.

Bharath Rupireddy sent in three more revisi= ons 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 pa= tch 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=C3=A9mi 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 afte= r an error is encountered at end of AbortCurrentTransaction().

Masahiro Ikeda sent in two revisions of a p= atch to to add WAL write/fsync statistics to the pg_stat_wal view.

Denis Smirnov sent in a PoC patch to refact= or the AM analyse API.

Bharath Rupireddy sent in a patch to add ta= ble Access Methods for Multi and Single Inserts.

Andrey Borodin and Gilles Darold traded pat= ches 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 f= or the next MultXact offset in a corner case, and add GUCs to tune MultiXact S= LRUs.

Hou Zhijie sent in a patch to fix a typo ab= out generate_gather_paths.

Shinya Kato sent in a patch to improve psql= 's tab completion for CLOSE, FETCH, and MOVE.

Micha=C3=ABl Paquier sent in a patch intend= ed 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 p= atch to implement custom compression methods for tables, including ways to change them on the fly.

Fujii Masao sent in another revision of a p= atch 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 INC= LUDING ACCESS METHOD option to CREATE TABLE LIKE.

Chen Hujaun sent in another revision of a p= atch 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 p= atch to ensure that pg_is_wal_replay_paused waits for recovery to pause.

Daniel Gustafsson sent in a patch to move t= he 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 f= ix 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 f= ast 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 th= e documentation for pg_shmem_allocations by mentioning that it's NULL for anonymous allocations= .

Peter Eisentraut sent in a patch to clean u= p 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 t= o rework PL/pgsql's assignment implementation to use more of what's in core.

Peter Eisentraut and Justin Pryzby traded p= atches to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.

This email was sent to you from PWN. It was delivered on their behalf by the PostgreSQL project. Any questions about the content of the message shou= ld be sent to PWN.

You were sent this email as a subscriber of the pgsql-announce mai= linglist, for for one of the content tags Community, PostgreSQL Project, Related propriet= ary or PWN. To unsubscribe from further emails, or change which emails you want to receive, please click th= e personal unsubscribe link that you can find in the headers of this email, or visit https://lists.postgresql.org/unsubscribe/.
 
--===============8586630223231515067==-- --===============7213167665063166033==--