On Mon, Apr 3, 2017 at 10:45 PM, Keith Fiske <ke...@omniti.com> wrote:
> > On Mon, Apr 3, 2017 at 5:13 AM, Ashutosh Bapat < > ashutosh.ba...@enterprisedb.com> wrote: > >> >> >> On Fri, Mar 31, 2017 at 9:00 PM, Keith Fiske <ke...@omniti.com> wrote: >> >>> I've gotten pg_partman working with native partitioning already so I can >>> hopefully have things ready to work when 10 is released. I've got a branch >>> on github with this version for anyone to test and I'll hopefully have this >>> released in the next few weeks after I finish some more testing myself. Any >>> feedback would be appreciated! >>> >>> https://github.com/keithf4/pg_partman/tree/v3.0.0 >>> >> >> There's already a proposal to support default partition as [1]. That >> proposal talks about default partition in list partitioned tables. For >> range partitioned tables, we expect that a single partition with unbounded >> bounds would serve as default partition. >> > > This would not work. The completely unbounded partition would overlap all > other possible partitions. How would it decide which child table to put > data in? Looks like this is stopped right from the start anyway. > > keith@keith=# create table testing_range (id int, created_at timestamptz) > partition by range (created_at); > CREATE TABLE > Time: 41.987 ms > > keith@keith=# create table testing_range_default partition of > testing_range for values from (unbounded) to (unbounded); > CREATE TABLE > Time: 8.625 ms > > keith@keith=# create table testing_range_p2017_04 partition of > testing_range for values from ('2017-04-01 00:00:00') to ('2017-05-01 > 00:00:00'); > ERROR: partition "testing_range_p2017_04" would overlap partition > "testing_range_default" > Time: 4.516 ms > > Hmm, looks like default partition for range would be helpful 1. in these case and 2. to hold data in the holes in the existing partitioning scheme. > >> >>> >>> >>> Thankfully since native partitioning still uses inheritance internally >>> for the most part, pg_partman works pretty well without nearly as much >>> change as I thought I would need. The biggest deficiency I'm seeing has to >>> do with not having a "default" partition to put data that doesn't match any >>> children. The fact that it throws an error is a concern, but it's not where >>> I see the main problem. Where this really comes into play is when someone >>> wants to make an existing table into a partitioned table. There's really no >>> easy way to do this outside of making a completely brand new partition set >>> and copying/moving the data from the old to the new. >>> >> >> If there are multiple partitions, there is likely to be more data that >> needs to be moved that is retained in the old table. So, creating complete >> brand new partitioning and copying/moving data is annoying but not as much >> as it sounds. Obviously, if we could avoid it, we should try to. >> > > Not sure I follow what you're saying here. With pg_partman, making the old > table the parent and still containing all the data has caused no issues > when I've migrated clients to it, nor has anyone reported any issues to me > with this system. New data goes to the child tables as they should and old > data is then moved when convenient. It makes things work very smoothly and > the only outage encountered is a brief lock at creation time. > In partitioning, partitioned table doesn't have any storage. Data that belongs to a given partition is expected to be there from day one. > > I've been watching that thread as well and as soon as a fix is posted > about the latest concerns, I'll gladly look into reviewing it. > Thanks. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
