On Fri, Dec 5, 2014 at 2:18 AM, Amit Kapila wrote:
> Do we really need to support dml or pg_dump for individual partitions?
I think we do. It's quite reasonable for a DBA (or developer or
whatever) to want to dump all the data that's in a single partition;
for example, maybe they have the table
On Tue, Dec 2, 2014 at 10:43 PM, Amit Langote
wrote:
>> So, we're going to support exactly two levels of partitioning?
>> partitions with partissub=false and subpartitions with partissub=true?
>> Why not support only one level of partitioning here but then let the
>> children have their own pg_pa
From: Amit Kapila [mailto:amit.kapil...@gmail.com]
Sent: Friday, December 05, 2014 5:10 PM
To: Amit Langote
Cc: Jim Nasby; Robert Haas; Andres Freund; Alvaro Herrera; Bruce Momjian; Pg
Hackers
Subject: Re: [HACKERS] On partitioning
On Fri, Dec 5, 2014 at 12:27 PM, Amit Langote
wrote:
> F
From: Amit Kapila [mailto:amit.kapil...@gmail.com]
On Fri, Dec 5, 2014 at 12:27 PM, Amit Langote
wrote:
> From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> On Thu, Dec 4, 2014 at 10:46 AM, Amit Langote
> wrote:
> >
> > > The more SQL way would be records (composite types). That would make
On Fri, Dec 5, 2014 at 12:27 PM, Amit Langote
wrote:
> From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> On Thu, Dec 4, 2014 at 10:46 AM, Amit Langote <
langote_amit...@lab.ntt.co.jp> wrote:
> >
> > > The more SQL way would be records (composite types). That would make
> > > catalog inspection
On Tue, Dec 2, 2014 at 8:53 PM, Robert Haas wrote:
>
> On Tue, Nov 25, 2014 at 8:20 PM, Amit Langote
> wrote:
> >> Before going too much further with this I'd mock up schemas for your
> >> proposed catalogs and a list of DDL operations to be supported, with
> >> the corresponding syntax, and floa
From: Amit Kapila [mailto:amit.kapil...@gmail.com]
On Thu, Dec 4, 2014 at 10:46 AM, Amit Langote
wrote:
>
> > The more SQL way would be records (composite types). That would make
> > catalog inspection a LOT easier and presumably make it easier to change the
> > partitioning key (I'm assuming A
On Wed, Dec 3, 2014 at 6:30 PM, Alvaro Herrera
wrote:
> Amit Langote wrote:
> > From: Robert Haas [mailto:robertmh...@gmail.com]
>
> > > What is an overflow partition and why do we want that?
> >
> > That would be a default partition. That is, where the tuples that
> > don't belong elsewhere (othe
On Thu, Dec 4, 2014 at 10:46 AM, Amit Langote
wrote:
>
>
> Hi,
>
> > From: Jim Nasby [mailto:jim.na...@bluetreble.com]
> > On 12/2/14, 9:43 PM, Amit Langote wrote:
> >
> > >> >What are you going to do if the partitioning key has two columns of
> > >> >different data types?
> > >> >
> > > Sorry, th
Hi,
> From: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com]
> Amit Langote wrote:
>
> > From: Robert Haas [mailto:robertmh...@gmail.com]
>
> > > What is an overflow partition and why do we want that?
> >
> > That would be a default partition. That is, where the tuples that
> > don't belong els
Hi,
> From: Jim Nasby [mailto:jim.na...@bluetreble.com]
> On 12/2/14, 9:43 PM, Amit Langote wrote:
>
> >> >What are you going to do if the partitioning key has two columns of
> >> >different data types?
> >> >
> > Sorry, this totally eluded me. Perhaps, the 'values' needs some more
> > thought.
* k...@rice.edu (k...@rice.edu) wrote:
> On Wed, Dec 03, 2014 at 10:00:26AM -0300, Alvaro Herrera wrote:
> > In my design I initially had overflow partitions too, because I
> > inherited the idea from Itagaki Takahiro's patch. Eventually I realized
> > that it's a useless concept, because you can
maybe vertica's approach will be a useful example
http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/PartitioningTables.htm
http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATETABLE.htm
... [ PARTITION BY partition-c
On Wed, Dec 03, 2014 at 10:00:26AM -0300, Alvaro Herrera wrote:
> Amit Langote wrote:
>
> > From: Robert Haas [mailto:robertmh...@gmail.com]
>
> > > What is an overflow partition and why do we want that?
> >
> > That would be a default partition. That is, where the tuples that
> > don't belong e
Amit Langote wrote:
> From: Robert Haas [mailto:robertmh...@gmail.com]
> > What is an overflow partition and why do we want that?
>
> That would be a default partition. That is, where the tuples that
> don't belong elsewhere (other defined partitions) go. VALUES clause of
> the definition for su
On 12/2/14, 9:43 PM, Amit Langote wrote:
>What is an overflow partition and why do we want that?
>
That would be a default partition. That is, where the tuples that don't belong
elsewhere (other defined partitions) go. VALUES clause of the definition for
such a partition would look like:
(a r
Hi Robert,
From: Robert Haas [mailto:robertmh...@gmail.com]
> > * Catalog schema:
> >
> > CREATE TABLE pg_catalog.pg_partitioned_rel
> > (
> >partrelidoidNOT NULL,
> >partkindoidNOT NULL,
> >partissub bool NOT NULL,
> >partkey
On Tue, Nov 25, 2014 at 8:20 PM, Amit Langote
wrote:
>> Before going too much further with this I'd mock up schemas for your
>> proposed catalogs and a list of DDL operations to be supported, with
>> the corresponding syntax, and float that here for comment.
More people should really comment on t
Sorry, a correction:
> CREATE TABLE pg_catalog.pg_partitioned_rel
> (
>partrelidoidNOT NULL,
>partkindoidNOT NULL,
>partissub bool NOT NULL,
>partkey int2vector NOT NULL, -- partitioning attributes
>partopcl
Hi,
> > I'm wondering here if it's better to keep partition values per partition
> > wherein we have two catalogs, say, pg_partitioned_rel and pg_partition_def.
> >
> > pg_partitioned_rel stores information like partition kind, key (attribute
> > number(s)?), key opclass(es). Optionally, we could
On Wed, Nov 19, 2014 at 10:27 PM, Amit Langote
wrote:
>> Maybe as anyarray, but I think pg_node_tree
>> might even be better. That can also represent data of some arbitrary
>> type, but it doesn't enforce that everything is uniform. So you could
>> have a list of objects of the form {RANGEPARTI
Robert,
>
> I thought putting the partition boundaries into pg_inherits was a
> strange choice. I'd put it in pg_class, or in pg_partition if we
> decide to create that.
Hmm, yeah I guess we are better off using pg_inherits for just saying that a
partition is an inheritance child. Other deta
On Thu, Nov 13, 2014 at 9:12 PM, Stephen Frost wrote:
>> > I'm not a fan of using pg_class- there are a number of columns in there
>> > which I would *not* wish to be allowed to be different per partition
>> > (starting with relowner and relacl...). Making those NULL would be just
>> > as bad (pr
Robert,
* Robert Haas (robertmh...@gmail.com) wrote:
> On Thu, Nov 13, 2014 at 1:39 AM, Stephen Frost wrote:
> > but
> > with declarative partitioning, I expect us to eventually be able to
> > eliminate complete partitions from consideration on both sides of a
> > partition-table join and optimiz
On Thu, Nov 13, 2014 at 1:39 AM, Stephen Frost wrote:
> Agreed- a node tree seems a bit too far to make this really work well..
> But, I'm curious what you were thinking specifically?
I gave a pretty specific example in my email.
> A node tree which
> accepts an "argument" of the constant used i
> ow...@postgresql.org] On Behalf Of Amit Langote
> Sent: Thursday, November 13, 2014 3:50 PM
>
> Greenplum uses a single table for this purpose with separate columns for
range
> and list cases, for example. They store allowed values per partition though.
> They have 6 partitioning related catalo
> From: Stephen Frost [mailto:sfr...@snowman.net]
> Sent: Thursday, November 13, 2014 3:40 PM
>
> > The point for me is just that range and list partitioning probably
> > need different structure, and hash partitioning, if we want to support
> > that, needs something else again. Range partitioni
* Robert Haas (robertmh...@gmail.com) wrote:
> On Wed, Nov 12, 2014 at 5:06 PM, Tom Lane wrote:
> > Robert Haas writes:
> >> Maybe as anyarray, but I think pg_node_tree
> >> might even be better. That can also represent data of some arbitrary
> >> type, but it doesn't enforce that everything is
On 11/12/14, 5:27 PM, Robert Haas wrote:
Maybe as anyarray, but I think pg_node_tree
>>might even be better. That can also represent data of some arbitrary
>>type, but it doesn't enforce that everything is uniform.
>
>Of course, the more general you make it, the more likely that it'll be
>impos
On Wed, Nov 12, 2014 at 5:06 PM, Tom Lane wrote:
> Robert Haas writes:
>> I thought putting the partition boundaries into pg_inherits was a
>> strange choice. I'd put it in pg_class, or in pg_partition if we
>> decide to create that.
>
> Yeah. I rather doubt that we want this mechanism to be ve
Robert Haas writes:
> I thought putting the partition boundaries into pg_inherits was a
> strange choice. I'd put it in pg_class, or in pg_partition if we
> decide to create that.
Yeah. I rather doubt that we want this mechanism to be very closely
tied to the existing inheritance features. If
On Mon, Nov 10, 2014 at 8:53 PM, Amit Langote
wrote:
> Above commands are merely transformed into ALTER TABLE subcommands that
> arrange
> partitioned table and partitions into inheritance hierarchy, but with extra
> information, that is, allowed values for the partition in a new anyarray
> colu
Hi,
> From: Robert Haas [mailto:robertmh...@gmail.com]
> Sent: Saturday, November 08, 2014 5:41 AM
>
> I'd be in favor of that.
Thanks!
> I am not sure whether the code is close
> enough to what we need to be really useful, but that's for you to
> decide.
Hmm, I'm not entirely convinced about
On Thu, Nov 6, 2014 at 9:17 PM, Amit Langote
wrote:
> I mentioned upthread about the possibility of resurrecting Itagaki-san's
> patch [1] to try to make things work in this direction. I would be willing to
> spend time on this. I see useful reviews of the patch by Robert [2], Simon
> [3] at
Hi,
> ow...@postgresql.org] On Behalf Of Robert Haas
> Sent: Tuesday, October 28, 2014 9:20 PM
>
> On Tue, Oct 28, 2014 at 6:06 AM, Andres Freund
> wrote:
> > In my opinion we can reuse (some of) the existing logic for INHERITS to
> > implement "proper" partitioning, but that should be an imple
On 2014-10-28 08:19:36 -0400, Robert Haas wrote:
> On Tue, Oct 28, 2014 at 6:06 AM, Andres Freund wrote:
> > In my opinion we can reuse (some of) the existing logic for INHERITS to
> > implement "proper" partitioning, but that should be an implementation
> > detail.
>
> Sure, that would be a sens
On Tue, Oct 28, 2014 at 6:06 AM, Andres Freund wrote:
> In my opinion we can reuse (some of) the existing logic for INHERITS to
> implement "proper" partitioning, but that should be an implementation
> detail.
Sure, that would be a sensible way to do it. I mostly care about not
throwing out all
On 2014-10-28 14:34:22 +0900, Amit Langote wrote:
>
> Hi,
>
> > From: Andres Freund [mailto:and...@2ndquadrant.com]
> > On 2014-10-27 06:29:33 -0300, Alvaro Herrera wrote:
> > > Amit Langote wrote:
> > > > FWIW, I think Robert's criticism regarding not basing this on
> inheritance
> > > > scheme
Hi,
> From: Andres Freund [mailto:and...@2ndquadrant.com]
> On 2014-10-27 06:29:33 -0300, Alvaro Herrera wrote:
> > Amit Langote wrote:
> > > FWIW, I think Robert's criticism regarding not basing this on
inheritance
> > > scheme was not responded to.
> >
> > It was responded to by ignoring it. I
On 2014-10-27 06:29:33 -0300, Alvaro Herrera wrote:
> Amit Langote wrote:
>
> > > On Mon, Oct 13, 2014 at 04:38:39PM -0300, Alvaro Herrera wrote:
> > > > Bruce Momjian wrote:
> > > > > I realize there hasn't been much progress on this thread, but I wanted
> > > > > to chime in to say I think our c
Amit Langote wrote:
> > On Mon, Oct 13, 2014 at 04:38:39PM -0300, Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > > I realize there hasn't been much progress on this thread, but I wanted
> > > > to chime in to say I think our current partitioning implementation is
> > > > too heavy administ
Hi,
> On Mon, Oct 13, 2014 at 04:38:39PM -0300, Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> > > I realize there hasn't been much progress on this thread, but I wanted
> > > to chime in to say I think our current partitioning implementation is
> > > too heavy administratively, error-prone, an
On Mon, Oct 13, 2014 at 04:38:39PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Fri, Aug 29, 2014 at 11:56:07AM -0400, Alvaro Herrera wrote:
> > > Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
> > > reference Tom's post
> > > http://www.postgresql.org/message-
Bruce Momjian wrote:
> On Fri, Aug 29, 2014 at 11:56:07AM -0400, Alvaro Herrera wrote:
> > Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
> > reference Tom's post
> > http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us
> > which mentions the possibility of a d
On Fri, Aug 29, 2014 at 11:56:07AM -0400, Alvaro Herrera wrote:
> Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
> reference Tom's post
> http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us
> which mentions the possibility of a different partitioning
> impleme
t...@sss.pgh.pa.us;
> alvhe...@2ndquadrant.com
> Subject: Re: [HACKERS] On partitioning
>
> Hi,
>
Apologize for having broken the original thread. :(
This was supposed to in reply to -
http://www.postgresql.org/message-id/CA+Tgmob5DEtO4SbD15q0OQJjyc05cTk8043Utwu_
=xdtvyg...@mail.gmail.co
Hi,
I tend to agree with Robert that partitioning should continue using
inheritance based implementation. In addition to his point about reinventing
things it could be pointed out that there are discussions/proposals elsewhere
about building foreign table inheritance capability; having partitionin
On Tue, Sep 2, 2014 at 4:18 PM, Martijn van Oosterhout
wrote:
> On Tue, Sep 02, 2014 at 09:44:17AM -0400, Bruce Momjian wrote:
>> On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote:
>> > There is one situation where you need to be more flexible, and that is
>> > if you ever wan
On Tue, Sep 02, 2014 at 09:44:17AM -0400, Bruce Momjian wrote:
> On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote:
> > There is one situation where you need to be more flexible, and that is
> > if you ever want to support online repartitioning. To do that you have
> > to disti
On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote:
> There is one situation where you need to be more flexible, and that is
> if you ever want to support online repartitioning. To do that you have
> to distinguish between "I want to insert tuple X, which partition
> should it g
On 09/01/2014 04:03 AM, Tom Lane wrote:
> I think one of the key design decisions we have to make is whether
> partitions are all constrained to have exactly the same set of indexes.
... and a lot of that comes down to what use cases the partitioning is
meant to handle, and what people are expect
On 09/01/2014 11:52 PM, Andres Freund wrote:
> I personally think that implementing cross partition indexes has a low
> enough cost/benefit ratio that I doubt it's wise to tackle it anytime
> soon.
UNIQUE constraints on partitioned tables (and thus foreign key
constraints pointing to partitioned t
On 09/01/2014 05:52 PM, Andres Freund wrote:
> On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
>> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
>> pagenr, tupnr).
>>
>> Cross-partition indexes are especially needed if we want to allow putting
>> UNIQUE constraints on
On 09/01/2014 06:59 PM, Tom Lane wrote:
Greg Stark writes:
On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane wrote:
Aside from costing planning time, most likely that would forever prevent
us from pushing some types of intelligence about partitioning into the
executor.
How would it affect this cal
On 2014-09-01 11:59:37 -0400, Tom Lane wrote:
> Greg Stark writes:
> > On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane wrote:
> >> Aside from costing planning time, most likely that would forever prevent
> >> us from pushing some types of intelligence about partitioning into the
> >> executor.
>
> > H
On Mon, Sep 1, 2014 at 4:59 PM, Tom Lane wrote:
> The "push into executor" idea I was alluding to is that we might invent
> plan constructs like a ModifyTable node that applies to a whole
> inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be
> done at runtime. You're not going t
On 2014-08-31 16:03:30 -0400, Tom Lane wrote:
> Another thought about this general topic:
>
> Alvaro Herrera writes:
> > ...
> > Allowed actions on a RELKIND_PARTITION:
> > * CREATE INDEX .. ON PARTITION ON TABLE
> > ...
> > Still To Be Designed
> >
> > * Are indexes/constr
Greg Stark writes:
> On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane wrote:
>> Aside from costing planning time, most likely that would forever prevent
>> us from pushing some types of intelligence about partitioning into the
>> executor.
> How would it affect this calculus if there were partitioned i
On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
> pagenr, tupnr).
>
> Cross-partition indexes are especially needed if we want to allow putting
> UNIQUE constraints on non-partition-key columns.
I actually don't think
On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane wrote:
> Aside from costing planning time, most likely that would forever prevent
> us from pushing some types of intelligence about partitioning into the
> executor.
How would it affect this calculus if there were partitioned indexes
which were created o
On Fri, Aug 29, 2014 at 12:35:50PM -0400, Tom Lane wrote:
> > Each partition is assigned an Expression that receives a tuple and
> > returns boolean. This expression returns true if a given tuple belongs
> > into it, false otherwise.
>
> -1, in fact minus a lot. One of the core problems of the c
On 08/31/2014 10:03 PM, Tom Lane wrote:
> Another thought about this general topic:
>
> Alvaro Herrera writes:
>> ...
>> Allowed actions on a RELKIND_PARTITION:
>> * CREATE INDEX .. ON PARTITION ON TABLE
>> ...
>> Still To Be Designed
>>
>> * Are indexes/constraints inherite
Another thought about this general topic:
Alvaro Herrera writes:
> ...
> Allowed actions on a RELKIND_PARTITION:
> * CREATE INDEX .. ON PARTITION ON TABLE
> ...
> Still To Be Designed
>
> * Are indexes/constraints inherited from the parent rel?
I think one of the key desig
On Sat, Aug 30, 2014 at 12:56 AM, Alvaro Herrera
wrote:
> Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
> reference Tom's post
> http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us
> which mentions the possibility of a different partitioning
> implementation
On Fri, Aug 29, 2014 at 11:56 AM, Alvaro Herrera
wrote:
> In this design, partitions are first-class objects, not normal tables in
> inheritance hierarchies. There are no pg_inherits entries involved at all.
Whoa. I always assumed that table inheritance was a stepping-stone to
real partitioning
Hannu Krosing wrote:
> Cross-partition indexes are especially needed if we want to allow putting
> UNIQUE constraints on non-partition-key columns.
I'm not going to implement cross-partition indexes in the first patch.
They are a huge can of worms.
--
Álvaro Herrerahttp://www.2n
On 08/29/2014 05:56 PM, Alvaro Herrera wrote:
> Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
> reference Tom's post
> http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us
> which mentions the possibility of a different partitioning
> implementation than what
On 08/29/2014 07:15 PM, Tom Lane wrote:
> Alvaro Herrera writes:
>> Tom Lane wrote:
>>> One other interesting thought that occurs to me: are we going to support
>>> UPDATEs that cause a row to belong to a different partition? If so, how
>>> are we going to handle the update chain links?
>> Bah, I
Andres Freund writes:
> On 2014-08-29 13:29:19 -0400, Tom Lane wrote:
>> An actual fix would presumably involve adding a partition number to the
>> ctid chain field in tuples in partitioned tables. The reason I bring it
>> up now is that we'd have to commit to doing that (or at least leaving room
On 2014-08-29 13:29:19 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2014-08-29 13:15:16 -0400, Tom Lane wrote:
> >> Hm. I certainly agree that it's a case that could be disallowed for a
> >> first cut, but it'd be nice to have some clue about how we might allow it
> >> eventually.
>
> >
Tom Lane wrote:
> Alvaro Herrera writes:
> > Tom Lane wrote:
> >> One other interesting thought that occurs to me: are we going to support
> >> UPDATEs that cause a row to belong to a different partition? If so, how
> >> are we going to handle the update chain links?
>
> > Bah, I didn't mention
Andres Freund writes:
> On 2014-08-29 13:15:16 -0400, Tom Lane wrote:
>> Hm. I certainly agree that it's a case that could be disallowed for a
>> first cut, but it'd be nice to have some clue about how we might allow it
>> eventually.
> Not pretty, but we could set t_ctid to some 'magic' value w
On 2014-08-29 13:15:16 -0400, Tom Lane wrote:
> Alvaro Herrera writes:
> > Tom Lane wrote:
> >> One other interesting thought that occurs to me: are we going to support
> >> UPDATEs that cause a row to belong to a different partition? If so, how
> >> are we going to handle the update chain links?
Alvaro Herrera writes:
> Tom Lane wrote:
>> One other interesting thought that occurs to me: are we going to support
>> UPDATEs that cause a row to belong to a different partition? If so, how
>> are we going to handle the update chain links?
> Bah, I didn't mention it? My current thinking is th
Tom Lane wrote:
> Greg Stark writes:
> > And I think there are still unanswered questions about indexes.
>
> One other interesting thought that occurs to me: are we going to support
> UPDATEs that cause a row to belong to a different partition? If so, how
> are we going to handle the update chai
Greg Stark writes:
> And I think there are still unanswered questions about indexes.
One other interesting thought that occurs to me: are we going to support
UPDATEs that cause a row to belong to a different partition? If so, how
are we going to handle the update chain links?
2014-08-29 18:35 GMT+02:00 Tom Lane :
> Alvaro Herrera writes:
> > [ partition sketch ]
>
> > In this design, partitions are first-class objects, not normal tables in
> > inheritance hierarchies. There are no pg_inherits entries involved at
> all.
>
> Hm, actually I'd say they are *not* first cl
On Fri, Aug 29, 2014 at 4:56 PM, Alvaro Herrera
wrote:
> For scan plans, we need to prepare Append lists which are used to scan
> for tuples in a partitioned relation. We can setup fake constraint
> expressions based on the partitioning expressions, which let the planner
> discard unnecessary par
Alvaro Herrera writes:
> [ partition sketch ]
> In this design, partitions are first-class objects, not normal tables in
> inheritance hierarchies. There are no pg_inherits entries involved at all.
Hm, actually I'd say they are *not* first class objects; the problem with
the existing design is
101 - 179 of 179 matches
Mail list logo
