Thread: REPLACE INTO table a la mySQL
I know we're not in the business of copying mySQL, but the REPLACE INTO table (...) values (...) could be a useful semantic. This is a combination INSERT or UPDATE statement. For one thing, it is atomic, and easier to work with at the application level. Also if the application doesn't care about previous values, then execution has fewer locking issues and race conditions. comments? Dale Johnson
Dale Johnson wrote: > > I know we're not in the business of copying mySQL, > but the REPLACE INTO table (...) values (...) could be > a useful semantic. This is a combination INSERT or > UPDATE statement. For one thing, it is atomic, and > easier to work with at the application level. Also > if the application doesn't care about previous values, > then execution has fewer locking issues and race > conditions. > > comments? > > Dale Johnson I don't know if it is standard SQL, but it will save hundreds of lines of code in applications everywhere. I LOVE the idea. I just finished writing a database merge/update program which could have been made much easier to write with this syntax.
> > I know we're not in the business of copying mySQL, > > but the REPLACE INTO table (...) values (...) could be > > a useful semantic. This is a combination INSERT or > > UPDATE statement. For one thing, it is atomic, and > > easier to work with at the application level. Also > > if the application doesn't care about previous values, > > then execution has fewer locking issues and race > > conditions. > > I don't know if it is standard SQL, but it will save hundreds of > lines of code > in applications everywhere. I LOVE the idea. I just finished > writing a database > merge/update program which could have been made much easier to > write with this > syntax. The reason MySQL probably has it though is because it doesn't support proper transactions. While we're at it, why not support the MySQL alternate INSERT syntax (rehetorical): INSERT INTO table SET field1='value1', field2='value2'; ... Chris
Christopher Kings-Lynne wrote:
>
> > > I know we're not in the business of copying mySQL,
> > > but the REPLACE INTO table (...) values (...) could be
> > > a useful semantic. This is a combination INSERT or
> > > UPDATE statement. For one thing, it is atomic, and
> > > easier to work with at the application level. Also
> > > if the application doesn't care about previous values,
> > > then execution has fewer locking issues and race
> > > conditions.
> >
> > I don't know if it is standard SQL, but it will save hundreds of
> > lines of code
> > in applications everywhere. I LOVE the idea. I just finished
> > writing a database
> > merge/update program which could have been made much easier to
> > write with this
> > syntax.
>
> The reason MySQL probably has it though is because it doesn't support proper
> transactions.
>
> While we're at it, why not support the MySQL alternate INSERT syntax
> (rehetorical):
>
> INSERT INTO table SET field1='value1', field2='value2';
That is not an issue, but a "REPLACE" syntax can take the place of this:
SQL("select * from table where ID = fubar");
if(HAS_VALUES(SQL))SQL("update table set xx=yy, www=zz where ID = fubar");
elseSQL("insert into table (...) values (...)");
REPLACE into table set xx=yy, ww = zz where ID = fubar;
A MUCH better solution!
Dale Johnson wrote: > I know we're not in the business of copying mySQL, > but the REPLACE INTO table (...) values (...) could be > a useful semantic. This is a combination INSERT or > UPDATE statement. For one thing, it is atomic, and > easier to work with at the application level. Also > if the application doesn't care about previous values, > then execution has fewer locking issues and race > conditions. > > comments? First it's not standard SQL, so chances aren't that good. Second, how do you think the system should behave in the following case: * Table A has one trigger BEFORE INSERT doing some checks plus inserting a row into table newA and updating arow in table balanceA. It also has triggers BEFORE UPDATE and BEFORE DELETE that update balanceA. * Now we do your REPLACE INTO The problem is that in a concurrent multiuser environment you cannot know if that row exists until you actuallydo the insert (except you lock the entire table and check for). Since there's a BEFORE trigger which potentially could suppress the INSERT, you can't do the insert before fireing it. Now it has been run, did it'sinserts and updates and the statement must be converted into an UPDATE because the row exists - how do you undothe trigger work? I know, mySQL doesn't have triggers, referential integrity and all that damned complicated stuff. That's why it canhave such a powerful non-standard command like REPLACE INTO. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
mlw wrote: > [...] > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > A MUCH better solution! Please solve the trigger problem at least theoretical before claiming that mySQL is that MUCH better. And please don't solve it by ripping out trigger support :-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck wrote: > Dale Johnson wrote: > > I know we're not in the business of copying mySQL, > > but the REPLACE INTO table (...) values (...) could be > > a useful semantic. This is a combination INSERT or > > UPDATE statement. For one thing, it is atomic, and > > easier to work with at the application level. Also > > if the application doesn't care about previous values, > > then execution has fewer locking issues and race > > conditions. > > > > comments? > > First it's not standard SQL, so chances aren't that good. > Second, how do you think the system should behave in the > following case: > > * Table A has one trigger BEFORE INSERT doing some checks > plus inserting a row into table newA and updating a row in > table balanceA. It also has triggers BEFORE UPDATE and > BEFORE DELETE that update balanceA. > > * Now we do your REPLACE INTO > > The problem is that in a concurrent multiuser environment you > cannot know if that row exists until you actually do the > insert (except you lock the entire table and check for). > Since there's a BEFORE trigger which potentially could > suppress the INSERT, you can't do the insert before fireing > it. Now it has been run, did it's inserts and updates and the > statement must be converted into an UPDATE because the row > exists - how do you undo the trigger work? > > I know, mySQL doesn't have triggers, referential integrity > and all that damned complicated stuff. That's why it can have > such a powerful non-standard command like REPLACE INTO. > > Jan Perhaps it is as easy as saying that this feature is a non-standard extension to SQL, thus a non-standard trigger mechanism is used. The trigger will be on the statement replace. The trigger function will carry with it the tuple, and the previous one if one exists. create trigger my_trigger before update or insert or delete or replace
"Jan Wieck" <JanWieck@Yahoo.com> wrote in message news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > mlw wrote: > > [...] > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > A MUCH better solution! > > Please solve the trigger problem at least theoretical before > claiming that mySQL is that MUCH better. And please don't > solve it by ripping out trigger support :-) > for INSERT OR REPLACE into table ... if the record was not there, fire the insert trigger else delete the row (fire delete trigger) insert the new row (fire the insert trigger) fi semantically no other way, I think Dale.
Dale Johnson wrote: > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > > mlw wrote: > > > [...] > > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > > > A MUCH better solution! > > > > Please solve the trigger problem at least theoretical before > > claiming that mySQL is that MUCH better. And please don't > > solve it by ripping out trigger support :-) > > > for INSERT OR REPLACE into table ... > if the record was not there, fire the insert trigger > else > delete the row (fire delete trigger) > insert the new row (fire the insert trigger) > fi > > semantically no other way, I think I'm not sure I agree. There are explicit triggers for update, insert, and delete, therefor why not also have a trigger for replace? It is one more case. Rather than try to figure out how to map replace into two distinct behaviors of insert or update based on some conditional logic, why not just have a replace trigger?
mlw wrote: > Dale Johnson wrote: > > > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message > > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > > > mlw wrote: > > > > [...] > > > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > > > > > A MUCH better solution! > > > > > > Please solve the trigger problem at least theoretical before > > > claiming that mySQL is that MUCH better. And please don't > > > solve it by ripping out trigger support :-) > > > > > for INSERT OR REPLACE into table ... > > if the record was not there, fire the insert trigger > > else > > delete the row (fire delete trigger) > > insert the new row (fire the insert trigger) > > fi > > > > semantically no other way, I think > > I'm not sure I agree. There are explicit triggers for update, insert, and > delete, therefor why not also have a trigger for replace? It is one more > case. Rather than try to figure out how to map replace into two distinct > behaviors of insert or update based on some conditional logic, why not just > have a replace trigger? Adding another trigger event type will break every existing DB schema that relies on custom triggers to ensure logical data integrity. Thus it is unacceptable as solution to support a non-standard feature - period. The question "does this row exist" can only be answered by looking at the primary key. Now BEFORE triggers areallowed to alter the key attributes, so the final primary key isn't known before they are executed. Thus the DELETE then INSERT semantic might be the only way. Pretty havy restriction, making the entire REPLACE INTO somewhat useless IMHO. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck wrote: > > mlw wrote: > > Dale Johnson wrote: > > > > > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message > > > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > > > > mlw wrote: > > > > > [...] > > > > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > > > > > > > A MUCH better solution! > > > > > > > > Please solve the trigger problem at least theoretical before > > > > claiming that mySQL is that MUCH better. And please don't > > > > solve it by ripping out trigger support :-) > > > > > > > for INSERT OR REPLACE into table ... > > > if the record was not there, fire the insert trigger > > > else > > > delete the row (fire delete trigger) > > > insert the new row (fire the insert trigger) > > > fi > > > > > > semantically no other way, I think > > > > I'm not sure I agree. There are explicit triggers for update, insert, and > > delete, therefor why not also have a trigger for replace? It is one more > > case. Rather than try to figure out how to map replace into two distinct > > behaviors of insert or update based on some conditional logic, why not just > > have a replace trigger? > > Adding another trigger event type will break every existing > DB schema that relies on custom triggers to ensure logical > data integrity. Thus it is unacceptable as solution to > support a non-standard feature - period. > > The question "does this row exist" can only be answered by > looking at the primary key. Now BEFORE triggers are allowed > to alter the key attributes, so the final primary key isn't > known before they are executed. > > Thus the DELETE then INSERT semantic might be the only way. > Pretty havy restriction, making the entire REPLACE INTO > somewhat useless IMHO. The only issue I have with your conclusion about DB schema is that REPLACE is not part of standard SQL, so we do not need be too concerned. Just give them a REPLACE trigger and be done with it. If that isn't good enough, in the FAQ, say that the standard way is insert or update.
On Mon, 11 Jun 2001, mlw wrote: > > Adding another trigger event type will break every existing > > DB schema that relies on custom triggers to ensure logical > > data integrity. Thus it is unacceptable as solution to > > support a non-standard feature - period. > > > > The question "does this row exist" can only be answered by > > looking at the primary key. Now BEFORE triggers are allowed > > to alter the key attributes, so the final primary key isn't > > known before they are executed. > > > > Thus the DELETE then INSERT semantic might be the only way. > > Pretty havy restriction, making the entire REPLACE INTO > > somewhat useless IMHO. > > The only issue I have with your conclusion about DB schema is that > REPLACE is not part of standard SQL, so we do not need be too > concerned. Just give them a REPLACE trigger and be done with it. If > that isn't good enough, in the FAQ, say that the standard way is > insert or update. I am not sure I like this: it is possible that someone's security is based on triggers, and adding replace as a trigger will let them get around it...Possibly this could be controlled by serverwide option 'enable_replace_into' or something like that for people with such setup..? -alex
mlw wrote: > > Dale Johnson wrote: > > > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message > > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > > > mlw wrote: > > > > [...] > > > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > > > > > A MUCH better solution! > > > > > > Please solve the trigger problem at least theoretical before > > > claiming that mySQL is that MUCH better. And please don't > > > solve it by ripping out trigger support :-) I was recently told about a similar feature coming to Oracle (or perhaps already in v9.x) Has anyone any knowledge of it ? -------------------- Hannu
Alex Pilosov wrote: > > On Mon, 11 Jun 2001, mlw wrote: > > > > Adding another trigger event type will break every existing > > > DB schema that relies on custom triggers to ensure logical > > > data integrity. Thus it is unacceptable as solution to > > > support a non-standard feature - period. > > > > > > The question "does this row exist" can only be answered by > > > looking at the primary key. Now BEFORE triggers are allowed > > > to alter the key attributes, so the final primary key isn't > > > known before they are executed. > > > > > > Thus the DELETE then INSERT semantic might be the only way. > > > Pretty havy restriction, making the entire REPLACE INTO > > > somewhat useless IMHO. > > > > The only issue I have with your conclusion about DB schema is that > > REPLACE is not part of standard SQL, so we do not need be too > > concerned. Just give them a REPLACE trigger and be done with it. If > > that isn't good enough, in the FAQ, say that the standard way is > > insert or update. > I am not sure I like this: it is possible that someone's security is based > on triggers, and adding replace as a trigger will let them get around > it... BTW, does current LOAD INTO trigger INSERT triggers ? >Possibly this could be controlled by serverwide option > 'enable_replace_into' or something like that for people with such setup..? > > -alex > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Hannu Krosing wrote: > > BTW, does current LOAD INTO trigger INSERT triggers ? If you mean COPY, yes. BTW2, we still allow TRUNCATE on tables that have DELETE triggers. Since it's a way to violate constraints it should IMHO not be allowed, or at least restricted to DBA. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
> Hannu Krosing wrote: > > > > BTW, does current LOAD INTO trigger INSERT triggers ? > > If you mean COPY, yes. > > BTW2, we still allow TRUNCATE on tables that have DELETE > triggers. Since it's a way to violate constraints it should > IMHO not be allowed, or at least restricted to DBA. You want a TODO item added? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"Jan Wieck" <JanWieck@Yahoo.com> wrote in message news:200106112044.f5BKiwa03120@jupiter.us.greatbridge.com... > mlw wrote: > > Dale Johnson wrote: > > > > > "Jan Wieck" <JanWieck@Yahoo.com> wrote in message > > > news:200106061506.f56F6dV01843@jupiter.us.greatbridge.com... > > > > mlw wrote: > > > > > [...] > > > > > REPLACE into table set xx=yy, ww = zz where ID = fubar; > > > > > > > > > > A MUCH better solution! > > > > > > > > Please solve the trigger problem at least theoretical before > > > > claiming that mySQL is that MUCH better. And please don't > > > > solve it by ripping out trigger support :-) > > > > > > > for INSERT OR REPLACE into table ... > > > if the record was not there, fire the insert trigger > > > else > > > delete the row (fire delete trigger) > > > insert the new row (fire the insert trigger) > > > fi > > > > > > semantically no other way, I think > > > > I'm not sure I agree. There are explicit triggers for update, insert, and > > delete, therefor why not also have a trigger for replace? It is one more > > case. Rather than try to figure out how to map replace into two distinct > > behaviors of insert or update based on some conditional logic, why not just > > have a replace trigger? > > Adding another trigger event type will break every existing > DB schema that relies on custom triggers to ensure logical > data integrity. Thus it is unacceptable as solution to > support a non-standard feature - period. > > The question "does this row exist" can only be answered by > looking at the primary key. Now BEFORE triggers are allowed > to alter the key attributes, so the final primary key isn't > known before they are executed. > > Thus the DELETE then INSERT semantic might be the only way. > Pretty havy restriction, making the entire REPLACE INTO > somewhat useless IMHO. > I think that application people would probably prefer the delete trigger, insert trigger. It makes more sense, because I would interpret replace as "get rid of the old if it exists" and "put in a new item". If people wanted to make sure code is run on delete, and they have to put it into a delete trigger and a replace trigger, it would be two places for them. Frankly, I'm not sure why this is being seen as a weak approach. My indended semantic was atomic delete (ignoring error) and insert. Dale.