Skip to content

Commit fc08c0a

Browse files
dmpgprodmpgpro
committed
Merge branch 'PGPRO9_6' into PGPROEE9_6_ALPHA
Conflicts: doc/src/sgml/filelist.sgml doc/src/sgml/release-pro-9.6.sgml
2 parents 22c5a59 + 4e4f227 commit fc08c0a

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

58 files changed

+1653
-361
lines changed

‎configure

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2754,7 +2754,7 @@ else
27542754
fi
27552755

27562756

2757-
PGPRO_VERSION="$PACKAGE_VERSION.1"
2757+
PGPRO_VERSION="$PACKAGE_VERSION.2"
27582758
PGPRO_PACKAGE_NAME="PostgresPro"
27592759
PGPRO_EDITION="enterprise"
27602760

‎configure.in

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ AC_DEFINE_UNQUOTED(PG_MAJORVERSION, "$PG_MAJORVERSION", [PostgreSQL major versio
3838
PGAC_ARG_REQ(with, extra-version, [STRING], [append STRING to version],
3939
[PG_VERSION="$PACKAGE_VERSION$withval"],
4040
[PG_VERSION="$PACKAGE_VERSION"])
41-
PGPRO_VERSION="$PACKAGE_VERSION.1"
41+
PGPRO_VERSION="$PACKAGE_VERSION.2"
4242
PGPRO_PACKAGE_NAME="PostgresPro"
4343
PGPRO_EDITION="enterprise"
4444
AC_SUBST(PGPRO_PACKAGE_NAME)

‎contrib/test_decoding/expected/spill.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -164,7 +164,7 @@ SAVEPOINT s2;
164164
INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
165165
RELEASE SAVEPOINT s2;
166166
COMMIT;
167-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
167+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
168168
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
169169
GROUP BY 1 ORDER BY 1;
170170
regexp_split_to_array | count | array_agg | array_agg
@@ -182,7 +182,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
182182
RELEASE SAVEPOINT s2;
183183
RELEASE SAVEPOINT s1;
184184
COMMIT;
185-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
185+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
186186
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
187187
GROUP BY 1 ORDER BY 1;
188188
regexp_split_to_array | count | array_agg | array_agg
@@ -200,7 +200,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
200200
RELEASE SAVEPOINT s2;
201201
RELEASE SAVEPOINT s1;
202202
COMMIT;
203-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
203+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
204204
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
205205
GROUP BY 1 ORDER BY 1;
206206
regexp_split_to_array | count | array_agg | array_agg
@@ -218,7 +218,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
218218
RELEASE SAVEPOINT s2;
219219
RELEASE SAVEPOINT s1;
220220
COMMIT;
221-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
221+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
222222
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
223223
GROUP BY 1 ORDER BY 1;
224224
regexp_split_to_array | count | array_agg | array_agg
@@ -238,7 +238,7 @@ SAVEPOINT s3;
238238
INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
239239
RELEASE SAVEPOINT s1;
240240
COMMIT;
241-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
241+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
242242
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
243243
GROUP BY 1 ORDER BY 1;
244244
regexp_split_to_array | count | array_agg | array_agg

‎contrib/test_decoding/sql/spill.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ SAVEPOINT s2;
116116
INSERT INTO spill_test SELECT 'serialize-subsmall-subbig--2:'||g.i FROM generate_series(2, 5001) g(i);
117117
RELEASE SAVEPOINT s2;
118118
COMMIT;
119-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
119+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
120120
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
121121
GROUP BY 1 ORDER BY 1;
122122

@@ -129,7 +129,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbig--2:'||g.i FROM gen
129129
RELEASE SAVEPOINT s2;
130130
RELEASE SAVEPOINT s1;
131131
COMMIT;
132-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
132+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
133133
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
134134
GROUP BY 1 ORDER BY 1;
135135

@@ -142,7 +142,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subbig-subsmall--2:'||g.i FROM g
142142
RELEASE SAVEPOINT s2;
143143
RELEASE SAVEPOINT s1;
144144
COMMIT;
145-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
145+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
146146
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
147147
GROUP BY 1 ORDER BY 1;
148148

@@ -155,7 +155,7 @@ INSERT INTO spill_test SELECT 'serialize-nested-subsmall-subbig--2:'||g.i FROM g
155155
RELEASE SAVEPOINT s2;
156156
RELEASE SAVEPOINT s1;
157157
COMMIT;
158-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
158+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
159159
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
160160
GROUP BY 1 ORDER BY 1;
161161

@@ -170,7 +170,7 @@ SAVEPOINT s3;
170170
INSERT INTO spill_test SELECT 'serialize-nested-subbig-subbigabort-subbig-3:'||g.i FROM generate_series(5001, 10000) g(i);
171171
RELEASE SAVEPOINT s1;
172172
COMMIT;
173-
SELECT (regexp_split_to_array(data, ':'))[4], COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
173+
SELECT (regexp_split_to_array(data, ':'))[4] COLLATE "C", COUNT(*), (array_agg(data))[1], (array_agg(data))[count(*)]
174174
FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL) WHERE data ~ 'INSERT'
175175
GROUP BY 1 ORDER BY 1;
176176

‎doc/src/sgml/config.sgml

Lines changed: 23 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1048,7 +1048,8 @@ include_dir 'conf.d'
10481048
in the <application>OpenSSL</> package for the syntax of this setting
10491049
and a list of supported values. The default value is
10501050
<literal>HIGH:MEDIUM:+3DES:!aNULL</>. It is usually reasonable,
1051-
unless you have specific security requirements.
1051+
unless you have specific security requirements. This parameter can only
1052+
be set at server start.
10521053
</para>
10531054

10541055
<para>
@@ -1120,7 +1121,8 @@ include_dir 'conf.d'
11201121
<listitem>
11211122
<para>
11221123
Specifies whether to use the server's SSL cipher preferences, rather
1123-
than the client's. The default is true.
1124+
than the client's. The default is true. This parameter can only be
1125+
set at server start.
11241126
</para>
11251127

11261128
<para>
@@ -1144,7 +1146,8 @@ include_dir 'conf.d'
11441146
Specifies the name of the curve to use in <acronym>ECDH</> key
11451147
exchange. It needs to be supported by all clients that connect.
11461148
It does not need to be same curve as used by server's Elliptic
1147-
Curve key. The default is <literal>prime256v1</>.
1149+
Curve key. The default is <literal>prime256v1</>. This parameter
1150+
can only be set at server start.
11481151
</para>
11491152

11501153
<para>
@@ -1914,10 +1917,10 @@ include_dir 'conf.d'
19141917
<xref linkend="guc-shared-buffers">, but smaller than the OS's page
19151918
cache, where performance might degrade. This setting may have no
19161919
effect on some platforms. The valid range is between
1917-
<literal>0</literal>, which disables controlled writeback, and
1920+
<literal>0</literal>, which disables forced writeback, and
19181921
<literal>2MB</literal>. The default is <literal>512kB</> on Linux,
1919-
<literal>0</> elsewhere. (Non-default values of
1920-
<symbol>BLCKSZ</symbol> change the default and maximum.)
1922+
<literal>0</> elsewhere. (If <symbol>BLCKSZ</symbol> is not 8kB,
1923+
the default and maximum values scale proportionally to it.)
19211924
This parameter can only be set in the <filename>postgresql.conf</>
19221925
file or on the server command line.
19231926
</para>
@@ -2066,10 +2069,10 @@ include_dir 'conf.d'
20662069
that are bigger than <xref linkend="guc-shared-buffers">, but smaller
20672070
than the OS's page cache, where performance might degrade. This
20682071
setting may have no effect on some platforms. The valid range is
2069-
between <literal>0</literal>, which disables controlled writeback,
2070-
and <literal>2MB</literal>. The default is <literal>0</> (i.e. no
2071-
flush control). (Non-default values of <symbol>BLCKSZ</symbol>
2072-
change the maximum.)
2072+
between <literal>0</literal>, which disables forced writeback,
2073+
and <literal>2MB</literal>. The default is <literal>0</>, i.e., no
2074+
forced writeback. (If <symbol>BLCKSZ</symbol> is not 8kB,
2075+
the maximum value scales proportionally to it.)
20732076
</para>
20742077
</listitem>
20752078
</varlistentry>
@@ -2529,10 +2532,11 @@ include_dir 'conf.d'
25292532
<para>
25302533
Specifies how often the WAL writer flushes WAL. After flushing WAL it
25312534
sleeps for <varname>wal_writer_delay</> milliseconds, unless woken up
2532-
by an asynchronously committing transaction. In case the last flush
2535+
by an asynchronously committing transaction. If the last flush
25332536
happened less than <varname>wal_writer_delay</> milliseconds ago and
25342537
less than <varname>wal_writer_flush_after</> bytes of WAL have been
2535-
produced since, WAL is only written to the OS, not flushed to disk.
2538+
produced since, then WAL is only written to the operating system, not
2539+
flushed to disk.
25362540
The default value is 200 milliseconds (<literal>200ms</>). Note that
25372541
on many systems, the effective resolution of sleep delays is 10
25382542
milliseconds; setting <varname>wal_writer_delay</> to a value that is
@@ -2551,12 +2555,12 @@ include_dir 'conf.d'
25512555
</term>
25522556
<listitem>
25532557
<para>
2554-
Specifies how often the WAL writer flushes WAL. In case the last flush
2558+
Specifies how often the WAL writer flushes WAL. If the last flush
25552559
happened less than <varname>wal_writer_delay</> milliseconds ago and
25562560
less than <varname>wal_writer_flush_after</> bytes of WAL have been
2557-
produced since, WAL is only written to the OS, not flushed to disk.
2558-
If <varname>wal_writer_flush_after</> is set to <literal>0</> WAL is
2559-
flushed every time the WAL writer has written WAL. The default is
2561+
produced since, then WAL is only written to the operating system, not
2562+
flushed to disk. If <varname>wal_writer_flush_after</> is set
2563+
to <literal>0</> then WAL data is flushed immediately. The default is
25602564
<literal>1MB</literal>. This parameter can only be set in the
25612565
<filename>postgresql.conf</> file or on the server command line.
25622566
</para>
@@ -2676,10 +2680,10 @@ include_dir 'conf.d'
26762680
that are bigger than <xref linkend="guc-shared-buffers">, but smaller
26772681
than the OS's page cache, where performance might degrade. This
26782682
setting may have no effect on some platforms. The valid range is
2679-
between <literal>0</literal>, which disables controlled writeback,
2683+
between <literal>0</literal>, which disables forced writeback,
26802684
and <literal>2MB</literal>. The default is <literal>256kB</> on
2681-
Linux, <literal>0</> elsewhere. (Non-default values of
2682-
<symbol>BLCKSZ</symbol> change the default and maximum.)
2685+
Linux, <literal>0</> elsewhere. (If <symbol>BLCKSZ</symbol> is not
2686+
8kB, the default and maximum values scale proportionally to it.)
26832687
This parameter can only be set in the <filename>postgresql.conf</>
26842688
file or on the server command line.
26852689
</para>

‎doc/src/sgml/contrib.sgml

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -110,13 +110,16 @@ CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;
110110
&btree-gist;
111111
&chkpass;
112112
&citext;
113+
&contrib-spi;
113114
&cube;
114115
&dblink;
115116
&dict-int;
116117
&dict-xsyn;
117118
&dump-stat;
118119
&earthdistance;
120+
&fasttrun;
119121
&file-fdw;
122+
&fulleq;
120123
&fuzzystrmatch;
121124
&hstore;
122125
&hunspell-dict;
@@ -126,6 +129,8 @@ CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;
126129
&jsquery;
127130
&lo;
128131
&ltree;
132+
&mchar;
133+
&online-analyze;
129134
&pageinspect;
130135
&passwordcheck;
131136
&pgarman;
@@ -142,12 +147,12 @@ CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;
142147
&pgtrgm;
143148
&pgvariables;
144149
&pgvisibility;
150+
&plantuner;
145151
&postgres-fdw;
146152
&rum;
147153
&seg;
148154
&sepgsql;
149155
&shared-ispell;
150-
&contrib-spi;
151156
&sr-plan;
152157
&sslinfo;
153158
&tablefunc;
@@ -159,7 +164,7 @@ CREATE EXTENSION <replaceable>module_name</> FROM unpackaged;
159164
&unaccent;
160165
&uuid-ossp;
161166
&xml2;
162-
167+
163168
</appendix>
164169

165170
<!--

‎doc/src/sgml/dml.sgml

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,18 @@ INSERT INTO products (product_no, name, price) VALUES
102102
</programlisting>
103103
</para>
104104

105+
<para>
106+
It is also possible to insert the result of a query (which might be no
107+
rows, one row, or many rows):
108+
<programlisting>
109+
INSERT INTO products (product_no, name, price)
110+
SELECT product_no, name, price FROM new_products
111+
WHERE release_date = 'today';
112+
</programlisting>
113+
This provides the full power of the SQL query mechanism (<xref
114+
linkend="queries">) for computing the rows to be inserted.
115+
</para>
116+
105117
<tip>
106118
<para>
107119
When inserting a lot of data at the same time, considering using
@@ -252,4 +264,91 @@ DELETE FROM products;
252264
then all rows in the table will be deleted! Caveat programmer.
253265
</para>
254266
</sect1>
267+
268+
<sect1 id="dml-returning">
269+
<title>Returning Data From Modified Rows</title>
270+
271+
<indexterm zone="dml-returning">
272+
<primary>RETURNING</primary>
273+
</indexterm>
274+
275+
<indexterm zone="dml-returning">
276+
<primary>INSERT</primary>
277+
<secondary>RETURNING</secondary>
278+
</indexterm>
279+
280+
<indexterm zone="dml-returning">
281+
<primary>UPDATE</primary>
282+
<secondary>RETURNING</secondary>
283+
</indexterm>
284+
285+
<indexterm zone="dml-returning">
286+
<primary>DELETE</primary>
287+
<secondary>RETURNING</secondary>
288+
</indexterm>
289+
290+
<para>
291+
Sometimes it is useful to obtain data from modified rows while they are
292+
being manipulated. The <command>INSERT</>, <command>UPDATE</>,
293+
and <command>DELETE</> commands all have an
294+
optional <literal>RETURNING</> clause that supports this. Use
295+
of <literal>RETURNING</> avoids performing an extra database query to
296+
collect the data, and is especially valuable when it would otherwise be
297+
difficult to identify the modified rows reliably.
298+
</para>
299+
300+
<para>
301+
The allowed contents of a <literal>RETURNING</> clause are the same as
302+
a <command>SELECT</> command's output list
303+
(see <xref linkend="queries-select-lists">). It can contain column
304+
names of the command's target table, or value expressions using those
305+
columns. A common shorthand is <literal>RETURNING *</>, which selects
306+
all columns of the target table in order.
307+
</para>
308+
309+
<para>
310+
In an <command>INSERT</>, the data available to <literal>RETURNING</> is
311+
the row as it was inserted. This is not so useful in trivial inserts,
312+
since it would just repeat the data provided by the client. But it can
313+
be very handy when relying on computed default values. For example,
314+
when using a <link linkend="datatype-serial"><type>serial</></link>
315+
column to provide unique identifiers, <literal>RETURNING</> can return
316+
the ID assigned to a new row:
317+
<programlisting>
318+
CREATE TABLE users (firstname text, lastname text, id serial primary key);
319+
320+
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
321+
</programlisting>
322+
The <literal>RETURNING</> clause is also very useful
323+
with <literal>INSERT ... SELECT</>.
324+
</para>
325+
326+
<para>
327+
In an <command>UPDATE</>, the data available to <literal>RETURNING</> is
328+
the new content of the modified row. For example:
329+
<programlisting>
330+
UPDATE products SET price = price * 1.10
331+
WHERE price &lt;= 99.99
332+
RETURNING name, price AS new_price;
333+
</programlisting>
334+
</para>
335+
336+
<para>
337+
In a <command>DELETE</>, the data available to <literal>RETURNING</> is
338+
the content of the deleted row. For example:
339+
<programlisting>
340+
DELETE FROM products
341+
WHERE obsoletion_date = 'today'
342+
RETURNING *;
343+
</programlisting>
344+
</para>
345+
346+
<para>
347+
If there are triggers (<xref linkend="triggers">) on the target table,
348+
the data available to <literal>RETURNING</> is the row as modified by
349+
the triggers. Thus, inspecting columns computed by triggers is another
350+
common use-case for <literal>RETURNING</>.
351+
</para>
352+
353+
</sect1>
255354
</chapter>

0 commit comments

Comments
 (0)