== PostgreSQL Weekly News - July 26 2015 ==
David Fetter <david <at> fetter.org>
2015-07-26 22:30:49 GMT
== 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.
== PostgreSQL Jobs for July ==
== PostgreSQL Local ==
PGDay Campinas 2015 will take place in Campinas on August 7.
PostgresOpen 2015 will being held in Dallas, Texas September 16-18.
PostgreSQL Session #7, will be held September 24th, 2015 in Paris,
PGDay.IT 2015 will take place in Prato on October 23, 2015. The
International Call For Papers is open until August 8.
PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until
PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center.
PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto
Alegre, Rio Grande do Sul, on November 18, 19 and 20.
== 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 david <at> fetter.org, German language
to pwn <at> pgug.de, Italian language to pwn <at> itpug.org. Spanish language
to pwn <at> 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.
- 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
- Add selectivity estimation functions for intarray operators. Uriy
Zhuravlev and Alexander Korotkov, reviewed by Jeff Janes, some
cleanup by me.
- 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.
Á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
- Fix mis-merge in previous commit
- 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.
- 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_Y90YgqFM11T2jvnxjLFmqw <at> mail.gmail.com
- Improve tab-completion for DROP POLICY. Backpatch to 9.5. Author:
- 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
- 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.
https://www.postgresql.org/message-id/CA+Tgmoa6SgDaxW_n_7SEhwBAc=mniYga+obUj5fmw4rU9_mLvA <at> mail.gmail.com
https://www.postgresql.org/message-id/29788.1437411581 <at> sss.pgh.pa.us
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
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
- 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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
- 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
- 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
- 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.)
- 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.
- 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.
Andres Freund pushed:
- Fix bug around assignment expressions containing indirections.
Handling of assigned-to expressions with indirection (e.g. set f1
= 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=KFxADZWw3wAHnnbxMxDzkEC6fNaFc7zSm411w <at> mail.gmail.com
Backpatch: 9.5, where the feature was introduced
- 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:
XTA435TCNvnDFSD--YvXs+pww <at> mail.gmail.com
Backpatch: 9.5, where grouping sets were introduced
- 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.fsf <at> 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.
- 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=1kw <at> mail.gmail.com
Backpatch: 9.5, where grouping sets were introduced
- 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=WG9omG5rFOMAYBweJxmpTaapvVp5pCeMrE6BfpCwr4Og <at> mail.gmail.com
Backpatch: 9.5, where grouping sets were introduced
- 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-+8jKGO4JPhesyeWg <at> mail.gmail.com
Backpatch: 9.5, where ON CONFLICT was introduced
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
- Improve markup for row_security. Wrap the literals on, off, force,
and BYPASSRLS with appropriate markup. Per Kevin Grittner.
== 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
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
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
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
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 <at> postgresql.org)
To make changes to your subscription: