Re: 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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

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 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
>
> ...

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(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 mobile: +1 415 235 3778
>
> Remember 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)fetter(dot)org http://fetter.org/
>>phone: +1 510 893 6100 mobile: +1 415 235 3778
>>
>>Remember 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(at)fetter(dot)org http://fetter.org/
> >>phone: +1 510 893 6100 mobile: +1 415 235 3778
> >>
> >>Remember 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 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(at)fetter(dot)org http://fetter.org/
> > >>phone: +1 510 893 6100 mobile: +1 415 235 3778
> > >>
> > >>Remember 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(at)candle(dot)pha(dot)pa(dot)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?
>
> http://archives.postgresql.org

--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On Tue, 2005-04-10 at 09:41 -0700, David Fetter wrote:
> Could you apply this to the 8.0 docs, too?

Applied, with fixes.

-Neil