From: Ian Lynagh <igloo@earth.li>
To: pgsql-sql@postgresql.org
Subject: Presenting consistent data
Date: Fri, 21 Jun 2002 19:10:06 +0100
Message-ID: <20020621181006.GA9386@stu163.keble.ox.ac.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: igloo@earth.li
Subject: Re: Presenting consistent data
In-Reply-To: <20020621181006.GA9386@stu163.keble.ox.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox