== PostgreSQL Weekly News - January 28 2018 ==

== PostgreSQL Weekly News - January 28 2018 ==

PGDay.IT 2018 will take place in Lazise on Lake Garda on June 29th, 2018. The
CfP is open at https://2018.pgday.it/en/blog/cfp until February 28, 2018, and
the Call for Workshops is at https://2018.pgday.it/en/blog/cfw until February
28, 2018
https://2018.pgday.it/en/

== PostgreSQL Product News ==

pgAdmin4 2.1, a web- and native GUI control center for PostgreSQL, released.
https://www.pgadmin.org/

== PostgreSQL Jobs for January ==

http://archives.postgresql.org/pgsql-jobs/2018-01/

== PostgreSQL Local ==

FOSDEM PGDay 2018, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 2nd, 2018.
https://2018.fosdempgday.org/

Prague PostgreSQL Developer Day 2018 (P2D2 2018) is a two-day
conference that will be held on February 14-15 2018 in Prague, Czech Republic.
http://www.p2d2.cz/

PGConf India 2018 will be on February 22-23, 2018 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL(at)SCaLE is a two day, two track event which takes place on
March 8-9, 2018, at Pasadena Convention Center, as part of SCaLE 16X.
http://www.socallinuxexpo.org/scale/16x/cfp

Nordic PGDay 2018 will be held in Oslo, Norway, at the Radisson Blu Hotel
Nydalen, on March 13, 2018. The CfP is open through December 31, 2017 at
https://2018.nordicpgday.org/cfp/

pgDay Paris 2018 will be held in Paris, France at the Espace Saint-Martin, on
March 15 2018. The CfP is open until December 31, 2017.
http://2018.pgday.paris/callforpapers/

PGConf APAC 2018 will be held in Singapore March 22-23, 2018.
http://2018.pgconfapac.org/

The German-speaking PostgreSQL Conference 2018 will take place on April 13th,
2018 in Berlin.
http://2018.pgconf.de/

PGConfNepal 2018 will be held May 4-5, 2018 at Kathmandu University, Dhulikhel,
Nepal. The CfP is open until February 1, 2018 at
https://postgresconf.org/conferences/Nepal2018/program/proposals
https://postgresconf.org/conferences/Nepal2018

PGCon 2018 will take place in Ottawa on May 29 - June 1, 2018.
https://www.pgcon.org/2018/

PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018. The
CfP will open soon.
http://pgconf.com.br

== 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 EST5EDT. 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 ==

Magnus Hagander pushed:

- Fix docs typo. Spotted by Thomas Munro
https://git.postgresql.org/pg/commitdiff/b9ff79b8f17697f3df492017d454caa9920a7183

- Add missing semicolons in documentation examples. Author: Daniel Gustafsson
<daniel(at)yesql(dot)se>
https://git.postgresql.org/pg/commitdiff/ba8c2dfffd8e018fa0fae554fee69a7b7e93472e

Peter Eisentraut pushed:

- Transaction control in PL procedures In each of the supplied procedural
languages (PL/pgSQL, PL/Perl, PL/Python, PL/Tcl), add language-specific commit
and rollback functions/commands to control transactions in procedures in that
language. Add similar underlying functions to SPI. Some additional cleanup
so that transaction commit or abort doesn't blow away data structures still
used by the procedure call. Add execution context tracking to CALL and DO
statements so that transaction control commands can only be issued in
top-level procedure and block calls, not function calls or other procedure or
block calls. - SPI Add a new function SPI_connect_ext() that is like
SPI_connect() but allows passing option flags. The only option flag right now
is SPI_OPT_NONATOMIC. A nonatomic SPI connection can execute transaction
control commands, otherwise it's not allowed. This is meant to be passed down
from CALL and DO statements which themselves know in which context they are
called. A nonatomic SPI connection uses different memory management. A
normal SPI connection allocates its memory in TopTransactionContext. For
nonatomic connections we use PortalContext instead. As the comment in
SPI_connect_ext() (previously SPI_connect()) indicates, one could potentially
use PortalContext in all cases, but it seems safest to leave the existing uses
alone, because this stuff is complicated enough already. SPI also gets new
functions SPI_start_transaction(), SPI_commit(), and SPI_rollback(), which can
be used by PLs to implement their transaction control logic. - portalmem.c
Some adjustments were made in the code that cleans up portals at transaction
abort. The portal code could already handle a command *committing* a
transaction and continuing (e.g., VACUUM), but it was not quite prepared for a
command *aborting* a transaction and continuing. In AtAbort_Portals(), remove
the code that marks an active portal as failed. As the comment there already
predicted, this doesn't work if the running command wants to keep running
after transaction abort. And it's actually not necessary, because pquery.c is
careful to run all portal code in a PG_TRY block and explicitly runs
MarkPortalFailed() if there is an exception. So the code in AtAbort_Portals()
is never used anyway. In AtAbort_Portals() and AtCleanup_Portals(), we need
to be careful not to clean up active portals too much. This mirrors similar
code in PreCommit_Portals(). - PL/Perl Gets new functions spi_commit() and
spi_rollback() - PL/pgSQL Gets new commands COMMIT and ROLLBACK. Update the
PL/SQL porting example in the documentation to reflect that transactions are
now possible in procedures. - PL/Python Gets new functions plpy.commit and
plpy.rollback. - PL/Tcl Gets new commands commit and rollback. Reviewed-by:
Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>
https://git.postgresql.org/pg/commitdiff/8561e4840c81f7e345be2df170839846814fa004

- PL/Python: Fix tests for older Python versions. Commit
8561e4840c81f7e345be2df170839846814fa004 neglected to handle older Python
versions that don't support the "with" statement. So write the tests in a way
that older versions can handle as well.
https://git.postgresql.org/pg/commitdiff/f498704346a4ce4953fc5f837cacb545b3166ee1

- Split out documentation of SSL parameters into their own section. Split the
"Authentication and Security" section into two separate sections
"Authentication" and "SSL". The latter part has gotten much longer over time,
and doesn't primarily have to do with authentication. Also, the row_security
parameter was inconsistently categorized, so clean that up while we're here.
https://git.postgresql.org/pg/commitdiff/7404e77cc1192855afef28ae557993ba6f35c16e

- Move EDH support to common files. The EDH support is not really specific to
the OpenSSL implementation, so move the support and documentation comments to
common files.
https://git.postgresql.org/pg/commitdiff/573bd08b99e277026e87bb55ae69c489fab321b8

- Add installcheck support to more test suites. Several of the test suites
under src/test/ were missing an installcheck target.
https://git.postgresql.org/pg/commitdiff/f5da5683a86e9fc42fdf3eae2da8b096bda76a8a

- Extract common bits from OpenSSL implementation. Some things in
be-secure-openssl.c and fe-secure-openssl.c were not actually specific to
OpenSSL but could also be used by other implementations. In order to avoid
copy-and-pasting, move some of that code to common files.
https://git.postgresql.org/pg/commitdiff/1c2183403b958422c27782329ba19f9a3e0874ba

- Move SSL API comments to header files. Move the documentation of the SSL API
calls are supposed to do into the headers files, instead of keeping them in
the files for the OpenSSL implementation. That way, they don't have to be
duplicated or be inconsistent when other implementations are added.
https://git.postgresql.org/pg/commitdiff/f966101d19fcef6441e43da417467b3ed5ad3074

- pgbench: Remove accidental garbage in test file. Author: Fabien COELHO
<coelho(at)cri(dot)ensmp(dot)fr>
https://git.postgresql.org/pg/commitdiff/f9bbd46adbf350ba9e99a808f2c759e4aab9ea70

- Add tests for record_image_eq and record_image_cmp. record_image_eq was
covered a bit by the materialized view code that it is meant to support, but
record_image_cmp was not tested at all. While we're here, add more tests to
record_eq and record_cmp as well, for symmetry. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/a61116da8b99c4ff4b8c5757697abda7ac36b022

- Allow spaces in connection strings in SSL tests. Connection strings can have
items with spaces in them, wrapped in quotes. The tests however ran a SELECT
'$connstr' upon connection which broke on the embedded quotes. Use dollar
quotes on the connstr to protect against this. This was hit during the
development of the macOS Secure Transport patch, but is independent of it.
Author: Daniel Gustafsson <daniel(at)yesql(dot)se>
https://git.postgresql.org/pg/commitdiff/4a3fdbdf766d80b21271e32da865801ab005d786

- Remove use of byte-masking macros in record_image_cmp. These were introduced
in 4cbb646334b3b998a29abef0d57608d42097e6c9, but after further analysis and
testing, they should not be necessary and probably weren't the part of that
commit that fixed anything. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/0b5e33f667a2042d7022da8bef31a8be5937aad1

- Update documentation to mention huge pages on other OSes. Previously, the
docs implied that only Linux and Windows could use huge pages. That's not
quite true: it's just that we only know how to request them explicitly on
those OSes. Be more explicit about what huge_pages really does and mention
that some OSes may use huge pages automatically. Author: Thomas Munro and
Catalin Iacob Reviewed-By: Justin Pryzby, Peter Eisentraut Discussion:
https://postgr.es/m/CAEepm=3qzR-hfjepymohuC4XO5phxoSoipOjm6BEhnJHjNR+jg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2a5ecb56d22340a00393fa60e7b910c472071875

- Remove byte-masking macros for Datum conversion macros. As the comment there
stated, these were needed for old-style user-defined functions, but since we
removed support for those, we don't need this anymore. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/a6ef00b5c3c4a287e03b634d328529b69cc1e770

- Use abstracted SSL API in server connection log messages. The existing
"connection authorized" server log messages used OpenSSL API calls directly,
even though similar abstracted API calls exist. Change to use the latter
instead. Change the function prototype for the functions that return the TLS
version and the cipher to return const char * directly instead of copying into
a buffer. That makes them slightly easier to use. Add bits= to the message.
psql shows that, so we might as well show the same information on the client
and server. Reviewed-by: Daniel Gustafsson <daniel(at)yesql(dot)se> Reviewed-by:
Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/c1869542b3a4da4b12cace2253ef177da761c00d

Tom Lane pushed:

- Make pg_dump's ACL, sec label, and comment entries reliably identifiable.
_tocEntryRequired() expects that it can identify ACL, SECURITY LABEL, and
COMMENT TOC entries that are for large objects by seeing whether the tag for
them starts with "LARGE OBJECT ". While that works fine for actual large
objects, which are indeed tagged that way, it's subject to false positives
unless every such entry's tag starts with an appropriate type ID. And in fact
it does not work for ACLs, because up to now we customarily tagged those
entries with just the bare name of the object. This means that an ACL for an
object named "LARGE OBJECT something" would be misclassified as data not
schema, with undesirable results in a schema-only or data-only dump ---
although pg_upgrade seems unaffected, due to the special case for
binary-upgrade mode further down in _tocEntryRequired(). We can fix this by
changing all the dumpACL calls to use the label strings already in use for
comments and security labels, which do follow the convention of starting with
an object type indicator. Well, mostly they follow it. dumpDatabase() got it
wrong, using just the bare database name for those purposes, so that a
database named "LARGE OBJECT something" would similarly be subject to having
its comment or security label dropped or included when not wanted. Bring that
into line too. (Note that up to now, database ACLs have not been processed by
pg_dump, so that this issue doesn't affect them.) _tocEntryRequired() itself
is not free of fault: it was overly liberal about matching object tags to
"LARGE OBJECT " in binary-upgrade mode. This looks like it is probably
harmless because there would be no data component to strip anyway in that
mode, but at best it's trouble waiting to happen, so tighten that up too. The
possible misclassification of SECURITY LABEL entries for databases is in
principle a security problem, but the opportunities for actual exploits seem
too narrow to be interesting. The other cases seem like just bugs, since an
object owner can change its ACL or comment for himself, he needn't try to
trick someone else into doing it by choosing a strange name. This has been
broken since per-large-object TOC entries were introduced in 9.0, so
back-patch to all supported branches. Discussion:
https://postgr.es/m/21714.1516553459@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/2b792ab094415f351abd5854de5cefb023931a85

- Reorder code in pg_dump to dump comments etc in a uniform order. Most of the
code in pg_dump dumps an object's comment, security label, and ACL auxiliary
TOC entries, in that order, immediately after the object's main TOC entry, and
at least dumpComment's API spec says this isn't optional. dumpDatabase was
significantly violating that when in binary-upgrade mode, by inserting totally
unrelated stuff between. Also, dumpForeignDataWrapper and dumpForeignServer
were being randomly inconsistent. Reorder code so everybody does it the same.
This may be future-proofing us against some code growing a requirement for
such auxiliary entries to be adjacent to their main entry. But for now it's
just neatnik-ism, so I see no need for back-patch. Discussion:
https://postgr.es/m/21714.1516553459@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/d6c84667d130f19efdf0f04f7d52a6b37df0f21b

- Move handling of database properties from pg_dumpall into pg_dump. This patch
rearranges the division of labor between pg_dump and pg_dumpall so that
pg_dump itself handles all properties attached to a single database. Notably,
a database's ACL (GRANT/REVOKE status) and local GUC settings established by
ALTER DATABASE SET and ALTER ROLE IN DATABASE SET can be dumped and restored
by pg_dump. This is a long-requested improvement. "pg_dumpall -g" will now
produce only role- and tablespace-related output, nothing about individual
databases. The total output of a regular pg_dumpall run remains the same.
pg_dump (or pg_restore) will restore database-level properties only when
creating the target database with --create. This applies not only to ACLs and
GUCs but to the other database properties it already handled, that is database
comments and security labels. This is more consistent and useful, but does
represent an incompatibility in the behavior seen without --create. (This
change makes the proposed patch to have pg_dump use "COMMENT ON DATABASE
CURRENT_DATABASE" unnecessary, since there is no case where the command is
issued that we won't know the true name of the database. We might still want
that patch as a feature in its own right, but pg_dump no longer needs it.)
pg_dumpall with --clean will now drop and recreate the "postgres" and
"template1" databases in the target cluster, allowing their locale and
encoding settings to be changed if necessary, and providing a cleaner way to
set nondefault tablespaces for them than we had before. This means that such
a script must now always be started in the "postgres" database; the order of
drops and reconnects will not work otherwise. Without --clean, the script
will not adjust any database-level properties of those two databases
(including their comments, ACLs, and security labels, which it formerly would
try to set). Another minor incompatibility is that the CREATE DATABASE
commands in a pg_dumpall script will now always specify locale and encoding
settings. Formerly those would be omitted if they matched the cluster's
default. While that behavior had some usefulness in some migration scenarios,
it also posed a significant hazard of unwanted locale/encoding changes. To
migrate to another locale/encoding, it's now necessary to use pg_dump without
--create to restore into a database with the desired settings. Commit
4bd371f6f's hack to emit "SET default_transaction_read_only = off" is gone: we
now dodge that problem by the expedient of not issuing ALTER DATABASE SET
commands until after reconnecting to the target database. Therefore, such
settings won't apply during the restore session. In passing, improve some
shaky grammar in the docs, and add a note pointing out that pg_dumpall's
output can't be expected to load without any errors. (Someday we might want
to fix that, but this is not that patch.) Haribabu Kommi, reviewed at various
times by Andreas Karlsson, Vaishnavi Prabakaran, and Robert Haas; further
hacking by me. Discussion:
https://postgr.es/m/CAJrrPGcUurV0eWTeXODwsOYFN=Ekq36t1s0YnFYUNzsmRfdAyA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b3f8401205afdaf63cb20dc316d44644c933d5a1

- In pg_dump, force reconnection after issuing ALTER DATABASE SET command(s).
The folly of not doing this was exposed by the buildfarm: in some cases, the
GUC settings applied through ALTER DATABASE SET may be essential to
interpreting the reloaded data correctly. Another argument why we can't
really get away with the scheme proposed in commit b3f840120 is that it cannot
work for parallel restore: even if the parent process manages to hang onto the
previous GUC state, worker processes would see the state post-ALTER-DATABASE.
(Perhaps we could have dodged that bullet by delaying DATABASE PROPERTIES
restoration to the end of the run, but that does nothing for the data
semantics problem.) This leaves us with no solution for the
default_transaction_read_only issue that commit 4bd371f6f intended to work
around, other than "you gotta remove such settings before dumping/upgrading".
However, in view of the fact that parallel restore broke that hack years ago
and no one has noticed, it's fair to question how many people care. I'm
unexcited about adding a large dollop of new complexity to handle that corner
case. This would be a one-liner fix, except it turns out that
ReconnectToServer tries to optimize away "redundant" reconnections. While
that may have been valuable when coded, a quick survey of current callers
shows that there are no cases where that's actually useful, so just remove
that check. While at it, remove the function's useless return value.
Discussion: https://postgr.es/m/12453.1516655001@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/160a4f62ee7b8a96984f8bef19c90488aa6c8045

- Documentation fix: pg_ctl no longer makes connection attempts. Overlooked in
commit f13ea95f9. Noted by Nick Barnes. Discussion:
https://postgr.es/m/20180123093723.7407.3386@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/c9707d9413b171a6f017db1ea7832d797d3abc0d

- Teach reparameterize_path() to handle AppendPaths. If we're inside a lateral
subquery, there may be no unparameterized paths for a particular child
relation of an appendrel, in which case we *must* be able to create
similarly-parameterized paths for each other child relation, else the planner
will fail with "could not devise a query plan for the given query". This
means that there are situations where we'd better be able to reparameterize at
least one path for each child. This calls into question the assumption in
reparameterize_path() that it can just punt if it feels like it. However, the
only case that is known broken right now is where the child is itself an
appendrel so that all its paths are AppendPaths. (I think possibly I
disregarded that in the original coding on the theory that nested appendrels
would get folded together --- but that only happens *after*
reparameterize_path(), so it's not excused from handling a child AppendPath.)
Given that this code's been like this since 9.3 when LATERAL was introduced,
it seems likely we'd have heard of other cases by now if there were a larger
problem. Per report from Elvis Pranskevichus. Back-patch to 9.3.
Discussion: https://postgr.es/m/5981018.zdth1YWmNy@hammer.magicstack.net
https://git.postgresql.org/pg/commitdiff/bb94ce4d26c3b011c01bf44ab200334fea52b600

- Improve implementation of pg_attribute_always_inline. Avoid compiler warnings
on MSVC (which doesn't want to see both __forceinline and inline) and ancient
GCC (which doesn't have __attribute__((always_inline))). Don't force
inline-ing when building at -O0, as the programmer is probably hoping for
exact source-to-object-line correspondence in that case. (For the moment this
only works for GCC; maybe we can extend it later.) Make
pg_attribute_always_inline be syntactically a drop-in replacement for inline,
rather than an additional wart. And improve the comments. Thomas Munro and
Michail Nikolaev, small tweaks by me Discussion:
https://postgr.es/m/32278.1514863068@sss.pgh.pa.us Discussion:
https://postgr.es/m/CANtu0oiYp74brgntKOxgg1FK5+t8uQ05guSiFU6FYz_5KUhr6Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/434e6e1484418c55561914600de9e180fc408378

- Improve pg_dump's handling of "special" built-in objects. We had some pretty
ad-hoc handling of the public schema and the plpgsql extension, which are both
presumed to exist in template0 but might be modified or deleted in the
database being dumped. Up to now, by default pg_dump would emit a CREATE
EXTENSION IF NOT EXISTS command as well as a COMMENT command for plpgsql. The
usefulness of the former is questionable, and the latter caused annoying
errors in non-superuser dump/restore scenarios. Let's instead install a rule
that built-in extensions (identified by having low-numbered OIDs) are not to
be dumped. We were doing it that way already in binary-upgrade mode, so this
just makes regular mode behave the same. It remains true that if someone has
installed a non-default ACL on the plpgsql language, that will get dumped
thanks to the pg_init_privs mechanism. This is more consistent with the
handling of built-in objects of other kinds. Also, change the very ad-hoc
mechanism that was used to avoid dumping creation and comment commands for the
public schema. Instead of hardwiring a test in _printTocEntry(), make use of
the DUMP_COMPONENT_ infrastructure to mark that schema up-front about what we
want to do with it. This has the visible effect that the public schema won't
be mentioned in the output at all, except for updating its ACL if it has a
non-default ACL. Previously, while it was normally not mentioned, --clean
mode would drop and recreate it, again causing headaches for non-superuser
usage. This change likewise makes the public schema less special and more
like other built-in objects. If plpgsql, or the public schema, has been
removed entirely in the source DB, that situation won't be reproduced in the
destination ... but that was true before. Discussion:
https://postgr.es/m/29048.1516812451@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/5955d934194c3888f30318209ade71b53d29777f

- Clean up some aspects of pg_dump/pg_restore item-selection logic. Ensure that
CREATE DATABASE and related commands are issued when, and only when, --create
is specified. Previously there were scenarios where using selective-dump
switches would prevent --create from having any effect. For example, it would
fail to do anything in pg_restore if the archive file had been made by a
selective dump, because there would be no TOC entry for the database. Since
we don't issue \connect either if we don't issue CREATE DATABASE, this could
result in unexpectedly restoring objects into the wrong database. Also fix
pg_restore's selective restore logic so that when an object is selected to be
restored, we also restore its ACL, comment, and security label if any.
Previously there was no way to get the latter properties except through
tedious mucking about with a -L file. If, for some reason, you don't want
these properties, you can match the old behavior by adding --no-acl etc.
While at it, try to make _tocEntryRequired() a little better organized and
better documented. Discussion:
https://postgr.es/m/32668.1516848577@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/0d4e6ed3085828edb68f516067d45761c0a89ac5

- Add missing "static" markers. Per buildfarm.
https://git.postgresql.org/pg/commitdiff/bb415675d8ab6e776321a96f9c0e77c12fda96ea

- Support --no-comments in pg_dump, pg_dumpall, pg_restore. We have switches
already to suppress other subsidiary object properties, such as ACLS, security
labels, ownership, and tablespaces, so just on the grounds of symmetry we
should allow suppressing comments as well. Also, commit 0d4e6ed30 added a
positive reason to have this feature, i.e. to allow obtaining the old behavior
of selective pg_restore should anyone desire that. Recent commits have
removed the cases where pg_dump emitted comments on built-in objects that the
restoring user might not have privileges to comment on, so the original
primary motivation for this feature is gone, but it still seems at least
somewhat useful in its own right. Robins Tharakan, reviewed by Fabrízio Mello
Discussion:
https://postgr.es/m/CAEP4nAx22Z4ch74oJGzr5RyyjcyUSbpiFLyeYXX8pehfou92ug@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/1368e92e16a098338e39c8e540bdf9f6cf35ebf4

- Remove the obsolete WITH clause of CREATE FUNCTION. This clause was
superseded by SQL-standard syntax back in 7.3. We've kept it around for
backwards-compatibility purposes ever since; but 15 years seems like long
enough for that, especially seeing that there are undocumented weirdnesses in
how it interacts with the SQL-standard syntax for specifying the same options.
Michael Paquier, per an observation by Daniel Gustafsson; some small cosmetic
adjustments to nearby code by me. Discussion:
https://postgr.es/m/20180115022748.GB1724@paquier.xyz
https://git.postgresql.org/pg/commitdiff/4971d2a32209118ebbdc6611341b89901e340902

- Avoid unnecessary use of pg_strcasecmp for already-downcased identifiers. We
have a lot of code in which option names, which from the user's viewpoint are
logically keywords, are passed through the grammar as plain identifiers, and
then matched to string literals during command execution. This approach
avoids making words into lexer keywords unnecessarily. Some places matched
these strings using plain strcmp, some using pg_strcasecmp. But the latter
should be unnecessary since identifiers would have been downcased on their way
through the parser. Aside from any efficiency concerns (probably not a big
factor), the lack of consistency in this area creates a hazard of subtle bugs
due to different places coming to different conclusions about whether two
option names are the same or different. Hence, standardize on using strcmp()
to match any option names that are expected to have been fed through the
parser. This does create a user-visible behavioral change, which is that
while formerly all of these would work: alter table foo set (fillfactor = 50);
alter table foo set (FillFactor = 50); alter table foo set ("fillfactor" =
50); alter table foo set ("FillFactor" = 50); now the last case will fail
because that double-quoted identifier is different from the others. However,
none of our documentation says that you can use a quoted identifier in such
contexts at all, and we should discourage doing so since it would break if we
ever decide to parse such constructs as true lexer keywords rather than poor
man's substitutes. So this shouldn't create a significant compatibility issue
for users. Daniel Gustafsson, reviewed by Michael Paquier, small changes by
me Discussion:
https://postgr.es/m/29405B24-564E-476B-98C0-677A29805B84@yesql.se
https://git.postgresql.org/pg/commitdiff/fb8697b31aaeebe6170c572739867dcaa01053c6

- Avoid crash during EvalPlanQual recheck of an inner indexscan. Commit
09529a70b changed nodeIndexscan.c and nodeIndexonlyscan.c to postpone
initialization of the indexscan proper until the first tuple fetch. It
overlooked the question of mark/restore behavior, which means that if some
caller attempts to mark the scan before the first tuple fetch, you get a null
pointer dereference. The only existing user of mark/restore is
nodeMergejoin.c, which (somewhat accidentally) will never attempt to set a
mark before the first inner tuple unless the inner child node is a Material
node. Hence the case can't arise normally, so it seems sufficient to document
the assumption at both ends. However, during an EvalPlanQual recheck,
ExecScanFetch doesn't call IndexNext but just returns the jammed-in test
tuple. Therefore, if we're doing a recheck in a plan tree with a mergejoin
with inner indexscan, it's possible to reach ExecIndexMarkPos with
iss_ScanDesc still null, as reported by Guo Xiang Tan in bug #15032. Really,
when there's a test tuple supplied during an EPQ recheck, touching the index
at all is the wrong thing: rather, the behavior of mark/restore ought to
amount to saving and restoring the es_epqScanDone flag. We can avoid finding
a place to actually save the flag, for the moment, because given the
assumption that no caller will set a mark before fetching a tuple,
es_epqScanDone must always be set by the time we try to mark. So the actual
behavior change required is just to not reach the index access if a test tuple
is supplied. The set of plan node types that need to consider this issue are
those that support EPQ test tuples (i.e., call ExecScan()) and also support
mark/restore; which is to say, IndexScan, IndexOnlyScan, and perhaps
CustomScan. It's tempting to try to fix the problem in one place by teaching
ExecMarkPos() itself about EPQ; but ExecMarkPos supports some plan types that
aren't Scans, and also it seems risky to make assumptions about what a
CustomScan wants to do here. Also, the most likely future change here is to
decide that we do need to support marks placed before the first tuple, which
would require additional work in IndexScan and IndexOnlyScan in any case.
Hence, fix the EPQ issue in nodeIndexscan.c and nodeIndexonlyscan.c, accepting
the small amount of code duplicated thereby, and leave it to CustomScan
providers to fix this bug if they have it. Back-patch to v10 where commit
09529a70b came in. In earlier branches, the index_markpos() call is a waste
of cycles when EPQ is active, but no more than that, so it doesn't seem
appropriate to back-patch further. Discussion:
https://postgr.es/m/20180126074932.3098.97815@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/2e668c522e58854ae19b7fdc5ac23f9b8a1275f5

- Update time zone data files to tzdata release 2018c. DST law changes in
Brazil, Sao Tome and Principe. Historical corrections for Bolivia, Japan, and
South Sudan. The "US/Pacific-New" zone has been removed (it was only a link
to America/Los_Angeles anyway).
https://git.postgresql.org/pg/commitdiff/41fc04ff913de6e0ffdbffff25298b39cd4ba42d

- Add stack-overflow guards in set-operation planning. create_plan_recurse
lacked any stack depth check. This is not per our normal coding rules, but
I'd supposed it was safe because earlier planner processing is more complex
and presumably should eat more stack. But bug #15033 from Andrew Grossman
shows this isn't true, at least not for queries having the form of a
many-thousand-way INTERSECT stack. Further testing showed that
recurse_set_operations is also capable of being crashed in this way, since it
likewise will recurse to the bottom of a parsetree before calling any support
functions that might themselves contain any stack checks. However, its stack
consumption is only perhaps a third of create_plan_recurse's. It's possible
that this particular problem with create_plan_recurse can only manifest in 9.6
and later, since before that we didn't build a Path tree for set operations.
But having seen this example, I now have no faith in the proposition that
create_plan_recurse doesn't need a stack check, so back-patch to all supported
branches. Discussion:
https://postgr.es/m/20180127050845.28812.58244@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/35a528062cc8ccdb51bde6c672991ae64e970847

Bruce Momjian pushed:

- doc: simplify intermediate certificate mention in libpq docs.
Backpatch-through: 9.3
https://git.postgresql.org/pg/commitdiff/a541dbb6fa389bb0ffdd24a403bc6d276d77a074

- doc: mention psql -l uses the 'postgres' database by default. Reported-by:
Mark Wood Bug: 14912 Discussion:
https://postgr.es/m/20171116171735.1474.30450@wrigleys.postgresql.org Author:
David G. Johnston Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/e0a0deca389849383ff6337a488300eb22f31cef

- doc: clarify use of RegisterDynamicBackgroundWorker. Document likely use of
RegisterDynamicBackgroundWorker by another background worker. Reported-by:
Chapman Flack Discussion:
https://postgr.es/m/CAB7nPqTdi=J9HH8PPPiEOohebdd+xkgbbhdY7=VbGnZ3CkZXxA@mail.gmail.com
Author: Chapman Flack
https://git.postgresql.org/pg/commitdiff/5b2a8cf96f6fa4f2c98c9a4c32a5a387b4f69d6c

- doc: properly indent CREATE TRIGGER paragraph. This was done to match the
surrounding indentation. Text added in PG 10. Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/d6ab7203607a3f43fe41d384f46c15bdac68d745

- Fix C comment typo. Reported-by: Masahiko Sawada Discussion:
https://postgr.es/m/CAD21AoBgnHy2YKAUuB6iVG4ibvLYepHr+RDRkr1arqWwc1AHCw@mail.gmail.com
Author: Masahiko Sawada
https://git.postgresql.org/pg/commitdiff/6588a43bcacca872fafba10363d346b806964d90

- C includes: Reorder C includes in partition.c. Discussion:
https://postgr.es/m/5A69AA50.2060600@lab.ntt.co.jp Author: Etsuro Fujita
https://git.postgresql.org/pg/commitdiff/010123e144a5a5d395a15067f301a2c2443f49cf

Robert Haas pushed:

- Report an ERROR if a parallel worker fails to start properly. Commit
28724fd90d2f85a0573a8107b48abad062a86d83 fixed things so that if a background
worker fails to start due to fork() failure or because it is terminated before
startup succeeds, BGWH_STOPPED will be reported. However, that only helps if
the code that uses the background worker machinery notices the change in
status, and the code in parallel.c did not. To fix that, do two things.
First, make sure that when a worker exits, it triggers the leader to read from
error queues. That way, if a worker which has attached to an error queue
exits uncleanly, the leader is sure to throw some error, either the contents
of the ErrorResponse sent by the worker, or "lost connection to parallel
worker" if it exited without sending one. To cover the case where the worker
never starts up in the first place or exits before attaching to the error
queue, the ParallelContext now keeps track of which workers have sent at least
one message via the error queue. A worker which sends no messages by the time
the parallel operation finishes will be checked to see whether it exited
before attaching to the error queue; if so, a new error message, "parallel
worker failed to initialize", will be reported. If not, we'll continue to
wait until it either starts up and exits cleanly, starts up and exits
uncleanly, or fails to start, and then take the appropriate action. Patch by
me, reviewed by Amit Kapila. Discussion:
http://postgr.es/m/CA+TgmoYnBgXgdTu6wk5YPdWhmgabYc9nY_pFLq=tB=FSLYkD8Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2badb5afb89cd569500ef7c3b23c7a9d11718f2f

- Update obsolete sentence in README.parallel. Since 9.6, heavyweight locking
is not an abstract and unhandled concern of the parallel machinery, but rather
something to which we have a specific approach.
https://git.postgresql.org/pg/commitdiff/28e04155f17cabda7a18aee31d130aa10e25ee86

- Avoid referencing off the end of subplan_partition_offsets. Report by
buildfarm member skink and Tom Lane. Analysis by me. Patch by Amit
Khandekar. Discussion:
http://postgr.es/m/CAJ3gD9fVA1iXQYhfqHP5n_TEd4U9=V8TL_cc-oKRnRmxgdvJrQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/945f71db845262e7491b5fe4403b01147027576b

- pageinspect: Fix use of wrong memory context by hash_page_items. This can
cause it to produce incorrect output. Report and patch by Masahiko Sawada.
Discussion:
http://postgr.es/m/CAD21AoBc5Asx7pXdUWu6NqU_g=Ysn95EGL9SMeYhLLduYoO_OA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b0313f9cc8f54d6a5c12f8987c9b6afa0a5bbced

- Factor some code out of create_grouping_paths. This is preparatory
refactoring to prepare the way for partition-wise aggregate, which will reuse
the new subroutines for child grouping rels. It also does not seem like a bad
idea on general principle, as the function was getting pretty long. Jeevan
Chalke. The larger patch series of which this patch is a part was reviewed
and tested by Antonin Houska, Rajkumar Raghuwanshi, Ashutosh Bapat, David
Rowley, Dilip Kumar, Konstantin Knizhnik, Pascal Legrand, and me. Some
cosmetic changes by me. Discussion:
http://postgr.es/m/CAM2+6=V64_xhstVHie0Rz=KPEQnLJMZt_e314P0jaT_oJ9MR8A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/9fd8b7d632570af90a0b374816f604f59bba11ad

Álvaro Herrera pushed:

- Remove unnecessary include. autovacuum.c no longer needs dsa.h, since commit
31ae1638ce3. Author: Masahiko Sawada Discussion:
https://postgr.es/m/CAD21AoCWvYyXrvdANSHWWWEWJH5TeAWAkJ_2gqrHhukG+OBo1g@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/95be5ce1bce3fdcf3ca0638baa12508e5b67ec17

- Ignore partitioned indexes where appropriate. get_relation_info() was too
optimistic about opening indexes in partitioned tables, which would raise
errors when any queries were planned on such tables. Fix by ignoring any
indexes of the partitioned kind. CLUSTER (and ALTER TABLE CLUSTER ON) had a
similar problem. Fix by disallowing these commands in partitioned tables.
Fallout from 8b08f7d4820f.
https://git.postgresql.org/pg/commitdiff/05fb5d661925f00106373f1a594be5aca24d9a94

== Pending Patches ==

Emre Hasegeli sent in another revision of a patch to improve geometric types.

Mark Rofail sent in another revision of a patch to implement foreign key arrays.

Nikolay Shaplov sent in a patch to add an enum relation option type.

John Naylor sent in a patch to add a standard error test for MCV lists.

Nikolay Shaplov sent in a patch to fix reloptions tests.

Michaël Paquier sent in a patch to handle better-supporting channel binding
types for SSL implementations.

Thomas Munro sent in a patch to halve the size of the test tables used for
parallel hash joins.

Álvaro Herrera sent in another revision of a patch to allow indexes on
partitioned tables to be unique.

Daniel Gustafsson sent in four more revisions of a patch to support Secure
Transport SSL library on macOS as OpenSSL alternative.

Maksim Milyutin sent in another revision of a patch to create a custom signal
handler for extensions.

Dmitry Dolgov sent in two more revisions of a patch to implement generic type
subscripting and use same for arrays and JSONB.

Doug Rady sent in five more revisions of a patch to add a pgbench option to
build using ppoll() for larger connection counts.

Konstantin Knizhnik sent in another revision of a patch to implement a built-in
connection pooler.

Doug Rady sent in another revision of a patch to pgbench to break out timing
data for initialization phases.

Thomas Munro and Amit Langote traded patches to add a column_constraint
description to the ALTER TABLE synopsis.

Haribabu Kommi sent in another revision of a patch to add two new libpq API's:
PQhostNoDefault, which is similar to PQhost, but it doesn't return the default
host name, and PQhostaddr, also similar to PQhost but returns the host address
of the connection or NULL as appropriate and use same to modify
pg_stat_wal_receiver to display the connected host.

Marco Nenciarini sent in another revision of a patch to suppress foreign key
references check in TRUNCATE if the session replication role is set to REPLICA.

Pavel Stěhule sent in another revision of a patch to enable forcing either
generic or custom plans for prepared queries.

Sergei Kornilov sent in another revision of a patch to make it possible to avoid
doing a full table scan when adding a NOT NULL constraint to a column by
phrasing it as a CHECK constraint.

Vik Fearing sent in a patch to psql to make tab completion work for FROM ONLY.

Jing Wang sent in another revision of a patch to libpq to support "prefer_read"
in connections.

David G. Johnston sent in a documentation patch to clarify the fact that
multidimensional arrays are not arrays of arrays and spell out some of the
consequences of that.

Etsuro Fujita sent in a patch to fix some comments in partition.c.

Álvaro Herrera sent in another revision of a patch to allow FOR EACH ROW
triggers on partitioned tables.

Masahiko Sawada sent in a patch to ensure that an autovacuum work-item doesn't
fail silently when the work-item array is full.

David Rowley and Amit Langote traded patches to prune partitions faster.

Simon Riggs sent in two more revisions of a patch to implement MERGE.

Ildus Kurbangaliev sent in two more revisions of a patch to implement custom
compression methods.

Marco Nenciarini sent in three more revisions of a patch to implement logical
decoding of TRUNCATE.

Kyotaro HORIGUCHI sent in a patch to add a regression test for the failure of
check_index_only.

Pavel Stěhule sent in another revision of a patch to implement default
namespaces for XPath expressions.

Fabien COELHO sent in another revision of a patch to add a --random-seed option
to pgbench.

Tom Lane sent in another revision of a patch to use dtype rec for all
composites.

Thomas Munro sent in a patch to make inject a random fork() failure into
postmaster.c for testing.

Thomas Munro sent in a patch to detect failures of fork().

Amit Langote sent in two more revisions of a patch to help speed up tuple
routing for non-bulk inserts into partitioned tables.

Álvaro Herrera sent in another revision of a patch to implement foreign keys on
partitioned tables.

Artur Zakirov sent in two more revisions of a patch to implement shared Ispell
dictionaries.

Álvaro Herrera sent in a patch to ignore partitioned indexes in
get_relation_info.

Daniel Gustafsson sent in two more revisions of a patch to implement an optional
message to the user when terminating/cancelling a backend.

Amul Sul sent in another revision of a patch to mark the ip_blkid in the tuple
header InvalidBlockNumber when a tuple is being moved to another partition and
add isolation tests.

David Steele sent in two more revisions of a patch to enable excluding unlogged
tables from base backups.

Thomas Munro sent in two more revisions of a patch to inject failures fork().

Pierre Ducroquet sent in a patch atop the LLVM JIT patch set which lets people
use LLVM 5.0.

Jing Wang sent in another revision of a patch to implement COMMENT ON DATABASE
CURRENT_DATABASE.

David Rowley sent in another revision of a patch to remove [Merge]Append nodes
which contain a single subpath.

Shubham Barai sent in another revision of a patch to implement predicate locking
for GiST indexes.

Thomas Munro sent in another revision of a patch to make SERIALIZABLE isolation
work with parallel query.

Robert Haas sent in another revision of a patch to speed up processing at Gather
nodes.

Tom Lane sent in another revision of a patch to add CONSTANT/NOT
NULL/initializer properties for plpgsql record variables.

Tom Lane sent in another revision of a patch to speed up PL/pgsql startup.

Álvaro Herrera sent in two revisions of a patch to speed up isolation tests.

Anastasia Lubennikova sent in another revision of a patch to implement
covering/unique indexes.

Ildar Musin sent in two more revisions of a patch to add a general purpose
hashing function to pgbench.

Amit Langote and Etsuro Fujita traded patches to change how list partition
constraint is emitted.

Masahiko Sawada sent in a patch to fix hash page items.

Andrew Dunstan sent in another revision of a patch to make ALTER TABLE ADD
COLUMN with a DEFAULT faster.

Edmund Horner sent in another revision of a patch to add psql tab completion for
SELECT.

Michaël Paquier sent in a patch to rewrite the test of pg_upgrade as a TAP test.

Aleksandr Parfenov sent in a patch to enable configuring languages on Windows.

Peter Eisentraut sent in another revision of a patch to implement generated
columns.

David Rowley sent in two revisions of a patch to enable CREATE TABLE ... LIKE
... to control whether STATISTICS is included.

Oliver Ford and Erik Rijkers traded patches to add RANGE with values and
exclusions clauses in windowing functions.

Dmitry Dolgov sent in a patch to add write lifetime hints for NVMe.

Amit Kapila sent in a patch to wait for parallel workers to attach.

Thomas Munro sent in a patch to document pgcrypto AES key sizes.

Bruce Momjian sent in another revision of a patch to implement quit and exit
commands in psql.