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.

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");
else
SQL("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 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

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

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 ?

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, Pennsylvania 19026

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

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.

Adding another trigger event "replace" is imho not acceptable, since
people guarding their data integrity with standards defined triggers
for insert update and delete would open the door to inconsistency
because they have not defined a replace trigger.

Fire the delete then the insert trigger is imho not a straightforward answer,
since a second possible interpretation would be to fire eighter the insert trigger
or the update trigger if a row already existed.

Andreas

Zeugswetter Andreas SB wrote:

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.

Adding another trigger event "replace" is imho not acceptable, since
people guarding their data integrity with standards defined triggers
for insert update and delete would open the door to inconsistency
because they have not defined a replace trigger.

Fire the delete then the insert trigger is imho not a straightforward answer,
since a second possible interpretation would be to fire eighter the insert trigger
or the update trigger if a row already existed.

Imho the second one is also the only correct one, as the definition of
REPLACE INTO
is "update if the row is there, else insert". The problem is just that
the test must
not fire any triggers and that test+(insert|update) must be atomic and
must fire the
respective trigger for insert|update. This just implies that it can't be
done by
simple rewrite, not that it is undoable.

OTOH, I think that our non-transactional UNIQUE constraint
implementation is a bigger
problem than REPLACE INTO (i.e. one is BUG the other is ENCHANCEMENT).

-----------------
Hannu