Thread: Adding MERGE to the TODO list (resend with subject)
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.
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, Pennsylvania19073
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
> 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, Pennsylvania19073
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, Pennsylvania19073
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, Pennsylvania19073
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.
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, Pennsylvania19073
Re: MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))
From
Mike Rylander
Date:
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