From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgresql MVCC, Read Committed Isolation Level and taking "snapshot" |
Date: | 2008-05-20 18:56:41 |
Message-ID: | 20080520205641.4eb5fe91@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 16 May 2008 09:55:56 -0400
Andrew Sullivan <ajs(at)commandprompt(dot)com> wrote:
> On Fri, May 16, 2008 at 09:06:11AM +0200, Ivan Sergio Borgonovo
> wrote:
> > Is
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
> > what I'm looking for?
>
> Yes.
OK...
What if I want to avoid the rollback problem and avoid to deal with
the
ERROR: could not serialize access due to concurrent update
I don't have to update/insert rows in the same tables I'm examining.
I just would like to have a coherent snapshot of some tables.
It should be something like:
1) check is some conditions are met with a bunch of selects and
computation on returned rows
2) if everything is OK copy a "slice" of the snapshot in *other*
tables.
It would be something like
select into _a, _b c, d from t1 join t2...
select into _c, _d c, d from t3 join t4...
if(...) ...
if(...) ...
if(everything is fine)
insert into t10 (a,b,h,i,l,m) select _a, _b, h, i, l, m
from t1
join t2...
I'm not interested into the fact that _a, _b, _c, _d, t1 or t2 may be
different when other transaction commit... I'm just interested in
"saving" in t10 a coherent state.
Since I'm going to save all the collected data into another table
other than the one that may be updated by another transaction am I
going to incur in the above ERROR: could not serialize...?
Can I reach my target of getting a coherent snapshot with a set of
"for share"?
Is there a set of examples that could highlight the differences of
"for share/update" and serializable with their pitfalls?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Petrov | 2008-05-20 18:59:43 | Corrupted database's files (linux RAID5 + PostgreSQL 8.3.0) |
Previous Message | Dan Joo | 2008-05-20 18:53:47 | Re: psql: FATAL: Ident authentication failedforuser"postgres" |