Re: Partitioning such that key field of inherited tables no longer retains any selectivity

On Sun, May 11, 2014 at 2:08 PM, Tim Kane [via PostgreSQL] <
ml-node+s1045698n5803574h18(at)n5(dot)nabble(dot)com> wrote:

>
>
> From: Tom Lane <[hidden email]<http://user/SendEmail.jtp?type=node&node=5803574&i=0>
> >
>
> David G Johnston <[hidden email]<http://user/SendEmail.jtp?type=node&node=5803574&i=1>>
> writes:
>
> Two approaches:
> 1. Standard virtual column name that, when used, gets rewritten into a
> constant that is stored at the table level.
> 2. A way for a column's value to be defined as a function call.
>
>
> Recent versions of the SQL spec have a notion of "generated columns"
> that I think subsumes both of these concepts. We had a draft patch
> awhile back that attempted to implement that feature. It crashed
> and burned for reasons I don't recall ... but certainly implementing
> an already-standardized feature is more attractive than just inventing
> behavior on our own.
>
>
> That sounds interesting.
> Is this what you are referring to? Actually, it looks like it would fit
> the bill and then some.
>
>
> —————————————————
>
> 4.14.8 Base columns and generated columns
>
> A column of a base table is either a base column or a generated column. A
> base column is one that is not a generated column. A generated column is
> one whose values are determined by evaluation of a generation expression,
> a <value expression> whose declared type is by implication that of the
> column. A generation expression can reference base columns of the base
> table to which it belongs but cannot otherwise access SQL- data. Thus, the
> value of the field corresponding to a generated column in row R is
> determined by the values of zero or more other fields of R.
>
> A generated column GC depends on each column that is referenced by a
> <column reference> in its generation expression, and each such referenced
> column is a parametric column of GC.
>
> —————————————————
>
>
​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).

Given that value of the partition column is constant, and each child table
has a name, is there some way, with the current implementation, to write a
query like:

SELECT *
FROM table_hierarchy
WHERE tableoid = ANY( get_tableoids_as_array('TABLE_NAME1'[,VARARGS]))

and have exclusion constraints work correctly?

Also, toward that end, it would seem that in this particular situation you
could accomplish much the same by using dynamic SQL; though I guess that
would depend on whether any given query needs to be able to return values
from more than one table.

My fluency with respect to inheritance is poor so please forgive if I'm out
in the rough on this one.

David J.

​

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Partitioning-such-that-key-field-of-inherited-tables-no-longer-retains-any-selectivity-tp5803549p5803579.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.