== PostgreSQL Weekly News - February 19 2017 ==
== PostgreSQL Product News ==
Ora2Pg 18.1, a tool for migrating Oracle databases to PostgreSQL, released.
psycopg2 2.7 beta2, a Python connector for PostgreSQL, released.
== PostgreSQL Jobs for February ==
== PostgreSQL Local ==
The first pgDay Israel 2017 will take place on March 2, 2017. Registration is
PGConf India 2017 will be on March 2-3, 2017 in Bengaluru, Karnataka.
PostgreSQL(at)SCaLE will take place on March 2-3, 2017, at Pasadena Convention
Center, as part of SCaLE 15X.
PgConf.Russia 2017 will take place on 15-17 March 2017 in Moscow.
PGDay Asia 2017 will be held March 17-18 in Singapore.
Nordic PGDay 2017 will be held in Stockholm, Sweden, at the Sheraton
Hotel, on March 21, 2017.
pgDay Paris 2017 will be held in Paris, France on March 23, 2017.
PGConf US 2017 CfP will be on March 28-31 in Jersey City, New Jersey.
PGCon 2017 will take place in Ottawa on 23-26 May.
Postgres Vision will take place in Boston, June 26 - 28, 2017.
the CfP is open until February 24, 2017 at CFP AT PostgresVision DOT com.
Swiss PGDay in Rapperswil will take place June 30, 2017.
The CfP is open through April 14, 2017.
== 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(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot)
== Applied Patches ==
Peter Eisentraut pushed:
- doc: Remove accidental extra table cell.
- Add CREATE COLLATION IF NOT EXISTS clause. The core of the functionality was
already implemented when pg_import_system_collations was added. This just
exposes it as an option in the SQL command.
- doc: Add advice about systemd RemoveIPC. Reviewed-by: Magnus Hagander
- Fix typo on comment.
- Remove redundant coverage target. This was probably forgotten to be removed
when general recursion support was added.
- pg_dump: Fix typo in query. This could lead to incorrect dumping of language
privileges in some cases, which is probably a rare situation.
- pg_dump: Message style improvements.
- doc: Fix typos. From: Thom Brown <thom(at)linux(dot)com>
- Optimize query for information_schema.constraint_column_usage. The way the
old query was written prevented some join optimizations because the join
conditions were hidden inside a CASE expression. With a large number of
constraints, the query became unreasonably slow. The new query performs much
better. From: Alexey Bashtanov <bashtanov(at)imap(dot)cc> Reviewed-by: Ashutosh
Robert Haas pushed:
- Remove contrib/tsearch2. This module was intended to ease migrations of
applications that used the pre-8.3 version of text search to the in-core
version introduced in that release. However, since all pre-8.3 releases of
the database have been out of support for more than 5 years at this point, we
expect that few people are depending on it at this point. If some people
still need it, nothing prevents it from being maintained as a separate
extension, outside of core. Discussion:
- Corrections and improvements to generic parallel query documentation. David
Rowley, reviewed by Brad DeJong, Amit Kapila, and me. Discussion:
- Don't disallow dropping NOT NULL for a list partition key. Range partitioning
doesn't support nulls in the partitioning columns, but list partitioning does.
Amit Langote, per a complaint from Amul Sul
- Minor fixes for WAL consistency checking. Michael Paquier, reviewed and
slightly revised by me. Discussion:
- Split index xlog headers from other private index headers. The xlog-specific
headers need to be included in both frontend code - specifically, pg_waldump -
and the backend, but the remainder of the private headers for each index are
only needed by the backend. By splitting the xlog stuff out into separate
headers, pg_waldump pulls in fewer backend headers, which is a good thing.
Patch by me, reviewed by Michael Paquier and Andres Freund, per a complaint
from Dilip Kumar. Discussion:
- Allow parallel workers to execute subplans. This doesn't do anything to make
Param nodes anything other than parallel-restricted, so this only helps with
uncorrelated subplans, and it's not necessarily very cheap because each worker
will run the subplan separately (just as a Hash Join will build a separate
copy of the hash table in each participating process), but it's a first step
toward supporting cases that are more likely to help in practice, and is
occasionally useful on its own. Amit Kapila, reviewed and tested by Rafia
Sabih, Dilip Kumar, and me. Discussion:
- Fix typo in comment. Higuchi Daisuke
- btree: Support parallel index scans. This isn't exposed to the optimizer or
the executor yet; we'll add support for those things in a separate patch. But
this puts the basic mechanism in place: several processes can attach to a
parallel btree index scan, and each one will get a subset of the tuples that
would have been produced by a non-parallel scan. Each index page becomes the
responsibility of a single worker, which then returns all of the TIDs on that
page. Rahila Syed, Amit Kapila, Robert Haas, reviewed and tested by Anastasia
Lubennikova, Tushar Ahuja, and Haribabu Kommi.
- Fix some nonstandard capitalization. Ashutosh Bapat
- pg_upgrade: Fix problems caused by renaming pg_resetxlog. Commit
85c11324cabaddcfaf3347df78555b30d27c5b5a renamed pg_resetxlog to pg_resetwal,
but didn't make pg_upgrade smart enough to cope with the situation. Michael
Paquier, per a complaint from Jeff Janes
- libpq: Make target_session_attrs=read-write consume empty result. Otherwise,
the leftover empty result can cause problems in some situations. Michael
Paquier and Ashutosh Bapat, per a report from Higuchi Daisuke
- Document new libpq connection statuses for target_session_attrs. I didn't
realize these would ever be visible to clients, but Michael figured out that
it can happen when using asynchronous interfaces such as PQconnectPoll.
- Fix wrong articles in pg_proc descriptions. This technically should involve a
catversion bump, but that seems pedantic, so I skipped it. Report and patch
by David Christensen.
- Replace min_parallel_relation_size with two new GUCs. When
min_parallel_relation_size was added, the only supported type of parallel scan
was a parallel sequential scan, but there are pending patches for parallel
index scan, parallel index-only scan, and parallel bitmap heap scan. Those
patches introduce two new types of complications: first, what's relevant is
not really the total size of the relation but the portion of it that we will
scan; and second, index pages and heap pages shouldn't necessarily be treated
in exactly the same way. Typically, the number of index pages will be quite
small, but that doesn't necessarily mean that a parallel index scan can't pay
off. Therefore, we introduce min_parallel_table_scan_size, which works out a
degree of parallelism for scans based on the number of table pages that will
be scanned (and which is therefore equivalent to min_parallel_relation_size
for parallel sequential scans) and also min_parallel_index_scan_size which can
be used to work out a degree of parallelism based on the number of index pages
that will be scanned. Amit Kapila and Robert Haas Discussion:
- Add optimizer and executor support for parallel index scans. In combination
with 569174f1be92be93f5366212cc46960d28a5c5cd, which taught the btree AM how
to perform parallel index scans, this allows parallel index scan plans on
btree indexes. This infrastructure should be general enough to support
parallel index scans for other index AMs as well, if someone updates them to
support parallel scans. Amit Kapila, reviewed and tested by Anastasia
Lubennikova, Tushar Ahuja, and Haribabu Kommi, and me.
- Avoid crash in ALTER TABLE not_partitioned DETACH PARTITION. Amit Langote,
reviewed and slightly changed by me.
- Add new function dsa_allocate0. This does the same thing as dsa_allocate,
except that the memory is guaranteed to be zero-filled on return. Dilip
Kumar, adjusted by me.
- Make dsa_allocate interface more like MemoryContextAlloc. A new function
dsa_allocate_extended now takes flags which indicate that huge allocations
should be permitted, that out-of-memory conditions should not throw an error,
and/or that the returned memory should be zero-filled, just like
9acb85597f1223ac26a5b19a9345849c43d0ff54, which added dsa_allocate0, was
broken because it failed to account for the possibility that dsa_allocate()
might return InvalidDsaPointer. This fixes that problem along the way.
Thomas Munro, with some comment changes by me. Discussion:
- Add optimizer and executor support for parallel index-only scans. Commit
5262f7a4fc44f651241d2ff1fa688dd664a34874 added similar support for parallel
index scans; this extends that work to index-only scans. As with parallel
index scans, this requires support from the index AM, so currently parallel
index-only scans will only be possible for btree indexes. Rafia Sabih,
reviewed and tested by Rahila Syed, Tushar Ahuja, and Amit Kapila Discussion:
- Make partitions automatically inherit OIDs. Previously, if the parent was
specified as WITH OIDS, each child also had to be explicitly specified as WITH
OIDS. Amit Langote, per a report from Simon Riggs. Some additional work on
the documentation changes by me. Discussion:
Fujii Masao pushed:
- Replace references to "xlog" with "wal" in docs. Commit
f82ec32ac30ae7e3ec7c84067192535b2ff8ec0e renamed the pg_xlog directory to
pg_wal. To make things consistent, we decided to eliminate "xlog" from
- Replace reference to "xlog-method" with "wal-method" in error message. Commit
62e8b38 renamed "--xlog-method" option for pg_basebackup to "--wal-method",
but forgot to update the error message mentioning that option.
Tom Lane pushed:
- Remove duplicate code in planner.c. I noticed while hacking on join UNION
transforms that planner.c's function get_base_rel_indexes() just duplicates
the functionality of get_relids_in_jointree(). It doesn't even have the
excuse of being older code :-(. Drop it and use the latter function instead.
- Fix YA unwanted behavioral difference with operator_precedence_warning. Jeff
Janes noted that the error cursor position shown for some errors would vary
when operator_precedence_warning is turned on. We'd prefer that option to
have no undocumented effects, so this isn't desirable. To fix, make sure that
an AEXPR_PAREN node has the same exprLocation as its child node. (Note: it
would be a little cheaper to use @2 here instead of an exprLocation call, but
there are cases where that wouldn't produce the identical answer, so don't do
it like that.) Back-patch to 9.5 where this feature was introduced.
- Fix tab completion for "ALTER SYSTEM SET variable ...". It wouldn't complete
"TO" after the variable name, which is certainly minor enough. But since we
do complete "TO" after "SET variable ...", and since this case used to work
pre-9.6, I think this is a bug. Also, fix the query used to collect the
variable names; whoever last touched it evidently didn't understand how the
pieces are supposed to fit together. It accidentally worked anyway, because
readline ignores irrelevant completions, but it was randomly unlike the ones
around it, and could be a source of actual bugs if someone copied it as a
prototype for another query.
- Doc: fix syntax synopsis for INSERT ... ON CONFLICT DO UPDATE. Commit
906bfcad7 adjusted the syntax synopsis for UPDATE, but missed the fact that
the INSERT synopsis now contains a duplicate of that. In passing, improve
wording and markup about using a table alias to dodge the conflict with use of
"excluded" as a special table name.
- Make sure that hash join's bulk-tuple-transfer loops are interruptible. The
loops in ExecHashJoinNewBatch(), ExecHashIncreaseNumBatches(), and
ExecHashRemoveNextSkewBucket() are all capable of iterating over many tuples
without ever doing a CHECK_FOR_INTERRUPTS, so that the backend might fail to
respond to SIGINT or SIGTERM for an unreasonably long time. Fix that. In the
case of ExecHashJoinNewBatch(), it seems useful to put the added
CHECK_FOR_INTERRUPTS into ExecHashJoinGetSavedTuple() rather than directly in
the loop, because that will also ensure that both principal code paths through
ExecHashJoinOuterGetTuple() will do a CHECK_FOR_INTERRUPTS, which seems like a
good idea to avoid surprises. Back-patch to all supported branches. Tom Lane
and Thomas Munro Discussion: https://postgr.es/m/6044(dot)1487121720(at)sss(dot)pgh(dot)pa(dot)us
- Doc: fix typo in logicaldecoding.sgml. There's no such field as
OutputPluginOptions.output_mode; it's actually output_type. Noted by T.
- Formatting and docs corrections for logical decoding output plugins. Make the
typedefs for output plugins consistent with project style; they were
previously not even consistent with each other as to layout or inclusion of
parameter names. Make the documentation look the same, and fix errors therein
(missing and misdescribed parameters). Back-patch because of the
- Doc: remove duplicate index entry. This causes a warning with the old
html-docs toolchain, though not with the new. I had originally supposed that
we needed both <indexterm> entries to get both a primary index entry and a
see-also link; but evidently not, as pointed out by Fabien Coelho.
- Document usage of COPT environment variable for adjusting configure flags.
Also add to the existing rather half-baked description of PROFILE, which does
exactly the same thing, but I think people use it differently. Discussion:
- Suppress "unused variable" warnings with older versions of flex. Versions of
flex before 2.5.36 might generate code that results in an "unused variable"
warning, when using %option reentrant. Historically we've worked around that
by specifying -Wno-error, but that's an unsatisfying solution. The official
"fix" for this was just to insert a dummy reference to the variable, so write
a small perl script that edits the generated C code similarly. The MSVC side
of this is untested, but the buildfarm should soon reveal if I broke that.
- Adjust PL/Tcl regression test to dodge a possible bug or zone dependency. One
case in the PL/Tcl tests is observed to fail on RHEL5 with a Turkish time zone
setting. It's not clear if this is an old Tcl bug or something odd about the
zone data, but in any case that test is meant to see if the Tcl [clock]
command works at all, not what its corner-case behaviors are. Therefore we
have no need to test exactly which week a Sunday midnight is considered to
fall into. Probe the following Tuesday instead. Discussion:
- Dept of second thoughts: rename new perl script. It didn't take long at all
for me to become irritated that the original choice of name for this script
resulted in "warning" showing up in several places in build logs, because I
tend to grep for that. Change the script name to avoid that.
- Make src/interfaces/libpq/test clean up after itself. It failed to remove a
.o file during "make clean", and it lacked a .gitignore file entirely.
Magnus Hagander pushed:
- Fix help message for pg_basebackup -R. The recovery.conf file that's
generated is specifically for replication, and not needed (or wanted) for
regular backup restore, so indicate that in the message.
== Pending Patches ==
Michaël Paquier sent in another revision of a patch to fix an issue where 2PC
files could be lost that stemmed from the fact that unlink() is not guaranteed
to be durable.
Corey Huinker sent in two more revisions of a patch to add \if and friends to
Ashutosh Sharma sent in a patch to scan has indexes a page at a time, where the
previous scans did tuple-at-a-time.
Amit Langote sent in a patch to add relkind checks to certain contrib modules.
Seki Eiji sent in two revisions of a patch to add a GetOldestXmin option which
ignores arbitrary vacuum flags.
Thomas Munro sent in another revision of a patch to implement [[Parallel]
Amit Khandekar sent in a patch to enable UPDATEs on partitioned tables that
would cause a tuple to move from one partition to another.
Vaishnavi Prabakaran and Aya Iwata traded patches to add batch/pipelining
support for libpq.
Amit Kapila sent in three more revisions of a patch to add WAL support for hash
Alexander Korotkov sent in another revision of a patch to cacheline-align
Kyotaro HORIGUCHI and Peter Eisentraut traded patches to make it possible for
logical encoding to do the right thing when the encodings of the origin and
replica don't match.
Pavel Stěhule sent in another revision of a patch to make it possible to set a
template database for pg_regress.
Dilip Kumar sent in three more revisions of a patch to implement parallel bitmap
Michaël Paquier sent in a patch to fix some issues with the WAL consistency
Amit Langote and Ashutosh Bapat traded patches to improve the documentation for
Erik Rijkers sent in a patch to fix the docs for CREATE SUBSCRIPTION.
Kyotaro HORIGUCHI sent in another revision of a patch to refactor tab completion
Dilip Kumar sent in another revision of a patch to implement parallel merge
Tom Lane sent in another revision of a patch to improve OR conditions on joined
Haribabu Kommi sent in another revision of a patch to add a pg_stat_wal_write
Masahiko Sawada sent in another revision of a patch to implement transactions
involving multiple postgres foreign servers.
Jeff Janes and Neha Khatri traded patches to fix an infelicity between
bytea_output and make installcheck.
Michaël Paquier sent in another revision of a patch to fix an issue in
PQsendQuery where an error occurs when target_session_attrs is set to
Masahiko Sawada sent in two more revisions of a patch to fix an infelicity
between DROP SUBSCRIPTION and ROLLBACK.
Robert Haas and Amit Kapila traded patches to implement parallel index scans.
Thomas Munro sent in two more revisions of a patch to help measure replay lag.
Kuntal Ghosh sent in a patch to add infrastructure required to expose
non-backend processes in pg_stat_activity, use same to expose stats for
auxiliary processes in pg_stat_get_activity, expose stats for autovacuum
launcher and bgworker, and add a proc_type column in pg_stat_get_activity.
Peter Eisentraut sent in a patch to add a max_worker_processes_per_user setting.
Peter Eisentraut sent in a patch to add errcontext to background worker
registration, and hange failures in RegisterBackgroundWorker() to hard errors.
David Christensen sent in a patch to fix some DESCR() grammar mistakes
introduced by the xlog -> wal changes.
Anastasia Lubennikova sent in another revision of a patch to add an IF NOT
EXISTS option for CREATE SERVER and CREATE USER MAPPING statements.
Pavel Stěhule sent in another revision of a patch to implement xmltable().
Thomas Munro sent in another revision of a patch to make it possible to run
parallel queries in SERIALIZABLE isolation mode.
Peter Eisentraut sent in another revision of a patch to implement ICU
Haribabu Kommi sent in a patch to allow parallel writers by separating concerns
so that the backend does the write operations, while the workers produce the
Masahiko Sawada sent in another revision of a patch to add a GUC for cleanup
David Christensen sent in a patch to teach Catalog.pm how many attributes there
should be per DATA() line.
Heikki Linnakangas sent in another revision of a patch to implement SCRAM auth.
Karl O. Pinc and Gilles Darold traded patches to implement pg_current_logfile().
Anastasia Lubennikova sent in another revision of a patch to implement covering
and unique indexes.
Amit Kapila sent in two revisions of a patch to ensure that ON CONFLICT DO
NOTHING work with partitioned tables.
Rafia Sabih sent in three more revisions of a patch to implement parallel
Tom Lane sent in two revisions of a patch to avoiding OOM in a hash join with
many duplicate inner keys.
Rushabh Lathia sent in two more revisions of a patch to implement gather merge.
Rafia Sabih sent in another revision of a patch to allow query string to
Surafel Temsgen sent in another revision of a patch to implement CORRESPONDING.
David Christensen sent in two revisions of a patch to add pg_disable_checksums()
and supporting infrastructure.
KaiGai Kohei sent in another revision of a patch to implement a
ParallelFinish-hook for FDW/CSP.
Amit Langote sent in a patch to fix the fact that pg_dump would emit ALTER TABLE
ONLY for partitioned tables.
Mithun Cy sent in a PoC patch to use a better way to expand hash indexes.
Peter Eisentraut sent in a patch to implement DCL for logical replication.
Amit Khandekar sent in another revision of a patch to implement parallel append.
Michaël Paquier sent in a patch to implement SASLprep aka NFKC for SCRAM
Tom Lane sent in a patch to add an InterTimestamp struct to fix an issue with
floating point timestamps in logical replication.
Alexander Korotkov sent in another revision of a patch to implement incremental
Jim Nasby sent in a patch to deal with the fact that pg_get_object_address()
doesn't support composites.
Robert Haas sent in a patch to fix an instability in the select_parallel
regression test by ensuring that workers get forgotten faster.
Robins Tharakan sent in a patch to add a --no-pgauthid to pg_dumpall, which will
make it at least not fail immediately on Amazon RDS Postgres and similar systems.
Erik Rijkers sent in a patch to fix some comments in origin.c and snapbuild.c
Neha Khatri sent in a patch to fix a typo in varlena.c.
pgsql-announce by date
|Next:||From: Christoph Berg||Date: 2017-02-23 11:01:41|
|Subject: Development snapshot packages for postgresql-10 available on
|Previous:||From: Gilles Darold||Date: 2017-02-17 14:30:49|
|Subject: Ora2Pg v18.1 has been released|