== PostgreSQL Weekly News - July 26 2015 == == PostgreSQL Product News ==
pgpool-II versions 3.4.3, 3.3.7, 3.2.12, 3.1.15, and 3.0.19 released. pgpool-II is a connection pooler and replication system for PostgreSQL. http://pgpool.net/mediawiki/index.php/Downloads == PostgreSQL Jobs for July == http://archives.postgresql.org/pgsql-jobs/2015-07/ == PostgreSQL Local == PGDay Campinas 2015 will take place in Campinas on August 7. http://pgdaycampinas.com.br/english/ PostgresOpen 2015 will being held in Dallas, Texas September 16-18. http://2015.postgresopen.org/ PostgreSQL Session #7, will be held September 24th, 2015 in Paris, France, http://www.postgresql-sessions.org/7/about PGDay.IT 2015 will take place in Prato on October 23, 2015. The International Call For Papers is open until August 8. http://pgday.it PostgreSQL Conference Europe 2015 will be held on October 27-30 in the Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until August 7. http://2015.pgconf.eu/ PGConf Silicon Valley 2015 is November 17-18 at the South San Francisco Convention Center. http://www.pgconfsv.com PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto Alegre, Rio Grande do Sul, on November 18, 19 and 20. http://pgbr.postgresql.org.br/2015/en/#call-for-papers == 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. Spanish language to p...@arpug.com.ar. == Applied Patches == Heikki Linnakangas pushed: - Handle AT_ReAddComment in test_ddl_deparse, and add a catch-all default. In the passing, also move AT_ReAddComment to more logical position in the enum, after all the Constraint-related subcommands. This fixes a compiler warning, added by commit e42375fc. Backpatch to 9.5, like that patch. http://git.postgresql.org/pg/commitdiff/13f2db2ffb2fac24fcb57ecc56e030e1145df127 - Sanity-check that a page zeroed by redo routine is marked with WILL_INIT. There was already a sanity-check in the other direction: if a page was marked with WILL_INIT, it had to be initialized by the redo routine. It's not strictly necessary for correctness that a page is marked with WILL_INIT if it's going to be initialized at redo, but it's a missed optimization if nothing else. Fix a few instances of this issue in SP-GiST, where a block in WAL record was not marked with WILL_INIT, but was in fact always initialized at redo. We were creating a full-page image of the page unnecessarily in those cases. Backpatch to 9.5, where the new WILL_INIT flag was added. http://git.postgresql.org/pg/commitdiff/eb11de8ff5eac3592d539ad7ca3059c02e4d3e99 - Add selectivity estimation functions for intarray operators. Uriy Zhuravlev and Alexander Korotkov, reviewed by Jeff Janes, some cleanup by me. http://git.postgresql.org/pg/commitdiff/c6fbe6d6fb828f50b9d67627588eb5ab8bd25e47 - Fix off-by-one error in calculating subtrans/multixact truncation point. If there were no subtransactions (or multixacts) active, we would calculate the oldestxid == next xid. That's correct, but if next XID happens to be on the next pg_subtrans (pg_multixact) page, the page does not exist yet, and SimpleLruTruncate will produce an "apparent wraparound" warning. The warning is harmless in this case, but looks very alarming to users. Backpatch to all supported versions. Patch and analysis by Thomas Munro. http://git.postgresql.org/pg/commitdiff/766dcfb16ca385274d510eaed01724bb3836efdd Álvaro Herrera pushed: - Add some comments to test_ddl_deparse and a README. Per comments from Heikki Linnakangas. Backpatch to 9.5, where this module was introduced. http://git.postgresql.org/pg/commitdiff/8f612b7f0042a33f59cd4a18144b4d74e78b6859 - Fix mis-merge in previous commit http://git.postgresql.org/pg/commitdiff/b7ca57ac0e80b8b511780ef1f19fa2124c901efb - Improve BRIN documentation somewhat. This removes some info about support procedures being used, which was obsoleted by commit db5f98ab4f, as well as add some more documentation on how to create new opclasses using the Minmax infrastructure. (Hopefully we can get something similar for Inclusion as well.) In passing, fix some obsolete mentions of "mmtuples" in source code comments. Backpatch to 9.5, where BRIN was introduced. http://git.postgresql.org/pg/commitdiff/8d9073692430a222939e7f2e436c80f0c271f116 - Fix (some of) pltcl memory usage. As reported by Bill Parker, PL/Tcl did not validate some malloc() calls against NULL return. Fix by using palloc() in a new long-lived memory context instead. This allows us to simplify error handling too, by simply deleting the memory context instead of doing retail frees. There's still a lot that could be done to improve PL/Tcl's memory handling ... This is pretty ancient, so backpatch all the way back. Author: Michael Paquier and Álvaro Herrera Discussion: https://www.postgresql.org/message-id/cafrbyqwyldyxfbohpfobgqnvuzo_y90ygqfm11t2jvnxjlf...@mail.gmail.com http://git.postgresql.org/pg/commitdiff/f8d67ca8d4cbbbd6c6b3319d531089880d332534 - Improve tab-completion for DROP POLICY. Backpatch to 9.5. Author: Pavel Stěhule http://git.postgresql.org/pg/commitdiff/1a5118008003b3c42f5cbb37980dabdb6a718e6f - Don't handle PUBLIC/NONE separately. Since those role specifiers are checked in the grammar, there's no need for the old checks to remain in place after 31eae6028ec. Remove them. Backpatch to 9.5. Noted and patch by Jeevan Chalke http://git.postgresql.org/pg/commitdiff/e52b690cf55f303839f12f8f1f136d2366d36298 - Fix omission of OCLASS_TRANSFORM in object_classes[]. This was forgotten in cac76582053e (and its fixup ad89a5d115). Since it seems way too easy to miss this, this commit also introduces a mechanism to enforce that the array is consistent with the enum. Problem reported independently by Robert Haas and Jaimin Pan. Patches proposed by Jaimin Pan, Jim Nasby, Michael Paquier and myself, though I didn't use any of these and instead went with a cleaner approach suggested by Tom Lane. Backpatch to 9.5. Discussion: https://www.postgresql.org/message-id/CA+Tgmoa6SgDaxW_n_7SEhwBAc=mniyga+obuj5fmw4ru9_m...@mail.gmail.com https://www.postgresql.org/message-id/29788.1437411...@sss.pgh.pa.us http://git.postgresql.org/pg/commitdiff/149b1dd84069a9b57962fdb4621754aed5dfb63e Teodor Sigaev pushed: - This supports the triconsistent function for pg_trgm GIN opclass to make it faster to implement indexed queries where some keys are common and some are rare. Patch by Jeff Janes http://git.postgresql.org/pg/commitdiff/97f3014647a5bd570032abd2b809d3233003f13f Andrew Dunstan pushed: - Fix location of output logs of pg_regress. initdb.log and postmaster.log were moved to within the temporary instance path by commit dcae5fa. This directory now gets removed at the end of the run of pg_regress when there are no failures found, which makes analysis of after-run issues difficult in some cases, and reduces the output verbosity of the buildfarm after a run. Fix by Michael Paquier Backpatch to 9.5 http://git.postgresql.org/pg/commitdiff/9faa6ae14f6098e4b55f0131f7ec2694a381fb87 - Redirect install output of make check into a log file. dbf2ec1a changed make check so that the installation logs get directed to stdout and stderr. Per discussion on -hackers, this patch restores saving it to a file. It is now saved in /tmp_install/log, which is created once per invocation of any make target doing regression tests. Along the way, add a missing /log/ entry to test_ddl_deparse's .gitignore. Michael Paquier. http://git.postgresql.org/pg/commitdiff/16c33c50e122e3e7d03fc7ddd5cbd105c0118234 - Fix treatment of nulls in jsonb_agg and jsonb_object_agg. The wrong is_null flag was being passed to datum_to_json. Also, null object key values are not permitted, and this was not being checked for. Add regression tests covering these cases, and also add those tests to the json set, even though it was doing the right thing. Fixes bug #13514, initially diagnosed by Tom Lane. http://git.postgresql.org/pg/commitdiff/d9a356ff2e6bb7ed5fb1145af49fa3e51e68a98a - Restore use of zlib default compression in pg_dump directory mode. This was broken by commit 0e7e355f27302b62af3e1add93853ccd45678443 and friends, which ignored the fact that gzopen() will treat "-1" in the mode argument as an invalid character, which it ignores, and a flag for compression level 1. Now, when this value is encountered no compression level flag is passed to gzopen, leaving it to use the zlib default. Also, enforce the documented allowed range for pg_dump's -Z option, namely 0 .. 9, and remove some consequently dead code from pg_backup_tar.c. Problem reported by Marc Mamin. Backpatch to 9.1, like the patch that introduced the bug. http://git.postgresql.org/pg/commitdiff/caef94d59fcfa1087be36d4a8b5ed4523872bf55 Tom Lane pushed: - Fix some oversights in BRIN patch. Remove HeapScanDescData.rs_initblock, which wasn't being used for anything in the final version of the patch. Fix IndexBuildHeapScan so that it supports syncscan again; the patch broke synchronous scanning for index builds by forcing rs_startblk to zero even when the caller did not care about that and had asked for syncscan. Add some commentary and usage defenses to heap_setscanlimits(). Fix heapam so that asking for rs_numblocks == 0 does what you would reasonably expect. As coded it amounted to requesting a whole-table scan, because those "--x <= 0" tests on an unsigned variable would behave surprisingly. http://git.postgresql.org/pg/commitdiff/434873806a9b1c0edd53c2a9df7c93a8ba021147 - Fix add_rte_to_flat_rtable() for recent feature additions. The TABLESAMPLE and row security patches each overlooked this function, though their errors of omission were opposite: RLS failed to zero out the securityQuals field, leading to wasteful copying of useless expression trees in finished plans, while TABLESAMPLE neglected to add a comment saying that it intentionally *isn't* deleting the tablesample subtree. There probably should be a similar comment about ctename, too. Back-patch as appropriate. http://git.postgresql.org/pg/commitdiff/46d0a9bfac3d5221702318cc1cf119221d729c84 - Redesign tablesample method API, and do extensive code review. The original implementation of TABLESAMPLE modeled the tablesample method API on index access methods, which wasn't a good choice because, without specialized DDL commands, there's no way to build an extension that can implement a TSM. (Raw inserts into system catalogs are not an acceptable thing to do, because we can't undo them during DROP EXTENSION, nor will pg_upgrade behave sanely.) Instead adopt an API more like procedural language handlers or foreign data wrappers, wherein the only SQL-level support object needed is a single handler function identified by having a special return type. This lets us get rid of the supporting catalog altogether, so that no custom DDL support is needed for the feature. Adjust the API so that it can support non-constant tablesample arguments (the original coding assumed we could evaluate the argument expressions at ExecInitSampleScan time, which is undesirable even if it weren't outright unsafe), and discourage sampling methods from looking at invisible tuples. Make sure that the BERNOULLI and SYSTEM methods are genuinely repeatable within and across queries, as required by the SQL standard, and deal more honestly with methods that can't support that requirement. Make a full code-review pass over the tablesample additions, and fix assorted bugs, omissions, infelicities, and cosmetic issues (such as failure to put the added code stanzas in a consistent ordering). Improve EXPLAIN's output of tablesample plans, too. Back-patch to 9.5 so that we don't have to support the original API in production. http://git.postgresql.org/pg/commitdiff/dd7a8f66ed278eef2f001a98e2312336c61ee527 - Update oidjoins regression test for 9.5. New FK relationships for pg_transform. Also findoidjoins now detects a few relationships it didn't before for pre-existing catalogs, as a result of new regression tests leaving entries in those catalogs that weren't there before. http://git.postgresql.org/pg/commitdiff/158d61534e98638106d85bdb1de5dbdb56bc8057 - In pg_ctl, report unexpected failure to stat() the postmaster.pid file. Any error other than ENOENT is a bit suspicious here, and perhaps should not be grounds for assuming the postmaster has failed. For the moment though, just report it, and don't change the behavior otherwise. The intent is mainly to try to determine why we are seeing intermittent failures in this area on some buildfarm members. Back-patch to 9.5 where some of these failures have happened. http://git.postgresql.org/pg/commitdiff/b7b5a1899aa3caeef30117f8e36c1f0e68e8847a - Some platforms now need contrib/tsm_system_time to be linked with libm. Buildfarm member hornet, at least, seems to want -lm in the link command. Probably this is due to the just-added use of isnan(). http://git.postgresql.org/pg/commitdiff/c879d51c5918ab5fc8feb9624aa4eae10ee93094 - Dodge portability issue (apparent compiler bug) in new tablesample code. Some of the older OS X critters in the buildfarm are failing regression, with symptoms showing that a request for 100% sampling in BERNOULLI or SYSTEM methods actually gets only around 50% of the table. gdb revealed that the computation of the "cutoff" number was producing 0x7FFFFFFF rather than the expected 0x100000000. Inspecting the assembly code, it looks like gcc is trying to use lrint() instead of rint() and then fumbling the conversion from long double to uint64. This seems like a clear compiler bug, but assigning the intermediate result into a plain double variable works around it, so let's just do that. (Another idea would be to give up one bit of hash width so that we don't need to use a uint64 cutoff, but let's see if this is enough.) http://git.postgresql.org/pg/commitdiff/d9476b83808a39d9985845071bf0a150a3063b37 - Make entirely-dummy appendrels get marked as such in set_append_rel_size. The planner generally expects that the estimated rowcount of any relation is at least one row, *unless* it has been proven empty by constraint exclusion or similar mechanisms, which is marked by installing a dummy path as the rel's cheapest path (cf. IS_DUMMY_REL). When I split up allpaths.c's processing of base rels into separate set_base_rel_sizes and set_base_rel_pathlists steps, the intention was that dummy rels would get marked as such during the "set size" step; this is what justifies an Assert in indxpath.c's get_loop_count that other relations should either be dummy or have positive rowcount. Unfortunately I didn't get that quite right for append relations: if all the child rels have been proven empty then set_append_rel_size would come up with a rowcount of zero, which is correct, but it didn't then do set_dummy_rel_pathlist. (We would have ended up with the right state after set_append_rel_pathlist, but that's too late, if we generate indexpaths for some other rel first.) In addition to fixing the actual bug, I installed an Assert enforcing this convention in set_rel_size; that then allows simplification of a couple of now-redundant tests for zero rowcount in set_append_rel_size. Also, to cover the possibility that third-party FDWs have been careless about not returning a zero rowcount estimate, apply clamp_row_est to whatever an FDW comes up with as the rows estimate. Per report from Andreas Seltenreich. Back-patch to 9.2. Earlier branches did not have the separation between set_base_rel_sizes and set_base_rel_pathlists steps, so there was no intermediate state where an appendrel would have had inconsistent rowcount and pathlist. It's possible that adding the Assert to set_rel_size would be a good idea in older branches too; but since they're not under development any more, it's likely not worth the trouble. http://git.postgresql.org/pg/commitdiff/358eaa01bf95935f9af968faf5b08d9914f6a445 - Fix oversight in flattening of subqueries with empty FROM. I missed a restriction that commit f4abd0241de20d5d6a79b84992b9e88603d44134 should have enforced: we can't pull up an empty-FROM subquery if it's under an outer join, because then we'd need to wrap its output columns in PlaceHolderVars. As the code currently stands, the PHVs end up with empty relid sets, which doesn't work (and is correctly caught by an Assert). It's possible that this could be fixed by assigning the PHVs the relid sets of the parent FromExpr/JoinExpr, but getting that to work is more complication than I care to add right now; indeed it's likely that we'll never bother, since pulling up empty-FROM subqueries is a rather marginal optimization anyway. Per report from Andreas Seltenreich. Back-patch to 9.5 where the faulty code was added. http://git.postgresql.org/pg/commitdiff/fca8e59c1c582030dd7a3c870e1c3c70e8a193aa Andres Freund pushed: - Fix bug around assignment expressions containing indirections. Handling of assigned-to expressions with indirection (e.g. set f1[1] = 3) was broken for ON CONFLICT DO UPDATE. The problem was that ParseState was consulted to determine if an INSERT-appropriate or UPDATE-appropriate behavior should be used when transforming expressions with indirections. When the wrong path was taken the old row was substituted with NULL, leading to wrong results.. To fix remove p_is_update and only use p_is_insert to decide how to transform the assignment expression, and uset p_is_insert while parsing the on conflict statement. This isn't particularly pretty, but it's not any worse than before. Author: Peter Geoghegan, slightly edited by me Discussion: CAM3SWZS8RPvA=kfxadzww3wahnnbxmxdzkec6fnafc7zsm4...@mail.gmail.com Backpatch: 9.5, where the feature was introduced http://git.postgresql.org/pg/commitdiff/c1ca3a19df376bcbb6d651d15b9a4ffcaa377ff1 - Fix flattening of nested grouping sets. Previously nested grouping set specifications accidentally weren't flattened, but instead contained the nested specification as a element in the outer list. Fix this by, as actually documented in comments, concatenating the nested set specification into the outer one. Also add tests to prevent this from breaking again. Author: Andrew Gierth, with tests from Jeevan Chalke Reported-By: Jeevan Chalke Discussion: CAM2+6=V5YvuxB+EyN4iH=gbd-xta435tcnvndfsd--yvxs+...@mail.gmail.com Backpatch: 9.5, where grouping sets were introduced http://git.postgresql.org/pg/commitdiff/faab14ecb8c1b4ea2bee3723d4fa04f47275abd3 - Allow to push down clauses from HAVING to WHERE when grouping sets are used. Previously we disallowed pushing down quals to WHERE in the presence of grouping sets. That's overly restrictive. We now instead copy quals to WHERE if applicable, leaving the one in HAVING in place. That's because, at that stage of the planning process, it's nontrivial to determine if it's safe to remove the one in HAVING. Author: Andrew Gierth Discussion: 874mkt3l59....@news-spur.riddles.org.uk Backpatch: 9.5, where grouping sets were introduced. This isn't exactly a bugfix, but it seems better to keep the branches in sync at this point. http://git.postgresql.org/pg/commitdiff/61444bfb809d3a088a270a59f383af3d4cd157b0 - Build column mapping for grouping sets in all required cases. The previous coding frequently failed to fail because for one it's unusual to have rollup clauses with one column, and for another sometimes the wrong mapping didn't cause obvious problems. Author: Jeevan Chalke Reviewed-By: Andrew Gierth Discussion: CAM2+6=W=9=hQOipH0HAPbkun3Z3TFWij_EiHue0_6UX=oR=1...@mail.gmail.com Backpatch: 9.5, where grouping sets were introduced http://git.postgresql.org/pg/commitdiff/144666f65b500fef864bca318f6245b03c0f457c - Recognize GROUPING() as a aggregate expression. Previously GROUPING() was not recognized as a aggregate expression, erroneously allowing the planner to move it from HAVING to WHERE. Author: Jeevan Chalke Reviewed-By: Andrew Gierth Discussion: CAM2+6=wg9omg5rfomaybwejxmptaapvvp5pcemre6bfpcwr...@mail.gmail.com Backpatch: 9.5, where grouping sets were introduced http://git.postgresql.org/pg/commitdiff/e6d8cb77c029b8122607e3d2eb1f3fca36d7b1db - Check the relevant index element in ON CONFLICT unique index inference. ON CONFLICT unique index inference had a thinko that could affect cases where the user-supplied inference clause required that an attribute match a particular (user specified) collation and/or opclass. infer_collation_opclass_match() has to check for opclass and/or collation matches and that the attribute is in the list of attributes or expressions known to be in the definition of the index under consideration. The bug was that these two conditions weren't necessarily evaluated for the same index attribute. Author: Peter Geoghegan Discussion: CAM3SWZR4uug=wvmgk7ugsqhn2mkezy9yu-+8jkgo4jphesy...@mail.gmail.com Backpatch: 9.5, where ON CONFLICT was introduced http://git.postgresql.org/pg/commitdiff/159cff58cf3b565be3c17901698a74238e9e23f8 Joe Conway pushed: - Make RLS work with UPDATE ... WHERE CURRENT OF. UPDATE ... WHERE CURRENT OF would not work in conjunction with RLS. Arrange to allow the CURRENT OF expression to be pushed down. Issue noted by Peter Geoghegan. Patch by Dean Rasheed. Back patch to 9.5 where RLS was introduced. http://git.postgresql.org/pg/commitdiff/b26e3d660df51a088d14c3c2cfce5990c13c1195 - Improve markup for row_security. Wrap the literals on, off, force, and BYPASSRLS with appropriate markup. Per Kevin Grittner. http://git.postgresql.org/pg/commitdiff/cf80ddee57103bb3b454c1607d100a4551d7a48c == Rejected Patches (for now) == No one was disappointed this week :-) == Pending Patches == Peter Geoghegan sent in a patch to prefetch from memtuples array in tuplesort on compilers that support same. Adam Brightwell sent in a patch to remove an unneeded #include in objectaddress.h. Alexander Korotkov sent in two more revisions of a patch to allow pg_rewind to work when target timeline was switched. Heikki Linnakangas sent in another revision of a patch to ensure that an all-zero GIN page doesn't cause an assertion failure. Michael Paquier sent in two more revisions of a patch to fix the location of output logs of pg_regress. Sameer Thakur sent in two more revisions of a patch to implement a VACUUM progress checker. Petr Jelinek sent in three more revisions of a patch to allow CREATE EXTENSION to pull in any dependent extensions. Haribabu Kommi sent in a patch to add a new function called pg_hba_lookup() to get all matching entries from the pg_hba.conf for the providing input data. Peter Eisentraut sent in another revision of a patch to add --slot option to pg_basebackup. Michael Paquier sent in a patch to fix a dump-restore hazard created by ALTER TABLE .. ADD PRIMARY KEY .. USING INDEX. Jaimin Pan sent in a patch to make object_classes break loudly. Peter Geoghegan sent in a patch to add a new tie-breaker which is used only when the tuples being sorted still fit in memory. Paul Ramsey sent in three more revisions of a patch to allow specifying remote extensions in the PostgreSQL FDW. Fabien COELHO sent in three more revisions of a patch to add pgbench stats per script. Ildus Kurbangaliev sent in two more revisions of a patch to add a wait_event column to pg_stat_activity. Dinesh Kumar sent in another revision of a patch to add an SQL-callable pg_report_log() function. Fabrízio de Royes Mello and Michael Paquier traded patches to add CINE for ALTER TABLE ... ADD COLUMN. Simon Riggs and Heikki Linnakangas traded patches to fix a WAL logging problem. Etsuro Fujita sent in two revisions of a patch to support ForeignRecheck for late row locking. Michael Paquier sent in a patch to fix some memory leaks in pg_rewind caused by missing PQclear calls. Florent Guiliani added a replication command named LOGICAL_DECODING_SNAPSHOT that does everything CREATE_REPLICATION_SLOT does for logical slots except that the slot is automatically dropped. Fabrízio de Royes Mello sent in a patch to add more test cases to cover ALTER TABLE on views. Kyotaro HORIGUCHI sent in two more revisions of a patch to allow backslash-continuations in custom scripts for pgbench. Álvaro Herrera sent in another revision of a patch to add valgrind tests to bufmgr.c. Heikki Linnakangas and Michael Paquier traded patches to support TAP tests with MSVC and Windows. Pavel Stěhule sent in another revision of a patch to add a --strict-names option to pg_dump. Noah Misch sent in a patch to correctly raise an error in cases where IBM xlc is wrong. Pavel Stěhule sent in two more revisions of a patch to add a libpq context filter. Fabien COELHO sent in two more revisions of a patch to allow extending pgbench expressions with functions. Fabien COELHO sent in another revision of a patch to add a checkpoint_sort GUC. Heikki Linnakangas sent in another revision of a patch to allow sharing aggregate state. Peter Geoghegan sent in a patch to remove a reference to an executor README section about speculative insertion. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce
