Native partitioning tablespace inheritance
Just had someone report that pg_partman wasn't handling tablespaces for native partitioning. https://github.com/keithf4/pg_partman/issues/212 I'd assumed that that was a property that was being inherited from the parent table, but apparently the TABLESPACE flag to CREATE TABLE is completely ignored for the parent table. I know the documentation states that you can set the tablespace per child table, but accepting the flag on the parent and completely ignoring it seems rather misleading to me. keith@keith=# CREATE TABLE (YY TIMESTAMP NOT NULL) PARTITION BY RANGE (YY ) TABLESPACE mytablespace; CREATE TABLE Time: 11.569 ms keith@keith=# \d+ ; Table "public." Column |Type | Collation | Nullable | Default | Storage | Stats target | Description +-+---+--+-+-+--+- yy | timestamp without time zone | | not null | | plain | | Partition key: RANGE (yy) keith@keith=# select relname, reltablespace from pg_class where relname = ''; relname | reltablespace -+--- | 0 (1 row) Any chance of this being an inheritable property that can simply be overridden if the TABLESPACE flag is set when creating a child table? If it's not set, just set the tablespace to whatever was set for the parent. For now, partman is going to have to rely on the template table option to handle this the same way it does for indexes right now. -- Keith Fiske Senior Database Engineer Crunchy Data - http://crunchydata.com
Re: Native partitioning tablespace inheritance
On Wed, Apr 11, 2018 at 4:54 PM, Michael Paquier wrote: > On Wed, Apr 11, 2018 at 12:52:06PM -0400, Keith Fiske wrote: > > Any chance of this being an inheritable property that can simply be > > overridden if the TABLESPACE flag is set when creating a child table? If > > it's not set, just set the tablespace to whatever was set for the parent. > > I am wondering how you would actually design that without some kind of > unintuitive behavior for the end user as for some applications a set of > child partitions sometimes take advantage of the fact that they are on > separate tablespaces. Hence why not relying on default_tablespace > instead when creating the partition set, or use a function wrapper which > enforces the tablespace when the partition is created? > -- > Michael > To me the current behavior is even more unintuitive. You tell it to put the parent table in a specific tablespace and it completely ignores the option and puts it in the default. Then when you go create children without specifying a tablespace, you don't see it going where you thought it would based on the parent's creation statement. Yes, you can tell each child where to go, but why not have at least a basic mechanism for setting a single tablespace value for a partition set into the parent itself the same way we're doing with indexes? If you set the tablespace you want a partition set to be in by setting that on that parent, I think that's pretty intuitive. If you want children to be in a different tablespace than the partition's default, then you can tell it that at child creation. Having to rely on custom written function to enforce just basic tablespace rules seems to be overcomplicating it to me. -- Keith Fiske Senior Database Engineer Crunchy Data - http://crunchydata.com
Re: Native partitioning tablespace inheritance
On Thu, Apr 12, 2018 at 3:15 PM, Jonathan S. Katz < jonathan.k...@excoventures.com> wrote: > > > On Apr 12, 2018, at 3:10 PM, Alvaro Herrera > wrote: > > > > Robert Haas wrote: > >> On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz > >> wrote: > >>> If there are no strong objections I am going to add this to the “Older > Bugs” > >>> section of Open Items in a little bit. > >> > >> I strongly object. This is not a bug. The TABLESPACE clause doing > >> exactly what it was intended to do, which is determine where all of > >> the storage associated with the partitioned table itself goes. It so > >> happens that there is no storage, so now somebody would like to > >> repurpose the same option to do something different. That's fine, but > >> it doesn't make the current behavior wrong. And we're certainly not > >> going to back-patch a behavior change like that. > > Behavior-wise it’s certainly a bug: you add a TABLESPACE on the parent > table, and that property is not passed down to the children, which is not > what the user expects. At a minimum, if we don’t back patch it, we > probably > need to update the documentation to let people know. > > > Keep in mind that we do not offer any promises to fix items listed in > > the Older Bugs section; as I said elsewhere, it's mostly a dumping > > ground for things that get ignored later. I think it's fine to add it > > there, if Jon wants to keep track of it, on the agreement that it will > > probably not lead to a backpatched fix. > > Per an off-list discussion, it does not make sense to back patch but > it does make sense to try to get it into 11 as part of making things more > stable. > > Perhaps as a short-term fix, we update the docs to let users know that if > you put a TABLESPACE on the parent table it does not get passed down > to the children? > > Jonathan > > I also think it's rather confusing that, even though there is technically no data storage going on with the parent, that the parent itself does not get placed in the tablespace given to the creation command. Just completely ignoring a flag given to a command with zero feedback is my biggest complaint on this. If it's going to be ignored, at least giving some sort of feedback (kind of like long name truncation does) would be useful here and should be considered for back-patching to 10. -- Keith Fiske Senior Database Engineer Crunchy Data - http://crunchydata.com
Re: Native partitioning tablespace inheritance
On Thu, Apr 12, 2018 at 3:39 PM, Robert Haas wrote: > On Thu, Apr 12, 2018 at 3:15 PM, Jonathan S. Katz > wrote: > > Behavior-wise it’s certainly a bug: you add a TABLESPACE on the parent > > table, and that property is not passed down to the children, which is not > > what the user expects. At a minimum, if we don’t back patch it, we > probably > > need to update the documentation to let people know. > > Well I don't object to updating the documentation, but just because > something isn't what the user expects doesn't make it a bug. Users > can have arbitrary expectations. > > On a practical level, what I think users *should* expect is that table > partitioning behaves like table inheritance except in cases where > we've gotten around to doing something different. Of course, the > behavior of table partitioning here is no worse than what table > inheritance does. The behavior doesn't cascade from parent to child > there, either. If we start classifying as a bug every area where > table partitioning works like table inheritance but someone can think > of something better, we've probably got about 50 bugs, some of which > will require years of work to fix. > > This is clearly new development aimed at delivering a novel behavior. > It isn't going to fix an error in code that already exists. It's > going to write new code to do something that the system has never done > before. > > Unless done rather carefully, it's also going to break > dump-and-restore and, I think, likely also pg_upgrade. Suppose that > in the original cluster TABLESPACE was set on the parent but not on > the children. The children are therefore dumped without a TABLESPACE > clause. On the old cluster that would have worked as intended, but > with this change the children will end up in the parent's tablespace > instead of the database default tablespace. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > Your last example is why I proposed taking the TABLESPACE defined on the parent and applying it to the children. Then all the children have one defined and nothing breaks as long as all tablespaces are properly defined as part of the restoration. I'm also not sure that we should have this mindset of partitioning working as inheritance does either. Inheritance was only used before because it was the only mechanism available. And while you do still use it under the hood for parts of partitioning, I don't see any reason we should be let people assume that partitioning works anything like inheritance. In my opinion they are two very distinct options. Now we have "real" partitioning, so let's act like it. That doesn't mean defining old behavior as a "bug", I agree. It just means we're defining and clarifying how partitioning itself is supposed to be working. -- Keith Fiske Senior Database Engineer Crunchy Data - http://crunchydata.com
