== PostgreSQL Weekly News - April 08 2018 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - April 08 2018 == |
| Дата | |
| Msg-id | 20180408213555.GA11444@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - April 08 2018 == PGConf US 2018 will be held in Jersey City, NJ, USA April 16-20, 2018. Registration is open. https://pgconf.org/conferences/2018 == PostgreSQL Product News == psqlODBC 10.02.0000 released. https://odbc.postgresql.org/docs/release.html PostGIS 2.4.4, 2.3.7, and 2.2.7, the industry standard geographic information system package for PostgreSQL, released. http://postgis.net/2018/04/06/postgis-patches/ pitrery 2.1, a set of Bash scripts to manage PITR backups for PostgreSQL, released. https://github.com/dalibo/pitrery == PostgreSQL Jobs for April == http://archives.postgresql.org/pgsql-jobs/2018-04/ == PostgreSQL Local == The German-speaking PostgreSQL Conference 2018 will take place on April 13th, 2018 in Berlin. http://2018.pgconf.de/ PGConfNepal 2018 will be held May 4-5, 2018 at Kathmandu University, Dhulikhel, Nepal. https://postgresconf.org/conferences/Nepal2018 PGCon 2018 will take place in Ottawa on May 29 - June 1, 2018. https://www.pgcon.org/2018/ Swiss PGDay 2018 will take place in Rapperswil (near Zurich) on June 29, 2018. The CfP is open through April 14, 2018, and registration is open through June 28, 2018. http://www.pgday.ch/2018/ PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. http://pgconf.com.br == 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 EST5EDT. Please send English language ones to david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org. == Applied Patches == Robert Haas pushed: - postgres_fdw: Push down partition-wise aggregation. Since commit 7012b132d07c2b4ea15b0b3cb1ea9f3278801d98, postgres_fdw has been able to push down the toplevel aggregation operation to the remote server. Commit e2f1eb0ee30d144628ab523432320f174a2c8966 made it possible to break down the toplevel aggregation into one aggregate per partition. This commit lets postgres_fdw push down aggregation in that case just as it does at the top level. In order to make this work, this commit adds an additional argument to the GetForeignUpperPaths FDW API. A matching argument is added to the signature for create_upper_paths_hook. Third-party code using either of these will need to be updated. Also adjust create_foreignscan_plan() so that it picks up the correct set of relids in this case. Jeevan Chalke, reviewed by Ashutosh Bapat and by me and with some adjustments by me. The larger patch series of which this patch is a part was also reviewed and tested by Antonin Houska, Rajkumar Raghuwanshi, David Rowley, Dilip Kumar, Konstantin Knizhnik, Pascal Legrand, and Rafia Sabih. Discussion: http://postgr.es/m/CAM2+6=V64_xhstVHie0Rz=KPEQnLJMZt_e314P0jaT_oJ9MR8A@mail.gmail.com Discussion: http://postgr.es/m/CAM2+6=XPWujjmj5zUaBTGDoB38CemwcPmjkRy0qOcsQj_V+2sQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7e0d64c7a57e28fbcf093b6da9310a38367c1d75 - Refactor PgFdwModifyState creation/destruction into separate functions. Etsuro Fujita. The larger patch series of which this is a part has been reviewed by Amit Langote, David Fetter, Maksim Milyutin, Álvaro Herrera, Stephen Frost, and me. Discussion: http://postgr.es/m/5A95487E.9050808@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/870d89608e5f891275d0b752560c827dbce3d7b4 - Enforce child constraints during COPY TO a partitioned table. The previous coding inadvertently checked the constraints for the partitioned table rather than the target partition, which could lead to data in a partition that fails to satisfy some constraint on that partition. This problem seems to date back to when table partitioning was introduced; prior to that, there was only one target table for a COPY, so the problem didn't occur, and the code just didn't get updated. Etsuro Fujita, reviewed by Amit Langote and Ashutosh Bapat Discussion: https://postgr.es/message-id/5ABA4074.1090500%40lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/cfbecf8100ecb83c07c2017f843b0642580416bf - Allow insert and update tuple routing and COPY for foreign tables. Also enable this for postgres_fdw. Etsuro Fujita, based on an earlier patch by Amit Langote. The larger patch series of which this is a part has been reviewed by Amit Langote, David Fetter, Maksim Milyutin, Álvaro Herrera, Stephen Frost, and me. Minor documentation changes to the final version by me. Discussion: http://postgr.es/m/29906a26-da12-8c86-4fb9-d8f88442f2b9@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/3d956d9562aa4811b5eaaaf5314d361c61be2ae0 - Fix possible failure in parallel index build. Report and proposed fix by David Rowley, put in patch form by Peter Geoghegan. Discussion: http://postgr.es/m/CAKJS1f91kq1wfYR8rnRRfKtxyhU2woEA+=whd640UxMyU+O0EQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/47cb9ca49a611fa518e1a0fe46526507c96a5612 Peter Eisentraut pushed: - Make be-secure-common.c more consistent for future SSL implementations. Recent commit 8a3d9425 has introduced be-secure-common.c, which is aimed at including backend-side APIs that can be used by any SSL implementation. The purpose is similar to fe-secure-common.c for the frontend-side APIs. However, this has forgotten to include check_ssl_key_file_permissions() in the move, which causes a double dependency between be-secure.c and be-secure-openssl.c. Refactor the code in a more logical way. This also puts into light an API which is usable by future SSL implementations for permissions on SSL key files. Author: Michael Paquier <michael@paquier.xyz> https://git.postgresql.org/pg/commitdiff/2764d5dcfa84d240c901c20ec6e194f72d82b78a - psql: Fix \ef, \sf tab completion. \ef and \sf take any kind of routine, not just normal functions. Author: Pavel Stehule <pavel.stehule@gmail.com> https://git.postgresql.org/pg/commitdiff/05e85d35afb0a58080d6a1a0b582bf8a6471c7f1 - Fix XML syntax in documentation. https://git.postgresql.org/pg/commitdiff/a92f24fc53a83ed6f3b2188bdc298f1c8a4a91ee - Transforms for jsonb to PL/Perl. Add a new contrib module jsonb_plperl that provides a transform between jsonb and PL/Perl. jsonb values are converted to appropriate Perl types such as arrays and hashes, and vice versa. Author: Anthony Bykov <a.bykov@postgrespro.ru> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Reviewed-by: Aleksander Alekseev <a.alekseev@postgrespro.ru> Reviewed-by: Nikita Glukhov <n.gluhov@postgrespro.ru> https://git.postgresql.org/pg/commitdiff/341e1661805879db958dde0a9ed1dc44b1bb10c3 - Attempt to fix jsonb_plperl build on Windows. https://git.postgresql.org/pg/commitdiff/4ab299981576ca0f3dbb879b5e2b704e743d87f3 - doc: Improve indentation of SQL examples. Some of these were indented using 8 spaces whereas the rest uses 4 spaces. Probably originally some difference in tab size. https://git.postgresql.org/pg/commitdiff/a56e26784d7f418015a5be471eb500614a2f24ee - Fix plan cache issue in PL/pgSQL CALL. If we are not going to save the plan, then we need to unset expr->plan after we are done, also in error cases. Otherwise, we get a dangling pointer next time around. This is not the ideal solution. It would be better if we could convince SPI not to associate a cached plan with a resource owner, and then we could just save the plan in all cases. But that would require bigger surgery. Reported-by: Pavel Stehule <pavel.stehule@gmail.com> https://git.postgresql.org/pg/commitdiff/b9986551e0c6129300b9d7a387baf2006724b297 - PL/pgSQL: Add support for SET TRANSACTION. A normal SQL command run inside PL/pgSQL acquires a snapshot, but SET TRANSACTION does not work anymore if a snapshot is set. So we have to handle this separately. Reviewed-by: Alexander Korotkov <a.korotkov@postgrespro.ru> Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com> https://git.postgresql.org/pg/commitdiff/b981275b6531df5a4c4f069571bcb39fc4dee770 - Fix compiler warning about format truncation. https://git.postgresql.org/pg/commitdiff/2cd6520e78fe8bbb4ba38f6c7624c002aa8cc481 - Improve messaging during logical replication worker startup. In case the subscription is removed before the worker is fully started, give a specific error message instead of the generic "cache lookup" error. Author: Petr Jelinek <petr.jelinek@2ndquadrant.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> https://git.postgresql.org/pg/commitdiff/c25304a945467f6edfcca10d7931b913776d066b - Split the SetSubscriptionRelState function into two. We don't actually need the insert-or-update logic, so it's clearer to have separate functions for the inserting and updating. Author: Petr Jelinek <petr.jelinek@2ndquadrant.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> https://git.postgresql.org/pg/commitdiff/bcf79b5bb648d30696406034a61ce0ca3dcb0dea - Add memory context identifier to portal context. Discussion: https://www.postgresql.org/message-id/6421.1522194949@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/94c1f9ba11d1241a2b3b2be7177604b26b08bc3d - Rename MemoryContextCopySetIdentifier() for clarity. MemoryContextCopySetIdentifier -> MemoryContextCopyAndSetIdentifier Discussion: https://www.postgresql.org/message-id/6421.1522194949@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/bbca77623fb5e60f5aa5f226998f0f5c7a66bbf0 - Logical decoding of TRUNCATE. Add a new WAL record type for TRUNCATE, which is only used when wal_level >= logical. (For physical replication, TRUNCATE is already replicated via SMGR records.) Add new callback for logical decoding output plugins to receive TRUNCATE actions. Author: Simon Riggs <simon@2ndquadrant.com> Author: Marco Nenciarini <marco.nenciarini@2ndquadrant.it> Author: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Petr Jelinek <petr.jelinek@2ndquadrant.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> https://git.postgresql.org/pg/commitdiff/5dfd1e5a6696b271a2cdee54143fbc209c88c02f - Logical replication support for TRUNCATE. Update the built-in logical replication system to make use of the previously added logical decoding for TRUNCATE support. Add the required truncate callback to pgoutput and a new logical replication protocol message. Publications get a new attribute to determine whether to replicate truncate actions. When updating a publication via pg_dump from an older version, this is not set, thus preserving the previous behavior. Author: Simon Riggs <simon@2ndquadrant.com> Author: Marco Nenciarini <marco.nenciarini@2ndquadrant.it> Author: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Reviewed-by: Petr Jelinek <petr.jelinek@2ndquadrant.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> https://git.postgresql.org/pg/commitdiff/039eb6e92f20499ac36cc74f8a5cef7430b706f6 - Fix timing issue in new subscription truncate test. We need to wait for the initial sync of all subscriptions. On some (faster?) machines, this didn't make a difference, but the (slower?) buildfarm machines are upset. https://git.postgresql.org/pg/commitdiff/529ab7bd1fb9c836fe5ccd96f79329d407522e20 Tom Lane pushed: - Teach configure --with-python to report the Python version number. We already do this for Perl and some other interesting tools, so it seems sensible to do it for Python as well, especially since the sub-release number is never determinable from other configure output and even the major/minor numbers may not be clear without excavation in config.log. While at it, get rid of the code's assumption that both the major and minor numbers contain exactly one digit. That will foreseeably be broken by Python 3.10 in perhaps four or five years. That's far enough out that we probably don't need to back-patch this. Discussion: https://postgr.es/m/2186.1522681145@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/beff4bb9c7411cfd973e192df05c0c1f5c796d22 - Remove contrib/jsonb_plpython's tests for infinity and NaN conversions. These tests don't work reliably with pre-2.6 Python versions, since Python code like float('inf') was not guaranteed to work before that, even granting an IEEE-compliant platform. Since there's no explicit handling of these cases in jsonb_plpython, we're not adding any real code coverage by testing them, and thus it doesn't seem to make sense to go to any great lengths to work around the test instability. Discussion: https://postgr.es/m/E1f1AMU-00031c-9N@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/6278a2a262b63faaf47eb2371f6bcb5b6e3ff118 - Fix some dubious WAL-parsing code. Coverity complained about possible buffer overrun in two places added by commit 1eb6d6527, and AFAICS it's reasonable to worry: even granting that the WAL originator properly truncated the commit GID to GIDSIZE, we should not really bet our lives on that having the same value as it does in the current build. Hence, use strlcpy() not strcpy(), and adjust the pointer advancement logic to be sure we skip over the whole source string even if strlcpy() truncated it. https://git.postgresql.org/pg/commitdiff/b01f32c313f00846b6f06cf871d0d9ce7c3ad5e5 - Suppress compiler warning in new jsonb_plperl code. Some compilers are evidently pickier than others about whether Perl's I32 typedef should be considered equivalent to int. Dodge the problem by using a separate variable; the prior coding was a bit confusing anyway. Per buildfarm. Note this does nothing to fix the test failures due to SV_to_JsonbValue not covering enough variable types. https://git.postgresql.org/pg/commitdiff/d4a4c3d5b433513fc38ce60e454d2c86e8bd0ed4 - Prevent accidental linking of system-supplied copies of libpq.so etc. We were being careless in some places about the order of -L switches in link command lines, such that -L switches referring to external directories could come before those referring to directories within the build tree. This made it possible to accidentally link a system-supplied library, for example /usr/lib/libpq.so, in place of the one built in the build tree. Hilarity ensued, the more so the older the system-supplied library is. To fix, break LDFLAGS into two parts, a sub-variable LDFLAGS_INTERNAL and the main LDFLAGS variable, both of which are "recursively expanded" so that they can be incrementally adjusted by different makefiles. Establish a policy that -L switches for directories in the build tree must always be added to LDFLAGS_INTERNAL, while -L switches for external directories must always be added to LDFLAGS. This is sufficient to ensure a safe search order. For simplicity, we typically also put -l switches for the respective libraries into those same variables. (Traditional make usage would have us put -l switches into LIBS, but cleaning that up is a project for another day, as there's no clear need for it.) This turns out to also require separating SHLIB_LINK into two variables, SHLIB_LINK and SHLIB_LINK_INTERNAL, with a similar rule about which switches go into which variable. And likewise for PG_LIBS. Although this change might appear to affect external users of pgxs.mk, I think it doesn't; they shouldn't have any need to touch the _INTERNAL variables. In passing, tweak src/common/Makefile so that the value of CPPFLAGS recorded in pg_config lacks "-DFRONTEND" and the recorded value of LDFLAGS lacks "-L../../../src/common". Both of those things are mistakes, apparently introduced during prior code rearrangements, as old versions of pg_config don't print them. In general we don't want anything that's specific to the src/common subdirectory to appear in those outputs. This is certainly a bug fix, but in view of the lack of field complaints, I'm unsure whether it's worth the risk of back-patching. In any case it seems wise to see what the buildfarm makes of it first. Discussion: https://postgr.es/m/25214.1522604295@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/dddfc4cb2edcfa5497f5d50190a7fb046c51da16 - Fix platform and Perl-version dependencies in new jsonb_plperl code. Testing SvTYPE() directly is more fraught with problems than one might think, because depending on context Perl might be storing a scalar value in one of several forms, eg both numeric and string values. This resulted in Perl-version-dependent buildfarm test failures. Instead use the SvTYPE test only to distinguish non-scalar cases (AV, HV, NULL). Disambiguate scalars by testing SvIOK, SvNOK, then SvPOK. This creates a preference order for how we will resolve cases where the value is available in more than one form, which seems fine to me. Furthermore, because we're now dealing directly with a "double" value in the SvNOK case, we can get rid of an inadequate and unportable string-comparison test for infinities, and use isinf() instead. (We do need some additional #include and "-lm" infrastructure to use that in a contrib module, per prior experiences.) In passing, prevent the regression test results from depending on DROP CASCADE order; I've not seen that malfunction, but it's trouble waiting to happen. Discussion: https://postgr.es/m/E1f3MMJ-0006bf-B0@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/331b2369c0ad1e51d5e50bf5dd75232e0160553a - Remove less-portable-than-believed test case. In commit 331b2369c I added a test to see what jsonb_plperl would do with a qr{} result. Turns out the answer is Perl version dependent. That fact doesn't bother me particularly, but coping with multiple result possibilities is way more work than this test seems worth. So remove it again. Discussion: https://postgr.es/m/E1f3MMJ-0006bf-B0@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/eac93e20afe434a79e81558c17a7a1408cf9d74a - Improve FSM management for BRIN indexes. BRIN indexes like to propagate additions of free space into the upper pages of their free space maps as soon as the new space is known, even when it's just on one individual index page. Previously this required calling FreeSpaceMapVacuum, which is quite an expensive thing if the map is large. Use the FreeSpaceMapVacuumRange function recently added by commit c79f6df75 to reduce the amount of work done for this purpose. Fix a couple of places that neglected to do the upper-page vacuuming at all after recording new free space. If the policy is to be that BRIN should do that, it should do it everywhere. Do RecordPageWithFreeSpace unconditionally in brin_page_cleanup, and do FreeSpaceMapVacuum unconditionally in brin_vacuum_scan. Because of the FSM's imprecise storage of free space, the old complications here seldom bought anything, they just slowed things down. This approach also provides a predictable path for FSM corruption to be repaired. Remove premature RecordPageWithFreeSpace call in brin_getinsertbuffer where it's about to return an extended page to the caller. The caller should do that, instead, after it's inserted its new tuple. Fix the one caller that forgot to do so. Simplify logic in brin_doupdate's same-page-update case by postponing brin_initialize_empty_new_buffer to after the critical section; I see little point in doing it before. Avoid repeat calls of RelationGetNumberOfBlocks in brin_vacuum_scan. Avoid duplicate BufferGetBlockNumber and BufferGetPage calls in a couple of places where we already had the right values. Move a BRIN_elog debug logging call out of a critical section; that's pretty unsafe and I don't think it buys us anything to not wait till after the critical section. Move the "*extended = false" step in brin_getinsertbuffer into the routine's main loop. There's no actual bug there, since the loop can't iterate with *extended still true, but it doesn't seem very future-proof as coded; and it's certainly not documented as a loop invariant. This is all from follow-on investigation inspired by commit c79f6df75. Discussion: https://postgr.es/m/5801.1522429460@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1383e2a1a937116e1367c9584984f0730f9ef4d5 - Remove some unnecessary quote marks from catalog DATA lines. This has no functional impact whatsoever. However, it causes these unnecessary quote marks to disappear from the generated postgres.bki file, making it easier to verify that the upcoming bootstrap data conversion patch doesn't change the generated file. https://git.postgresql.org/pg/commitdiff/cb1ff1e5af83f2c548fcb15596d474c198a021c5 - Doc: fix broken markup. Commit 3d956d956 was apparently not checked against HEAD's doc toolchain. Per buildfarm. https://git.postgresql.org/pg/commitdiff/eb2a0e00b18aedabd76f5442e38f4bf5d1b94974 - Add missing "static" markers. Evidently forgotten in commit 11523e860. Per buildfarm member pademelon. https://git.postgresql.org/pg/commitdiff/2fa55f26a0c56f40b71f96859280dc5d11751514 - Replace our traditional initial-catalog-data format with a better design. Historically, the initial catalog data to be installed during bootstrap has been written in DATA() lines in the catalog header files. This had lots of disadvantages: the format was badly underdocumented, it was very difficult to edit the data in any mechanized way, and due to the lack of any abstraction the data was verbose, hard to read/understand, and easy to get wrong. Hence, move this data into separate ".dat" files and represent it in a way that can easily be read and rewritten by Perl scripts. The new format is essentially "key => value" for each column; while it's a bit repetitive, explicit labeling of each value makes the data far more readable and less error-prone. Provide a way to abbreviate entries by omitting field values that match a specified default value for their column. This allows removal of a large amount of repetitive boilerplate and also lowers the barrier to adding new columns. Also teach genbki.pl how to translate symbolic OID references into numeric OIDs for more cases than just "regproc"-like pg_proc references. It can now do that for regprocedure-like references (thus solving the problem that regproc is ambiguous for overloaded functions), operators, types, opfamilies, opclasses, and access methods. Use this to turn nearly all OID cross-references in the initial data into symbolic form. This represents a very large step forward in readability and error resistance of the initial catalog data. It should also reduce the difficulty of renumbering OID assignments in uncommitted patches. Also, solve the longstanding problem that frontend code that would like to use OID macros and other information from the catalog headers often had difficulty with backend-only code in the headers. To do this, arrange for all generated macros, plus such other declarations as we deem fit, to be placed in "derived" header files that are safe for frontend inclusion. (Once clients migrate to using these pg_*_d.h headers, it will be possible to get rid of the pg_*_fn.h headers, which only exist to quarantine code away from clients. That is left for follow-on patches, however.) The now-automatically-generated macros include the Anum_xxx and Natts_xxx constants that we used to have to update by hand when adding or removing catalog columns. Replace the former manual method of generating OID macros for pg_type entries with an automatic method, ensuring that all built-in types have OID macros. (But note that this patch does not change the way that OID macros for pg_proc entries are built and used. It's not clear that making that match the other catalogs would be worth extra code churn.) Add SGML documentation explaining what the new data format is and how to work with it. Despite being a very large change in the catalog headers, there is no catversion bump here, because postgres.bki and related output files haven't changed at all. John Naylor, based on ideas from various people; review and minor additional coding by me; previous review by Alvaro Herrera Discussion: https://postgr.es/m/CAJVSVGWO48JbbwXkJz_yBFyGYW-M9YWxnPdxJBUosDC9ou_F0Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/372728b0d49552641f0ea83d9d2e08817de038fa - Switch client-side code to include catalog/pg_foo_d.h not pg_foo.h. Everything of use to frontend code should now appear in the _d.h files, and making this change frees us from needing to worry about whether the catalog header files proper are frontend-safe. Remove src/interfaces/ecpg/ecpglib/pg_type.h entirely, as the previous commit reduced it to a confusingly-named wrapper around pg_type_d.h. In passing, make test_rls_hooks.c follow project convention of including our own files with #include "" not <>. Discussion: https://postgr.es/m/23690.1523031777@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9c0a0de4c91b2dc911220d769cf2c5f754cfcb90 - Merge catalog/pg_foo_fn.h headers back into pg_foo.h headers. Traditionally, include/catalog/pg_foo.h contains extern declarations for functions in backend/catalog/pg_foo.c, in addition to its function as the authoritative definition of the pg_foo catalog's rowtype. In some cases, we'd been forced to split out those extern declarations into separate pg_foo_fn.h headers so that the catalog definitions could be #include'd by frontend code. That problem is gone as of commit 9c0a0de4c, so let's undo the splits to make things less confusing. Discussion: https://postgr.es/m/23690.1523031777@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/cefa3871534d2c467a520820c0ae3f002a46d8e4 - Reduce worst-case shell command line length during "make install". Addition of the catalog/pg_foo_d.h headers seems to have pushed us over the brink of the maximum command line length for some older platforms during "make install" for our header files. The main culprit here is repetition of the target directory path, which could be long. Rearrange so that we don't repeat that once per file, but only once per subdirectory. Per buildfarm. Discussion: https://postgr.es/m/E1f5Dwm-0004n5-7O@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/cca563f38422f2a7c6c56f162efe2689bf1c7697 - Cosmetic cleanups in initial catalog data. Write ',' and ';' for typdelim values instead of the obscurantist ASCII octal equivalents. Not sure why anybody ever thought the latter were better; maybe it had something to do with lack of a better quoting convention, twenty-plus years ago? Reassign a couple of high-numbered OIDs that were left in during yesterday's mad rush to commit stuff of uncertain internal temperature. The latter requires a catversion bump, though the former wouldn't since the end-result catalog data is unchanged. https://git.postgresql.org/pg/commitdiff/4f85f66469594996584d50e6a940b5ba873aa713 - Doc: clarify explanation of pg_dump usage. This section confusingly used both "infile" and "outfile" to refer to the same file, i.e. the textual output of pg_dump. Use "dumpfile" for both cases, per suggestion from Jonathan Katz. Discussion: https://postgr.es/m/152311295239.31235.6487236091906987117@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/893e9e65402e79cc5eb865825be32f675d8a72b2 - Fix additional breakage in covering-index patch. CheckIndexCompatible() misused ComputeIndexAttrs() by not bothering to fill ii_NumIndexAttrs and ii_NumIndexKeyAttrs in the passed IndexInfo. Omission of ii_NumIndexAttrs was previously unimportant, but now this matters because ComputeIndexAttrs depends on ii_NumIndexKeyAttrs to decide how many columns it needs to report on. (BTW, the fact that this oversight wasn't detected earlier implies that we have no regression test verifying whether CheckIndexCompatible ever succeeds. Bad dog. Not the job of this patch to fix it, though.) Also, change the API of ComputeIndexAttrs so that it fills the opclass output array for all column positions, as it does for the options output array; positions for non-key index columns are filled with zeroes. This isn't directly fixing any bug, but it seems like a good idea. Per valgrind failure reports from buildfarm. Alexander Korotkov, tweaked a bit by me Discussion: https://postgr.es/m/CAPpHfduWrysrT-qAhn+3Ea5+Mg6Vhc-oA6o2Z-hRCPRdvf3tiw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b3b7f7898f17009912b3fc16620d042ec8482557 Simon Riggs pushed: - MERGE SQL Command following SQL:2016. MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g. MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING; MERGE works with regular and partitioned tables, including column and row security enforcement, as well as support for row, statement and transition triggers. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used statically from PL/pgSQL. MERGE does not yet support inheritance, write rules, RETURNING clauses, updatable views or foreign tables. MERGE follows SQL Standard per the most recent SQL:2016. Includes full tests and documentation, including full isolation tests to demonstrate the concurrent behavior. This version written from scratch in 2017 by Simon Riggs, using docs and tests originally written in 2009. Later work from Pavan Deolasee has been both complex and deep, leaving the lead author credit now in his hands. Extensive discussion of concurrency from Peter Geoghegan, with thanks for the time and effort contributed. Various issues reported via sqlsmith by Andreas Seltenreich Authors: Pavan Deolasee, Simon Riggs Reviewers: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e6597dc3533946b98acba7871bd4ca1f7a3d4c1d - Modified files for MERGE. https://git.postgresql.org/pg/commitdiff/354f13855e6381d288dfaa52bcd4f2cb0fd4a5eb - Revert "Modified files for MERGE". This reverts commit 354f13855e6381d288dfaa52bcd4f2cb0fd4a5eb. https://git.postgresql.org/pg/commitdiff/7cf8a5c302735d193dcf901b87e03e324903c632 - Revert "MERGE SQL Command following SQL:2016". This reverts commit e6597dc3533946b98acba7871bd4ca1f7a3d4c1d. https://git.postgresql.org/pg/commitdiff/aa5877bb26347c58a34aee4e460eb1e1123bb096 - MERGE SQL Command following SQL:2016. MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g. MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING; MERGE works with regular and partitioned tables, including column and row security enforcement, as well as support for row, statement and transition triggers. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used statically from PL/pgSQL. MERGE does not yet support inheritance, write rules, RETURNING clauses, updatable views or foreign tables. MERGE follows SQL Standard per the most recent SQL:2016. Includes full tests and documentation, including full isolation tests to demonstrate the concurrent behavior. This version written from scratch in 2017 by Simon Riggs, using docs and tests originally written in 2009. Later work from Pavan Deolasee has been both complex and deep, leaving the lead author credit now in his hands. Extensive discussion of concurrency from Peter Geoghegan, with thanks for the time and effort contributed. Various issues reported via sqlsmith by Andreas Seltenreich Authors: Pavan Deolasee, Simon Riggs Reviewer: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d204ef63776b8a00ca220adec23979091564e465 - New files for MERGE. https://git.postgresql.org/pg/commitdiff/83454e3c2b28141c0db01c7d2027e01040df5249 - WITH support in MERGE. Author: Peter Geoghegan Recursive support removed, no tests Docs added by me https://git.postgresql.org/pg/commitdiff/aa3faa3c7a7a49b3318059ccaf79bc1886a64707 - Tab completion for MERGE. Author: Pavan Deolasee https://git.postgresql.org/pg/commitdiff/4923550c20ae6d122ae0867480a7de8b040f7d80 - MERGE post-commit review. Review comments from Andres Freund * Consolidate code into AfterTriggerGetTransitionTable() * Rename nodeMerge.c to execMerge.c * Rename nodeMerge.h to execMerge.h * Move MERGE handling in ExecInitModifyTable() into a execMerge.c ExecInitMerge() * Move mt_merge_subcommands flags into execMerge.h * Rename opt_and_condition to opt_merge_when_and_condition * Wordsmith various comments Author: Pavan Deolasee Reviewer: Simon Riggs https://git.postgresql.org/pg/commitdiff/4b2d44031f8c005f6f86364d7663858b6b5bdd14 - MERGE INSERT allows only one VALUES clause. Doc syntax and brief mention of restriction https://git.postgresql.org/pg/commitdiff/a5d86181ecc9c441d4c327771c43134de59549cd - MERGE fix variable warning in non-assert builds. Author: Jesper Pedersen https://git.postgresql.org/pg/commitdiff/3af7b2b0d497cfc240ffc8098ef068adb30048a2 - MERGE minor errata. https://git.postgresql.org/pg/commitdiff/01b88b4df5e2df0365cceaf79a039214d9f05273 - Allow cpluspluscheck to pass by renaming variable. Use of a C++ keyword as a function name caused problems Reported-by: Álvaro Herrera https://git.postgresql.org/pg/commitdiff/530e69e59b07cf94a65cfde7dd1a8b1c62b44228 - MERGE syntax diagram correction. Reported-by: Andrew Gierth https://git.postgresql.org/pg/commitdiff/ddb4158579e052ee35313c333256cb1f16ee65fa - Improve parse representation for MERGE. Separation of parser data structures from executor, as requested by Tom Lane. Further improvements possible. While there, implement error for multiple VALUES clauses via parser to allow line number of error, as requested by Andres Freund. Author: Pavan Deolasee Discussion: https://www.postgresql.org/message-id/CABOikdPpqjectFchg0FyTOpsGXyPoqwgC==OLKWuxgBOsrDDZw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f1464c53804fa7280a7942f6ac08038440f73b11 Magnus Hagander pushed: - Validate page level checksums in base backups. When base backups are run over the replication protocol (for example using pg_basebackup), verify the checksums of all data blocks if checksums are enabled. If checksum failures are encountered, log them as warnings but don't abort the backup. This becomes the default behaviour in pg_basebackup (provided checksums are enabled on the server), so add a switch (-k) to disable the checks if necessary. Author: Michael Banck Reviewed-By: Magnus Hagander, David Steele Discussion: https://postgr.es/m/20180228180856.GE13784@nighthawk.caipicrew.dd-dns.de https://git.postgresql.org/pg/commitdiff/4eb77d50c21ddd35b77421c27e0d7853eb4f9202 - Fix for checksum validation patch. Reorder the check for non-BLCKSZ size reads to make sure we don't abort sending the file in this case. Missed in the previous commit. https://git.postgresql.org/pg/commitdiff/a08dc711952081d63577fc182fcf955958f70add - Properly use INT64_FORMAT in output. Per buildfarm animal prairiedog, suggestion solution from Tom. https://git.postgresql.org/pg/commitdiff/10d62d1065290469ae79518f8166550b2760dc26 - Fix pg_bsaebackup checksum tests. Hopefully fix the fact that these checks are unstable, by introducing the corruption in a separate table from pg_class, and also explicitly disable autovacuum on those tables. Also make sure PostgreSQL is stopped while the corruption is introduced to avoid possible caching effects. Author: Michael Banck https://git.postgresql.org/pg/commitdiff/ee9e1455310ec57774ca67158571bec5d3288cdf - Allow background workers to bypass datallowconn. This adds a "flags" field to the BackgroundWorkerInitializeConnection() and BackgroundWorkerInitializeConnectionByOid(). For now only one flag, BGWORKER_BYPASS_ALLOWCONN, is defined, which allows the worker to ignore datallowconn. https://git.postgresql.org/pg/commitdiff/eed1ce72e1593d3e8b7461d7744808d4d6bf402b - Fix worker_spi for new parameter to initialize connection. Missed in previous commit. Spotted by Teodor and the buildfarm https://git.postgresql.org/pg/commitdiff/6a5f796b48b0e9808fef9731da8aea17f56de999 - Allow on-line enabling and disabling of data checksums. This makes it possible to turn checksums on in a live cluster, without the previous need for dump/reload or logical replication (and to turn it off). Enabling checkusm starts a background process in the form of a launcher/worker combination that goes through the entire database and recalculates checksums on each and every page. Only when all pages have been checksummed are they fully enabled in the cluster. Any failure of the process will revert to checksums off and the process has to be started. This adds a new WAL record that indicates the state of checksums, so the process works across replicated clusters. Authors: Magnus Hagander and Daniel Gustafsson Review: Tomas Vondra, Michael Banck, Heikki Linnakangas, Andrey Borodin https://git.postgresql.org/pg/commitdiff/1fde38beaa0c3e66c340efc7cc0dc272d6254bb0 - Attempt to fix win32 build of pg_verify_checksums. S_ISLNK doesn't exist on Win32, instead we should use pgwin32_is_junction(). https://git.postgresql.org/pg/commitdiff/3b0b4f31f73a5f45f8e122d826211c13cd2412f7 - Clean up intermetiate state in pg_basebackup tests. These tests accummulated almost a gigabyte of data during the test which was then removed at the end. Instead, remove output that's no longer needed between the individual tests, to keep the total disk usage down lower. Author: Michael Banck https://git.postgresql.org/pg/commitdiff/032429701e477116dae9482cc1bf0df8d5ac8f77 - Fix typo. Author: Michael Banck https://git.postgresql.org/pg/commitdiff/f66c37b2f7b99d1216da9b6267793cfbdc2e58c4 Teodor Sigaev pushed: - Add prefix operator for TEXT type. The prefix operator along with SP-GiST indexes can be used as an alternative for LIKE 'word%' commands and it doesn't have a limitation of string/prefix length as B-Tree has. Bump catalog version Author: Ildus Kurbangaliev with some editorization by me Review by: Arthur Zakirov, Alexander Korotkov, and me Discussion: https://www.postgresql.org/message-id/flat/20180202180327.222b04b3@wp.localdomain https://git.postgresql.org/pg/commitdiff/710d90da1fd8c1d028215ecaf7402062079e99e9 - Skip full index scan during cleanup of B-tree indexes when possible. Vacuum of index consists from two stages: multiple (zero of more) ambulkdelete calls and one amvacuumcleanup call. When workload on particular table is append-only, then autovacuum isn't intended to touch this table. However, user may run vacuum manually in order to fill visibility map and get benefits of index-only scans. Then ambulkdelete wouldn't be called for indexes of such table (because no heap tuples were deleted), only amvacuumcleanup would be called In this case, amvacuumcleanup would perform full index scan for two objectives: put recyclable pages into free space map and update index statistics. This patch allows btvacuumclanup to skip full index scan when two conditions are satisfied: no pages are going to be put into free space map and index statistics isn't stalled. In order to check first condition, we store oldest btpo_xact in the meta-page. When it's precedes RecentGlobalXmin, then there are some recyclable pages. In order to check second condition we store number of heap tuples observed during previous full index scan by cleanup. If fraction of newly inserted tuples is less than vacuum_cleanup_index_scale_factor, then statistics isn't considered to be stalled. vacuum_cleanup_index_scale_factor can be defined as both reloption and GUC (default). This patch bumps B-tree meta-page version. Upgrade of meta-page is performed "on the fly": during VACUUM meta-page is rewritten with new version. No special handling in pg_upgrade is required. Author: Masahiko Sawada, Alexander Korotkov Review by: Peter Geoghegan, Kyotaro Horiguchi, Alexander Korotkov, Yura Sokolov Discussion: https://www.postgresql.org/message-id/flat/CAD21AoAX+d2oD_nrd9O2YkpzHaFr=uQeGr9s1rKC3O4ENc568g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/857f9c36cda520030381bd8c2af20adf0ce0e1d4 - Fix typo. Masahiko Sawada https://git.postgresql.org/pg/commitdiff/51e6562324f8f03909f1ad1754720e95ae238fad - Fix misprint in documentation. Masahiko Sawada https://git.postgresql.org/pg/commitdiff/a02d51c0d3c0c25d99d2a1cd84f3321640294fa9 - Remove unused vars and mark assert-only vars. Kyotaro HORIGUCHI https://git.postgresql.org/pg/commitdiff/17d8beb4f52599805283b1f2f49e02491656f7e1 - Fix handling of non-upgraded B-tree metapages. 857f9c36 bumps B-tree metapage version while upgrade is performed "on the fly" when needed. However, some asserts fired when old version metapage was cached to rel->rd_amcache. Despite new metadata fields are never used from rel->rd_amcache, that needs to be fixed. This patch introduces metadata upgrade during its caching, which fills unavailable fields with their default values. contrib/pageinspect is also patched to handle non-upgraded metapages in the same way. Author: Alexander Korotkov https://git.postgresql.org/pg/commitdiff/0a64b45152b593c5eb95f2e88fbce7fbfe84ae7b - Add support of bool, bpchar, name and uuid to btree_gin. Mostly for completeness, but I believe there are cases to use that in multicolumn GIN indexes. Bump btree_gin module version Author: Matheus Oliveira Reviewed by: Tomas Vondra Discussion: https://www.postgresql.org/message-id/flat/CAJghg4LMJf6Z13fnZD-MBNiGxzd0cA2=F3TDjNkX3eQH58hktQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f4cd7102b5a6097fb603c789728fbfd5d6fd43c5 - Add websearch_to_tsquery. Error-tolerant conversion function with web-like syntax for search query, it simplifies constraining search engine with close to habitual interface for users. Bump catalog version Authors: Victor Drobny, Dmitry Ivanov with editorization by me Reviewed by: Aleksander Alekseev, Tomas Vondra, Thomas Munro, Aleksandr Parfenov Discussion: https://www.postgresql.org/message-id/flat/fe931111ff7e9ad79196486ada79e268@postgrespro.ru https://git.postgresql.org/pg/commitdiff/1664ae1978bf0f5ee940dc2fc8313e6400a7e7da - Remove tsearch test contained russian characters, missed in. 1664ae1978bf0f5ee940dc2fc8313e6400a7e7da https://git.postgresql.org/pg/commitdiff/1a8c95365e01c37ff5f51eb803e8ce926fbb0238 - Predicate locking in hash indexes. Hash index searches acquire predicate locks on the primary page of a bucket. It acquires a lock on both the old and new buckets for scans that happen concurrently with page splits. During a bucket split, a predicate lock is copied from the primary page of an old bucket to the primary page of a new bucket. Author: Shubham Barai, Amit Kapila Reviewed by: Amit Kapila, Alexander Korotkov, Thomas Munro Discussion: https://www.postgresql.org/message-id/flat/CALxAEPvNsM2GTiXdRgaaZ1Pjd1bs+sxfFsf7Ytr+iq+5JJoYXA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b508a56f2f3a2d850e75a14661943d6b4dde8274 - Add json(b)_to_tsvector function. Jsonb has a complex nature so there isn't best-for-everything way to convert it to tsvector for full text search. Current to_tsvector(json(b)) suggests to convert only string values, but it's possible to index keys, numerics and even booleans value. To solve that json(b)_to_tsvector has a second required argument contained a list of desired types of json fields. Second argument is a jsonb scalar or array right now with possibility to add new options in a future. Bump catalog version Author: Dmitry Dolgov with some editorization by me Reviewed by: Teodor Sigaev Discussion: https://www.postgresql.org/message-id/CA+q6zcXJQbS1b4kJ_HeAOoOc=unfnOrUEL=KGgE32QKDww7d8g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1c1791e00065f6986f9d44a78ce7c28b2d1322dd - Make test of json(b)_to_tsvector language-independ. Missed in 1c1791e00065f6986f9d44a78ce7c28b2d1322dd commit https://git.postgresql.org/pg/commitdiff/01bb85169afadfe63e2f0e344ff671292080de7e - Indexes with INCLUDE columns and their support in B-tree. This patch introduces INCLUDE clause to index definition. This clause specifies a list of columns which will be included as a non-key part in the index. The INCLUDE columns exist solely to allow more queries to benefit from index-only scans. Also, such columns don't need to have appropriate operator classes. Expressions are not supported as INCLUDE columns since they cannot be used in index-only scans. Index access methods supporting INCLUDE are indicated by amcaninclude flag in IndexAmRoutine. For now, only B-tree indexes support INCLUDE clause. In B-tree indexes INCLUDE columns are truncated from pivot index tuples (tuples located in non-leaf pages and high keys). Therefore, B-tree indexes now might have variable number of attributes. This patch also provides generic facility to support that: pivot tuples contain number of their attributes in t_tid.ip_posid. Free 13th bit of t_info is used for indicating that. This facility will simplify further support of index suffix truncation. The changes of above are backward-compatible, pg_upgrade doesn't need special handling of B-tree indexes for that. Bump catalog version Author: Anastasia Lubennikova with contribition by Alexander Korotkov and me Reviewed by: Peter Geoghegan, Tomas Vondra, Antonin Houska, Jeff Janes, David Rowley, Alexander Korotkov Discussion: https://www.postgresql.org/message-id/flat/56168952.4010101@postgrespro.ru https://git.postgresql.org/pg/commitdiff/8224de4f42ccf98e08db07b43d52fed72f962ebb - Remove unused variable in non-assert-enabled build. Use field of structure in Assert directly Jeff Janes https://git.postgresql.org/pg/commitdiff/34602b0a1dfd41400d376e73e41ad868ad2d3ce8 - match_clause_to_index should check only key columns. Alexander Korotkov per gripe from Tom Lane noticed on valgrind-enabled buildfarm members https://git.postgresql.org/pg/commitdiff/02f3e558f21c0fbec9f94d5de9ad34f321eb0e57 Bruce Momjian pushed: - C comment: mention null handling in BuildTupleFromCStrings(). Discussion: https://postgr.es/m/CAFjFpRcF-wNbe0w-m3NpkEwr9shmOZ=GoESOzd2Wog9h55J8sA@mail.gmail.com Author: Ashutosh Bapat https://git.postgresql.org/pg/commitdiff/242408dbef283ff148a3a671a7f4ebbad6118b22 - docs: update ltree URL for the DMOZ catalog. Reported-by: bbrincat@gmail.com Discussion: https://postgr.es/m/152283596377.1441.11672249301622760943@wrigleys.postgresql.org Author: Oleg Bartunov Backpatch-through: 9.3 https://git.postgresql.org/pg/commitdiff/cd1661bbcc7c20a5c4d00dd114263ea9afe36063 - doc: remove mention of the DMOZ catalog in ltree docs. Discussion: https://postgr.es/m/CAF4Au4xYem_W3KOuxcKct7=G4j8Z3uO9j3DUKTFJqUsfp_9pQg@mail.gmail.com Author: Oleg Bartunov Backpatch-through: 9.3 https://git.postgresql.org/pg/commitdiff/c39e903d510064e4415bbadb43e34f6998351cca Álvaro Herrera pushed: - Pass correct TupDesc to ri_NullCheck() in Assert. Previous coding was passing the wrong table's tuple descriptor, which accidentally fails to fail because no existing test case exercises a foreign key in which the referenced attributes are further to the right of the referencing attributes. Add a test so that further breakage is visible. This got broken in 16828d5c0273. Discussion: https://postgr.es/m/20180403204723.fqte755nukgm42uf@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/cd5005bc12d0f9a15fe7dddd3e1ac92496608114 - Don't clone internal triggers to partitions. Trigger cloning to partitions was supposed to occur for user-visible triggers only, but during development the protection that prevented it from occurring to internal triggers was lost. Reinstate it, as well as add a test case to ensure internal triggers (in the tested case, triggers implementing a deferred unique constraint) are not cloned. Without the code fix, the partitions in the test end up with different numbers of triggers, which is clearly wrong ... Bug in 86f575948c77. Discussion: https://postgr.es/m/20180403214903.ozfagwjcpk337uw7@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/851f4b4e143063719553bccc73119a0d29fb1afc - Foreign keys on partitioned tables. Author: Álvaro Herrera Discussion: https://postgr.es/m/20171231194359.cvojcour423ulha4@alvherre.pgsql Reviewed-by: Peter Eisentraut https://git.postgresql.org/pg/commitdiff/3de241dba86f3dd000434f70aebba725fb928032 - Restore erroneously removed ONLY from PK check. This is a blind fix, since I don't have SE-Linux to verify it. Per unwanted change in rhinoceros, running sepgsql tests. Noted by Tom Lane. Discussion: https://postgr.es/m/32347.1522865050@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7d7c99790b2a7e6f4e5287a3fb29f73cedbb2105 - Add missing include. Newly added prototype broke cpluspluscheck. Minor buglet in commit 8694cc96b52a. https://git.postgresql.org/pg/commitdiff/fbc27330b8f8693d8fa9b1f8cb450567c3d81640 - Faster partition pruning. Add a new module backend/partitioning/partprune.c, implementing a more sophisticated algorithm for partition pruning. The new module uses each partition's "boundinfo" for pruning instead of constraint exclusion, based on an idea proposed by Robert Haas of a "pruning program": a list of steps generated from the query quals which are run iteratively to obtain a list of partitions that must be scanned in order to satisfy those quals. At present, this targets planner-time partition pruning, but there exist further patches to apply partition pruning at execution time as well. This commit also moves some definitions from include/catalog/partition.h to a new file include/partitioning/partbounds.h, in an attempt to rationalize partitioning related code. Authors: Amit Langote, David Rowley, Dilip Kumar Reviewers: Robert Haas, Kyotaro Horiguchi, Ashutosh Bapat, Jesper Pedersen. Discussion: https://postgr.es/m/098b9c71-1915-1a2a-8d52-1a7a50ce79e8@lab.ntt.co.jp https://git.postgresql.org/pg/commitdiff/9fdb675fc5d2de825414e05939727de8b120ae81 - Fix badly edited doc sentence. Noted by Vik Fearing and Robert Haas https://git.postgresql.org/pg/commitdiff/3cabe3863015efc72d513d3aa3b47d03eed96f49 - Document partprune.c a little better. Author: Amit Langote Reviewed-by: Álvaro Herrera, David Rowley Discussion: https://postgr.es/m/CA+HiwqGzq4D6z=8R0AP+XhbTFCQ-4Ct+t2ekqjE9Fpm84_JUGg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/971d7ddbe19ad9525457e65d01b8b6504ab57cc4 - Support partition pruning at execution time. Existing partition pruning is only able to work at plan time, for query quals that appear in the parsed query. This is good but limiting, as there can be parameters that appear later that can be usefully used to further prune partitions. This commit adds support for pruning subnodes of Append which cannot possibly contain any matching tuples, during execution, by evaluating Params to determine the minimum set of subnodes that can possibly match. We support more than just simple Params in WHERE clauses. Support additionally includes: 1. Parameterized Nested Loop Joins: The parameter from the outer side of the join can be used to determine the minimum set of inner side partitions to scan. 2. Initplans: Once an initplan has been executed we can then determine which partitions match the value from the initplan. Partition pruning is performed in two ways. When Params external to the plan are found to match the partition key we attempt to prune away unneeded Append subplans during the initialization of the executor. This allows us to bypass the initialization of non-matching subplans meaning they won't appear in the EXPLAIN or EXPLAIN ANALYZE output. For parameters whose value is only known during the actual execution then the pruning of these subplans must wait. Subplans which are eliminated during this stage of pruning are still visible in the EXPLAIN output. In order to determine if pruning has actually taken place, the EXPLAIN ANALYZE must be viewed. If a certain Append subplan was never executed due to the elimination of the partition then the execution timing area will state "(never executed)". Whereas, if, for example in the case of parameterized nested loops, the number of loops stated in the EXPLAIN ANALYZE output for certain subplans may appear lower than others due to the subplan having been scanned fewer times. This is due to the list of matching subnodes having to be evaluated whenever a parameter which was found to match the partition key changes. This commit required some additional infrastructure that permits the building of a data structure which is able to perform the translation of the matching partition IDs, as returned by get_matching_partitions, into the list index of a subpaths list, as exist in node types such as Append, MergeAppend and ModifyTable. This allows us to translate a list of clauses into a Bitmapset of all the subpath indexes which must be included to satisfy the clause list. Author: David Rowley, based on an earlier effort by Beena Emerson Reviewers: Amit Langote, Robert Haas, Amul Sul, Rajkumar Raghuwanshi, Jesper Pedersen Discussion: https://postgr.es/m/CAOG9ApE16ac-_VVZVvv0gePSgkg_BwYEV1NBqZFqDR2bBE0X0A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/499be013de65242235ebdde06adb08db887f0ea5 - Add bms_prev_member function. This works very much like the existing bms_last_member function, only it traverses through the Bitmapset in the opposite direction from the most significant bit down to the least significant bit. A special prevbit value of -1 may be used to have the function determine the most significant bit. This is useful for starting a loop. When there are no members less than prevbit, the function returns -2 to indicate there are no more members. Author: David Rowley Discussion: https://postgr.es/m/CAKJS1f-K=3d5MDASNYFJpUpc20xcBnAwNC1-AOeunhn0OtkWbQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5c0675215e153ba1297fd494b34af2fdebd645d1 Andres Freund pushed: - Fix GCC 7 snprintf() compiler warning. Make buffer 1 byte larger to fit a sign. It's actually impossible for there to be a sign in practice, but this is still required to keep GCC 7 happy. Cleanup from commit 51bc271790eb234a1ba4d14d3e6530f70de92ab5. Based on a suggestion from Peter Eisentraut. Author: Peter Geoghegan Reported-By: Peter Eisentraut Discussion: https://postgr.es/m/d1cc82ed-d07d-cef2-7c00-2e987f121648@2ndquadrant.com https://git.postgresql.org/pg/commitdiff/2b3031559a607833e4e265bb9726d52f398661e0 - Fix and improve pg_atomic_flag fallback implementation. The atomics fallback implementation for pg_atomic_flag was broken, returning the inverted value from pg_atomic_test_set_flag(). This was unnoticed because a) atomic flags were unused until recently b) the test code wasn't run when the fallback implementation was in use (because it didn't allow to test for some edge cases). Fix the bug, and improve the fallback so it has the same behaviour as the non-fallback implementation in the problematic edge cases. That breaks ABI compatibility in the back branches when fallbacks are in use, but given they were broken until now... Author: Andres Freund Reported-by: Daniel Gustafsson Discussion: https://postgr.es/m/FB948276-7B32-4B77-83E6-D00167F8EEB4@yesql.se https://postgr.es/m/20180406233854.uni2h3mbnveczl32@alap3.anarazel.de Backpatch: 9.5-, where the atomics abstraction was introduced. https://git.postgresql.org/pg/commitdiff/8c3debbbf61892dabd8b6f3f8d55e600a7901f2b - Attempt to fix endianess issues in new hash partition test. The tests added as part of 9fdb675fc5 yield differing results depending on endianess, causing buildfarm failures. As the differences are expected, split the hash partitioning tests into a different file and maintain alternative output. The separate file is so the amount of duplicated output is reduced. David produced the alternative output without a machine to test on, so it's possible this'll require a buildfarm cycle or two to get right. Author: David Rowley Discussion: https://postgr.es/m/CAKJS1f-6f4c2Qhuipe-GY7BKmFd0FMBobRnLS7hVCoAmTszsBg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/40e42e1024c580a50483d788b3f68668da750c64 - Blindly attempt to fix sepgsql tests broken due to 9fdb675fc5. The failure appears to solely be caused by the changed partition pruning logic. Author: Andres Freund Discussion: https://postgr.es/m/20180406210330.wmqw42wqgiicktli@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/4f813c7203e0c08395833b2bd75ac9ccf63c6264 - Deactive flapping checksum isolation tests. They've been broken for days, and prevent other tests from being run. The plan is to revert their addition later. Discussion: https://postgr.es/m/20180407162252.wfo5aorjrjw2n5ws@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/bf75fe47e444c01a35992a38a01d32b191f69aca - Raise error when affecting tuple moved into different partition. When an update moves a row between partitions (supported since 2f178441044b), our normal logic for following update chains in READ COMMITTED mode doesn't work anymore. Cross partition updates are modeled as an delete from the old and insert into the new partition. No ctid chain exists across partitions, and there's no convenient space to introduce that link. Not throwing an error in a partitioned context when one would have been thrown without partitioning is obviously problematic. This commit introduces infrastructure to detect when a tuple has been moved, not just plainly deleted. That allows to throw an error when encountering a deletion that's actually a move, while attempting to following a ctid chain. The row deleted as part of a cross partition update is marked by pointing it's t_ctid to an invalid block, instead of self as a normal update would. That was deemed to be the least invasive and most future proof way to represent the knowledge, given how few infomask bits are there to be recycled (there's also some locking issues with using infomask bits). External code following ctid chains should be updated to check for moved tuples. The most likely consequence of not doing so is a missed error. Author: Amul Sul, editorialized by me Reviewed-By: Amit Kapila, Pavan Deolasee, Andres Freund, Robert Haas Discussion: http://postgr.es/m/CAAJ_b95PkwojoYfz0bzXU8OokcTVGzN6vYGCNVUukeUDrnF3dw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f16241bef7cc271bff60e23de2f827a10e50dde8 - Remove overzeleous assertions in pg_atomic_flag code. The atomics code asserts proper alignment in various places. That's mainly because the alignment of 64bit integers is not sufficient for atomic operations on all platforms. Some ABIs only have four byte alignment, but don't have atomic behavior when crossing page boundaries. The flags code isn't affected by that however, as the type alignment always is sufficient for atomic operations. Nevertheless the code asserted alignment requirements. Before 8c3debbb it was only broken on hppa, after it probably affect further platforms. Thus remove the assertions for pg_atomic_flag operators. Per buildfarm animal pademelon. Discussion: https://postgr.es/m/7223.1523124425@sss.pgh.pa.us Backpatch: 9.5- https://git.postgresql.org/pg/commitdiff/d234602c28e8e1baea342602dbb404cee9fde47e Heikki Linnakangas pushed: - Fix incorrect description of USE_SLICING_BY_8_CRC32C. And a typo in the description of USE_SSE42_CRC32C_WITH_RUNTIME_CHECK, spotted by Daniel Gustafsson. https://git.postgresql.org/pg/commitdiff/8989f52b1b0636969545e6c8f6c813bc563ebcf5 - Also fix the descriptions in pg_config.h.win32. I missed pg_config.h.win32 in the previous commit that fixed these in pg_config.h.in. https://git.postgresql.org/pg/commitdiff/638a199fa9459dac42b588ccfcf7003539f37416 - Use ARMv8 CRC instructions where available. ARMv8 introduced special CPU instructions for calculating CRC-32C. Use them, when available, for speed. Like with the similar Intel CRC instructions, several factors affect whether the instructions can be used. The compiler intrinsics for them must be supported by the compiler, and the instructions must be supported by the target architecture. If the compilation target architecture does not support the instructions, but adding "-march=armv8-a+crc" makes them available, then we compile the code with a runtime check to determine if the host we're running on supports them or not. For the runtime check, use glibc getauxval() function. Unfortunately, that's not very portable, but I couldn't find any more portable way to do it. If getauxval() is not available, the CRC instructions will still be used if the target architecture supports them without any additional compiler flags, but the runtime check will not be available. Original patch by Yuqi Gu, heavily modified by me. Reviewed by Andres Freund, Thomas Munro. Discussion: https://www.postgresql.org/message-id/HE1PR0801MB1323D171938EABC04FFE7FA9E3110%40HE1PR0801MB1323.eurprd08.prod.outlook.com https://git.postgresql.org/pg/commitdiff/f044d71e331d77a0039cec0a11859b5a3c72bc95 - Fix the new ARMv8 CRC code for short and unaligned input. The code before the main loop, to handle the possible 1-7 unaligned bytes at the beginning of the input, was broken, and read past the input, if the the input was very short. https://git.postgresql.org/pg/commitdiff/3a5e0a91bb324ad2b2b1a0623a3f2e37772b43fc Stephen Frost pushed: - Rewrite pg_dump TAP tests. This reworks how the tests to run are defined. Instead of having to define all runs for all tests, we define those tests which should pass (generally using one of the defined broad hashes), add in any which should be specific for this test, and exclude any specific runs that shouldn't pass for this test. This ends up removing some 4k+ lines (more than half the file) but, more importantly, greatly simplifies the way runs-to-be-tested are defined. As discussed in the updated comments, for example, take the test which does CREATE TABLE test_table. That CREATE TABLE should show up in all 'full' runs of pg_dump, except those cases where 'test_table' is excluded, of course, and that's exactly how the test gets defined now (modulo a few other related cases, like where we dump only that table, or we dump the schema it's in, or we exclude the schema it's in): like => { %full_runs, %dump_test_schema_runs, only_dump_test_table => 1, section_pre_data => 1, }, unlike => { exclude_dump_test_schema => 1, exclude_test_table => 1, }, }, Next, we no longer expect every run to be listed for every test. If a run is listed in 'like' (directly or through a hash) then it's a 'like', unless it's listed in 'unlike' in which case it's an 'unlike'. If it isn't listed in either, then it's considered an 'unlike' automatically. Lastly, this changes the code to no longer use like/unlike but rather to use 'ok()' with 'diag()' which allows much more control over what gets spit out to the screen. Gone are the days of the entire dump being sent to the console, now you'll just get a couple of lines for each failing test which say the test that failed and the run that it failed on. This covers both the pg_dump TAP tests in src/bin/pg_dump and those in src/test/modules/test_pg_dump. https://git.postgresql.org/pg/commitdiff/446f7f5d789fe9ecfacd998407b5bee70aaa64f7 - Add default roles for file/program access. This patch adds new default roles named 'pg_read_server_files', 'pg_write_server_files', 'pg_execute_server_program' which allow an administrator to GRANT to a non-superuser role the ability to access server-side files or run programs through PostgreSQL (as the user the database is running as). Having one of these roles allows a non-superuser to use server-side COPY to read, write, or with a program, and to use file_fdw (if installed by a superuser and GRANT'd USAGE on it) to read from files or run a program. The existing misc file functions are also changed to allow a user with the 'pg_read_server_files' default role to read any files on the filesystem, matching the privileges given to that role through COPY and file_fdw from above. Reviewed-By: Michael Paquier Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net https://git.postgresql.org/pg/commitdiff/0fdc8495bff02684142a44ab3bc5b18a8ca1863a - Support new default roles with adminpack. This provides a newer version of adminpack which works with the newly added default roles to support GRANT'ing to non-superusers access to read and write files, along with related functions (unlinking files, getting file length, renaming/removing files, scanning the log file directory) which are supported through adminpack. Note that new versions of the functions are required because an environment might have an updated version of the library but still have the old adminpack 1.0 catalog definitions (where EXECUTE is GRANT'd to PUBLIC for the functions). This patch also removes the long-deprecated alternative names for functions that adminpack used to include and which are now included in the backend, in adminpack v1.1. Applications using the deprecated names should be updated to use the backend functions instead. Existing installations which continue to use adminpack v1.0 should continue to function until/unless adminpack is upgraded. Reviewed-By: Michael Paquier Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net https://git.postgresql.org/pg/commitdiff/11523e860f8fe29f9142fb63c44e01cd0d5e7375 - Remove explicit superuser checks in favor of ACLs. This removes the explicit superuser checks in the various file-access functions in the backend, specifically pg_ls_dir(), pg_read_file(), pg_read_binary_file(), and pg_stat_file(). Instead, EXECUTE is REVOKE'd from public for these, meaning that only a superuser is able to run them by default, but access to them can be GRANT'd to other roles. Reviewed-By: Michael Paquier Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net https://git.postgresql.org/pg/commitdiff/e79350fef2917522571add750e3e21af293b50fe - Allow group access on PGDATA. Allow the cluster to be optionally init'd with read access for the group. This means a relatively non-privileged user can perform a backup of the cluster without requiring write privileges, which enhances security. The mode of PGDATA is used to determine whether group permissions are enabled for directory and file creates. This method was chosen as it's simple and works well for the various utilities that write into PGDATA. Changing the mode of PGDATA manually will not automatically change the mode of all the files contained therein. If the user would like to enable group access on an existing cluster then changing the mode of all the existing files will be required. Note that pg_upgrade will automatically change the mode of all migrated files if the new cluster is init'd with the -g option. Tests are included for the backend and all the utilities which operate on the PG data directory to ensure that the correct mode is set based on the data directory permissions. Author: David Steele <david@pgmasters.net> Reviewed-By: Michael Paquier, with discussion amongst many others. Discussion: https://postgr.es/m/ad346fe6-b23e-59f1-ecb7-0e08390ad629%40pgmasters.net https://git.postgresql.org/pg/commitdiff/c37b3d08ca6873f9d4eaf24c72a90a550970cbb8 - Refactor dir/file permissions. Consolidate directory and file create permissions for tools which work with the PG data directory by adding a new module (common/file_perm.c) that contains variables (pg_file_create_mode, pg_dir_create_mode) and constants to initialize them (0600 for files and 0700 for directories). Convert mkdir() calls in the backend to MakePGDirectory() if the original call used default permissions (always the case for regular PG directories). Add tests to make sure permissions in PGDATA are set correctly by the tools which modify the PG data directory. Authors: David Steele <david@pgmasters.net>, Adam Brightwell <adam.brightwell@crunchydata.com> Reviewed-By: Michael Paquier, with discussion amongst many others. Discussion: https://postgr.es/m/ad346fe6-b23e-59f1-ecb7-0e08390ad629%40pgmasters.net https://git.postgresql.org/pg/commitdiff/da9b580d89903fee871cf54845ffa2b26bda2e11 - Fix EXEC BACKEND + Windows builds for group privs. Under EXEC BACKEND we also need to be going through the group privileges setup since we do support that on Unixy systems, so add that to SubPostmasterMain(). Under Windows, we need to simply return true from GetDataDirectoryCreatePerm(), but that wasn't happening due to a missing #else clause. Per buildfarm. https://git.postgresql.org/pg/commitdiff/2b74022473f0c7a7fbe11a4973f80478226275bd Andrew Gierth pushed: - Install errcodes.txt for use by extensions. Maintainers of out-of-tree PLs typically need access to the set of error codes. To avoid the need to duplicate that information in some form in PL source trees, provide errcodes.txt as part of a server installation. Thomas Munro, based on a suggestion from Andrew Gierth Discussion: https://postgr.es/m/87woykk7mu.fsf%40news-spur.riddles.org.uk https://git.postgresql.org/pg/commitdiff/1fd8690668635bab9dfa16b2885e6e474f8451ba - Support index INCLUDE in the AM properties interface. This rectifies an oversight in commit 8224de4f4, by adding a new property 'can_include' for pg_indexam_has_property, and adjusting the results of pg_index_column_has_property to give more appropriate results for INCLUDEd columns. https://git.postgresql.org/pg/commitdiff/49b0e300f7dd56b092c0046ee29dc2b15beea9a8 - Attempt to stabilize partition_prune test output. Disable index-only scan for tests that might report variable results for "Heap Fetches" statistic due to concurrent transactions affecting whether all-visible flags can be set. Author: David Rowley Discussion: https://postgr.es/m/CAKJS1f_yjtHDJnDzx1uuR_3D7beDVAkNQfWJhRLA1gvPCzkAhg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/b47a86f5008f2674af20dd00bc233e7b74e01bba == Pending Patches == Nikhil Sontakke and Tomáš Vondra traded patches to implement logical decoding of two-phase transactions. Yugo Nagata sent in two revisions of a patch to fix some issues in lockable views. Álvaro Herrera sent in a patch to fix some broken ATTACH PARTITION behavior. Tom Lane sent in a patch to refactor -L switch handling and construction of LDFLAGS. Alexander Lakhin sent in two revisions of a patch to fix "make installcheck-world" after "make clean" or precompiled binaries. Claudio Freire and Heikki Linnakangas traded patches to enable work_mem to be >1GB. Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes. Artur Zakirov sent in another revision of a patch to implement shared ISpell dictionaries. Ashutosh Bapat sent in two more revisions of a patch to make nested ConvertRowtypeExpr execution more efficient. Marina Polyakova sent in two more revisions of a patch to fix some pgbench serialization and deadlock errors. Konstantin Knizhnik sent in a patch to fix an issue where PostgreSQL can get stuck in deadlock detection. Marina Polyakova sent in a patch to add support for printing/reading MergeAction nodes. Aleksandr Parfenov sent in two more revisions of a patch to implement a more flexible configuration for full-text search. Nathan Bossart sent in another revision of a patch to fix some crashes in VACUUM. Heikki Linnakangas sent in a patch to reduce the number of PostmasterIsAlive calls during WAL redo. Julian Markwort sent in another revision of a patch to add a pg_stat_statements_plans view. Daniel Gustafsson sent in another revision of a patch to add an optional message to user when terminating/cancelling a backend. Konstantin Knizhnik sent in two more revisions of a patch to implement connection pooling per session in the backend. Magnus Hagander sent in another revision of a patch to implement a new pg_hba.conf option: clientcert=verify-full. Kyotaro HORIGUCHI sent in another revision of a patch to ensure that full_page_writes takes effect at REDO point and that additional XLOG_FPW_CHANGE is written only for turning-off. This fixes a race condition between config reload on checkpointer and StartupXLOG. Thomas Munro sent in a patch to force reopen when syncing in an attempt to fix a Linux issue which could cause data loss. Thomas Munro sent in four more revisions of a patch to make sure not to forget about fsync requests after a failed fsync(). Tom Lane sent in a patch to document the bootstrap data conversion. Fabien COELHO sent in another revision of a patch to make it possible to store pgbench SELECT results into variables. Alexander Korotkov sent in another revision of a patch to implement incremental sort. Tom Lane sent in another revision of a patch to remove useless DISTINCT clauses. Chapman Flack sent in three revisions of a patch to update the README for resource owners to reflect current realities. Peter Geoghegan sent in a patch to remove some superfluous amcheck INCLUDE tests. Jeevan Chalke sent in a patch to add partial path only when rel's consider_parallel is true. Per sqlsmith breakage. David Rowley sent in a patch to stabilize the tests for runtime partition pruning. Arseny Sher sent in a patch to fix slot's xmin advancement and subxact's lost snapshots in decoding.
В списке pgsql-announce по дате отправления: