Re: Postgres 11: Table Partitioning and Primary Keys
On Mon, Jul 8, 2019 at 11:34 PM Michael Paquier wrote: > On Mon, Jul 08, 2019 at 08:12:18PM -0700, David G. Johnston wrote: > > Reads a bit backward. How about: > > > > "As uniqueness can only be enforced within an individual partition when > > defining a primary key on a partitioned table all columns present in the > > partition key must also exist in the primary key." > > Yes, I was not really inspired on this one. > > Looking closely at the code in DefineIndex() (and as Rajkumar has > mentioned upthread for unique constraints) this can happen for primary > keys, unique constraints and exclusion constraints. So we had better > mention all three of them. I am not sure that we need to be explicit > about the uniqueness part though, let's say the following: > "When defining a primary key, a unique constraint or an exclusion > constraint on a partitioned table, all the columns present in the > constraint definition must be included in the partition key." > > That isn't true, it needs to be reversed at least: "Table-scoped constraints defined on a partitioned table - primary key, unique, and exclusion - must include the partition key columns because the enforcement of such constraints is performed independently on each partition." The complaint here is the user puts a PK id column on their partitioned table and wonders why they need the partition key columns to also be in the PK. The answer is the description provided above - with the reminder (or initial cluing in depending) to the reader that this limitation exists because we do not implement global constraints/indexes but instead the definition on the partitioned table is simply copied to all of its partitions. For me this seems worthy of recapping at this location (I haven't gone looking for a nice cross-reference link to put there). David J.
Re: Postgres 11: Table Partitioning and Primary Keys
Sorry for jumping in late here. On Tue, Jul 9, 2019 at 3:51 PM Michael Paquier wrote: > On Tue, Jul 09, 2019 at 03:34:48PM +0900, Michael Paquier wrote: > > Looking closely at the code in DefineIndex() (and as Rajkumar has > > mentioned upthread for unique constraints) this can happen for primary > > keys, unique constraints and exclusion constraints. So we had better > > mention all three of them. I am not sure that we need to be explicit > > about the uniqueness part though, let's say the following: > > "When defining a primary key, a unique constraint or an exclusion > > constraint on a partitioned table, all the columns present in the > > constraint definition must be included in the partition key." > > Let's try again that (that's a long day..): > "When defining a primary key, a unique constraint or an exclusion > constraint on a partitioned table, all the columns present in the > partition key must be included in the constraint definition." As mentioned in the docs, defining exclusion constraints on partitioned tables is not supported. -- on 13dev create table p (a int, exclude using gist (a with &&)) partition by list (a); ERROR: exclusion constraints are not supported on partitioned tables Regarding primary key and unique constraints, how about writing it such that it's clear that there are limitations? Maybe like: "While defining a primary key and unique constraints on partitioned tables is supported, the set of columns being constrained must include all of the partition key columns." Maybe, as David also says, it might be a good idea to mention the reason why. So maybe like: "While defining a primary key and unique constraints on partitioned tables is supported, the set of columns being constrained must include all of the partition key columns. This limitation exists because PostgreSQL can ensure uniqueness only across a given partition." Thanks, Amit [1] https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE 5.11.2.3. Limitations The following limitations apply to partitioned tables: * There is no way to create an exclusion constraint spanning all partitions; it is only possible to constrain each leaf partition individually.
Re: Ambiguous language in Table 8.13. Special Date/Time Inputs [EXT]
> Uh, I believe midnight is always the start of the day. The U.S. National Institute of Standards and Technology (NIST) deprecates the term “midnight” as ambiguous and recommmends the use of 24-hour clock notation: = BEGIN QUOTE = When someone refers to "midnight tonight" or "midnight last night" the reference of time is obvious. However, if a date/time is referred to as "at midnight on Friday, October 20th" the intention could be either midnight the beginning of the day or midnight at the end of the day. To avoid ambiguity, specification of an event as occurring on a particular day at 11:59 p.m. or 12:01 a.m. is a good idea, especially legal documents such as contracts and insurance policies. Another option would be to use 24-hour clock, using the designation of to refer to midnight at the beginning of a given day (or date) and 2400 to designate the end of a given day (or date). = END QUOTE = Source: https://www.nist.gov/pml/time-and-frequency-division/times-day-faqs > I think 00:00:00 looks awkward. Perhaps, but in technical documentation, accuracy should have priority over aesthetics, surely? David Harper Wellcome Sanger Institute, Cambridge, England -- The Wellcome Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
Re: Ambiguous language in Table 8.13. Special Date/Time Inputs [EXT]
On Tue, Jul 9, 2019 at 09:14:27AM +0100, David Harper wrote: > > Uh, I believe midnight is always the start of the day. > > The U.S. National Institute of Standards and Technology (NIST) deprecates the > term “midnight” as ambiguous and recommmends the use of 24-hour clock > notation: > > = BEGIN QUOTE = > When someone refers to "midnight tonight" or "midnight last night" the > reference of time is obvious. However, if a date/time is referred to as "at > midnight on Friday, October 20th" the intention could be either midnight the > beginning of the day or midnight at the end of the day. > > To avoid ambiguity, specification of an event as occurring on a particular > day at 11:59 p.m. or 12:01 a.m. is a good idea, especially legal documents > such as contracts and insurance policies. Another option would be to use > 24-hour clock, using the designation of to refer to midnight at the > beginning of a given day (or date) and 2400 to designate the end of a given > day (or date). > = END QUOTE = > > Source: > > https://www.nist.gov/pml/time-and-frequency-division/times-day-faqs > > > I think 00:00:00 looks awkward. > > Perhaps, but in technical documentation, accuracy should have priority over > aesthetics, surely? > Yes, I see what you mean now. How is this patch, which uses "zero hour"? -- Bruce Momjian 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 + diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 35ecd48ed5..3b5b8a65d0 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2174,17 +2174,17 @@ January 8 04:05:06 1999 PST today date, timestamp - midnight today + midnight (zero hour) today tomorrow date, timestamp - midnight tomorrow + midnight (zero hour) tomorrow yesterday date, timestamp - midnight yesterday + midnight (zero hour) yesterday allballs
Re: Ambiguous language in Table 8.13. Special Date/Time Inputs [EXT]
Bruce Momjian writes: > Yes, I see what you mean now. How is this patch, which uses "zero > hour"? Ugh. Are we writing spy novels now? I actually agree with your opinion that "midnight" is fine. That text has been that way for over fifteen years[1], and nobody's complained before that it was ambiguous. But, if we're going to change it, let's just s/midnight/00:00:00/ and be done. Making up fancy terminology doesn't improve matters. regards, tom lane [1] git blame dates the text to commit 1b342df00 of 2002-11-11, and apparently it was not new then, but I'm too lazy to dig further.
Re: Improvement of GIN figure
On Tue, Jul 9, 2019 at 12:20 AM Oleg Bartunov wrote: > Sorry, I may missed the discussion of what colors and fonts we accept > for our documentation, but > the color and fonts used I don't like. I attached our version of GIN figure. I agree that the existing colors look awful, and that muted pastel colors would work better. Doesn't seem like something that should happen at the cost of making the diagram less informative, though. -- Peter Geoghegan
Re: Improvement of GIN figure
> I agree that the existing colors look awful, and that muted pastel > colors would work better. Doesn't seem like something that should > happen at the cost of making the diagram less informative, though. I am not an expert in the area but I think we should cosider people with color disability. https://www.invisionapp.com/inside-design/color-accessibility-product-design/ Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Re: Improvement of GIN figure
On Tue, Jul 9, 2019 at 3:22 PM Tatsuo Ishii wrote: > > I agree that the existing colors look awful, and that muted pastel > > colors would work better. Doesn't seem like something that should > > happen at the cost of making the diagram less informative, though. > > I am not an expert in the area but I think we should cosider people > with color disability. Good point. I think that that shouldn't be too hard to mostly get right. It's good that the diagrams will already work with a screen reader. -- Peter Geoghegan
Re: Postgres 11: Table Partitioning and Primary Keys
On 2019-Jul-09, Amit Langote wrote: > As mentioned in the docs, defining exclusion constraints on > partitioned tables is not supported. Right. > "While defining a primary key and unique constraints on partitioned > tables is supported, the set of columns being constrained must include > all of the partition key columns. This limitation exists because > PostgreSQL can ensure uniqueness only > across a given partition." I feel that PKs are mostly a special case of UNIQUE keys, so I tend to mention UNIQUE as the central element and let PKs fall out from that. That's a mild personal preference only though. Anyway, based on your proposed wording, I wrote this: Unique constraints on partitioned tables (as well as primary keys) must constrain all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually. I'm not really sure about the "must constrain" verbiage. Is that really comprehensible? Also, I chose to place it just above the existing para that mentions FK limitations, which reads: While primary keys are supported on partitioned tables, foreign keys referencing partitioned tables are not supported. (Foreign key references from a partitioned table to some other table are supported.) Your proposed wording seemed to use too many of the same words, which prompted me to change a bit. Maybe I read too many novels and insufficient technical literature. In CREATE TABLE, we already have this: When establishing a unique constraint for a multi-level partition hierarchy, all the columns in the partition key of the target partitioned table, as well as those of all its descendant partitioned tables, must be included in the constraint definition. which may not be the pinnacle of clarity, but took some time to craft and I think is correct. Also it doesn't mention primary keys explicitly; maybe we should patch it by adding "(as well as a primary key)" right after "a unique constraint". Thoughts? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Postgres 11: Table Partitioning and Primary Keys
Alvaro Herrera writes: > That's a mild personal preference only though. Anyway, based on your > proposed wording, I wrote this: > > >Unique constraints on partitioned tables (as well as primary keys) >must constrain all the partition key columns. This limitation exists >because PostgreSQL can only enforce >uniqueness in each partition individually. > > > I'm not really sure about the "must constrain" verbiage. Is that really > comprehensible? I think "must include" might be better. > In CREATE TABLE, we already have this: > > When establishing a unique constraint for a multi-level partition > hierarchy, all the columns in the partition key of the target > partitioned table, as well as those of all its descendant partitioned > tables, must be included in the constraint definition. > > which may not be the pinnacle of clarity, but took some time to craft > and I think is correct. Also it doesn't mention primary keys > explicitly; maybe we should patch it by adding "(as well as a primary > key)" right after "a unique constraint". Thoughts? I'd leave that alone. I don't think the parenthetical comment about primary keys in your new text is adding much either. regards, tom lane
Re: Postgres 11: Table Partitioning and Primary Keys
On Wed, Jul 10, 2019 at 7:53 AM Alvaro Herrera wrote: > On 2019-Jul-09, Amit Langote wrote: > > "While defining a primary key and unique constraints on partitioned > > tables is supported, the set of columns being constrained must include > > all of the partition key columns. This limitation exists because > > PostgreSQL can ensure uniqueness only > > across a given partition." > > I feel that PKs are mostly a special case of UNIQUE keys, so I tend to > mention UNIQUE as the central element and let PKs fall out from that. > That's a mild personal preference only though. Anyway, based on your > proposed wording, I wrote this: > > > >Unique constraints on partitioned tables (as well as primary keys) >must constrain all the partition key columns. This limitation exists >because PostgreSQL can only enforce >uniqueness in each partition individually. > > > > I'm not really sure about the "must constrain" verbiage. Is that really > comprehensible? Looks good after replacing "must constraint" by "must include" as suggested by Tom. > Also, I chose to place it just above the existing para > that mentions FK limitations This placement of the new text sounds good. > In CREATE TABLE, we already have this: > > When establishing a unique constraint for a multi-level partition > hierarchy, all the columns in the partition key of the target > partitioned table, as well as those of all its descendant partitioned > tables, must be included in the constraint definition. > > > which may not be the pinnacle of clarity, but took some time to craft > and I think is correct. Also it doesn't mention primary keys > explicitly; maybe we should patch it by adding "(as well as a primary > key)" right after "a unique constraint". Thoughts? Works for me. Thanks, Amit
