Best practices: MERGE
Folks,
Although the SQL:2003 command MERGE has not yet been implemented in
PostgreSQL, I'm guessing that there are best practices for how to
implement the MERGE functionality.
To recap, MERGE means (roughly) INSERT the tuple if no tuple matches
certain criteria, otherwise UPDATE using similar criteria.
The "correct" solution, as far as I can tell, is to acquire a LOCK on
the table IN SHARE MODE at the beginning of the transaction, but this
has (at least for many applications) unacceptable performance
characteristics. Accepting that there is a slight risk of a race
condition when *not* locking the table at the beginning of the
transaction, what procedure minimizes this risk and recovers well from
said race condition, should it occur?
TIA for any hints, tips or pointers on this :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
The "correct" solution, as far as I can tell, is to acquire a LOCK on
the table IN SHARE MODE at the beginning of the transaction, but this
has (at least for many applications) unacceptable performance
characteristics. Accepting that there is a slight risk of a race
condition when *not* locking the table at the beginning of the
transaction, what procedure minimizes this risk and recovers well from
said race condition, should it occur?
IN SHARE MODE is not enough, you can get deadlocks. You require IN
SHARE ROW EXCLUSIVE MODE. other than that, it's a sucky solution
because it breaks concurrency. In pgsql 8, you can do it using pl/pgsql
exception handling.
Chris
On Tue, Mar 08, 2005 at 11:45:19AM +0800, Christopher Kings-Lynne wrote:
The "correct" solution, as far as I can tell, is to acquire a LOCK
on the table IN SHARE MODE at the beginning of the transaction, but
this has (at least for many applications) unacceptable performance
characteristics. Accepting that there is a slight risk of a race
condition when *not* locking the table at the beginning of the
transaction, what procedure minimizes this risk and recovers well
from said race condition, should it occur?IN SHARE MODE is not enough, you can get deadlocks. You require IN
SHARE ROW EXCLUSIVE MODE. other than that, it's a sucky solution
because it breaks concurrency. In pgsql 8, you can do it using
pl/pgsql exception handling.
Luckily, PG 8 is available for this. Do you have a short example?
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.
Chris
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.
Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Attachments:
upsert.difftext/plain; charset=us-asciiDownload+34-0
On Mon, 2005-03-07 at 19:34 -0800, David Fetter wrote:
Although the SQL:2003 command MERGE has not yet been implemented in
PostgreSQL, I'm guessing that there are best practices for how to
implement the MERGE functionality.To recap, MERGE means (roughly) INSERT the tuple if no tuple matches
certain criteria, otherwise UPDATE using similar criteria.
Don't understand that way round...
I thought the logic was:
UPDATE WHERE ..... (locate row)
IF NOT FOUND THEN
INSERT (new row)
You can create a procedure to do that, but MERGE would work better.
ISTM that would require writing some new code that was a mix of
heap_update and heap_insert logic for the low level stuff would be
required. The existing heap_update code is most similar, since the logic
is roughly
UPDATE WHERE.... (locate row)
IF FOUND THEN
INSERT (new row version)
though with various changes to row visibility stuff.
One might aim to do this in two stages:
1. initially support a single row upsert such as MySQL's REPLACE command
2. a full implementation of MERGE that used set logic as per the spec
...
Best Regards, Simon Riggs
You can create a procedure to do that, but MERGE would work better.
ISTM that would require writing some new code that was a mix of
heap_update and heap_insert logic for the low level stuff would be
required. The existing heap_update code is most similar, since the logic
is roughlyUPDATE WHERE.... (locate row)
IF FOUND THEN
INSERT (new row version)though with various changes to row visibility stuff.
One might aim to do this in two stages:
1. initially support a single row upsert such as MySQL's REPLACE command
2. a full implementation of MERGE that used set logic as per the spec...
The main issue is dealing with merging into unique index race conditions.
Chris
Patch applied. Thanks. Sorry for the delay in applying.
---------------------------------------------------------------------------
David Fetter wrote:
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Is that broken?
http://momjian.postgresql.org/main/writings/pgsql/sgml/build.html
Chris
Bruce Momjian wrote:
Show quoted text
Patch applied. Thanks. Sorry for the delay in applying.
---------------------------------------------------------------------------
David Fetter wrote:
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Thanks, fixed.
---------------------------------------------------------------------------
Christopher Kings-Lynne wrote:
Is that broken?
http://momjian.postgresql.org/main/writings/pgsql/sgml/build.html
Chris
Bruce Momjian wrote:
Patch applied. Thanks. Sorry for the delay in applying.
---------------------------------------------------------------------------
David Fetter wrote:
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Mon, Apr 18, 2005 at 11:55:44PM -0400, Bruce Momjian wrote:
Thanks, fixed.
Could you apply this to the 8.0 docs, too? The exception handling
works in 8.0, and I know at least two places where it's in production
:)
Cheers,
D
---------------------------------------------------------------------------
Christopher Kings-Lynne wrote:
Is that broken?
http://momjian.postgresql.org/main/writings/pgsql/sgml/build.html
Chris
Bruce Momjian wrote:
Patch applied. Thanks. Sorry for the delay in applying.
---------------------------------------------------------------------------
David Fetter wrote:
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote:
Luckily, PG 8 is available for this. Do you have a short example?
No, and I think it should be in the manual as an example.
You will need to enter a loop that uses exception handling to detect
unique_violation.Pursuant to an IRC discussion to which Dennis Bjorklund and
Christopher Kings-Lynne made most of the contributions, please find
enclosed an example patch demonstrating an UPSERT-like capability.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
