On Mon, Sep 11, 2017 at 8:00 AM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > How difficult/tedious/troublesome would be to install the missing > partitions if you set hash partitioning with a default partition and > only later on notice that some partitions are missing? I think if the > answer is that you need to exclusive-lock something for a long time and > this causes a disruption in production systems, then it's better not to > allow a default partition at all and just force all the hash partitions > to be there from the start. > > On the other hand, if you can get tuples out of the default partition > into their intended regular partitions without causing any disruption, > then it seems okay to allow default partitions in hash partitioning > setups.
I think there's no real use case for default partitioning, and yeah, you do need exclusive locks to repartition things (whether hash partitioning or otherwise). It would be nice to fix that eventually, but it's hard, because the executor has to cope with the floor moving under it, and as of today, it really can't cope with that at all - not because of partitioning specifically, but because of existing design decisions that will require a lot of work (and probably arguing) to revisit. I think the way to get around the usability issues for hash partitioning is to eventually add some syntax that does things like (1) automatically create the table with N properly-configured partitions, (2) automatically split an existing partition into N pieces, and (3) automatically rewrite the whole table using a different partition count. People seem to find the hash partitioning stuff a little arcane. I don't want to discount that confusion with some sort of high-handed "I know better" attitude, I think the interface that users will actually see can end up being pretty straightforward. The complexity that is there in the syntax is to allow pg_upgrade and pg_dump/restore to work properly. But users don't necessarily have to use the same syntax that pg_dump does, just as you can say CREATE INDEX ON a (b) and let the system specify the index name, but at dump time the index name is specified explicitly. > (I, like many others, was unable to follow the default partition stuff > as closely as I would have liked.) Uh, sorry about that. Would it help if I wrote a blog post on it or something? The general idea is simple: any tuples that don't route to any other partition get routed to the default partition. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
