Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE - Mailing list pgsql-hackers

On Wed, Oct 9, 2013 at 5:37 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> * Reordering of value locks generally. I still need to ensure this
>> will behave reasonably at higher isolation levels (i.e. they'll get a
>> serialization failure). I think that Robert accepts that this isn't
>> inconsistent with read committed's documented behavior, and that it is
>> useful, and maybe even essential.
>
> I think there's a sentence missing here, or something.  Obviously, the
> behavior at higher isolation levels is neither consistent nor
> inconsistent with read committed's documented behavior; it's another
> issue entirely.

Here, "this" referred to the reordering concept generally. So I was
just saying that I'm not actually introducing any anomaly that is
described by the standard at read committed, and that at repeatable
read+, we can have actual serial ordering of value locks without
requiring them to last a long time, because we can throw serialization
failures, and can even do so when not strictly logically necessary.

>> * The basic question of whether or not it's possible to lock values
>> and rows at the same time, and if that matters (because it turns out
>> what looks like that isn't, because deleters will effectively lock
>> values without even touching an index). I think Robert saw the
>> difficulty of doing this, but it would be nice to get a definitive
>> answer. I think that any MERGE implementation worth its salt will not
>> deadlock without the potential for multiple rows to be locked in an
>> inconsistent order, so this shouldn't either, and as I believe I
>> demonstrated, value locks and row locks should not be held at the same
>> time for at least that reason. Right?
>
> Right.

I'm glad we're on the same page with that - it's a very important
consideration to my mind.

>> * The syntax. I like the composability, and the way it's likely to
>> become idiomatic to combine it with wCTEs. Others may not.
>
> I've actually lost track of what syntax you're proposing.

I'm continuing to propose:

INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

with a much less interesting variant that could be jettisoned:

INSERT...ON DUPLICATE KEY IGNORE

I'm also proposing extended RETURNING to make it work with this. So
the basic idea is that within Postgres, the idiomatic way to correctly
do upsert becomes something like:

postgres=# with r as (
insert into foo(a,b)
values (5, '!'), (6, '@')
on duplicate key lock for update
returning rejects *
)
update foo set b = r.b from r where foo.a = r.a;

>> * The visibility hacks that V4 is likely to have. The fact that
>> preserving the composable syntax may imply changes to
>> HeapTupleSatisfiesMVCC() so that rows locked but with no currently
>> visible version (under conventional rules) are visible to our snapshot
>> by virtue of having been locked all the same (this only matters at
>> read committed).
>
> I continue to think this is a bad idea.

Fair enough.

Is it just because of performance concerns? If so, that's probably not
that hard to address. It either has a measurable impact on performance
for a very unsympathetic benchmark or it doesn't. I guess that's the
standard that I'll be held to, which is probably fair.

Do you see the appeal of the composable syntax?

I appreciate that it's odd that serializable transactions now have to
worry about seeing something they shouldn't have seen (when they
conclusively have to go lock a row version not current to their
snapshot). But that's simpler than any of the alternatives that I see.
Does there really need to be a new snapshot type with one tiny
difference that apparently doesn't actually affect conventional
clients of MVCC snapshots?

-- 
Peter Geoghegan