Le PostgresOpen aura lieu à San Francisco du 6 au 8 septembre : https://2017.postgresopen.org/

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en mars

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20170312234158.GB31409@fetter.org

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org et en espagnol à pwn (a) arpug.com.ar.

Correctifs appliqués

Simon Riggs pushed:

Peter Eisentraut pushed:

Robert Haas pushed:

Tom Lane pushed:

  • Avoid dangling pointer to relation name in RLS code path in DoCopy(). With RLS active, "COPY tab TO ..." failed under -DRELCACHE_FORCE_RELEASE, and would sometimes fail without that, because it used the relation name directly from the relcache as part of the parsetree it's building. That becomes a potentially-dangling pointer as soon as the relcache entry is closed, a bit further down. Typical symptom if the relcache entry chanced to get cleared would be "relation does not exist" error with a garbage relation name, or possibly a core dump; but if you were really truly unlucky, the COPY might copy from the wrong table. Per report from Andrew Dunstan that regression tests fail with -DRELCACHE_FORCE_RELEASE. The core tests now pass for me (but have not tried "make check-world" yet). Discussion: https://postgr.es/m/7b52f900-0579-cda9-ae2e-de5da17090e6@2ndQuadrant.com http://git.postgresql.org/pg/commitdiff/a8df75b0a470f477dad75a7408e429e10c13fc07
  • Repair incorrect pg_dump labeling for some comments and security labels. We attached no schema label to comments for procedural languages, casts, transforms, operator classes, operator families, or text search objects. The first three categories of objects don't really have schemas, but pg_dump treats them as if they do, and it seems like the TocEntry fields for their comments had better match the TocEntry fields for the parent objects. (As an example of a possible hazard, the type names in a CAST will be formatted with the assumption of a particular search_path, so failing to ensure that this same path is active for the COMMENT ON command could lead to an error or to attaching the comment to the wrong cast.) In the last six cases, this was a flat-out error --- possibly mine to begin with, but it was a long time ago. The security label for a procedural language was likewise not correctly labeled as to schema, and both the comment and security label for a procedural language were not correctly labeled as to owner. In simple cases the restore would accidentally work correctly anyway, since these comments and security labels would normally get emitted right after the owning object, and so the search path and active user would be correct anyhow. But it could fail in corner cases; for example a schema-selective restore would omit comments it should include. Giuseppe Broccolo noted the oversight, and proposed the correct fix, for text search dictionary objects; I found the rest by cross-checking other dumpComment() calls. These oversights are ancient, so back-patch all the way. Discussion: https://postgr.es/m/CAFzmHiWwwzLjzwM4x5ki5s_PDMR6NrkipZkjNnO3B0xEpBgJaA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/299990ba16ab75b09b7fed61322f1f23fdc7cf4c
  • Remove vestigial grammar support for CHARACTER ... CHARACTER SET option. The SQL standard says that you should be able to write "CHARACTER SET foo" as part of the declaration of a char-type column. We don't implement that, but a rough form of support has existed in gram.y since commit f10b63923. That's now sat there for nigh 20 years without anyone fleshing it out --- and even if someone did, the contemplated approach of having separate data type name(s) for every character set certainly isn't what we'd do today. Let's just remove the grammar production; if anyone is ever motivated to work on this, reinventing the grammar support is a trivial fraction of what they'd have to do. And we've never documented anything about supporting such a clause. Per gripe from Neha Khatri. Discussion: https://postgr.es/m/CAFO0U+-iOS5oYN5v3SBuZvfhPUTRrkDFEx8w7H17B07Rwg3YUA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/11324e408f0e3a25621c611467927c644894b30d
  • Fix pgbench's failure to honor the documented long-form option "--builtin". Not only did it not accept --builtin as a synonym for -b, but what it did accept as a synonym was --tpc-b (huh?), which it got even further wrong by marking as no_argument, so that if you did try that you got a core dump. I suppose this is leftover from some early design for the new switches added by commit 8bea3d221, but it's still pretty sloppy work. Per bug #14580 from Stepan Pesternikov. Back-patch to 9.6 where the error was introduced. Report: https://postgr.es/m/20170307123347.25054.73207@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/ef2662394455578f6c57e99a7896c69bdd9fbd74
  • Clean up test_ifaddrs a bit. We customarily #include <netinet/in.h> before <arpa/inet.h>; according to our git history (cf commit 527f8babc) there used to be platform(s) where <arpa/inet.h> didn't compile otherwise. That's probably not really an issue anymore, but since test_ifaddrs.c is the one and only place in our code that's not following that rule, bring it into line. Also remove #include <sys/socket.h>, as that's duplicative given that libpq/ifaddr.h does so (via pqcomm.h). In passing, add a .gitignore file so nobody accidentally commits the test_ifaddrs executable, as I nearly did. I see no particular need to back-patch this, as it's just neatnik-ism considering we don't build test_ifaddrs by default, or even document it anywhere. http://git.postgresql.org/pg/commitdiff/03cf2219346aa78ecd1b6d4501a7697692a43c62
  • Invent start_proc parameters for PL/Tcl. Define GUCs pltcl.start_proc and pltclu.start_proc. When set to a nonempty value at the time a new Tcl interpreter is created, the parameterless pltcl or pltclu function named by the GUC is called to allow user-controlled initialization to occur within the interpreter. This is modeled on plv8's start_proc parameter, and also has much in common with plperl's on_init feature. It allows users to fully replace the "modules" feature that was removed in commit 817f2a586. Since an initializer function could subvert later Tcl code in nearly arbitrary ways, mark both GUCs as SUSET for now. It would be nice to find a way to relax that someday; but the corresponding GUCs in plperl are also SUSET, and there's not been much complaint. Discussion: https://postgr.es/m/22067.1488046447@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/0d2b1f305dc78d536d80cfb4bb2ac4d7104453db
  • Silence compiler warnings in tbm_prepare_shared_iterate(). Maybe Robert's compiler can convince itself that these variables are never used uninitialized, but mine can't. http://git.postgresql.org/pg/commitdiff/270d7dd8a5a7128fc2b859f3bf95e2c1fb45be79
  • Use doubly-linked block lists in aset.c to reduce large-chunk overhead. Large chunks (those too large for any palloc freelist) are managed as separate blocks. Formerly, realloc'ing or pfree'ing such a chunk required O(N) time in a context with N blocks, since we had to traipse down the singly-linked block list to locate the block's predecessor before we could fix the list links. This can result in O(N^2) runtime in situations where large numbers of such chunks are manipulated within one context. Cases like that were not foreseen in the original design of aset.c, and indeed didn't arise until fairly recently. But such problems can now occur in reorderbuffer.c and in hash joining, both of which make repeated large requests without scaling up their request size as they do so, and which will free their requests in not-necessarily-LIFO order. To fix, change the block list from singly-linked to doubly-linked. This adds another 4 or 8 bytes to ALLOC_BLOCKHDRSZ, but that doesn't seem like unacceptable overhead, since aset.c's blocks are normally 8K or more, and never less than 1K in current practice. In passing, get rid of some redundant AllocChunkGetPointer() calls in AllocSetRealloc (the compiler might be smart enough to optimize these away anyway, but no need to assume that) and improve AllocSetCheck's checking of block header fields. Back-patch to 9.4 where reorderbuffer.c appeared. We could take this further back, but currently there's no evidence that it would be useful. Discussion: https://postgr.es/m/CAMkU=1x1hvue1XYrZoWk_omG0Ja5nBvTdvgrOeVkkeqs71CV8g@mail.gmail.com http://git.postgresql.org/pg/commitdiff/ff97741bc810390db6dd4da0f31ee1e93c8d3abb
  • Silence compiler warnings in BitmapHeapNext(). Same disease as 270d7dd8a5a7128fc2b859f3bf95e2c1fb45be79. http://git.postgresql.org/pg/commitdiff/15d03e597662847e13428a8b3ce9dd59c38decf3
  • Put back <float.h> in a few files that need it for _isnan(). Further fallout from commit c29aff959: there are some files that need <float.h>, and were getting it from datatype/timestamp.h, but it was not apparent in my (tgl's) testing because the requirement for <float.h> exists only on certain Windows toolchains. Report and patch by David Rowley. Discussion: https://postgr.es/m/CAKJS1f-BHceaFzZScFapDV48gUVM2CAOBfhkgffdqXzFb+kwew@mail.gmail.com http://git.postgresql.org/pg/commitdiff/86dbbf20d8496ede77566873d1e22cd11c1e544c
  • Suppress compiler warning in non-USE_LIBXML builds. Compilers that don't realize that ereport(ERROR) doesn't return complained that XmlTableGetValue() failed to return a value. Also, make XmlTableFetchRow's non-USE_LIBXML case look more like the other ones. As coded, it could lead to "unreachable code" warnings with USE_LIBXML enabled. Oversights in commit fcec6caaf. Per buildfarm. http://git.postgresql.org/pg/commitdiff/f379121093deb5465b01d93ebe9410d96c6cd093
  • Suppress compiler warning in slab.c. Compilers that don't realize that elog(ERROR) doesn't return complained that SlabRealloc() failed to return a value. While at it, fix the rather muddled header comment for the function. Per buildfarm. http://git.postgresql.org/pg/commitdiff/2f899e7d37ece937740c99164dd846c4b6f884eb
  • Document intentional violations of header inclusion policy. Although there are good reasons for our policy of including postgres.h as the first #include in every .c file, never from .h files, there are two places where it seems expedient to violate the policy because the alternative is to modify externally-supplied .c files. (In the case of the regexp library, the idea that it's externally-supplied is kind of at odds with reality, but I haven't entirely given up hope that it will become a standalone project some day.) Add some comments to make it explicit that this is a policy violation and provide the reasoning. In passing, move #include "miscadmin.h" out of regcomp.c and into regcustom.h, which is where it should be if we're taking this reasoning seriously at all. Discussion: https://postgr.es/m/CAEepm=2zCoeq3QxVwhS5DFeUh=yU6z81pbWMgfOB8OzyiBwxzw@mail.gmail.com Discussion: https://postgr.es/m/11634.1488932128@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/d6b059ec740a6affce9a069f1210d161068317e3
  • Bring plpgsql into line with header inclusion policy. We have a project policy that every .c file should start by including postgres.h, postgres_fe.h, or c.h as appropriate; and then there is no need for any .h file to explicitly include any of these. (The core reason for this policy is to make it easy to verify that pg_config_os.h is included before any system headers such as <stdio.h>; without that, we have portability issues on some platforms due to variation in largefile options across different modules in the backend. Also, if .h files were responsible for choosing which of these key headers to include, .h files that need to be includable in either frontend or backend compiles would be in trouble.) plpgsql was blithely ignoring this policy, so whack it upside the head until it complies. I also chose to standardize on including plpgsql's own .h files after all core-system headers that it pulls in. That could've been done either way, but this way seems saner. Discussion: https://postgr.es/m/CAEepm=2zCoeq3QxVwhS5DFeUh=yU6z81pbWMgfOB8OzyiBwxzw@mail.gmail.com Discussion: https://postgr.es/m/11634.1488932128@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/08da52859a1fadeac10aab621c6c793791ec1f2c
  • Fix inclusions of postgres_fe.h from .h files. We have a project policy that every .c file should start by including postgres.h, postgres_fe.h, or c.h as appropriate; and then there is no need for any .h file to explicitly include any of these. Fix a few headers that were violating this policy by including postgres_fe.h. Discussion: https://postgr.es/m/CAEepm=2zCoeq3QxVwhS5DFeUh=yU6z81pbWMgfOB8OzyiBwxzw@mail.gmail.com Discussion: https://postgr.es/m/11634.1488932128@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/9722bb5757c5e90617be685bf127911b63efe08d
  • Fix inclusions of c.h from .h files. We have a project policy that every .c file should start by including postgres.h, postgres_fe.h, or c.h as appropriate; and then there is no need for any .h file to explicitly include any of these. Fix a few headers that were violating this policy by including c.h. Discussion: https://postgr.es/m/CAEepm=2zCoeq3QxVwhS5DFeUh=yU6z81pbWMgfOB8OzyiBwxzw@mail.gmail.com Discussion: https://postgr.es/m/11634.1488932128@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/a72f0365db4168e7d720608fe3ac0ca3fe9355df
  • Fix timestamptz regression test to still work with latest IANA zone data. The IANA timezone crew continues to chip away at their project of removing timezone abbreviations that have no real-world currency from their database. The tzdata2017a update removes all such abbreviations for South American zones, as well as much of the Pacific. This breaks some test cases in timestamptz.sql that were expecting America/Santiago and America/Caracas to have non-numeric abbreviations. The test cases involving America/Santiago seem to have selected that zone more or less at random, so just replace it with America/New_York, which is of similar longitude. The cases involving America/Caracas are harder since they were chosen to test a time-varying zone abbreviation around a point where it changed meaning in the backwards direction. Fortunately, Europe/Moscow has a similar case in 2014, and the MSK/MSD abbreviations are well enough attested that IANA seems unlikely to decide to remove them from the database in future. With these changes, this regression test should pass when using any IANA zone database from 2015 or later. One could wish that there were a few years more daylight on how out-of-date your zone database can be ... but really the --with-system-tzdata option is only meant for use on platforms where the zone database is kept up-to-date pretty faithfully, so I do not think this is a big objection. Discussion: https://postgr.es/m/6749.1489087470@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/f077e1b2e374fadc443b6aa9fa54ad9bb6f908c7
  • Fix hard-coded relkind constants in pg_dump.c. Although it's reasonable to expect that most of these constants will never change, that does not make it good programming style to hard-code the value rather than using the RELKIND_FOO macros. There were only a few such violations, and all relatively new AFAICT. Existing style is mostly to inject relkind values into constructed query strings using %c. I did not bother to touch places that did it like that, but really a better technique is to stringify the RELKIND macro, at least in places where you'd want single quotes around the code character. That avoids any runtime effort and keeps the RELKIND symbol close to where it's used. Discussion: https://postgr.es/m/11145.1488931324@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/fe797b4a6a69ec0c9bf6ff992eac20c4084fda41
  • Make CppAsString2() more visible in c.h. For some reason this standard C string-processing hack was buried in an NLS-related section of c.h. Put it beside CppAsString() so that people are more likely to find it and not be tempted to reinvent local copies, as I nearly did. And provide a more helpful comment, too. http://git.postgresql.org/pg/commitdiff/9cfc4deeb9e6bd8162bd85dce5809d4d7aea2b66
  • Fix hard-coded relkind constants in psql/describe.c. Although it's reasonable to expect that most of these constants will never change, that does not make it good programming style to hard-code the value rather than using the RELKIND_FOO macros. Discussion: https://postgr.es/m/11145.1488931324@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/395bfaae8e786eb17fc9dd79e4636f97c9d9b820
  • Fix portability problem in Catalog.pm. Commit 7666e73a2 introduced a dependency on filehandles' input_line_number method, but apparently that's a Perl neologism. Use $. instead, which works at least back to Perl 5.10, and hopefully back to 5.8. Jeff Janes Discussion: https://postgr.es/m/CAMkU=1wuQW=xVfu-14A4VCvxO0ohkD3m9vk6HOj_dprQoKNAQw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/15bb93e28e49fdf4f28d509c07d1527886acb3e2
  • Fix hard-coded relkind constants in assorted src/bin files. Although it's reasonable to expect that most of these constants will never change, that does not make it good programming style to hard-code the value rather than using the RELKIND_FOO macros. Discussion: https://postgr.es/m/11145.1488931324@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/fcd778eb703c154c0fbba249e17c21b7ae4de19b
  • Add .gitignore to contrib/amcheck. Oversight in commit 3717dc149. http://git.postgresql.org/pg/commitdiff/574268e37b3b7b3449eb0034013443d88f4bd320
  • Fix hard-coded relkind constants in assorted other files. Although it's reasonable to expect that most of these constants will never change, that does not make it good programming style to hard-code the value rather than using the RELKIND_FOO macros. I think I've now gotten all the hard-coded references in C code. Unfortunately there's no equally convenient way to parameterize SQL files ... Discussion: https://postgr.es/m/11145.1488931324@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/9c2635e26f6f4e34b3b606c0fc79d0e111953a74
  • Un-break things on IPv6-less platforms. Commit be37c2120 forgot to teach initdb about commenting out the IPv6 replication entry that it caused to exist in pg_hba.conf.sample. Per buildfarm. http://git.postgresql.org/pg/commitdiff/a83e4b4f31c7afa5f7360086ebb1916cc99a4dbe
  • Change the relkind for partitioned tables from 'P' to 'p'. Seven of the eight other relkind codes are lower-case, so it wasn't consistent for this one to be upper-case. Fix it while we still can. Historical notes: the reason for the lone exception, i.e. sequences being 'S', is that 's' was once used for "special" relations. Also, at one time the partitioned-tables patch used both 'P' and 'p', but that got changed, leaving only a surprising choice behind. This also fixes a couple little bits of technical debt, such as type_sanity.sql not knowing that 'm' is a legal value for relkind. Discussion: https://postgr.es/m/27899.1488909319@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/8b358b42f8eb6156a82ac9a41fc4e8335c8dc37a
  • Sanitize newlines in object names in "pg_restore -l" output. Commits 89e0bac86 et al replaced newlines with spaces in object names printed in SQL comments, but we neglected to consider that the same names are also printed by "pg_restore -l", and a newline would render the output unparseable by "pg_restore -L". Apply the same replacement in "-l" output. Since "pg_restore -L" doesn't actually examine any object names, only the dump ID field that starts each line, this is enough to fix things for its purposes. The previous fix was treated as a security issue, and we might have done that here as well, except that the issue was reported publicly to start with. Anyway it's hard to see how this could be exploited for SQL injection; "pg_restore -L" doesn't do much with the file except parse it for leading integers. Per bug #14587 from Milos Urbanek. Back-patch to all supported versions. Discussion: https://postgr.es/m/20170310155318.1425.30483@wrigleys.postgresql.org http://git.postgresql.org/pg/commitdiff/f39ddd843667c68f760cb4cf23c89c1f1d9aebb8
  • Reduce log verbosity of startup/shutdown for launcher subprocesses. There's no really good reason why the autovacuum launcher and logical replication launcher should announce themselves at startup and shutdown by default. Users don't care that those processes exist, and it's inconsistent that those background processes announce themselves while others don't. So, reduce those messages from LOG to DEBUG1 level. I was sorely tempted to reduce the "starting logical replication worker for subscription ..." message to DEBUG1 as well, but forebore for now. Those processes might possibly be of direct interest to users, at least until logical replication is a lot better shaken out than it is today. Discussion: https://postgr.es/m/19479.1489121003@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/6ec4c8584c45ee784a95e02c40bb643430dee32a
  • contrib/amcheck needs RecentGlobalXmin to be PGDLLIMPORT'ified. Per buildfarm. Maybe some of the other xmin variables in snapmgr.h ought to get this too, but for the moment I'm just interested in un-breaking the buildfarm. http://git.postgresql.org/pg/commitdiff/56018bf26eec1a0b4bf20303c98065a8eb1b0c5d
  • Improve postmaster's logging of listen socket creation. When one of the kernel calls in the socket()/bind()/listen() sequence fails, include the specific address we're trying to bind to in the log message. This greatly eases debugging of network misconfigurations. Also, after successfully setting up a listen socket, report its address in the log, to ease verification that the expected addresses were bound. There was some debate about whether to print this message at LOG level or only DEBUG1, but the majority of votes were for the former. Discussion: https://postgr.es/m/9564.1489091245@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/f9dfa5c9776649f769d537dd0923003b35f128de
  • Add a "subtransaction" command to PL/Tcl. This allows rolling back the effects of some SPI commands without having to fail the entire PL/Tcl function. Victor Wagner, reviewed by Pavel Stehule Discussion: https://postgr.es/m/20170108205750.2dab04a1@wagner.wagner.home http://git.postgresql.org/pg/commitdiff/b58fd4a9cab21e9d937a4e369bab31b3a5d24710

Stephen Frost pushed:

  • pg_upgrade: Fix large object COMMENTS, SECURITY LABELS. When performing a pg_upgrade, we copy the files behind pg_largeobject and pg_largeobject_metadata, allowing us to avoid having to dump out and reload the actual data for large objects and their ACLs. Unfortunately, that isn't all of the information which can be associated with large objects. Currently, we also support COMMENTs and SECURITY LABELs with large objects and these were being silently dropped during a pg_upgrade as pg_dump would skip everything having to do with a large object and pg_upgrade only copied the tables mentioned to the new cluster. As the file copies happen after the catalog dump and reload, we can't simply include the COMMENTs and SECURITY LABELs in pg_dump's binary-mode output but we also have to include the actual large object definition as well. With the definition, comments, and security labels in the pg_dump output and the file copies performed by pg_upgrade, all of the data and metadata associated with large objects is able to be successfully pulled forward across a pg_upgrade. In 9.6 and master, we can simply adjust the dump bitmask to indicate which components we don't want. In 9.5 and earlier, we have to put explciit checks in in dumpBlob() and dumpBlobs() to not include the ACL or the data when in binary-upgrade mode. Adjustments made to the privileges regression test to allow another test (large_object.sql) to be added which explicitly leaves a large object with a comment in place to provide coverage of that case with pg_upgrade. Back-patch to all supported branches. Discussion: https://postgr.es/m/20170221162655.GE9812@tamriel.snowman.net http://git.postgresql.org/pg/commitdiff/ff992c074e308ade204a38eb43a6d19c8403414e
  • pg_dump: Properly handle public schema ACLs with --clean. pg_dump has always handled the public schema in a special way when it comes to the "--clean" option. To wit, we do not drop or recreate the public schema in "normal" mode, but when we are run in "--clean" mode then we do drop and recreate the public schema. When running in "--clean" mode, the public schema is dropped and then recreated and it is recreated with the normal schema-default privileges of "nothing". This is unlike how the public schema starts life, which is to have CREATE and USAGE GRANT'd to the PUBLIC role, and that is what is recorded in pg_init_privs. Due to this, in "--clean" mode, pg_dump would mistakenly only dump out the set of privileges required to go from the initdb-time privileges on the public schema to whatever the current-state privileges are. If the privileges were not changed from initdb time, then no privileges would be dumped out for the public schema, but with the schema being dropped and recreated, the result was that the public schema would have no ACLs on it instead of what it should have, which is the initdb-time privileges. Practically speaking, this meant that pg_dump with --clean mode dumping a database where the ACLs on the public schema were not changed from the default would, upon restore, result in a public schema with *no* privileges GRANT'd, not matching the state of the existing database (where the initdb-time privileges would have been CREATE and USAGE to the PUBLIC role for the public schema). To fix, adjust the query in getNamespaces() to ignore the pg_init_privs entry for the public schema when running in "--clean" mode, meaning that the privileges for the public schema would be dumped, correctly, as if it was going from a newly-created schema to the current state (which is, indeed, what will happen during the restore thanks to the DROP/CREATE). Only the public schema is handled in this special way by pg_dump, no other initdb-time objects are dropped/recreated in --clean mode. Back-patch to 9.6 where the bug was introduced. Discussion: https://postgr.es/m/3534542.o3cNaKiDID%40techfox http://git.postgresql.org/pg/commitdiff/330b84d8c40864007833e05dc9d849c4bda77240
  • psql: Add \gx command. It can often be useful to use expanded mode output (\x) for just a single query. Introduce a \gx which acts exactly like \g except that it will force expanded output mode for that one \gx call. This is simpler than having to use \x as a toggle and also means that the user doesn't have to worry about the current state of the expanded variable, or resetting it later, to ensure a given query is always returned in expanded mode. Primairly Christoph's patch, though I did tweak the documentation and help text a bit, and re-indented the tab completion section. Author: Christoph Berg Reviewed By: Daniel Verite Discussion: https://postgr.es/m/20170127132737.6skslelaf4txs6iw%40msg.credativ.de http://git.postgresql.org/pg/commitdiff/b2678efd43f17db7dfa04e0ca076ea01275cd9bc
  • Expose explain's SUMMARY option. This exposes the existing explain summary option to users to allow them to choose if they wish to have the planning time and totalled run time included in the EXPLAIN result. The existing default behavior is retained if SUMMARY is not specified- running explain without analyze will not print the summary lines (just the planning time, currently) while running explain with analyze will include the summary lines (both the planning time and the totalled execution time). Users who wish to see the summary information for plain explain can now use: EXPLAIN (SUMMARY ON) query; Users who do not want to have the summary printed for an analyze run can use: EXPLAIN (ANALYZE ON, SUMMARY OFF) query; With this, we can now also have EXPLAIN ANALYZE queries included in our regression tests by using: EXPLAIN (ANALYZE ON, TIMING OFF, SUMMARY off) query; I went ahead and added an example of this, which will hopefully not make the buildfarm complain. Author: Ashutosh Bapat Discussion: https://postgr.es/m/CAFjFpReE5z2h98U2Vuia8hcEkpRRwrauRjHmyE44hNv8-xk+XA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/f9b1a0dd403ec0931213c66d5f979a3d3e8e7e30
  • Add relkind checks to certain contrib modules. The contrib extensions pageinspect, pg_visibility and pgstattuple only work against regular relations which have storage. They don't work against foreign tables, partitioned (parent) tables, views, et al. Add checks to the user-callable functions to return a useful error message to the user if they mistakenly pass an invalid relation to a function which doesn't accept that kind of relation. In passing, improve some of the existing checks to use ereport() instead of elog(), add a function to consolidate common checks where appropriate, and add some regression tests. Author: Amit Langote, with various changes by me Reviewed by: Michael Paquier and Corey Huinker Discussion: https://postgr.es/m/ab91fd9d-4751-ee77-c87b-4dd704c1e59c@lab.ntt.co.jp http://git.postgresql.org/pg/commitdiff/c08d82f38ebf763b79bd43ae34b7310ee47aaacd
  • pgstattuple: Fix typo partitiond -> partitioned. Pointed out by Michael Paquier http://git.postgresql.org/pg/commitdiff/90e91e242fe99582b6d2dc18177e79d99c91695d

Andres Freund pushed:

  • Make simplehash.h grow hashtable in additional cases. Increase the size when either the distance between actual and optimal slot grows too large, or when too many subsequent entries would have to be moved. This addresses reports that the simplehash performed, sometimes considerably, worse than dynahash. The reason turned out to be that insertions into the hashtable where, due to the use of parallel query, in effect done from another hashtable, in hash-value order. If the target hashtable, due to mis-estimation, was sized a lot smaller than the source table(s) that lead to very imbalanced tables; a lot of entries in many close-by buckets from the source tables were inserted into a single, wider, bucket on the target table. As the growth factor was solely computed based on the fillfactor, the performance of the table decreased further and further. b81b5a96f424531b was an attempt to address this problem for hash aggregates (but not for bitmap scans), but it turns out that the current method of mixing hash values often actually leaves neighboring hash-values close to each other, just in different value range. It might be worth revisiting that independently of the performance issues addressed in this patch.. To address that problem resize tables in two additional cases: Firstly when the optimal position for an entry would be far from the actual position, secondly when many entries would have to be moved to make space for the new entry (while satisfying the robin hood property). Due to the additional resizing threshold it seems possible, and testing confirms that so far, that a higher fillfactor doesn't hurt performance and saves a bit of memory. It seems better to increase it now, before a release containing any of this code, rather than wonder in some later release. The various boundaries aren't determined in a particularly scientific manner, they might need some fine-tuning. In all my tests the new code now, even with parallelism, performs at least as good as the old code, in several scenarios significantly better. Reported-By: Dilip Kumar, Robert Haas, Kuntal Ghosh Discussion: https://postgr.es/m/CAFiTN-vagvuAydKG9VnWcoK=ADAhxmOa4ZTrmNsViBBooTnriQ@mail.gmail.com https://postgr.es/m/CAGz5QC+=fNTYgzMLTBUNeKt6uaWZFXJbkB5+7oWm-n9DwVxcLA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/d4c62a6b623d6eef88218158e9fa3cf974c6c7e5
  • Add amcheck extension to contrib. This is the beginning of a collection of SQL-callable functions to verify the integrity of data files. For now it only contains code to verify B-Tree indexes. This adds two SQL-callable functions, validating B-Tree consistency to a varying degree. Check the, extensive, docs for details. The goal is to later extend the coverage of the module to further access methods, possibly including the heap. Once checks for additional access methods exist, we'll likely add some "dispatch" functions that cover multiple access methods. Author: Peter Geoghegan, editorialized by Andres Freund Reviewed-By: Andres Freund, Tomas Vondra, Thomas Munro, Anastasia Lubennikova, Robert Haas, Amit Langote Discussion: CAM3SWZQzLMhMwmBqjzK+pRKXrNUZ4w90wYMUWfkeV8mZ3Debvw@mail.gmail.com http://git.postgresql.org/pg/commitdiff/3717dc149ecf44b8be95350a68605ba7299474fd
  • amcheck: editorialize variable name & comment. No exclusive lock is taken anymore... http://git.postgresql.org/pg/commitdiff/fcd8d25d38b5f42ec4ae77a673813c2dc279ccf7
  • Enable 64 bit atomics on ARM64. Previously they were disabled due to performance concerns on 32bit arm, where 64bit atomics are often implemented via kernel traps. Author: Roman Shaposhnik Discussion: http://postgr.es/m/CA+ULb+uErkFuXUCCXWHYvnV5KnAyjGUzzRcPA-M0cgO+Hm4RSA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/f8f1430ae709c0018a217c77893754f38b3c5b93
  • Improve expression evaluation test coverage. Upcoming patches are revamping expression evaluation significantly. It therefore seems prudent to try to ensure that the coverage of the existing evaluation code is high. This commit adds coverage for the cases that can reasonably be tested. There's still a bunch of unreachable error messages and such, but otherwise this achieves nearly full regression test coverage (with the exception of the unused GetAttributeByNum/GetAttributeByName). Author: Andres Freund Discussion: https://postgr.es/m/20170310194021.ek4bs4bl2khxkmll@alap3.anarazel.de http://git.postgresql.org/pg/commitdiff/ce38949ba23ab311f274aa4196be09d18d30e5a6

Heikki Linnakangas pushed:

Magnus Hagander pushed:

Fujii Masao pushed:

  • Fix connection leak in DROP SUBSCRIPTION command, take 2. Commit 898a792eb8283e31efc0b6fcbc03bbcd5f7df667 fixed the connection leak issue, but it was an unreliable way of bugfix. This bugfix was assuming that walrcv_command() subroutine cannot throw an error, but it's untenable assumption. For example, if it will be changed so that an error is thrown, connection leak issue will happen again. This patch ensures that the connection is closed even when walrcv_command() subroutine throws an error. Patch by me, reviewed by Petr Jelinek and Michael Paquier Discussion: https://www.postgresql.org/message-id/2058.1487704345@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/77d21970ae19418f321e6a76ddf1a57ae999c77a
  • Prevent logical rep workers with removed subscriptions from starting. Any logical rep workers must have their subscription entries in pg_subscription. To ensure this, we need to prevent the launcher from starting new worker corresponding to the subscription that DROP SUBSCRIPTION command is removing. To implement this, previously LogicalRepLauncherLock was introduced and held until the end of transaction running DROP SUBSCRIPTION. But using LWLock for that purpose was not valid. Instead, this commit changes DROP SUBSCRIPTION so that it takes AccessExclusiveLock on pg_subscription, in order to ensure that the launcher cannot see any subscriptions being removed. Also this commit gets rid of LogicalRepLauncherLock. Patch by me, reviewed by Petr Jelinek Discussion: https://www.postgresql.org/message-id/CAHGQGwHPi8ky-yANFfe0sgmhKtsYcQLTnKx07bW9S7-Rn1746w@mail.gmail.com http://git.postgresql.org/pg/commitdiff/4eafdcc27608dfb8a3afa3155d6ae07f66179782

Ãlvaro Herrera pushed:

  • Support XMLTABLE query expression. XMLTABLE is defined by the SQL/XML standard as a feature that allows turning XML-formatted data into relational form, so that it can be used as a <table primary> in the FROM clause of a query. This new construct provides significant simplicity and performance benefit for XML data processing; what in a client-side custom implementation was reported to take 20 minutes can be executed in 400ms using XMLTABLE. (The same functionality was said to take 10 seconds using nested PostgreSQL XPath function calls, and 5 seconds using XMLReader under PL/Python). The implemented syntax deviates slightly from what the standard requires. First, the standard indicates that the PASSING clause is optional and that multiple XML input documents may be given to it; we make it mandatory and accept a single document only. Second, we don't currently support a default namespace to be specified. This implementation relies on a new executor node based on a hardcoded method table. (Because the grammar is fixed, there is no extensibility in the current approach; further constructs can be implemented on top of this such as JSON_TABLE, but they require changes to core code.) Author: Pavel Stehule, Ãlvaro Herrera Extensively reviewed by: Craig Ringer Discussion: https://postgr.es/m/CAFj8pRAgfzMD-LoSmnMGybD0WsEznLHWap8DO79+-GTRAPR4qA@mail.gmail.com http://git.postgresql.org/pg/commitdiff/fcec6caafa2346b6c9d3ad5065e417733bd63cd9
  • Fix XMLTABLE on older libxml2. libxml2 older than 2.9.1 does not have xmlXPathSetContextNode (released in 2013, so reasonable platforms have trouble). That function is fairly trivial, so I have inlined it in the one added caller. This passes tests on my machine; let's see what the buildfarm thinks about it. Per joint complaint from Tom Lane and buildfarm. http://git.postgresql.org/pg/commitdiff/a9f66f92533b2bfd7abf289219152091b7697e87

Michael Meskes pushed:

Joe Conway pushed:

Correctifs en attente

Jim Nasby sent in another revision of a patch to add SPI_execute_callback() and a callback-based DestReceiver, and modify plpython to use same.

Etsuro Fujita, Ashutosh Bapat, and David Rowley traded patches to fix an issue where foreign Join pushdowns were not working properly for outer joins.

Amit Langote sent in two more revisions of a patch to avoid creating scan nodes for partitioned tables and not allocate storage for partitioned tables.

Kyotaro HORIGUCHI sent in another revision of a patch to use a radix tree for character conversion.

Kyotaro HORIGUCHI sent in another revision of a patch to fix some infelicities between logical replication and database encoding.

Masahiko Sawada sent in a patch to update the documentation to reflect the fact that oldestxmin is now shown in VACUUM.

Dagfinn Ilmari Mannsåker sent in another revision of a patch to fix many perlcritic exceptions.

Kyotaro HORIGUCHI sent in a patch to add a WAL relief vent for replication slots.

Takeshi Ideriha sent in another revision of a patch to add DECLARE STATEMENT to ECPG.

Petr Jelínek sent in a patch to logical replication apply to run with sync commit off by default.

Robert Haas sent in a patch to fix the computation of parallel workers to distinguish "can't have a heap page" from "found zero heap pages."

Kyotaro HORIGUCHI sent in another revision of a patch to remove NamedLWLockTrancheArray.

Masahiko Sawada sent in another revision of a patch to add a GUC for cleanup indexes threshold.

Thomas Munro sent in two more revisions of a patch to add a [parallel [shared]] hash.

Aleksander Alekseev sent in three more revisions of a patch to make declarative partitioning work faster when large numbers of partitions are present.

Masahiko Sawada sent in another revision of a patch to report the number of skipped frozen pages by manual VACUUM.

David Steele sent in another revision of a patch to add a configurable file mode mask.

Amos Bird sent in a patch to make psql show indexes with type info.

Andreas Karlsson sent in a patch to rename the default log directory from pg_log to log.

Petr Jelínek and Peter Eisentraut traded patches to allow enable an existing data copy for logical replication.

Andres Freund sent in another revision of a patch to speed up expression processing.

Jan Michálek sent in another revision of a patch to allow formatting tables in psql as markdown, mediawiki, and rst.

Masahiko Sawada sent in another revision of a patch to allow transactions across foreign tables.

Masahiko Sawada sent in a patch to document the fact that PREPARE TRANSACTION is now part of ECPG.

Simon Riggs and Dean Rasheed traded patches to speed up replay of AccessExclusiveLock.

Tomas Vondra sent in another revision of a patch to implement two slab-like memory allocators.

David Rowley sent in a patch to fix some warnings in the slab-like memory allocators patch.

Haribabu Kommi sent in another revision of a patch to refactor handling of database attributes between pg_dump and pg_dumpall.

Haribabu Kommi sent in another revision of a patch to implement a pg_stat_sql view.

Pavan Deolasee sent in another revision of a patch to skip all-visible pages during second HeapScan of CREATE INDEX CONCURRENTLY.

Michaël Paquier sent in another revision of a patch to implement SASLprep.

Ivan Kartyshov sent in two more revisions of a patch to make async slave to wait for lsn to be replayed.

Michaël Paquier sent in two revisions of a patch to add clause PASSWORD (val USING protocol) to CREATE/ALTER ROLE.

Rafia Sabih and Robert Haas traded patches to enable parallelism for queries coming from SQL or other PL functions.

Daniel Vérité and Vaishnavi Prabakaran traded patches to add batch/pipelining support for libpq.

Takayuki Tsunakawa sent in a patch to refactor strinfo in dblink.

Daniel Vérité and Vaishnavi Prabakaran traded patches to add batch/pipelining support for libpq.

Takayuki Tsunakawa sent in a patch to refactor strinfo in dblink.

Amit Khandekar sent in two more revisions of a patch to implement parallel append.

Kevin Grittner sent in two more revisions of a patch to add transition table support for, among other things, statement triggers and materialized view refreshes.

Kuntal Ghosh sent in another revision of a patch to add WAL consistency check support for hash indexes.

Andrew Gierth sent in two more revisions of a patch to add hash support for grouping sets.

Takayuki Tsunakawa sent in another revision of a patch to support huge pages on Windows.

Craig Ringer sent in a patch to fix an off-by-one error in logical slot resource retention.

Rushabh Lathia sent in another revision of a patch to add wait events for disk I/O.

Amit Langote sent in a patch to use ALTER FOREIGN TABLE with foreign table in tests.

Ãlvaro Herrera sent in another revision of a patch to add WARM.

Victor Wagner sent in three more revisions of a patch to add explicit subtransactions for PL/Tcl.

Peter Eisentraut sent in another revision of a patch to cast result of copyObject() to correct type.

Peter Eisentraut sent in another revision of a patch to implement ICU integration.

Amit Langote sent in another revision of a patch to fix an infelicity between declarative partitioning and ON CONFLICT.

Amit Langote sent in another revision of a patch to improve the documents on declarative partitioning.

Amit Langote sent in a patch to fix a bug in get_partition_for_tuple.

Peter Geoghegan sent in a patch to fix the amcheck build on Windows.

Petr Jelínek sent in a patch to fix remote position tracking in logical replication.

Kuntal Ghosh sent in another revision of a patch to expose wait events for non-backends.

Beena Emerson sent in another revision of a patch to allow increasing the default WAL segment size.

Vinayak Pokale sent in another revision of a patch to add an ANALYZE command progress checker.

Michaël Paquier sent in two more revisions of a patch to fix unlogged flushes.

Surafel Temesgen and Pavel Stěhule traded patches to implement CORRESPONDING.

Tom Lane sent in two revisions of a patch to upgrade the postmaster's log messages about bind/listen errors.

Michaël Paquier sent in a patch to change references of password encryption to hashing.

Nikita Glukhov sent in another revision of a patch to fix a bug in SP-GiST box_ops.

Ashutosh Bapat sent in another revision of a patch to implement partition-wise join for join between (declaratively) partitioned tables.

Dmitry Dolgov sent in another revision of a patch to add FTS support for JSON[B].

Thomas Munro sent in another revision of a patch to fix SERIALIZABLE isolation for parallel query.

Nikhil Sontakke sent in another revision of a patch to speed up two-phase transactions.

Pavel Stěhule sent in a patch to add VERBOSE_SORT_COLUMNS and VERBOSE_SORT_DIRECTION to psql.

Pavel Stěhule sent in a patch to add default namespaces for XPath expressions.

Tom Lane and Andres Freund traded patches to speed up running all the tests.

Tom Lane sent in another revision of a patch to add \if and friends to psql.

Nikolay Shaplov sent in another revision of a patch to move all am-related reloption code into src/backend/access/[am-name] and get rid of relopt_kind for custom AM.

Stephen Frost sent in two more revisions of a patch to add macaddr 64 bit (EUI-64) datatype support.

Peter Geoghegan sent in another revision of a patch to add parallel tuplesort (for parallel B-Tree index creation).