Prev: [HACKERS] Concurrent MERGE
Next: ECPG dynamic cursor fix for UPDATE/DELETE... WHERE CURRENT OF :curname
From: Robert Haas on 5 Aug 2010 11:57
On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote:
> Looks like MERGE is progressing well.
>
> At 2010 Dev Mtg, we put me down to work on making merge work
> concurrently. That was garbled slightly and had me down as working on
> predicate locking which is the general solution to the problem.
>
> Do we still need me to work on concurrent MERGE, or is that included in
> the current MERGE patch (can't see it), or is that covered elsewhere
> (for example Kevin Grittner's recent work)?
>
> Still happy to do work as proposed, just checking still required.

I suspect Kevin's patch will solve it if using a sufficiently high
transaction isolation level, but something else might be needed
otherwise. However, I confess to ignorance as to the underlying
issues? Why is MERGE worse in this regard than, say, UPDATE?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 5 Aug 2010 12:39
On 05/08/10 18:43, Simon Riggs wrote:
> Do we still need me to work on concurrent MERGE, or is that included in
> the current MERGE patch (can't see it), or ...

It's not in the current MERGE patch.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 5 Aug 2010 12:43
On 05/08/10 18:57, Robert Haas wrote:
> On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs<simon(a)2ndquadrant.com> wrote:
>> Looks like MERGE is progressing well.
>>
>> At 2010 Dev Mtg, we put me down to work on making merge work
>> concurrently. That was garbled slightly and had me down as working on
>> predicate locking which is the general solution to the problem.
>>
>> Do we still need me to work on concurrent MERGE, or is that included in
>> the current MERGE patch (can't see it), or is that covered elsewhere
>> (for example Kevin Grittner's recent work)?
>>
>> Still happy to do work as proposed, just checking still required.
>
> I suspect Kevin's patch will solve it if using a sufficiently high
> transaction isolation level, but something else might be needed
> otherwise.

With truly serializable isolation I think you'll get a serialization
failure error.

> However, I confess to ignorance as to the underlying
> issues? Why is MERGE worse in this regard than, say, UPDATE?

MERGE can be used to implement "upsert", where a row is updated if it
exists and inserted if it doesn't. I don't think Kevin's patch will
suffice for that. You don't usually want a serialization failure error
when you run two upserts at the same time, you want both of them to
succeed, one doing an insert and the other one doing an update.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 5 Aug 2010 12:57
Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> wrote:

>> However, I confess to ignorance as to the underlying
>> issues? Why is MERGE worse in this regard than, say, UPDATE?
>
> MERGE can be used to implement "upsert", where a row is updated if
> it exists and inserted if it doesn't. I don't think Kevin's patch
> will suffice for that. You don't usually want a serialization
> failure error when you run two upserts at the same time, you want
> both of them to succeed, one doing an insert and the other one
> doing an update.

The patch Dan and I are working on won't block anything that
snapshot isolation doesn't already block, so if the behavior you
want is that one is held up until the other is done with something,
it's not going to help. What it would do is detect whether two
concurrent upserts are behaving in a way that is consistent with
some serial execution of the two upserts; it would do nothing if
there was a consistent interpretation, but roll one back if each
appeared to come before the other in some respect.

All of that, of course, with the usual caveats that it would have
*no* impact unless both were run at the SERIALIZABLE isolation
level, there could be false positives, and the MERGE code might
possibly need to add a few calls to the functions added in the
serializable patch.

I hope that clarified rather than muddied the waters....

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Chris Browne on 5 Aug 2010 13:10
robertmhaas(a)gmail.com (Robert Haas) writes:
> On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote:
>> Looks like MERGE is progressing well.
>>
>> At 2010 Dev Mtg, we put me down to work on making merge work
>> concurrently. That was garbled slightly and had me down as working on
>> predicate locking which is the general solution to the problem.
>>
>> Do we still need me to work on concurrent MERGE, or is that included in
>> the current MERGE patch (can't see it), or is that covered elsewhere
>> (for example Kevin Grittner's recent work)?
>>
>> Still happy to do work as proposed, just checking still required.
>
> I suspect Kevin's patch will solve it if using a sufficiently high
> transaction isolation level, but something else might be needed
> otherwise. However, I confess to ignorance as to the underlying
> issues? Why is MERGE worse in this regard than, say, UPDATE?

It's worse than UPDATE because
- It could be an INSERT, if the data's new, but
- If the data's there, it becomes an UPDATE, but
- If some concurrent update has just DELETEd the data that's there, it
becomes an INSERT again, but
- Oops, that DELETE rolled bac, so it's an UPDATE again...

Recurse as needed to make it more undecidable as to whether it's really
an INSERT or an UPDATE :-).
--
Rules of the Evil Overlord #208. "Members of my Legion of Terror will
attend seminars on Sensitivity Training. It's good public relations
for them to be kind and courteous to the general population when not
actively engaged in sowing chaos and destruction."
 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: [HACKERS] Concurrent MERGE
Next: ECPG dynamic cursor fix for UPDATE/DELETE... WHERE CURRENT OF :curname