RE: [HACKERS] RE: [BUGS] Update is not atomic

[Mikheev, Vadim]

  Incrementing comand counter is not enough - dirty reads are required
  to handle concurrent PK updates.
 
 What's that with you and dirty reads? Every so often you tell
 me that something would require them -  you  really  like  to
 read dirty things - no? :-)

Dirty things occure - I like to handle them -:)
All MVCC stuff is just ability to handle dirties, unlike old,
locking, behaviour when transaction closed doors to table while
doing its dirty things. Welcome to open world but be ready to
handle dirty things -:)

 So  let  me  get it straight: I execute the entire UPDATE SET
 A=A+1, then increment the command counter and  don't  see  my
 own  results?  So  an  index  scan with heap tuple check will
 return OLD (+NEW?) rows? Last  time  I  fiddled  around  with
 Postgres it didn't, but I could be wrong.

How are you going to see concurrent PK updates without dirty reads?
If two transactions inserted same PK and perform duplicate check at
the same time - how will they see duplicates if no one committed yet?
Look - there is very good example of using dirty reads in current
system: uniq indices, from where we started this thread. So, how uniq
btree handles concurrent (and own!) duplicates? Btree calls heap_fetch
with SnapshotDirty to see valid and *going to be valid* tuples with
duplicate key. If VALID -- ABORT, if UNCOMMITTED (going to be valid)
-- wait for concurrent transaction commit/abort (note that for
obvious reasons heap_fetch(SnapshotDirty) doesn't return OLD rows
modified by current transaction). I had to add all this SnapshotDirty
stuff right to get uniq btree working with MVCC. All what I propose now
is to add ability to perform dirty scans to SPI (and so to PL/*), to be
able make right decisions in SPI functions and triggers, and make those
decisions *at right time*, unlike uniq btree which makes decision
too soon. Is it clear now how to use dirty reads for PK *and* FK?

You proposed using share *row* locks for FK before. I objected then and
object now. It will not work for PK because of PK rows do not exist
for concurrent transactions. What would work here is *key* locks (locks
placed for some key in a table, no matter does row with this key exist
or not). This is what good locking systems, like Informix, use. But
PG is not locking system, no reasons to add key lock overhead, because
of PG internals are able to handle dirties and we need just add same
abilities to externals.

Vadim
 

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

http://www.postgresql.org/search.mpl

RE: [HACKERS] RE: [BUGS] Update is not atomic

[Mikheev, Vadim]

   update a set a=a+1 where a2;
   ERROR:  Cannot insert a duplicate key into unique index a_pkey
 
  We use uniq index for UK/PK but shouldn't. Jan?
 
 What  else  can  you  use  than  an  index? A deferred until
 statement end trigger checking for  duplicates?  Think  it'd
 have a real bad performance impact.

AFAIR, standard requires deffered (until statement/transaction(?)
end) as default behaviour for RI (all?) constraints. But no matter
what is default, deffered *must* be available = uniq indices
must not be used.

 Whatever  the  execution order might be, the update of '3' to
 '4' will see the other '4' as existent WRT the scan commandId
 and  given snapshot - right? If we at the time we now fire up
 the ERROR add the key, the  index  and  heap  to  a  list  of
 possible dupkeys, that we'll check at the end of the actual
 command, the above would work. The  check  at  statement  end
 would have to increment the commandcounter and for each entry
 do an index scan with the key, counting the number of  found,
 valid heap tuples.

Incrementing comand counter is not enough - dirty reads are required
to handle concurrent PK updates.

 Well,  with  some  million rows doing a set a = a + 1 could
 run out of memory. So this would be something that'd work  in
 the  sandbox  and  for non-broken applications (tm). Maybe at

How is this different from (deffered) updates of million FK we allow
right now? Let's user decide what behaviour (deffered/immediate) he
need. The point is that now user has no ability to choose what's
right for him.

 some level (when we escalate the lock to a full table  lock?)
 we  simply  forget  about  single  keys, but have a new index
 access function that checks the entire index for  uniqueness.

I wouldn't bother to implement this. User always has ability to excl.
lock table, drop constraints, update whatever he want and recreate
constraints again.

Vadim

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