Обсуждение: REPLACE INTO table a la mySQL

Поиск
Список
Период
Сортировка

REPLACE INTO table a la mySQL

От
"Dale Johnson"
Дата:
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




Re: REPLACE INTO table a la mySQL

От
mlw
Дата:
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.


RE: Re: REPLACE INTO table a la mySQL

От
"Christopher Kings-Lynne"
Дата:
> > 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



Re: REPLACE INTO table a la mySQL

От
mlw
Дата:
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!


Re: REPLACE INTO table a la mySQL

От
Jan Wieck
Дата:
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



Re: Re: REPLACE INTO table a la mySQL

От
Jan Wieck
Дата:
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



Re: REPLACE INTO table a la mySQL

От
mlw
Дата:
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




Re: Re: REPLACE INTO table a la mySQL

От
"Dale Johnson"
Дата:
"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.




Re: REPLACE INTO table a la mySQL

От
mlw
Дата:
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?





Re: Re: REPLACE INTO table a la mySQL

От
Jan Wieck
Дата:
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



Re: Re: REPLACE INTO table a la mySQL

От
mlw
Дата:
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.


Re: Re: REPLACE INTO table a la mySQL

От
Alex Pilosov
Дата:
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 



Re: Re: REPLACE INTO table a la mySQL

От
Hannu Krosing
Дата:
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


Re: Re: REPLACE INTO table a la mySQL

От
Hannu Krosing
Дата:
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


Re: Re: REPLACE INTO table a la mySQL

От
Jan Wieck
Дата:
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



Re: Re: REPLACE INTO table a la mySQL

От
Bruce Momjian
Дата:
> 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
 


Re: Re: REPLACE INTO table a la mySQL

От
"Dale Johnson"
Дата:
"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.