== PostgreSQL Weekly News - April 5, 2020 == - Mailing list pgsql-announce
| From | David Fetter |
|---|---|
| Subject | == PostgreSQL Weekly News - April 5, 2020 == |
| Date | |
| Msg-id | 20200405203051.GA13186@fetter.org Whole thread Raw |
| List | pgsql-announce |
== PostgreSQL Weekly News - April 5, 2020 == The Code of Conduct Committee 2019 Annual Report has been published. https://www.postgresql.org/about/policies/coc/reports/2019/ PostgreSQL Ibiza has been cancelled due to COVID-19. == PostgreSQL Product News == pg_timeout 0.0.1, an extension that makes it possible to set database idle session timeout with GUC parameters and a dedicated background worker, released. https://github.com/pierreforstmann/pg_timeout Joe 0.6.0, a Slack chatbot that helps backend developers and DBAs troubleshoot and optimize PostgreSQL queries, releaesd. https://gitlab.com/postgres-ai/joe/-/releases#0.6.0 pg_logqueryid, an extension that enables logging pg_stat_statements queryid when auto_explain is enabled, released. https://github.com/pierreforstmann/pg_logqueryid pgAdmin4 4.20, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_20.html == PostgreSQL Jobs for April == http://archives.postgresql.org/pgsql-jobs/2020-04/ == PostgreSQL Local == PGCon 2020 will take place online on May 26-29, 2020. https://www.pgcon.org/2020/ PostgresLondon 2020 will be July 7-8, 2020 with an optional training day on July 6. http://postgreslondon.org PG Day Russia will be in Saint Petersburg on July 10, 2020. The CfP is open at https://pgday.ru/en/2020/for-speakers through April 6, 2020. https://pgday.ru/en/2020/ FOSS4G 2020, will take place in Calgary, Alberta, Canada August 24-29 2020. the Call for Papers is currently open at https://2020.foss4g.org/speakers/ https://2020.foss4g.org/ PGDay Ukraine will take place September 5th, 2020 in Lviv at the Bank Hotel. https://pgday.org.ua/ pgDay Israel 2020 will take place on September 10, 2020 in Tel Aviv. http://pgday.org.il/ PGDay Austria will take place September 18, 2020 at Schloss Schoenbrunn (Apothekertrakt) in Vienna. The CfP is open until April 19, 2020 at https://pgday.at/en/talk-commitee/ https://pgday.at/en/ == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm PST8PDT to david@fetter.org. == Applied Patches == Tom Lane pushed: - Doc: correct misstatement about ltree label maximum length. The documentation says that the max length is 255 bytes, but code inspection says it's actually 255 characters; and relevant lengths are stored as uint16 so that that works. https://git.postgresql.org/pg/commitdiff/122b0ccfef068b0c0c3716c83a93173866e454aa - Cosmetic improvements in ltree code. Add more comments in ltree.h, and correct a misstatement or two. Use a symbol, rather than hardwired constants, for the maximum length of an ltree label. The max length is still hardwired in the associated error messages, but I want to clean that up as part of a separate patch to improve the error messages. https://git.postgresql.org/pg/commitdiff/2743d9ae4a490a7d96b5c19d50694bd101a87dc8 - Be more careful about extracting encoding from locale strings on Windows. GetLocaleInfoEx() can fail on strings that setlocale() was perfectly happy with. A common way for that to happen is if the locale string is actually a Unix-style string, say "et_EE.UTF-8". In that case, what's after the dot is an encoding name, not a Windows codepage number; blindly treating it as a codepage number led to failure, with a fairly silly error message. Hence, check to see if what's after the dot is all digits, and if not, treat it as a literal encoding name rather than a codepage number. This will do the right thing with many Unix-style locale strings, and produce a more sensible error message otherwise. Somewhat independently of that, treat a zero (CP_ACP) result from GetLocaleInfoEx() as meaning that we must use UTF-8 encoding. Back-patch to all supported branches. Juan José Santamaría Flecha Discussion: https://postgr.es/m/24905.1585445371@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/8c49454caa636a02aa37e10b8941b7e67b6954bb - Improve error messages in ltree_in and lquery_in. Ensure that the type name is mentioned in all cases (bare "syntax error" isn't that helpful). Avoid using the term "level", since that's not used in the documentation. Phrase error position reports as "at character N" not "in position N"; the latter seems ambiguous, and it's certainly not how we say it elsewhere. For the same reason, make the character position values be 1-based not 0-based. Provide a position in more cases. (I continued to leave that out of messages complaining about end-of-input, where it seemed pointless, as well as messages complaining about overall input complexity, where fingering any one part of the input would be arbitrary.) Discussion: https://postgr.es/m/15582.1585529626@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e07e2a40bd0c3c02a9baf2e5ddccf665e73208fb - Fix lquery's NOT handling, and add ability to quantify non-'*' items. The existing implementation of the ltree ~ lquery match operator is sufficiently complex and undocumented that it's hard to tell exactly what it does. But one thing it clearly gets wrong is the combination of NOT symbols (!) and '*' symbols. A pattern such as '*.!foo.*' should, by any ordinary understanding of regular expression behavior, match any ltree that has at least one label that's not "foo". As best we can tell by experimentation, what it's actually matching is any ltree in which *no* label is "foo". That's surprising, and not at all what the documentation says. Now, that's arguably a useful behavior, so if we rewrite to fix the bug we should provide some other way to get it. To do so, add the ability to attach lquery quantifiers to non-'*' items as well as '*'s. Then the pattern '!foo{,}' expresses "any ltree in which no label is foo". For backwards compatibility, the default quantifier for non-'*' items has to be "{1}", although the default for '*' items is '{,}'. I wouldn't have done it like that in a green field, but it's not totally horrible. Armed with that, rewrite checkCond() from scratch. Treating '*' and non-'*' items alike makes it simpler, not more complicated, so that the function actually gets a lot shorter than it was. Filip Rembiałkowski, Tom Lane, Nikita Glukhov, per a very ancient bug report from M. Palm Discussion: https://postgr.es/m/CAP_rww=waX2Oo6q+MbMSiZ9ktdj6eaJj0cQzNu=Ry2cCDij5fw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/70dc4c509b330fdd965d795e8d7f41f09d56c9ae - Teach pg_ls_dir_files() to ignore ENOENT failures from stat(). Buildfarm experience shows that this function can fail with ENOENT if some other process unlinks a file between when we read the directory entry and when we try to stat() it. The problem is old but we had not noticed it until 085b6b667 added regression test coverage. To fix, just ignore ENOENT failures. There is one other case that this might hide: a symlink that points to nowhere. That seems okay though, at least better than erroring. Back-patch to v10 where this function was added, since the regression test cases were too. Discussion: https://postgr.es/m/20200308173103.GC1357@telsasoft.com https://git.postgresql.org/pg/commitdiff/82e801852274e46492b0e160624a850157c677e4 - Still another try at stabilizing stats_ext test results. The stats_ext test is not expecting that autovacuum will touch any of its tables; an expectation falsified by commit b07642dbc. Although I'm suspicious that there's something else going on that makes extended stats estimates not 100% reproducible, it's pretty easy to demonstrate that there are places in this test that fail if an autovacuum updates the table's stats unexpectedly. Hence, revert the band-aid changes made by 2dc16efed and 24566b359 in favor of summarily disabling autovacuum for all the tables that this test checks estimated rowcounts for. Also remove an evidently obsolete comment at the head of the test. Discussion: https://postgr.es/m/15012.1585623298@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0936d1b6ffc6d59b4d17f3d6e53c036110c02b44 - Fix race condition in statext_store(). Must hold some lock on the pg_statistic_ext_data catalog *before* we look up the tuple we aim to replace. Otherwise a concurrent VACUUM FULL or similar operation could move it to a different TID, leaving us trying to replace the wrong tuple. Back-patch to v12 where this got broken. Credit goes to Dean Rasheed; I'm just doing the clerical work. Discussion: https://postgr.es/m/CAEZATCU0zHMDiQV0g8P2U+YSP9C1idUPrn79DajsbonwkN0xvQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/fe3036527a1ff715bceb22ff5cba919001262a71 - Improve selectivity estimation for assorted match-style operators. Quite a few matching operators such as JSONB's @> used "contsel" and "contjoinsel" as their selectivity estimators. That was a bad idea, because (a) contsel is only a stub, yielding a fixed default estimate, and (b) that default is 0.001, meaning we estimate these operators as five times more selective than equality, which is surely pretty silly. There's a good model for improving this in ltree's ltreeparentsel(): for any "var OP constant" query, we can try applying the operator to all of the column's MCV and histogram values, taking the latter as being a random sample of the non-MCV values. That code is actually 100% generic, except for the question of exactly what default selectivity ought to be plugged in when we don't have stats. Hence, migrate the guts of ltreeparentsel() into the core code, provide wrappers "matchingsel" and "matchingjoinsel" with a more-appropriate default estimate, and use those for the non-geometric operators that formerly used contsel (mostly JSONB containment operators and tsquery matching). Also apply this code to some match-like operators in hstore, ltree, and pg_trgm, including the former users of ltreeparentsel as well as ones that improperly used contsel. Since commit 911e70207 just created new versions of those extensions that we haven't released yet, we can sneak this change into those new versions instead of having to create an additional generation of update scripts. Patch by me, reviewed by Alexey Bashtanov Discussion: https://postgr.es/m/12237.1582833074@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a80818605e5447b9b846590c3d3fab99060cb53e - Check equality semantics for unique indexes on partitioned tables. We require the partition key to be a subset of the set of columns being made unique, so that physically-separate indexes on the different partitions are sufficient to enforce the uniqueness constraint. The existing code checked that the listed columns appear, but did not inquire into the index semantics, which is a serious oversight given that different index opclasses might enforce completely different notions of uniqueness. Ideally, perhaps, we'd just match the partition key opfamily to the index opfamily. But hash partitioning uses hash opfamilies which we can't directly match to btree opfamilies. Hence, look up the equality operator in each family, and accept if it's the same operator. This should be okay in a fairly general sense, since the equality operator ought to precisely represent the opfamily's notion of uniqueness. A remaining weak spot is that we don't have a cross-index-AM notion of which opfamily member is "equality". But we know which one to use for hash and btree AMs, and those are the only two that are relevant here at present. (Any non-core AMs that know how to enforce equality are out of luck, for now.) Back-patch to v11 where this feature was introduced. Guancheng Luo, revised a bit by me Discussion: https://postgr.es/m/D9C3CEF7-04E8-47A1-8300-CA1DCD5ED40D@gmail.com https://git.postgresql.org/pg/commitdiff/501b0187998c24d4a950459d9bf5e67f9f3e4a25 - Add support for binary I/O of ltree, lquery, and ltxtquery types. Not much to say here --- does what it says on the tin. The "binary" representation in each case is really just the same as the text format, though we prefix a version-number byte in case anyone ever feels motivated to change that. Thus, there's not any expectation of improved speed or reduced space; the point here is just to allow clients to use binary format for all columns of a query result or COPY data. This makes use of the recently added ALTER TYPE support to add binary I/O functions to an existing data type. As in commit a80818605, we can piggy-back on there already being a new-for-v13 version of the ltree extension, so we don't need a new update script file. Nino Floris, reviewed by Alexander Korotkov and myself Discussion: https://postgr.es/m/CANmj9Vxx50jOo1L7iSRxd142NyTz6Bdcgg7u9P3Z8o0=HGkYyQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/949a9f043eb70a4986041b47513579f9a13d6a33 - Clean up parsing of ltree and lquery some more. Fix lquery parsing to handle repeated flag characters correctly, and to enforce the max label length correctly in some cases where it did not before, and to detect empty labels in some cases where it did not before. In a more cosmetic vein, use a switch rather than if-then chains to handle the different states, and avoid unnecessary checks on charlen when looking for ASCII characters, and factor out multiple copies of the label length checking code. Tom Lane and Dmitry Belyavsky Discussion: https://postgr.es/m/CADqLbzLVkBuPX0812o+z=c3i6honszsZZ6VQOSKR3VPbB56P3w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/17ca067995114ee40749d9138ba85fdd68518052 - Improve user control over truncation of logged bind-parameter values. This patch replaces the boolean GUC log_parameters_on_error introduced by commit ba79cb5dc with an integer log_parameter_max_length_on_error, adding the ability to specify how many bytes to trim each logged parameter value to. (The previous coding hard-wired that choice at 64 bytes.) In addition, add a new parameter log_parameter_max_length that provides similar control over truncation of query parameters that are logged in response to statement-logging options, as opposed to errors. Previous releases always logged such parameters in full, possibly causing log bloat. For backwards compatibility with prior releases, log_parameter_max_length defaults to -1 (log in full), while log_parameter_max_length_on_error defaults to 0 (no logging). Per discussion, log_parameter_max_length is SUSET since the DBA should control routine logging behavior, but log_parameter_max_length_on_error is USERSET because it also affects errcontext data sent back to the client. Alexey Bashtanov, editorialized a little by me Discussion: https://postgr.es/m/b10493cc-a399-a03a-67c7-068f2791ee50@imap.cc https://git.postgresql.org/pg/commitdiff/0b34e7d307e6a142ee94800e6d5f3e73449eeffd - Improve stability fix for partition_aggregate test. Instead of disabling autovacuum on these test tables, adjust the partition boundaries so that the child partitions are not all the same size. That should cause the planner to use a predictable ordering of the per-partition scan nodes even in cases where autovacuum causes the rowcount estimates to be off a bit. Moreover, this also lets these tests show that the planner does properly order the tables in descending size order, something that wasn't being proven before. The pagg_tab1 and pagg_tab2 partitions are still all the same size, but that should be fine, because those tables are so small that (1) autovacuum won't fire on them, and (2) even if it did, it couldn't change the reltuples value --- with only one page, it can't see just part of the relation. Discussion: https://postgr.es/m/24467.1585838693@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7cb0a423f914af6936d13a8c7f2e35c0a4e4bd14 - Fix bogus CALLED_AS_TRIGGER() defenses. contrib/lo's lo_manage() thought it could use trigdata->tg_trigger->tgname in its error message about not being called as a trigger. That naturally led to a core dump. unique_key_recheck() figured it could Assert that fcinfo->context is a TriggerData node in advance of having checked that it's being called as a trigger. That's harmless in production builds, and perhaps not that easy to reach in any case, but it's logically wrong. The first of these per bug #16340 from William Crowell; the second from manual inspection of other CALLED_AS_TRIGGER call sites. Back-patch the lo.c change to all supported branches, the other to v10 where the thinko crept in. Discussion: https://postgr.es/m/16340-591c7449dc7c8c47@postgresql.org https://git.postgresql.org/pg/commitdiff/6dd9f357792545b626e28b2e1f73cb4c32c437f1 - Fix bugs in gin_fuzzy_search_limit processing. entryGetItem()'s three code paths each contained bugs associated with filtering the entries for gin_fuzzy_search_limit. The posting-tree path failed to advance "advancePast" after having decided to filter an item. If we ran out of items on the current page and needed to advance to the next, what would actually happen is that entryLoadMoreItems() would re-load the same page. Eventually, the random dropItem() test would accept one of the same items it'd previously rejected, and we'd move on --- but it could take awhile with small gin_fuzzy_search_limit. To add insult to injury, this case would inevitably cause entryLoadMoreItems() to decide it needed to re-descend from the root, making things even slower. The posting-list path failed to implement gin_fuzzy_search_limit filtering at all, so that all entries in the posting list would be returned. The bitmap-result path used a "gotitem" variable that it failed to update in the one place where it'd actually make a difference, ie at the one "continue" statement. I think this was unreachable in practice, because if we'd looped around then it shouldn't be the case that the entries on the new page are before advancePast. Still, the "gotitem" variable was contributing nothing to either clarity or correctness, so get rid of it. Refactor all three loops so that the termination conditions are more alike and less unreadable. The code coverage report showed that we had no coverage at all for the re-descend-from-root code path in entryLoadMoreItems(), which seems like a very bad thing, so add a test case that exercises it. We also had exactly no coverage for gin_fuzzy_search_limit, so add a simplistic test case that at least hits those code paths a little bit. Back-patch to all supported branches. Adé Heyward and Tom Lane Discussion: https://postgr.es/m/CAEknJCdS-dE1Heddptm7ay2xTbSeADbkaQ8bU2AXRCVC2LdtKQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e41955faf060f90918303ce0623df9d765144bf6 - Cosmetic improvements for code related to partitionwise join. Move have_partkey_equi_join and match_expr_to_partition_keys to relnode.c, since they're used only there. Refactor build_joinrel_partition_info to split out the code that fills the joinrel's partition key lists; this doesn't have any non-cosmetic impact, but it seems like a useful separation of concerns. Improve assorted nearby comments. Amit Langote, with a little further editorialization by me Discussion: https://postgr.es/m/CA+HiwqG2WVUGmLJqtR0tPFhniO=H=9qQ+Z3L_ZC+Y3-EVQHFGg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0568e7a2a4f133a7c16776bcae92c53fcf247b73 - Remove bogus Assert, add some regression test cases showing why. Commit 77ec5affb added an assertion to enforce_generic_type_consistency that boils down to "if the function result is polymorphic, there must be at least one polymorphic argument". This should be true for user-created functions, but there are built-in functions for which it's not true, as pointed out by Jaime Casanova. Hence, go back to the old behavior of leaving the return type alone. There's only a limited amount of stuff you can do with such a function result, but it does work to some extent; add some regression test cases to ensure we don't break that again. Discussion: https://postgr.es/m/CAJGNTeMbhtsCUZgJJ8h8XxAJbK7U2ipsX8wkHRtZRz-NieT8RA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/07871d40c72e498b6e034eb674df5d8d206976bc - Further improve stability fix for partition_aggregate test. Commit 7cb0a423f overlooked that the multi-level partition test table pagg_tab_ml still had an exactly even row split at its upper level of partitioning, so that some of the sub-aggregation plan steps still had exactly equal costs, leading to plan instability. Tweak the partition boundaries some more to make the row distribution unequal at both levels. This leads to more changes in the "expected" plan order than the previous round, but it seems fine. (Actually, I'm surprised that this didn't affect even more plans in this test: looking at the underlying costs shows that some of the parallel plan groups are *not* getting sorted by cost. Bug?) Per buildfarm member lousyjack, https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=lousyjack&dt=2020-04-04%2021%3A03%3A04 Discussion: https://postgr.es/m/24467.1585838693@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/18d85e9b8a2b784bcee350c59cf20c5c697a1c1f Amit Kapila pushed: - Introduce vacuum errcontext to display additional information. The additional information displayed will be block number for error occurring while processing heap and index name for error occurring while processing the index. This will help us in diagnosing the problems that occur during a vacuum. For ex. due to corruption (either caused by bad hardware or by some bug) if we get some error while vacuuming, it can help us identify the block in heap and or additional index information. It sets up an error context callback to display additional information with the error. During different phases of vacuum (heap scan, heap vacuum, index vacuum, index clean up, heap truncate), we update the error context callback to display appropriate information. We can extend it to a bit more granular level like adding the phases for FSM operations or for prefetching the blocks while truncating. However, I felt that it requires adding many more error callback function calls and can make the code a bit complex, so left those for now. Author: Justin Pryzby, with few changes by Amit Kapila Reviewed-by: Alvaro Herrera, Amit Kapila, Andres Freund, Michael Paquier and Sawada Masahiko Discussion: https://www.postgresql.org/message-id/20191120210600.GC30362@telsasoft.com https://git.postgresql.org/pg/commitdiff/b61d161c146328ae6ba9ed937862d66e5c8b035a - Avoid calls to RelationGetRelationName() and RelationGetNamespace() in.vacuum code. After commit b61d161c14, during vacuum, we cache the information of relation name and relation namespace in local structure LVRelStats so that we can use it in an error callback function. We can use the cached information to avoid the calls to RelationGetRelationName(), RelationGetNamespace() and get_namespace_name(). This is mainly for the consistent in vacuum code path but it will avoid the extra syscache lookup we do in get_namespace_name(). Author: Justin Pryzby Reviewed-by: Amit Kapila Discussion: https://www.postgresql.org/message-id/20191120210600.GC30362@telsasoft.com https://git.postgresql.org/pg/commitdiff/ef75140fe756e6460524b067e063beac109853ec - Fix coverity complaint about commit 40d964ec99. The coverity complained that dividing integer expressions and then converting the integer quotient to type "double" would lose fractional part. Typecasting one of the arguments of expression with double should fix the report. Author: Mahendra Singh Thalor Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/20200329224818.6phnhv7o2q2rfovf@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/2401d93718310237b3cb1ff914abc1bcbdd8e1dc - Allow parallel vacuum to accumulate buffer usage. Commit 40d964ec99 allowed vacuum command to process indexes in parallel but forgot to accumulate the buffer usage stats of parallel workers. This allows leader backend to accumulate buffer usage stats of all the parallel workers. Reported-by: Julien Rouhaud Author: Sawada Masahiko Reviewed-by: Dilip Kumar, Amit Kapila and Julien Rouhaud Discussion: https://postgr.es/m/20200328151721.GB12854@nol https://git.postgresql.org/pg/commitdiff/3a5e22138a8d014590834eb808c99a436c246aab - Add infrastructure to track WAL usage. This allows gathering the WAL generation statistics for each statement execution. The three statistics that we collect are the number of WAL records, the number of full page writes and the amount of WAL bytes generated. This helps the users who have write-intensive workload to see the impact of I/O due to WAL. This further enables us to see approximately what percentage of overall WAL is due to full page writes. In the future, we can extend this functionality to allow us to compute the the exact amount of WAL data due to full page writes. This patch in itself is just an infrastructure to compute WAL usage data. The upcoming patches will expose this data via explain, auto_explain, pg_stat_statements and verbose (auto)vacuum output. Author: Kirill Bychik, Julien Rouhaud Reviewed-by: Dilip Kumar, Fujii Masao and Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/df3b181499b40523bd6244a4e5eb554acb9020ce - Allow pg_stat_statements to track WAL usage statistics. This commit adds three new columns in pg_stat_statements output to display WAL usage statistics added by commit df3b181499. This commit doesn't bump the version of pg_stat_statements as the same is done for this release in commit 17e0328224. Author: Kirill Bychik and Julien Rouhaud Reviewed-by: Julien Rouhaud, Fujii Masao, Dilip Kumar and Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6b466bf5f2bea0c89fab54eef696bcfc7ecdafd7 Fujii Masao pushed: - Expose BufferUsageAccumDiff(). Previously pg_stat_statements calculated the difference of buffer counters by its own code even while BufferUsageAccumDiff() had the same code. This commit expose BufferUsageAccumDiff() and makes pg_stat_statements use it for the calculation, in order to simply the code. This change also would be useful for the upcoming patch for the planning counters in pg_stat_statements because the patch will add one more code for the calculation of difference of buffer counters and that can easily be done by using BufferUsageAccumDiff(). Author: Julien Rouhaud Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/bdfee4e0-a304-2498-8da5-3cb52c0a193e@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/4a539a25ebfc48329fd656a95f3c1eb2cda38af3 - Allow the planner-related functions and hook to accept the query string. This commit adds query_string argument into the planner-related functions and hook and allows us to pass the query string to them. Currently there is no user of the query string passed. But the upcoming patch for the planning counters will add the planning hook function into pg_stat_statements and the function will need the query string. So this change will be necessary for that patch. Also this change is useful for some extensions that want to use the query string in their planner hook function. Author: Pascal Legrand, Julien Rouhaud Reviewed-by: Yoshikazu Imai, Tom Lane, Fujii Masao Discussion: https://postgr.es/m/CAOBaU_bU1m3_XF5qKYtSj1ua4dxd=FWDyh2SH4rSJAUUfsGmAQ@mail.gmail.com Discussion: https://postgr.es/m/1583789487074-0.post@n3.nabble.com https://git.postgresql.org/pg/commitdiff/6aba63ef3e606db71beb596210dd95fa73c44ce2 - Report waiting via PS while recovery is waiting for buffer pin in hot standby. Previously while the startup process was waiting for the recovery conflict with snapshot, tablespace or lock to be resolved, waiting was reported in PS display, but not in the case of recovery conflict with buffer pin. This commit makes the startup process in hot standby report waiting via PS while waiting for the conflicts with other backends holding buffer pins to be resolved. Author: Masahiko Sawada Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CA+fd4k4mXWTwfQLS3RPwGr4xnfAEs1ysFfgYHvmmoUgv6Zxvmg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/64638ccba3a659d8b8a3a4bc5b47307685a64a8a - Improve the message logged when recovery is paused. When recovery target is reached and recovery is paused because of recovery_target_action=pause, executing pg_wal_replay_resume() causes the standby to promote, i.e., the recovery to end. So, in this case, the previous message "Execute pg_wal_replay_resume() to continue" logged was confusing because pg_wal_replay_resume() doesn't cause the recovery to continue. This commit improves the message logged when recovery is paused, and the proper message is output based on what (pg_wal_replay_pause or recovery_target_action) causes recovery to be paused. Author: Sergei Kornilov, revised by Fujii Masao Reviewed-by: Robert Haas Discussion: https://postgr.es/m/19168211580382043@myt5-b646bde4b8f3.qloud-c.yandex.net https://git.postgresql.org/pg/commitdiff/b0236508d3589a45e574284cd3303b689111765d - Allow pg_stat_statements to track planning statistics. This commit makes pg_stat_statements support new GUC pg_stat_statements.track_planning. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc. This feature is useful to check the statements that it takes a long time to plan. Previously since pg_stat_statements tracked only the execution statistics, we could not use that for the purpose. The planning and execution statistics are stored at the end of each phase separately. So there are not always one-to-one relationship between them. For example, if the statement is successfully planned but fails in the execution phase, only its planning statistics are stored. This may cause the users to be able to see different pg_stat_statements results from the previous version. To avoid this, pg_stat_statements.track_planning needs to be disabled. This commit bumps the version of pg_stat_statements to 1.8 since it changes the definition of pg_stat_statements function. Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com https://git.postgresql.org/pg/commitdiff/17e03282241c6ac58a714eb0c3b6a8018cf6167a - Include information on buffer usage during planning phase, in EXPLAIN output. When BUFFERS option is enabled, EXPLAIN command includes the information on buffer usage during each plan node, in its output. In addition to that, this commit makes EXPLAIN command include also the information on buffer usage during planning phase, in its output. This feature makes it easier to discern the cases where lots of buffer access happen during planning. Author: Julien Rouhaud, slightly revised by Fujii Masao Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/16109-26a1a88651e90608@postgresql.org https://git.postgresql.org/pg/commitdiff/ed7a5095716ee498ecc406e1b8d5ab92c7662d10 - Add wait events for recovery conflicts. This commit introduces new wait events RecoveryConflictSnapshot and RecoveryConflictTablespace. The former is reported while waiting for recovery conflict resolution on a vacuum cleanup. The latter is reported while waiting for recovery conflict resolution on dropping tablespace. Also this commit changes the code so that the wait event Lock is reported while waiting in ResolveRecoveryConflictWithVirtualXIDs() for recovery conflict resolution on a lock. Basically the wait event Lock is reported during that wait, but previously was not reported only when that wait happened in ResolveRecoveryConflictWithVirtualXIDs(). Author: Masahiko Sawada Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/CA+fd4k4mXWTwfQLS3RPwGr4xnfAEs1ysFfgYHvmmoUgv6Zxvmg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/18808f8c893d4f425f2d21b2a1ffc8e51f1bd0ba - Revert "Include information on buffer usage during planning phase, in EXPLAIN output.". This reverts commit ed7a5095716ee498ecc406e1b8d5ab92c7662d10. Per buildfarm member prion. https://git.postgresql.org/pg/commitdiff/19db23bcbda99e93321cb0636677ec9c6e121a2a - Improve stability of explain regression test. The explain regression test runs EXPLAIN commands via the function that filters unstable outputs. To produce more stable test output, this commit improves the function so that it also filters out text-mode Buffers lines. This is necessary because text-mode Buffers lines vary depending the system state. This improvement will get rid of the regression test failure that the commit ed7a509571 caused on the buildfarm members prion and dory because of the instability of Buffers lines. Author: Fujii Masao Reviewed-by: Tom Lane Discussion: https://postgr.es/m/20200403025751.GB1759@paquier.xyz https://git.postgresql.org/pg/commitdiff/c0885c4c30698a1beef40a8df0edb253750d3b43 - Include information on buffer usage during planning phase, in EXPLAIN output, take two. When BUFFERS option is enabled, EXPLAIN command includes the information on buffer usage during each plan node, in its output. In addition to that, this commit makes EXPLAIN command include also the information on buffer usage during planning phase, in its output. This feature makes it easier to discern the cases where lots of buffer access happen during planning. This commit revives the original commit ed7a509571 that was reverted by commit 19db23bcbd. The original commit had to be reverted because it caused the regression test failure on the buildfarm members prion and dory. But since commit c0885c4c30 got rid of the caues of the test failure, the original commit can be safely introduced again. Author: Julien Rouhaud, slightly revised by Fujii Masao Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/16109-26a1a88651e90608@postgresql.org https://git.postgresql.org/pg/commitdiff/ce77abe63cfc85fb0bc236deb2cc34ae35cb5324 Peter Eisentraut pushed: - Add new part SQL/MDA to information_schema.sql_parts. https://git.postgresql.org/pg/commitdiff/a01e1b8b9d937894a916465643d9707861ffb838 - Improve handling of parameter differences in physical replication. When certain parameters are changed on a physical replication primary, this is communicated to standbys using the XLOG_PARAMETER_CHANGE WAL record. The standby then checks whether its own settings are at least as big as the ones on the primary. If not, the standby shuts down with a fatal error. The correspondence of settings between primary and standby is required because those settings influence certain shared memory sizings that are required for processing WAL records that the primary might send. For example, if the primary sends a prepared transaction, the standby must have had max_prepared_transaction set appropriately or it won't be able to process those WAL records. However, fatally shutting down the standby immediately upon receipt of the parameter change record might be a bit of an overreaction. The resources related to those settings are not required immediately at that point, and might never be required if the activity on the primary does not exhaust all those resources. If we just let the standby roll on with recovery, it will eventually produce an appropriate error when those resources are used. So this patch relaxes this a bit. Upon receipt of XLOG_PARAMETER_CHANGE, we still check the settings but only issue a warning and set a global flag if there is a problem. Then when we actually hit the resource issue and the flag was set, we issue another warning message with relevant information. At that point we pause recovery, so a hot standby remains usable. We also repeat the last warning message once a minute so it is harder to miss or ignore. Reviewed-by: Sergei Kornilov <sk@zsrv.org> Reviewed-by: Masahiko Sawada <masahiko.sawada@2ndquadrant.com> Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/4ad69a4c-cc9b-0dfe-0352-8b1b0cd36c7b@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/246f136e76ecd26844840f2b2057e2c87ec9868d - Allow using Unix-domain sockets on Windows in tests. The test suites currently don't use Unix-domain sockets on Windows. This optionally allows enabling that by setting the environment variable PG_TEST_USE_UNIX_SOCKETS. This should currently be considered experimental. In particular, pg_regress.c contains some comments that the cleanup code for Unix-domain sockets doesn't work correctly under Windows, which hasn't been an problem until now. But it's good enough for locally supervised testing of the functionality. Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com> Discussion: https://www.postgresql.org/message-id/flat/54bde68c-d134-4eb8-5bd3-8af33b72a010@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/1d53432ff940b789c2431ba476a2a6e2db3edf84 - Update SQL features. Set T653 to supported. This has always been possible. https://git.postgresql.org/pg/commitdiff/fc8c3bdde20c4045659f082910cfcbb2b8c9fa4a - Fix INSERT OVERRIDING USER VALUE behavior. The original implementation disallowed using OVERRIDING USER VALUE on identity columns defined as GENERATED ALWAYS, which is not per standard. So allow that now. Expand documentation and tests around this. Author: Dean Rasheed <dean.a.rasheed@gmail.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Vik Fearing <vik@postgresfriends.org> Discussion: https://www.postgresql.org/message-id/flat/CAEZATCVrh2ufCwmzzM%3Dk_OfuLhTTPBJCdFkimst2kry4oHepuQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/de3bbfcc962f24c1a20a17b76c604e5973a05817 - Update SQL features count. The previously listed total of 179 does not appear to be correct for SQL:2016 anymore. (Previous SQL versions had slightly different feature sets, so it's plausible that it was once correct.) The currently correct count is the number of rows in the respective tables in appendix F in SQL parts 2 and 11, minus 2 features that are listed twice. Thus the correct count is currently 177. This also matches the number of Core entries the built documentation currently shows, so it's internally consistent. https://git.postgresql.org/pg/commitdiff/369623492d08703c6e6269c995ce73b73d187416 - Refactor code to look up local replication tuple. This unifies some duplicate code. Author: Amit Langote <amitlangote09@gmail.com> Discussion: https://www.postgresql.org/message-id/CA+HiwqFjYE5anArxvkjr37AQMd52L-LZtz9Ld2QrLQ3YfcYhTw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d8653f468789a75627c2fc82e73e2755ad8d1fb4 - Add some comments to some SQL features. Otherwise, it could be confusing to a reader that some of these well-publicized features are simply listed as unsupported without further explanation. https://git.postgresql.org/pg/commitdiff/c6e0edad465e3774401b7f09ad70bd22e5421858 - doc: Update for Unix-domain sockets on Windows. Update the documentation to reflect that Unix-domain sockets are now usable on Windows. https://git.postgresql.org/pg/commitdiff/580a446c21f83bcddbaf3ce5f1bab72239c11eb6 - Fix whitespace. https://git.postgresql.org/pg/commitdiff/070c3d3937e75e04d36405287353b7eca516555d - Add SQL functions for Unicode normalization. This adds SQL expressions NORMALIZE() and IS NORMALIZED to convert and check Unicode normal forms, per SQL standard. To support fast IS NORMALIZED tests, we pull in a new data file DerivedNormalizationProps.txt from Unicode and build a lookup table from that, using techniques similar to ones already used for other Unicode data. make update-unicode will keep it up to date. We only build and use these tables for the NFC and NFKC forms, because they are too big for NFD and NFKD and the improvement is not significant enough there. Reviewed-by: Daniel Verite <daniel@manitou-mail.org> Reviewed-by: Andreas Karlsson <andreas@proxel.se> Discussion: https://www.postgresql.org/message-id/flat/c1909f27-c269-2ed9-12f8-3ab72c8caf7a@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/2991ac5fc9b3904ca4582be6d323497d7c3d17c9 - Revert "Improve handling of parameter differences in physical replication". This reverts commit 246f136e76ecd26844840f2b2057e2c87ec9868d. That patch wasn't quite complete enough. Discussion: https://www.postgresql.org/message-id/flat/E1jIpJu-0007Ql-CL%40gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/552fcebff04699103cefd2efa71fae1274893dbe - Save errno across LWLockRelease() calls. Fixup for "Drop slot's LWLock before returning from SaveSlotToPath()" Reported-by: Michael Paquier <michael@paquier.xyz> https://git.postgresql.org/pg/commitdiff/a9d9bdd3ad21a73b481911f22279e004679d172e David Rowley pushed: - Attempt to fix unstable regression tests, take 2. Following up on 2dc16efed, petalura has suffered some additional failures in stats_ext which again appear to be around the timing of an autovacuum during the test, causing instability in the row estimates. Again, let's fix this by explicitly performing a VACUUM on the table and not leave it to happen by chance of an autovacuum pass. Discussion: https://postgr.es/m/CAApHDvok5hmXr%2BbUbJe7%2B2sQzWo4B_QzSk7RKFR9fP6BjYXx5g%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/24566b359d095c3800c2a326d88a595722813f58 - Attempt to stabilize partitionwise_aggregate test. In b07642dbc, we added code to trigger autovacuums based on the number of INSERTs into a table. This seems to have cause some destabilization of the regression tests. Likely this is due to an autovacuum triggering mid-test and (per theory from Tom Lane) one of the test's queries causes autovacuum to skip some number of pages, resulting in the reltuples estimate changing. The failure that this is attempting to fix is around the order of subnodes in an Append. Since the planner orders these according to the subnode cost, then it's possible that a small change in the reltuples value changes the subnode's cost enough that it swaps position with one of its fellow subnodes. The failure here only seems to occur on slower buildfarm machines. In this case, lousyjack, which seems have taken over 8 minutes to run just the partitionwise_aggregate test. Such a slow run would increase the chances that the autovacuum launcher would trigger a vacuum mid-test. Faster machines run this test in sub second time, so have a much smaller window for an autovacuum to trigger. Here we fix this by disabling autovacuum on all tables created in the test. Additionally, this reverts the change made in the partitionwise_aggregate test in 2dc16efed. Discussion: https://postgr.es/m/22297.1585797192@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/cefb82d49e2152e844af148a95d4072999dc3c6b Alexander Korotkov pushed: - Implement operator class parameters. PostgreSQL provides set of template index access methods, where opclasses have much freedom in the semantics of indexing. These index AMs are GiST, GIN, SP-GiST and BRIN. There opclasses define representation of keys, operations on them and supported search strategies. So, it's natural that opclasses may be faced some tradeoffs, which require user-side decision. This commit implements opclass parameters allowing users to set some values, which tell opclass how to index the particular dataset. This commit doesn't introduce new storage in system catalog. Instead it uses pg_attribute.attoptions, which is used for table column storage options but unused for index attributes. In order to evade changing signature of each opclass support function, we implement unified way to pass options to opclass support functions. Options are set to fn_expr as the constant bytea expression. It's possible due to the fact that opclass support functions are executed outside of expressions, so fn_expr is unused for them. This commit comes with some examples of opclass options usage. We parametrize signature length in GiST. That applies to multiple opclasses: tsvector_ops, gist__intbig_ops, gist_ltree_ops, gist__ltree_ops, gist_trgm_ops and gist_hstore_ops. Also we parametrize maximum number of integer ranges for gist__int_ops. However, the main future usage of this feature is expected to be json, where users would be able to specify which way to index particular json parts. Catversion is bumped. Discussion: https://postgr.es/m/d22c3a18-31c7-1879-fc11-4c1ce2f5e5af%40postgrespro.ru Author: Nikita Glukhov, revised by me Reviwed-by: Nikolay Shaplov, Robert Haas, Tom Lane, Tomas Vondra, Alvaro Herrera https://git.postgresql.org/pg/commitdiff/911e70207703799605f5a0e8aad9f06cff067c63 - Remove rudiments of supporting procnum == 0 from 911e702077. Early versions of opclass options patch uses zero support procedure as opclass options procedure. This commit removes rudiments of it, which were committed in 911e702077. Also, it implements correct handling of amoptsprocnum == 0. https://git.postgresql.org/pg/commitdiff/851b14b0c69b3773753a3c202bff42c3e4425d8d - Fix missing SP-GiST support in 911e702077. 911e702077 misses setting of amoptsprocnum for SP-GiST. This commit fixes that. https://git.postgresql.org/pg/commitdiff/364bdd0b411343747aeca17708ff7817d7fe0b00 - Improve error reporting in opclasscmds.c. This commit improves error reporting introduced by 911e702077. It puts argument of errmsg() to the single line for easier grepping source for error text. Also it improves wording of errhint(). https://git.postgresql.org/pg/commitdiff/02a5786df24b12c6379ef1b0375b70b8a9fb4925 - Documentation corrections for opclass parameters. Discussion: https://postgr.es/m/20200331024419.GB14618%40telsasoft.com Author: Justin Pryzby https://git.postgresql.org/pg/commitdiff/3f1802e1fdb74a33db176291be27a2ec243511c6 - Correct CREATE INDEX documentation for opclass parameters. Old versions of opclass parameters patch supported ability to specify DEFAULT as the opclass name in CREATE INDEX command. This ability was removed in the final version, but 911e702077 still mentions that in the documentation. https://git.postgresql.org/pg/commitdiff/3eabc62312ef9da7885d2d3380986e0592a0ee5d - Fix typo in contrib/intarray documentation. Reported-by: Erik Rijkers Discussion: https://postgr.es/m/82529ecf9bcc58d5b5cf9f3ffb699881%40xs4all.nl https://git.postgresql.org/pg/commitdiff/4d276ba94fd9b19457aeb5b6d9af00589fe184a0 Peter Geoghegan pushed: - Consistently truncate non-key suffix columns. INCLUDE indexes failed to have their non-key attributes physically truncated away in certain rare cases. This led to physically larger pivot tuples that contained useless non-key attribute values. The impact on users should be negligible, but this is still clearly a regression (Postgres 11 supports INCLUDE indexes, and yet was not affected). The bug appeared in commit dd299df8, which introduced "true" suffix truncation of key attributes. Discussion: https://postgr.es/m/CAH2-Wz=E8pkV9ivRSFHtv812H5ckf8s1-yhx61_WrJbKccGcrQ@mail.gmail.com Backpatch: 12-, where "true" suffix truncation was introduced. https://git.postgresql.org/pg/commitdiff/4b42a89938ac9d2ec06e9d831356407040e9094c - Refactor nbtree high key truncation. Simplify _bt_truncate(), the routine that generates truncated leaf page high keys. Remove a micro-optimization that avoided a second palloc0() call (this was used when a heap TID was needed in the final pivot tuple, though only when the index happened to not be an INCLUDE index). Removing this dubious micro-optimization allows _bt_truncate() to use the index_truncate_tuple() indextuple.c utility routine in all cases. This was already the common case. This commit is a HEAD-only follow up to bugfix commit 4b42a899. https://git.postgresql.org/pg/commitdiff/7c2dbc691c3e19b7d33c78f6c8aacc6e0cab510b - Further simplify nbtree high key truncation. Commit 7c2dbc69 reorganized _bt_truncate() in a way that enables a further simplification that I (pgeoghegan) missed: Since we mark the tuple that is returned to the caller as a pivot tuple before the point where its heap TID is set as of 7c2dbc69, it is possible to use the high level BTreeTupleGetHeapTID() inline function to get an item pointer. Do it that way now. This approach is clearer and more maintainable. https://git.postgresql.org/pg/commitdiff/f01157e2ac8ac4dff8ba159c36edf2fdb7d6704e - Add CREATE INDEX deduplication assertions. Add two assertions that verify the assumptions about posting list tuple space accounting and suffix truncation made within nbtsort.c. https://git.postgresql.org/pg/commitdiff/7dbe290da446544a04ace7d342841070f062a0ed Andres Freund pushed: - Deduplicate PageIsNew() check in lazy_scan_heap(). The recheck isn't needed anymore, as RelationGetBufferForTuple() now extends the relation with RBM_ZERO_AND_LOCK. Previously we needed to handle the fact that relation extension extended the relation and then separately acquired a lock on the page - while expecting that the page is empty. Reported-By: Ranier Vilela Discussion: https://postgr.es/m/CAEudQArA_=J0D5T258xsCY6Xtf6wiH4b=QDPDgVS+WZUN10WDw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d4b34f60c54904bb3647911dfd9d79d8a4fab430 Michaël Paquier pushed: - Revert "Skip redundant anti-wraparound vacuums". This reverts commit 2aa6e33, that added a fast path to skip anti-wraparound and non-aggressive autovacuum jobs (these have no sense as anti-wraparound implies aggressive). With a cluster using a high amount of relations with a portion of them being heavily updated, this could cause autovacuum to lock down, with autovacuum workers attempting repeatedly those jobs on the same relations for the same database, that just kept being skipped. This lock down can be solved with a manual VACUUM FREEZE. Justin King has reported one environment where the issue happened, and Julien Rouhaud and I have been able to reproduce it in a second environment. With a very aggressive autovacuum_freeze_max_age, triggering those jobs with pgbench is a matter of minutes, and hitting the lock down is a lot harder (my local tests failed to do that). Note that anti-wraparound and non-aggressive jobs can only be triggered on a subset of shared catalogs: - pg_auth_members - pg_authid - pg_database - pg_replication_origin - pg_shseclabel - pg_subscription - pg_tablespace While the lock down was possible down to v12, the root cause of those jobs is a much older issue, which needs more analysis. Bonus thanks to Andres Freund for the discussion. Reported-by: Justin King Discussion: https://postgr.es/m/CAE39h22zPLrkH17GrkDgAYL3kbjvySYD1io+rtnAUFnaJJVS4g@mail.gmail.com Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/dd9ac7d5d80608a640bb82cffb6a805ce84cf112 - Move routine definitions of xlogarchive.c to a new header file. The definitions of the routines defined in xlogarchive.c have been part of xlog_internal.h which is included by several frontend tools, but all those routines are only called by the backend. More cleanup could be done within xlog_internal.h, but that's already a nice cut. This will help a follow-up patch for pg_rewind where handling of restore_command is added for frontends. Author: Alexey Kondratov, Michael Paquier Reviewed-by: Álvaro Herrera, Alexander Korotkov Discussion: https://postgr.es/m/a3acff50-5a0d-9a2c-b3b2-ee36168955c1@postgrespro.ru https://git.postgresql.org/pg/commitdiff/616ae3d2b0566e91b49f301bf08410a9972fed93 - Add -c/--restore-target-wal to pg_rewind. pg_rewind needs to copy from the source cluster to the target cluster a set of relation blocks changed from the previous checkpoint where WAL forked up to the end of WAL on the target. Building this list of relation blocks requires a range of WAL segments that may not be present anymore on the target's pg_wal, causing pg_rewind to fail. It is possible to work around this issue by copying manually the WAL segments needed but this may lead to some extra and actually useless work. This commit introduces a new option allowing pg_rewind to use a restore_command while doing the rewind by grabbing the parameter value of restore_command from the target cluster configuration. This allows the rewind operation to be more reliable, so as only the WAL segments needed by the rewind are restored from the archives. In order to be able to do that, a new routine is added to src/common/ to allow frontend tools to restore files from archives using an already-built restore command. This version is more simple than the backend equivalent as there is no need to handle the non-recovery case. Author: Alexey Kondratov Reviewed-by: Andrey Borodin, Andres Freund, Alvaro Herrera, Alexander Korotkov, Michael Paquier Discussion: https://postgr.es/m/a3acff50-5a0d-9a2c-b3b2-ee36168955c1@postgrespro.ru https://git.postgresql.org/pg/commitdiff/a7e8ece41cf7a96d8a9c4c037cdfef304d950831 - Fix crash in psql when attempting to reuse old connection. In a psql session, if the connection to the server is abruptly cut, the referenced connection would become NULL as of CheckConnection(). This could cause a hard crash with psql if attempting to connect by reusing the past connection's data because of a null-pointer dereference with either PQhost() or PQdb(). This issue is fixed by making sure that no reuse of the past connection is done if it does not exist. Issue has been introduced by 6e5f8d4, so backpatch down to 12. Reported-by: Hugh Wang Author: Michael Paquier Reviewed-by: Álvaro Herrera, Tom Lane Discussion: https://postgr.es/m/16330-b34835d83619e25d@postgresql.org Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/8d84dd00123985e739233fa67c9b1d555f33ed03 - Add support for \aset in pgbench. This option is similar to \gset, except that it is able to store all results from combined SQL queries into separate variables. If a query returns multiple rows, the last result is stored and if a query returns no rows, nothing is stored. While on it, add a TAP test for \gset to check for a failure when a query returns multiple rows. Author: Fabien Coelho Reviewed-by: Ibrar Ahmed, Michael Paquier Discussion: https://postgr.es/m/alpine.DEB.2.21.1904081914200.2529@lancre https://git.postgresql.org/pg/commitdiff/9d8ef98800bd291de145fb1be41f0868546e02ab Magnus Hagander pushed: - Fix assorted typos. Author: Daniel Gustafsson <daniel@yesql.se> https://git.postgresql.org/pg/commitdiff/087d3d0583cf292146a7385746d1f5b53eeeaee6 Bruce Momjian pushed: - Allow ecpg to be built stand-alone, allow parallel libpq make. This change defines SHLIB_PREREQS for the libpgport dependency, rather than using a makefile rule. This was broken in PG 12. Reported-by: Filip Janus Discussion: https://postgr.es/m/E5Dc85EGUY4wyG8cjAU0qoEdCJxGK_qhW1s9qSuYq9A@mail.gmail.com Author: Dagfinn Ilmari Mannsåker (for libpq) Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/051fd5e0f99b14d7bd76fb800bd077bf394fecd5 - doc: adjust UPDATE/DELETE's FROM/USING to match SELECT's FROM. Previously the syntax and wording were unclear. Reported-by: Alexey Bashtanov Discussion: https://postgr.es/m/968d4724-8e58-788f-7c45-f7b1813824cc@imap.cc Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/33cd0e5ea65d1fad0a579fa7ef0bab337246c231 - doc: clarify which table creation is used for inheritance part. Previously people might assume that the partition syntax version of CREATE TABLE is to be used for the inheritance partition table example; mention that the non-partitioned version should be used. Reported-by: mib@nic.at Discussion: https://postgr.es/m/158089540905.1098.15071165437284409576@wrigleys.postgresql.org Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/d97d55460bbda698a8d3ca100bee5485255b888f - doc: add namespace column to pg_buffercache example query. Without the namespace, the table name could be ambiguous. Reported-by: adunham@arbormetrix.com Discussion: https://postgr.es/m/158155175140.23798.2189464781144503491@wrigleys.postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/9b2009c4cfe1450228b941aba52e00e6bb938282 - dummy commit. https://git.postgresql.org/pg/commitdiff/834b80464d653c976787f5b5849fa0595678d0a0 - Revert erroroneous commit 834b80464d; my apologies. Backpatch-through: master https://git.postgresql.org/pg/commitdiff/c2da793fd28073603c39d7abfffbc203a9bd4ac0 - doc: clarify when row-level locks are released. They are released just like table-level locks. Also clean up wording. (Uses wording "rolled back to".) Reported-by: me@sillymon.ch Discussion: https://postgr.es/m/158074944048.1095.4309647363871637715@wrigleys.postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/046fd4f364157fca85eadfddb0f0e8e5d7ceef26 - doc: clarify hierarchy of objects: global, db, schema, etc. The previous wording was confusing because it wasn't in decreasing order and had to backtrack. Also clarify role/user wording. Reported-by: jbird@nuna.com Discussion: https://postgr.es/m/158057750885.1123.2806779262588618988@wrigleys.postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/009e422c1b6854870b6b7f6ba0b7e2816395d933 - doc: remove mention of bitwise operators as solely type-limited. There are other operators that have limited number data type support, so just remove the sentence. Reported-by: Sergei Agalakov Discussion: https://postgr.es/m/158032651854.19851.16261832706661813796@wrigleys.postgresql.org Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/92d31085e926253aa650b9d1e1f2f09934d0ddfc - psql: do file completion for \gx. This was missed when the feature was added. Reported-by: Vik Fearing Discussion: https://postgr.es/m/eca20529-0b06-b493-ee38-f071a75dcd5b@postgresfriends.org Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/08481eedd186ea5c22eef86e85cacacbc715f995 - doc: remove comma, related to commit 92d31085e9. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/750b8832-d123-7f9b-931e-43ce8321b2d7@2ndquadrant.com Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/c713dc2f7be314ee541f0abd170b69c90d8bfb14 - doc: remove unnecessary INNER keyword. A join that was added in commit 9b2009c4cf that did not use the INNER keyword but the existing query used it. It was cleaner to remove the existing INNER keyword. Reported-by: Peter Eisentraut Discussion: https://postgr.es/m/a1ffbfda-59d2-5732-e5fb-3df8582b6434@2ndquadrant.com Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/8da1538b39f2803fdc75de8505dd096e29e65a52 Álvaro Herrera pushed: - Remove header noise from test_decoding test. Use psql's expanded output to avoid a pointless header. Kyotaro Horiguchi, after an idea of Michael Paquier Discussion: https://postgr.es/m/20181120050744.GJ4400@paquier.xyz https://git.postgresql.org/pg/commitdiff/69360b34589bd6dbc7bc58dec718f938167f1071 - Add a glossary to the documentation. More work is still needed, but this is a good start. Co-authored-by: Corey Huinker <corey.huinker@gmail.com> Co-authored-by: Jürgen Purtz <juergen@purtz.de> Co-authored-by: Roger Harkavy <rogerharkavy@gmail.com> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Discussion: https://postgr.es/m/CADkLM=eP6HOeqDjn0FdXuGRusQu4oWH_LFsKjjafmhvWD=aSpQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/347d2b07fcc250680f75b5f89ba49d4805782c6b Tomáš Vondra pushed: - Collect statistics about SLRU caches. There's a number of SLRU caches used to access important data like clog, commit timestamps, multixact, asynchronous notifications, etc. Until now we had no easy way to monitor these shared caches, compute hit ratios, number of reads/writes etc. This commit extends the statistics collector to track this information for a predefined list of SLRUs, and also introduces a new system view pg_stat_slru displaying the data. The list of built-in SLRUs is fixed, but additional SLRUs may be defined in extensions. Unfortunately, there's no suitable registry of SLRUs, so this patch simply defines a fixed list of SLRUs with entries for the built-in ones and one entry for all additional SLRUs. Extensions adding their own SLRU are fairly rare, so this seems acceptable. This patch only allows monitoring of SLRUs, not tuning. The SLRU sizes are still fixed (hard-coded in the code) and it's not entirely clear which of the SLRUs might need a GUC to tune size. In a way, allowing us to determine that is one of the goals of this patch. Bump catversion as the patch introduces new functions and system view. Author: Tomas Vondra Reviewed-by: Alvaro Herrera Discussion: https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development https://git.postgresql.org/pg/commitdiff/28cac71bd368788d1ab22f048eef211641fb1283 - Fix typo in SLRU stats documentation. Author: Noriyoshi Shinoda Discussion: https://www.postgresql.org/message-id/flat/20200119143707.gyinppnigokesjok@development https://git.postgresql.org/pg/commitdiff/2c220ca46f3f6de0611367312bec0daef99b29eb Thomas Munro pushed: - Add maintenance_io_concurrency to postgresql.conf.sample. New GUC from commit fc34b0d9. https://git.postgresql.org/pg/commitdiff/37b3794dfcfb9d55f7ea83693f50b1484caab21c Robert Haas pushed: - pg_waldump: Add a --quiet option. The primary motivation for this change is that it will be used by the upcoming patch to add backup manifests, but it also seems to have some potential more general use. Andres Freund and Robert Haas Discussion: http://postgr.es/m/20200330020814.nspra4mvby42yoa4@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/ac44367efbef198c57a18b96dbc6a39191720994 - Add checksum helper functions. These functions make it easier to write code that wants to compute a checksum for some data while allowing the user to configure the type of checksum that gets used. This is another piece of infrastructure for the upcoming patch to add backup manifests. Patch written from scratch by me, but it is similar to previous work by Rushabh Lathia and Suraj Kharage. Suraj also reviewed this version off-list. Advice on how not to break Windows from Davinder Singh. Discussion: http://postgr.es/m/CA+TgmoZV8dw1H2bzZ9xkKwdrk8+XYa+DC9H=F7heO2zna5T6qg@mail.gmail.com Discussion: http://postgr.es/m/CA+TgmoZRTBiPyvQEwV79PU1ePTtSEo2UeVncrkJMbn1sU1gnRA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c12e43a2e0d45a6b59f2cea53f1b82e52fdcff7a - pg_waldump: Don't call XLogDumpDisplayStats() if -q is specified. Commit ac44367efbef198c57a18b96dbc6a39191720994 introduced this problem. Report and fix by Fujii Masao. Discussion: http://postgr.es/m/d332b8f0-0c72-3cd6-6945-7a86a503662a@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/3031440e9809bbf7cf279b85306df2e3b686539d - Generate backup manifests for base backups, and validate them. A manifest is a JSON document which includes (1) the file name, size, last modification time, and an optional checksum for each file backed up, (2) timelines and LSNs for whatever WAL will need to be replayed to make the backup consistent, and (3) a checksum for the manifest itself. By default, we use CRC-32C when checksumming data files, because we are trying to detect corruption and user error, not foil an adversary. However, pg_basebackup and the server-side BASE_BACKUP command now have options to select a different algorithm, so users wanting a cryptographic hash function can select SHA-224, SHA-256, SHA-384, or SHA-512. Users not wanting file checksums at all can disable them, or disable generating of the backup manifest altogether. Using a cryptographic hash function in place of CRC-32C consumes significantly more CPU cycles, which may slow down backups in some cases. A new tool called pg_validatebackup can validate a backup against the manifest. If no checksums are present, it can still check that the right files exist and that they have the expected sizes. If checksums are present, it can also verify that each file has the expected checksum. Additionally, it calls pg_waldump to verify that the expected WAL files are present and parseable. Only plain format backups can be validated directly, but tar format backups can be validated after extracting them. Robert Haas, with help, ideas, review, and testing from David Steele, Stephen Frost, Andrew Dunstan, Rushabh Lathia, Suraj Kharage, Tushar Ahuja, Rajkumar Raghuwanshi, Mark Dilger, Davinder Singh, Jeevan Chalke, Amit Kapila, Andres Freund, and Noah Misch. Discussion: http://postgr.es/m/CA+TgmoZV8dw1H2bzZ9xkKwdrk8+XYa+DC9H=F7heO2zna5T6qg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0d8c9c1210c44b36ec2efcb223a1dfbe897a3661 - pg_validatebackup: Adjust TAP tests to placate perlcritic. It seems that we have a policy that every Perl subroutine should end with an explicit "return", so add explicit "return" statements to all the new subroutines added by my prior commit 0d8c9c1210c44b36ec2efcb223a1dfbe897a3661. Per buildfarm. https://git.postgresql.org/pg/commitdiff/87e300434058a157bbc4ef8d039937abdefa7610 - pg_validatebackup: Use tempdir_short in TAP tests. The buildfarm is very unhappy right now because TAP test 003_corruption.pl uses TestLib::tempdir to generate the name of a temporary directory that is used as a tablespace name, and this results in a 'symbolic link target too long' error message on many of the buildfarm machines, but not on my machine. It appears that other people have run into similar problems in the past and that TestLib::tempdir_short was the solution, so let's try using that instead. https://git.postgresql.org/pg/commitdiff/21dc48840c24e70b1b1f0f6478f3dba5343182dd - pg_validatebackup: Also use perl2host in TAP tests. Second try at getting the buildfarm to be happy with 003_corrution.pl as added by commit 0d8c9c1210c44b36ec2efcb223a1dfbe897a3661. Per suggestion from Álvaro Herrera. Discussion: http://postgr.es/m/20200403205412.GA8279@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/460314db08e8688e1a54a0a26657941e058e45c5 - pg_validatebackup: Adjust TAP tests to undo permissions change. It may be necessary to go further and remove this test altogether, but I'm going to try this fix first. It's not clear, at least to me, exactly how this is breaking buildfarm members, but it appears to be doing so. https://git.postgresql.org/pg/commitdiff/19c0422ad012636d00ba221bd7052cb71448efca - pg_validatebackup: Fix 'make clean' to remove tmp_check. Report by Tom Lane. Discussion: http://postgr.es/m/22394.1585951968@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9f8f881caa0fabdf7ff46cc55a991ffeab39bd92 - Be more careful about time_t vs. pg_time_t in basebackup.c. lapwing is complaining that about a call to pg_gmtime, saying that it "expected 'const pg_time_t *' but argument is of type 'time_t *'". I at first thought that the problem had someting to do with const, but Thomas Munro suggested that it might be just because time_t and pg_time_t are different identifers. lapwing is i686 rather than x86_64, and pg_time_t is always int64, so that seems like a good guess. There is other code that just casts time_t to pg_time_t without any conversion function, so try that approach here. Introduced in commit 0d8c9c1210c44b36ec2efcb223a1dfbe897a3661. https://git.postgresql.org/pg/commitdiff/db1531cae00941bfe4f6321fdef1e1ef355b6bed - Fix resource management bug with replication=database. Commit 0d8c9c1210c44b36ec2efcb223a1dfbe897a3661 allowed BASE_BACKUP to acquire a ResourceOwner without a transaction so that the backup manifest functionality could use a BufFile, but it overlooked the fact that when a walsender is used with replication=database, it might have a transaction in progress, because in that mode, SQL and replication commands can be mixed. Try to fix things up so that the two cleanup mechanisms don't conflict. Per buildfarm member serinus, which triggered the problem when CREATE_REPLICATION_SLOT failed from inside a transaction. It passed on the subsequent run, so evidently the failure doesn't happen every time. https://git.postgresql.org/pg/commitdiff/3e0d80fd8d3dd4f999e0d3aa3e591f480d8ad1fd Jeff Davis pushed: - Include chunk overhead in hash table entry size estimate. Don't try to be precise about it, just use a constant 16 bytes of chunk overhead. Being smarter would require knowing the memory context where the chunk will be allocated, which is not known by all callers. Discussion: https://postgr.es/m/20200325220936.il3ni2fj2j2b45y5@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/0588ee63aa2d8c5765d086991555cd9acdd4d86f Noah Misch pushed: - Skip WAL for new relfilenodes, under wal_level=minimal. Until now, only selected bulk operations (e.g. COPY) did this. If a given relfilenode received both a WAL-skipping COPY and a WAL-logged operation (e.g. INSERT), recovery could lose tuples from the COPY. See src/backend/access/transam/README section "Skipping WAL for New RelFileNode" for the new coding rules. Maintainers of table access methods should examine that section. To maintain data durability, just before commit, we choose between an fsync of the relfilenode and copying its contents to WAL. A new GUC, wal_skip_threshold, guides that choice. If this change slows a workload that creates small, permanent relfilenodes under wal_level=minimal, try adjusting wal_skip_threshold. Users setting a timeout on COMMIT may need to adjust that timeout, and log_min_duration_statement analysis will reflect time consumption moving to COMMIT from commands like COPY. Internally, this requires a reliable determination of whether RollbackAndReleaseCurrentSubTransaction() would unlink a relation's current relfilenode. Introduce rd_firstRelfilenodeSubid. Amend the specification of rd_createSubid such that the field is zero when a new rel has an old rd_node. Make relcache.c retain entries for certain dropped relations until end of transaction. Bump XLOG_PAGE_MAGIC, since this introduces XLOG_GIST_ASSIGN_LSN. Future servers accept older WAL, so this bump is discretionary. Kyotaro Horiguchi, reviewed (in earlier, similar versions) by Robert Haas. Heikki Linnakangas and Michael Paquier implemented earlier designs that materially clarified the problem. Reviewed, in earlier designs, by Andrew Dunstan, Andres Freund, Alvaro Herrera, Tom Lane, Fujii Masao, and Simon Riggs. Reported by Martijn van Oosterhout. Discussion: https://postgr.es/m/20150702220524.GA9392@svana.org https://git.postgresql.org/pg/commitdiff/c6b92041d38512a4176ed76ad06f713d2e6c01a8 - Add perl2host call missing from a new test file. Oversight in today's commit c6b92041d38512a4176ed76ad06f713d2e6c01a8. Per buildfarm member jacana. Discussion: http://postgr.es/m/20200404223212.GC3442685@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/70de4e950c3b9db620346317f30d31827ac6c3f1 == Pending Patches == Movead Li sent in a patch to clarify the error message issued in XLogInsertRecord. Tomáš Vondra sent in another revision of a patch to fix xid assignment. Alexandra Wang sent in another revision of a patch to implement Zedstore. Ranier Vilela sent in another revision of a patch to remove some redundant initializations. John Naylor sent in two revisions of a patch to tweak perfect hash multipliers. Amit Kapila and Justin Pryzby traded patches to avoid some calls to RelationGetRelationName() and RelationGetNamespace(). John Naylor sent in two more revisions of a patch to allow truncating timestamps on a wider range of intervals. Masahiko Sawada sent in three more revisions of a patch to fix some problems of recovery conflict wait events. Kyotaro HORIGUCHI sent in four more revisions of a patch to reimplement the stats collector to used shared memory rather than files. Masahiko Sawada, Dilip Kumar, and Amit Kapila traded patches to compute buffer usage for VACUUM and CREATE INDEX. Julien Rouhaud sent in six more revisions of a patch to calculate WAL usage. Amit Langote sent in two more revisions of a patch to add partitioned tables to publications. Noah Misch sent in another revision of a patch to fix the WAL-skipping feature. Tomáš Vondra and James Coleman traded patches to implement incremental sorting. Masahiko Sawada sent in another revision of a patch to implement an internal key management system. Ranier Vilela sent in a patch to fix a type var declaration in src/backend/access/brin/brin.c. Tom Lane sent in another revision of a patch to implement an am check members callback. Julien Rouhaud and Fujii Masao traded patches to add planning counters to pg_stat_statements. Zeng Wenjing sent in two more revisions of a patch to implement global temporary tables. Álvaro Herrera and Kyotaro HORIGUCHI traded patches to restrict maximum keep segments by repslots. Bruce Momjian sent in another revision of a patch to add backend type to log_line_prefix. Movead Li and Fujii Masao traded patches to fix a situation where recovery_target_action=pause had a confusing hint. Alexey Bashtanov sent in three more revisions of a patch to control the maximum length of parameters logged. Fujii Masao sent in two more revisions of a patch to show planning buffers. Movead Li sent in four more revisions of a patch to fix a bug when use get_bit() function for a long bytea string. Ashutosh Bapat and Etsuro Fujita traded patches to improve the partition-matching algorithm for partition-wise joins. Justin Pryzby sent in three more revisions of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly. Anna Akenteva and Ivan Kartyshov traded patches to implement WAIT FOR. Peter Eisentraut sent in a patch to save errno across LWLockRelease() calls. Michaël Paquier sent in another revision of a patch to better document the way colors are used. Julien Rouhaud sent in another revision of a patch to implement collation versioning. Julien Rouhaud sent in another revision of a patch to expose queryid in pg_stat_activity and log_line_prefix. Michaël Paquier sent in another revision of a patch to preserve clustered indexes after rewrites of ALTER TABLE. Kyotaro HORIGUCHI sent in a patch to protect dsm_impl from EINTR. Thomas Munro sent in three more revisions of a patch to add an SQL type xid8 to expose FullTransactionId to users, and introduce xid8-based functions to replace txid_XXX. Nikita Glukhov sent in two more revisions of a patch to make Ltree syntax improvements. Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes. Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins. Fabien COELHO sent in another revision of a patch to allow line continuations in pg_hba.conf files. Andy Fan sent in another revision of a patch to maintain UniqueKey at each RelOptInfo, which knowledge can be used for various optimizations. Ibrar Ahmed sent in another revision of a patch to do better VACUUM memory management. Julien Rouhaud sent in four more revisions of a patch to add a pg_check_relation() function. Amit Langote sent in another revision of a patch to fix partition-wise join to handle FULL JOINs correctly. Tom Lane sent in another revision of a patch to fix partition-wise joins. Álvaro Herrera sent in another revision of a patch to implement multiranges. Tomáš Vondra sent in another revision of a patch to consider low startup cost in add_partial_path. Michail Nikolaev sent in a patch to fix an nbtree race and document same. Peter Geoghegan sent in a patch to non-opportunistically delete B-Tree items.
pgsql-announce by date: