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

[David G Johnston <david.g.johnston@xxxxxxxxx>]

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

From: Tom Lane <[hidden email]>

David G Johnston <[hidden email]> 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: Re: Partitioning such that key field of inherited tables no longer retains any selectivity
Sent from the PostgreSQL - general mailing list archive at Nabble.com.