Re: views, queries, and locks

On Tue, Apr 3, 2012 at 8:58 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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.

I need to have something table-like from the client's perspective for
a bunch of reasons.
For now, assume that I want to keep using the view and that I'd like
to find better ways to address my concerns.

--
Jon