== PostgreSQL Weekly News - January 15 2017 == == PostgreSQL Product News ==
postgresql-unit 2.0, an extension which implements a PostgreSQL datatype for SI units, some non-SI units and bytes, released. https://github.com/ChristophBerg/postgresql-unit == PostgreSQL Jobs for January == http://archives.postgresql.org/pgsql-jobs/2017-01/ == PostgreSQL Local == PGConf India 2017 will be on March 2-3, 2017 in Bengaluru, Karnataka. http://pgconf.in/ PostgreSQL@SCaLE will take place on March 2-3, 2017, at Pasadena Convention Center, as part of SCaLE 15X. http://www.socallinuxexpo.org/scale/15x/ PgConf.Russia 2017 will take place on 15-17 March 2017 in Moscow. https://pgconf.ru/en PGDay Asia 2017 will be held March 17-18 in Singapore. The CfP closes January 16, 2017. http://tinyurl.com/pgDay-Asia-2017-Cfp Nordic PGDay 2017 will be held in Stockholm, Sweden, at the Sheraton Hotel, on March 21, 2017. The CfP is open until January 9, 2017. https://2017.nordicpgday.org/cfp/ pgDay Paris 2017 will be held in Paris, France on March 23, 2017. The CfP is open until January 9, 2017. http://2017.pgday.paris/callforpapers/ PGConf US 2017 CfP will be on March 28-31 in Jersey City, New Jersey. http://www.pgconf.us/2017/ PGCon 2017 will take place in Ottawa on 23-26 May. Proposals are due by 19 Jan 2017. http://www.pgcon.org/2017/papers.php == 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 Pacific time. Please send English language ones to da...@fetter.org, German language to p...@pgug.de, Italian language to p...@itpug.org. == Applied Patches == Magnus Hagander pushed: - Use an enum instead of two bools to indicate wal inclusion in base backups. This makes the code easier to read as it becomes more explicit what the different allowed combinations really are. Suggested by Michael Paquier http://git.postgresql.org/pg/commitdiff/534b6f3ef29a11d19deeef6cef3bc8d4984de3c8 - Fix some typos in comments. Masahiko Sawada http://git.postgresql.org/pg/commitdiff/268f9e3d9292af1d6ca9aa66b42daac2ef1a0987 - Change default values for backup and replication parameters. This changes the default values of the following parameters: wal_level = replica, max_wal_senders = 10, max_replication_slots = 10, in order to make it possible to make a backup and set up simple replication on the default settings, without requiring a system restart. Discussion: https://postgr.es/m/cabuevey4pr_eavzezsbf5s+v0eevw7shj2t-auwbhojt+yr...@mail.gmail.com Reviewed by Peter Eisentraut. Benchmark help from Tomas Vondra. http://git.postgresql.org/pg/commitdiff/f6d6d2920d2cddc4b381cbbf2915db9d985d20a9 Tom Lane pushed: - Expand the regression tests for PL/Tcl. This raises the test coverage (by line count) in pltcl.c from about 70% to 86%. Karl Lehenbauer and Jim Nasby Discussion: https://postgr.es/m/92a1670d-21b6-8f03-9c13-e4fb2207a...@bluetreble.com http://git.postgresql.org/pg/commitdiff/961bed0208912a929a47c5a30190ff76748f3a03 - Fix error handling in pltcl_returnnext. We can't throw elog(ERROR) out of a Tcl command procedure; we have to catch the error and return TCL_ERROR to the Tcl interpreter. pltcl_returnnext failed to meet this requirement, so that errors detected by pltcl_build_tuple_result or other functions called here led to longjmp'ing out of the Tcl interpreter and thereby leaving it in a bad state. Use the existing subtransaction support to prevent that. Oversight in commit 26abb50c4, found more or less accidentally by the buildfarm thanks to the tests added in 961bed020. Report: https://postgr.es/m/30647.1483989...@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/8c5722948e831c1862a39da2bb5d793a6f2aabab - Fix field order in struct catcache. Somebody failed to grasp the point of having the #ifdef CATCACHE_STATS fields at the end of the struct. Put that back the way it should be, and add a comment making it more explicit why it should be that way. http://git.postgresql.org/pg/commitdiff/e2117e4ab0d7fcb76f20cbf2e52541998d639d9e - In PL/Tcl tests, don't choke if optional error fields are missing. This fixes a portability issue introduced by commit 961bed020: with a compiler that doesn't support PG_FUNCNAME_MACRO, the "funcname" field of errorCode won't be provided, leading to a failure of the unset command. I added -nocomplain to the unset commands for filename and lineno too, just in case, though I know of no platform that wouldn't populate those fields. (BTW, -nocomplain is new in Tcl 8.4, but fortunately we dropped support for pre-8.4 Tcl some time ago.) Per buildfarm member pademelon. http://git.postgresql.org/pg/commitdiff/5b29e6b688d1e783a098aa24f9e795f8de798a87 - Fix some more regression test row-order-instability issues. Commit 0563a3a8b just introduced another instance of the same unsafe testing methodology that appeared in 2ac3ef7a0, which I corrected in 257d81572. Robert/Amit, please stop doing that. Also look through the rest of f0e44751d's test cases, and correct some other queries with underdetermined ordering of results from the system catalogs. These haven't failed in the buildfarm yet, but I don't have any confidence in that staying true. Per multiple buildfarm members. http://git.postgresql.org/pg/commitdiff/5ad966ab1c50e829462f2b3e3ffa59e2d95479e6 - Throw suitable error for COPY TO STDOUT/FROM STDIN in a SQL function. A client copy can't work inside a function because the FE/BE wire protocol doesn't support nesting of a COPY operation within query results. (Maybe it could, but the protocol spec doesn't suggest that clients should support this, and libpq for one certainly doesn't.) In most PLs, this prohibition is enforced by spi.c, but SQL functions don't use SPI. A comparison of _SPI_execute_plan() and init_execution_state() shows that rejecting client COPY is the only discrepancy in what they allow, so there's no other similar bugs. This is an astonishingly ancient oversight, so back-patch to all supported branches. Report: https://postgr.es/m/by2pr05mb2309eaba3defa0143f50f0d593...@by2pr05mb2309.namprd05.prod.outlook.com http://git.postgresql.org/pg/commitdiff/75abb955dfef064f2fbc5c043f37fff8d0262ffe - Change representation of statement lists, and add statement location info. This patch makes several changes that improve the consistency of representation of lists of statements. It's always been the case that the output of parse analysis is a list of Query nodes, whatever the types of the individual statements in the list. This patch brings similar consistency to the outputs of raw parsing and planning steps: * The output of raw parsing is now always a list of RawStmt nodes; the statement-type-dependent nodes are one level down from that. * The output of pg_plan_queries() is now always a list of PlannedStmt nodes, even for utility statements. In the case of a utility statement, "planning" just consists of wrapping a CMD_UTILITY PlannedStmt around the utility node. This list representation is now used in Portal and CachedPlan plan lists, replacing the former convention of intermixing PlannedStmts with bare utility-statement nodes. Now, every list of statements has a consistent head-node type depending on how far along it is in processing. This allows changing many places that formerly used generic "Node *" pointers to use a more specific pointer type, thus reducing the number of IsA() tests and casts needed, as well as improving code clarity. Also, the post-parse-analysis representation of DECLARE CURSOR is changed so that it looks more like EXPLAIN, PREPARE, etc. That is, the contained SELECT remains a child of the DeclareCursorStmt rather than getting flipped around to be the other way. It's now true for both Query and PlannedStmt that utilityStmt is non-null if and only if commandType is CMD_UTILITY. That allows simplifying a lot of places that were testing both fields. (I think some of those were just defensive programming, but in many places, it was actually necessary to avoid confusing DECLARE CURSOR with SELECT.) Because PlannedStmt carries a canSetTag field, we're also able to get rid of some ad-hoc rules about how to reconstruct canSetTag for a bare utility statement; specifically, the assumption that a utility is canSetTag if and only if it's the only one in its list. While I see no near-term need for relaxing that restriction, it's nice to get rid of the ad-hocery. The API of ProcessUtility() is changed so that what it's passed is the wrapper PlannedStmt not just the bare utility statement. This will affect all users of ProcessUtility_hook, but the changes are pretty trivial; see the affected contrib modules for examples of the minimum change needed. (Most compilers should give pointer-type-mismatch warnings for uncorrected code.) There's also a change in the API of ExplainOneQuery_hook, to pass through cursorOptions instead of expecting hook functions to know what to pick. This is needed because of the DECLARE CURSOR changes, but really should have been done in 9.6; it's unlikely that any extant hook functions know about using CURSOR_OPT_PARALLEL_OK. Finally, teach gram.y to save statement boundary locations in RawStmt nodes, and pass those through to Query and PlannedStmt nodes. This allows more intelligent handling of cases where a source query string contains multiple statements. This patch doesn't actually do anything with the information, but a follow-on patch will. (Passing this information through cleanly is the true motivation for these changes; while I think this is all good cleanup, it's unlikely we'd have bothered without this end goal.) catversion bump because addition of location fields to struct Query affects stored rules. This patch is by me, but it owes a good deal to Fabien Coelho who did a lot of preliminary work on the problem, and also reviewed the patch. Discussion: https://postgr.es/m/alpine.DEB.2.20.1612200926310.29821@lancre http://git.postgresql.org/pg/commitdiff/ab1f0c8225714aaa18d2f9ca4f80cd009f145421 - Teach contrib/pg_stat_statements to handle multi-statement commands better. Make use of the statement boundary info added by commit ab1f0c822 to let pg_stat_statements behave more sanely when multiple SQL queries are jammed into one query string. It now records just the relevant part of the source string, not the whole thing, for each individual query. Even when no multi-statement strings are involved, users may notice small changes in the output: leading and trailing whitespace and semicolons will be stripped from statements, which did not happen before. Also, significantly expand pg_stat_statements' regression test script. Fabien Coelho, reviewed by Craig Ringer and Kyotaro Horiguchi, some mods by me Discussion: https://postgr.es/m/alpine.DEB.2.20.1612200926310.29821@lancre http://git.postgresql.org/pg/commitdiff/83f2061dd037477ec8479ee160367840e203a722 - Fix matching of boolean index columns to sort ordering. Normally, if we have a WHERE clause like "indexcol = constant", the planner will figure out that that index column can be ignored when determining whether the index has a desired sort ordering. But this failed to work for boolean index columns, because a condition like "boolcol = true" is canonicalized to just "boolcol" which does not give rise to an EquivalenceClass. Add a check to allow the same type of deduction to be made in this case too. Per a complaint from Dima Pavlov. Arguably this is a bug, but given the limited impact and the small number of complaints so far, I won't risk destabilizing plans in stable branches by back-patching. Patch by me, reviewed by Michael Paquier Discussion: https://postgr.es/m/1788.1481605...@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/0777f7a2e8e0a51f0f60cfe164d538bb459bf9f2 Álvaro Herrera pushed: - BRIN revmap pages are not standard pages, and therefore we ought not to tell XLogRegisterBuffer the opposite, when writing XLog for a brin update that moves the index tuple to a different page. Otherwise, xlog insertion would try to "compress the hole" when producing a full-page image for it; but since we don't update pd_lower/upper, the hole covers the whole page. On WAL replay, the revmap page becomes empty and so the entire portion of the index is useless and needs to be recomputed. This is low-probability: a BRIN update only moves an index tuple to a different page when the summary tuple is larger than the existing one, which doesn't happen with fixed-width datatypes. Also, the revmap page must be first after a checkpoint. Report and patch: Kuntal Ghosh Bug is alleged to have detected by a WAL-consistency-checking tool. Discussion: https://postgr.es/m/CAGz5QCJ=00UQjScSEFbV=0qo5shtzb9wwz_fm7+wd83zps9...@mail.gmail.com I posted a test case demonstrating the problem, but I'm refraining from adding it to the test suite; if the WAL consistency tool makes it in, that will be a better way to catch this from regressing. (We should definitely have someting that causes not-same-page updates, though.) http://git.postgresql.org/pg/commitdiff/7403561c0f6a8c62b79b6ddf0364ae6c01719068 - Fix ALTER TABLE / SET TYPE for irregular inheritance. If inherited tables don't have exactly the same schema, the USING clause in an ALTER TABLE / SET DATA TYPE misbehaves when applied to the children tables since commit 9550e8348b79. Starting with that commit, the attribute numbers in the USING expression are fixed during parse analysis. This can lead to bogus errors being reported during execution, such as: ERROR: attribute 2 has wrong type DETAIL: Table has type smallint, but query expects integer. Since it wouldn't do to revert to the original coding, we now apply a transformation to map the attribute numbers to the correct ones for each child. Reported by Justin Pryzby Analysis by Tom Lane; patch by me. Discussion: https://postgr.es/m/20170102225618.ga10...@telsasoft.com http://git.postgresql.org/pg/commitdiff/3957b58b8885441c8d03bc1cfc00e47cf8cd7975 - Fix overflow check in StringInfo; add missing casts. A few thinkos I introduced in fa2fa9955280. Also, amend a similarly broken comment. Report by Daniel Vérité. Authors: Daniel Vérité, Álvaro Herrera Discussion: https://postgr.es/m/1706e85e-60d2-494e-8a64-9af1e1b21...@manitou-mail.org http://git.postgresql.org/pg/commitdiff/42f50cb8fa9848bbbc6776bcea03293a6b28b2d4 Stephen Frost pushed: - Fix invalid-parallel-jobs error message. Including the program name twice is not helpful: -> pg_dump -j -1 pg_dump: pg_dump: invalid number of parallel jobs Correct by removing the progname from the exit_horribly() call used when validating the number of parallel jobs. Noticed while testing various pg_dump error cases. Back-patch to 9.3 where parallel pg_dump was added. http://git.postgresql.org/pg/commitdiff/2ef6fe9cbae9fe7789a35cbc5fa1bbf78c163d42 - pg_dump: Strict names with no matching schema. When using pg_dump --strict-names and a schema pattern which doesn't match any schemas (eg: --schema='nonexistant*'), we were incorrectly throwing an error claiming no tables were found when, really, there were no schemas found: -> pg_dump --strict-names --schema='nonexistant*' pg_dump: no matching tables were found for pattern "nonexistant*" Fix that by changing the error message to say 'schemas' instead, since that is what we are actually complaining about. Noticed while testing pg_dump error cases. Back-patch to 9.6 where --strict-names and this error message were introduced. http://git.postgresql.org/pg/commitdiff/abfd0095c1e1a2e3fad2696516b64871895334ec - pg_restore: Don't allow non-positive number of jobs. pg_restore will currently accept invalid values for the number of parallel jobs to run (eg: -1), unlike pg_dump which does check that the value provided is reasonable. Worse, '-1' is actually a valid, independent, parameter (as an alias for --single-transaction), leading to potentially completely unexpected results from a command line such as: -> pg_restore -j -1 Where a user would get neither parallel jobs nor a single-transaction. Add in validity checking of the parallel jobs option, as we already have in pg_dump, before we try to open up the archive. Also move the check that we haven't been asked to run more parallel jobs than possible on Windows to the same place, so we do all the option validity checking before opening the archive. Back-patch all the way, though for 9.2 we're adding the Windows-specific check against MAXIMUM_WAIT_OBJECTS as that check wasn't back-patched originally. Discussion: https://www.postgresql.org/message-id/20170110044815.GC18360%40tamriel.snowman.net http://git.postgresql.org/pg/commitdiff/e72059f3757594c5530ce321acdbe67f0da5da13 Robert Haas pushed: - Improve coding in _hash_addovflpage. Instead of relying on the page contents to know whether we have advanced from the primary bucket page to an overflow page, track that explicitly. Amit Kapila, per a complaint by me. http://git.postgresql.org/pg/commitdiff/e898437460f55b49623d1aea435cd92e0011d54d - Fix incorrect function name in comment. Amit Langote http://git.postgresql.org/pg/commitdiff/76568d37865c5c21ae154008b2c681e3e32ac880 - Fix cardinality estimates for parallel joins. For a partial path, the cardinality estimate needs to reflect the number of rows we think each worker will see, rather than the total number of rows; otherwise, costing will go wrong. The previous coding got this completely wrong for parallel joins. Unfortunately, this change may destabilize plans for users of 9.6 who have enabled parallel query, but since 9.6 is still fairly new I'm hoping expectations won't be too settled yet. Also, this is really a brown-paper-bag bug, so leaving it unfixed for the entire lifetime of 9.6 seems unwise. Related reports (whose import I initially failed to recognize) by Tomas Vondra and Tom Lane. Discussion: http://postgr.es/m/CA+TgmoaDxZ5z5Kw_oCQoymNxNoVaTCXzPaODcOuao=czk8d...@mail.gmail.com http://git.postgresql.org/pg/commitdiff/0c2070cefa0e5d097b715c9a3b9b5499470019aa - Fix a bug in how we generate partition constraints. Move the code for doing parent attnos to child attnos mapping for Vars in partition constraint expressions to a separate function map_partition_varattnos() and call it from the appropriate places. Doing it in get_qual_from_partbound(), as is now, would produce wrong result in certain multi-level partitioning cases, because it only considers the current pair of parent-child relations. In certain multi-level partitioning cases, attnums for the same key attribute(s) might differ between various levels causing the same attribute to be numbered differently in different instances of the Var corresponding to a given attribute. With this commit, in generate_partition_qual(), we first generate the the whole partition constraint (considering all levels of partitioning) and then do the mapping, so that Vars in the final expression are numbered according the leaf relation (to which it is supposed to apply). Amit Langote, reviewed by me. http://git.postgresql.org/pg/commitdiff/0563a3a8b59150bf3cc8b2b7077f684e0eaf8aff Bruce Momjian pushed: - pg_xlogdump: document --path behavior. The previous --path documentation and --help output were wrong in both its meaning and the defaults. Reviewed-by: Michael Paquier Backpatch-through: 9.6 http://git.postgresql.org/pg/commitdiff/73f8d7331343cefaaa97a0039421158fc8462c81 Peter Eisentraut pushed: - Make whitespace consistent inside some script files. I don't know what the global standard might be, but at least adjacent code should use the same whitespace. http://git.postgresql.org/pg/commitdiff/da0dbea9c30e7c91e9e78d72ab3dbee77d7ca0cd - Fix mistake in comment. The node->restart() function doesn't take a mode argument. http://git.postgresql.org/pg/commitdiff/750c59d7ec573f9443af2eb7d9186946bc7e008c - pg_upgrade: Fix for changed pg_ctl default stop mode. In 9.5, the default pg_ctl stop mode was changed from "smart" to "fast". pg_upgrade still thought the default mode was "smart" and only specified the mode when "fast" was asked for. This results in using "fast" all the time. It's not clear what the effect in practice is, but fix it nonetheless to restore the previous behavior. http://git.postgresql.org/pg/commitdiff/7f5b043d69a83e44e22a6b42f902e3e268b80a24 - Updates to reflect that pg_ctl stop -m fast is the default. Various example and test code used -m fast explicitly, but since it's the default, this can be omitted now or should be replaced by a better example. pg_upgrade is not touched, so it can continue to operate with older installations. http://git.postgresql.org/pg/commitdiff/e574f15d6295b12c03ef8810c00976b65933711a - pg_ctl: Change default to wait for all actions. The different actions in pg_ctl had different defaults for -w and -W, mostly for historical reasons. Most users will want the -w behavior, so make that the default. Remove the -w option in most example and test code, so avoid confusion and reduce verbosity. pg_upgrade is not touched, so it can continue to work with older installations. Reviewed-by: Beena Emerson <memissemer...@gmail.com> Reviewed-by: Ryan Murphy <ryanfmur...@gmail.com> http://git.postgresql.org/pg/commitdiff/05cd12ed5bc74c853a161c5a138a0cb6f9b0cb8a == Pending Patches == Amul Sul sent in two more revisions of a patch to implement pg_background. Dilip Kumar sent in four more revisions of a patch to implement parallel bitmap heap scan. Euler Taveira de Oliveira sent in another revision of a patch to move collation import to the backend. Anastasia Lubennikova and Erik Rijkers traded patches to implement covering + unique indexes. Takayuki Tsunakawa sent in another revision of a patch to support huge pages on Windows. Jon Nelson sent in another revision of a patch to guc-ify the formerly hard-coded MAX_SEND_SIZE to max_wal_send. Álvaro Herrera sent in another revision of a patch to help fix pg_dump and COPY bugs around big lines. David Fetter sent in three more revisions of a patch to add an extension that disallows simple UPDATEs and DELETEs that lack any WHERE clause. Álvaro Herrera sent in a patch to remove floats from the bootstrap scanner and parser. Antonin Houska sent in two revisions of a patch to implement grouped base relation, which is infrastructure for distributed aggregation. Vladimir Rusinov sent in four more revisions of a patch to rename things *xlog* to things *wal*. Keith Fiske and Amit Langote traded patches to fix up declarative partitioning. Haribabu Kommi sent in another revision of a patch to add macaddr 64 bit (EUI-64) datatype support. Michaël Paquier sent in two more revisions of a patch to add compression-level adjustment to pg_receivelog. Kyotaro HORIGUCHI sent in another revision of a patch to implement radix trees for character conversion. Ashutosh Sharma sent in another revision of a patch to add microvacuum support for hash indexes. Pavel Stěhule sent in another revision of a patch to implement \gstore and friends in psql. Matheus de Oliveira sent in another revision of a patch to add ALTER DEFAULT PRIVILEGES with GRANT/REVOKE ON SCHEMAS. Rafia Sabih sent in two revisions of a patch to enable passing query string to workers. Jesper Pedersen and Ashutosh Sharma traded patches to add hash index support to the pageinspect contrib extension. Nikita Glukhovs sent in another revision of a patch to add a recursive version of json_populate_record(). David Rowley sent in a patch to make connection_limit ignore bgworkers. Peter Eisentraut sent in a patch to factor out the many copies of atooid. Robert Haas sent in another revision of a patch to implement gather merge. Etsuro Fujita sent in a patch to rearrange some function declarations for correctness. Pavel Stěhule sent in a patch to add some checks to PL/pgsql. Fabien COELHO and Rafia Sabih traded patches to give pgbench a way to use backslash as a continuation character. Álvaro Herrera and Pavel Stěhule traded patches to implement xmltable(). Amit Kapila sent in another revision of a patch to add WAL logging for hash indexes. Mithun Cy sent in another revision of a patch to cache hash index meta pages. Etsuro Fujita sent in another revision of a patch to push more FULL JOINs down to FDWs. Ashutosh Bapat sent in a patch to remove an unused member root in foreign_glob_cxt. Dmitry Dolgov sent in another revision of a patch to implement generic type subscripts. Gilles Darold and Karl O. Pinc traded patches to implement pg_current_logfile(). Rafia Sabih sent in another revision of a patch to add parallel index-only scan. Mithun Cy sent in a patch to fix a typo in a comment in hashsearch.c. Ants Aasma sent in another revision of a patch to send hot standby feedback on first connect. Anastasia Lubennikova sent in a patch to implement IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING statements. Amit Khandekar sent in a patch to prevent useless VACUUMs. Peter Moser sent in another revision of a patch to implement temporal query processing with range types. Etsuro Fujita sent in another revision of a patch to fix a bug in the Postgres FDW. Amit Kapila sent in another revision of a patch to parallelize queries containing subplans. Petr Jelínek sent in two more revisions of a patch to implement logical replication. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce
