Thread: default child of partition master

I'm using partitioned tables a lot and loving it. I have a suggestion
that I believe would make it easier to bring the performance and
maintenance advantages of partitions to more applications and users:

As I understand it, at present I can select records from sales_master
and get data returned from all tables that inherit sales_master:

sales_2006_q1
sales_2006_q2
sales_2006_q3
...

But INSERT and certain other operations must specify the destination
table. (Only makes sense.)

My suggestion is to allow specifying a default destination table in
the master partition table definition. This default destination table
could be changed with ALTER TABLE.

This would make it simpler to adapt existing applications to use
partitions. Instead of having to edit the table name in every location
in every application that accesses a particular table - the partition
master can be created with the name all those applications expect.

The existing data is then placed into partitions split by date  - and
the applications continue to function as expected.

A scheduled process creates a new table that inherits the master as
needed: sales_2006_q4, sales_2007_q1 etc - inheriting from
sales_master. And each time, sales_master is altered to set the
default table to, for instance sales_2006_q4.

The need I have for this right now is for dbmail - a mail store in SQL
that "supports" both mysql and postgresql. It's pretty good - I've
been using it for some years - but they don't seem to know much about
postgresql. The table that holds the message blocks has a huge amount
of deletes and inserts. They do have a maintenance util which runs on
a cron frequently but... this is a live mail system - one cannot do
operations that prevent mail being delivered - and the run times for
full vacuums on the whole message blocks table or whole db are huge.

This table would be extremely enhanced by partitions split on date.
However it is extermely unlikely that I could get the dbmail
developers (and everyone else who has written related apps that access
the dbmail tables, including me) to alter their code so that it
selects from a master table but inserts to an *ever-changing other
table*.

For one thing, every type of maintenance could be done on the older
tables without affecting mail delivery into the current table. Users
who access only recent mail wouldn't suffer due to the size of the
table caused by users who keep a lot of mail on the server. Etc.

If the "specify a default table for INSERTs" (and other operations
that can't be sent to the partition master) feature were added - it
would allow users of postgresql and dbmail - (and many other apps in
similar situations) to independently choose to run partitions.

Thank you for your consideration,

- April Lorenzen

"April Lorenzen" <outboundindex@gmail.com> writes:
> My suggestion is to allow specifying a default destination table in
> the master partition table definition. This default destination table
> could be changed with ALTER TABLE.

The recommended way to do this is with an ON INSERT DO INSTEAD rule
on the master table.  The advantage of using rules is that you can have
several conditional rules to ensure that insertions go into the right
sub-tables, whereas a default would likely just fail :-(
        regards, tom lane

Keep in mind if you have multiple rules for a master table, it won't return the number of affected rows as you might expect. This screws up Hibernate which I'm using for my application. It checks the return value to make sure it was inserted properly. Luckily I only need one rule which puts it into the "current" child table (im partitioning on current timestamp). I suppose I could get around this by using a stored procedure or something but that would not be as portable. I'm looking forward to future versions of PG which automate more of the partitioning features :) keep up the good work!

Gene




--
Eugene Hart

On Sun, Aug 20, 2006 at 12:58:31PM -0400, Gene wrote:
> Keep in mind if you have multiple rules for a master table, it won't return
> the number of affected rows as you might expect. This screws up Hibernate
> which I'm using for my application. It checks the return value to make sure
> it was inserted properly. Luckily I only need one rule which puts it into
> the "current" child table (im partitioning on current timestamp). I suppose
> I could get around this by using a stored procedure or something but that
> would not be as portable. I'm looking forward to future versions of PG which
> automate more of the partitioning features :) keep up the good work!

Sounds like a bug in Hibernate. It should be checking for errors
instead.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461