Date: Fri, 21 Jun 2002 19:10:06 +0100
From: Ian Lynagh <igloo@earth.li>
To: pgsql-sql@postgresql.org
Subject: Presenting consistent data
Message-ID: <20020621181006.GA9386@stu163.keble.ox.ac.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.25i


Hi all,

Suppose I have two tables, pluses and minuses, and for any given id I
maintain the invariant
    sum(pluses.plus) - sum(minuses.minus) = 0

At a given point in time the tables may be like this:

  pluses
id    plus  desc
 1     100   'a'
 1     150   'b'
 1      25   'c'
 2      80   'a'
 2      70   'c'
 
  minuses
id   minus  desc
 1     120   'd'
 1      80   'e'
 1      75   'f'
 2      60   'd'
 2      50   'e'
 2      40   'f'
 
I want to ultimately produce HTML output that looks like this:

 1    275    a=100,b=150,c=25    d=120,e=80,f=75
 2    150    a=80,c=70           d=60,e=50,f=40

The problem is that the obvious (to me!) way to do it involves multiple
separate queries, so with the read committed isolation level it is
possible that the data presented (for a given id) will not be consistent.

I believe serializable isolation level would get around this, but at the
expense of me having to retry "manually" any data-altering queries.

Alternatively I could lock the tables with (I think) ShareLock, but this
may not be the best solution from a performance point of view.


Does anyone have any other suggestions?


Thanks
Ian