Ah, thanks a lot for the Select .. For Update clue/reminder! The partitions are actually going to arrive from different systems (lab reports coming from lab equipment and nurses entering bedside data at the same time etc). So I'll have to be defensive about concurrency.
Best regards
Seref
On Tue, Aug 28, 2012 at 2:14 PM, Craig Ringer
<ringerc@ringerc.id.au> wrote:
On 08/28/2012 08:56 PM, Seref Arikan wrote:
Can I simply adopt the naive approach of updating an EHR metadata table
within a transaction in every partition addition/deletion operation?
It *is* a trade-off, like any performance choice. Careful work is also required to handle concurrency issues correctly.
I do the same thing in much smaller (tiny, even) databases where I have expensive queries I want to respond before the user noticed they were waiting. For example, in a parent->child relationship I sometimes maintain a summary table with a 1:1 relationship with the parent that summarizes the children.
It's usually a good idea to keep your summary tables clearly separate as trigger-maintained materialized views, rather than updating "real" entities with summary info too. You avoid churn on your "real" tables, avoid some interesting lock ordering issues, etc.
Some explicit locking with `SELECT ... FOR UPDATE` can be important to avoid unexpected concurrency issues.
--
Craig Ringer