Re: Declarative partitioning - another take

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 (??);

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Tue, Sep 6, 2016 at 6:37 PM, Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> On Tue, Sep 6, 2016 at 9:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Wed, Aug 31, 2016 at 1:05 PM, Amit Langote
> > <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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.
>
> Agreed. So I will stick with the existing approach.
>
> Thanks,
> Amit
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>