R: Table Partitioning is in 8.1

From: "Paolo Magnoli" <pmagnoli(at)systemevolution(dot)it>
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: R: Table Partitioning is in 8.1
Date: 2005-09-23 10:30:43
Message-ID: ILEALHCEJHFMKFCLMCFGCEIJCAAA.pmagnoli@systemevolution.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, I seem to recall that in Oracle you load into specific partitions
without specifically naming them in insert statements (in other words you
insert into table, the engine redirects data to the corrisponding
partition), I quickly looked at postgresql partitioning and it seems to me
that you need to insert into a specified derived table (unless you set up
rules).
It would be good to have an insert behaviour similar to Oracle by default.
Also I see that the original table is always scanned, partition exclusion
happens only on the derived tables, is this correct?
Regards

paolo

> -----Messaggio originale-----
> Da: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]Per conto di Simon Riggs
> Inviato: venerdì 23 settembre 2005 11.51
> A: Jim C. Nasby
> Cc: Tom Lane; pgsql-hackers(at)postgresql(dot)org
> Oggetto: Re: [HACKERS] Table Partitioning is in 8.1
>
>
> On Thu, 2005-09-22 at 14:37 -0500, Jim C. Nasby wrote:
> > On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote:
> > > On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> > > > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > > > Is it possible that the Release Notes do not fully explain the
> > > > > Constraint Exclusion feature? Or is it the consensus that
> it works but
> > > > > not quite well enough to make a song and dance about yet?
> > > >
> > > > I hardly think that the existing constraint-exclusion code
> is enough for
> > > > us to claim we "support table partitioning". There's too much grunt
> > > > work that the DBA still has to do to set up a partitioning
> arrangement.
> > >
> > > So you think the DBA can do partitioning? Good.
> > >
> > > Setting up partitioning in Oracle or SQLServer2005 requires lots of
> > > syntax and multiple commands. There are fewer commands with PostgreSQL
> > > and they are ISO/ANSI compliant also.
> >
> > Actually, IIRC it takes 2 commands; one to initially setup the
> > partitioning and one to create new partitions as needed. 3 commands if
> > you count DROP PARTITON.
>
> Which is what it takes in PostgreSQL.
>
> > It's been a while since I looked at what you've done, but I seem to
> > recall needing to manually maintain rules every time you create a new
> > partition.
>
> All current implementations I am aware of require Declarative statements
> each time a new partition is started.
>
> But there is no *need* to create RULEs. You would need to do that if you
> wanted automatic routing of INSERT statements. The current PostgreSQL
> implementation does not do this, and yes, this would require creating a
> set of Rule statements to do this - though possible to do this
> automatically with a simple script. My experience with Oracle has been
> that one loads into specifically identified partitions, so the loading
> case for PostgreSQL is essentially identical to the common case for
> Oracle. My experience may not be everybody's, so I may be off-base on
> this, but I've not put a priority on solving that issue. Do people
> consider that aspect essential to the wider use of partitioning?
>
> I don't consider CE to be complete, any more than any newly introduced
> major feature set, but IMHO the important first order functionality is
> now available in PostgreSQL.
>
> I'm looking at probably Tuesday to submit first draft docs.
>
> Best Regards, Simon Riggs
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2005-09-23 10:56:32 Re: R: Table Partitioning is in 8.1
Previous Message Dave Page 2005-09-23 10:29:06 Re: pgxs and pginstaller