Re: views, queries, and locks

On Tue, Apr 3, 2012 at 7:21 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Tue, Apr 3, 2012 at 2:45 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>> Jon Nelson wrote on 03.04.2012 20:41:
>>
>>> Close, but not quite. It's not rotation but every N minutes a
>>> newly-built table appears. I'd like that table to appear as part of
>>> the view as soon as possible.
>>
>>
>> Can't you use table inheritance for that?
>
>
> Not efficiently. My view looks like this:
>
> select <bunch of stuff from table A>, DATE 'date string here' as
> some_date_column
> UNION ALL
> select <bunch of stuff from table B>, DATE 'date string here' as
> some_date_column
> ....
>
> for lots and lots of tables. Storing that DATE would be cost
> prohibitive and inefficient, since the same value would be used
> throughout each 'sub' table.This let's me do queries that involve
> 'some_date_column' and the query optimizer will remove the tables that
> don't apply, etc.

I was thinking it was something like that. Have you thought of using
a pl/pgsql function with a built up and executed query to accomplish
this? That way you'd get both the efficiency of your current method
without having to rebuild views all the time.