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



Mon, 04 Jun 2001 03:15:03 GMT
"Dale Johnson" <djohnson@mi.ab.ca>
REPLACE INTO table  a la mySQL
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.

Tue, 05 Jun 2001 18:26:22 -0400
mlw <markw@mohawksoft.com>
Re: 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.
>
> 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


Wed, 6 Jun 2001 10:10:44 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Re: REPLACE INTO table  a la mySQL
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");
else
	SQL("insert into table (...) values (...)");


REPLACE into table set xx=yy, ww = zz where ID = fubar;

A MUCH better solution!

Tue, 05 Jun 2001 22:26:44 -0400
mlw <markw@mohawksoft.com>
Re: REPLACE INTO table  a la mySQL
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

--

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


Wed, 6 Jun 2001 11:00:11 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: REPLACE INTO table  a la mySQL
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


Wed, 6 Jun 2001 11:06:39 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Re: REPLACE INTO table  a la mySQL
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



Wed, 06 Jun 2001 16:57:35 -0400
mlw <markw@mohawksoft.com>
Re: REPLACE INTO table  a la mySQL

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



Fri, 08 Jun 2001 09:19:49 GMT
"Dale Johnson" <djohnson@mi.ab.ca>
Re: Re: REPLACE INTO table  a la mySQL
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?




Mon, 11 Jun 2001 11:52:28 -0400
mlw <markw@mohawksoft.com>
Re: REPLACE INTO table  a la mySQL
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.


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


Mon, 11 Jun 2001 16:44:58 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Re: REPLACE INTO table  a la mySQL
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.

Mon, 11 Jun 2001 18:42:41 -0400
mlw <markw@mohawksoft.com>
Re: Re: REPLACE INTO table  a la mySQL
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 


Mon, 11 Jun 2001 19:11:58 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Re: REPLACE INTO table  a la mySQL
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

Tue, 12 Jun 2001 22:56:28 +0500
Hannu Krosing <hannu@tm.ee>
Re: Re: REPLACE INTO table  a la mySQL
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

Tue, 12 Jun 2001 23:34:29 +0500
Hannu Krosing <hannu@tm.ee>
Re: Re: REPLACE INTO table  a la mySQL
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


Wed, 13 Jun 2001 09:12:05 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Re: REPLACE INTO table  a la mySQL
> 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, Pennsylvania 19026

Wed, 13 Jun 2001 11:07:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: REPLACE INTO table  a la mySQL

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




Fri, 15 Jun 2001 09:58:18 GMT
"Dale Johnson" <djohnson@mi.ab.ca>
Re: Re: REPLACE INTO table  a la mySQL