
Can we add the MERGE command to the TODO list?
Is anyone actively examining this issue?

And yes, I realize it is not for 7.5.
It would be good to be able to say it is on
the list for some future release, however.

Thanks,

elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.


Sat, 8 May 2004 14:37:08 -0700
elein <elein@varlena.com>
Adding MERGE to the TODO list (resend with subject)
elein wrote:
> 
> Can we add the MERGE command to the TODO list?
> Is anyone actively examining this issue?
> 
> And yes, I realize it is not for 7.5.
> It would be good to be able to say it is on
> the list for some future release, however.

What does the MERGE command do?  I have never heard of it, so I doubt
someone is working on it.

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

Sat, 8 May 2004 18:46:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding MERGE to the TODO list (resend with subject)
It is the SQL2003 standard for (update else insert).
Check out General Bits monday... :-)

--elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.


On Sat, May 08, 2004 at 06:46:29PM -0400, Bruce Momjian wrote:
> elein wrote:
> > 
> > Can we add the MERGE command to the TODO list?
> > Is anyone actively examining this issue?
> > 
> > And yes, I realize it is not for 7.5.
> > It would be good to be able to say it is on
> > the list for some future release, however.
> 
> What does the MERGE command do?  I have never heard of it, so I doubt
> someone is working on it.
> 
> -- 
>   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?
> 
>                http://archives.postgresql.org

Sat, 8 May 2004 16:19:05 -0700
elein <elein@varlena.com>
Re: Adding MERGE to the TODO list (resend with subject)
> What does the MERGE command do?  I have never heard of it, so I doubt
> someone is working on it.

It is basically the SQL standard version of MySQL's REPLACE syntax.  It 
does an update-else-insert set.  However, the trick is that it uses some 
sort of next key locking to ensure that it cannot fail.  Something that 
is impossible to do in PostgreSQL at the moment. Nested transactions 
will help, however.

Chris


Sun, 09 May 2004 09:50:00 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Adding MERGE to the TODO list (resend with subject)
On Sun, May 09, 2004 at 09:50:00AM +0800, Christopher Kings-Lynne wrote:
> >What does the MERGE command do?  I have never heard of it, so I doubt
> >someone is working on it.
> 
> It is basically the SQL standard version of MySQL's REPLACE syntax.  It 
> does an update-else-insert set.  However, the trick is that it uses some 
> sort of next key locking to ensure that it cannot fail.  Something that 
> is impossible to do in PostgreSQL at the moment. Nested transactions 
> will help, however.

I intend to release locks on subtransaction abort, so if the update
fails there's room for another transaction to insert the key (which I
understand should fail?).  I guess there's a different locking mechanism
needed; I believe nested transactions will not be enough.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The first of April is the day we remember what we are
the other 364 days of the year"  (Mark Twain)

Sat, 8 May 2004 23:54:05 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Adding MERGE to the TODO list (resend with subject)
> I intend to release locks on subtransaction abort, so if the update
> fails there's room for another transaction to insert the key (which I
> understand should fail?).  I guess there's a different locking mechanism
> needed; I believe nested transactions will not be enough.

Except you can keep trying and trying without the outermost transaction 
failing.

Chris


Sun, 09 May 2004 12:13:31 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Adding MERGE to the TODO list (resend with subject)
On Sun, May 09, 2004 at 12:13:31PM +0800, Christopher Kings-Lynne wrote:
> >I intend to release locks on subtransaction abort, so if the update
> >fails there's room for another transaction to insert the key (which I
> >understand should fail?).  I guess there's a different locking mechanism
> >needed; I believe nested transactions will not be enough.
> 
> Except you can keep trying and trying without the outermost transaction 
> failing.

But that won't provide the necessary next key locking you mentioned in
your first email, will it?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No necesitamos banderas
No reconocemos fronteras"                  (Jorge Gonzï¿½lez)

Sun, 9 May 2004 01:18:25 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Adding MERGE to the TODO list (resend with subject)
>>Except you can keep trying and trying without the outermost transaction 
>>failing.
> 
> But that won't provide the necessary next key locking you mentioned in
> your first email, will it?

No, but since I can loop an infinite number of times until either the 
update or insert works, I don't need next key locking.

BTW, the reference in MySQL:

http://dev.mysql.com/doc/mysql/en/REPLACE.html

Hmm...no refernce to next key locking. Maybe that's an Innodb thing...

Anyway, you can see how they've implemented their algorithm.

Here is docs on the DB2 merge command from which the standard was derived:

http://databasejournal.com/features/db2/article.php/10896_3322041_2

Chris

Sun, 09 May 2004 13:32:58 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Adding MERGE to the TODO list (resend with subject)
On Sun, May 09, 2004 at 01:32:58PM +0800, Christopher Kings-Lynne wrote:
> >>Except you can keep trying and trying without the outermost transaction 
> >>failing.
> >
> >But that won't provide the necessary next key locking you mentioned in
> >your first email, will it?
> 
> No, but since I can loop an infinite number of times until either the 
> update or insert works, I don't need next key locking.

Oh, I see.  Complex stuff ... I wonder how will it work with sequences
-- if one insertion fails and we have to try again, there's a chance a
sequence could be advanced more than once.  Note the article skips the
"signal-statement" symbol (is it present in SQL99? What does it do?)

I also wonder if there will be a corresponding RULE implementation ...

The full DB2 reference is at
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0010873.htm

(signal-statement is something to raise an exception, apparently)

(I wonder why they don't use BNF syntax anymore ...)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando maï¿½ana llegue pelearemos segun lo que maï¿½ana exija" (Mowgli)

Sun, 9 May 2004 12:09:49 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Adding MERGE to the TODO list (resend with subject)

Added to TODO:

	* Add MERGE command that does UPDATE, or on failure, INSERT


---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> >>Except you can keep trying and trying without the outermost transaction 
> >>failing.
> > 
> > But that won't provide the necessary next key locking you mentioned in
> > your first email, will it?
> 
> No, but since I can loop an infinite number of times until either the 
> update or insert works, I don't need next key locking.
> 
> BTW, the reference in MySQL:
> 
> http://dev.mysql.com/doc/mysql/en/REPLACE.html
> 
> Hmm...no refernce to next key locking. Maybe that's an Innodb thing...
> 
> Anyway, you can see how they've implemented their algorithm.
> 
> Here is docs on the DB2 merge command from which the standard was derived:
> 
> http://databasejournal.com/features/db2/article.php/10896_3322041_2
> 
> Chris
> 
> ---------------------------(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

Sun, 9 May 2004 23:58:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding MERGE to the TODO list (resend with subject)
Alvaro Herrera wrote:

<snip>
> Oh, I see.  Complex stuff ... I wonder how will it work with sequences
> -- if one insertion fails and we have to try again, there's a chance a
> sequence could be advanced more than once.  Note the article skips the
> "signal-statement" symbol (is it present in SQL99? What does it do?)
> 
> I also wonder if there will be a corresponding RULE implementation ...
> 
> The full DB2 reference is at
> http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0010873.htm
> 
> (signal-statement is something to raise an exception, apparently)
> 
> (I wonder why they don't use BNF syntax anymore ...)
> 
Just to add to this information, Oracle 9i and 10g have also implemented 
the MERGE command. 9i offers an update/insert, whereas 10g adds a delete 
option as well, which is rather handy.
'Purpose', quoted from: 
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_9016.htm#sthref7014

(note, to view this link, you will need to sign up for a free OTN acct.)

"Use the MERGE statement to select rows from one or more sources for 
update or insertion into one or more tables. You can specify conditions 
to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It 
lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same 
row of the target table multiple times in the same MERGE statement."

Point being, I've found the delete option very useful too, rather than 
having to do the same procedurally.



Mon, 10 May 2004 14:11:20 GMT
Bricklen <bricklen-rem@yahoo.comz>
Re: Adding MERGE to the TODO list (resend with subject)
Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> Added to TODO:
> 
> 	* Add MERGE command that does UPDATE, or on failure, INSERT

perhaps the issue raised about BEFOR/AFTER INSERT/UPDATE TRIGGERS and
RULES should get a mention in the TODO (... needs to be discussed, or
somesuch ...)

-------------
Hannu

Tue, 11 May 2004 00:23:43 +0300
Hannu Krosing <hannu@tm.ee>
Re: Adding MERGE to the TODO list (resend with subject)
Hannu Krosing wrote:
> Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > Added to TODO:
> > 
> > 	* Add MERGE command that does UPDATE, or on failure, INSERT
> 
> perhaps the issue raised about BEFOR/AFTER INSERT/UPDATE TRIGGERS and
> RULES should get a mention in the TODO (... needs to be discussed, or
> somesuch ...)

Uh, what was the issue there?

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

Mon, 10 May 2004 17:26:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding MERGE to the TODO list (resend with subject)
Bruce Momjian kirjutas T, 11.05.2004 kell 00:26:
> Hannu Krosing wrote:
> > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > Added to TODO:
> > > 
> > > 	* Add MERGE command that does UPDATE, or on failure, INSERT
> > 
> > perhaps the issue raised about BEFOR/AFTER INSERT/UPDATE TRIGGERS and
> > RULES should get a mention in the TODO (... needs to be discussed, or
> > somesuch ...)
> 
> Uh, what was the issue there?

I think it is not well defined, which triggers should be run. For
example, should BEFORE UPDATE trigger be run and should INSERT be done
if BEFORE UPDATE forbids UPDATE. 

Also how should the rule system act - should there be separate rules for
MERGE, or/and should MERGE be expanded by both UPDATE and INSERT rules.

---------------
Hannu


Tue, 11 May 2004 09:59:42 +0300
Hannu Krosing <hannu@tm.ee>
Re: Adding MERGE to the TODO list (resend with subject)
Hannu Krosing wrote:
> Bruce Momjian kirjutas T, 11.05.2004 kell 00:26:
> > Hannu Krosing wrote:
> > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > > Added to TODO:
> > > > 
> > > > 	* Add MERGE command that does UPDATE, or on failure, INSERT
> > > 
> > > perhaps the issue raised about BEFOR/AFTER INSERT/UPDATE TRIGGERS and
> > > RULES should get a mention in the TODO (... needs to be discussed, or
> > > somesuch ...)
> > 
> > Uh, what was the issue there?
> 
> I think it is not well defined, which triggers should be run. For
> example, should BEFORE UPDATE trigger be run and should INSERT be done
> if BEFORE UPDATE forbids UPDATE. 
> 
> Also how should the rule system act - should there be separate rules for
> MERGE, or/and should MERGE be expanded by both UPDATE and INSERT rules.

OK, I added information to TODO questioning how to handle rules and
triggers.

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

Tue, 11 May 2004 09:44:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding MERGE to the TODO list (resend with subject)

OK, DELETE added:

	* Add MERGE command that does UPDATE/DELETE, or on failure,
	  INSERT (rules, triggers?)


---------------------------------------------------------------------------

Bricklen wrote:
> Alvaro Herrera wrote:
> 
> <snip>
> > Oh, I see.  Complex stuff ... I wonder how will it work with sequences
> > -- if one insertion fails and we have to try again, there's a chance a
> > sequence could be advanced more than once.  Note the article skips the
> > "signal-statement" symbol (is it present in SQL99? What does it do?)
> > 
> > I also wonder if there will be a corresponding RULE implementation ...
> > 
> > The full DB2 reference is at
> > http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0010873.htm
> > 
> > (signal-statement is something to raise an exception, apparently)
> > 
> > (I wonder why they don't use BNF syntax anymore ...)
> > 
> Just to add to this information, Oracle 9i and 10g have also implemented 
> the MERGE command. 9i offers an update/insert, whereas 10g adds a delete 
> option as well, which is rather handy.
> 'Purpose', quoted from: 
> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_9016.htm#sthref7014
> 
> (note, to view this link, you will need to sign up for a free OTN acct.)
> 
> "Use the MERGE statement to select rows from one or more sources for 
> update or insertion into one or more tables. You can specify conditions 
> to determine whether to update or insert into the target tables.
> 
> This statement is a convenient way to combine multiple operations. It 
> lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
> 
> MERGE is a deterministic statement. That is, you cannot update the same 
> row of the target table multiple times in the same MERGE statement."
> 
> Point being, I've found the delete option very useful too, rather than 
> having to do the same procedurally.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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

Tue, 11 May 2004 16:23:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Adding MERGE to the TODO list (resend with subject)
On Tuesday 11 May 2004 09:44 am, Bruce Momjian wrote:
[snip]
> > > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > > > Added to TODO:
> > > > >
> > > > > 	* Add MERGE command that does UPDATE, or on failure, INSERT
> > > >
[snip]

Hello all.

I have been lurking here for a bit and the MERGE topic (on [HACKERS]) caught 
my eye, so I had a go at implementing a basic version of MERGE-on-INSERT in 
pl/pgsql.  It is attached below, and any comments are welcome.  I find it 
useful on "status" type tables, though it is not very nice when there are 
many clients (table locking to avoid race conditions).

Hope someone will find it useful!

-miker
Fri, 14 May 2004 14:04:20 -0400
Mike Rylander <miker@purplefrog.com>
Re: [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the
	TODO list (resend with subject))