Re: Is there a way to create a functional index that tables tableoid column as an arg?

On 11 March 2018 at 07:53, Ryan Murphy <ryanfmurphy(at)gmail(dot)com> wrote:
> I am using table inheritance and have e.g. the following tables:
>
> create table animal ( ... );
> create table dog ( ... ) inherits (animal);
> create table cat ( ... ) inherits (animal);
> create table person ( ... ) inherits (animal);
> create table musician ( ... ) inherits (person);
> create table politician ( ... ) inherits (person);
>
> Now I have a query that gets all the "animal"'s except for those that are
> "person"'s.
>
> select * from only animal
>
> won't cut it, because it leaves out the dogs and cats.
>
> select *, tableoid::regclass relname from animal
> where relname != 'person'::regclass
>
> also won't cut it because it leaves out the musicians and politicians.
>
> So I have created an immutable function is_a_kind_of(tbl regclass,
> parent_tbl regclass) that returns true iff tbl is identical with, or
> directly or indirectly inherits from, parent_tbl. For example:
>
> is_a_kind_of('person','person') => true
> is_a_kind_of('person','animal') => true
> is_a_kind_of('musician','person') => true
> is_a_kind_of('animal','person') => false
> is_a_kind_of('dog','person') => false
>
> No problems so far. Now my query works:
>
> select *,tableoid from "animal"
> where not is_a_kind_of(tableoid::regclass::text, 'person')
>
> This query is somewhat slow though - I'd like to index the is_a_kind_of()
> call. And Postgres supports functional indexes! So I try:
> create index animal_is_person on animal (
> is_a_kind_of(tableoid::regclass, 'person') );
>
> ERROR: index creation on system columns is not supported
>
> I see that this is because "tableoid" is a system column. Does anyone know
> any workaround for this? So close yet so far away!

I don't think having an index on the tableoid would help you here
anyway. Previous versions did allow indexes on system columns, but
that was disallowed recently due to the fact that the value of most
system columns are changed internally by postgres and the indexes
wouldn't correctly be updated. tableoid might be an exception to
this, but it does not really seem like a useful column to index,
giving it would be indexing the same value for each record in the
table.

The reason it's slow is that PostgreSQL is executing the function once
per input row. In this case, that must be done because you're passing
in tableoid as a function parameter, and that could change from one
row to the next (once the Append moves to the next subplan).

What you'd want to do instead is create a set-returning function that
finds all the inherited children and returns them all. This will allow
PostgreSQL to execute the function just once at the start of the
query, then join the results to this function.

Something like:

create or replace function get_inherited_tables (prelid oid) returns
setof oid as $$
with recursive c as (
select inhrelid,inhparent from pg_inherits where inhparent = prelid
union all
select i.inhrelid,i.inhparent from pg_inherits i inner join c
on i.inhparent = c.inhrelid
)
select prelid union all select inhrelid from c;
$$ language sql stable;

then have your queries do:

-- get all animals that are persons
select ... from animal where tableoid in (select
get_inherited_tables('person'::regclass);

-- get all animals that are not persons
select ... from animal where tableoid not in (select
get_inherited_tables('person'::regclass);

Just be careful around search_paths and your use of regclass. In this
case, if "animal" was not in the first schema in search_path, but
someone created another table called "person" that was in the first
schema listed in search_path, then the query would not do what you
want. You might want to consider prefixing the input parameter into
get_inherited_tables with the schema name too.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services