Re: views, queries, and locks

On Tue, Apr 3, 2012 at 1:41 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Generally speaking, in SQL, locks are held until the transaction
>> commits; there are tons of reasons why things have to work that way.
>> Anyways, I'm betting your requirement to have to re-CREATE the view
>> can be abstracted out somehow.  I'm guessing you have some type of
>> table rotation going on?
>
> 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.

How sophisticated are the queries that are touching this view? How
much data in the tables? If you don't need to push quals down into
the view, we can do a view wrapping function that can pick up the new
tables.

> Regarding locks: I know that's how locks usually work - but what I'm
> asking is if holding on to the lock for the view, once the view has
> been 'expanded', is necessary at all.

Unfortunately, it is.

merlin