Re: outer joins and for update

On Tue, Nov 15, 2005 at 02:22:15AM +1100, Gavin Sherry wrote:
> On Mon, 14 Nov 2005, Tom Lane wrote:
> > is that you have exclusive hold on the selected rows and they won't
> > change underneath you before the end of your transaction. In the case
> > of an outer join where the left-side row joined to nothing on the
> > right-side, we can't guarantee that: repeating the SELECT might find a
> > matching right-side row, thereby changing the allegedly-locked join row.
> > To guarantee a stable view of the data, we'd need a predicate lock that
> > prevents a matching right-side row from being inserted.
>
> Well.... we can guarantee that we wont see rows added by concurrent
> transactions if we're in serializable isolation level :-).

Do we really need to prevent inserts from happening under a SELECT FOR
UPDATE? ISTM that's trying to apply serializable concurrency to SELECT
FOR UPDATE even if it's running in a read committed transaction. In the
single table case we don't prevent someone from inserting a value...

# session 1
decibel=# insert into t values('1');
INSERT 633175 1

# session 2
decibel=# begin;
BEGIN
decibel=# select * from t where t='1' for update;
t
---
1
(1 row)

# session 1
decibel=# insert into t values('1');
INSERT 633176 1
decibel=# select * from t;
t
---
1
1
(2 rows)

decibel=# update t set t='2';
# Blocks on session 2

Am I missing something here?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461