Re: view/pgpgsql functions bug

Tom Lane wrote:

>Joe Maldonado <jmaldonado(at)webehosting(dot)biz> writes:
>
>
>>After a create or replace view, the new view definition is not being
>>used by plpgsql functions that use the view. Is this a known bug ? Is
>>there a workaround it ?
>>
>>
>
>Start a fresh backend session. The old query plan is presumably being
>cached by plpgsql. (Yes, this is something we'd like to fix.)
>
> regards, tom lane
>
Since we need processes that hold persistent DB connections to be able
to get data from the changed view, does it make sense to use plpgsql
functions instead of views ?

I'll try to be precise about what I'm solving. If I have tables
A1..An, all of which I would like to truncate periodically and insert
with new data, but still block any kind of querying (queries are
selects with some kind of join on a subset of these tables, and
perhaps with other tables) until such truncates & inserts across all
tables are completed, then the thing that comes to mind is locking
each of these inorder A1..An and then doing truncates, inserts within
a transaction.

However this can lead to deadlocks or failed select queries (if
postgres detects the deadlock), since a select may have obtained data
from Aj and waiting on Ak, where k < j when the above transaction is
ongoing.

So my current solution is as follows. Maintain two sets of tables
A1..An, B1..Bn. Maintain functions A1func() etc for each of those
tables A1..An. All select queries use the functions instead of the
actual tables. Within a single transaction, insert new data into
B1..Bn, using create or replace, change these functions to use Bi
instead of Ai, truncate A1..An, and commit.

Could this scheme lead to inconsistencies ? I'm aware of one. A select
query that joins A1func() and A2func() could obtain old data from
A1func() and then obtain freshly imported data from A2func(). Since
the likelihood of this happening is rare (the above transaction
completes in milliseconds) I'm willing to compromise on this to avoid
explicitly locking N tables. Other than this, everything else seems to
work ok.

Since this requires some understanding internals of SELECT, JOIN etc..
I'd like to hear what you guys think about it.

Note that I prefer not to DROP tables since I have several
dependencies on A1..An and do not wish to recreate them periodically.

Joe

>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>