On Fri, 28 Feb 2003, Jean-Christian Imbeault wrote:
> I have written an application that prints out data from a database. One
> problem I am encountering is in the time it takes to generate the print
> file the data may have changed.
>
> I was thinking that a way around this would be to lock the rows that are
> used to generate the print file. However I am not quite sure how to best
> go about this.
>
> Is locking the rows the best (or simplest) solution?
>
> If locking is a good idea how could I lock and then unlock the rows
> returned by this (somewhat ugly) query?
>
> select prod_id, sum(quantity), case when (select stock_qty+cancelled_qty
> from stock where pid=prod_id) is null then 0 else (select
> stock_qty+cancelled_qty from stock where pid=prod_id) end as stock_qty
> from invoice_li, invoices where invoices.id=invoice_id AND dist_id=1 AND
> NOT invoices.cancelled AND NOT invoice_li.cancelled group by prod_id;
>
> I will also be doing some other select queries between this one and the
> printing (I need to get more information on each prod_id returned).
>
> I've read the iDocs and Bruce's online manual but can seem to find any
> info on the syntax for locking other that for SELECT FOR UPDATE. And
> even then the docs don't state how to remove the lock created by a
> SELECT FOR UPDATE.
No need. Take a look here:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html
And read up on serializable, which should do exactly what you want. I.e.
Even if people are changing the database underneath you, you'll see the
database wholly as if it never changed since your begin; was issued.