Re: Declarative partitioning - another take - Mailing list pgsql-hackers
| From | Rajkumar Raghuwanshi |
|---|---|
| Subject | Re: Declarative partitioning - another take |
| Date | |
| Msg-id | CAKcux6k1aEvxSgFK=Gkb1usTeDg84GXMvDXhhFGOJYr96uO=nQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Declarative partitioning - another take (Amit Langote <amitlangote09@gmail.com>) |
| Responses |
Re: Declarative partitioning - another take
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
| List | pgsql-hackers |
Hi,
I have a query regarding list partitioning,
For example if I want to store employee data in a table, with "IT" dept employee in emp_p1 partition, "HR" dept employee in emp_p2 partition and if employee belongs to other than these two, should come in emp_p3 partition.
In this case not sure how to create partition table. Do we have something like we have UNBOUNDED for range partition or oracle have "DEFAULT" for list partition.
create table employee (empid int, dept varchar) partition by list(dept);
create table emp_p1 partition of employee for values in ('IT');
create table emp_p2 partition of employee for values in ('HR');
create table emp_p3 partition of employee for values in (??);
I have a query regarding list partitioning,
For example if I want to store employee data in a table, with "IT" dept employee in emp_p1 partition, "HR" dept employee in emp_p2 partition and if employee belongs to other than these two, should come in emp_p3 partition.
In this case not sure how to create partition table. Do we have something like we have UNBOUNDED for range partition or oracle have "DEFAULT" for list partition.
create table employee (empid int, dept varchar) partition by list(dept);
create table emp_p1 partition of employee for values in ('IT');
create table emp_p2 partition of employee for values in ('HR');
create table emp_p3 partition of employee for values in (??);
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
On Tue, Sep 6, 2016 at 6:37 PM, Amit Langote <amitlangote09@gmail.com> wrote:
Agreed. So I will stick with the existing approach.On Tue, Sep 6, 2016 at 9:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Aug 31, 2016 at 1:05 PM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>>> However, it seems a lot better to make it a property of the parent
>>> from a performance point of view. Suppose there are 1000 partitions.
>>> Reading one toasted value for pg_class and running stringToNode() on
>>> it is probably a lot faster than scanning pg_inherits to find all of
>>> the child partitions and then doing an index scan to find the pg_class
>>> tuple for each and then decoding all of those tuples and assembling
>>> them into some data structure.
>>
>> Seems worth trying. One point that bothers me a bit is how do we enforce
>> partition bound condition on individual partition basis. For example when
>> a row is inserted into a partition directly, we better check that it does
>> not fall outside the bounds and issue an error otherwise. With current
>> approach, we just look up a partition's bound from the catalog and gin up
>> a check constraint expression (and cache in relcache) to be enforced in
>> ExecConstraints(). With the new approach, I guess we would need to look
>> up the parent's partition descriptor. Note that the checking in
>> ExecConstraints() is turned off when routing a tuple from the parent.
>
> [ Sorry for the slow response. ]
>
> Yeah, that's a problem. Maybe it's best to associate this data with
> the childrels after all - or halfway in between, e.g. augment
> pg_inherits with this information. After all, the performance problem
> I was worried about above isn't really much of an issue: each backend
> will build a relcache entry for the parent just once and then use it
> for the lifetime of the session unless some invalidation occurs. So
> if that takes a small amount of extra time, it's probably not really a
> big deal. On the other hand, if we can't build the implicit
> constraint for the child table without opening the parent, that's
> probably going to cause us some serious inconvenience.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
pgsql-hackers by date: