November 21, 2024: PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released!

PostgreSQL Weekly News - January 2, 2022

Posted on 2022-01-05 by PWN
PWN

PostgreSQL Weekly News - January 2, 2022

PostgreSQL Product News

Pgpool-II 4.2.7, 4.1.10, 4.0.17, and 3.7.22 a connection pooler and statement replication system for PostgreSQL, re le as ed.

parquet_s3_fdw 0.2.1, a foreign data wrapper for parquet files on S3, released.

Database Lab 3.0, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released.

sqlite_fdw 2.1.1 released.

DynamoDB FDW 1.1.0 released.

pg_query_rewrite 0.0.3, a rewriter for certain types of PostgreSQL statements, released.

InfluxDB fdw 1.1.1 released https://github.com/pgspider/influxdb_fdw

pgspider v2.0, a cluster engine for distributed data based on PostgreSQL foreign data wrappers, released.

pg_builder 2.0.0 a PHP query builder for PostgreSQL, released.

JDBC FDW 0.1.0 released

griddb_fdw 2.1.1 released. https://github.com/pgspider/griddb_fdw

PostgreSQL Jobs for January

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

PostgreSQL Local

Nordic PGDay 2022 will be held in Helsinki, Finland at the Hilton Helsinki Strand Hotel on March 22, 2022. here

pgDay Paris 2022 will be held in Paris, France on March 24, 2022.

FOSDEM PGDay 2022 will be held on line, on Feb 5-6, 2022. https://fosdem.org/2022/

Citus Con, a virtual global developer event, is happening April 12-13, 2022. The CFP is now open.

PostgreSQL in the News

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

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

Submit news and announcements by Sunday at 3:00pm PST8PDT to [email protected].

Applied Patches

Peter Eisentraut pushed:

John Naylor pushed:

  • Add fast path for validating UTF-8 text. Our previous validator used a traditional algorithm that performed comparison and branching one byte at a time. It's useful in that we always know exactly how many bytes we have validated, but that precision comes at a cost. Input validation can show up prominently in profiles of COPY FROM, and future improvements to COPY FROM such as parallelism or faster line parsing will put more pressure on input validation. Hence, add fast paths for both ASCII and multibyte UTF-8: Use bitwise operations to check 16 bytes at a time for ASCII. If that fails, use a "shift-based" DFA on those bytes to handle the general case, including multibyte. These paths are relatively free of branches and thus robust against all kinds of byte patterns. With these algorithms, UTF-8 validation is several times faster, depending on platform and the input byte distribution. The previous coding in pg_utf8_verifystr() is retained for short strings and for when the fast path returns an error. Review, performance testing, and additional hacking by: Heikki Linakangas, Vladimir Sitnikov, Amit Khandekar, Thomas Munro, and Greg Stark Discussion: https://www.postgresql.org/message-id/CAFBsxsEV_SzH%2BOLyCiyon%3DiwggSyMh_eF6A3LU2tiWf3Cy2ZQg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/911588a3f816d875261d8f7d89e2517978831cd5

Tom Lane pushed:

  • Add a \getenv command to psql. \getenv fetches the value of an environment variable into a psql variable. This is the inverse of the \setenv command that was added over ten years ago. We'd not seen a compelling use-case for \getenv at the time, but upcoming regression test refactoring provides a sufficient reason to add it now. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/33d3eeadb21d2268104840cfef6bc2226ddfc680

  • Remove dynamic translation of regression test scripts, step 1. pg_regress has long had provisions for dynamically substituting path names into regression test scripts and result files, but use of that feature has always been a serious pain in the neck, mainly because updating the result files requires tedious manual editing. Let's get rid of that in favor of passing down the paths in environment variables. In addition to being easier to maintain, this way is capable of dealing with path names that require escaping at runtime, for example paths containing single-quote marks. (There are other stumbling blocks in the way of actually building in a path that looks like that, but removing this one seems like a good thing to do.) The key coding rule that makes that possible is to concatenate pieces of a dynamically-variable string using psql's \set command, and then use the :'variable' notation to quote and escape the string for the next level of interpretation. In hopes of making this change more transparent to "git blame", I've split it into two steps. This commit adds the necessary pg_regress.c support and changes all the *.source files in-place so that they no longer require any dynamic translation. The next commit will just "git mv" them into the regular sql/ and expected/ directories. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d1029bb5a26cb84b116b0dee4dde312291359f2a

  • Remove dynamic translation of regression test scripts, step 2. "git mv" all the input/.source and output/.source files into the corresponding sql/ and expected/ directories. Then remove the pg_regress and Makefile infrastructure associated with dynamic translation. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/dc9c3b0ff21465fa89d71eecf5e6cc956d647eca

  • Merge dblink's paths test script into its main test. There's no longer any reason to fire up a separate psql run to create these functions. (Some refactoring in the main regression tests is also called for, but that will take more thought.) Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/0e6e7f0806b2080cb31f33ff992ec2e4e35fa6f1

  • Add missing EmitWarningsOnPlaceholders() calls. Extensions that define any custom GUCs should call EmitWarningsOnPlaceholders after doing so, to help catch misspellings. Many of our contrib modules hadn't gotten the memo on that, though. Also add such calls to src/test/modules extensions that have GUCs. While these aren't really user-facing, they should illustrate good practice not faulty practice. Shinya Kato Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/1fada5d81e6769ded832a4ca62ee9371bac3fb9f

  • Add help & tab-complete support for psql's \getenv. I forgot about these details in 33d3eeadb :-(. Noted by Christoph Berg. Discussion: https://postgr.es/m/YcI8i/[email protected] https://git.postgresql.org/pg/commitdiff/0f2abd05441f524a67bc58ef5f0cc32054f7fb66

  • Rethink handling of settings with a prefix reserved by an extension. Commit 75d22069e made SET print a warning if you tried to set an unrecognized parameter within namespace previously reserved by an extension. It seems better for that to be an outright error though, for the same reason that we don't let you set unrecognized unqualified parameter names. In any case, the preceding implementation was inefficient and erroneous. Perform the check in a more appropriate spot, and be more careful about prefix-match cases. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/2ed8a8cc5b634d33ea07d681c6b02213da07f792

  • Rename EmitWarningsOnPlaceholders() to MarkGUCPrefixReserved(). This seems like a clearer name for what it does now. Provide a compatibility macro so that extensions don't have to convert to the new name right away. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/5609cc01c69b80f8788771dc6f5696a459469119

  • Revert changes about warnings/errors for placeholders. Revert commits 5609cc01c, 2ed8a8cc5, and 75d22069e until we have a less broken idea of how this should work in parallel workers. Per buildfarm. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/cab5b9ab2c066ba904f13de2681872dcda31e207

  • Fix issues in pgarch's new directory-scanning logic. The arch_filenames[] array elements were one byte too small, so that a maximum-length filename would get corrupted if another entry were made after it. (Noted by Thomas Munro, fix by Nathan Bossart.) Move these arrays into a palloc'd struct, so that we aren't wasting a few kilobytes of static data in each non-archiver process. Add a binaryheap_reset() call to make it plain that we start the directory scan with an empty heap. I don't think there's any live bug of that sort, but it seems fragile, and this is very cheap insurance. Cleanup for commit beb4e9ba1, so no back-patch needed. Discussion: https://postgr.es/m/CA+hUKGLHAjHuKuwtzsW7uMJF4BVPcQRL-UMZG_HM-g0y7yLkUg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1fb17b1903414676bd371068739549cd2966fe87

  • Minor cleanup/optimization in pg_dump. In the wake of commits 05649b88c and 5209c0ba0, findComments() and findSecLabels() no longer use their "Archive *fout" arguments, so get rid of those. While doing that, I noticed that there's no very good reason why dumpCompositeTypeColComments() should be doing its own query to fetch the column names of the composite type, when the calling function has just fetched the same data. Tweak it to use that query result. This probably doesn't save a lot for most people, because since 5209c0ba0 we won't get into this code at all unless the composite type has at least one comment. Nonetheless, it's a wasted query. https://git.postgresql.org/pg/commitdiff/c7cf73eb7b9e7911748ebe117a7219f21e504121

  • pg_dump: make dumpPublication et al. less unlike sibling functions. dumpPublication, dumpPublicationNamespace, dumpPublicationTable, and dumpSubscription failed to check dataOnly. This is just a latent bug, because pg_backup_archiver.c would filter out the ArchiveEntry later; but they're wasting cycles in data-only dumps, and the omission might become a live bug someday. In any case, it's not good to have some dumpFoo functions do this and some not. On the same reasoning, make dumpPublicationNamespace follow the same pattern as every other dumpFoo function for checking the DUMP_COMPONENT_DEFINITION flag. (Since 5209c0ba0, we wouldn't even get here if that flag isn't set, so checking it is just pro forma right now. But it might not be so forever.) Since this is just cosmetic and/or future-proofing, no need for back-patch. https://git.postgresql.org/pg/commitdiff/5e65df64d631257ce60016bec0aca43f042b1d33

  • pg_dump: minor performance improvements from eliminating sub-SELECTs. Get rid of the "username_subquery" mechanism in favor of doing local lookups of role names from role OIDs. The PG backend isn't terribly smart about scalar SubLinks in SELECT output lists, so this offers a small performance improvement, at least in installations with more than a couple of users. In any case the old method didn't make for particularly readable SQL code. While at it, I removed the various custom warning messages about failing to find an object's owner, in favor of just fatal'ing in the local lookup function. AFAIK there is no reason any longer to treat that as anything but a catalog-corruption case, and certainly no reason to make translators deal with a dozen different messages where one would do. (If it turns out that fatal() is indeed a bad idea, we can back off to issuing pg_log_warning() and returning an empty string, resulting in the same behavior as before, except more consistent.) Also drop an entirely unnecessary sub-SELECT to check on the pg_depend status of a sequence relation: we already have a LEFT JOIN to fetch the row of interest in the FROM clause. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d5e8930f50e31d836d84b353b9dadedd5007bb70

  • pg_dump: avoid unsafe function calls in getPolicies(). getPolicies() had the same disease I fixed in other places in commit e3fcbbd62, i.e., it was calling pg_get_expr() for expressions on tables that we don't necessarily have lock on. To fix, restrict the query to only collect interesting rows, rather than doing the filtering on the client side. Like the previous patch, apply to HEAD only for now. Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/3e6e86abca0138abd7265306beb6346dc2d9e221

  • Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/4ace456776524839ef3279ab0bad8a2c9f6cc2a7

Amit Kapila pushed:

Michaël Paquier pushed:

  • Remove assertion for ALTER TABLE .. DETACH PARTITION CONCURRENTLY. One code path related to this flavor of ALTER TABLE was checking that the relation to detach has to be a normal table or a partitioned table, which would fail if using the command with a different relation kind. Views, sequences and materialized views cannot be part of a partition tree, so these would cause the command to fail anyway, but the assertion was triggered. Foreign tables can be part of a partition tree, and again the assertion would have failed. The simplest solution is just to remove this assertion, so as we get the same failure as the non-concurrent code path. While on it, add a regression test in postgres_fdw for the concurrent partition detach of a foreign table, as per a suggestion from Alexander Lakhin. Issue introduced in 71f4c8c. Reported-by: Alexander Lakhin Author: Michael Paquier, Alexander Lakhin Reviewed-by: Peter Eisentraut, Kyotaro Horiguchi Discussion: https://postgr.es/m/[email protected] Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/2e577c94466fde77d24cd44dc47059cf9cf392a4

  • Correct comment and some documentation about REPLICA_IDENTITY_INDEX. catalog/pg_class.h was stating that REPLICA_IDENTITY_INDEX with a dropped index is equivalent to REPLICA_IDENTITY_DEFAULT. The code tells a different story, as it is equivalent to REPLICA_IDENTITY_NOTHING. The behavior exists since the introduction of replica identities, and fe7fd4e even added tests for this case but I somewhat forgot to fix this comment. While on it, this commit reorganizes the documentation about replica identities on the ALTER TABLE page, and a note is added about the case of dropped indexes with REPLICA_IDENTITY_INDEX. Author: Michael Paquier, Wei Wang Reviewed-by: Euler Taveira Discussion: https://postgr.es/m/OS3PR01MB6275464AD0A681A0793F56879E759@OS3PR01MB6275.jpnprd01.prod.outlook.com Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/fc95d35b9429096ec4d028d79dcf1fb8b5d4b16e

  • Fix incorrect field count in pg_control_checkpoint(). 18 columns are generated in this function, but we had enough space for 19 of them. Introduced by 4b0d28d. Author: Bharath Rupireddy Reviewed-by: Justin Pryzby, Euler Taveira Discussion: https://postgr.es/m/CALj2ACVQ=hAs=sT0n4xriimqRrrgECySfg_tSqA+26Rb_yfs2A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/86d9888d2ead04a1a139bbaef9d7f4648022fe4b

Bruce Momjian pushed:

Fujii Masao pushed:

Thomas Munro pushed:

Daniel Gustafsson pushed:

Álvaro Herrera pushed:

Andres Freund pushed:

Magnus Hagander pushed: