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

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.

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

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)

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

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)

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

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)

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

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.

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

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

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

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

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

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