Re: Broken RR?

Does Postgres garantee repeatable-read (RR) during transactions? And does it
implement ARIES/KVL?

If so, why is the following possible?

T1: begin;
T1: select * from table;
(notice the row with id = X)
T2: begin;
T2: delete from table where id = X;
T1: select * from table;
(notice the row with id = X suddenly is gone)

_________________________________________________________________
Tag MSN Hotmail med dig p mobilen http://www.msn.dk/mobile

> T1: begin;
> T1: select * from table;
> (notice the row with id = X)
> T2: begin;
> T2: delete from table where id = X;
> T1: select * from table;
> (notice the row with id = X suddenly is gone)

You'll need to SELECT ... FOR UPDATE to lock the row, or use the
SERIALIZABLE transaction more I think...

Chris

On Thu, 5 Jun 2003, Rasmus Resen Amossen wrote:

> Does Postgres garantee repeatable-read (RR) during transactions? And does it
> implement ARIES/KVL?
>
> If so, why is the following possible?
>
> T1: begin;
> T1: select * from table;
> (notice the row with id = X)
> T2: begin;
> T2: delete from table where id = X;
> T1: select * from table;
> (notice the row with id = X suddenly is gone)

I can't reproduce the above. Are you sure T2 isn't committing? If it
were, since the default isolation level is read committed, T1 would be
allowed to see the state after T2 has committed. It isn't allowed to in
serializable isolation (or in repeatable read, but afaik we only support
read committed and serializable currently).

Rasmus Resen Amossen wrote:

> Does Postgres garantee repeatable-read (RR) during transactions? And
> does it implement ARIES/KVL?
>
> If so, why is the following possible?
>
> T1: begin;
> T1: select * from table;
> (notice the row with id = X)
> T2: begin;
> T2: delete from table where id = X;
> T1: select * from table;
> (notice the row with id = X suddenly is gone)

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html#XACT-SERIALIZABLE

This should probably have been posted to the novice, sql, or general
mailing as well, BTW. But that is also mentioned in the on-line pages: ;-)

http://www.postgresql.org/lists.html

"This list is for the discussion of current development issues,
problems and bugs and the discussion of proposed new features.

If people in the other lists don't know the answer to a question and
it is likely that only a developer will know the answer, you may
re-post that question here. You must try elsewhere first!"

HTH,

Mike Mascari
mascarm(at)mascari(dot)com

I wrote:

> Rasmus Resen Amossen wrote:
>
>
>>Does Postgres garantee repeatable-read (RR) during transactions? And
>>does it implement ARIES/KVL?
>>
>>If so, why is the following possible?
>>
>>T1: begin;
>>T1: select * from table;
>> (notice the row with id = X)
>>T2: begin;
>>T2: delete from table where id = X;
>>T1: select * from table;
>> (notice the row with id = X suddenly is gone)
>
>
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=transaction-iso.html#XACT-SERIALIZABLE

Whoops. Sorry. I though this was confusion regarding phantom rowsand
READ COMMITTED vs. SERIALIZABLE. Nevertheless, I cannot repeat the
above...

Mike Mascari
mascarm(at)mascari(dot)com

I'm moving this to GENERAL. Whomsoever replies there please delete the
pgsql-hackers cc entry.

On Thu, 5 Jun 2003, Rasmus Resen Amossen wrote:

> Does Postgres garantee repeatable-read (RR) during transactions? And does it
> implement ARIES/KVL?
>
> If so, why is the following possible?
>
> T1: begin;
> T1: select * from table;
> (notice the row with id = X)
> T2: begin;
> T2: delete from table where id = X;
> T1: select * from table;
> (notice the row with id = X suddenly is gone)

What version of postgresql are you running?

Did you NOT commit the T2 transaction before the last select for T1?

If you commit the deletion, and do NOT have transaction mode set to
serializable, then yes, this is what you'll see.

You can either use select for update or serializable transactions.

Hi All,
I have a problem. I have 3 tables.
1. stock changes
2. product groups
3. a link table between the 2 above

I need the name of the product group the product belongs to, which product
is the subject of the stock change. (I hope it's understandable)

So I tried this query:
----------------------------------------------------------------------------
------------------
DB=# select t_stockchanges.productid, (select name from t_productgroups
where id=(select productgroupid from t_prod_in_pgr where
productid=t_stockchanges.productid)) as pgroup from t_stockchanges;
----------------------------------------------------------------------------
------------------

As I suppose this query should have produced 2 columns, the productid and
the related product group name: pgroup.

But instead it generated an error. It says, that the subquery (I suppose the
most inner) gives back more then 1 tuple. How can I query the only 1 record,
that matches the actual t-stockchanges records productid field. Isn't it
calculated for each stockchanges record?

Thank you in advance.

Best Regards,
-- Csaba

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com)
Version: 6.0.489 / Virus Database: 288 - Release Date: 2003. 06. 10.

On Thursday 19 Jun 2003 5:36 pm, Együd Csaba wrote:
> Hi All,
> I have a problem. I have 3 tables.
> 1. stock changes
> 2. product groups
> 3. a link table between the 2 above
>
> I need the name of the product group the product belongs to, which product
> is the subject of the stock change. (I hope it's understandable)
>
> So I tried this query:
> ---------------------------------------------------------------------------
>- ------------------
> DB=# select t_stockchanges.productid, (select name from t_productgroups
> where id=(select productgroupid from t_prod_in_pgr where
> productid=t_stockchanges.productid)) as pgroup from t_stockchanges;

Try something like:

SELECT chg.productid, grp.name as pgroup
FROM t_stockchanges chg, t_prod_in_pgr pp, t_productgroups grp
WHERE chg.productid=pp.productid AND pp.productgroupid=grp.id;

I might have got some of your fields wrong, but what I'm trying to do is join
across the linked fields.

change.product_id => linktbl.product_id, linktbl.group_id => groups.group_id

No need for a subselect here.
--
Richard Huxton