PostgreSQL Weekly News - November 8, 2020PostgreSQL Weekly News - November 8, 2020Congratulations to new core team members Andres Freund and Jonathan Katz! https://www.postgresql.org/developer/core/ Person of the week: https://postgresql.life/post/elein_mustain/ PostgreSQL Product NewsphpPgAdmin 7.13.1, a web-based administration tool for PostgreSQL, released. https://xzilla.net//blog/2020/Nov/phpPgAdmin-7-13-1-released.html Ajqvue Version 3.3, a java-based UI which supports PostgreSQL, released. http://ajqvue.com pg_statement_rollback, an extension that adds server side transaction with rollback at statement level, released. https://github.com/lzlabs/pg_statement_rollback/releases/ PostgreSQL Jobs for Novemberhttp://archives.postgresql.org/pgsql-jobs/2020-11/ PostgreSQL in the NewsPlanet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org. Applied PatchesTom Lane pushed: Fix two issues in TOAST decompression. pglz_maximum_compressed_size() potentially underestimated the amount of compressed data required to produce N bytes of decompressed data; this is a fault in commit 11a078cf8. Separately from that, pglz_decompress() failed to protect itself against corrupt compressed data, particularly off == 0 in a match tag. Commit c60e520f6 turned such a situation into an infinite loop, where before it'd just have resulted in garbage output. The combination of these two bugs seems like it may explain bug #16694 from Tom Vijlbrief, though it's impossible to be quite sure without direct inspection of the failing session. (One needs to assume that the pglz_maximum_compressed_size() bug caused us to fail to fetch the second byte of a match tag, and what happened to be there instead was a zero. The reported infinite loop is hard to explain without off == 0, though.) Aside from fixing the bugs, rewrite associated comments for more clarity. Back-patch to v13 where both these commits landed. Discussion: https://postgr.es/m/16694-f107871e499ec114@postgresql.org https://git.postgresql.org/pg/commitdiff/dfc797730fc7a07c0e6bd636ad1a564aecab3161 Second thoughts on TOAST decompression. On detecting a corrupted match tag, pglz_decompress() should just summarily return -1. Breaking out of the loop, as I did in dfc797730, doesn't quite guarantee that will happen. Also, we can use unlikely() on that check, just in case it helps. Backpatch to v13, like the previous patch. https://git.postgresql.org/pg/commitdiff/fd2997565c6f66837440dd57f5e52b56aa964d14 Rethink the generation rule for fmgroids.h macros. Traditionally, the names of fmgroids.h macros for pg_proc OIDs have been constructed from the prosrc field. But sometimes the same C function underlies multiple pg_proc entries, forcing us to make an arbitrary choice of which OID to reference; the other entries are then not namable via fmgroids.h. Moreover, we could not have macros at all for pg_proc entries that aren't for C-coded functions. Instead, use the proname field, and append the proargtypes field (replacing inter-argument spaces with underscores) if proname is not unique. Special-casing unique entries such as F_OIDEQ removes the need to change a lot of code. Indeed, I can only find two places in the tree that need to be adjusted; while this changes quite a few existing entries in fmgroids.h, few of them are referenced from C code. With this patch, all entries in pg_proc.dat have macros in fmgroids.h. Discussion: https://postgr.es/m/472274.1604258384@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8e1f37c07aafd4bb7aa6e1e1982010af11f8b5c7 Remove special checks for pg_rewrite.ev_qual and ev_action being NULL. make_ruledef() and make_viewdef() were coded to cope with possible null-ness of these columns, but they've been marked BKI_FORCE_NOT_NULL for some time. So there's not really any need to do more than what we do for the other columns of pg_rewrite, i.e. just Assert that we got non-null results. (There is a school of thought that says Asserts aren't the thing to do to check for corrupt data, but surely here is not the place to start if we want such a policy.) Also, remove long-dead-if-indeed-it-ever-wasn't-dead handling of an empty actions list in make_ruledef(). That's an error case and should be treated as such. (DO INSTEAD NOTHING is represented by a CMD_NOTHING Query, not an empty list; cf transformRuleStmt.) Kyotaro Horiguchi, some changes by me Discussion: https://postgr.es/m/CAEudQApoA=tMTic6xEPYP_hsNZ8XtToVThK_0x7D_aFQYowq3w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e1339bfc7a2fd4629e1c3f8f919ddd05b4745e13 Fix unportable use of getnameinfo() in pg_hba_file_rules view. fill_hba_line() thought it could get away with passing sizeof(struct sockaddr_storage) rather than the actual addrlen previously returned by getaddrinfo(). While that appears to work on many platforms, it does not work on FreeBSD 11: you get back a failure, which leads to the view showing NULL for the address and netmask columns in all rows. The POSIX spec for getnameinfo() is pretty clearly on FreeBSD's side here: you should pass the actual address length. So it seems plausible that there are other platforms where this coding also fails, and we just hadn't noticed. Also, IMO the fact that getnameinfo() failure leads to a NULL output is pretty bogus in itself. Our pg_getnameinfo_all() wrapper is careful to emit "???" on failure, and we should use that in such cases. NULL should only be emitted in rows that don't have IP addresses. Per bug #16695 from Peter Vandivier. Back-patch to v10 where this code was added. Discussion: https://postgr.es/m/16695-a665558e2f630be7@postgresql.org https://git.postgresql.org/pg/commitdiff/0a4b34031279d938c2e59df8df7159d6c11e39b5 Allow users with BYPASSRLS to alter their own passwords. The intention in commit 491c029db was to require superuserness to change the BYPASSRLS property, but the actual effect of the coding in AlterRole() was to require superuserness to change anything at all about a BYPASSRLS role. Other properties of a BYPASSRLS role should be changeable under the same rules as for a normal role, though. Fix that, and also take care of some documentation omissions related to BYPASSRLS and REPLICATION role properties. Tom Lane and Stephen Frost, per bug report from Wolfgang Walther. Back-patch to all supported branches. Discussion: https://postgr.es/m/a5548a9f-89ee-3167-129d-162b5985fcf8@technowledgy.de https://git.postgresql.org/pg/commitdiff/d907bd0543aa63e59653d7345840bed0f8b3a83b Improve error messages around REPLICATION and BYPASSRLS properties. Clarify wording as per suggestion from Wolfgang Walther. No back-patch; this doesn't seem worth thrashing translatable strings in the back branches. Tom Lane and Stephen Frost Discussion: https://postgr.es/m/a5548a9f-89ee-3167-129d-162b5985fcf8@technowledgy.de https://git.postgresql.org/pg/commitdiff/17fb60387ce3fdc2bbb13d9b67bed0e4da77e173 Guard against core dump from uninitialized subplan. If the planner erroneously puts a non-parallel-safe SubPlan into a parallelized portion of the query tree, nodeSubplan.c will fail in the worker processes because it finds a null in es_subplanstates, which it's unable to cope with. It seems worth a test-and-elog to make that an error case rather than a core dump case. This probably should have been included in commit 16ebab688, which was responsible for allowing nulls to appear in es_subplanstates to begin with. So, back-patch to v10 where that came in. Discussion: https://postgr.es/m/924226.1604422326@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/92f87182f2c617fd420832972b6d0ae4527301c8 Remove useless entries for aggregate functions from fmgrtab.c. Gen_fmgrtab.pl treated aggregate functions the same as other built-in functions, which is wasteful because there is no real need to have entries for them in the fmgr_builtins[] table. Suppressing those entries saves about 3KB in the compiled table on my machine; which is not a lot but it's not nothing either, considering that that table is pretty "hot". The only outside code change needed is that ExecInitWindowAgg() can't be allowed to call fmgr_info_cxt() on a plain aggregate function. But that saves a few cycles anyway. Having done that, the aggregate_dummy() function is unreferenced and might as well be dropped. Using "aggregate_dummy" as the prosrc value for an aggregate is now just a documentation convention not something that matters. There was some discussion of using NULL instead to save a few bytes in pg_proc, but we'd have to remove prosrc's BKI_FORCE_NOT_NULL marking which doesn't seem a great idea. Anyway, it's possible there's client-side code that expects to see "aggregate_dummy" there, so I'm loath to change it without a strong reason. Discussion: https://postgr.es/m/533989.1604263665@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/f21636e5d5b8394ed076e18ddc5f4ba710c69c99 Improve our ability to regurgitate SQL-syntax function calls. The SQL spec calls out nonstandard syntax for certain function calls, for example substring() with numeric position info is supposed to be spelled "SUBSTRING(string FROM start FOR count)". We accept many of these things, but up to now would not print them in the same format, instead simplifying down to "substring"(string, start, count). That's long annoyed me because it creates an interoperability problem: we're gratuitously injecting Postgres-specific syntax into what might otherwise be a perfectly spec-compliant view definition. However, the real reason for addressing it right now is to support a planned change in the semantics of EXTRACT() a/k/a date_part(). When we switch that to returning numeric, we'll have the parser translate EXTRACT() to some new function name (might as well be "extract" if you ask me) and then teach ruleutils.c to reverse-list that per SQL spec. In this way existing calls to date_part() will continue to have the old semantics. To implement this, invent a new CoercionForm value COERCE_SQL_SYNTAX, and make the parser insert that rather than COERCE_EXPLICIT_CALL when the input has SQL-spec decoration. (But if the input has the form of a plain function call, continue to mark it COERCE_EXPLICIT_CALL, even if it's calling one of these functions.) Then ruleutils.c recognizes COERCE_SQL_SYNTAX as a cue to emit SQL call syntax. It can know which decoration to emit using hard-wired knowledge about the functions that could be called this way. (While this solution isn't extensible without manual additions, neither is the grammar, so this doesn't seem unmaintainable.) Notice that this solution will reverse-list a function call with SQL decoration only if it was entered that way; so dump-and-reload will not by itself produce any changes in the appearance of views. This requires adding a CoercionForm field to struct FuncCall. (I couldn't resist the temptation to rearrange that struct's field order a tad while I was at it.) FuncCall doesn't appear in stored rules, so that change isn't a reason for a catversion bump, but I did one anyway because the new enum value for CoercionForm fields could confuse old backend code. Possible future work: * Perhaps CoercionForm should now be renamed to DisplayForm, or something like that, to reflect its more general meaning. This'd require touching a couple hundred places, so it's not clear it's worth the code churn. * The SQLValueFunction node type, which was invented partly for the same goal of improving SQL-compatibility of view output, could perhaps be replaced with regular function calls marked with COERCE_SQL_SYNTAX. It's unclear if this would be a net code savings, however. Discussion: https://postgr.es/m/42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu https://git.postgresql.org/pg/commitdiff/40c24bfef92530bd846e111c1742c2a54441c62c Declare lead() and lag() using anycompatible not anyelement. This allows use of a "default" expression that doesn't slavishly match the data column's type. Formerly you got something like "function lag(numeric, integer, integer) does not exist", which is not just unhelpful but actively misleading. The SQL spec suggests that the default should be coerced to the data column's type, but this implementation instead chooses the common supertype, which seems at least as reasonable. (Note: I took the opportunity to run "make reformat-dat-files" on pg_proc.dat, so this commit includes some cosmetic changes to recently-added entries that aren't related to lead/lag.) Vik Fearing Discussion: https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org https://git.postgresql.org/pg/commitdiff/5c292e6b90433c760a3e15027646c7b94afd0cdd Declare assorted array functions using anycompatible not anyelement. Convert array_append, array_prepend, array_cat, array_position, array_positions, array_remove, array_replace, and width_bucket to use anycompatiblearray. This is a simple extension of commit 5c292e6b9 to hit some other places where there's a pretty obvious gain in usability from doing so. Ideally we'd also modify other functions taking multiple old-style polymorphic arguments. But most of the remainder are tied into one or more operator classes, making any such change a much larger can of worms than I desire to open right now. Discussion: https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org https://git.postgresql.org/pg/commitdiff/9e38c2bb5093ceb0c04d6315ccd8975bd17add66 Remove underflow error in float division with infinite divisor. float4_div and float8_div correctly produced zero for zero divided by infinity, but threw an underflow error for nonzero finite values divided by infinity. This seems wrong; at the very least it's inconsistent with the behavior recently implemented for numeric infinities. Remove the error and allow zero to be returned. This patch also removes a useless isinf() test from the overflow checks in these functions (non-Inf divided by Inf can't produce Inf). Extracted from a larger patch; this seems significant outside the context of geometric operators, so it deserves its own commit. Kyotaro Horiguchi Discussion: https://postgr.es/m/CAGf+fX70rWFOk5cd00uMfa__0yP+vtQg5ck7c2Onb-Yczp0URA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fac83dbd6fe1ac3d4125bfa39f287f95bffe6cda Don't throw an error for LOCK TABLE on a self-referential view. LOCK TABLE has complained about "infinite recursion" when applied to a self-referential view, ever since we made it recurse into views in v11. However, that breaks pg_dump's new assumption that it's okay to lock every relation. There doesn't seem to be any good reason to throw an error: if we just abandon the recursion, we've still satisfied the requirement of locking every referenced relation. Per bug #16703 from Andrew Bille (via Alexander Lakhin). Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/5b7bfc39726ff9f6c52dd73e337c34e74e0d1f39 Revert "pg_dump: Lock all relations, not just plain tables". Revert 403a3d91c, as well as the followup fix 7f4235032, in all branches. We need to think a bit harder about what the behavior of LOCK TABLE on views should be, and there's no time for that before next week's releases. We'll take another crack at this later. Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/d3adaabaf7d555ec8bb1d83c43f72e79f1bf0b7d Revert "Accept relations of any kind in LOCK TABLE". Revert 59ab4ac32, as well as the followup fix 33862cb9c, in all branches. We need to think a bit harder about what the behavior of LOCK TABLE on views should be, and there's no time for that before next week's releases. We'll take another crack at this later. Discussion: https://postgr.es/m/16703-e348f58aab3cf6cc@postgresql.org https://git.postgresql.org/pg/commitdiff/eeda7f6338095701cfe1ba3da37070508efe019e Fix ecpg's mishandling of B'...' and X'...' literals. These were broken in multiple ways: * The xbstart and xhstart lexer actions neglected to set "state_before_str_start" before transitioning to the xb/xh states, thus possibly resulting in "internal error: unreachable state" later. * The test for valid string contents at the end of xb state was flat out wrong, as it accounted incorrectly for the "b" prefix that the xbstart action had injected. Meanwhile, the xh state had no such check at all. * The generated literal value failed to include any quote marks. * The grammar did the wrong thing anyway, typically ignoring the literal value and emitting something else, since BCONST and XCONST tokens were handled randomly differently from SCONST tokens. The first of these problems is evidently an oversight in commit 7f380c59f, but the others seem to be very ancient. The lack of complaints shows that ECPG users aren't using these syntaxes much (although I do vaguely remember one previous complaint). As written, this patch is dependent on 7f380c59f, so it can't go back further than v13. Given the shortage of complaints, I'm not excited about adapting the patch to prior branches. Report and patch by Shenhao Wang (test case adjusted by me) Discussion: https://postgr.es/m/d6402f1bacb74ecba22ef715dbba17fd@G08CNEXMBPEKD06.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/1e3868ab3bef5cfa0f4d44a6937a880be7a3a482 Avoid re-using output variables in new ecpg test case. The buildfarm thinks this leads to memory stomps, though annoyingly I can't duplicate that here. The existing code in strings.pgc is doing something that doesn't seem to be sanctioned at all really by the documentation, but I'm disinclined to try to make that nicer right now. Let's just declare some more output variables in hopes of working around it. https://git.postgresql.org/pg/commitdiff/eed4356fad84b0fd6e3caa49c7006f401159ac9a
David Rowley pushed: Allow run-time pruning on nested Append/MergeAppend nodes. Previously we only tagged on the required information to allow the executor to perform run-time partition pruning for Append/MergeAppend nodes belonging to base relations. It was thought that nested Append/MergeAppend nodes were just about always pulled up into the top-level Append/MergeAppend and that making the run-time pruning info for any sub Append/MergeAppend nodes was a waste of time. However, that was likely badly thought through. Some examples of cases we're unable to pullup nested Append/MergeAppends are: 1) Parallel Append nodes with a mix of parallel and non-parallel paths into a Parallel Append. 2) When planning an ordered Append scan a sub-partition which is unordered may require a nested MergeAppend path to ensure sub-partitions don't mix up the order of tuples being fed into the top-level Append. Unfortunately, it was not just as simple as removing the lines in createplan.c which were purposefully not building the run-time pruning info for anything but RELOPT_BASEREL relations. The code in add_paths_to_append_rel() was far too sloppy about which partitioned_rels it included for the Append/MergeAppend paths. The original code there would always assume accumulate_append_subpath() would pull each sub-Append and sub-MergeAppend path into the top-level path. While it does not appear that there were any actual bugs caused by having the additional partitioned table RT indexes recorded, what it did mean is that later in planning, when we built the run-time pruning info that we wasted effort and built PartitionedRelPruneInfos for partitioned tables that we had no subpaths for the executor to run-time prune. Here we tighten that up so that partitioned_rels only ever contains the RT index for partitioned tables which actually have subpaths in the given Append/MergeAppend. We can now Assert that every PartitionedRelPruneInfo has a non-empty present_parts. That should allow us to catch any weird corner cases that have been missed. In passing, it seems there is no longer a good reason to have the AppendPath and MergeAppendPath's partitioned_rel fields a List of IntList. We can simply have a List of Relids instead. This is more compact in memory and faster to add new members to. We still know which is the root level partition as these always have a lower relid than their children. Previously this field was used for more things, but run-time partition pruning now remains the only user of it and it has no need for a List of IntLists. Here we also get rid of the RelOptInfo partitioned_child_rels field. This is what was previously used to (sometimes incorrectly) set the Append/MergeAppend path's partitioned_rels field. That was the only usage of that field, so we can happily just remove it. I also couldn't resist changing some nearby code to make use of the newly added for_each_from macro so we can skip the first element in the list without checking if the current item was the first one on each iteration. A bug report from Andreas Kretschmer prompted all this work, however, after some consideration, I'm not personally classing this as a bug fix. So no backpatch. In Andreas' test case, it just wasn't that clear that there was a nested Append since the top-level Append just had a single sub-path which was pulled up a level, per 8edd0e794. Author: David Rowley Reviewed-by: Amit Langote Discussion: https://postgr.es/m/flat/CAApHDvqSchs%2BubdybcfFaSPB%2B%2BEA7kqMaoqajtP0GtZvzOOR3g%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/a929e17e5a8c9b751b66002c8a89fdebdacfe194 Fix unstable partition_prune regression tests. This was broken recently by a929e17e5. I'd failed to remember that parallel tests should have their EXPLAIN output run through the explain_parallel_append function so that the output is stable when parallel workers fail to start. fairywren was first to notice. Reported-by: Michael Paquier Discussion: https://postgr.es/m/20201102062951.GB15770@paquier.xyz https://git.postgresql.org/pg/commitdiff/90d8f1b1826ce076a502a43fe7c88423b46c6349
Amit Kapila pushed: Michaël Paquier pushed: Fix some grammar and typos in comments and docs. The documentation fixes are backpatched down to where they apply. Author: Justin Pryzby Discussion: https://postgr.es/m/20201031020801.GD3080@telsasoft.com Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/8a15e735be00f156a7227741c0ce88702e6de099 Revert pg_relation_check_pages(). This reverts the following set of commits, following complaints about the lack of portability of the central part of the code in bufmgr.c as well as the use of partition mapping locks during page reads: c780a7a9 f2b88396 b787d4ce ce7f772c 60a51c6b Per discussion with Andres Freund, Robert Haas and myself. Bump catalog version. Discussion: https://postgr.es/m/20201029181729.2nrub47u7yqncsv7@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/e152506adef4bc503ea7b8ebb4fedc0b8eebda81 Fix minor issues with new unicode {de,re}composition code. The table generation script would incorrectly complain in the recomposition sorting when matching code points. This would not have caused the generation of an incorrect table. Note that this condition is not reachable yet, but could have been reached with future updates. pg_bswap.h does not need to be included in the frontend.x Author: John Naylor Discussion: https://postgr.es/m/CAFBsxsGWmExpvv=61vtDKCs7+kBbhkwBDL2Ph9CacziFKnV_yw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ceaeac54f75f0117a7b3caa2f8ecba5cfac18ee5 Add GUC_LIST_INPUT and GUC_LIST_QUOTE to unix_socket_directories. This should have been done in the initial commit that made unix_socket_directories a list as of c9b0cbe. This change allows to support correctly the case of ALTER SYSTEM, where it is possible to specify multiple paths as a list, like the following pattern where flattening is applied to each item: ALTER SYSTEM SET unix_socket_directories = '/path1', '/path2'; Any parameters specified in postgresql.conf are parsed the same way, so there is no compatibility change. pg_dump has a hardcoded list of parameters marked with GUC_LIST_QUOTE, that gets its routine update. These are reordered alphabetically for clarity. Author: Ian Lawrence Barwick Reviewed-by: Peter Eisentraunt, Tom Lane, Michael Paquier Discussion: https://postgr.es/m/CAB8KJ=iMOtNY6_sUwV=LQVCJ2zgYHBDyNzVfvE5GN3WQ3v9kQg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a05dbf477b0ef173adb1ae5d004cbeb0cf400b66
Heikki Linnakangas pushed: doc: Mention UNION/ORDER BY etc. keywords in section headers. Most of the section and sub-section headers in the Queries chapter have the keywords literally stated, but neither "Sorting Rows" nor "Combining Rows" did. There's no rule that they must be, but it seems like a good practice. The keywords will ring a bell to anyone with with even a little bit of SQL experience. David G. Johnston, per suggestion by bilge@scriptfusion.com Discussion: https://www.postgresql.org/message-id/159981394174.31338.7014519396749859167%40wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/8ef2a5afdf8ec9e4c8b28a7042c9508eb6161671 pg_rewind: Move syncTargetDirectory() to file_ops.c. For consistency. All the other low-level functions that operate on the target directory are in file_ops.c. Reviewed-by: Michael Paquier Discussion: https://www.postgresql.org/message-id/0c5b3783-af52-3ee5-f8fa-6e794061f70d%40iki.fi https://git.postgresql.org/pg/commitdiff/ffb4e27e9c5ea87f9fecb7036dfc7cc1f38169b6 Refactor pg_rewind for more clear decision making. Deciding what to do with each file is now a separate step after all the necessary information has been gathered. It is more clear that way. Previously, the decision-making was divided between process_source_file() and process_target_file(), and it was a bit hard to piece together what the overall rules were. Reviewed-by: Kyotaro Horiguchi, Soumyadeep Chakraborty Discussion: https://www.postgresql.org/message-id/0c5b3783-af52-3ee5-f8fa-6e794061f70d%40iki.fi https://git.postgresql.org/pg/commitdiff/eb00f1d4bf96bdba236bcc089f3ae94db9b7c603 pg_rewind: Replace the hybrid list+array data structure with simplehash. Now that simplehash can be used in frontend code, let's make use of it. Reviewed-by: Kyotaro Horiguchi, Soumyadeep Chakraborty Discussion: https://www.postgresql.org/message-id/0c5b3783-af52-3ee5-f8fa-6e794061f70d%40iki.fi https://git.postgresql.org/pg/commitdiff/f81e97d0475cd4bc597adc23b665bd84fbf79a0d pg_rewind: Refactor the abstraction to fetch from local/libpq source. This makes the abstraction of a "source" server more clear, by introducing a common abstract class, borrowing the object-oriented programming term, that represents all the operations that can be done on the source server. There are two implementations of it, one for fetching via libpq, and another to fetch from a local directory. This adds some code, but makes it easier to understand what's going on. The copy_executeFileMap() and libpq_executeFileMap() functions contained basically the same logic, just calling different functions to fetch the source files. Refactor so that the common logic is in one place, in a new function called perform_rewind(). Reviewed-by: Kyotaro Horiguchi, Soumyadeep Chakraborty Discussion: https://www.postgresql.org/message-id/0c5b3783-af52-3ee5-f8fa-6e794061f70d%40iki.fi https://git.postgresql.org/pg/commitdiff/37d2ff38031262a1778bc76a9c55fff7afbcf275
Thomas Munro pushed: Add pg_depend.refobjversion. Provide a place for the version of referenced database objects to be recorded. A follow-up commit will use this to record dependencies on collation versions for indexes, but similar ideas for other kinds of objects have also been mooted. Author: Thomas Munro thomas.munro [AT] gmail.com Reviewed-by: Julien Rouhaud rjuju123 [AT] gmail.com Reviewed-by: Peter Eisentraut peter.eisentraut [AT] 2ndquadrant.com Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/cd6f479e79f3a33ef7a919c6b6c0c498c790f154 Remove pg_collation.collversion. This model couldn't be extended to cover the default collation, and didn't have any information about the affected database objects when the version changed. Remove, in preparation for a follow-up commit that will add a new mechanism. Author: Thomas Munro thomas.munro [AT] gmail.com Reviewed-by: Julien Rouhaud rjuju123 [AT] gmail.com Reviewed-by: Peter Eisentraut peter.eisentraut [AT] 2ndquadrant.com Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/7d1297df0830725d4434ba7dbf71a9e8221ad49e Track collation versions for indexes. Record the current version of dependent collations in pg_depend when creating or rebuilding an index. When accessing the index later, warn that the index may be corrupted if the current version doesn't match. Thanks to Douglas Doole, Peter Eisentraut, Christoph Berg, Laurenz Albe, Michael Paquier, Robert Haas, Tom Lane and others for very helpful discussion. Author: Thomas Munro thomas.munro [AT] gmail.com Author: Julien Rouhaud rjuju123 [AT] gmail.com Reviewed-by: Peter Eisentraut peter.eisentraut [AT] 2ndquadrant.com (earlier versions) Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/257836a75585934cc05ed7a80bccf8190d41e056 Tolerate version lookup failure for old style Windows locale names. Accept that we can't get versions for such locale names for now. Users will need to specify the newer language tag format to enable the collation versioning feature. It's not clear that we can do automatic conversion from the old style to the new style reliably enough for this purpose. Unfortunately, this means that collation versioning probably won't work for the default collation unless you provide something like en-US at initdb or CREATE DATABASE time (though, for reasons not yet understood, it does seem to work on some systems). It'd be nice to find a better solution, or document this quirk if we settle on it, but this should unbreak the 3 failing build farm animals in the meantime. Reviewed-by: David Rowley dgrowleyml [AT] gmail.com Reviewed-by: Tom Lane tgl [AT] sss.pgh.pa.us Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/9f12a3b95dd56c897f1aa3d756d8fb419e84a187 Fix unlinking of SLRU segments. Commit dee663f7 intended to drop any queued up fsync requests before unlinking segment files, but missed a code path. Fix, by centralizing the forget-and-unlink code into a single function. Reported-by: Tomas Vondra tomas.vondra [AT] 2ndquadrant.com Discussion: https://postgr.es/m/20201104013205.icogbi773przyny5%40development https://git.postgresql.org/pg/commitdiff/c732c3f8c122009de373cff9b44b5cf0992ba1bf Fix assertion in collation version lookup. Commit 257836a7 included an assertion that a version lookup routine is not trying to look up "C" or "POSIX", but that case is reachable with the user-facing SQL function pg_collation_actual_version(). Remove the assertion. https://git.postgresql.org/pg/commitdiff/d50e3b1f8d763d51080ca4f6cbaa6818a6472292
Magnus Hagander pushed: Peter Eisentraut pushed: Use PG_GETARG_TRANSACTIONID where appropriate. Some places were using PG_GETARG_UINT32 where PG_GETARG_TRANSACTIONID would be more appropriate. (Of course, they are the same internally, so there is no externally visible effect.) To do that, export PG_GETARG_TRANSACTIONID outside of xid.c. We also export PG_RETURN_TRANSACTIONID for symmetry, even though there are currently no external users. Author: Ashutosh Bapat ashutosh.bapat [AT] 2ndquadrant.com Discussion: https://www.postgresql.org/message-id/flat/d8f6bdd536df403b9b33816e9f7e0b9d@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/dd26a0ad760b11237d7ea9cda8ccccc1826c2c64 Remove use of deprecated containment operators in tests. Switch @ to <@ and ~ to @> in gist-related tests. The old operator names have been deprecated and will eventually (possibly soon) be removed. Author: Justin Pryzby pryzby [AT] telsasoft.com Discussion: https://www.postgresql.org/message-id/flat/20201027032511.GF9241@telsasoft.com https://git.postgresql.org/pg/commitdiff/3165426e54df02a6199c0a216546e5095e875a0a Remove deprecated containment operators for built-in types. Remove old containment operators @ and ~ for built-in geometry data types. These have been deprecated; use <@ and @> instead. (Some contrib modules still contain the same deprecated operators. That will be dealt with separately.) Author: Justin Pryzby pryzby [AT] telsasoft.com Discussion: https://www.postgresql.org/message-id/flat/20201027032511.GF9241@telsasoft.com https://git.postgresql.org/pg/commitdiff/2f70fdb0644c32c4154236c2b5c241bec92eac5e Disallow ALTER TABLE ONLY / DROP EXPRESSION. The current implementation cannot handle this correctly, so just forbid it for now. GENERATED clauses must be attached to the column definition and cannot be added later like DEFAULT, so if a child table has a generation expression that the parent does not have, the child column will necessarily be an attlocal column. So to implement ALTER TABLE ONLY / DROP EXPRESSION, we'd need extra code to update attislocal of the direct child tables, somewhat similar to how DROP COLUMN does it, so that the resulting state can be properly dumped and restored. Discussion: https://www.postgresql.org/message-id/flat/15830.1575468847%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/bf797a8d9768239f5e3204b013044274b2c7c24a Enable hash partitioning of text arrays. hash_array_extended() needs to pass PG_GET_COLLATION() to the hash function of the element type. Otherwise, the hash function of a collation-aware data type such as text will error out, since the introduction of nondeterministic collation made hash functions require a collation, too. The consequence of this is that before this change, hash partitioning using an array over text in the partition key would not work. Reviewed-by: Heikki Linnakangas hlinnaka [AT] iki.fi Reviewed-by: Tom Lane tgl [AT] sss.pgh.pa.us Reviewed-by: Michael Paquier michael [AT] paquier.xyz Discussion: https://www.postgresql.org/message-id/flat/32c1fdae-95c6-5dc6-058a-a90330a3b621%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/560564d3addcb6c54e13c1ca8bd11afafbb7ba11 Move catalog toast table declarations. Move the system catalog toast table declarations from catalog/toasting.h to the respective parent tables' catalog/pg_*.h files. The original reason for having it split was that the old genbki system produced the output in the order of the catalog files it read, so all the toasting stuff needed to come separately. But this is no longer the case, and keeping it together makes more sense. Reviewed-by: John Naylor john.naylor [AT] enterprisedb.com Discussion: https://www.postgresql.org/message-id/flat/c7cc82d6-f976-75d6-2e3e-b03d2cab26bb@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/b4c9695e79f79d39d55853acac4c1493ac70a529 Move catalog index declarations. Move the system catalog index declarations from catalog/indexing.h to the respective parent tables' catalog/pg_*.h files. The original reason for having it split was that the old genbki system produced the output in the order of the catalog files it read, so all the indexing stuff needed to come separately. But this is no longer the case, and keeping it together makes more sense. Reviewed-by: John Naylor john.naylor [AT] enterprisedb.com Discussion: https://www.postgresql.org/message-id/flat/c7cc82d6-f976-75d6-2e3e-b03d2cab26bb@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/bdc4edbea6fc847f806e1e7118d730e159512bfc Fix redundant error messages in client tools. A few client tools duplicate error messages already provided by libpq. Discussion: https://www.postgresql.org/message-id/flat/3e937641-88a1-e697-612e-99bba4b8e5e4%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/6be725e701611660b36642de9ff1d665a1eb24f5 Fix test for error message change. fix for 6be725e701611660b36642de9ff1d665a1eb24f5 https://git.postgresql.org/pg/commitdiff/8cff66d309b90e03f1e76494375960f35eff6424
Tomáš Vondra pushed: Fix get_useful_pathkeys_for_relation for volatile expressions. When considering Incremental Sort below a Gather Merge, we need to be a bit more careful when matching pathkeys to EC members. It's not enough to find a member whose Vars are all in the current relation's target; volatile expressions in particular need to be contained in the target, otherwise it's too early to use the pathkey. Reported-by: Jaime Casanova Author: James Coleman Reviewed-by: Tomas Vondra Backpatch-through: 13, where the incremental sort code was added Discussion: https://postgr.es/m/CAJGNTeNaxpXgBVcRhJX%2B2vSbq%2BF2kJqGBcvompmpvXb7pq%2BoFA%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/ebb7ae839d033d0f279670e249f54646a08b8c48 Use INT64_FORMAT to print int64 variables in sort debug. Commit 6ee3b5fb99 cleaned up most of the long/int64 confusion related to incremental sort, but the sort debug messages were still using %ld for int64 variables. So fix that. Author: Haiying Tang Backpatch-through: 13, where the incremental sort code was added Discussion: https://postgr.es/m/4250be9d350c4992abb722a76e288aef%40G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/90851d1d26f54ccb4d7b1bc49449138113d6ec83 Properly detoast data in brin_form_tuple. brin_form_tuple failed to consider the values may be toasted, inserting the toast pointer into the index. This may easily result in index corruption, as the toast data may be deleted and cleaned up by vacuum. The cleanup however does not care about indexes, leaving invalid toast pointers behind, which triggers errors like this: ERROR: missing chunk number 0 for toast value 16433 in pg_toast_16426 A less severe consequence are inconsistent failures due to the index row being too large, depending on whether brin_form_tuple operated on plain or toasted version of the row. For example CREATE TABLE t (val TEXT); INSERT INTO t VALUES ('... long value ...') CREATE INDEX idx ON t USING brin (val); would likely succeed, as the row would likely include toast pointer. Switching the order of INSERT and CREATE INDEX would likely fail: ERROR: index row size 8712 exceeds maximum 8152 for index "idx" because this happens before the row values are toasted. The bug exists since PostgreSQL 9.5 where BRIN indexes were introduced. So backpatch all the way back. Author: Tomas Vondra Reviewed-by: Alvaro Herrera Backpatch-through: 9.5 Discussion: https://postgr.es/m/20201001184133.oq5uq75sb45pu3aw@development Discussion: https://postgr.es/m/20201104010544.zexj52mlldagzowv%40development https://git.postgresql.org/pg/commitdiff/7577dd84807a808f295dbfb0191540750ff98002
Fujii Masao pushed: Peter Geoghegan pushed: Fix nbtree cleanup-only VACUUM stats inaccuracies. Logic for counting heap TIDs from posting list tuples (added by commit 0d861bbb) was faulty. It didn't count any TIDs/index tuples in the event of no callback being set. This meant that we incorrectly counted no index tuples in clean-up only VACUUMs, which could lead to pg_class.reltuples being spuriously set to 0 in affected indexes. To fix, go back to counting items from the page in cases where there is no callback. This approach isn't very accurate, but it works well enough in practice while avoiding the expense of accessing every index tuple during cleanup-only VACUUMs. Author: Peter Geoghegan pg [AT] bowt.ie Reported-By: Jehan-Guillaume de Rorthais jgdr [AT] dalibo.com https://postgr.es/m/20201023174451.69e358f1@firost Backpatch: 13-, where nbtree deduplication was introduced https://git.postgresql.org/pg/commitdiff/48e1291342dd7771cf8c67aa1d7ec1f394b95dd8 Fix wal_consistency_checking nbtree bug. wal_consistency_checking indicated an inconsistency in certain cases involving nbtree page deletion. The underlying issue is that there was a minor difference between the page image produced after a REDO routine ran and the corresponding page image following original execution. This harmless inconsistency has been around forever. We more or less expect total consistency among even deleted nbtree pages these days, though, so this won't do anymore. To fix, tweak the REDO routine to match original execution. Oversight in commit f47b5e13. https://git.postgresql.org/pg/commitdiff/efc5dcfd8ad4e1df633025d8a91b64cd44d93f42 Improve nbtree README's LP_DEAD section. The description of how LP_DEAD bit setting by index scans works following commit 2ed5b87f was rather unclear. Clean that up a bit. Also refer to LP_DEAD bit setting within _bt_check_unique() at the start of the same section. This mechanism may actually be more important than the generic kill_prior_tuple mechanism that the section focuses on, so it at least deserves to be mentioned in passing. https://git.postgresql.org/pg/commitdiff/5a2f154a2ecaf545000a3ff3cdbadc76ae1df30a
Álvaro Herrera pushed: Pending PatchesNikhil Benesch sent in a patch to support negative indexes in the split_part() function, those counting from the end of the array instead of the start. Justin Pryzby sent in another revision of a patch to refactor CIC to rely on REINDEX CONCURRENTLY, refactor same to allow reindexing all index partitions at once, and make ReindexPartitions() set indisvalid so things that come by later can see they're ready to go. Magnus Hagander sent in another revision of a patch to remove the obsolete analyze_new_cluster.sh script and things that know about it from pg_upgrade. Anastasia Lubennikova sent in another revision of a patch to teach COPY FREEZE to set PD_ALL_VISIBLE and visibility map bits. David G. Johnston sent in another revision of a patch to clarify the fact that signal functions have no feedback. Heikki Linnakangas sent in four revisions of a patch to split copy.c into copyto.c and copyfrom.c, and further split copyfrom.c into copyfrom.c and copyfromparse.c. This will make working on the usually independent functionalities of the split files more convenient and easier to read. Álvaro Herrera sent in another revision of a patch to add batch/pipelining support to libpq. Pavel Stěhule sent in a patch to reduce overhead of execution of CALL statement in no atomic mode from PL/pgSQL. Magnus Hagander sent in two revisions of a patch to remove the obsolete -O switch for postgres, which once allowed passing options to each server process. Kyotaro HORIGUCHI sent in two more revisions of a patch to fix a dereference before NULL check in src/backend/storage/ipc/latch.c. David Rowley sent in two revisions of a patch to reduce the number of special cases to build contrib modules on windows. Konstantin Knizhnik sent in three more revisions of a patch to add custom compression to libpq. Fabien COELHO sent in another revision of a patch to pgbench to add an option which delays queries until connections are established. Thomas Munro and David Rowley traded patches to implement collation versioning. Jinbao Chen sent in a patch to add a new table am 'tid_visible' to test the visibility of a tid. Peter Geoghegan sent in another revision of a patch to add delete deduplication to nbtree. Stephen Frost sent in two more revisions of a patch to use pre-fetching for ANALYZE. Tomáš Vondra sent in another revision of a patch to use INT64_FORMAT to print int64 variables in sort debug. Bharath Rupireddy sent in another revision of a patch to use multi Inserts in Create Table As. Amit Langote sent in another revision of a patch to call BeginDirectModify from ExecInitModifyTable, and initialize result relation information lazily. This work builds infrastructure that will later be used to make writes on foreign tables more efficient. Vigneshwaran C sent in two more revisions of a patch to improve the connection authorization message for GSS authenticated/encrypted connections by adding a log message to include GSS authentication, encryption, and principal information. This message will help the user to know whether GSS authentication or encryption was used and which GSS principal was used. Tomáš Vondra sent in three more revisions of a patch to implement BRIN multi-range indexes. Álvaro Herrera sent in another revision of a patch to implement ALTER TABLE ... DETACH CONCURRENTLY. Tsutomu Yamada sent in another revision of a patch to add \dX, which lists extended statistics, to psql. Pavel Borisov sent in two more revisions of a patch to deprecate and replace <^ and >^ operators for points. Melanie Plageman sent in another revision of a patch to support parallel FULL JOIN and RIGHT JOIN. Kyotaro HORIGUCHI sent in two more revisions of a patch to use shared memory instead of files for storage in the stats collector. Ajin Cherian and Peter Smith traded patches to add logical decoding of two-phase transactions. Kirk Jamison sent in another revision of a patch to make DropRelFileNodeBuffers() more efficient during recovery by avoiding scanning the whole buffer pool when the relation is small enough, or the the total number of blocks to be invalidated is below the threshold of full scanning. Daniel Gustafsson sent in two more revisions of a patch to support NSS as a TLS backend for libpq. Takamichi Osumi sent in three more revisions of a patch to implement CREATE OR REPLACE TRIGGER. Fujii Masao sent in another revision of a patch to use standard SIGHUP and SIGTERM handlers in the autoprewarm process. Justin Pryzby sent in another revision of a patch to Implement CLUSTER of partitioned table. This requires either specification of a partitioned index on which to cluster, or that an partitioned index was previously set clustered. Kyotaro HORIGUCHI sent in another revision of a patch to fix NaN handling in some geometric operators and functions. Michaël Paquier sent in another revision of a patch to rework the SHA2 APIs, switch sha2_openssl.c to use EVP, and make pgcrypto use the in-core resowner facility for EVP. Yuzuko Hosoya sent in another revision of a patch to fix some infelicities between autovacuum and partitioned tables. Justin Pryzby sent in another revision of a patch to make pg_ls_* show directories and shared filesets. Seino Yuki sent in another revision of a patch to enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW. Kyotaro HORIGUCHI sent in two more revisions of a patch to implement CatCache expiration. David Pirotte sent in another revision of a patch to add logical decoding messages to pgoutput. Masahiko Sawada sent in two more revisions of a patch to implement a transaction manager for foreign transactions. Masahiro Ikeda sent in another revision of a patch to add statistics to the pg_stat_wal view. Justin Pryzby sent in a patch atop the one for incremental view maintenance patch which fixes some documentation. Michaël Paquier sent in a patch to refactor the MD5 implementations to be just one, and switch to EVP for OpenSSL. Justin Pryzby sent in another revision of a patch to clarify the computation of min/max IO and specifically the double use and effect of correlation, avoid re-using the "pages_fetched" variable, and use the correlation statistic in costing bitmap scans as for an index scan. Sergei Kornilov sent in another revision of a patch to allow some recovery parameters to be changed with reload. Marina Polyakova sent in two revisions of a patch to fix a bug that manifested as pgbench no longer supporting a large number of client connections on Windows. Andrey Borodin sent in another revision of a patch to add Sortsupport for sorting GiST build for gist_btree types. Jürgen Purtz and Erik Rijkers traded patches to add an architecture chapter to the tutorial. Dilip Kumar sent in another revision of a patch to implement custom table compression methods. Tomáš Vondra sent in a patch to remove some duplicate code from brin_memtuple_initialize. |