Re: effective SELECT from child tables

On L, 2005-10-01 at 19:59 +0100, Simon Riggs wrote:

> Jim,
>
> Your idea was noted before and actually; I mentioned it to show that I
> listen and take note of ideas from any source.
>
> For everybody, I would note that the current behaviour is exactly the
> way that List Partitioning works on other systems.
>
> The cost of this technique is only paid if you choose to partition on
> something that you would not otherwise have included in your table. In
> many cases, you'll choose a column that would have been in the table if
> you created one big table so the additional cost is zero.

If we had not disabled SELECT rules on ordinary tables some time back
(reserving them exclusively for VIEWs), then most of the benefit of not
storing static tables would have been obtained by storing NULL in the
constant column (via RULE or TRIGGER) and creating an ON SELECT rule on
the subtable that returns the desired constant value.

I also often wish that this would be possible when someone adds a column
with a default value to a multi-million row table on a 24/7 production
system and insists on filling all existing columns with the default.

A rule "ON SELECT FROM table_x WHERE col_x IS NULL return
col_x=default_for_col_x" would solve that nicely.

This would even not require adding null bitmap to existing tuples with
no null values.

> In your example, I would expect to see project_id in a superclass table
> and so there would be no cost.
>
> The idea is neat, but IMHO the potential saving of this idea is not big
> enough for me to prioritise that very highly over other items at this
> time.

I think that bringing the ON SELECT rules of form "ON SELECT ... RETURN
DEFAUL FOR COLUMN x" would be the cleanest and easiest way to do this.

Another use of SELECT rules would be introducing computed columns, which
can also be done by a NULL-filled column and ON SELECT rule using a
function.

We could additionally require the column on which this is defined to
have a "MUST BE NULL" constraint :)

--
Hannu Krosing <hannu(at)skype(dot)net>