Postgresql partitioning

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

Thanks for the quick response. And I assume that primary key
uniqueness is not tested across tables. Right?

Thanks,
Ram

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

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