SEO

Hi developers,

Concurrently updating an updatable view seems to cause
an unexpected result. Is it a known issue?

=> select version();
version
-------------------------------------------------------------------
PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.4 (mingw special)
(1 row)

=> create table test (id int4 primary key, dt text)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
=> insert into test values (1, 'a');
INSERT 0 1

=> create view test_v as select * from test;
CREATE VIEW
=> create rule test_upd as on update to test_v
do instead update test set dt=NEW.dt where id=OLD.id;
CREATE RULE

The result of concurrently running the same query
update test_v set dt='b' where dt='a'
is as follows.

session-1 => begin;
BEGIN
session-1 => update test_v set dt='b' where dt='a';
UPDATE 1

session-2 => begin;
BEGIN
session-2 => update test_v set dt='b' where dt='a';
(blocked)

session-1 => commit;
COMMIT

(session-2)
UPDATE 1

*Explain* shows the following plan for the query here.
=> explain update test_v set dt='b' where dt='a';
QUERY PLAN
-----------------------------------------------------------------
Hash Join (cost=24.57..50.59 rows=6 width=10)
Hash Cond: (public.test.id = public.test.id)
-> Seq Scan on test (cost=0.00..21.60 rows=1160 width=10)
-> Hash (cost=24.50..24.50 rows=6 width=4)
-> Seq Scan on test (cost=0.00..24.50 rows=6 width=4)
Filter: (dt = 'a'::text)
(6 rows)

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hiroshi Inoue wrote:
> Concurrently updating an updatable view seems to cause
> an unexpected result. Is it a known issue?

Looks right to me. What did you expect?


--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Heikki Linnakangas wrote:
> Hiroshi Inoue wrote:
>> Concurrently updating an updatable view seems to cause
>> an unexpected result. Is it a known issue?
>
> Looks right to me. What did you expect?

Shouldn't the last response
(session-2)
UPDATE 1

be
(seesion-2)
UPDATE 0
?

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hiroshi Inoue wrote:
> Heikki Linnakangas wrote:
>> Hiroshi Inoue wrote:
>>> Concurrently updating an updatable view seems to cause
>>> an unexpected result. Is it a known issue?
>> Looks right to me. What did you expect?
>
> Shouldn't the last response
> (session-2)
> UPDATE 1
>
> be
> (seesion-2)
> UPDATE 0
> ?

Ah, I re-read the example and I see what you mean now.

The problem is that the new tuple version is checked only against the
condition in the update rule, id=OLD.id, but not the condition in the
original update-claus, dt='a'.

Yeah, that's confusing .

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Heikki Linnakangas wrote:
> The problem is that the new tuple version is checked only against the
> condition in the update rule, id=OLD.id, but not the condition in the
> original update-claus, dt='a'.
>
> Yeah, that's confusing .

Bit more than just normal rule confusion I'd say. Try the following two
statements in parallel (assuming you've just run the previous):

UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with
re-checking clauses involving subqueries or joins I'd guess.

I'm trying to decide if it's unexpected or just plain wrong, and I think
I'd have to argue wrong.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Richard Huxton wrote:
> Heikki Linnakangas wrote:
>> The problem is that the new tuple version is checked only against the
>> condition in the update rule, id=OLD.id, but not the condition in the
>> original update-claus, dt='a'.
>>
>> Yeah, that's confusing .
>
> Bit more than just normal rule confusion I'd say. Try the following two
> statements in parallel (assuming you've just run the previous):
>
> UPDATE test SET dt='c';
> UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
>
> This isn't a problem with the view mechanism - it's a problem with
> re-checking clauses involving subqueries or joins I'd guess.
>
> I'm trying to decide if it's unexpected or just plain wrong, and I think
> I'd have to argue wrong.

Or perhaps I'd not argue that :-/

This is really about MVCC in read committed mode, and the "just right
for simpler cases":
http://www.postgresql.org/docs/8.2/i...READ-COMMITTED

Clearly there needs to be a change to the sentence: "Because of the
above rule, it is possible for an updating command to see an
inconsistent snapshot: it can see the effects of concurrent updating
commands that affected the same rows it is trying to update"

Not true if there's a subquery/join involved.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Richard Huxton wrote:
> Richard Huxton wrote:
>> Heikki Linnakangas wrote:
>>> The problem is that the new tuple version is checked only against the
>>> condition in the update rule, id=OLD.id, but not the condition in the
>>> original update-claus, dt='a'.
>>>
>>> Yeah, that's confusing .
>>
>> Bit more than just normal rule confusion I'd say. Try the following
>> two statements in parallel (assuming you've just run the previous):
>>
>> UPDATE test SET dt='c';
>> UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
>>
>> This isn't a problem with the view mechanism - it's a problem with
>> re-checking clauses involving subqueries or joins I'd guess.
>>
>> I'm trying to decide if it's unexpected or just plain wrong, and I
>> think I'd have to argue wrong.
>
> Or perhaps I'd not argue that :-/
Well, src/backend/executor/README agrees with you that it's wrong..

"Note a fundamental bogosity of this approach: if the relation containing
the original tuple is being used in a self-join, the other instance(s) of
the relation will be treated as still containing the original tuple, whereas
logical consistency would demand that the modified tuple appear in them too.
But we'd have to actually substitute the modified tuple for the original,
while still returning all the rest of the relation, to ensure consistent
answers. Implementing this correctly is a task for future work."

> This is really about MVCC in read committed mode, and the "just right
> for simpler cases":
> http://www.postgresql.org/docs/8.2/i...READ-COMMITTED
>
> Clearly there needs to be a change to the sentence: "Because of the
> above rule, it is possible for an updating command to see an
> inconsistent snapshot: it can see the effects of concurrent updating
> commands that affected the same rows it is trying to update"
>
> Not true if there's a subquery/join involved.
If the cited part of the README is correct, then all joins and subqueries
are fine, except if they refer to the table being updated.

I think there should be a big, fat warning that self-referential
updates have highly non-obvious behaviour in read-committed mode,
and should be avoided.

greetings, Florian Pflug



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Richard Huxton wrote:
> Heikki Linnakangas wrote:
>> The problem is that the new tuple version is checked only against the
>> condition in the update rule, id=OLD.id, but not the condition in the
>> original update-claus, dt='a'.
>>
>> Yeah, that's confusing .
>
> Bit more than just normal rule confusion I'd say. Try the following two
> statements in parallel (assuming you've just run the previous):
>
> UPDATE test SET dt='c';
> UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
>
> This isn't a problem with the view mechanism - it's a problem with
> re-checking clauses involving subqueries or joins I'd guess.

I don't understand the PostgreSQL specific *FROM* clause correctly.
Currently the relations in the *FROM* clause seem to be read only
and UPDATE operations seem to acquire no tuple level lock on them.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hiroshi Inoue wrote:
> Richard Huxton wrote:
>> Heikki Linnakangas wrote:
>>> The problem is that the new tuple version is checked only against the
>>> condition in the update rule, id=OLD.id, but not the condition in the
>>> original update-claus, dt='a'.
>>>
>>> Yeah, that's confusing .
>>
>> Bit more than just normal rule confusion I'd say. Try the following
>> two statements in parallel (assuming you've just run the previous):
>>
>> UPDATE test SET dt='c';
>> UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
>>
>> This isn't a problem with the view mechanism - it's a problem with
>> re-checking clauses involving subqueries or joins I'd guess.
>
> I don't understand the PostgreSQL specific *FROM* clause correctly.
> Currently the relations in the *FROM* clause seem to be read only
> and UPDATE operations seem to acquire no tuple level lock on them.

Yes, the above query is equivalent to:
UPDATE test SET dt='x' WHERE id IN (SELECT id FROM test WHERE dt='b');

There are some expressions more naturally expressed as a set of where
conditions though, and I think the "FROM" is just to provide a place to
name them.

The FROM form seemed to be the more natural match to the plan your view
was generating - I'm not sure which the plan transformation process
produces.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Florian G. Pflug wrote:
> Richard Huxton wrote:
>> Richard Huxton wrote:
>>> Heikki Linnakangas wrote:

<snip>

>>> Bit more than just normal rule confusion I'd say. Try the following
>>> two statements in parallel (assuming you've just run the previous):
>>>
>>> UPDATE test SET dt='c';
>>> UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
>>>
>>> This isn't a problem with the view mechanism - it's a problem with
>>> re-checking clauses involving subqueries or joins I'd guess.
>>>
>>> I'm trying to decide if it's unexpected or just plain wrong, and I
>>> think I'd have to argue wrong.
>>
>> Or perhaps I'd not argue that :-/

> Well, src/backend/executor/README agrees with you that it's wrong..

Thanks for the pointer.

> "Note a fundamental bogosity of this approach: if the relation containing
> the original tuple is being used in a self-join, the other instance(s) of
> the relation will be treated as still containing the original tuple,
> whereas
> logical consistency would demand that the modified tuple appear in them
> too.

Is the above description about UPDATE or DELETE operations?
AFAIR SELECT FOR UPDATE operations avoided the incosistency from the
first for joins though I'm not sure about subqueries.
Or I may be misunderstanding something?

> But we'd have to actually substitute the modified tuple for the original,
> while still returning all the rest of the relation, to ensure consistent
> answers. Implementing this correctly is a task for future work."

<snip>

> I think there should be a big, fat warning that self-referential
> updates have highly non-obvious behaviour in read-committed mode,
> and should be avoided.

It seems pretty difficult for PostgreSQL rule system to avoid such
kind of updates. I'm suspicious if UPDATABLE VIEWS can be implemented
using the rule system.

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster