Skip to content

Commit 9ab42f3

Browse files
vbwagnervbwagner
committed
Merge branch 'REL9_5_STABLE' into PGPRO9_5
Merge pre-release of 9.5.1
2 parents c5ba352 + 65f1510 commit 9ab42f3

File tree

34 files changed

+3511
-151
lines changed

34 files changed

+3511
-151
lines changed

‎contrib/hstore/expected/hstore.out

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1466,10 +1466,10 @@ select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=
14661466
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
14671467
(1 row)
14681468

1469-
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1470-
hstore_to_json_loose
1471-
------------------------------------------------------------------------------------------
1472-
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}
1469+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
1470+
hstore_to_json_loose
1471+
-------------------------------------------------------------------------------------------------------------
1472+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "h": "2016-01-01", "a key": 1}
14731473
(1 row)
14741474

14751475
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
@@ -1484,10 +1484,10 @@ select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=
14841484
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
14851485
(1 row)
14861486

1487-
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1488-
hstore_to_jsonb_loose
1489-
---------------------------------------------------------------------------------------
1490-
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1}
1487+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
1488+
hstore_to_jsonb_loose
1489+
----------------------------------------------------------------------------------------------------------
1490+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "h": "2016-01-01", "a key": 1}
14911491
(1 row)
14921492

14931493
create table test_json_agg (f1 text, f2 hstore);

‎contrib/hstore/sql/hstore.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -334,11 +334,11 @@ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe
334334
-- json and jsonb
335335
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
336336
select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json);
337-
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
337+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
338338

339339
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
340340
select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb);
341-
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
341+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
342342

343343
create table test_json_agg (f1 text, f2 hstore);
344344
insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),

‎contrib/postgres_fdw/deparse.c

Lines changed: 11 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,7 @@ static void deparseTargetList(StringInfo buf,
111111
PlannerInfo *root,
112112
Index rtindex,
113113
Relation rel,
114+
bool is_returning,
114115
Bitmapset *attrs_used,
115116
List **retrieved_attrs);
116117
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
@@ -721,7 +722,7 @@ deparseSelectSql(StringInfo buf,
721722
* Construct SELECT list
722723
*/
723724
appendStringInfoString(buf, "SELECT ");
724-
deparseTargetList(buf, root, baserel->relid, rel, attrs_used,
725+
deparseTargetList(buf, root, baserel->relid, rel, false, attrs_used,
725726
retrieved_attrs);
726727

727728
/*
@@ -735,7 +736,8 @@ deparseSelectSql(StringInfo buf,
735736

736737
/*
737738
* Emit a target list that retrieves the columns specified in attrs_used.
738-
* This is used for both SELECT and RETURNING targetlists.
739+
* This is used for both SELECT and RETURNING targetlists; the is_returning
740+
* parameter is true only for a RETURNING targetlist.
739741
*
740742
* The tlist text is appended to buf, and we also create an integer List
741743
* of the columns being retrieved, which is returned to *retrieved_attrs.
@@ -745,6 +747,7 @@ deparseTargetList(StringInfo buf,
745747
PlannerInfo *root,
746748
Index rtindex,
747749
Relation rel,
750+
bool is_returning,
748751
Bitmapset *attrs_used,
749752
List **retrieved_attrs)
750753
{
@@ -774,6 +777,8 @@ deparseTargetList(StringInfo buf,
774777
{
775778
if (!first)
776779
appendStringInfoString(buf, ", ");
780+
else if (is_returning)
781+
appendStringInfoString(buf, " RETURNING ");
777782
first = false;
778783

779784
deparseColumnRef(buf, rtindex, i, root);
@@ -791,6 +796,8 @@ deparseTargetList(StringInfo buf,
791796
{
792797
if (!first)
793798
appendStringInfoString(buf, ", ");
799+
else if (is_returning)
800+
appendStringInfoString(buf, " RETURNING ");
794801
first = false;
795802

796803
appendStringInfoString(buf, "ctid");
@@ -800,7 +807,7 @@ deparseTargetList(StringInfo buf,
800807
}
801808

802809
/* Don't generate bad syntax if no undropped columns */
803-
if (first)
810+
if (first && !is_returning)
804811
appendStringInfoString(buf, "NULL");
805812
}
806813

@@ -1019,11 +1026,8 @@ deparseReturningList(StringInfo buf, PlannerInfo *root,
10191026
}
10201027

10211028
if (attrs_used != NULL)
1022-
{
1023-
appendStringInfoString(buf, " RETURNING ");
1024-
deparseTargetList(buf, root, rtindex, rel, attrs_used,
1029+
deparseTargetList(buf, root, rtindex, rel, true, attrs_used,
10251030
retrieved_attrs);
1026-
}
10271031
else
10281032
*retrieved_attrs = NIL;
10291033
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2232,6 +2232,59 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
22322232
1104 | 204 | ddd |
22332233
(819 rows)
22342234

2235+
EXPLAIN (verbose, costs off)
2236+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
2237+
QUERY PLAN
2238+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2239+
Insert on public.ft2
2240+
Output: (tableoid)::regclass
2241+
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
2242+
-> Result
2243+
Output: 9999, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10), NULL::user_enum
2244+
(5 rows)
2245+
2246+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
2247+
tableoid
2248+
----------
2249+
ft2
2250+
(1 row)
2251+
2252+
EXPLAIN (verbose, costs off)
2253+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
2254+
QUERY PLAN
2255+
-------------------------------------------------------------------------------------------------------------------
2256+
Update on public.ft2
2257+
Output: (tableoid)::regclass
2258+
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1
2259+
-> Foreign Scan on public.ft2
2260+
Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid
2261+
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
2262+
(6 rows)
2263+
2264+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
2265+
tableoid
2266+
----------
2267+
ft2
2268+
(1 row)
2269+
2270+
EXPLAIN (verbose, costs off)
2271+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
2272+
QUERY PLAN
2273+
------------------------------------------------------------------------------------
2274+
Delete on public.ft2
2275+
Output: (tableoid)::regclass
2276+
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
2277+
-> Foreign Scan on public.ft2
2278+
Output: ctid
2279+
Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
2280+
(6 rows)
2281+
2282+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
2283+
tableoid
2284+
----------
2285+
ft2
2286+
(1 row)
2287+
22352288
-- Test that trigger on remote table works as expected
22362289
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
22372290
BEGIN

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -358,6 +358,15 @@ EXPLAIN (verbose, costs off)
358358
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
359359
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
360360
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
361+
EXPLAIN (verbose, costs off)
362+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
363+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
364+
EXPLAIN (verbose, costs off)
365+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
366+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
367+
EXPLAIN (verbose, costs off)
368+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
369+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
361370

362371
-- Test that trigger on remote table works as expected
363372
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$

‎doc/src/sgml/ddl.sgml

Lines changed: 29 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -495,8 +495,8 @@ CREATE TABLE products (
495495
</indexterm>
496496

497497
<para>
498-
Unique constraints ensure that the data contained in a column or a
499-
group of columns is unique with respect to all the rows in the
498+
Unique constraints ensure that the data contained in a column, or a
499+
group of columns, is unique among all the rows in the
500500
table. The syntax is:
501501
<programlisting>
502502
CREATE TABLE products (
@@ -518,8 +518,8 @@ CREATE TABLE products (
518518
</para>
519519

520520
<para>
521-
If a unique constraint refers to a group of columns, the columns
522-
are listed separated by commas:
521+
To define a unique constraint for a group of columns, write it as a
522+
table constraint with the column names separated by commas:
523523
<programlisting>
524524
CREATE TABLE example (
525525
a integer,
@@ -546,9 +546,10 @@ CREATE TABLE products (
546546

547547
<para>
548548
Adding a unique constraint will automatically create a unique B-tree
549-
index on the column or group of columns used in the constraint.
550-
A uniqueness constraint on only some rows can be enforced by creating
551-
a <link linkend="indexes-partial">partial index</link>.
549+
index on the column or group of columns listed in the constraint.
550+
A uniqueness restriction covering only some rows cannot be written as
551+
a unique constraint, but it is possible to enforce such a restriction by
552+
creating a unique <link linkend="indexes-partial">partial index</link>.
552553
</para>
553554

554555
<indexterm>
@@ -557,10 +558,10 @@ CREATE TABLE products (
557558
</indexterm>
558559

559560
<para>
560-
In general, a unique constraint is violated when there is more than
561+
In general, a unique constraint is violated if there is more than
561562
one row in the table where the values of all of the
562563
columns included in the constraint are equal.
563-
However, two null values are not considered equal in this
564+
However, two null values are never considered equal in this
564565
comparison. That means even in the presence of a
565566
unique constraint it is possible to store duplicate
566567
rows that contain a null value in at least one of the constrained
@@ -584,8 +585,9 @@ CREATE TABLE products (
584585
</indexterm>
585586

586587
<para>
587-
Technically, a primary key constraint is simply a combination of a
588-
unique constraint and a not-null constraint. So, the following
588+
A primary key constraint indicates that a column, or group of columns,
589+
can be used as a unique identifier for rows in the table. This
590+
requires that the values be both unique and not null. So, the following
589591
two table definitions accept the same data:
590592
<programlisting>
591593
CREATE TABLE products (
@@ -605,7 +607,7 @@ CREATE TABLE products (
605607
</para>
606608

607609
<para>
608-
Primary keys can also constrain more than one column; the syntax
610+
Primary keys can span more than one column; the syntax
609611
is similar to unique constraints:
610612
<programlisting>
611613
CREATE TABLE example (
@@ -617,32 +619,32 @@ CREATE TABLE example (
617619
</programlisting>
618620
</para>
619621

620-
<para>
621-
A primary key indicates that a column or group of columns can be
622-
used as a unique identifier for rows in the table. (This is a
623-
direct consequence of the definition of a primary key. Note that
624-
a unique constraint does not, by itself, provide a unique identifier
625-
because it does not exclude null values.) This is useful both for
626-
documentation purposes and for client applications. For example,
627-
a GUI application that allows modifying row values probably needs
628-
to know the primary key of a table to be able to identify rows
629-
uniquely.
630-
</para>
631-
632622
<para>
633623
Adding a primary key will automatically create a unique B-tree index
634-
on the column or group of columns used in the primary key.
624+
on the column or group of columns listed in the primary key, and will
625+
force the column(s) to be marked <literal>NOT NULL</>.
635626
</para>
636627

637628
<para>
638629
A table can have at most one primary key. (There can be any number
639-
of unique and not-null constraints, which are functionally the same
640-
thing, but only one can be identified as the primary key.)
630+
of unique and not-null constraints, which are functionally almost the
631+
same thing, but only one can be identified as the primary key.)
641632
Relational database theory
642633
dictates that every table must have a primary key. This rule is
643634
not enforced by <productname>&productname;</productname>, but it is
644635
usually best to follow it.
645636
</para>
637+
638+
<para>
639+
Primary keys are useful both for
640+
documentation purposes and for client applications. For example,
641+
a GUI application that allows modifying row values probably needs
642+
to know the primary key of a table to be able to identify rows
643+
uniquely. There are also various ways in which the database system
644+
makes use of a primary key if one has been declared; for example,
645+
the primary key defines the default target column(s) for foreign keys
646+
referencing its table.
647+
</para>
646648
</sect2>
647649

648650
<sect2 id="ddl-constraints-fk">

‎doc/src/sgml/hstore.sgml

Lines changed: 23 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -325,11 +325,21 @@ b
325325
<row>
326326
<entry><function>hstore_to_json(hstore)</function><indexterm><primary>hstore_to_json</primary></indexterm></entry>
327327
<entry><type>json</type></entry>
328-
<entry>get <type>hstore</type> as a <type>json</type> value</entry>
328+
<entry>get <type>hstore</type> as a <type>json</type> value, converting
329+
all non-null values to JSON strings</entry>
329330
<entry><literal>hstore_to_json('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
330331
<entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
331332
</row>
332333

334+
<row>
335+
<entry><function>hstore_to_jsonb(hstore)</function><indexterm><primary>hstore_to_jsonb</primary></indexterm></entry>
336+
<entry><type>jsonb</type></entry>
337+
<entry>get <type>hstore</type> as a <type>jsonb</type> value, converting
338+
all non-null values to JSON strings</entry>
339+
<entry><literal>hstore_to_jsonb('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
340+
<entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
341+
</row>
342+
333343
<row>
334344
<entry><function>hstore_to_json_loose(hstore)</function><indexterm><primary>hstore_to_json_loose</primary></indexterm></entry>
335345
<entry><type>json</type></entry>
@@ -338,6 +348,14 @@ b
338348
<entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
339349
</row>
340350

351+
<row>
352+
<entry><function>hstore_to_jsonb_loose(hstore)</function><indexterm><primary>hstore_to_jsonb_loose</primary></indexterm></entry>
353+
<entry><type>jsonb</type></entry>
354+
<entry>get <type>hstore</type> as a <type>jsonb</type> value, but attempt to distinguish numerical and Boolean values so they are unquoted in the JSON</entry>
355+
<entry><literal>hstore_to_jsonb_loose('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
356+
<entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
357+
</row>
358+
341359
<row>
342360
<entry><function>slice(hstore, text[])</function><indexterm><primary>slice</primary></indexterm></entry>
343361
<entry><type>hstore</type></entry>
@@ -414,8 +432,10 @@ b
414432

415433
<note>
416434
<para>
417-
The function <function>hstore_to_json</function> is used when an <type>hstore</type>
418-
value is cast to <type>json</type>.
435+
The function <function>hstore_to_json</function> is used when
436+
an <type>hstore</type> value is cast to <type>json</type>.
437+
Likewise, <function>hstore_to_jsonb</function> is used when
438+
an <type>hstore</type> value is cast to <type>jsonb</type>.
419439
</para>
420440
</note>
421441

0 commit comments

Comments
 (0)