Re: Re: Partitioning such that key field of inherited tables no longer retains any selectivity - Mailing list pgsql-general

On Sun, May 11, 2014 at 2:08 PM, Tim Kane [via PostgreSQL] <[hidden email]> wrote:

[------------------]

​This is basically what I intended to describe in "option 2"...without the benefit of ever having really read the SQL standard.

So the planner would have to know that, for a given table, the generation expression results in a constant - would likely in fact have to be a constant expression like, assuming a non-number value, ='column_value', where the "=" sign indicates that this is a generation expression and not a stored value (like default behaves currently).

wouldn't it be ways better, if the constraints for partitioning by inharitance were set at the "master" table, instead of the way it's currently done at the inharited tables (as exclusive CHECK-s there)?

I mean a constraint like a "function(table columns) reutrning table_name or tablespace_name of the actual target table"?

<start preudocode>
create table master (a int, b int, c int);
create table table_a (inharits master);
create table table_b (inharits master);

create function(a,b) returns text as $$ if a > b then return "table_a" else return "table_b"; end if; end $$
... or:
create function(a,b) returns tablespace as $$ if a > b then return tablespace("table_a") else return tablespace("table_b"); end if; end $$

alter table master add constraint "partitioning" check/select/route function(a,b);
<end pseudocode>


-R