== PostgreSQL Weekly News - December 23, 2018 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - December 23, 2018 ==
Date: 2018-12-23 23:02:34
Message-ID: 20181223230234.GA17928@fetter.org
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-announce

== PostgreSQL Weekly News - December 23, 2018 ==

== PostgreSQL Product News ==

pgpoolAdmin 4.0.1 released.

== PostgreSQL Jobs for December ==


== PostgreSQL Local ==

FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 1st, 2019.

Prague PostgreSQL Developer Day 2019 (P2D2 2019) is a two-day
conference that will be held on February 13-14, 2019 in Prague, Czech Republic.
The CfP is open until January 4, 2018 at https://p2d2.cz/callforpapers

PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka.

pgDay Paris 2019 will be held in Paris, France on March 12, 2019
at 199bis rue Saint-Martin.

PGConf APAC 2019 will be held in Singapore March 19-21, 2019.

The German-speaking PostgreSQL Conference 2019 will take place on May 10, 2019
in Leipzig. The CfP is open until February 26, 2019 at http://2019.pgconf.de/cfp

PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. Both the
CfP https://2019.pgday.it/en/blog/cfp and the Call for Workshops
https://2019.pgday.it/en/blog/cfw are openuntil January 15, 2019.

PGCon 2019 will take place in Ottawa on May 28-31, 2019. The CfP is open
through January 19, 2019 at http://www.pgcon.org/2019/papers.php

Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019.
The CfP is open January 17, 2019 through April 18, 2019, and registration will
open January 17, 2019.

== 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 PST8PDT to david(at)fetter(dot)org(dot)

== Applied Patches ==

Tom Lane pushed:

- Modernize our code for looking up descriptive strings for Unix signals. At
least as far back as the 2008 spec, POSIX has defined strsignal(3) for looking
up descriptive strings for signal numbers. We hadn't gotten the word though,
and were still using the crufty old sys_siglist array, which is in no standard
even though most Unixen provide it. Aside from not being formally
standards-compliant, this was just plain ugly because it involved #ifdef's at
every place using the code. To eliminate the #ifdef's, create a portability
function pg_strsignal, which wraps strsignal(3) if available and otherwise
falls back to sys_siglist[] if available. The set of Unixen with neither API
is probably empty these days, but on any platform with neither, you'll just
get "unrecognized signal". All extant callers print the numeric signal number
too, so no need to work harder than that. Along the way, upgrade
pg_basebackup's child-error-exit reporting to match the rest of the system.
Discussion: https://postgr.es/m/25758.1544983503@sss.pgh.pa.us

- Drop support for getting signal descriptions from sys_siglist[]. It appears
that all platforms that have sys_siglist[] also have strsignal(), making that
fallback case in pg_strsignal() dead code. Getting rid of it allows dropping
a configure test, which seems worth more than providing textual signal
descriptions on whatever platforms might still hypothetically have use for the
fallback case. Discussion: https://postgr.es/m/25758.1544983503@sss.pgh.pa.us

- Fix ancient thinko in mergejoin cost estimation. "rescanratio" was computed
as 1 + rescanned-tuples / total-inner-tuples, which is sensible if it's to be
multiplied by total-inner-tuples or a cost value corresponding to scanning all
the inner tuples. But in reality it was (mostly) multiplied by inner_rows or
a related cost, numbers that take into account the possibility of stopping
short of scanning the whole inner relation thanks to a limited key range in
the outer relation. This'd still make sense if we could expect that stopping
short would result in a proportional decrease in the number of tuples that
have to be rescanned. It does not, however. The argument that establishes
the validity of our estimate for that number is independent of whether we scan
all of the inner relation or stop short, and experimentation also shows that
stopping short doesn't reduce the number of rescanned tuples. So the correct
calculation is 1 + rescanned-tuples / inner_rows, and we should be sure to
multiply that by inner_rows or a corresponding cost value. Most of the time
this doesn't make much difference, but if we have both a high rescan rate (due
to lots of duplicate values) and an outer key range much smaller than the
inner key range, then the error can be significant, leading to a large
underestimate of the cost associated with rescanning. Per report from
Vijaykumar Jain. This thinko appears to go all the way back to the
introduction of the rescan estimation logic in commit 70fba7043, so back-patch
to all supported branches. Discussion:

- Update sepgsql regression test results for commit ca4103025. Per buildfarm.

- Make collation-aware system catalog columns use "C" collation. Up to now we
allowed text columns in system catalogs to use collation "default", but that
isn't really safe because it might mean something different in template0 than
it means in a database cloned from template0. In particular, this could mean
that cloned pg_statistic entries for such columns weren't entirely valid,
possibly leading to bogus planner estimates, though (probably) not any
outright failures. In the wake of commit 5e0928005, a better solution is
available: if we label such columns with "C" collation, then their
pg_statistic entries will also use that collation and hence will be valid
independently of the database collation. This also provides a cleaner
solution for indexes on such columns than the hack added by commit 0b28ea79c:
the indexes will naturally inherit "C" collation and don't have to be forced
to use text_pattern_ops. Also, with the planned improvement of type "name" to
be collation-aware, this policy will apply cleanly to both text and name
columns. Because of the pg_statistic angle, we should also apply this policy
to the tables in information_schema. This patch does that by adjusting
information_schema's textual domain types to specify "C" collation. That has
the user-visible effect that order-sensitive comparisons to textual
information_schema view columns will now use "C" collation by default. The
SQL standard says that the collation of those view columns is
implementation-defined, so I think this is legal per spec. At some point this
might allow for translation of such comparisons into indexable conditions on
the underlying "name" columns, although additional work will be needed before
that can happen. Discussion:

- Doc: fix incorrect example of collecting arguments with fmgr macros. Thinko
in commit f66912b0a. Back-patch to v10, as that was. Discussion:

- Small improvements for allocation logic in ginHeapTupleFastCollect(). Avoid
repetitive calls to repalloc() when the required size of the collector array
grows more than 2x in one call. Also ensure that the array size is a power of
2 (since palloc will probably consume a power of 2 anyway) and doesn't start
out very small (which'd likely just lead to extra repallocs). David Rowley,
tweaked a bit by me Discussion:

- Make type "name" collation-aware. The "name" comparison operators now all
support collations, making them functionally equivalent to "text" comparisons,
except for the different physical representation of the datatype. They do, in
fact, mostly share the varstr_cmp and varstr_sortsupport infrastructure, which
has been slightly enlarged to handle the case. To avoid changes in the
default behavior of the datatype, set name's typcollation to C_COLLATION_OID
not DEFAULT_COLLATION_OID, so that by default comparisons to a name value will
continue to use strcmp semantics. (This would have been the case for system
catalog columns anyway, because of commit 6b0faf723, but doing this makes it
true for user-created name columns as well. In particular, this avoids
locale-dependent changes in our regression test results.) In consequence,
tweak a couple of places that made assumptions about collatable base types
always having typcollation DEFAULT_COLLATION_OID. I have not, however,
attempted to relax the restriction that user- defined collatable types must
have that. Hence, "name" doesn't behave quite like a user-defined type; it
acts more like a domain with COLLATE "C". (Conceivably, if we ever get rid of
the need for catalog name columns to be fixed-length, "name" could actually
become such a domain over text. But that'd be a pretty massive undertaking,
and I'm not volunteering.) Discussion:

- Add text-vs-name cross-type operators, and unify name_ops with text_ops. Now
that name comparison has effectively the same behavior as text comparison, we
might as well merge the name_ops opfamily into text_ops, allowing cross-type
comparisons to be processed without forcing a datatype coercion first. We
need do little more than add cross-type operators to make the opfamily
complete, and fix one or two places in the planner that assumed text_ops was a
single-datatype opfamily. I chose to unify hash name_ops into hash text_ops
as well, since the types have compatible hashing semantics. This allows
marking the new cross-type equality operators as oprcanhash. (Note: this
doesn't remove the name_ops opclasses, so there's no breakage of index
definitions. Those opclasses are just reparented into the text_ops opfamily.)
Discussion: https://postgr.es/m/15938.1544377821@sss.pgh.pa.us

- Make bitmapset.c use 64-bit bitmap words on 64-bit machines. Using the full
width of the CPU's native word size shouldn't cost anything in typical cases.
When working with large bitmapsets, this halves the number of operations
needed for many common BMS operations. On the right sort of test case, a
measurable improvement is obtained. David Rowley Discussion:

- Doc: fix ancient mistake in search_path documentation. "$user" in a
search_path string is replaced by CURRENT_USER not SESSION_USER. (It actually
was SESSION_USER in the initial implementation, but we changed it shortly
later, and evidently forgot to fix the docs to match.) Noted by
antonov(at)stdpr(dot)ru Discussion:

- Base information_schema.sql_identifier domain on name, not varchar. The SQL
spec says that sql_identifier is a domain over varchar, but it also says that
that domain is supposed to represent the set of valid identifiers for the
implementation, in particular applying a length limit matching the
implementation's identifier length limit. We were declaring sql_identifier as
just "character varying", thus duplicating what the spec says about base type,
but entirely failing at the rest of it. Instead, let's declare sql_identifier
as a domain over type "name". (We can drop the COLLATE "C" added by commit
6b0faf723, since that's now implicit in "name".) With the recent improvements
to name's comparison support, there's not a lot of functional difference
between name and varchar. So although in principle this is a spec deviation,
it's a pretty minor one. And correctly enforcing PG's name length limit is a
good thing; on balance this seems closer to the intent of the spec than what
we had. But that's all just language-lawyering. The *real* reason to do this
is that it makes sql_identifier columns exposed by information_schema views be
just direct representations of the underlying "name" catalog columns,
eliminating a semantic mismatch that was disastrous for performance of typical
queries on the information_schema. In combination with the recent change to
allow dropping no-op CoerceToDomain nodes, this allows (for example) queries
such as select ... from information_schema.tables where table_name = 'foo'; to
produce an indexscan rather than a seqscan on pg_class. Discussion:

- Avoid producing over-length specific_name outputs in information_schema.
information_schema output columns that are declared as being type
sql_identifier are supposed to conform to the implementation's rules for valid
identifiers, in particular the identifier length limit. Several places
potentially violated this limit by concatenating a function's name and OID.
(The OID is added to ensure name uniqueness within a schema, since the spec
doesn't expect function name overloading.) Simply truncating the concatenation
result to fit in "name" won't do, since losing part of the OID might wind up
giving non-unique results. Instead, let's truncate the function name as
necessary. The most practical way to do that is to do it in a C function; the
information_schema.sql script doesn't have easy access to the value of
NAMEDATALEN, nor does it have an easy way to truncate on the basis of
resulting byte-length rather than number of characters. (There are still a
couple of places that cast concatenation results to sql_identifier, but as far
as I can see they are guaranteed not to produce over-length strings, at least
with the normal value of NAMEDATALEN.) Discussion:

Michaël Paquier pushed:

- Make constraint rename issue relcache invalidation on target relation. When a
constraint gets renamed, it may have associated with it a target relation (for
example domain constraints don't have one). Not invalidating the target
relation cache when issuing the renaming can result in issues with subsequent
commands that refer to the old constraint name using the relation cache,
causing various failures. One pattern spotted was using CREATE TABLE LIKE
after a constraint renaming. Reported-by: Stuart <sfbarbee(at)gmail(dot)com> Author:
Amit Langote Reviewed-by: Michael Paquier Discussion:

- Fix use-after-free bug when renaming constraints. This is an oversight from
recent commit b13fd344. While on it, tweak the previous test with a better
name for the renamed primary key. Detected by buildfarm member prion which
forces relation cache release with -DRELCACHE_FORCE_RELEASE. Back-patch down
to 9.4 as the previous commit.

- Include ALTER INDEX SET STATISTICS in pg_dump. The new grammar pattern of
ALTER INDEX SET STATISTICS able to use column numbers on top of the existing
column names introduced by commit 5b6d13e forgot to add support for the
feature in pg_dump, so defining statistics on index columns was missing from
the dumps, potentially causing silent planning problems with a subsequent
restore. pg_dump ought to not use column names in what it generates as these
are automatically generated by the server and could conflict with real
relation attributes with matching patterns. "expr" and "exprN", N incremented
automatically after the creation of the first one, are used as default
attribute names for index expressions, and that could easily match what is
defined in other relations, causing the dumps to fail if some of those
attributes are renamed at some point. So to avoid any problems, the new
grammar with column numbers gets used. Reported-by: Ronan Dunklau Author:
Michael Paquier Reviewed-by: Tom Lane, Adrien Nayrat, Amul Sul Discussion:
Backpatch-through: 11, where the new syntax has been introduced.

- Update project link of pgBadger in documentation. The project has moved to a
new place. Reported-by: Peter Neave Discussion:

- Tweak description comments in tests for partition functions. The new wording
is more generic and fixes one grammar mistake and one typo on the way. Per
discussion between Amit Langote and me. Discussion:

- Include partitioned indexes to system view pg_indexes. pg_tables already
includes partitioned tables, so for consistency pg_indexes should show
partitioned indexes. Author: Suraj Kharage Reviewed-by: Amit Langote, Michael
Paquier Discussion:

- Add more tab completion for CREATE TABLE in psql The following completion
patterns are added: - CREATE TABLE <name> with '(', OF or PARTITION OF. -
CREATE TABLE <name> OF with list of composite types. - CREATE TABLE name
(...) with PARTITION OF, WITH, TABLESPACE, ON COMMIT (depending on the
presence of a temporary table). - CREATE TABLE ON COMMIT with actions (only
for temporary tables). Author: Dagfinn Ilmari Mannsåker Discussion:

- Add completion for storage parameters after CREATE TABLE WITH in psql. In
passing, move the list of parameters where it can be used for both CREATE
TABLE and ALTER TABLE, and reorder it alphabetically. Author: Dagfinn Ilmari
Mannsåker Discussion: https://postgr.es/m/d8j1s77kdbb.fsf@dalvik.ping.uio.no

- Disable WAL-skipping optimization for COPY on views and foreign tables. COPY
can skip writing WAL when loading data on a table which has been created in
the same transaction as the one loading the data, however this cannot work on
views or foreign table as this would result in trying to flush relation files
which do not exist. So disable the optimization so as commands are able to
work the same way with any configuration of wal_level. Tests are added to
cover the different cases, which need to have wal_level set to minimal to
allow the problem to show up, and that is not the default configuration.
Reported-by: Luis M. Carril, Etsuro Fujita Author: Amit Langote, Michael
Paquier Reviewed-by: Etsuro Fujita Discussion:
https://postgr.es/m/15552-c64aa14c5c22f63c@postgresql.org Backpatch-through:
10, where support for COPY on views has been added, while v11 has added
support for COPY on foreign tables.

Amit Kapila pushed:

- Remove extra semicolons. Reported-by: David Rowley Author: David Rowley
Reviewed-by: Amit Kapila Backpatch-through: 10 Discussion:

Álvaro Herrera pushed:

- Clarify runtime pruning in EXPLAIN. Author: Amit Langote Reviewed-by: David
Rowley Discussion:

- Fix tablespace handling for partitioned tables. When partitioned tables were
introduced, we failed to realize that by copying the tablespace handling for
other relation kinds with no physical storage we were causing the secondary
effect that their partitions would not automatically inherit the tablespace
setting. This is surprising and unhelpful, so change it to adopt the behavior
introduced in pg11 (commit 33e6c34c3267) for partitioned indexes: the parent
relation remembers the tablespace specification, which is then used for any
new partitions that don't declare one. Because this commit changes behavior
of the TABLESPACE clause for partitioned tables (it's no longer a no-op), it
is not backpatched. Author: David Rowley, Álvaro Herrera Reviewed-by: Michael
Paquier Discussion:

- DETACH PARTITION: hold locks on indexes until end of transaction. When a
partition is detached from its parent, we acquire locks on all attached
indexes to also detach them ... but we release those locks immediately. This
is a violation of the policy of keeping locks on user objects to the end of
the transaction. Bug introduced in 8b08f7d4820f. It's unclear that there are
any ill effects possible, but it's clearly wrong nonetheless. It's likely
that bad behavior *is* possible, but mostly because the relation that the
index is for is only locked with AccessShareLock, which is an older bug that
shall be fixed separately. While touching that line of code, close the index
opened with index_open() using index_close() instead of relation_close(). No
difference in practice, but let's be consistent. Unearthed by Robert Haas.

- Fix lock level used for partition when detaching it. For probably bogus
reasons, we acquire only AccessShareLock on the partition when we try to
detach it from its parent partitioned table. This can cause ugly things to
happen if another transaction is doing any sort of DDL to the partition
concurrently. Upgrade that lock to ShareUpdateExclusiveLock, which per
discussion seems to be the minimum needed. Reported by Robert Haas.

- Remove function names from error messages. They are not necessary, and having
them there gives useless work for translators.

Tatsuo Ishii pushed:

- Doc: fix typo in "Generic File Access Functions" section. Issue reported by
me and fix by Tom Lane. Discussion:

Peter Geoghegan pushed:

- Correct obsolete nbtree recovery comments. Commit 40dae7ec537, which made the
handling of interrupted nbtree page splits more robust, removed an
nbtree-specific end-of-recovery cleanup step. This meant that it was no
longer possible to complete an interrupted page split during recovery.
However, a reference to recovery as a reason for using a NULL stack while
inserting into a parent page was missed. Remove the reference. Remove a
similar obsolete reference to recovery that was introduced much more recently,
as part of the btree fastpath optimization enhancement that made it into
Postgres 11 (commit 2b272734, and follow-up commits). Backpatch: 11-, where
the fastpath optimization was introduced.

- Remove obsolete nbtree duplicate entries comment. Remove a comment from the
Berkeley days claiming that nbtree must disambiguate duplicate keys within
_bt_moveright(). There is no special care taken around duplicates within
_bt_moveright(), at least since commit 9e85183bfc3 removed inscrutable
_bt_moveright() code to handle pages full of duplicates.

Gregory Stark pushed:

- Fix ADD IF NOT EXISTS used in conjunction with ALTER TABLE ONLY. The flag for
IF NOT EXISTS was only being passed down in the normal recursing case. It's
been this way since originally added in 9.6 in commit 2cd40adb85 so backpatch
back to 9.6.

Alexander Korotkov pushed:

- Check for conflicting queries during replay of gistvacuumpage(). 013ebc0a7b
implements so-called GiST microvacuum. That is gistgettuple() marks index
tuples as dead when kill_prior_tuple is set. Later, when new tuple insertion
claims page space, those dead index tuples are physically deleted from page.
When this deletion is replayed on standby, it might conflict with read-only
queries. But 013ebc0a7b doesn't handle this. That may lead to disappearance
of some tuples from read-only snapshots on standby. This commit implements
resolving of conflicts between replay of GiST microvacuum and standby queries.
On the master we implement new WAL record type XLOG_GIST_DELETE, which
comprises necessary information. On stable releases we've to be tricky to
keep WAL compatibility. Information required for conflict processing is just
appended to data of XLOG_GIST_PAGE_UPDATE record. So, PostgreSQL version,
which doesn't know about conflict processing, will just ignore that.
Reported-by: Andres Freund Diagnosed-by: Andres Freund Discussion:
Author: Alexander Korotkov Backpatch-through: 9.6

Peter Eisentraut pushed:

- Fix ancient compiler warnings and typos in !HAVE_SYMLINK code. This has never
been correct since this code was introduced.

- Add some const decorations. These mainly help understanding the function
signatures better.

WRITE_INT_ARRAY, WRITE_BOOL_ARRAY macros to outfuncs.c, mirroring the existing
READ_*_ARRAY macros in readfuncs.c. Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

== Pending Patches ==

Álvaro Herrera sent in another revision of a patch to Allow newly created
partitions to inherit their parents' tablespaces.

Surafel Temesgen sent in another revision of a patch to add a multi-values
INSERT option to pg_dump.

Alexander Kukushkin sent in another revision of a patch to add

Etsuro Fujita sent in another revision of a patch to postgres_fdw to make it do

Dmitry Dolgov sent in a patch to add pg_dump support to user-defined access

Michaël Paquier sent in two more revisions of a patch to ensure that things
created by ALTER INDEX are supported by pg_dump.

Álvaro Herrera sent in another revision of a patch to ensure that storage is
only created when needed.

Pavel Stěhule sent in two more revisions of a patch to add \dP (partitioned
tables) to psql.

Hayato Kuroda sent in two revisions of a patch to add DECLARE STATEMENT to ECPG.

Aleksey Kondratov and Tomáš Vondra traded patches to add logical_work_mem.

Andres Freund sent in a patch to add overflow checks for interval.

Konstantin Knizhnik sent in another revision of a patch to build in connection

Tony Reix sent in a patch to enable SysV shm for AIX.

Kyotaro HORIGUCHI sent in a patch to clarify the fact that -c options passed to
psql are treated as though they were lines in a script and make psql's behavior
more uniform in the presence of multiple -c options.

Robbie Harwood sent in another revision of a patch to support GSSAPI encryption.

Filip Rembiałkowski and Pavel Stěhule traded patches to add a --force option to

Masahiko Sawada sent in another revision of a patch to implement block-level
parallel vacuum.

Peter Eisentraut sent in a patch to implement insensitive collations.

Ryo Matsumura sent in another revision of a patch to add bytea to ECPG.

Surafel Temesgen sent in another revision of a patch to add conflict handling to

Chengchao Yu sent in another revision of a patch to fix a deadlock issue in
single-user mode.

David Rowley sent in another revision of a patch to llow Append to be used in
place of MergeAppend for some cases.

Álvaro Herrera sent in a patch to add the idea of an unsuitable relkind to

Nathan Bossart sent in a patch to add some options to vacuumdb that correspond
to the ones VACUUM already has by itself.

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

Kyotaro HORIGUCHI sent in another revision of a patch to allow skipping WAL
logging in cases where that would help.

David Rowley sent in a patch to make it possible to use POPCNT and similar
advanced bit-twiddling instructions where available.

Michael Banck sent in another revision of a patch to make it possible to verify
checksums online.

Yuzuko Hosoya sent in two revisions of a patch to improve selectivity estimate
for range queries.

Robert Haas sent in a patch to make it possible to ATTACH/DETACH PARTITION

John Naylor sent in three revisions of a patch to reduce the footprint of
ScanKeyword by making it offset-based.

Kyotaro HORIGUCHI sent in a patch to clarify the comments about "all" and
"replication" in pg_hba.conf.sample.

Tsutomu Yamada and Michaël Paquier traded patches to add tab completion in psql

Michael Banck sen in another revision of a patch to add progress reporting for

Jesper Pedersen sent in a patch to make pg_upgrade pass -j down to vacuumdb.

Michaël Paquier sent in a patch to clean up some elog messages and comments for
do_pg_stop_backup and do_pg_start_backup.

Michaël Paquier sent in another revision of a patch to change pgarch_readyXlog()
to return .history files first.

Michael Banck sent in a patch to add offline enabling/disabling of checksums to

Tom Lane sent in a patch to support parameterized TID scans.

Jeff Janes sent in a patch to make relcache init write errors not be fatal.

Dilip Kumar sent in another revision of a patch to add an UNDO log manager,
provide access to its data via the buffer manager, and provide an interface for
prepare, insert, or fetch the UNDO records.

Heikki Linnakangas sent in another revision of a patch to speed up up
text_position_next with multibyte encodings using the single-byte
Boyer-Moore-Horspool search.

David Rowley sent in a patch to fix a performance issue in foreign-key-aware
join estimation.

Browse pgsql-announce by date

  From Date Subject
Next Message Gilles Darold 2018-12-27 23:11:34 pgBadger 10.2 is out
Previous Message Bo Peng 2018-12-20 06:04:22 PgpoolAdmin 4.0.1 officially released.