pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
possible row locking bug in 7.0.3 & 7.1
21+ messages / 5 participants
[nested] [flat]

* possible row locking bug in 7.0.3 & 7.1
@ 2001-03-27 21:10  Forest Wilkinson <fspam@home.com>
  0 siblings, 2 replies; 21+ messages in thread

From: Forest Wilkinson @ 2001-03-27 21:10 UTC (permalink / raw)
  To: pgsql-sql

I'm having a problem with functions written in SQL.  Specifically, they
don't seem to be adhering to Postgres locking rules.  For the record, I'm 
using postgres 7.0.3, installed from RPMs, on Red Hat 6.2.  I got the same
results with postgres 7.1 beta 6, installed from sources.

Here's what I'm seeing:
(psql input represented by '<<'; output represented by '>>'.)


session1<< create table idseq
session1<<   (
session1<<   name varchar(32) not null,
session1<<   id int8 not null default 0
session1<<   );
session1>> CREATE

session1<< insert into idseq values ('myid');
session1>> INSERT 18734 1


Each row in the table is supposed to represent a named numeric sequence,
much like the sequences built into postgres.  (Mine use an int8 though,
so their values can be much higher.)


session1<< create function nextid( varchar(32)) returns int8 as '
session1<<   select * from idseq where name = $1::text for update;
session1<<   update idseq set id = id + 1 where name = $1::text;
session1<<   select id from idseq where name = $1::text;
session1<<   ' language 'sql';
session1>> CREATE


The idea here is that the select...for update within the nextid() function
will establish a row level lock, preventing two concurrent function calls
from overlapping.

Next, I test with two sessions as follows:


session1<< begin;
session1>> BEGIN

session2<< begin;
session2>> BEGIN

session1<< select nextid('myid');
session1>>  nextid
session1>> --------
session1>>       1
session1>> (1 row)

session2<< select nextid('myid');

(session2 blocks until session1 completes its transaction)

session1<< commit;
session1>> COMMIT

(session2 resumes)

session2>>  nextid
session2>> --------
session2>>       0
session2>> (1 row)

What gives???  I expected the second call to nextid() to return 2!

session2<< commit;
session2>> COMMIT

session2<< select * from idseq;
session2>>  name | id
session2>> ------+----
session2>>  myid |  2
session2>> (1 row)

session1<< select * from idseq;
session1>>  name | id
session1>> ------+----
session1>>  myid |  2
session1>> (1 row)

As you can see, my nextid() function is not synchronized the way I hoped.
I don't know why, though.  Can someone help?  I'm going to try out some of my 
SPI functions with 7.1 beta 6, to see if they exhibit a locking problem as 
well.

Thanks,

Forest Wilkinson




^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-27 23:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Forest Wilkinson <fspam@home.com>
  1 sibling, 1 reply; 21+ messages in thread

From: Tom Lane @ 2001-03-27 23:14 UTC (permalink / raw)
  To: Forest Wilkinson <fspam@home.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

Forest Wilkinson <fspam@home.com> writes:
> session1<< create function nextid( varchar(32)) returns int8 as '
> session1<<   select * from idseq where name = $1::text for update;
> session1<<   update idseq set id = id + 1 where name = $1::text;
> session1<<   select id from idseq where name = $1::text;
> session1<<   ' language 'sql';
> [ doesn't work as expected in parallel transactions ]

This is a fairly interesting example.  What I find is that at the final
SELECT, the function can see both the tuple outdated by the other
transaction AND the new tuple it has inserted.  (You can demonstrate
that by doing select count(id) instead of select id.)  Whichever one
happens to be visited first is the one that gets returned by the
function, and that's generally the older one in this example.

MVCC seems to be operating as designed here, more or less.  The outdated
tuple is inserted by a known-committed transaction, and deleted by a
transaction that's also committed, but one that committed *since the
start of the current transaction*.  So its effects should not be visible
to the SELECT, and therefore the tuple should be visible.  The anomalous
behavior is not really in the final SELECT, but in the earlier commands
that were able to see the effects of a transaction committed later than
the start of the second session's transaction.

The workaround for Forest is to make the final SELECT be a SELECT FOR
UPDATE, so that it's playing by the same rules as the earlier commands.
But I wonder whether we ought to rethink the MVCC rules so that that's
not necessary.  I have no idea how we might change the rules though.
If nothing else, we should document this issue better: SELECT and SELECT
FOR UPDATE have different visibility rules, so you probably don't want
to intermix them.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 00:14  Philip Warner <pjw@rhyme.com.au>
  parent: Forest Wilkinson <fspam@home.com>
  1 sibling, 1 reply; 21+ messages in thread

From: Philip Warner @ 2001-03-28 00:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

At 18:14 27/03/01 -0500, Tom Lane wrote:
>Forest Wilkinson <fspam@home.com> writes:
>> session1<< create function nextid( varchar(32)) returns int8 as '
>> session1<<   select * from idseq where name = $1::text for update;
>> session1<<   update idseq set id = id + 1 where name = $1::text;
>> session1<<   select id from idseq where name = $1::text;
>> session1<<   ' language 'sql';
>> [ doesn't work as expected in parallel transactions ]
>
>What I find is that at the final
>SELECT, the function can see both the tuple outdated by the other
>transaction AND the new tuple it has inserted.

Surely we should distinguish between real new tuples, and new tuple
versions? I don't think it's ever reasonable behaviour to see two versions
of the same row.


>(You can demonstrate
>that by doing select count(id) instead of select id.)  Whichever one
>happens to be visited first is the one that gets returned by the
>function, and that's generally the older one in this example.
>
>MVCC seems to be operating as designed here, more or less.  The outdated
>tuple is inserted by a known-committed transaction, and deleted by a
>transaction that's also committed, but one that committed *since the
>start of the current transaction*.  So its effects should not be visible
>to the SELECT, and therefore the tuple should be visible.  The anomalous
>behavior is not really in the final SELECT, but in the earlier commands
>that were able to see the effects of a transaction committed later than
>the start of the second session's transaction.

Looking at the docs, I see that 'SERIALIZABLE' has the same visibility
rules as 'READ COMMITTED', which is very confusing. I expect that a Read
Committed TX should see committed changes for a TX that commits during the
first TX (although this may need to be limited to TXs started before the
first TX, but I'm not sure). If this is not the case, then we never get
non-repeatable reads, AFAICT:

    P2 (‘‘Non-repeatable read’’): SQL-transaction T1 reads a row. 
    SQL-transaction T2 then modifies or deletes that row and performs 
    a COMMIT. If T1 then attempts to reread the row, it may
    receive the modified value or discover that the row has been deleted.

which is one of the differences between SERIALIZABLE and READ-COMMITTED.


>The workaround for Forest is to make the final SELECT be a SELECT FOR
>UPDATE, so that it's playing by the same rules as the earlier commands.

Eek. Does this seem good to you? I would expect that SELECT and
SELECT...FOR UPDATE should return the same result set.


>But I wonder whether we ought to rethink the MVCC rules so that that's
>not necessary.  I have no idea how we might change the rules though.

Disallowing visibility of two versions of the same row would help.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 00:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Philip Warner <pjw@rhyme.com.au>
  0 siblings, 1 reply; 21+ messages in thread

From: Tom Lane @ 2001-03-28 00:30 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

Philip Warner <pjw@rhyme.com.au> writes:
>> The workaround for Forest is to make the final SELECT be a SELECT FOR
>> UPDATE, so that it's playing by the same rules as the earlier commands.

> Eek. Does this seem good to you?

I did call it a workaround ;-)

I don't think that we dare try to make any basic changes in MVCC for 7.1
at this late hour, so Forest is going to have to live with that answer
for awhile.  But I would like to see a cleaner answer in future
releases.  As I've opined before, the whole EvalPlanQual mechanism
strikes me as essentially bogus in any case...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 06:08  Forest Wilkinson <fspam@home.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 21+ messages in thread

From: Forest Wilkinson @ 2001-03-28 06:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

On Tuesday 27 March 2001 15:14, Tom Lane wrote:
> Forest Wilkinson <fspam@home.com> writes:
> > session1<< create function nextid( varchar(32)) returns int8 as '
> > session1<<   select * from idseq where name = $1::text for update;
> > session1<<   update idseq set id = id + 1 where name = $1::text;
> > session1<<   select id from idseq where name = $1::text;
> > session1<<   ' language 'sql';
> > [ doesn't work as expected in parallel transactions ]
[snip]
> The workaround for Forest is to make the final SELECT be a SELECT FOR
> UPDATE, so that it's playing by the same rules as the earlier commands.
> But I wonder whether we ought to rethink the MVCC rules so that that's
> not necessary.  I have no idea how we might change the rules though.
> If nothing else, we should document this issue better: SELECT and SELECT
> FOR UPDATE have different visibility rules, so you probably don't want
> to intermix them.

My, that's ugly.  (But thanks for the workaround.)

If I remember correctly, UPDATE establishes a lock on the affected rows, 
which will block another UPDATE on the same rows for the duration of the 
transaction.  If that's true, shouldn't I be able to achieve my desired 
behavior by removing the initial as follows:

create function nextid( varchar(32)) returns int8 as '
  update idseq set id = id + 1 where name = $1::text;
  select id from idseq where name = $1::text;
  ' language 'sql';

Or, would I still have to add FOR UPDATE to that final SELECT?


Forest



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 06:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Forest Wilkinson <fspam@home.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Tom Lane @ 2001-03-28 06:11 UTC (permalink / raw)
  To: Forest Wilkinson <fspam@home.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

Forest Wilkinson <fspam@home.com> writes:
> If I remember correctly, UPDATE establishes a lock on the affected rows, 
> which will block another UPDATE on the same rows for the duration of the 
> transaction.  If that's true, shouldn't I be able to achieve my desired 
> behavior by removing the initial as follows:

> create function nextid( varchar(32)) returns int8 as '
>   update idseq set id = id + 1 where name = $1::text;
>   select id from idseq where name = $1::text;
>   ' language 'sql';

> Or, would I still have to add FOR UPDATE to that final SELECT?

You're right, the initial SELECT FOR UPDATE is a waste of cycles
considering that you're not using the value it returns.  But you'll
still need the last select to be FOR UPDATE so that it plays by the
same rules as the UPDATE does.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 06:19  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 21+ messages in thread

From: Hiroshi Inoue @ 2001-03-28 06:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Philip Warner <pjw@rhyme.com.au>; pgsql-sql; pgsql-hackers@postgresql.org

Tom Lane wrote:
> 
> Philip Warner <pjw@rhyme.com.au> writes:
> >> The workaround for Forest is to make the final SELECT be a SELECT FOR
> >> UPDATE, so that it's playing by the same rules as the earlier commands.
> 
> > Eek. Does this seem good to you?
> 
> I did call it a workaround ;-)
> 
> I don't think that we dare try to make any basic changes in MVCC for 7.1
> at this late hour, so Forest is going to have to live with that answer
> for awhile.  But I would like to see a cleaner answer in future
> releases.

Is it the MVCC's restriction that each query inside a function
must use the same snapshot ?

> As I've opined before, the whole EvalPlanQual mechanism
> strikes me as essentially bogus in any case...
> 

How would you change it ? UPDATE/SELECT FOR UPDATE have to
SELECT/UPDATE the latest tuples. I don't think of any simple
way for 'SELECT FOR UPDATE' to have the same visibility as
simple SELECT.

regards,
Hiroshi Inoue



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-28 17:58  Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 0 replies; 21+ messages in thread

From: Mikheev, Vadim @ 2001-03-28 17:58 UTC (permalink / raw)
  To: 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Philip Warner <pjw@rhyme.com.au>; pgsql-sql; pgsql-hackers@postgresql.org

> > I don't think that we dare try to make any basic changes in 
> > MVCC for 7.1 at this late hour, so Forest is going to have
> > to live with that answer for awhile. But I would like to see
> > a cleaner answer in future releases.
> 
> Is it the MVCC's restriction that each query inside a function
> must use the same snapshot ?

No. MVCC restricts what is visible to query itself. Current
function' behaviour is like Oracle' one.
Strictly speaking queries inside function don't use the same
snapshot - they see changes made by other queries of this
function. Should we allow them see changes made by other
transactions? I'm not sure. Maybe by special means like
CREATE SNAPSHOT S;
SELECT FROM foo WITH SNAPSHOT S;
?

For this particular case - concurrent UPDATE then
UPDATE/DELETE + SELECT - there is simple solution: meeting
tuple updated by concurrent *committed* transaction
SELECT (in READ COMMITTED mode) should look in newer tuple
versions. If some of newer tuples is invalidated (updated/deleted)
by *this* transaction and this invalidation is *visible*
to SELECT (older CommandId) then old tuple version must not
be returned (newer tuple version will be returned of course).
Reported problem is caused by bug (only one tuple version must be
returned by SELECT) and this is way to fix it.

But note that for the case of concurrent DELETE then
INSERT + SELECT two tuples will be returned anyway and
I don't think that this is bug.

> > As I've opined before, the whole EvalPlanQual mechanism
> > strikes me as essentially bogus in any case...
> 
> How would you change it ? UPDATE/SELECT FOR UPDATE have to
> SELECT/UPDATE the latest tuples. I don't think of any simple
> way for 'SELECT FOR UPDATE' to have the same visibility as
> simple SELECT.

Yes, I also don't understand what's wrong with EvalPlanQual.

Vadim



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 02:49  Philip Warner <pjw@rhyme.com.au>
  0 siblings, 0 replies; 21+ messages in thread

From: Philip Warner @ 2001-03-30 02:49 UTC (permalink / raw)
  To: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

At 09:58 28/03/01 -0800, Mikheev, Vadim wrote:
>
>Reported problem is caused by bug (only one tuple version must be
>returned by SELECT) and this is way to fix it.
>

I assume this is not possible in 7.1?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 03:14  Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 0 replies; 21+ messages in thread

From: Mikheev, Vadim @ 2001-03-30 03:14 UTC (permalink / raw)
  To: 'Philip Warner' <pjw@rhyme.com.au>; 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

> >Reported problem is caused by bug (only one tuple version must be
> >returned by SELECT) and this is way to fix it.
> >
> 
> I assume this is not possible in 7.1?

Just looked in heapam.c - I can fix it in two hours.
The question is - should we do this now?
Comments?

Vadim



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 04:02  Philip Warner <pjw@rhyme.com.au>
  0 siblings, 3 replies; 21+ messages in thread

From: Philip Warner @ 2001-03-30 04:02 UTC (permalink / raw)
  To: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

At 19:14 29/03/01 -0800, Mikheev, Vadim wrote:
>> >Reported problem is caused by bug (only one tuple version must be
>> >returned by SELECT) and this is way to fix it.
>> >
>> 
>> I assume this is not possible in 7.1?
>
>Just looked in heapam.c - I can fix it in two hours.
>The question is - should we do this now?
>Comments?

It's a bug; how confident are you of the fix?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 04:16  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Philip Warner <pjw@rhyme.com.au>
  2 siblings, 1 reply; 21+ messages in thread

From: Hiroshi Inoue @ 2001-03-30 04:16 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; +Cc: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org

Philip Warner wrote:
> 
> At 19:14 29/03/01 -0800, Mikheev, Vadim wrote:
> >> >Reported problem is caused by bug (only one tuple version must be
> >> >returned by SELECT) and this is way to fix it.
> >> >
> >>
> >> I assume this is not possible in 7.1?
> >
> >Just looked in heapam.c - I can fix it in two hours.
> >The question is - should we do this now?
> >Comments?
> 
> It's a bug; how confident are you of the fix?
> 

I doubt if it's a bug of SELECT. Well what
'concurrent UPDATE then SELECT FOR UPDATE +
SELECT' return ?

regards,
Hiroshi Inoue



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 05:17  Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 1 reply; 21+ messages in thread

From: Mikheev, Vadim @ 2001-03-30 05:17 UTC (permalink / raw)
  To: 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Philip Warner <pjw@rhyme.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org

> > >> I assume this is not possible in 7.1?
> > >
> > >Just looked in heapam.c - I can fix it in two hours.
> > >The question is - should we do this now?
> > >Comments?
> > 
> > It's a bug; how confident are you of the fix?

95% -:)

> I doubt if it's a bug of SELECT. Well what
> 'concurrent UPDATE then SELECT FOR UPDATE +
> SELECT' return ?

I'm going to add additional check to heapgettup and
heap_fetch:

HeapTupleSatisfies(T) is TRUE:

IF XactIsoLevel is READ_COMMITTED
and snapshot != SnapshotDirty
and !(T->t_data->t_infomask & HEAP_XMAX_INVALID)
and T->t_data->t_infomask & HEAP_XMAX_COMMITTED
and T->t_self != T->t_data->t_ctid
{
  FOR ( ; ; )
  {
    fetch tuple->t_data->t_ctid tuple
    IF t_infomask & (HEAP_XMAX_INVALID | HEAP_MARKED_FOR_UPDATE)
      break; -- and return T
    IF t_infomask & HEAP_XMAX_COMMITTED
    {
      IF t_self != ctid	-- updated
        continue;
      break; -- deleted, return T
    }
    -- uncommitted update/delete
    IF t_xmax != CurrentTransactionID
      break; -- and return T
    -- changed by current TX!
    IF changed *BEFORE* this query began
    {
      -- DELETE + SELECT: nothing to return
      -- UPDATE + SELECT: newer tuple version
      -- will be/was returned by query
      return NULL;
    }
    continue;
  }
}

Vadim



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 06:14  Philip Warner <pjw@rhyme.com.au>
  parent: Philip Warner <pjw@rhyme.com.au>
  2 siblings, 0 replies; 21+ messages in thread

From: Philip Warner @ 2001-03-30 06:14 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org

At 13:16 30/03/01 +0900, Hiroshi Inoue wrote:
>Philip Warner wrote:
>> 
>> At 19:14 29/03/01 -0800, Mikheev, Vadim wrote:
>> >> >Reported problem is caused by bug (only one tuple version must be
>> >> >returned by SELECT) and this is way to fix it.
>> >> >
>> >>
>> >> I assume this is not possible in 7.1?
>> >
>> >Just looked in heapam.c - I can fix it in two hours.
>> >The question is - should we do this now?
>> >Comments?
>> 
>> It's a bug; how confident are you of the fix?
>> 
>
>I doubt if it's a bug of SELECT. 

No idea where the bug is, but SELECT should never return two versions of
the *same* row.


>'Well what
>'concurrent UPDATE then SELECT FOR UPDATE +
>SELECT' return ?

No idea, maybe Vadim or Tom can help?



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 06:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  0 siblings, 1 reply; 21+ messages in thread

From: Tom Lane @ 2001-03-30 06:15 UTC (permalink / raw)
  To: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

> Just looked in heapam.c - I can fix it in two hours.
> The question is - should we do this now?

This scares the hell out of me.

I do NOT think we should be making quick-hack changes in fundamental
system semantics at this point of the release cycle.

The problem went unnoticed for two full release cycles --- therefore,
it can wait another cycle for a fix that has been considered, reviewed,
and tested.  Let's not risk making things worse by releasing a new
behavior we might find out is also wrong.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 10:15  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 0 replies; 21+ messages in thread

From: Hiroshi Inoue @ 2001-03-30 10:15 UTC (permalink / raw)
  To: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; +Cc: Philip Warner <pjw@rhyme.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org

"Mikheev, Vadim" wrote:
> 
> > > >> I assume this is not possible in 7.1?
> > > >
> > > >Just looked in heapam.c - I can fix it in two hours.
> > > >The question is - should we do this now?
> > > >Comments?
> > >
> > > It's a bug; how confident are you of the fix?
> 
> 95% -:)
> 
> > I doubt if it's a bug of SELECT. Well what
> > 'concurrent UPDATE then SELECT FOR UPDATE +
> > SELECT' return ?
> 
> I'm going to add additional check to heapgettup and
> heap_fetch:
>

SELECT seems to be able to return a different result
from that of preceding SELECT FOR UPDATE even after
applying your change.
SELECT doesn't seem guilty but the result is far 
from intuitive.
It seems impossoble for all queires inside such
a function to use a common snapshot.

regards,
Hiroshi Inoue



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 18:54  Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 0 replies; 21+ messages in thread

From: Mikheev, Vadim @ 2001-03-30 18:54 UTC (permalink / raw)
  To: 'Hiroshi Inoue' <Inoue@tpf.co.jp>; +Cc: Philip Warner <pjw@rhyme.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-hackers@postgresql.org

> > > I doubt if it's a bug of SELECT. Well what
> > > 'concurrent UPDATE then SELECT FOR UPDATE +
> > > SELECT' return ?
> > 
> > I'm going to add additional check to heapgettup and
> > heap_fetch:
> 
> SELECT seems to be able to return a different result
> from that of preceding SELECT FOR UPDATE even after
> applying your change.

Oh, you're right. Well, if we really want that SELECT
returns the same result as SELECT FOR UPDATE *in functions*
(out of functions results are already same) then we have to
add some modifications to fix proposed:

1. If newer version of visible tuple T is marked for update by
us *before* query began then do not return T.

2. If tuple T1 is *not visible* because of it was inserted by
concurrent committed TX then check if it's marked for update
by current TX *before* query began and return *this* tuple
version if yes.

This will be in accordance with standard which requires
us return committed (whenever) rows in READ COMMITTED mode.
In fact, in this mode our SELECTs provide higher isolation
than required by standard returning rows committed *before*
query began. Why? Because of SELECT doesn't lock rows and
the same row may be visited by SELECT in join queries
many times - so we have to be protected against concurrent
updates. SELECT FOR UPDATE protects us BUT if query itself
calls some functions which updates queried table then currently
we may lose information that tuple was marked for update before
query began - so updating tuple inserted by concurrent committed
TX and marked for update by us we would have to save its t_cmax
in t_cmin (and either add new flag to t_infomask or don't turn
OFF HEAP_MARKED_FOR_UPDATE in this case).

This is not what I would like to do in 7.1

> SELECT doesn't seem guilty but the result is far 
> from intuitive.

I think that SELECT is guilty. At least returning two versions
of the same row! (One that could be fixed easy).

Vadim



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-30 23:47  Forest Wilkinson <fspam@home.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 21+ messages in thread

From: Forest Wilkinson @ 2001-03-30 23:47 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Mikheev, Vadim <vmikheev@SECTORBASE.COM>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

On Thursday 29 March 2001 22:15, Tom Lane wrote:
> > Just looked in heapam.c - I can fix it in two hours.
> > The question is - should we do this now?
>
> This scares the hell out of me.
>
> I do NOT think we should be making quick-hack changes in fundamental
> system semantics at this point of the release cycle.

Although I'm the one who is being bit by this bug, I tend to agree.

> The problem went unnoticed for two full release cycles 

I first reported the problem on 25 September 2000, on the pgsql-sql list, 
message subject "SQL functions not locking properly?"  I was using 7.0.2 at 
the time.  Also, I seem to remember that a problem of this nature bit me in 
6.5.x as well.

> it can wait another cycle for a fix that has been considered, reviewed,
> and tested.  Let's not risk making things worse by releasing a new
> behavior we might find out is also wrong.

Good point.  How long is the next cycle likely to take?

Forest



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-03-31 00:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Forest Wilkinson <fspam@home.com>
  0 siblings, 0 replies; 21+ messages in thread

From: Tom Lane @ 2001-03-31 00:01 UTC (permalink / raw)
  To: Forest Wilkinson <fspam@home.com>; +Cc: Mikheev, Vadim <vmikheev@SECTORBASE.COM>; pgsql-sql; pgsql-hackers@postgresql.org

Forest Wilkinson <fspam@home.com> writes:
> Good point.  How long is the next cycle likely to take?

Good question.  I'd like to say 4 to 6 months, but that was how long 7.1
was supposed to take, and we're pushing a year now.

What might make the most sense is to develop and test a fix in the early
part of the 7.2 development cycle, and then back-patch it into a 7.1.x
release perhaps 2 or 3 months from now.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-05-17 02:18  Forest Wilkinson <fspam@home.com>
  parent: Philip Warner <pjw@rhyme.com.au>
  2 siblings, 0 replies; 21+ messages in thread

From: Forest Wilkinson @ 2001-05-17 02:18 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; Mikheev, Vadim <vmikheev@SECTORBASE.COM>; 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

I see postgres 7.1.1 is out now.  Was the fix for this problem included in 
the new release?


On Thursday 29 March 2001 20:02, Philip Warner wrote:
> At 19:14 29/03/01 -0800, Mikheev, Vadim wrote:
> >> >Reported problem is caused by bug (only one tuple version must be
> >> >returned by SELECT) and this is way to fix it.
> >>
> >> I assume this is not possible in 7.1?
> >
> >Just looked in heapam.c - I can fix it in two hours.
> >The question is - should we do this now?
> >Comments?
>
> It's a bug; how confident are you of the fix?



^ permalink  raw  reply  [nested|flat] 21+ messages in thread

* RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
@ 2001-05-19 00:28  Mikheev, Vadim <vmikheev@SECTORBASE.COM>
  0 siblings, 0 replies; 21+ messages in thread

From: Mikheev, Vadim @ 2001-05-19 00:28 UTC (permalink / raw)
  To: 'Forest Wilkinson' <fspam@home.com>; Philip Warner <pjw@rhyme.com.au>; 'Hiroshi Inoue' <Inoue@tpf.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org

> I see postgres 7.1.1 is out now.  Was the fix for this
> problem included in the new release?

I fear it will be in 7.2 only.

> On Thursday 29 March 2001 20:02, Philip Warner wrote:
> > At 19:14 29/03/01 -0800, Mikheev, Vadim wrote:
> > >> >Reported problem is caused by bug (only one tuple 
> version must be
> > >> >returned by SELECT) and this is way to fix it.
> > >>
> > >> I assume this is not possible in 7.1?
> > >
> > >Just looked in heapam.c - I can fix it in two hours.
> > >The question is - should we do this now?
> > >Comments?
> >
> > It's a bug; how confident are you of the fix?
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 



^ permalink  raw  reply  [nested|flat] 21+ messages in thread


end of thread, other threads:[~2001-05-19 00:28 UTC | newest]

Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-27 21:10 possible row locking bug in 7.0.3 & 7.1 Forest Wilkinson <fspam@home.com>
2001-03-27 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 06:08   ` Forest Wilkinson <fspam@home.com>
2001-03-28 06:11     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 00:14 ` Philip Warner <pjw@rhyme.com.au>
2001-03-28 00:30   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 06:19     ` Hiroshi Inoue <Inoue@tpf.co.jp>
2001-03-28 17:58 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Mikheev, Vadim <vmikheev@SECTORBASE.COM>
2001-03-30 02:49 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Philip Warner <pjw@rhyme.com.au>
2001-03-30 03:14 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Mikheev, Vadim <vmikheev@SECTORBASE.COM>
2001-03-30 04:02 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Philip Warner <pjw@rhyme.com.au>
2001-03-30 04:16 ` Hiroshi Inoue <Inoue@tpf.co.jp>
2001-03-30 06:15   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-30 23:47     ` Forest Wilkinson <fspam@home.com>
2001-03-31 00:01       ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-30 06:14 ` Philip Warner <pjw@rhyme.com.au>
2001-05-17 02:18 ` Forest Wilkinson <fspam@home.com>
2001-03-30 05:17 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Mikheev, Vadim <vmikheev@SECTORBASE.COM>
2001-03-30 10:15 ` Hiroshi Inoue <Inoue@tpf.co.jp>
2001-03-30 18:54 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Mikheev, Vadim <vmikheev@SECTORBASE.COM>
2001-05-19 00:28 RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Mikheev, Vadim <vmikheev@SECTORBASE.COM>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox