Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql partitioning

Postgresql partitioning

From
"Ram Ravichandran"
Date:
Hey,

Suppose I have a table with the following fields:

CREATE TABLE distributors (
     id     DECIMAL(3) PRIMARY KEY,
     name    VARCHAR(40),
     status INTEGER
 );

I would ike to partition this table based on status which can be [0,1,2,3,4].

I was wondering if the records can change their status. i.e. If I did
UPDATE distributors SET status = 4 WHERE id = 231122;

would POSTGRESQL automatically change the record from the current
partition (say partition where status = 3) to the partition where
status = 4?
Or would I have to explicitly delete it from one partition table, and
reinsert it in the other?

Thanks,

Ram

Re: Postgresql partitioning

From
Erik Jones
Date:
On Mar 21, 2008, at 7:15 PM, Ram Ravichandran wrote:

> Hey,
>
> Suppose I have a table with the following fields:
>
> CREATE TABLE distributors (
>     id     DECIMAL(3) PRIMARY KEY,
>     name    VARCHAR(40),
>     status INTEGER
> );
>
> I would ike to partition this table based on status which can be
> [0,1,2,3,4].
>
> I was wondering if the records can change their status. i.e. If I did
> UPDATE distributors SET status = 4 WHERE id = 231122;
>
> would POSTGRESQL automatically change the record from the current
> partition (say partition where status = 3) to the partition where
> status = 4?

No.  Assuming you have CHECK constraints on you partition tables and
constraint_exclusion=on postgres will emit an error on an update like
that.

> Or would I have to explicitly delete it from one partition table, and
> reinsert it in the other?

Yes.  Also, note that this can't be done in an ON UPDATE trigger as
CHECK constraints are checked before any triggers are run.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: Postgresql partitioning

From
"Ram Ravichandran"
Date:
Thanks for the quick response. And I assume that primary key
uniqueness is not tested across tables. Right?

Thanks,
Ram

On Fri, Mar 21, 2008 at 8:59 PM, Erik Jones <erik@myemma.com> wrote:
> On Mar 21, 2008, at 7:15 PM, Ram Ravichandran wrote:
>
>  > Hey,
>  >
>  > Suppose I have a table with the following fields:
>  >
>  > CREATE TABLE distributors (
>  >     id     DECIMAL(3) PRIMARY KEY,
>  >     name    VARCHAR(40),
>  >     status INTEGER
>  > );
>  >
>  > I would ike to partition this table based on status which can be
>  > [0,1,2,3,4].
>  >
>  > I was wondering if the records can change their status. i.e. If I did
>  > UPDATE distributors SET status = 4 WHERE id = 231122;
>  >
>  > would POSTGRESQL automatically change the record from the current
>  > partition (say partition where status = 3) to the partition where
>  > status = 4?
>
>  No.  Assuming you have CHECK constraints on you partition tables and
>  constraint_exclusion=on postgres will emit an error on an update like
>  that.
>
>
>  > Or would I have to explicitly delete it from one partition table, and
>  > reinsert it in the other?
>
>  Yes.  Also, note that this can't be done in an ON UPDATE trigger as
>  CHECK constraints are checked before any triggers are run.
>
>  Erik Jones
>
>  DBA | Emma(R)
>  erik@myemma.com
>  800.595.4401 or 615.292.5888
>  615.292.0777 (fax)
>
>  Emma helps organizations everywhere communicate & market in style.
>  Visit us online at http://www.myemma.com
>
>
>
>

Re: Postgresql partitioning

From
Reece Hart
Date:
On Fri, 2008-03-21 at 23:00 -0400, Ram Ravichandran wrote:
> I assume that primary key
> uniqueness is not tested across tables. Right?

That's correct.  It's on the TODOs:
Inheritance
      * Allow inherited tables to inherit indexes, UNIQUE constraints,
        and primary/foreign keys
(at http://www.postgresql.org/docs/faqs.TODO.html )


I wonder whether you might be able to achieve the benefits of
partitioning and the simplicity of a single-table updates by using a
view with an update rule. This would allow you to embed the logic for
moving rows between partitions when the partition criterion changes into
the database. I've not done this myself, so I'm, um, not speaking from
experience.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: Postgresql partitioning

From
Robert Treat
Date:
On Saturday 22 March 2008 09:39, Reece Hart wrote:
> On Fri, 2008-03-21 at 23:00 -0400, Ram Ravichandran wrote:
> > I assume that primary key
> > uniqueness is not tested across tables. Right?
>
> That's correct.  It's on the TODOs:
> Inheritance
>       * Allow inherited tables to inherit indexes, UNIQUE constraints,
>         and primary/foreign keys
> (at http://www.postgresql.org/docs/faqs.TODO.html )
>
>
> I wonder whether you might be able to achieve the benefits of
> partitioning and the simplicity of a single-table updates by using a
> view with an update rule. This would allow you to embed the logic for
> moving rows between partitions when the partition criterion changes into
> the database. I've not done this myself, so I'm, um, not speaking from
> experience.
>

Actually you can add an update rule to the parent table itself,  rewriting
into a set of insert, delete statements. (or call a function to manage it
which is probably better on a larger number of partitions)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL