Re: Postgresql MVCC, Read Committed Isolation Level and taking "snapshot"

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

In response to

Responses

Browse pgsql-general by date

  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"