
> > What you need is one view that is a union all select selecting all
your
> > partial tables, all applications only accesses this one view.  You
need 1
> > insert 2 update 1 delete rule for each table plus 3 remainder rules
(=4*N
> > + 3 rules) (the select rule is already done by create view), plus
one
> > check constraint for each partial table.
> 
> WOW! You *are* good. Care to reveal the trick? I can see the insert
and
> delete rules but what are you doing for the updates?

The update needs one for the case where the tuple stays in the same
partial table, 
and the second for moving it to another table. The first is simple
(where old.x = new.x),
the second must delete the original and insert a new row into the view.
The view insert 
rule handles the distribution (might be, that there are TODO's here, my
data stays in 
place). The additional problem with the second rule is, that the
returned UPDATE tag (iirc)
does not return the correct number of rows (deleted + inserted rows, see
lengthy thread 
about the return tag). Whether that is an actual problem will depend on
your apps.

Andreas

Mon, 19 May 2003 15:58:24 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Feature suggestions (long)
On Mon, May 19, 2003 at 03:58:24PM +0200, Zeugswetter Andreas SB SD wrote:
> The update needs one for the case where the tuple stays in the same
> partial table, and the second for moving it to another table. The first is
> simple (where old.x = new.x), the second must delete the original and
> insert a new row into the view. The view insert rule handles the
> distribution (might be, that there are TODO's here, my data stays in
> place). The additional problem with the second rule is, that the returned
> UPDATE tag (iirc) does not return the correct number of rows (deleted +
> inserted rows, see lengthy thread about the return tag). Whether that is
> an actual problem will depend on your apps.

Hmm, it's easy when you put it like that. Still, each update will be
converted into a whole host of inserts and deletes, so your N squared
complexity is still there. One thing is that the rules are always expanded,
whether or not any rows match. You'll get lots and lots of little queries.
One of my other wishes was for the EXPLAIN output to remain somewhat
legible.

Also, such a setup could never take advantage of the multi-table indexes I
suggested, since the whole benefit of those comes from treating a whole
inheritence hierarchy as a single virtual table (the UNIQUEness checks are
just a really cool bonus).

Anyway, the general trend seems to be against the idea so I may as well go
think of something else :)
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington
Tue, 20 May 2003 00:40:00 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: Feature suggestions (long)
On Tue, May 20, 2003 at 12:40:00AM +1000, Martijn van Oosterhout wrote:
> Anyway, the general trend seems to be against the idea so I may as well go
> think of something else :)

I'm disappointed to hear that. Having no way to effectively partition
data is a real pain in pgsql, and your proposal would adress that. Yes,
you can build it yourself by creating the view and all the rules by
hand, but that has a lot of drawbacks:

It's completely PGSQL specific
It leaves no possibility for performance improvements down the road
It's a lot of code to write
You have to manually maintain it all every time you need to add a new
partition (in your example, at the start of every year).

I don't know what the policies for patches are, but I'd hope that the
core team would consider adding this functionality, especially since a
first-round implimentation can be done entirely with rules (or so it
seems).

I certainly understand that development time is a very limited resource,
and I'm willing to work on this (though I'm not a C coder). Even if no
one can commit to this right now, can't it be added to the todo list?
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Tue, 20 May 2003 10:02:24 -0500
"Jim C. Nasby" <jim@nasby.net>
Re: Feature suggestions (long)
On Tue, May 20, 2003 at 10:02:24AM -0500, Jim C. Nasby wrote:
> On Tue, May 20, 2003 at 12:40:00AM +1000, Martijn van Oosterhout wrote:
> > Anyway, the general trend seems to be against the idea so I may as well go
> > think of something else :)
> 
> I'm disappointed to hear that. Having no way to effectively partition
> data is a real pain in pgsql, and your proposal would adress that. Yes,
> you can build it yourself by creating the view and all the rules by
> hand, but that has a lot of drawbacks:

I agree, there is a lot of potential here. And I don't beleive it would be
too much work as most of the infrastructure is already there. At this stage
I'm just wondering if it will go on the TODO list. I propose that the
following items be added:

   * Improve the planner to take CHECK constraints into account to prune the plan.
   * Allow a single index to index multiple tables (also for inherited PRIMARY KEYS)
   * Allow partitioning of table into multiple subtables

The first two items would be useful in their own right. With them the final
one would be straight forward. I'd be prepared to put some effort into this
if there is some indication it would be accepted.

> I don't know what the policies for patches are, but I'd hope that the
> core team would consider adding this functionality, especially since a
> first-round implimentation can be done entirely with rules (or so it
> seems).

Well, I think the policy is 'if you write the code you have a better chance
to have it accepted' :) So, if it's likely to be accepted then we only need
to find someone to code it. Given the other priorities currently I think
waiting for the core team to write it would be futile (unless you can
convince someone like IBM to give the core team money to write it).

Right now I'd be happy if the anonymous CVS server would talk to me :)

By the way, has anyone given thought to user-defined storage managers? Apart
from allowing backward compatable table access, you could implement a simple
version of partitioning that doesn't take advantage of planner tricks.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington
Wed, 21 May 2003 01:23:44 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: Feature suggestions (long)

This seems related to table spaces --- once we have them, having some
automatic way to spread the data across tablespaces would make sense, so
I have added this discussion to that TODO.detail.

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Tue, May 20, 2003 at 10:02:24AM -0500, Jim C. Nasby wrote:
> > On Tue, May 20, 2003 at 12:40:00AM +1000, Martijn van Oosterhout wrote:
> > > Anyway, the general trend seems to be against the idea so I may as well go
> > > think of something else :)
> > 
> > I'm disappointed to hear that. Having no way to effectively partition
> > data is a real pain in pgsql, and your proposal would adress that. Yes,
> > you can build it yourself by creating the view and all the rules by
> > hand, but that has a lot of drawbacks:
> 
> I agree, there is a lot of potential here. And I don't beleive it would be
> too much work as most of the infrastructure is already there. At this stage
> I'm just wondering if it will go on the TODO list. I propose that the
> following items be added:
> 
>    * Improve the planner to take CHECK constraints into account to prune the plan.
>    * Allow a single index to index multiple tables (also for inherited PRIMARY KEYS)
>    * Allow partitioning of table into multiple subtables
> 
> The first two items would be useful in their own right. With them the final
> one would be straight forward. I'd be prepared to put some effort into this
> if there is some indication it would be accepted.
> 
> > I don't know what the policies for patches are, but I'd hope that the
> > core team would consider adding this functionality, especially since a
> > first-round implimentation can be done entirely with rules (or so it
> > seems).
> 
> Well, I think the policy is 'if you write the code you have a better chance
> to have it accepted' :) So, if it's likely to be accepted then we only need
> to find someone to code it. Given the other priorities currently I think
> waiting for the core team to write it would be futile (unless you can
> convince someone like IBM to give the core team money to write it).
> 
> Right now I'd be happy if the anonymous CVS server would talk to me :)
> 
> By the way, has anyone given thought to user-defined storage managers? Apart
> from allowing backward compatable table access, you could implement a simple
> version of partitioning that doesn't take advantage of planner tricks.
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > "the West won the world not by the superiority of its ideas or values or
> > religion but rather by its superiority in applying organized violence.
> > Westerners often forget this fact, non-Westerners never do."
> >   - Samuel P. Huntington
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 2 Jun 2003 14:36:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Feature suggestions (long)

I was able to merge your ideas into the TODO because they are also items
that relate to other optimizations.  Look for 'subtable' on the web TODO
to see the changes:

> * Allow a single index to index multiple tables (for inheritance and
subtables)
> * Improve the planner to use CHECK constraints to prune the plan (for
subtables)
> * Allow partitioning of table into multiple subtables

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Tue, May 20, 2003 at 10:02:24AM -0500, Jim C. Nasby wrote:
> > On Tue, May 20, 2003 at 12:40:00AM +1000, Martijn van Oosterhout wrote:
> > > Anyway, the general trend seems to be against the idea so I may as well go
> > > think of something else :)
> > 
> > I'm disappointed to hear that. Having no way to effectively partition
> > data is a real pain in pgsql, and your proposal would adress that. Yes,
> > you can build it yourself by creating the view and all the rules by
> > hand, but that has a lot of drawbacks:
> 
> I agree, there is a lot of potential here. And I don't beleive it would be
> too much work as most of the infrastructure is already there. At this stage
> I'm just wondering if it will go on the TODO list. I propose that the
> following items be added:
> 
>    * Improve the planner to take CHECK constraints into account to prune the plan.
>    * Allow a single index to index multiple tables (also for inherited PRIMARY KEYS)
>    * Allow partitioning of table into multiple subtables
> 
> The first two items would be useful in their own right. With them the final
> one would be straight forward. I'd be prepared to put some effort into this
> if there is some indication it would be accepted.
> 
> > I don't know what the policies for patches are, but I'd hope that the
> > core team would consider adding this functionality, especially since a
> > first-round implimentation can be done entirely with rules (or so it
> > seems).
> 
> Well, I think the policy is 'if you write the code you have a better chance
> to have it accepted' :) So, if it's likely to be accepted then we only need
> to find someone to code it. Given the other priorities currently I think
> waiting for the core team to write it would be futile (unless you can
> convince someone like IBM to give the core team money to write it).
> 
> Right now I'd be happy if the anonymous CVS server would talk to me :)
> 
> By the way, has anyone given thought to user-defined storage managers? Apart
> from allowing backward compatable table access, you could implement a simple
> version of partitioning that doesn't take advantage of planner tricks.
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > "the West won the world not by the superiority of its ideas or values or
> > religion but rather by its superiority in applying organized violence.
> > Westerners often forget this fact, non-Westerners never do."
> >   - Samuel P. Huntington
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 2 Jun 2003 14:43:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Feature suggestions (long)