Native partitioning tablespace inheritance

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

On Wed, Apr 11, 2018 at 4:54 PM, Michael Paquier <michael@paquier.xyz>
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

On 4/11/18 17:19, Keith Fiske wrote:

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.

I agree it should do one or the other, but not what it's doing now.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 12 April 2018 at 09:19, Keith Fiske <keith.fiske@crunchydata.com> wrote:

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.

I imagine the correct thing to do is properly record the TABLESPACE
option for the partitioned table then make child tables use that if
nothing else was specified.

This would allow the parent partition's tablespace to be changed from
time to time as disk partitions become full to allow the new
partitions to be created in the tablespace which sits on a disk with
the most free space.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Apr 11, 2018 at 9:55 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

I imagine the correct thing to do is properly record the TABLESPACE
option for the partitioned table then make child tables use that if
nothing else was specified.

This would allow the parent partition's tablespace to be changed from
time to time as disk partitions become full to allow the new
partitions to be created in the tablespace which sits on a disk with
the most free space.

Hmm, that's interesting. So you want the children to inherit the
parent's tablespace when they are created, but if the parent's
tablespace is later changed, the existing children don't move? I
guess that's a defensible behavior, but it's not one I would have
considered. It's certainly quite different from what the TABLESPACE
option means when applied to an unpartitioned table.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Apr 12, 2018, at 09:17, Robert Haas <robertmhaas@gmail.com> wrote:
Hmm, that's interesting. So you want the children to inherit the
parent's tablespace when they are created, but if the parent's
tablespace is later changed, the existing children don't move?

+1 to that behavior.

While it's always possible to just say "do the right thing" to the application when creating new children (that is, expect that they will always specify a tablespace if it's not the default), this seems like the least-surprising behavior.

It's true that an unpartitioned table will always be created in the default tablespace unless otherwise specified, but child tables are sufficiently distinct from that case that I don't see it as a painful asymmetry.

--
-- Christophe Pettus
xof@thebuild.com

On Apr 12, 2018, at 2:36 PM, Christophe Pettus <xof@thebuild.com> wrote:

On Apr 12, 2018, at 09:17, Robert Haas <robertmhaas@gmail.com> wrote:
Hmm, that's interesting. So you want the children to inherit the
parent's tablespace when they are created, but if the parent's
tablespace is later changed, the existing children don't move?

+1 to that behavior.

While it's always possible to just say "do the right thing" to the application when creating new children (that is, expect that they will always specify a tablespace if it's not the default), this seems like the least-surprising behavior.

It's true that an unpartitioned table will always be created in the default tablespace unless otherwise specified, but child tables are sufficiently distinct from that case that I don't see it as a painful asymmetry.

If there are no strong objections I am going to add this to the “Older Bugs”
section of Open Items in a little bit.

Jonathan

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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.

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.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Apr 12, 2018 at 3:10 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Robert Haas wrote:

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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.

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.

*shrug*

If it's not a bug, then it doesn't make sense to add it to a list of
bugs just as a way of keeping track of it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Apr 12, 2018, at 3:10 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Robert Haas wrote:

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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

On Thursday, April 12, 2018, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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.

The part about accepting an option that is basically invalid is reasonably
bug-like. Having tablespace and partition by clauses be mutually exclusive
would be worthy of fixing though it couldn't be back-patched.
Documentation is good but outright prevention is better.

If we can't agree on the future behavior we should at least prevent the
existing situation in v11. I'm doubting whether redefine behavior of the
existing option to anything other than an error would be acceptable.

David J.

On Thu, Apr 12, 2018 at 3:15 PM, Jonathan S. Katz <
jonathan.katz@excoventures.com> wrote:

On Apr 12, 2018, at 3:10 PM, Alvaro Herrera <alvherre@alvh.no-ip.org>

wrote:

Robert Haas wrote:

On Thu, Apr 12, 2018 at 2:40 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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

On Thu, Apr 12, 2018 at 3:15 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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

On Thu, Apr 12, 2018 at 3:39 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Apr 12, 2018 at 3:15 PM, Jonathan S. Katz
<jonathan.katz@excoventures.com> 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

Keith Fiske wrote:

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.

+1 (See also ALTER TABLE .. SET OWNER, which I ought to have fixed but
didn't).

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Apr 12, 2018 at 03:39:19PM -0400, Robert Haas wrote:

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.

Yes, I agree that this is not a bug, and should not be categorized as
such. Mentioning in the documentation explicitely that tablespaces are
not inherited may be worth it.

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.

+1.

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.

I have not looked at the problem closely, but now pg_dump relies heavily
on default_tablespace to make sure that a table is using the correctly
tablespace and does not append a TABLESPACE clause to CREATE TABLE so as
pg_restore can work with --no-tablespaces. So we'll surely get some
regressions and corner cases if not careful.
--
Michael

On Thu, Apr 12, 2018 at 3:55 PM, Keith Fiske
<keith.fiske@crunchydata.com> wrote:

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 decided to test this out using the following commands:

create tablespace xkcd location '/Users/rhaas/xkcd';
create table foo (a int, b text) partition by hash (a);
create table foo1 partition of foo for values with (modulus 2, remainder 0);
alter table foo set tablespace xkcd;
create table foo2 partition of foo for values with (modulus 2, remainder 1);

Under the proposed definition, foo2 is going to end up in tablespace
xkcd. Let's simulate that:

alter table foo2 set tablespace xkcd;

As things stand today, if you dump and restore at this point, foo and
foo1 get dumped with default_tablespace = '' and foo2 gets dumped with
default_tablespace = 'xkcd'. So you're correct that every child will
get restored into the correct tablespace. But it appears to me that
the parent's tablespace is not preserved, so some pg_dump change would
be needed to fix that.

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.

Well, let me put it this way. Someone who assumes that partitioning
works like inheritance except where we've explicitly made it work
differently will be correct. Someone who assumes something else will
be incorrect. I'm not saying that we shouldn't change things in the
future. I think there's a lot of opportunity for improvement.
However, I also think that partitioning shouldn't get to ignore the
feature freeze deadline. There's been a huge amount of progress in
this release: faster pruning, run-time pruning, indexes, foreign keys,
triggers, hash partitioning, default partitioning, update tuple
routing, partition-wise join & aggregate, and other things. What
didn't get done should, in my opinion, wait for v12. I know that's
painful, but IMHO you've got to draw the line someplace, and we picked
a date and should stick with it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Apr 13, 2018, at 20:01, Robert Haas <robertmhaas@gmail.com> wrote:

Well, let me put it this way. Someone who assumes that partitioning
works like inheritance except where we've explicitly made it work
differently will be correct. Someone who assumes something else will
be incorrect. I'm not saying that we shouldn't change things in the
future. I think there's a lot of opportunity for improvement.
However, I also think that partitioning shouldn't get to ignore the
feature freeze deadline. There's been a huge amount of progress in
this release: faster pruning, run-time pruning, indexes, foreign keys,
triggers, hash partitioning, default partitioning, update tuple
routing, partition-wise join & aggregate, and other things. What
didn't get done should, in my opinion, wait for v12. I know that's
painful, but IMHO you've got to draw the line someplace, and we picked
a date and should stick with it.

Yeah, but the more we wait, the more painful would be the change of behavior.
Just like with CTE there would be more people arguing that users now rely on it.

On Fri, Apr 13, 2018 at 08:23:22PM +0300, Evgeniy Shishkin wrote:

On Apr 13, 2018, at 20:01, Robert Haas <robertmhaas@gmail.com> wrote:

Well, let me put it this way. Someone who assumes that partitioning
works like inheritance except where we've explicitly made it work
differently will be correct. Someone who assumes something else will
be incorrect. I'm not saying that we shouldn't change things in the
future. I think there's a lot of opportunity for improvement.
However, I also think that partitioning shouldn't get to ignore the
feature freeze deadline. There's been a huge amount of progress in
this release: faster pruning, run-time pruning, indexes, foreign keys,
triggers, hash partitioning, default partitioning, update tuple
routing, partition-wise join & aggregate, and other things. What
didn't get done should, in my opinion, wait for v12. I know that's
painful, but IMHO you've got to draw the line someplace, and we picked
a date and should stick with it.

+1. There are maaany things to test and look at, so the focus should be
in stabilizing the release.

Yeah, but the more we wait, the more painful would be the change of behavior.
Just like with CTE there would be more people arguing that users now rely on it.

There is room for development in v12 and beyond if there are thoughts
that this behavior should be changed. The amount of features of v11 is
now sealed, the future is not.
--
Michael