Thread: Auto-partitioning?

As I understand partitioning, you can automatic "locate the partition into which a
row should be added" by adding rules such as (from the documentation):

----------------------------------------------------------
CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
     ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
     INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
     ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
     INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
--------------------------------------------------------

Is it possible to add a default rule that gets invoked if all the
above rules fail to match?  Specifically, I'm wondering if it's
possible to add a default rule that would create a new partition
(with indices, etc.) and would add a new rule for this partition
to match the similar ones above (and, of course, then move the
INSERT into the new partition).

In our case, the partitioning would be simple list partitioning on
the values of an 'experiment id', so I'm not concerned about trying
to create a range-based WHERE clause in the new rule.

Thanks!
Steve
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

Steve Wampler wrote:
> ... Specifically, I'm wondering if it's
> possible to add a default rule that would create a new partition
> (with indices, etc.) and would add a new rule for this partition
> to match the similar ones above (and, of course, then move the
> INSERT into the new partition).

I think I've answered my own question, but would love an expert
to validate the answer:

   The answer is "no", apparently because 'name' in

      CREATE RULE name ...

   can't be an expression.  So there's no way to dynamically create a
   sequence of rules because you can't create new rule names inside
   the body of the 'default' rule.  Ergo, no way to to auto-partitioning.

Is that correct?  Is that the only reason, or is 'create rule...'
simply not something that can be done inside another 'create rule...'?

It's possible to have a 'default' rule (of sorts) by taking advantage
of the alphabetic order of rule application, but that rule is unable
to produce new rules.  Pity.

Thanks,
Steve
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Steve Wampler wrote:
> Steve Wampler wrote:
>> ... Specifically, I'm wondering if it's
>> possible to add a default rule that would create a new partition
>> (with indices, etc.) and would add a new rule for this partition
>> to match the similar ones above (and, of course, then move the
>> INSERT into the new partition).
>
> I think I've answered my own question, but would love an expert
> to validate the answer:
>
>   The answer is "no", apparently because 'name' in

That answer is no but you could probably pull it off with a trigger.

Sincerely,

Joshua D. Drake


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGy1BNATb/zqfZUUQRAlhxAJsEpKJicoMkvFXS+T5DiCjroSYj9QCcCjmt
qaaXSe764ULKH5h3z8p6QUc=
=2xC8
-----END PGP SIGNATURE-----

Hi

Just a hint.
We do this auto-partitioning with PgQ. Instead of writing records into table we push them into queue and use consumer called table_dispatcher to creates tartitioned tables as needed and put records into them. We have multiple destination databases where to write data and target tables have different structures so queue based solution is convenient for us.

Asko

"use consumer called table_dispatcher"... could you elaborate this
further on how to do this?

Thanks,
G


On Aug 22, 3:27 pm, asc...@gmail.com ("Asko Oja") wrote:
> Hi
>
> Just a hint.
> We do this auto-partitioning with PgQ. Instead of writing records into table
> we push them into queue and use consumer called table_dispatcher to creates
> tartitioned tables as needed and put records into them. We have multiple
> destination databases where to write data and target tables have different
> structures so queue based solution is convenient for us.
>
> Asko
>
> On 8/21/07, Joshua D. Drake <j...@commandprompt.com> wrote:
>
>
>
>
>
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
>
> > Steve Wampler wrote:
> > > Steve Wampler wrote:
> > >> ... Specifically, I'm wondering if it's
> > >> possible to add a default rule that would create a newpartition
> > >> (with indices, etc.) and would add a new rule for thispartition
> > >> to match the similar ones above (and, of course, then move the
> > >> INSERT into the newpartition).
>
> > > I think I've answered my own question, but would love an expert
> > > to validate the answer:
>
> > >   The answer is "no", apparently because 'name' in
>
> > That answer is no but you could probably pull it off with atrigger.
>
> > Sincerely,
>
> > Joshua D. Drake
>
> > - --
>
> >       === The PostgreSQL Company: Command Prompt, Inc. ===
> > Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
> > PostgreSQL solutions since 1997  http://www.commandprompt.com/
> >                         UNIQUE NOT NULL
> > Donate to the PostgreSQL Project:http://www.postgresql.org/about/donate
> > PostgreSQL Replication:http://www.commandprompt.com/products/
>
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.4.6 (GNU/Linux)
> > Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org
>
> > iD8DBQFGy1BNATb/zqfZUUQRAlhxAJsEpKJicoMkvFXS+T5DiCjroSYj9QCcCjmt
> > qaaXSe764ULKH5h3z8p6QUc=
> > =2xC8
> > -----END PGP SIGNATURE-----
>
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match- Hide quoted text -
>
> - Show quoted text -