tables have merged columns
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/functions-json.html Description: In this version i cannot see columns https://www.postgresql.org/docs/13/functions-json.html in older versions are columns seen correctly https://www.postgresql.org/docs/12/functions-json.html Tested on chrome and firefox David T.
Re: tables have merged columns
On Friday, November 13, 2020, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/13/functions-json.html > Description: > > In this version i cannot see columns > https://www.postgresql.org/docs/13/functions-json.html > > in older versions are columns seen correctly > https://www.postgresql.org/docs/12/functions-json.html > > Tested on chrome and firefox > The format of tables changed with version 13. David J.
Partitioning docs (was Re: Range partitioning and overlap)
[ redirecting to pgsql-docs ]
I wrote:
> Edson Richter writes:
>> Further on the documentation: "When creating a range partition, the lower
>> bound specified with FROM is an inclusive bound, whereas the upper bound
>> specified with TO is an exclusive bound."
>> I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation
>> page about partitioning. May be this statement is in another page?
> It's in the CREATE TABLE reference page. Seems like it would be a good
> idea to have it also in ddl.sgml's discussion of partitioning, though.
I went to do that, and soon decided that section 5.11 (Table Partitioning)
really could stand a fair amount of editorial attention. There's a lot
of less than pitch-perfect English, paragraphs that were evidently written
with only minimal attention to nearby existing material, diving into the
weedy details in even the earliest introductory paras, and so on. I
propose the attached.
regards, tom lane
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 872f7a7fac..25948a7316 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3553,8 +3553,8 @@ VALUES ('Albany', NULL, NULL, 'NY');
When queries or updates access a large percentage of a single
- partition, performance can be improved by taking advantage
- of sequential scan of that partition instead of using an
+ partition, performance can be improved by using a
+ sequential scan of that partition instead of using an
index and random access reads scattered across the whole table.
@@ -3577,7 +3577,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
- The benefits will normally be worthwhile only when a table would
+ These benefits will normally be worthwhile only when a table would
otherwise be very large. The exact point at which a table will
benefit from partitioning depends on the application, although a
rule of thumb is that the size of the table should exceed the physical
@@ -3599,6 +3599,13 @@ VALUES ('Albany', NULL, NULL, 'NY');
the ranges of values assigned to different partitions. For
example, one might partition by date ranges, or by ranges of
identifiers for particular business objects.
+ Each range's bounds are understood as being inclusive at the
+ lower end and exclusive at the upper end. For example, if one
+ partition's range is from 1
+ to 10, and the next one's range is
+ from 10 to 20, then
+ value 10 belongs to the second partition not
+ the first.
@@ -3608,7 +3615,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
- The table is partitioned by explicitly listing which key values
+ The table is partitioned by explicitly listing which key value(s)
appear in each partition.
@@ -3640,25 +3647,34 @@ VALUES ('Albany', NULL, NULL, 'NY');
Declarative Partitioning
-PostgreSQL offers a way to specify how to
-divide a table into pieces called partitions. The table that is divided
+PostgreSQL allows you to specify declaratively
+that a table is divided into partitions. The table that is divided
is referred to as a partitioned table. The
specification consists of the partitioning method
-and a list of columns or expressions to be used as the
-partition key.
+as described above, plus a list of columns or expressions to be used
+as the partition key.
-All rows inserted into a partitioned table will be routed to one of the
-partitions based on the value of the partition
-key. Each partition has a subset of the data defined by its
-partition bounds. The currently supported
-partitioning methods are range, list, and hash.
+The partitioned table itself is a virtual table having
+no storage of its own. Instead, the storage belongs
+to partitions, which are otherwise-ordinary
+tables associated with the partitioned table.
+Each partition stores a subset of the data as defined by its
+partition bounds.
+All rows inserted into a partitioned table will be routed to the
+appropriate one of the partitions based on the values of the partition
+key column(s).
+Updating the partition key of a row will cause it to be moved into a
+different partition if it no longer satisfies the partition bounds
+of its original partition.
-Partitions may themselves be defined as partitioned tables, using what is
-called sub-partitioning. Partitions may have their
+Partitions may themselves be defined as partitioned tables, resulting
+in sub-partitioning. Although all partitions
+must have the same columns as their partitioned parent, partitions may
+have their
own indexes, constraints and default values, distinct from those of othe
Re: Partitioning docs (was Re: Range partitioning and overlap)
On Fri, Nov 13, 2020 at 7:13 PM Tom Lane wrote: > [ redirecting to pgsql-docs ] > > I wrote: > > Edson Richter writes: > >> Further on the documentation: "When creating a range partition, the > lower bound specified with FROM is an inclusive bound, whereas the upper > bound specified with TO is an exclusive bound." > > >> I'm pretty sure I cannot find this statement in PostgreSQL 13 > documentation page about partitioning. May be this statement is in another > page? > > > It's in the CREATE TABLE reference page. Seems like it would be a good > > idea to have it also in ddl.sgml's discussion of partitioning, though. > > I went to do that, and soon decided that section 5.11 (Table Partitioning) > really could stand a fair amount of editorial attention. There's a lot > of less than pitch-perfect English, paragraphs that were evidently written > with only minimal attention to nearby existing material, diving into the > weedy details in even the earliest introductory paras, and so on. I > propose the attached. > > Looks good to me (just read the patch), Thanks! Just one suggestion toward the top: ...allows you to (specify declaratively => declare) that a table is divided into partitions. (specification becomes declaration further down) I am curious as to your thoughts on unique indexes and whether/how to better incorporate advice regarding the use of ON CONFLICT with partitioning [1] vis-a-vis the overview's claim of: "The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory" [2] David J. [1] https://www.postgresql.org/message-id/CAKFQuwYLtjoPh6Crrr1b2e92NSWJDLPE1W08C63u3JU9RBjooA%40mail.gmail.com [2] https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW
Re: Partitioning docs (was Re: Range partitioning and overlap)
"David G. Johnston" writes: > Looks good to me (just read the patch), Thanks! Just one suggestion toward > the top: > ...allows you to (specify declaratively => declare) that a table is divided > into partitions. (specification becomes declaration further down) Sure. That's a little further from the previous wording than what I had, but it's probably better. > I am curious as to your thoughts on unique indexes and whether/how to > better incorporate advice regarding the use of ON CONFLICT with > partitioning [1] vis-a-vis the overview's claim of: > "The partitioning substitutes for leading columns of indexes, reducing > index size and making it more likely that the heavily-used parts of the > indexes fit in memory" [2] >From a semantic standpoint, there's no doubt that requiring unique indexes to include the partition key is fine and necessary. (I tried to explain why in this rewrite.) In the case of list partitioning with a single value per partition, it's conceivable that we could drop the partition key column from the index implementing the constraint on that partition, but it'd be a bit of a wart and I'm not sure that it'd be worth the trouble. Anyplace where a partition can have more than one value of the partition key column, you still need that column in the index. I think what the docs are talking about here is indexes that are *not* declared unique, or that are declared unique but are created per-partition rather than globally. In those cases you can leave off the partition key and the index will still do what you need. Possibly a better way to write that claim is that partitioning can substitute for the upper levels of a huge index, rather than "leading columns" per se. That way of looking at it is still sensible when a partition covers more than one value of the key column. regards, tom lane
