Postgres 11 release notes

Hello Bruce,

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Here is some, for things I know about:

Add major scripting features to pgbench (Fabien Coelho)

I assume that you are refering to "bc7fa0c1". I do not think that anything
qualifies as "major".

I would rather describe it as "Add support for NULL and boolean, as well
as assorted operators and functions, to pgbench expressions". Or something
in better English.

Add \if macro support to pgbench (Fabien Coelho)

I would remove the word "macro", as it is not a pre-compilation feature,
it is just a somehow simple standard conditional.

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics.

I'm planning to try to help with the later.

--
Fabien.

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup of
B-tree indexes when possible
c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb to
all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries matching
JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in json(b)
(Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

Thank you!

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

On 05/11/2018 11:08 AM, Bruce Momjian wrote:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Very superficial things:

Add predicate locking for Hash, GiST, and GIN indexes

s/likelyhood/likelihood/

Add extension jsonb_plpython

There are two such entries; one looks like it should be plperl.

Improve the speed of aggregate computations

This entry is missing a bullet.

-Chap

On Fri, May 11, 2018 at 06:29:39PM +0200, Fabien COELHO wrote:

Hello Bruce,

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Here is some, for things I know about:

Add major scripting features to pgbench (Fabien Coelho)

I assume that you are refering to "bc7fa0c1". I do not think that anything
qualifies as "major".

I would rather describe it as "Add support for NULL and boolean, as well as
assorted operators and functions, to pgbench expressions". Or something in
better English.

OK, new wording is:

Add pgbench expressions support for NULLs, booleans, and some
functions and operators (Fabien Coelho)

Add \if macro support to pgbench (Fabien Coelho)

I would remove the word "macro", as it is not a pre-compilation feature, it
is just a somehow simple standard conditional.

New wording is:

Add \if conditional support to pgbench (Fabien Coelho)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics.

I'm planning to try to help with the later.

Great, thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb
to all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries
matching JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in
json(b) (Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

OK, how is this text?

Add function <function>json(b)_to_tsvector()</function> to create
text search query for matching <type>JSON</type>/<type>JSONB
</type>values (Dmitry Dolgov)

I have made this change but can adjust it some more.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 01:40:54PM -0400, Chapman Flack wrote:

On 05/11/2018 11:08 AM, Bruce Momjian wrote:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Very superficial things:

Add predicate locking for Hash, GiST, and GIN indexes

s/likelyhood/likelihood/

Add extension jsonb_plpython

There are two such entries; one looks like it should be plperl.

Improve the speed of aggregate computations

This entry is missing a bullet.

Great, all fixed, and the URL contents are updated with this and
previous suggestions, plus more markup.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 11:50:51AM -0700, Andres Freund wrote:

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

You can see why I was hesitant to include it, based on this text, but I
am happy to add it.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi,

Quick notes:

<para>
Add Just-In-Time (<acronym>JIT</acronym>) compilation of plans
run the by the executor
(Andres Freund)
</para>
</listitem>

It's currently not yet compilation of entire plans, but only parts. I
think that's a relevant distinction because I'd like to add that as a
feature to v12 ;). How about just adding 'parts of' or such? I'd also
rephrase the plan and 'run by the executor a bit'. How about:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of parts of
queries, to accelerate their execution.

<listitem>
<!--
2018-03-20 [5b2526c83] Add configure infrastructure (- -with-llvm) to enable LLV
-->

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
</listitem>

<listitem>
<!--
2018-03-20 [6869b4f25] Add C++ support to configure.
-->

<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>
</listitem>

<listitem>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

Greetings,

Andres Freund

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Andres Freund wrote:

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

I don't see why users would be interested in these items at all, so +1
for omitting them.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-05-11 14:59:04 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 11:50:51AM -0700, Andres Freund wrote:

On 2018-05-11 14:44:06 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 07:49:50PM +0300, Teodor Sigaev wrote:

Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Hi!

Seems, you miss:
857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

It's a *huge* performance problem in larger installations
currently. When you have a multi-TB relation and correspondingly large
relation, the VM allows to make the heap cleanups cheap, but then the
index scan takes just about forever. I know at least one large PG user
that moved off postgres because of it. This won't solve all of those
concerns, but it definitely is crucial to know for such users.

People would notice by vacuums of large relations not taking forever
anymore. And the behaviour change would be to a) upgrade b) tune the
associated reloption/GUC.

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

I don't think the table being 'append-only' is necessary? Nor does it
have to be a manual vacuum. And 'needless index scan' sounds less than
it is/was, namely a full scan of the index. Perhaps something like:

Allow vacuum to skip doing a full scan of btree indexes after VACUUM,
if not necessary.

or something like that?

You can see why I was hesitant to include it, based on this text, but I
am happy to add it.

I can't. Even if the above were accurate it'd be relevant information.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 04:00:58PM -0300, Alvaro Herrera wrote:

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

Uh, from the commit it says:

When workload on particular table is append-only, then autovacuum
isn't intended to touch this table. However, user may run vacuum
manually in order to fill visibility map and get benefits of
--------
index-only scans. Then ambulkdelete wouldn't be called for
indexes of such table (because no heap tuples were deleted), only
---------------------------
amvacuumcleanup would be called In this case, amvacuumcleanup
would perform full index scan for two objectives: put recyclable
pages into free space map and update index statistics.

Why would autovacuum run on a table with no expired index entries?

Personally, I think the fact that autovacuum doesn't run on suvch tables
and therefore doesn't automatically do index-only scans is a problem. I
tried to fix it years ago, but failed and gave up.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 11:59:12AM -0700, Andres Freund wrote:

Hi,

Quick notes:

<para>
Add Just-In-Time (<acronym>JIT</acronym>) compilation of plans
run the by the executor
(Andres Freund)
</para>
</listitem>

It's currently not yet compilation of entire plans, but only parts. I
think that's a relevant distinction because I'd like to add that as a
feature to v12 ;). How about just adding 'parts of' or such? I'd also
rephrase the plan and 'run by the executor a bit'. How about:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of parts of
queries, to accelerate their execution.

OK, new text:

Add Just-In-Time (<acronym>JIT</acronym>) compilation of some
parts of query plans to improve execution speed (Andres Freund)

<listitem>
<!--
2018-03-20 [5b2526c83] Add configure infrastructure (- -with-llvm) to enable LLV
-->

<para>
Add configure flag <option>--with-llvm</option> to test for
<acronym>LLVM</acronym> support (Andres Freund)
</para>
</listitem>

<listitem>
<!--
2018-03-20 [6869b4f25] Add C++ support to configure.
-->

<para>
Have configure check for the availability of a C++ compiler
(Andres Freund)
</para>
</listitem>

<listitem>

I wonder if we shouldn't omit those, considering them part of the JIT
entry? Not quite sure what our policy there is.

OK, removed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 12:04 PM, Andres Freund <andres@anarazel.de> wrote:

I don't think the table being 'append-only' is necessary? Nor does it
have to be a manual vacuum. And 'needless index scan' sounds less than
it is/was, namely a full scan of the index. Perhaps something like:

Allow vacuum to skip doing a full scan of btree indexes after VACUUM,
if not necessary.

or something like that?

I suggest "Allow vacuuming to avoid full index scans for indexes when
there are no dead tuples found in a table. Where necessary, the
behavior can be adjusted via the new configuration parameter
vacuum_cleanup_index_scale_factor."

Also:

* "Allow indexes to be built in parallel" should specify that it only
works for B-Tree index builds.

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

--
Peter Geoghegan

857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

Hm, it could be something like:
Add possibility to miss scan indexes on append-only table, it decreases
vacuum time on such tables.

c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb
to all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries
matching JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in
json(b) (Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

OK, how is this text?

Add function <function>json(b)_to_tsvector()</function> to create
text search query for matching <type>JSON</type>/<type>JSONB
</type>values (Dmitry Dolgov)

Not query - *_to_tsvector functions produce a tsvector. So:

Add function <function>json(b)_to_tsvector()</function> to use
customizable full text search over json(b) columns.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

On 2018-05-11 15:11:31 -0400, Bruce Momjian wrote:

On Fri, May 11, 2018 at 04:00:58PM -0300, Alvaro Herrera wrote:

Bruce Momjian wrote:

OK, so what is the text that people will understand? This?

Prevent manual VACUUMs on append-only tables from performing
needless index scans

Make vacuum cheaper by avoiding scans of btree indexes when not
necessary
?

Why "manual vacuum"? It's a problem with vacuums invoked from
autovacuum too.

Uh, from the commit it says:

When workload on particular table is append-only, then autovacuum
isn't intended to touch this table. However, user may run vacuum
manually in order to fill visibility map and get benefits of
--------
index-only scans. Then ambulkdelete wouldn't be called for
indexes of such table (because no heap tuples were deleted), only
---------------------------
amvacuumcleanup would be called In this case, amvacuumcleanup
would perform full index scan for two objectives: put recyclable
pages into free space map and update index statistics.

Why would autovacuum run on a table with no expired index entries?

Anti-Wraparound is one case. One where it's really painful to take
forever on lots of unchanged tables. Lots of hot updates another.

Btw, is it just me, or do the commit and docs confuse say stalled when
stale is intended?

Personally, I think the fact that autovacuum doesn't run on suvch tables
and therefore doesn't automatically do index-only scans is a problem. I
tried to fix it years ago, but failed and gave up.

I'm really unhappy about that too.

Greetings,

Andres Freund

On Fri, May 11, 2018 at 12:22:08PM -0700, Andres Freund wrote:

Btw, is it just me, or do the commit and docs confuse say stalled when
stale is intended?

Should be fixed since yesterday's 8e12f4a250d250a89153da2eb9b91c31bb80c483 ?

Justin

On Fri, May 11, 2018 at 12:17 PM, Peter Geoghegan <pg@bowt.ie> wrote:

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

Also, it should be moved to "Migration to Version 11", since the only
issue here is compatibility with older versions.

--
Peter Geoghegan

On Fri, May 11, 2018 at 12:17:47PM -0700, Peter Geoghegan wrote:

On Fri, May 11, 2018 at 12:04 PM, Andres Freund <andres@anarazel.de> wrote:

I don't think the table being 'append-only' is necessary? Nor does it
have to be a manual vacuum. And 'needless index scan' sounds less than
it is/was, namely a full scan of the index. Perhaps something like:

Allow vacuum to skip doing a full scan of btree indexes after VACUUM,
if not necessary.

or something like that?

I suggest "Allow vacuuming to avoid full index scans for indexes when
there are no dead tuples found in a table. Where necessary, the
behavior can be adjusted via the new configuration parameter
vacuum_cleanup_index_scale_factor."

Also:

* "Allow indexes to be built in parallel" should specify that it only
works for B-Tree index builds.

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

All done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 12:28:57PM -0700, Peter Geoghegan wrote:

On Fri, May 11, 2018 at 12:17 PM, Peter Geoghegan <pg@bowt.ie> wrote:

* Suggest replacement sort item be phrased as: "Remove the
configuration parameter replacement_sort_tuples. <newline> The
replacement selection sort algorithm is no longer used."

Also, it should be moved to "Migration to Version 11", since the only
issue here is compatibility with older versions.

Done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 10:20:17PM +0300, Teodor Sigaev wrote:

857f9c36cda520030381bd8c2af20adf0ce0e1d4 Skip full index scan during cleanup
of B-tree indexes when possible

I read that and thought it was too details to be in the release notes.
It is not that it is unimportant, but it is hard to see how people would
notice the difference or change their behavior based on this change.

Hm, it could be something like:
Add possibility to miss scan indexes on append-only table, it decreases
vacuum time on such tables.

c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc Add explicit cast from scalar jsonb
to all numeric and bool types.

Pls, edit:
Add functionjson(b)_to_tsvector to create usable text search queries
matching JSON/JSONB values (Dmitry Dolgov)
to
Add function json(b)_to_tsvector to create usable vectors to search in
json(b) (Dmitry Dolgov)
or somehow else. Your wording is about query but actually that functions are
about how to create tsvector from json(b)

OK, how is this text?

Add function <function>json(b)_to_tsvector()</function> to create
text search query for matching <type>JSON</type>/<type>JSONB
</type>values (Dmitry Dolgov)

Not query - *_to_tsvector functions produce a tsvector. So:

Add function <function>json(b)_to_tsvector()</function> to use customizable
full text search over json(b) columns.

Done and URL updated.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 2:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

Done and URL updated.

I have some feedback on "Allow NOT NULL to be added to columns without
requiring a table rewrite". I suggest that this be phrased as "Avoid a
table rewrite when ALTER TABLE ADD COLUMN sets a default value for a
column". Referencing the fact that Postgres was previously able to do
this with a NULL default doesn't seem to add anything.

--
Peter Geoghegan

On Sat, May 12, 2018 at 01:22:55PM -0700, Peter Geoghegan wrote:

On Fri, May 11, 2018 at 2:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

Done and URL updated.

I have some feedback on "Allow NOT NULL to be added to columns without
requiring a table rewrite". I suggest that this be phrased as "Avoid a
table rewrite when ALTER TABLE ADD COLUMN sets a default value for a
column". Referencing the fact that Postgres was previously able to do
this with a NULL default doesn't seem to add anything.

Agreed, done:

Allow <command>ALTER TABLE</command> to add a non-null default
column without a table rewrite (Andrew Dunstan, Serge Rielau)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

<para>
Add information_schema columns related to table constraints and
triggers (Michael Paquier)
</para>
The author of this entry is Peter Eisentraut, not me.

<para>
Channel binding requires the server end
of the <acronym>TLS</acronym> connection to
prove that it knows the password. The options are <link
linkend="libpq-scram-channel-binding"><option>scram_channel_binding=tls-unique</option></link>
and <option>scram_channel_binding=tls-server-end-point</option>.
</para>
This is not actually correct. Channel binding is an MITM prevention
mechanism which makes sure that after the SSL handshake the backend and
the frontend are still connected to the same things. "tls-unique" makes
sure that a connection is uniquely used using a hash of the TLS finish
message, and end-point makes sure that the endpoints are the same using
a hash of the server certificate.

<para>
WHAT DOES THIS DOC TEXT MEAN? "An empty value specifies that
the client will not use channel binding. The default value
is tls-unique."
</para>
This means that the client can choose to not use channel binding (which
sends a 'n' flag if you refer to the communication protocol of SCRAM),
even if the server has advertised to the client channel binding. So
this provides a way to disable the feature at will, an on/off switch if
you want. If a v10 libpq tries to connect to a v11 server, then it
won't use channel binding automatically. That may be worth adding to
the documentation as well.

<para>
Allow access to file system functions to be controlled by
<command>GRANT</command>/<command>REVOKE</command> permissions,
rather than super-user checks (Michael Paquier)
</para>
Author is Stephen Frost here.

<para>
Use <command>GRANT</command>/<command>REVOKE</command>
to control access to <link
linkend="lo-import"><function>lo_import()</function></link>
and <function>lo_export()</function> (Michael Paquier)
</para>
Tom Lane is a co-author here I think.

<para>
Add libpq parameter to allow physical and logical replication
connections (Michael Paquier)
</para>
This commit has just added documentation which was missing and
incomplete. I would suggest to remove it from the release notes as no
new feature has been added.

<para>
Add <link
linkend="app-pgreceivewal"><application>pg_receivewal</application></link>
option <option>--no-sync</option> to prevent synchronous
<acronym>WAL</acronym> writes (Michael Paquier)
</para>
Perhaps this should be rewritten? --no-sync just disables any fsync
calls for WAL segments, which is useful for tests, not recommended for
production environments.

<para>
Prevent <application>pg_rewind</application> from running as
<literal>root</literal> (Magnus Hagander)
</para>
This one's authorship is actually mine, after a bug I found :)
--
Michael

Hello Bruce,

Thanks for the greate work!

2018-05-12 0:08 GMT+09:00 Bruce Momjian <bruce@momjian.us>:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I think the behavior change (from PostgreSQL 11) of power() with NaN below
also need to report to users as the major change.

related commit: 61b200e2f582d0886d9de947e182483339d881fd
6bdf1303b34bc630e8945ae3407ec7e8395c8fe5

Discussion:
/messages/by-id/75DB81BEEA95B445AE6D576A0A5C9E936A73E741@BPXM05GP.gisp.nec.co.jp

Thanks and best regards,
---
Dang Minh Huong

On Sat, May 12, 2018 at 12:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

Thank you for compiling this, Bruce!

I expect a torrent of feedback. ;-)

The following item

+<!--
+2017-11-23 [05b6ec39d] Show partition info from psql \d+
+-->
+
+       <para>
+        Have psql \d+ show a partition count of zero (Amit Langote)
+       </para>

missed mentioning Ashutosh Bapat as an author.

Maybe, the item name and description text could be improved as follows:

+       <para>
+        Have psql \d+ always show the partition information (Amit
Langote, Ashutosh Bapat)
+       </para>
+
+       <para>
+        Previously partition information would not be displayed for a
partitioned table if
+        it had no partitions.  Also indicate which partitions are
themselves partitioned.
+       </para>

Thanks,
Amit

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Thanks for compiling the first draft. I think there are few things
which we should add to the Parallel Queries section. (a) commit
e89a71fb449af2ef74f47be1175f99956cf21524 -
Pass InitPlan values to workers via Gather (Merge). We can write
something like "Allow the part of the plan that references an initplan
to run in parallel" or "Parallelise queries containing initplans",
(b) "Parallelise queries that contains expensive functions in target
lists" (Commits - 3f90ec8597c3515e0d3190613b31491686027e4b and
11cf92f6e2e13c0a6e3f98be3e629e6bd90b74d5).

Both of these features can parallelize queries in many workloads. The
feature (a) has sped up some of the TPC-H queries especially the cases
where InitPlan itself contains Gather node and feature (b) is also
quite helpful with PostGIS functions, see
https://carto.com/blog/inside/postgres-parallel/

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sun, May 13, 2018 at 4:40 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Thanks for compiling the first draft.

One more comment on below item:

Allow entire hash index pages to be scanned (Ashutosh Sharma)

Previously each hash index entry has to be locked and scanned separately.

I think it is better to write the second line as "Previously for each
hash index entry, we need to refind the scan position within the page
and cuts down on lock/unlock traffic.".

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sun, May 13, 2018 at 03:43:08PM +0900, Michael Paquier wrote:

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

A couple of extra notes while reading the HTML notes:

<para>
Add timeline information to the <link
linkend="backup-lowlevel-base-backup"><filename>backup_label</filename></link>
file (Simon Riggs)
</para>
This feature has been implemented by me, not Simon.

<para>
Document that <filename>pg_internal.init</filename> files do not
need to be included in the base backup (David Steele)
</para>
Does not seem necessary to add that in the release notes..

<para>
Cause large object permission checks
to happen on large object open, <link
linkend="lo-open"><function>lo_open()</function></link>, not
read/write (Tom Lane)
</para>
Co-authoring with Tom on this one.
--
Michael

On 5/13/18 8:26 PM, Michael Paquier wrote:

On Sun, May 13, 2018 at 03:43:08PM +0900, Michael Paquier wrote:

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

<para>
Document that <filename>pg_internal.init</filename> files do not
need to be included in the base backup (David Steele)
</para>
Does not seem necessary to add that in the release notes..

I was actually just about to respond about this one. This patch
implements the feature in pg_basebackup as well as documenting that it
does not need to be backed up.

I think it should be combined with this item:

Exclude unlogged and temporary tables from streaming base backups

Sometime like:

Exclude unlogged and temporary tables and pg_internal.init from
streaming base backups

Thanks,
--
-David
david@pgmasters.net

On 05/12/2018 08:47 PM, Bruce Momjian wrote:

On Sat, May 12, 2018 at 01:22:55PM -0700, Peter Geoghegan wrote:

On Fri, May 11, 2018 at 2:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

Done and URL updated.

I have some feedback on "Allow NOT NULL to be added to columns without
requiring a table rewrite". I suggest that this be phrased as "Avoid a
table rewrite when ALTER TABLE ADD COLUMN sets a default value for a
column". Referencing the fact that Postgres was previously able to do
this with a NULL default doesn't seem to add anything.

Agreed, done:

Allow <command>ALTER TABLE</command> to add a non-null default
column without a table rewrite (Andrew Dunstan, Serge Rielau)

I think "a column with a non-null default" would be a bit clearer than
"a non-null default column".

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics. I hope to do better job during Postgres 12.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

I think the below commit is missed in the release notes?

5edc63bda68a77c4d38f0cbeae1c4271f9ef4100

Committer: Robert Haas <rhaas@postgresql.org> 2017-11-10 13:50:50

Account for the effect of lossy pages when costing bitmap scans.

Dilip Kumar, reviewed by Alexander Kumenkov, Amul Sul, and me.
Some final adjustments by me.

As part of this commit, we are also accounting for the lossy pages during
bitmap costing. This will consider
the effect of work_mem while selecting the bitmap heap scan path.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Sun, May 13, 2018 at 03:43:08PM +0900, Michael Paquier wrote:

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

<para>
Add information_schema columns related to table constraints and
triggers (Michael Paquier)
</para>
The author of this entry is Peter Eisentraut, not me.

Thanks, I got "Reviewed-by" and "Author" mixed up.

<para>
Channel binding requires the server end
of the <acronym>TLS</acronym> connection to
prove that it knows the password. The options are <link
linkend="libpq-scram-channel-binding"><option>scram_channel_binding=tls-unique</option></link>
and <option>scram_channel_binding=tls-server-end-point</option>.
</para>
This is not actually correct. Channel binding is an MITM prevention
mechanism which makes sure that after the SSL handshake the backend and
the frontend are still connected to the same things. "tls-unique" makes
sure that a connection is uniquely used using a hash of the TLS finish
message, and end-point makes sure that the endpoints are the same using
a hash of the server certificate.

So, channel binding has had me confused since I first heard about it. I
have done some research and reworded the commit with the attached first patch.

Also, I have created a second patch which actually explains the two
SCRAM channel binding options and how the work.

One question I do have is how do we prevent a fake server in the middle
from pretending it is a PG 10 server and therefore avoiding channel
binding protections? I don't see any channel binding options in
pg_hba.conf, and while libpq has options, they are explained with "This
parameter is mainly intended for protocol testing."

<para>
WHAT DOES THIS DOC TEXT MEAN? "An empty value specifies that
the client will not use channel binding. The default value
is tls-unique."
</para>
This means that the client can choose to not use channel binding (which
sends a 'n' flag if you refer to the communication protocol of SCRAM),
even if the server has advertised to the client channel binding. So
this provides a way to disable the feature at will, an on/off switch if
you want. If a v10 libpq tries to connect to a v11 server, then it
won't use channel binding automatically. That may be worth adding to
the documentation as well.

I have updated the docs in the second patch to explain this.

<para>
Allow access to file system functions to be controlled by
<command>GRANT</command>/<command>REVOKE</command> permissions,
rather than super-user checks (Michael Paquier)
</para>
Author is Stephen Frost here.

Done.

<para>
Use <command>GRANT</command>/<command>REVOKE</command>
to control access to <link
linkend="lo-import"><function>lo_import()</function></link>
and <function>lo_export()</function> (Michael Paquier)
</para>
Tom Lane is a co-author here I think.

Done.

<para>
Add libpq parameter to allow physical and logical replication
connections (Michael Paquier)
</para>
This commit has just added documentation which was missing and
incomplete. I would suggest to remove it from the release notes as no
new feature has been added.

Removed.

<para>
Add <link
linkend="app-pgreceivewal"><application>pg_receivewal</application></link>
option <option>--no-sync</option> to prevent synchronous
<acronym>WAL</acronym> writes (Michael Paquier)
</para>
Perhaps this should be rewritten? --no-sync just disables any fsync
calls for WAL segments, which is useful for tests, not recommended for
production environments.

Done.

<para>
Prevent <application>pg_rewind</application> from running as
<literal>root</literal> (Magnus Hagander)
</para>
This one's authorship is actually mine, after a bug I found :)

Done, thanks much.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 09:26:07AM +0900, Michael Paquier wrote:

On Sun, May 13, 2018 at 03:43:08PM +0900, Michael Paquier wrote:

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

A couple of extra notes while reading the HTML notes:

<para>
Add timeline information to the <link
linkend="backup-lowlevel-base-backup"><filename>backup_label</filename></link>
file (Simon Riggs)
</para>
This feature has been implemented by me, not Simon.

OK, fixed.

<para>
Document that <filename>pg_internal.init</filename> files do not
need to be included in the base backup (David Steele)
</para>
Does not seem necessary to add that in the release notes..

Uh, I am not sure how people would hear about this information if we
don't have it in the release notes.

<para>
Cause large object permission checks
to happen on large object open, <link
linkend="lo-open"><function>lo_open()</function></link>, not
read/write (Tom Lane)
</para>
Co-authoring with Tom on this one.

Fixed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 07:10:18AM -0400, David Steele wrote:

On 5/13/18 8:26 PM, Michael Paquier wrote:

On Sun, May 13, 2018 at 03:43:08PM +0900, Michael Paquier wrote:

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version
here:

Thanks for gathering all the commits in one piece, Bruce.

I expect a torrent of feedback. ;-)

I looked at the entries where my name shows up. Here is some feedback
with HEAD at 8c6227a2 (latest as of writing this message).

<para>
Document that <filename>pg_internal.init</filename> files do not
need to be included in the base backup (David Steele)
</para>
Does not seem necessary to add that in the release notes..

I was actually just about to respond about this one. This patch
implements the feature in pg_basebackup as well as documenting that it
does not need to be backed up.

I think it should be combined with this item:

Exclude unlogged and temporary tables from streaming base backups

Sometime like:

Exclude unlogged and temporary tables and pg_internal.init from
streaming base backups

Good idea, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sun, May 13, 2018 at 05:43:25PM +0900, Đặng Minh Hướng wrote:

Hello Bruce,

Thanks for the greate work!

2018-05-12 0:08 GMT+09:00 Bruce Momjian <bruce@momjian.us>:

I have committed the first draft of the Postgres 11 release notes.  I
will add more markup soon.  You can view the most current version here:

        http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback.  ;-)

I think the behavior change (from PostgreSQL 11) of power() with NaN below also
need to report to users as the major change.

related commit: 61b200e2f582d0886d9de947e182483339d881fd
                          6bdf1303b34bc630e8945ae3407ec7e8395c8fe5
                
Discussion: https://postgr.es/m/
75DB81BEEA95B445AE6D576A0A5C9E936A73E741@BPXM05GP.gisp.nec.co.jp

Wow, that is an interesting case. The patch was applied to head and
backbranches, (so I didn't see it) but three days later reverted in back
branches, and after my date cut-off:

commit f74d83b3034f830bd68489b4aba99a4dee29c565
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed May 2 17:32:40 2018 -0400

Revert back-branch changes in power()'s behavior for NaN inputs.

Per discussion, the value of fixing these bugs in the back branches
doesn't outweigh the downsides of changing corner-case behavior in
a minor release. Hence, revert commits 217d8f3a1 and 4d864de48 in
the v10 branch and the corresponding commits in 9.3-9.6.

Discussion: /messages/by-id/75DB81BEEA95B445AE6D576A0A5C9E936A73E741@BPXM05GP.gisp.nec.co.jp

Added:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sun, May 13, 2018 at 06:53:26PM +0900, Amit Langote wrote:

On Sat, May 12, 2018 at 12:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

Thank you for compiling this, Bruce!

I expect a torrent of feedback. ;-)

The following item

+<!--
+2017-11-23 [05b6ec39d] Show partition info from psql \d+
+-->
+
+       <para>
+        Have psql \d+ show a partition count of zero (Amit Langote)
+       </para>

missed mentioning Ashutosh Bapat as an author.

Added.

Maybe, the item name and description text could be improved as follows:

+       <para>
+        Have psql \d+ always show the partition information (Amit
Langote, Ashutosh Bapat)
+       </para>
+
+       <para>
+        Previously partition information would not be displayed for a
partitioned table if
+        it had no partitions.  Also indicate which partitions are
themselves partitioned.
+       </para>

I like it, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sun, May 13, 2018 at 04:40:19PM +0530, Amit Kapila wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Thanks for compiling the first draft. I think there are few things
which we should add to the Parallel Queries section. (a) commit
e89a71fb449af2ef74f47be1175f99956cf21524 -
Pass InitPlan values to workers via Gather (Merge). We can write
something like "Allow the part of the plan that references an initplan
to run in parallel" or "Parallelise queries containing initplans",

Uh, I need some explaination of what an initplan is and what type of
query this helps.

(b) "Parallelise queries that contains expensive functions in target
lists" (Commits - 3f90ec8597c3515e0d3190613b31491686027e4b and
11cf92f6e2e13c0a6e3f98be3e629e6bd90b74d5).

So we generate the entire query before the target list function calls,
the run another parallel run just for those function calls? Really? Do
we run each column in its own worker or do we split the result set into
parts and run those in parallel? How do we know, just the function call
costs? I can admit I never saw that coming.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sun, May 13, 2018 at 04:55:35PM +0530, Amit Kapila wrote:

On Sun, May 13, 2018 at 4:40 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Thanks for compiling the first draft.

One more comment on below item:

Allow entire hash index pages to be scanned (Ashutosh Sharma)

Previously each hash index entry has to be locked and scanned separately.

I think it is better to write the second line as "Previously for each
hash index entry, we need to refind the scan position within the page
and cuts down on lock/unlock traffic.".

Nice, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 07:47:54AM -0400, Andrew Dunstan wrote:

On 05/12/2018 08:47 PM, Bruce Momjian wrote:

On Sat, May 12, 2018 at 01:22:55PM -0700, Peter Geoghegan wrote:

On Fri, May 11, 2018 at 2:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

Done and URL updated.

I have some feedback on "Allow NOT NULL to be added to columns without
requiring a table rewrite". I suggest that this be phrased as "Avoid a
table rewrite when ALTER TABLE ADD COLUMN sets a default value for a
column". Referencing the fact that Postgres was previously able to do
this with a NULL default doesn't seem to add anything.

Agreed, done:

Allow <command>ALTER TABLE</command> to add a non-null default
column without a table rewrite (Andrew Dunstan, Serge Rielau)

I think "a column with a non-null default" would be a bit clearer than
"a non-null default column".

Yeah, I didn't like what I had either, so I changed it to what you
suggested, though it gives us an odd "with X, without Y" pattern:

Allow <command>ALTER TABLE</command> to add a column with
a non-null default without a table rewrite (Andrew Dunstan,
Serge Rielau)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 05:34:54PM +0530, Dilip Kumar wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes.� I
will add more markup soon.� You can view the most current version here:

� � � � http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback.� ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11:� parallel FDW access and improved multi-variate
statistics.� I hope to do better job during Postgres 12.

--
� Bruce Momjian� <bruce@momjian.us>� � � � http://momjian.us
� EnterpriseDB� � � � � � � � � � � � � � �http://enterprisedb.com

+ As you are, so once was I.� As I am, so you will be. +
+� � � � � � � � � � � Ancient Roman grave inscription +

I think the below commit is missed in the release notes?

5edc63bda68a77c4d38f0cbeae1c4271f9ef4100

Committer: Robert Haas <rhaas@postgresql.org>� 2017-11-10 13:50:50

� � Account for the effect of lossy pages when costing bitmap scans.
� ��
� � Dilip Kumar, reviewed by Alexander Kumenkov, Amul Sul, and me.
� � Some final adjustments by me.

As part of this commit, we are also accounting for the lossy pages during
bitmap costing.� This will consider
the effect of work_mem while selecting the bitmap heap scan path.

Uh, that seems very exotic to mention, sorry. Others have opinions?

I have updated the PG11 doc URL with all the changes so far.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 04:04:58PM -0400, Bruce Momjian wrote:

So, channel binding has had me confused since I first heard about it. I
have done some research and reworded the commit with the attached
first patch.

pg11.diff looks roughly fine to me.

Also, I have created a second patch which actually explains the two
SCRAM channel binding options and how the work.

+         The list of channel binding types supported by the server are
+         listed in <xref linkend="sasl-authentication"/>.  An empty value
+         specifies that the client will not use channel binding.  If this
+         parameter is not specified, <literal>tls-unique</literal> is used,
+         if supported by both server and client.

OK, that's simple enough for users, and we talk about the libpq
parameter here.

The second paragraph is also a nice addition. You really looked at this
stuff!

One question I do have is how do we prevent a fake server in the middle
from pretending it is a PG 10 server and therefore avoiding channel
binding protections? I don't see any channel binding options in
pg_hba.conf, and while libpq has options, they are explained with "This
parameter is mainly intended for protocol testing."

The answer is that you cannot do that now, as much as you cannot have a
client forbid connection attempt if the client requests SCRAM but the
server downgrades to MD5. I had a topic on the matter at an unconf
session at the last PGAsia, and except for administrators which forgot
to upgrade a set of servers that was not something worth complicating
the code for, at least that's the conclusion which came out of the
session. At the end, this is not actually something that you would
control from the server if you care about security, but something which
is controlled from the client. The limitations that we have know are
partially due to the way libpq handles the authentication protocol.
Hence if you want to prevent servers attempting to do downgrades, you
need options like sslmode saying those things from the client point of
view:
- I want SCRAM, but refuse connection request if server attempts MD5 or
something else.
- I want SCRAM and channel binding, but refuse connection request if
server does not advertise channel binding to the client.

There may be value to an server side parameter which forces clients to
use channel binding even if the server has advertized the channel
binding SASL mechanism, and even if connection is made with SSL, but
that's not a downgrade-attack prevention.
--
Michael

Hi Bruce,

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I could not find this:

$ git log -1 f8e5f15
commit f8e5f156b30efee5d0038b03e38735773abcb7ed
Author: Andres Freund <andres@anarazel.de>
Date: Mon Sep 18 19:36:44 2017 -0700

Rearm statement_timeout after each executed query.

Previously statement_timeout, in the extended protocol, affected all
messages till a Sync message. For clients that pipeline/batch query
execution that's problematic.

Instead disable timeout after each Execute message, and enable, if
necessary, the timer in start_xact_command(). As that's done only for
Execute and not Parse / Bind, pipelining the latter two could still
cause undesirable timeouts. But a survey of protocol implementations
shows that all drivers issue Sync messages when preparing, and adding
timeout rearming to both is fairly expensive for the common parse /
bind / execute sequence.

Author: Tatsuo Ishii, editorialized by Andres Freund
Reviewed-By: Takayuki Tsunakawa, Andres Freund
Discussion: /messages/by-id/20170222.115044.1665674502985097185.t-ishii@sraoss.co.jp

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Tue, May 15, 2018 at 08:10:20AM +0900, Michael Paquier wrote:

On Mon, May 14, 2018 at 04:04:58PM -0400, Bruce Momjian wrote:

So, channel binding has had me confused since I first heard about it. I
have done some research and reworded the commit with the attached
first patch.

pg11.diff looks roughly fine to me.

Also, I have created a second patch which actually explains the two
SCRAM channel binding options and how the work.

+         The list of channel binding types supported by the server are
+         listed in <xref linkend="sasl-authentication"/>.  An empty value
+         specifies that the client will not use channel binding.  If this
+         parameter is not specified, <literal>tls-unique</literal> is used,
+         if supported by both server and client.

OK, that's simple enough for users, and we talk about the libpq
parameter here.

Great, committed.

The second paragraph is also a nice addition. You really looked at this
stuff!

Committed too.

Yeah, it bugs me when I hear terms thrown around but can't get to the
details of what is happening. This PDF unlocked it for me:

http://www.manulis.eu/papers/MaStDe_SSR14.pdf

One question I do have is how do we prevent a fake server in the middle
from pretending it is a PG 10 server and therefore avoiding channel
binding protections? I don't see any channel binding options in
pg_hba.conf, and while libpq has options, they are explained with "This
parameter is mainly intended for protocol testing."

The answer is that you cannot do that now, as much as you cannot have a
client forbid connection attempt if the client requests SCRAM but the
server downgrades to MD5. I had a topic on the matter at an unconf
session at the last PGAsia, and except for administrators which forgot
to upgrade a set of servers that was not something worth complicating
the code for, at least that's the conclusion which came out of the
session. At the end, this is not actually something that you would
control from the server if you care about security, but something which
is controlled from the client. The limitations that we have know are
partially due to the way libpq handles the authentication protocol.
Hence if you want to prevent servers attempting to do downgrades, you
need options like sslmode saying those things from the client point of
view:
- I want SCRAM, but refuse connection request if server attempts MD5 or
something else.
- I want SCRAM and channel binding, but refuse connection request if
server does not advertise channel binding to the client.

Agreed. The libpq parameters don't help, I assume.

There may be value to an server side parameter which forces clients to
use channel binding even if the server has advertised the channel
binding SASL mechanism, and even if connection is made with SSL, but
that's not a downgrade-attack prevention.

What TLS does is to mix the offered ciphers into the negotiation hash so
a man-in-the-middle can't pretend it doesn't support something. Could
we do something like that here?

I have to question the value of man-in-the-middle protection that is so
easily bypassed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Tue, May 15, 2018 at 09:19:04AM +0900, Tatsuo Ishii wrote:

Hi Bruce,

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I could not find this:

$ git log -1 f8e5f15
commit f8e5f156b30efee5d0038b03e38735773abcb7ed
Author: Andres Freund <andres@anarazel.de>
Date: Mon Sep 18 19:36:44 2017 -0700

Rearm statement_timeout after each executed query.

Previously statement_timeout, in the extended protocol, affected all
messages till a Sync message. For clients that pipeline/batch query
execution that's problematic.

Instead disable timeout after each Execute message, and enable, if
necessary, the timer in start_xact_command(). As that's done only for
Execute and not Parse / Bind, pipelining the latter two could still
cause undesirable timeouts. But a survey of protocol implementations
shows that all drivers issue Sync messages when preparing, and adding
timeout rearming to both is fairly expensive for the common parse /
bind / execute sequence.

Author: Tatsuo Ishii, editorialized by Andres Freund
Reviewed-By: Takayuki Tsunakawa, Andres Freund
Discussion: /messages/by-id/20170222.115044.1665674502985097185.t-ishii@sraoss.co.jp

That seemed too detailed for the release notes. Is that wrong?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

I could not find this:

$ git log -1 f8e5f15
commit f8e5f156b30efee5d0038b03e38735773abcb7ed
Author: Andres Freund <andres@anarazel.de>
Date: Mon Sep 18 19:36:44 2017 -0700

Rearm statement_timeout after each executed query.

Previously statement_timeout, in the extended protocol, affected all
messages till a Sync message. For clients that pipeline/batch query
execution that's problematic.

Instead disable timeout after each Execute message, and enable, if
necessary, the timer in start_xact_command(). As that's done only for
Execute and not Parse / Bind, pipelining the latter two could still
cause undesirable timeouts. But a survey of protocol implementations
shows that all drivers issue Sync messages when preparing, and adding
timeout rearming to both is fairly expensive for the common parse /
bind / execute sequence.

Author: Tatsuo Ishii, editorialized by Andres Freund
Reviewed-By: Takayuki Tsunakawa, Andres Freund
Discussion: /messages/by-id/20170222.115044.1665674502985097185.t-ishii@sraoss.co.jp

That seemed too detailed for the release notes. Is that wrong?

This commit gives user-visible changes to the statment timeout
behavior. So I think this should be added to the release notes.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Hi,

2018/05/15 5:28、Bruce Momjian <bruce@momjian.us>のメール:

Added:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

Thank you.

Regards,
—-
Dang Minh Huong

On Tue, May 15, 2018 at 2:05 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Sun, May 13, 2018 at 04:40:19PM +0530, Amit Kapila wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Thanks for compiling the first draft. I think there are few things
which we should add to the Parallel Queries section. (a) commit
e89a71fb449af2ef74f47be1175f99956cf21524 -
Pass InitPlan values to workers via Gather (Merge). We can write
something like "Allow the part of the plan that references an initplan
to run in parallel" or "Parallelise queries containing initplans",

Uh, I need some explaination of what an initplan is

See the below comment in the code:
" This module is concerned with executing SubPlan expression nodes,
which should not be confused with sub-SELECTs appearing in FROM.
SubPlans are divided into "initplans", which are those that need only
one evaluation per query (among other restrictions, this requires that
they don't use any direct correlation variables from the parent plan
level), and "regular" subplans, which are re-evaluated every time
their result is required.

and what type of
query this helps.

Serial-Plan
-----------------
postgres=# explain select * from t1 where t1.k=(select max(k) from t3);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on t1 (cost=35.51..71.01 rows=10 width=12)
Filter: (k = $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=35.50..35.51 rows=1 width=4)
-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=4)
(5 rows)

Parallel-Plan
--------------------
postgres=# explain select * from t1 where t1.k=(select max(k) from t3);
QUERY PLAN
---------------------------------------------------------------------------------------
Gather (cost=9.71..19.38 rows=2 width=12)
Workers Planned: 2
Params Evaluated: $1
InitPlan 1 (returns $1)
-> Finalize Aggregate (cost=9.70..9.71 rows=1 width=4)
-> Gather (cost=9.69..9.70 rows=2 width=4)
Workers Planned: 2
-> Partial Aggregate (cost=9.69..9.70 rows=1 width=4)
-> Parallel Seq Scan on t3 (cost=0.00..8.75
rows=375 width=4)
-> Parallel Seq Scan on t1 (cost=0.00..9.67 rows=1 width=12)
Filter: (k = $1)
(11 rows)

Here, you can observe that initplan itself gets parallelized and the
part of plan tree referring initplan also got parallelized.

The other example from regression test is:

explain (costs off)
select count(*) from tenk1
where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
QUERY PLAN
------------------------------------------------------
Aggregate
InitPlan 1 (returns $2)
-> Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on tenk2
-> Gather
Workers Planned: 4
Params Evaluated: $2
-> Parallel Seq Scan on tenk1
Filter: (unique1 = $2)
(12 rows)

I can share more examples if required.

(b) "Parallelise queries that contains expensive functions in target
lists" (Commits - 3f90ec8597c3515e0d3190613b31491686027e4b and
11cf92f6e2e13c0a6e3f98be3e629e6bd90b74d5).

So we generate the entire query before the target list function calls,
the run another parallel run just for those function calls?

No, it is not like that. We divide the scan among workers and each
worker should perform projection of the rows it scanned (after
applying filter). Now, if the expensive functions are part of target
lists, then we can push the computation of expensive functions (as
part of target list) in workers which will divide the work.

Really? Do
we run each column in its own worker or do we split the result set into
parts and run those in parallel? How do we know, just the function call
costs?

The function's cost can be determined via pg_proc->procost. For this
particular case, you can refer the call graph -
create_pathtarget->set_pathtarget_cost_width->cost_qual_eval_node->cost_qual_eval_walker->get_func_cost

I can admit I never saw that coming.

I think the use case becomes interesting with parallel query because
now you can divide such cost among workers.

Feel free to ask more questions if above doesn't clarify the usage of
these features.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 2018/05/15 5:30, Bruce Momjian wrote:

On Sun, May 13, 2018 at 06:53:26PM +0900, Amit Langote wrote:

The following item

+<!--
+2017-11-23 [05b6ec39d] Show partition info from psql \d+
+-->
+
+       <para>
+        Have psql \d+ show a partition count of zero (Amit Langote)
+       </para>

missed mentioning Ashutosh Bapat as an author.

Added.

Maybe, the item name and description text could be improved as follows:

+       <para>
+        Have psql \d+ always show the partition information (Amit
Langote, Ashutosh Bapat)
+       </para>
+
+       <para>
+        Previously partition information would not be displayed for a
partitioned table if
+        it had no partitions.  Also indicate which partitions are
themselves partitioned.
+       </para>

I like it, done.

Thank you.

Regards,
Amit

(2018/05/12 0:08), Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics. I hope to do better job during Postgres 12.

Thanks for compiling this, Bruce!

I found a copy-pasto in this:

Below you will find a detailed account of the changes between
<productname>PostgreSQL</productname> 10 and the previous major
release.

I think the PG version should be 11, not 10. Patch attached.

Best regards,
Etsuro Fujita

On 15 May 2018 at 08:28, Bruce Momjian <bruce@momjian.us> wrote:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

While I'm not in favour of removing Dang's credit here, technically
this patch was Tom's. The code added in float.c by Dang's patch
(61b200e2f) was effectively reverted by 6bdf1303. Dang's regression
tests remain, so should also be credited along with Tom.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12 May 2018 at 03:08, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I wonder if 3cda10f41bfed -- "Use atomic ops to hand out pages to scan
in parallel scan." should be listed in the notes.

If so, I'd suggest something like:

Improve performance of Parallel Seq Scans with many parallel workers
(David Rowley).

I'd be more inclined to leave it off the notes if the improvement was
marginal, but it was a fairly considerable improvement, per my
benchmarks in [1]/messages/by-id/CAKJS1f9tgsPhqBcoPjv9_KUPZvTLCZ4jy=B=bhqgaKn7cYzm-w@mail.gmail.com.

[1]: /messages/by-id/CAKJS1f9tgsPhqBcoPjv9_KUPZvTLCZ4jy=B=bhqgaKn7cYzm-w@mail.gmail.com

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi,

From: David Rowley [mailto:david.rowley@2ndquadrant.com]
Sent: Tuesday, May 15, 2018 3:01 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Đặng Minh Hướng <kakalot49@gmail.com>; PostgreSQL-development
<pgsql-hackers@postgresql.org>
Subject: Re: Postgres 11 release notes

On 15 May 2018 at 08:28, Bruce Momjian <bruce@momjian.us> wrote:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

While I'm not in favour of removing Dang's credit here, technically this
patch was Tom's. The code added in float.c by Dang's patch
(61b200e2f) was effectively reverted by 6bdf1303. Dang's regression tests
remain, so should also be credited along with Tom.

Thanks David, I agreed.
Also 6bdf1303 should be included like below,

<!--
 Branch: master [6bdf1303b] Avoid wrong results for power() with NaN
+Branch: master [61b200e2f] Avoid wrong results for power() with NaN
 -->

        <para>
         Consistently return <literal>NaN</literal> for
         <literal>NaN</literal> inputs to <function>power()</function>
-        on older platforms (Dang Minh Huong)
+        on older platforms (Tom Lane, Dang Minh Huong)
        </para>

Thanks and best regards,
---
Dang Minh Huong
NEC Solution Innovators, Ltd.
http://www.nec-solutioninnovators.co.jp/en/

On Tue, May 15, 2018 at 11:43 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 12 May 2018 at 03:08, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I wonder if 3cda10f41bfed -- "Use atomic ops to hand out pages to scan
in parallel scan." should be listed in the notes.

If so, I'd suggest something like:

Improve performance of Parallel Seq Scans with many parallel workers
(David Rowley).

+1 to add this in Release Notes.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

David Rowley <david.rowley@2ndquadrant.com> writes:

On 15 May 2018 at 08:28, Bruce Momjian <bruce@momjian.us> wrote:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

While I'm not in favour of removing Dang's credit here, technically
this patch was Tom's. The code added in float.c by Dang's patch
(61b200e2f) was effectively reverted by 6bdf1303. Dang's regression
tests remain, so should also be credited along with Tom.

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that
we ensured POSIX behavior for NaNs only in HEAD --- I wonder
whether we shouldn't do what was mentioned in the commit log for
6bdf1303, and teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

regards, tom lane

On Tue, May 15, 2018 at 08:45:07AM +0530, Amit Kapila wrote:

No, it is not like that. We divide the scan among workers and each
worker should perform projection of the rows it scanned (after
applying filter). Now, if the expensive functions are part of target
lists, then we can push the computation of expensive functions (as
part of target list) in workers which will divide the work.

Really? Do
we run each column in its own worker or do we split the result set into
parts and run those in parallel? How do we know, just the function call
costs?

The function's cost can be determined via pg_proc->procost. For this
particular case, you can refer the call graph -
create_pathtarget->set_pathtarget_cost_width->cost_qual_eval_node->cost_qual_eval_walker->get_func_cost

I can admit I never saw that coming.

I think the use case becomes interesting with parallel query because
now you can divide such cost among workers.

Feel free to ask more questions if above doesn't clarify the usage of
these features.

OK, I have added the following release note item for both of these:

2017-11-16 [e89a71fb4] Pass InitPlan values to workers via Gather (Merge).
2018-03-29 [3f90ec859] Postpone generate_gather_paths for topmost scan/join rel
2018-03-29 [11cf92f6e] Rewrite the code that applies scan/join targets to paths

Allow single-evaluation queries, e.g. <literal>FROM</literal>
clause queries, and functions in the target list to be
parallelized (Amit Kapila, Robert Haas)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 16 May 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <david.rowley@2ndquadrant.com> writes:

While I'm not in favour of removing Dang's credit here, technically
this patch was Tom's. The code added in float.c by Dang's patch
(61b200e2f) was effectively reverted by 6bdf1303. Dang's regression
tests remain, so should also be credited along with Tom.

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that
we ensured POSIX behavior for NaNs only in HEAD --- I wonder
whether we shouldn't do what was mentioned in the commit log for
6bdf1303, and teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

I'm inclined to agree. It's hard to imagine these two functions
behaving differently in regards to NaN input is useful to anyone.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, May 15, 2018 at 09:51:47AM +0900, Tatsuo Ishii wrote:

I could not find this:

$ git log -1 f8e5f15
commit f8e5f156b30efee5d0038b03e38735773abcb7ed
Author: Andres Freund <andres@anarazel.de>
Date: Mon Sep 18 19:36:44 2017 -0700

Rearm statement_timeout after each executed query.

Previously statement_timeout, in the extended protocol, affected all
messages till a Sync message. For clients that pipeline/batch query
execution that's problematic.

Instead disable timeout after each Execute message, and enable, if
necessary, the timer in start_xact_command(). As that's done only for
Execute and not Parse / Bind, pipelining the latter two could still
cause undesirable timeouts. But a survey of protocol implementations
shows that all drivers issue Sync messages when preparing, and adding
timeout rearming to both is fairly expensive for the common parse /
bind / execute sequence.

Author: Tatsuo Ishii, editorialized by Andres Freund
Reviewed-By: Takayuki Tsunakawa, Andres Freund
Discussion: /messages/by-id/20170222.115044.1665674502985097185.t-ishii@sraoss.co.jp

That seemed too detailed for the release notes. Is that wrong?

This commit gives user-visible changes to the statment timeout
behavior. So I think this should be added to the release notes.

OK, makes sense. Here is what I added:

2017-09-18 [f8e5f156b] Rearm statement_timeout after each executed query.

In the <link linkend="protocol-query-concepts">Extended Query
Protocol</link>, have <varname>statement_timeout</varname> apply
to each <literal>Execute</literal> message, not to all commands
before <literal>Sync</literal> (Tatsuo Ishii)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Tue, May 15, 2018 at 06:40:18AM +0000, Huong Dangminh wrote:

Hi,

From: David Rowley [mailto:david.rowley@2ndquadrant.com]
Sent: Tuesday, May 15, 2018 3:01 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Đặng Minh Hướng <kakalot49@gmail.com>; PostgreSQL-development
<pgsql-hackers@postgresql.org>
Subject: Re: Postgres 11 release notes

On 15 May 2018 at 08:28, Bruce Momjian <bruce@momjian.us> wrote:

Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</literal>
on older platforms (Dang Minh Huong)

While I'm not in favour of removing Dang's credit here, technically this
patch was Tom's. The code added in float.c by Dang's patch
(61b200e2f) was effectively reverted by 6bdf1303. Dang's regression tests
remain, so should also be credited along with Tom.

Thanks David, I agreed.
Also 6bdf1303 should be included like below,

<!--
Branch: master [6bdf1303b] Avoid wrong results for power() with NaN
+Branch: master [61b200e2f] Avoid wrong results for power() with NaN
-->

<para>
Consistently return <literal>NaN</literal> for
<literal>NaN</literal> inputs to <function>power()</function>
-        on older platforms (Dang Minh Huong)
+        on older platforms (Tom Lane, Dang Minh Huong)
</para>

OK, changed, thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Tue, May 15, 2018 at 02:26:33PM +0900, Etsuro Fujita wrote:

(2018/05/12 0:08), Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics. I hope to do better job during Postgres 12.

Thanks for compiling this, Bruce!

I found a copy-pasto in this:

Below you will find a detailed account of the changes between
<productname>PostgreSQL</productname> 10 and the previous major
release.

I think the PG version should be 11, not 10. Patch attached.

Ah, seems I missed that one, thanks.

---------------------------------------------------------------------------

Best regards,
Etsuro Fujita

diff --git a/doc/src/sgml/release-11.sgml b/doc/src/sgml/release-11.sgml
index 4b31b46..407c67b 100644
--- a/doc/src/sgml/release-11.sgml
+++ b/doc/src/sgml/release-11.sgml
@@ -345,7 +345,7 @@ Branch: master [6bdf1303b] Avoid wrong results for power() with NaN

<para>
Below you will find a detailed account of the changes between
-    <productname>PostgreSQL</productname> 10 and the previous major
+    <productname>PostgreSQL</productname> 11 and the previous major
release.
</para>

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Tue, May 15, 2018 at 01:04:55PM +0530, Amit Kapila wrote:

On Tue, May 15, 2018 at 11:43 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 12 May 2018 at 03:08, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

I wonder if 3cda10f41bfed -- "Use atomic ops to hand out pages to scan
in parallel scan." should be listed in the notes.

If so, I'd suggest something like:

Improve performance of Parallel Seq Scans with many parallel workers
(David Rowley).

+1 to add this in Release Notes.

Done, and URL updated with all confirmed changed. :-)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

This commit gives user-visible changes to the statment timeout
behavior. So I think this should be added to the release notes.

OK, makes sense. Here is what I added:

2017-09-18 [f8e5f156b] Rearm statement_timeout after each executed query.

In the <link linkend="protocol-query-concepts">Extended Query
Protocol</link>, have <varname>statement_timeout</varname> apply
to each <literal>Execute</literal> message, not to all commands
before <literal>Sync</literal> (Tatsuo Ishii)

Can you please add Andres Freund to the author? He made extensive
changes to the original patch to improve it.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

David Rowley <david.rowley@2ndquadrant.com> writes:

On 16 May 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that
we ensured POSIX behavior for NaNs only in HEAD --- I wonder
whether we shouldn't do what was mentioned in the commit log for
6bdf1303, and teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

I'm inclined to agree. It's hard to imagine these two functions
behaving differently in regards to NaN input is useful to anyone.

Here's a proposed patch for that.

regards, tom lane

fOn Wed, May 16, 2018 at 06:11:52AM +0900, Tatsuo Ishii wrote:

This commit gives user-visible changes to the statment timeout
behavior. So I think this should be added to the release notes.

OK, makes sense. Here is what I added:

2017-09-18 [f8e5f156b] Rearm statement_timeout after each executed query.

In the <link linkend="protocol-query-concepts">Extended Query
Protocol</link>, have <varname>statement_timeout</varname> apply
to each <literal>Execute</literal> message, not to all commands
before <literal>Sync</literal> (Tatsuo Ishii)

Can you please add Andres Freund to the author? He made extensive
changes to the original patch to improve it.

Done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Can you please add Andres Freund to the author? He made extensive
changes to the original patch to improve it.

Done.

Thanks!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Change the ps process display labels to match the
pg_stat_activity.backend_type labels (Peter Eisentraut)

pg_stat_activity.backend_type label for postgres process dedicated to
user sessions is "client backend" while ps still shows something like
"postgres: t-ishii postgres [local] idle". Do we want to mention this?

For example "Change the ps process display labels to match the
pg_stat_activity.backend_type labels except client backend (Peter
Eisentraut)"

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Wed, May 16, 2018 at 08:33:53AM +0900, Tatsuo Ishii wrote:

Change the ps process display labels to match the
pg_stat_activity.backend_type labels (Peter Eisentraut)

pg_stat_activity.backend_type label for postgres process dedicated to
user sessions is "client backend" while ps still shows something like
"postgres: t-ishii postgres [local] idle". Do we want to mention this?

For example "Change the ps process display labels to match the
pg_stat_activity.backend_type labels except client backend (Peter
Eisentraut)"

Good point. Should we mention background workers instead?

Change the ps process display labels for background workers
to match the pg_stat_activity.backend_type labels Eisentraut)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

pg_stat_activity.backend_type label for postgres process dedicated to
user sessions is "client backend" while ps still shows something like
"postgres: t-ishii postgres [local] idle". Do we want to mention this?

For example "Change the ps process display labels to match the
pg_stat_activity.backend_type labels except client backend (Peter
Eisentraut)"

Good point. Should we mention background workers instead?

Change the ps process display labels for background workers
to match the pg_stat_activity.backend_type labels Eisentraut)

Seems good to me.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Wed, May 16, 2018 at 08:52:05AM +0900, Tatsuo Ishii wrote:

pg_stat_activity.backend_type label for postgres process dedicated to
user sessions is "client backend" while ps still shows something like
"postgres: t-ishii postgres [local] idle". Do we want to mention this?

For example "Change the ps process display labels to match the
pg_stat_activity.backend_type labels except client backend (Peter
Eisentraut)"

Good point. Should we mention background workers instead?

Change the ps process display labels for background workers
to match the pg_stat_activity.backend_type labels Eisentraut)

Seems good to me.

Done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

There's a small typo.

Add support for with huge(large) pages on Windows (Takayuki Tsunakawa, Thomas Munro)

I think a space between "huge" and "(large)" is needed.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Wed, May 16, 2018 at 09:01:35AM +0900, Tatsuo Ishii wrote:

There's a small typo.

Add support for with huge(large) pages on Windows (Takayuki Tsunakawa, Thomas Munro)

I think a space between "huge" and "(large)" is needed.

Done, URL updated.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 14, 2018 at 08:45:44PM -0400, Bruce Momjian wrote:

What TLS does is to mix the offered ciphers into the negotiation hash so
a man-in-the-middle can't pretend it doesn't support something. Could
we do something like that here?

I have to admit that I don't quite follow here, the shape of channel
binding data is decided by RFC 5929, so we need to stick with it.

I have to question the value of man-in-the-middle protection that is so
easily bypassed.

Well, the backend does its job, and answers based on what the client
wants to do. But what you are questioning here is the handling of
authentication downgrade attempts from a server by libpq, which is a
different problem, larger than just channel binding as it relates as
well to MD5/SCRAM interactions. For example, it is perfectly possible
to implement downgrade protections for any drivers which speak the
protocol, like JDBC, even with a v11 backend.
--
Michael

On Wed, May 16, 2018 at 12:17 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Tue, May 15, 2018 at 08:45:07AM +0530, Amit Kapila wrote:

No, it is not like that. We divide the scan among workers and each
worker should perform projection of the rows it scanned (after
applying filter). Now, if the expensive functions are part of target
lists, then we can push the computation of expensive functions (as
part of target list) in workers which will divide the work.

Really? Do
we run each column in its own worker or do we split the result set into
parts and run those in parallel? How do we know, just the function call
costs?

The function's cost can be determined via pg_proc->procost. For this
particular case, you can refer the call graph -
create_pathtarget->set_pathtarget_cost_width->cost_qual_eval_node->cost_qual_eval_walker->get_func_cost

I can admit I never saw that coming.

I think the use case becomes interesting with parallel query because
now you can divide such cost among workers.

Feel free to ask more questions if above doesn't clarify the usage of
these features.

OK, I have added the following release note item for both of these:

2017-11-16 [e89a71fb4] Pass InitPlan values to workers via Gather (Merge).
2018-03-29 [3f90ec859] Postpone generate_gather_paths for topmost scan/join rel
2018-03-29 [11cf92f6e] Rewrite the code that applies scan/join targets to paths

Allow single-evaluation queries, e.g. <literal>FROM</literal>
clause queries, and functions in the target list to be
parallelized (Amit Kapila, Robert Haas)

Sorry, but it is not clear to me what you intend to say by "e.g.
<literal>FROM</literal> clause queries"? What I could imagine is
something like "e.g. queries in <literal>WHERE</literal> clause that
return aggregate value"

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 15 May 2018 at 22:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <david.rowley@2ndquadrant.com> writes:

On 16 May 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that
we ensured POSIX behavior for NaNs only in HEAD --- I wonder
whether we shouldn't do what was mentioned in the commit log for
6bdf1303, and teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

I'm inclined to agree. It's hard to imagine these two functions
behaving differently in regards to NaN input is useful to anyone.

Here's a proposed patch for that.

In the case 1 ^ NaN = 1, what should the result scale be?

For other inputs, the result scale is at least as large as the scale
of the first input, so I would suggest that the same ought to be the
case here.

Otherwise, this looks fine, and I agree that it makes thinks neater /
more consistent.

Regards,
Dean

(2018/05/16 4:45), Bruce Momjian wrote:

On Tue, May 15, 2018 at 02:26:33PM +0900, Etsuro Fujita wrote:

(2018/05/12 0:08), Bruce Momjian wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics. I hope to do better job during Postgres 12.

Thanks for compiling this, Bruce!

I found a copy-pasto in this:

Below you will find a detailed account of the changes between
<productname>PostgreSQL</productname> 10 and the previous major
release.

I think the PG version should be 11, not 10. Patch attached.

Ah, seems I missed that one, thanks.

Thank you.

Best regards,
Etsuro Fujita

On 16/05/18 07:22, Michael Paquier wrote:

On Mon, May 14, 2018 at 08:45:44PM -0400, Bruce Momjian wrote:

What TLS does is to mix the offered ciphers into the negotiation hash so
a man-in-the-middle can't pretend it doesn't support something. Could
we do something like that here?

I have to admit that I don't quite follow here, the shape of channel
binding data is decided by RFC 5929, so we need to stick with it.

I have to question the value of man-in-the-middle protection that is so
easily bypassed.

Well, the backend does its job, and answers based on what the client
wants to do. But what you are questioning here is the handling of
authentication downgrade attempts from a server by libpq, which is a
different problem, larger than just channel binding as it relates as
well to MD5/SCRAM interactions. For example, it is perfectly possible
to implement downgrade protections for any drivers which speak the
protocol, like JDBC, even with a v11 backend.

I have to agree with Bruce, that it's pretty useless to implement
channel binding, if there is no way to require it in libpq. IMHO that
must be fixed.

It's true that even if libpq doesn't implement it, other drivers like
JDBC could. Good for them, but that still sucks for libpq.

- Heikki

* Allow bytes to be specified for server variable sizes (Beena Emerson)

The specification is "B".

I had to dig the commit in the git history to figure out what this is.
I'd suggest rewording this:

* Allow server options related to memory and file sizes, to be specified
as number of bytes.

The new unit is "B". This is in addition to "kB", "MB", "GB" and "TB",
which were accepted previously.

- Heikki

On Wed, May 16, 2018 at 01:09:07PM +0300, Heikki Linnakangas wrote:

I have to agree with Bruce, that it's pretty useless to implement channel
binding, if there is no way to require it in libpq. IMHO that must be
fixed.

Wouldn't we want to also do something for the case where a client is
willing to use SCRAM but that the server forces back MD5? In which
case, one possibility is a connection parameter like the following,
named say authmethod:
- An empty value is equivalent to the current behavior, and is the
default.
- 'scram' means that client is willing to use SCRAM, which would cause a
failure if server attempts to enforce MD5.
- 'scram-plus' means that client enforces SCRAM and channel binding.

Or we could just have a channel_binding_mode, which has a "require"
value like sslmode, and "prefer" mode, which is the default and the
current behavior... Still what to do with MD5 requests in this case?
--
Michael

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 15 May 2018 at 22:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Here's a proposed patch for that.

In the case 1 ^ NaN = 1, what should the result scale be?

The result is exact, so I don't see a reason to be worried about its
scale. Messing with the scale would also require expending even
more code on what is, in the end, a corner case that no users have
expressed concern about.

regards, tom lane

On 2018-May-15, Bruce Momjian wrote:

On Wed, May 16, 2018 at 09:01:35AM +0900, Tatsuo Ishii wrote:

There's a small typo.

Add support for with huge(large) pages on Windows (Takayuki Tsunakawa, Thomas Munro)

I think a space between "huge" and "(large)" is needed.

Done, URL updated.

I'm not sure why we say "huge (large) pages". The natural term for
Windows is "large-pages",
https://msdn.microsoft.com/en-us/library/windows/desktop/aa366720(v=vs.85).aspx
so I think we should use that terminology. Maybe something like

Add support for <firstterm>large pages</firstterm> on Windows (Takayuki Tsunakawa, Thomas Munro)
This is controlled by the <link>huge_pages</link> configuration parameter.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, May 16, 2018 at 4:08 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

On 2018-May-15, Bruce Momjian wrote:

On Wed, May 16, 2018 at 09:01:35AM +0900, Tatsuo Ishii wrote:

There's a small typo.

Add support for with huge(large) pages on Windows (Takayuki

Tsunakawa, Thomas Munro)

I think a space between "huge" and "(large)" is needed.

Done, URL updated.

I'm not sure why we say "huge (large) pages". The natural term for
Windows is "large-pages",
https://msdn.microsoft.com/en-us/library/windows/desktop/
aa366720(v=vs.85).aspx
so I think we should use that terminology. Maybe something like

Add support for <firstterm>large pages</firstterm> on Windows (Takayuki
Tsunakawa, Thomas Munro)
This is controlled by the <link>huge_pages</link> configuration parameter.

I assume the point here is that we the term huge pages is what's used in
PostgreSQL. For example, they are still configured using the huge_pages
GUC.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 2018-May-16, Magnus Hagander wrote:

On Wed, May 16, 2018 at 4:08 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

I'm not sure why we say "huge (large) pages". The natural term for
Windows is "large-pages",
https://msdn.microsoft.com/en-us/library/windows/desktop/
aa366720(v=vs.85).aspx
so I think we should use that terminology. Maybe something like

Add support for <firstterm>large pages</firstterm> on Windows (Takayuki
Tsunakawa, Thomas Munro)
This is controlled by the <link>huge_pages</link> configuration parameter.

I assume the point here is that we the term huge pages is what's used
in PostgreSQL. For example, they are still configured using the
huge_pages GUC.

That's exactly what my proposed wording says :-)

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, May 16, 2018 at 6:24 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

On 2018-May-16, Magnus Hagander wrote:

On Wed, May 16, 2018 at 4:08 PM, Alvaro Herrera <

alvherre@2ndquadrant.com>

wrote:

I'm not sure why we say "huge (large) pages". The natural term for
Windows is "large-pages",
https://msdn.microsoft.com/en-us/library/windows/desktop/
aa366720(v=vs.85).aspx
so I think we should use that terminology. Maybe something like

Add support for <firstterm>large pages</firstterm> on Windows (Takayuki
Tsunakawa, Thomas Munro)
This is controlled by the <link>huge_pages</link> configuration

parameter.

I assume the point here is that we the term huge pages is what's used
in PostgreSQL. For example, they are still configured using the
huge_pages GUC.

That's exactly what my proposed wording says :-)

Uh. Nevermind. Nothing to see here. For some reason I only saw the first
paragraph.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 16 May 2018 at 09:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <david.rowley@2ndquadrant.com> writes:

On 16 May 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that
we ensured POSIX behavior for NaNs only in HEAD --- I wonder
whether we shouldn't do what was mentioned in the commit log for
6bdf1303, and teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

I'm inclined to agree. It's hard to imagine these two functions
behaving differently in regards to NaN input is useful to anyone.

Here's a proposed patch for that.

Looks good to me.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 16 May 2018 at 14:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

In the case 1 ^ NaN = 1, what should the result scale be?

The result is exact, so I don't see a reason to be worried about its
scale. Messing with the scale would also require expending even
more code on what is, in the end, a corner case that no users have
expressed concern about.

OK, fine. Not really worth worrying about.

Regards,
Dean

On Wed, May 16, 2018 at 01:22:45PM +0900, Michael Paquier wrote:

On Mon, May 14, 2018 at 08:45:44PM -0400, Bruce Momjian wrote:

What TLS does is to mix the offered ciphers into the negotiation hash so
a man-in-the-middle can't pretend it doesn't support something. Could
we do something like that here?

I have to admit that I don't quite follow here, the shape of channel
binding data is decided by RFC 5929, so we need to stick with it.

OK, let me explain. First, there are two man-in-the-middle types of
attacks. The first one allows the two legitimate ends of the TLS
connection to negotiate the shared secret between themselves, but
injects or changes some of the packets before the shared secret is
agreed upon, perhaps to downgrade the strength of the protocol options.
TLS prevents this type of man-in-the-middle attack by hashing the shared
secret with a hash of all of the TLS negotiation messages previously
sent. Each side who knows the shared secret can verify that no messages
were changed; see:

https://security.stackexchange.com/questions/115284/how-can-an-attacker-downgrade-modify-the-cipher-suites-when-they-are-maced-fre

The second type of man-in-the-middle attack is where the
man-in-the-middle is negotiating the shared secret separately with the
two end points. There is no way to detect this without having some
shared secret between the two valid end points. One shared secret is
the password hashed with the shared secret, which is what tls-unique
uses. The second uses the server certificate hashed with the shared
secret. This is now documented in Postgres:

In <acronym>SCRAM</acronym> with <literal>tls-unique</literal>
channel binding, the shared secret negotiated during the SSL session
is mixed into the user-supplied password hash. The shared secret
is partly chosen by the server, but not directly transmitted, making
it impossible for a fake server to create an SSL connection with the
client that has the same shared secret it has with the real server.
<acronym>SCRAM</acronym> with <literal>tls-server-end-point</literal>
mixes a hash of the server's certificate into the user-supplied password
hash. While a fake server can retransmit the real server's certificate,
it doesn't have access to the private key matching that certificate, and
therefore cannot prove it is the owner, causing SSL connection failure.

TLS prevents a man-in-the-middle from injecting invalid packets.
However, it does not prevent a man-in-the-middle attack with separeate
shared secret negotiation unless certificates are used.

The current problem under discussion is the same as that of the
man-in-the-middle packet change/injection attack in that the
man-in-the-middle can change the options reported as supported by the
Postgres server, before the password is sent. The most efficient fix
for this would be for the all Postgres protocol messages up to the point
where the authentication was chosen to be hashed with the password and
sent to the server. In that way, if a man-in-the-middle changed the
server-reported supported authentication, the server would identify this
and refuse the connection.

The problem is that current and past-version PG authentication methods
don't have any protocol downgrade protection, and it is hard to add it
unless you just remove support for old protocols. I think the only
reasonable solution is to allow the client and/or server to force
channel binding.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 16, 2018 at 08:59:23PM +0900, Michael Paquier wrote:

On Wed, May 16, 2018 at 01:09:07PM +0300, Heikki Linnakangas wrote:

I have to agree with Bruce, that it's pretty useless to implement channel
binding, if there is no way to require it in libpq. IMHO that must be
fixed.

Wouldn't we want to also do something for the case where a client is
willing to use SCRAM but that the server forces back MD5? In which
case, one possibility is a connection parameter like the following,
named say authmethod:
- An empty value is equivalent to the current behavior, and is the
default.
- 'scram' means that client is willing to use SCRAM, which would cause a
failure if server attempts to enforce MD5.
- 'scram-plus' means that client enforces SCRAM and channel binding.

Or we could just have a channel_binding_mode, which has a "require"
value like sslmode, and "prefer" mode, which is the default and the
current behavior... Still what to do with MD5 requests in this case?

Just to reiterate, MD5 and SCRAM-less-binding is designed to avoid
packet _replay_. It assumes no man-in-the-middle has adjusted what is
supported by the two endpoints.

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

FYI, I think the server could also require channel binding for SCRAM. We
already have scram-sha-256 in pg_hba.conf, and I think
scram-sha-256-plus would be reasonable.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 16, 2018 at 09:55:05AM +0530, Amit Kapila wrote:

On Wed, May 16, 2018 at 12:17 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Tue, May 15, 2018 at 08:45:07AM +0530, Amit Kapila wrote:

No, it is not like that. We divide the scan among workers and each
worker should perform projection of the rows it scanned (after
applying filter). Now, if the expensive functions are part of target
lists, then we can push the computation of expensive functions (as
part of target list) in workers which will divide the work.

Really? Do
we run each column in its own worker or do we split the result set into
parts and run those in parallel? How do we know, just the function call
costs?

The function's cost can be determined via pg_proc->procost. For this
particular case, you can refer the call graph -
create_pathtarget->set_pathtarget_cost_width->cost_qual_eval_node->cost_qual_eval_walker->get_func_cost

I can admit I never saw that coming.

I think the use case becomes interesting with parallel query because
now you can divide such cost among workers.

Feel free to ask more questions if above doesn't clarify the usage of
these features.

OK, I have added the following release note item for both of these:

2017-11-16 [e89a71fb4] Pass InitPlan values to workers via Gather (Merge).
2018-03-29 [3f90ec859] Postpone generate_gather_paths for topmost scan/join rel
2018-03-29 [11cf92f6e] Rewrite the code that applies scan/join targets to paths

Allow single-evaluation queries, e.g. <literal>FROM</literal>
clause queries, and functions in the target list to be
parallelized (Amit Kapila, Robert Haas)

Sorry, but it is not clear to me what you intend to say by "e.g.
<literal>FROM</literal> clause queries"? What I could imagine is
something like "e.g. queries in <literal>WHERE</literal> clause that
return aggregate value"

Oh, sorry, changed to:

Allow single-evaluation queries, e.g. <literal>WHERE</literal>
clause aggregate queries, and functions in the target list to be
parallelized (Amit Kapila, Robert Haas)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 16, 2018 at 01:50:04PM +0300, Heikki Linnakangas wrote:

* Allow bytes to be specified for server variable sizes (Beena Emerson)

The specification is "B".

I had to dig the commit in the git history to figure out what this is. I'd
suggest rewording this:

* Allow server options related to memory and file sizes, to be specified as
number of bytes.

The new unit is "B". This is in addition to "kB", "MB", "GB" and "TB", which
were accepted previously.

OK, good, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 16, 2018 at 10:08:18AM -0400, Alvaro Herrera wrote:

On 2018-May-15, Bruce Momjian wrote:

On Wed, May 16, 2018 at 09:01:35AM +0900, Tatsuo Ishii wrote:

There's a small typo.

Add support for with huge(large) pages on Windows (Takayuki Tsunakawa, Thomas Munro)

I think a space between "huge" and "(large)" is needed.

Done, URL updated.

I'm not sure why we say "huge (large) pages". The natural term for
Windows is "large-pages",
https://msdn.microsoft.com/en-us/library/windows/desktop/aa366720(v=vs.85).aspx
so I think we should use that terminology. Maybe something like

Add support for <firstterm>large pages</firstterm> on Windows (Takayuki Tsunakawa, Thomas Munro)
This is controlled by the <link>huge_pages</link> configuration parameter.

OK, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 16, 2018 at 08:20:49PM -0400, Bruce Momjian wrote:

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

Noted. Which means that the parameter is ignored when using a non-SSL
connection, as well as when the server tries to enforce the use of
anything else than SCRAM.

FYI, I think the server could also require channel binding for SCRAM. We
already have scram-sha-256 in pg_hba.conf, and I think
scram-sha-256-plus would be reasonable.

Noted as well. There is of course the question of v10 libpq versions
which don't support channel binding, but if an admin is willing to set
up scram-sha-256-plus in pg_hba.conf then he can request his users to
update his drivers/libs as well.

What's the take of others? Magnus, Stephen or Heikki perhaps (you've
been the most involved with SCRAM early talks)?
--
Michael

On Thu, May 17, 2018 at 09:56:49AM +0900, Michael Paquier wrote:

On Wed, May 16, 2018 at 08:20:49PM -0400, Bruce Momjian wrote:

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

Noted. Which means that the parameter is ignored when using a non-SSL
connection, as well as when the server tries to enforce the use of
anything else than SCRAM.

Uh, a man-in-the-middle could prevent SSL or ask for a different
password authentication method and then channel binding would not be
used. I think when you say you want channel binding, you have to fail
if you don't get it.

FYI, I think the server could also require channel binding for SCRAM. We
already have scram-sha-256 in pg_hba.conf, and I think
scram-sha-256-plus would be reasonable.

Noted as well. There is of course the question of v10 libpq versions
which don't support channel binding, but if an admin is willing to set
up scram-sha-256-plus in pg_hba.conf then he can request his users to
update his drivers/libs as well.

Yes, I don't see a way around it. Once you accept that someone in the
middle can change what you request undetected, then you can't do
fallback. Imagine a man-in-the-middle with TLS where the
man-in-the-middle allows the two end-points to negotiate the shared
secret, but the man-in-the-middle forces a weak cipher. This is what is
happening with Postgres when the man-in-the-middle forces a weaker
authentication method.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Thu, May 17, 2018 at 5:54 AM, Bruce Momjian <bruce@momjian.us> wrote:

Oh, sorry, changed to:

Allow single-evaluation queries, e.g. <literal>WHERE</literal>
clause aggregate queries, and functions in the target list to be
parallelized (Amit Kapila, Robert Haas)

LGTM. Thanks.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Hi,

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
David Rowley <david.rowley@2ndquadrant.com> writes:

On 16 May 2018 at 02:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not particularly fussed about getting credit for that. However,
looking again at how that patch series turned out --- ie, that we
ensured POSIX behavior for NaNs only in HEAD --- I wonder whether we
shouldn't do what was mentioned in the commit log for 6bdf1303, and
teach numeric_pow() about these same special cases.
It seems like it would be more consistent to change both functions
for v11, rather than letting that other shoe drop in some future
major release.

I'm inclined to agree. It's hard to imagine these two functions
behaving differently in regards to NaN input is useful to anyone.

Thank you. The patch looks fine to me.
Also, I have done the "make check" in Windows and Linux environment with no problem.

Thanks and best regards,
---
Dang Minh Huong
NEC Solution Innovators, Ltd.
http://www.nec-solutioninnovators.co.jp/en/

On Wed, May 16, 2018 at 09:09:22PM -0400, Bruce Momjian wrote:

On Thu, May 17, 2018 at 09:56:49AM +0900, Michael Paquier wrote:

On Wed, May 16, 2018 at 08:20:49PM -0400, Bruce Momjian wrote:

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

Noted. Which means that the parameter is ignored when using a non-SSL
connection, as well as when the server tries to enforce the use of
anything else than SCRAM.

Uh, a man-in-the-middle could prevent SSL or ask for a different
password authentication method and then channel binding would not be
used. I think when you say you want channel binding, you have to fail
if you don't get it.

I am not exactly sure what is the result we are looking for here, so I
am adding for now an open item which refers to this part of the thread.
Please note that I am fine to spend cycles if needed to address any
issues and/or concerns. Let's the discussion continue for now.
--
Michael

Hi Bruce,

Here is some bonus feedback.

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I expect a torrent of feedback. ;-)

I have just noticed that this entry does not have the correct author
(guess who?):
<para>
Add libpq option to support channel binding when using <link
linkend="auth-password"><acronym>SCRAM</acronym></link>
authentication (Peter Eisentraut)
</para>

I think that there should be two different entries in the release notes
for channel binding:
1) The new connection parameter in libpq which allows to control the
channel binding name, as well as to decide if it should be disabled.
I would think that this is better placed within the section for client
interface changes.
2) Channel binding itself, which should be part of the authentication
section.

<para>
Have libpq's <link
linkend="libpq-pqhost"><function>PQhost()</function></link>
always return the actual connected host (Hari Babu)
</para>
Should this be added as well in the section "Client interfaces"?
--
Michael

On Thu, May 17, 2018 at 2:56 AM, Michael Paquier <michael@paquier.xyz>
wrote:

On Wed, May 16, 2018 at 08:20:49PM -0400, Bruce Momjian wrote:

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

Noted. Which means that the parameter is ignored when using a non-SSL
connection, as well as when the server tries to enforce the use of
anything else than SCRAM.

(apologies if this was covered earlier, as I'm entering late into the
discussion)

"ignored" in combination with a security parameter is generally a very very
red flag.

If the client requests channel binding and ends up using a non encrypted
connection, surely the correct thing to do is fail the connection, rather
than downgrade the authentication?

We should really make sure we don't re-implement something as silly as our
current "sslmode=prefer", because it makes no sense. From the client side
perspective, there really only needs to be two choices -- "enforce channel
binding at level <x>" or "meh, I don't care". In the "meh, I don't care"
mode, go with whatever the server picks (through enforcement in pg_hba.conf
for example).

FYI, I think the server could also require channel binding for SCRAM. We

already have scram-sha-256 in pg_hba.conf, and I think
scram-sha-256-plus would be reasonable.

Noted as well. There is of course the question of v10 libpq versions
which don't support channel binding, but if an admin is willing to set
up scram-sha-256-plus in pg_hba.conf then he can request his users to
update his drivers/libs as well.

Yes. And they *should* fail if they don't upgrade. That's what requirement
means... :)

What's the take of others? Magnus, Stephen or Heikki perhaps (you've

been the most involved with SCRAM early talks)?

Saw it by luck. It would probably be better if it wasn't hidden deep in a
thread about release notes.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Wed, May 16, 2018 at 09:09:22PM -0400, Bruce Momjian wrote:

FYI, I think the server could also require channel binding for SCRAM. We
already have scram-sha-256 in pg_hba.conf, and I think
scram-sha-256-plus would be reasonable.

Noted as well. There is of course the question of v10 libpq versions
which don't support channel binding, but if an admin is willing to set
up scram-sha-256-plus in pg_hba.conf then he can request his users to
update his drivers/libs as well.

Yes, I don't see a way around it. Once you accept that someone in the
middle can change what you request undetected, then you can't do
fallback. Imagine a man-in-the-middle with TLS where the
man-in-the-middle allows the two end-points to negotiate the shared
secret, but the man-in-the-middle forces a weak cipher. This is what is
happening with Postgres when the man-in-the-middle forces a weaker
authentication method.

Technically, you can do automatic fallback if the fallback has
man-in-the-middle and downgrade protection. Technically, because TLS is
already active when we start authentication negotiation, we don't need
downgrade protection as long as we have man-in-the-middle protection.

Unfortunately, only SCRAM with channel binding and sslmode=verify-full
have man-in-the-middle protection. Therefore, downgrading from SCRAM
with channel binding to SCRAM without channel binding, MD5, or
'password' is only safe if sslmode=verify-full is enabled. Technically
MD5, or 'password' has weak or non-existent replay protection, but if we
are requiring TLS, then that doesn't really matter, assuming we have
man-in-the-middle protection.

I don't know if falling back from SCRAM with channel binding to a lesser
authentication methods only if sslmode=verify-full is enabled is really
helpful to anyone since it requires certificate installation.

TLS has similar downgrade issues:

http://www.educatedguesswork.org/2012/07/problems_with_secure_upgrade_t.html

but many of its downgrade options have downgrade protection, and you
don't lose man-in-the-middle protection by downgrading.
Man-in-the-middle protection via certificate checking happens
independent of the TLS version being used, which is not the case for
Postgres authentication downgrade options.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Thu, May 17, 2018 at 09:48:54PM +0900, Michael Paquier wrote:

On Wed, May 16, 2018 at 09:09:22PM -0400, Bruce Momjian wrote:

On Thu, May 17, 2018 at 09:56:49AM +0900, Michael Paquier wrote:

On Wed, May 16, 2018 at 08:20:49PM -0400, Bruce Momjian wrote:

SCRAM-with-binding is the first password method that attempts to avoid
man-in-the-middle attacks, and therefore is much less likely to be able
to trust what the endpoints supports. I think it is really the
channel_binding_mode that we want to control at the client. The lesser
modes are much more reasonable to use an automatic best-supported
negotiation, which is what we do now.

Noted. Which means that the parameter is ignored when using a non-SSL
connection, as well as when the server tries to enforce the use of
anything else than SCRAM.

Uh, a man-in-the-middle could prevent SSL or ask for a different
password authentication method and then channel binding would not be
used. I think when you say you want channel binding, you have to fail
if you don't get it.

I am not exactly sure what is the result we are looking for here, so I
am adding for now an open item which refers to this part of the thread.
Please note that I am fine to spend cycles if needed to address any
issues and/or concerns. Let's the discussion continue for now.

Agreed, and I just posted a more detailed email about when
authentication downgrades are possible.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Thu, May 17, 2018 at 03:38:36PM +0200, Magnus Hagander wrote:

What's the take of others?� Magnus, Stephen or Heikki perhaps (you've
been the most involved with SCRAM early talks)?

Saw it by luck. It would probably be better if it wasn't hidden deep in a
thread about release notes.

Agreed. The problem was so glaring that I assumed I was not
understanding it. I have modified this email subject so users will
hopefully read back in this thread to see the details.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Huong Dangminh <huo-dangminh@ys.jp.nec.com> writes:

Thank you. The patch looks fine to me.
Also, I have done the "make check" in Windows and Linux environment with no problem.

Pushed, thanks for reviewing/testing.

regards, tom lane

On Thu, May 17, 2018 at 10:35:53PM +0900, Michael Paquier wrote:

Hi Bruce,

Here is some bonus feedback.

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I expect a torrent of feedback. ;-)

I have just noticed that this entry does not have the correct author
(guess who?):

Fixed. (I think I guessed right.)

<para>
Add libpq option to support channel binding when using <link
linkend="auth-password"><acronym>SCRAM</acronym></link>
authentication (Peter Eisentraut)
</para>

I think that there should be two different entries in the release notes
for channel binding:
1) The new connection parameter in libpq which allows to control the
channel binding name, as well as to decide if it should be disabled.
I would think that this is better placed within the section for client
interface changes.

Well, I tend to put items in the first section that applies, and in this
case, you are right that the API is libpq but the feature is
authentication. We know we are going to need to adjust this feature,
so let's see where it ends up and let's revisit it.

2) Channel binding itself, which should be part of the authentication
section.

<para>
Have libpq's <link
linkend="libpq-pqhost"><function>PQhost()</function></link>
always return the actual connected host (Hari Babu)
</para>
Should this be added as well in the section "Client interfaces"?

Well, again, using the rules above, the PQhost item goes into the first
section where it fits, and incompatibility is the first such section.
There are other items in incompatibility that could be moved to lower,
but again, we want the incompatibilities to all be in the same place.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Thu, May 17, 2018 at 02:23:00PM -0400, Bruce Momjian wrote:

On Thu, May 17, 2018 at 10:35:53PM +0900, Michael Paquier wrote:

Hi Bruce,

Here is some bonus feedback.

On Fri, May 11, 2018 at 11:08:52AM -0400, Bruce Momjian wrote:

I expect a torrent of feedback. ;-)

I have just noticed that this entry does not have the correct author
(guess who?):

Fixed. (I think I guessed right.)

Thanks, Bruce. It looks that your guess is right.
--
Michael

On Thu, May 17, 2018 at 10:05:25AM -0400, Bruce Momjian wrote:

Agreed. The problem was so glaring that I assumed I was not
understanding it. I have modified this email subject so users will
hopefully read back in this thread to see the details.

Okay, let's use this new thread then for the follow-up discussion. For
people wondering, this basically refers to the following message from
Bruce and Heikki:
/messages/by-id/d01b31f5-0b3e-b69a-1504-a79649d81f46@iki.fi

There are actually two problems which have been touched in this
discussion:
1) A client using libpq may be forced by a rogue server to not use
channel binding even if it is willing to do so. For example, a v11
libpq with a v10 server enters in this category. An idea here would be
to provide an extra connection parameter which allows the client to
reject an access to a server if channel binding is not supported. One
idea is something I mentioned here:
/messages/by-id/20180516115923.GB14835@paquier.xyz
So we could have channel_binding_mode, which has a 'prefer' mode, which
is the actual default we have in the tree, as well as a 'require' mode,
which allows the client to fail connection if a server does not publish
the SCRAM-PLUS mechanism after the initial authentication message
exchange.
2) Allow clients to connect to servers only if they use channel
binding. This goes with a server-side hba configuration, like
scram-sha-256-plus to not allow access to clients in a SCRAM
authentication if they don't have channel binding support.

From a security point of view, 1) is important for libpq, but I am not
much enthusiast about 2) as a whole. The backend has proper support for
channel binding, hence other drivers speaking the protocol could have
their own restriction mechanisms.

I actually touched a bit what's being discussed here as part of the
channel binding thread:
/messages/by-id/CAB7nPqTRW9qbPSYpWtKJD9A+QBd6dS0ePkgrrjbkkG0C10zsBA@mail.gmail.com
However per what I read this does not cover the need to allow the client
to allow that channel binding *has* to be used depending on its
requirements.

I'd like to mention as well that I touched the topic during the
unconference of PGConf Asia last December:
https://wiki.postgresql.org/wiki/PGConf.ASIA2017_Developer_Unconference#SCRAM_improvements

Any ideas raised there did not get much enthusiam from the
participants. Magnus and Bruce have participated in the conference,
perhaps you were not at my unconf session..
--
Michael

On Fri, May 18, 2018 at 10:46:46AM +0900, Michael Paquier wrote:

On Thu, May 17, 2018 at 10:05:25AM -0400, Bruce Momjian wrote:

Agreed. The problem was so glaring that I assumed I was not
understanding it. I have modified this email subject so users will
hopefully read back in this thread to see the details.

Okay, let's use this new thread then for the follow-up discussion. For
people wondering, this basically refers to the following message from
Bruce and Heikki:
/messages/by-id/d01b31f5-0b3e-b69a-1504-a79649d81f46@iki.fi

FYI, it was only glaring to me because I have spent so many months
recently studying security.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 18, 2018 at 10:46:46AM +0900, Michael Paquier wrote:

From a security point of view, 1) is important for libpq, but I am not
much enthusiast about 2) as a whole. The backend has proper support for
channel binding, hence other drivers speaking the protocol could have
their own restriction mechanisms.

So, I have been playing with libpq to address point 1), and added a new
connection parameter called channel_binding_mode which can be set to
'prefer', which is what libpq uses now, and 'require'. The patch has
two important parts:
1) If a server does not support channel binding, still it is sending
back a SCRAM authentication, but the client still wants to enforce the
use of channel binding, then libpq reacts as follows:
$ psql -d "dbname=foo channel_binding_mode=require"
psql: channel binding required for SASL authentication but no valid
mechanism could be selected
This requires pg_SASL_init() to be patched after the SASL mechanism has
been selected. That error can be triggered with a v10 server with whom
a SCRAM authentication is done, as well as with a v11 server where SSL
is not used. Some people may use sslmode=prefer in combination to
channel_binding_mode=require, in which case an error should be raised if
the SSL connection cannot be achieved first. That addresses a bit of
the craziness of sslmode=prefer...
2) If client wants to use channel binding, but the server is trying to
enforce another protocol than SCRAM, like MD5, trust, gssapi or such,
then the following error happens:
$ psql -d "dbname=foo channel_binding_mode=require"
psql: channel binding required for authentication but no valid protocol are used
In this case, it seems to me that the best bet is to patch
pg_fe_sendauth() and filter by message types.

In the attached, I have added the parameter and some documentation. I
have not added tests, but some things could be tested in the SSL suite:
- Check for incorrect values in the parameter.
- Test connection without SCRAM with "require"
- Test connection without SSL but SCRAM with "require"
I have not put much thoughts into the documentation, but the patch is
rather simple so hopefully that helps in making progress in the
discussion.
--
Michael

H, Bruce!

11 мая 2018 г., в 20:08, Bruce Momjian <bruce@momjian.us> написал(а):

I expect a torrent of feedback. ;-)

I'm not sure it is usefull in release notes since it is more about API, and not user-facing change. Just in case.
GiST opclasses now can omit compress and decompress functions. If compress function is omited, IndexOnlyScan is enabled for opclass without any extra change.
https://github.com/postgres/postgres/commit/d3a4f89d8a3e500bd7c0b7a8a8a5ce1b47859128 <https://github.com/postgres/postgres/commit/d3a4f89d8a3e500bd7c0b7a8a8a5ce1b47859128&gt;

Best regards, Andrey Borodin.

On Tue, May 15, 2018 at 2:11 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 14, 2018 at 05:34:54PM +0530, Dilip Kumar wrote:

On Fri, May 11, 2018 at 8:38 PM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

On a personal note, I want to apologize for not adequately championing
two features that I think have strategic significance but didn't make it
into Postgres 11: parallel FDW access and improved multi-variate
statistics. I hope to do better job during Postgres 12.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

I think the below commit is missed in the release notes?

5edc63bda68a77c4d38f0cbeae1c4271f9ef4100

Committer: Robert Haas <rhaas@postgresql.org> 2017-11-10 13:50:50

Account for the effect of lossy pages when costing bitmap scans.

Dilip Kumar, reviewed by Alexander Kumenkov, Amul Sul, and me.
Some final adjustments by me.

As part of this commit, we are also accounting for the lossy pages during
bitmap costing. This will consider
the effect of work_mem while selecting the bitmap heap scan path.

Uh, that seems very exotic to mention, sorry. Others have opinions?

This patch improves the plan selection in many cases, see the
discussion of this patch[1]/messages/by-id/CAFiTN-uL=rQtvt9zFnLV9khXODhEyJTvC4TB135HSK1=YdFAxQ@mail.gmail.com[2]/messages/by-id/9daaf288-e67f-f349-965f-3a8c6e0819ae@postgrespro.ru. The change in plan leads to
significant performance improvements in a number of TPC-H queries at
various settings. I think it is worth considering to add this in the
release notes.

[1]: /messages/by-id/CAFiTN-uL=rQtvt9zFnLV9khXODhEyJTvC4TB135HSK1=YdFAxQ@mail.gmail.com
[2]: /messages/by-id/9daaf288-e67f-f349-965f-3a8c6e0819ae@postgrespro.ru

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Fri, May 18, 2018 at 12:03:49PM +0900, Michael Paquier wrote:

On Fri, May 18, 2018 at 10:46:46AM +0900, Michael Paquier wrote:

From a security point of view, 1) is important for libpq, but I am not
much enthusiast about 2) as a whole. The backend has proper support for
channel binding, hence other drivers speaking the protocol could have
their own restriction mechanisms.

So, I have been playing with libpq to address point 1), and added a new
connection parameter called channel_binding_mode which can be set to
'prefer', which is what libpq uses now, and 'require'. The patch has
two important parts:

Good work, but I think the existance of both scram_channel_binding and
channel_binding_mode in libpq is confusing. I am thinking we should
have one channel binding parameter that can take values "prefer",
"tls-unique", "tls-server-end-point", and "require". I don't see the
point to having "none" and "allow" that sslmode supports. "tls-unique"
and "tls-server-end-point" would _require_ those channel binding modes;
the setting would never be ignored, e.g. if no SSL.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 18, 2018 at 10:56:25AM +0500, Andrey Borodin wrote:

H, Bruce!

11 мая 2018 г., в 20:08, Bruce Momjian <bruce@momjian.us> написал(а):

I expect a torrent of feedback. ;-)

I'm not sure it is usefull in release notes since it is more about API, and not
user-facing change. Just in case.
GiST opclasses now can omit compress and decompress functions. If compress
function is omited, IndexOnlyScan is enabled for opclass without any extra
change.
https://github.com/postgres/postgres/commit/
d3a4f89d8a3e500bd7c0b7a8a8a5ce1b47859128

Uh, we do have this for SP-GiST:

Allow SP-GiST indexes to optionally use compression (Teodor Sigaev,
Heikki Linnakangas, Alexander Korotkov, Nikita Glukhov)

I am unclear how far downt the API stack I should go in documenting
changes like this.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

While we're all griping about omissions from the release notes ...
I think you should have documented that we fixed plpgsql to cope
(or cope better, at least) with intrasession changes in the rowtypes
of composite-type variables. See bug #15203 for the latest in a long
line of user complaints about that --- so it's not like this is
something users don't care about.

regards, tom lane

On May 11, 2018, at 11:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

I expect a torrent of feedback. ;-)

Per feedback from many asynchronous threads, attached is the proposed
patch for the list of major features. I also expect a torrent of feedback. I
will have a corresponding press release for Beta 1 in the very near future.

This being my first doc patch proposal, I tried to follow the formatting I
saw in the existing SGML. Please let me know if I can make improvements.

Thanks,

Jonathan

Hi Bruce.

On Tue, May 15, 2018 at 12:46 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

On 2018/05/15 5:30, Bruce Momjian wrote:

I like it, done.

Thank you.

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

Thanks,
Amit

On Fri, May 18, 2018 at 09:30:22AM -0400, Bruce Momjian wrote:

Good work, but I think the existance of both scram_channel_binding and
channel_binding_mode in libpq is confusing. I am thinking we should
have one channel binding parameter that can take values "prefer",
"tls-unique", "tls-server-end-point", and "require". I don't see the
point to having "none" and "allow" that sslmode supports. "tls-unique"
and "tls-server-end-point" would _require_ those channel binding modes;
the setting would never be ignored, e.g. if no SSL.

I can see the point you are coming at. Do you think that we should
worry about users willing to use specifically tls-server-end-point
(which is something actually in the backend protocol only for JDBC) and
manage a cluster of both v10 and v11 servers? In this case we assume
that "prefer" means always using tls-unique as channel binding, but
there is no way to say "I would prefer channel binding if available,
only with end-point". It would not be that hard to let the application
layer on top of libpq handle that complexity with channel binding
handling, though it makes the life of libpq consumers a bit harder.

Hence, I would actually eliminate "require", as that would be actually
the same as "tls-unique", and the possibility to use an empty value from
the list of options available but add "none" as that's actually the same
as the current empty value. This gives as list:
- tls-unique
- tls-server-end-point
- none
- prefer, which has the meaning of preferring tls-unique
- And prefer-end-point? But thinking why end-point has been added it
would not be worth it, and for tests only the option requiring end-point
is enough.

The previous patch has actually problems with servers using "trust",
"password" and any protocol which can send directly AUTH_REQ_OK as those
could still enforce a downgrade to not use channel binding, so we
actually need to make sure that AUTH_REQ_SASL_FIN has been received when
channel binding is required when looking at a AUTH_REQ_OK message.
--
Michael

On 19 May 2018 at 03:58, Amit Langote <amitlangote09@gmail.com> wrote:

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

While I do think that was a good change, I do think there's much still
left to do to speed up usage of partitioned tables with many
partitions.

I've been working a bit in this area over the past few weeks and with
PG11 I measured a single INSERT into a 10k RANGE partitioned table at
just 84 tps (!), while inserting the same row into a non-partitioned
table was about 11.1k tps. I have patches locally that take this up to
~9.8k tps, which I'll submit for PG12. I'm unsure if we should be
shouting anything from the rooftops about the work done in this area
for PG11, since it's still got a long way to go still before the
feature is usable with higher numbers of partitions. I do think your
change was a good one to make, but I just don't want users to think
that we're done here when we all know that much work remains.

If we're going to add an item in the release notes about this then I
wouldn't object, providing it could be done in a way that indicates
we've not finished here yet, but if that's the case then maybe it's
better to say nothing at all.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, May 12, 2018 at 1:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version here:

http://momjian.us/pgsql_docs/release-11.html

Thanks for preparing the release notes.

Have pg_dump dump all aspects of a database (Haribabu Kommi)

pg_dump and pg_restore, without --clean, no longer dump/restore database
comments and security labels.

There is small change in option name, the option to print database comments
is --create not --clean.

Have libpq's PQhost() always return the actual connected host (Hari Babu)

Previously PQhost() often returned the supplied host parameters, which

could contain

several hosts. The same is true of PQport(), which now returns the actual

port number,

not the multiple supplied port numbers. ACCURATE?

Now PQhost() can return hostaddr also if host is not available. How about
changing as below?

Have libpq's PQhost() always return the actual connected host/hostaddr
(Haribabu Kommi)

hostaddr is returned, when there is no host available with the connected
host.

Regards,
Haribabu Kommi
Fujitsu Australia

On Mon, May 21, 2018 at 4:34 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 19 May 2018 at 03:58, Amit Langote <amitlangote09@gmail.com> wrote:

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

While I do think that was a good change, I do think there's much still
left to do to speed up usage of partitioned tables with many
partitions.

I've been working a bit in this area over the past few weeks and with
PG11 I measured a single INSERT into a 10k RANGE partitioned table at
just 84 tps (!), while inserting the same row into a non-partitioned
table was about 11.1k tps. I have patches locally that take this up to
~9.8k tps, which I'll submit for PG12. I'm unsure if we should be
shouting anything from the rooftops about the work done in this area
for PG11, since it's still got a long way to go still before the
feature is usable with higher numbers of partitions. I do think your
change was a good one to make, but I just don't want users to think
that we're done here when we all know that much work remains.

If we're going to add an item in the release notes about this then I
wouldn't object, providing it could be done in a way that indicates
we've not finished here yet, but if that's the case then maybe it's
better to say nothing at all.

You're right, it surely isn't time yet to make fanfare about this. I
will look forward to being able to review your patches. :-)

Thanks,
Amit

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

Per feedback from many asynchronous threads, attached is the proposed
patch for the list of major features. I also expect a torrent of feedback. I
will have a corresponding press release for Beta 1 in the very near future.
This being my first doc patch proposal, I tried to follow the formatting I
saw in the existing SGML. Please let me know if I can make improvements.

Pushed with some very minor editorialization.

regards, tom lane

On May 21, 2018, at 12:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

Per feedback from many asynchronous threads, attached is the proposed
patch for the list of major features. I also expect a torrent of feedback. I
will have a corresponding press release for Beta 1 in the very near future.
This being my first doc patch proposal, I tried to follow the formatting I
saw in the existing SGML. Please let me know if I can make improvements.

Pushed with some very minor editorialization.

Thanks! I do agree with your editorial, the main goal now is to give exposure to features for testing.

Jonathan

-----Original Message-----
From: Bruce Momjian [mailto:bruce@momjian.us]
I have committed the first draft of the Postgres 11 release notes. I will add more
markup soon. You can view the most current version here:

Hi, there is a small typo.

I think "These function" should be "These functions".
(At the next sentece "these functions" is used.)

Regards,
Ideriha, Takeshi

On Sat, May 19, 2018 at 09:35:57PM +0900, Michael Paquier wrote:

The previous patch has actually problems with servers using "trust",
"password" and any protocol which can send directly AUTH_REQ_OK as those
could still enforce a downgrade to not use channel binding, so we
actually need to make sure that AUTH_REQ_SASL_FIN has been received when
channel binding is required when looking at a AUTH_REQ_OK message.

Okay, so I have digested the previous comments with the attached.
scram_channel_binding is modified as follows (from commit message):
- "prefer", is the default and behaves so as channel binding is used if
available. If the cluster does not support it then it is not used. This
does not protect from downgrade attacks.
- "disable", which is the equivalent of the empty value previously,
disables channel binding.
- "tls-unique" and "tls-server-end-point" make channel binding a
requirement and use the channel binding of the same name for
connection. Note that in this case, if the connection is attempted
without SSL or if server does not support channel binding then libpq
refuses the connection as well.

In order to make sure that a client is not tricked by a "trust"
connection downgrade which sends back directly AUTH_REQ_OK, one way I
have thought about is to check if the client has achieved with a server
a full SASL exchange when receiving this message type, which is
something that we know about as the exchange state is saved in
PGconn->sasl_state.

The patch includes documentation and tests, which check that connections
are refused without SSL and or if the server downgrades authentication
requests.

Thanks,
--
Michael

On Sat, May 19, 2018 at 09:35:57PM +0900, Michael Paquier wrote:

On Fri, May 18, 2018 at 09:30:22AM -0400, Bruce Momjian wrote:

Good work, but I think the existance of both scram_channel_binding and
channel_binding_mode in libpq is confusing. I am thinking we should
have one channel binding parameter that can take values "prefer",
"tls-unique", "tls-server-end-point", and "require". I don't see the
point to having "none" and "allow" that sslmode supports. "tls-unique"
and "tls-server-end-point" would _require_ those channel binding modes;
the setting would never be ignored, e.g. if no SSL.

I can see the point you are coming at. Do you think that we should
worry about users willing to use specifically tls-server-end-point
(which is something actually in the backend protocol only for JDBC) and
manage a cluster of both v10 and v11 servers? In this case we assume
that "prefer" means always using tls-unique as channel binding, but
there is no way to say "I would prefer channel binding if available,
only with end-point". It would not be that hard to let the application
layer on top of libpq handle that complexity with channel binding
handling, though it makes the life of libpq consumers a bit harder.

This is going to be hard enough so let's do whatever we can to simplify
it.

Hence, I would actually eliminate "require", as that would be actually
the same as "tls-unique", and the possibility to use an empty value from
the list of options available but add "none" as that's actually the same
as the current empty value. This gives as list:
- tls-unique
- tls-server-end-point
- none
- prefer, which has the meaning of preferring tls-unique
- And prefer-end-point? But thinking why end-point has been added it
would not be worth it, and for tests only the option requiring end-point
is enough.

Since tls-unique and tls-server-end-point provide the same level of
security, I don't see any value in allowing prefer-tls-server-end-point,
as you stated above.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Tue, May 22, 2018 at 05:22:19PM +0900, Michael Paquier wrote:

On Sat, May 19, 2018 at 09:35:57PM +0900, Michael Paquier wrote:

The previous patch has actually problems with servers using "trust",
"password" and any protocol which can send directly AUTH_REQ_OK as those
could still enforce a downgrade to not use channel binding, so we
actually need to make sure that AUTH_REQ_SASL_FIN has been received when
channel binding is required when looking at a AUTH_REQ_OK message.

Okay, so I have digested the previous comments with the attached.
scram_channel_binding is modified as follows (from commit message):
- "prefer", is the default and behaves so as channel binding is used if
available. If the cluster does not support it then it is not used. This
does not protect from downgrade attacks.
- "disable", which is the equivalent of the empty value previously,
disables channel binding.

Yes, I never liked the 'empty value' idea since I don't know of any
other libpq settings that use that API. "disable" matches "sslmode"
too, which is nice.

In order to make sure that a client is not tricked by a "trust"
connection downgrade which sends back directly AUTH_REQ_OK, one way I
have thought about is to check if the client has achieved with a server
a full SASL exchange when receiving this message type, which is
something that we know about as the exchange state is saved in
PGconn->sasl_state.

I had not thought of 'trust'. I was more worried about the password
hash being downgraded in robustness or passed through a
man-in-the-middle, while the 'trust' does not require. However, you are
right that channel binding, when required, does require the other end to
know the same password as the client knows. Good point.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, May 18, 2018 at 10:28:12AM -0400, Tom Lane wrote:

While we're all griping about omissions from the release notes ...
I think you should have documented that we fixed plpgsql to cope
(or cope better, at least) with intrasession changes in the rowtypes
of composite-type variables. See bug #15203 for the latest in a long
line of user complaints about that --- so it's not like this is
something users don't care about.

It was fixed in this commit, right?

commit 4b93f57999a2ca9b9c9e573ea32ab1aeaa8bf496
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Feb 13 18:52:21 2018 -0500

Make plpgsql use its DTYPE_REC code paths for composite-type variables.

Sure I can add it, once you confirm.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sat, May 19, 2018 at 12:58:04AM +0900, Amit Langote wrote:

Hi Bruce.

On Tue, May 15, 2018 at 12:46 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

On 2018/05/15 5:30, Bruce Momjian wrote:

I like it, done.

Thank you.

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

Uh, we already have this in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Do you want me to add the git commit hash to this release note entry?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, May 21, 2018 at 07:34:18PM +1200, David Rowley wrote:

On 19 May 2018 at 03:58, Amit Langote <amitlangote09@gmail.com> wrote:

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

While I do think that was a good change, I do think there's much still
left to do to speed up usage of partitioned tables with many
partitions.

I've been working a bit in this area over the past few weeks and with
PG11 I measured a single INSERT into a 10k RANGE partitioned table at
just 84 tps (!), while inserting the same row into a non-partitioned
table was about 11.1k tps. I have patches locally that take this up to
~9.8k tps, which I'll submit for PG12. I'm unsure if we should be

Yikes! I think the question is whether we need to _remove_ the item I
just posted that is already in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 2018/05/23 10:16, Bruce Momjian wrote:

On Sat, May 19, 2018 at 12:58:04AM +0900, Amit Langote wrote:

Hi Bruce.

On Tue, May 15, 2018 at 12:46 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

On 2018/05/15 5:30, Bruce Momjian wrote:

I like it, done.

Thank you.

I wonder what you think about including this little performance item:

/messages/by-id/E1eotSQ-0005V0-LV@gemulon.postgresql.org

especially considering the part of the commit message which states

...Still, testing shows
that this makes single-row inserts significantly faster on a table
with many partitions without harming the bulk-insert case.

I recall seeing those inserts being as much as 2x faster as partition
count grows beyond hundreds. One might argue that we should think
about publicizing this only after we've dealt with the
lock-all-partitions issue that's also mentioned in the commit message
which is still a significant portion of the time spent and I'm totally
fine with that.

Uh, we already have this in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Do you want me to add the git commit hash to this release note entry?

I suppose you meant the above as an entry for performance improvement of
partition "pruning". The commit I quoted is concerned with making "tuple
routing" a bit faster, but as David said that's not making it as fast as
it could really be. So, we should hold off from touting it as an
improvement at this point and I have to agree. Sorry for the noise.

Thanks,
Amit

On Mon, May 21, 2018 at 06:28:36PM +1000, Haribabu Kommi wrote:

On Sat, May 12, 2018 at 1:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes.� I
will add more markup soon.� You can view the most current version here:

� � � � http://momjian.us/pgsql_docs/release-11.html

Thanks for preparing the release notes.

Have pg_dump dump all aspects of a database (Haribabu Kommi)

pg_dump and pg_restore, without --clean, no longer dump/restore database
comments and security labels.

There is small change in option name, the option to print database comments is
--create not --clean.

Uh, what about security labels?

Have libpq's PQhost() always return the actual connected host (Hari Babu)

Previously PQhost() often returned the supplied host parameters, which could

contain

several hosts. The same is true of PQport(), which now returns the actual port

number,

not the multiple supplied port numbers. ACCURATE?

Now PQhost() can return hostaddr also if host is not available. How about
changing as below?

Have libpq's PQhost() always return the actual connected host/hostaddr
(Haribabu Kommi)

hostaddr is returned, when there is no host available with the connected host.

OK, here is the new text:

Have libpq's <link
linkend="libpq-pqhost"><function>PQhost()</function></link>
always return the actual connected host (Hari Babu)

Previously <function>PQhost()</function> often returned the
supplied host parameters, which could contain several hosts.
-> It will now also return the host's IP address if the host name was
-> not supplied. The same is true of <function>PQport()</function>,
which now returns the actual port number, not the multiple supplied
port numbers.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 23, 2018 at 10:28:41AM +0900, Amit Langote wrote:

Uh, we already have this in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Do you want me to add the git commit hash to this release note entry?

I suppose you meant the above as an entry for performance improvement of
partition "pruning". The commit I quoted is concerned with making "tuple
routing" a bit faster, but as David said that's not making it as fast as
it could really be. So, we should hold off from touting it as an
improvement at this point and I have to agree. Sorry for the noise.

OK, no problem. So _finding_ the rows is faster, but adding rows to
partitioned tables with many partitions is still slow, got it.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sat, May 19, 2018 at 2:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

While we're all griping about omissions from the release notes ...

Hi Bruce,

<para>
Add support for <productname>ARMv8</productname> hardware
<acronym>CRC</acronym> calculations (Yuqi Gu, Heikki Linnakangas)
</para>

Could I please ask for a third author credit for this one? See
commits 1c72ec6 (and follow-up a7a7387) which extended ARM CRC support
to non-Linux systems.

--
Thomas Munro
http://www.enterprisedb.com

On Wed, May 23, 2018 at 01:37:22PM +1200, Thomas Munro wrote:

On Sat, May 19, 2018 at 2:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

While we're all griping about omissions from the release notes ...

Hi Bruce,

<para>
Add support for <productname>ARMv8</productname> hardware
<acronym>CRC</acronym> calculations (Yuqi Gu, Heikki Linnakangas)
</para>

Could I please ask for a third author credit for this one? See
commits 1c72ec6 (and follow-up a7a7387) which extended ARM CRC support
to non-Linux systems.

Done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On 2018/05/23 10:36, Bruce Momjian wrote:

On Wed, May 23, 2018 at 10:28:41AM +0900, Amit Langote wrote:

Uh, we already have this in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Do you want me to add the git commit hash to this release note entry?

I suppose you meant the above as an entry for performance improvement of
partition "pruning". The commit I quoted is concerned with making "tuple
routing" a bit faster, but as David said that's not making it as fast as
it could really be. So, we should hold off from touting it as an
improvement at this point and I have to agree. Sorry for the noise.

OK, no problem. So _finding_ the rows is faster, but adding rows to
partitioned tables with many partitions is still slow, got it.

Yeah.

To be honest, even _finding_ is not yet performing the best it could be,
which I guess David would chime in to say. We know what needs to be fixed
to get the close-to-ideal performance there, but didn't have time to do it
for PG 11. To clarify, what changed is that we replaced constraint
exclusion, which has to consider the partition constraint of *all*
partitions individually, as an algorithm for partition pruning by a faster
alternative that only looks at the parent table's partition descriptor.
That gives a good boost but that's not the end of it. Moreover, addition
of this new pruning algorithm enabled the development of execution time
pruning which is a completely new feature.

Thanks,
Amit

On Wed, May 23, 2018 at 11:34 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 21, 2018 at 06:28:36PM +1000, Haribabu Kommi wrote:

On Sat, May 12, 2018 at 1:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

I have committed the first draft of the Postgres 11 release notes. I
will add more markup soon. You can view the most current version

here:

http://momjian.us/pgsql_docs/release-11.html

Thanks for preparing the release notes.

Have pg_dump dump all aspects of a database (Haribabu Kommi)

pg_dump and pg_restore, without --clean, no longer dump/restore database
comments and security labels.

There is small change in option name, the option to print database

comments is

--create not --clean.

Uh, what about security labels?

Yes, security labels also gets dumped only with --create option.

Have libpq's PQhost() always return the actual connected host (Hari

Babu)

Previously PQhost() often returned the supplied host parameters, which

could

contain

several hosts. The same is true of PQport(), which now returns the

actual port

number,

not the multiple supplied port numbers. ACCURATE?

Now PQhost() can return hostaddr also if host is not available. How about
changing as below?

Have libpq's PQhost() always return the actual connected host/hostaddr
(Haribabu Kommi)

hostaddr is returned, when there is no host available with the connected

host.

OK, here is the new text:

Have libpq's <link
linkend="libpq-pqhost"><function>PQhost()</function></link>
always return the actual connected host (Hari Babu)

Can you update the author name as (Haribabu Kommi) to make it consistent
with other entries.

Previously <function>PQhost()</function> often returned the
supplied host parameters, which could contain several hosts.
-> It will now also return the host's IP address if the host name was
-> not supplied. The same is true of <function>PQport()</function>,
which now returns the actual port number, not the multiple supplied
port numbers.

That looks good to me.

Regards,
Haribabu Kommi
Fujitsu Australia

On 23 May 2018 at 13:18, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 21, 2018 at 07:34:18PM +1200, David Rowley wrote:

I've been working a bit in this area over the past few weeks and with
PG11 I measured a single INSERT into a 10k RANGE partitioned table at
just 84 tps (!), while inserting the same row into a non-partitioned
table was about 11.1k tps. I have patches locally that take this up to
~9.8k tps, which I'll submit for PG12. I'm unsure if we should be

Yikes! I think the question is whether we need to _remove_ the item I
just posted that is already in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Well, partition elimination/pruning and tuple routing are not the same
thing. Pruning saves us scanning partitions that can't contain
matching tuples, whereas routing finds a home for a specific tuple.

Amit's work to improve partition elimination certainly is much faster
than constraint exclusion. It's not the last thing we'll ever do to
speed up the planning of queries for partitioned tables but it is a
very good start, and without it, run-time pruning would not be
possible.

I'd say the release notes in this regard don't claim anything that's
untrue. They look fine to me. Thanks for working on them!

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, May 23, 2018 at 11:55:29AM +1000, Haribabu Kommi wrote:

Yes, security labels also gets dumped only with --create option.

OK.

Can you update the author name as (Haribabu Kommi) to make it consistent
with other entries.

Done. Applied patch attached.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Wed, May 23, 2018 at 02:06:15PM +1200, David Rowley wrote:

On 23 May 2018 at 13:18, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 21, 2018 at 07:34:18PM +1200, David Rowley wrote:

I've been working a bit in this area over the past few weeks and with
PG11 I measured a single INSERT into a 10k RANGE partitioned table at
just 84 tps (!), while inserting the same row into a non-partitioned
table was about 11.1k tps. I have patches locally that take this up to
~9.8k tps, which I'll submit for PG12. I'm unsure if we should be

Yikes! I think the question is whether we need to _remove_ the item I
just posted that is already in the release notes:

Allow faster partition elimination during query processing (Amit
Langote, David Rowley, Dilip Kumar)

This speeds access to partitioned tables with many partitions.

Well, partition elimination/pruning and tuple routing are not the same
thing. Pruning saves us scanning partitions that can't contain
matching tuples, whereas routing finds a home for a specific tuple.

I just suspected they would use the same algorithm.

Amit's work to improve partition elimination certainly is much faster
than constraint exclusion. It's not the last thing we'll ever do to
speed up the planning of queries for partitioned tables but it is a
very good start, and without it, run-time pruning would not be
possible.

I'd say the release notes in this regard don't claim anything that's
untrue. They look fine to me. Thanks for working on them!

OK.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Bruce Momjian <bruce@momjian.us> writes:

On Fri, May 18, 2018 at 10:28:12AM -0400, Tom Lane wrote:

While we're all griping about omissions from the release notes ...
I think you should have documented that we fixed plpgsql to cope
(or cope better, at least) with intrasession changes in the rowtypes
of composite-type variables.

It was fixed in this commit, right?
commit 4b93f57999a2ca9b9c9e573ea32ab1aeaa8bf496

That was the main one, anyway.

regards, tom lane

On Tue, May 22, 2018 at 08:19:34PM -0400, Bruce Momjian wrote:

Since tls-unique and tls-server-end-point provide the same level of
security, I don't see any value in allowing prefer-tls-server-end-point,
as you stated above.

Thanks. We are on the same line for this portion then.
--
Michael

On 22/05/18 11:22, Michael Paquier wrote:

On Sat, May 19, 2018 at 09:35:57PM +0900, Michael Paquier wrote:

The previous patch has actually problems with servers using "trust",
"password" and any protocol which can send directly AUTH_REQ_OK as those
could still enforce a downgrade to not use channel binding, so we
actually need to make sure that AUTH_REQ_SASL_FIN has been received when
channel binding is required when looking at a AUTH_REQ_OK message.

Okay, so I have digested the previous comments with the attached.
scram_channel_binding is modified as follows (from commit message):
- "prefer", is the default and behaves so as channel binding is used if
available. If the cluster does not support it then it is not used. This
does not protect from downgrade attacks.
- "disable", which is the equivalent of the empty value previously,
disables channel binding.
- "tls-unique" and "tls-server-end-point" make channel binding a
requirement and use the channel binding of the same name for
connection. Note that in this case, if the connection is attempted
without SSL or if server does not support channel binding then libpq
refuses the connection as well.