MERGE vs REPLACE
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.
That said, what kind of support for insert-or-update-this-row do we want
to provide, if any? Should it be a REPLACE command, an extension of
the INSERT command, a modication of the MERGE syntax, or something
else?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut wrote:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.That said, what kind of support for insert-or-update-this-row do we want
to provide, if any? Should it be a REPLACE command, an extension of
the INSERT command, a modication of the MERGE syntax, or something
else?
MERGE of course, it's standard, REPLACE is mysql extension
--
Regards
Petr Jelinek (PJMODOS)
www.parba.cz
On 11/11/05, Peter Eisentraut <peter_e@gmx.net> wrote:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.That said, what kind of support for insert-or-update-this-row do we want
to provide, if any? Should it be a REPLACE command, an extension of
the INSERT command, a modication of the MERGE syntax, or something
else?--
Peter Eisentraut
http://developer.postgresql.org/~petere/
MERGE seems to me the better option... not just because is standard
but at least i can see some use cases for it...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Jaime Casanova wrote:
MERGE seems to me the better option... not just because is standard
but at least i can see some use cases for it...
I don't think you understand my message: MERGE does not do what REPLACE
does.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.
Normally I'd plump for following the standard ... but AFAIR, we have had
bucketloads of requests for REPLACE functionality, and not one request
for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
whole lot harder and slower than REPLACE, it seems that we could do
worse than to concentrate on doing REPLACE for now. (We can always come
back to MERGE some other day.)
regards, tom lane
On 11/11/05, Peter Eisentraut <peter_e@gmx.net> wrote:
Jaime Casanova wrote:
MERGE seems to me the better option... not just because is standard
but at least i can see some use cases for it...I don't think you understand my message: MERGE does not do what REPLACE
does.--
Peter Eisentraut
http://developer.postgresql.org/~petere/
I understand you well... what i was trying to say is that i prefer
MERGE (standard SQL command) to be done because the functionally it
has (basically a merge of two tables) seems to me to be more usefull
than REPLACE (MySql Command)...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Guys,
I understand you well... what i was trying to say is that i prefer
MERGE (standard SQL command) to be done because the functionally it
has (basically a merge of two tables) seems to me to be more usefull
than REPLACE (MySql Command)...
But even REPLACE requires predicate locking. There's no real way to get
around it.
--Josh
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
On 11/11/05, Josh Berkus <josh@agliodbs.com> wrote:
Guys,
I understand you well... what i was trying to say is that i prefer
MERGE (standard SQL command) to be done because the functionally it
has (basically a merge of two tables) seems to me to be more usefull
than REPLACE (MySql Command)...But even REPLACE requires predicate locking. There's no real way to get
around it.--Josh
why? seems that REPLACE only work if there are at least one row matching...
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Jaime,
why? seems that REPLACE only work if there are at least one row
matching...
Scenario:
session1: REPLACE .... 1
session2: REPLACE ..... 1
session1: check to see that "1" exists .... no
session2: check to see that "1" exists .... no
session1: INSERT 1
session2: INSERT 1 .... ERROR
Get the picture? The only way to avoid a race condition is to be able to
do "predicate locking", that is to lock the table against any data write
matching that predicate.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
On Fri, 2005-11-11 at 14:40 -0800, Josh Berkus wrote:
Jaime,
why? seems that REPLACE only work if there are at least one row
matching...
Get the picture? The only way to avoid a race condition is to be able to
do "predicate locking", that is to lock the table against any data write
matching that predicate.
So? That is what save points are for. You can even skip the select for
update if you don't mind dead tuples from the attempted insert.
SELECT ... FOR UPDATE;
IF not exists THEN
SAVEPOINT;
INSERT ;
IF UNIQUE VIOLATION THEN
/* Someone else inserted between the SELECT and our INSERT */
ROLLBACK TO SAVEPOINT;
UPDATE;
ELSE
RELEASE SAVEPOINT;
FI
ELSE
UPDATE;
FI
--
Josh Berkus <josh@agliodbs.com> writes:
But even REPLACE requires predicate locking. There's no real way to get
around it.
The point though is that REPLACE is restricted to a type of predicate
narrow enough to be enforced through a unique-index mechanism, and so
it's implementable without solving the general case of predicate
locking.
Predicate locking for narrow cases isn't very hard; it's the general
case of arbitrary predicates that's hard.
regards, tom lane
On Fri, 11 Nov 2005, Josh Berkus wrote:
Jaime,
why? seems that REPLACE only work if there are at least one row
matching...Scenario:
session1: REPLACE .... 1
session2: REPLACE ..... 1
session1: check to see that "1" exists .... no
session2: check to see that "1" exists .... no
session1: INSERT 1
session2: INSERT 1 .... ERRORGet the picture? The only way to avoid a race condition is to be able to
do "predicate locking", that is to lock the table against any data write
matching that predicate.
When it comes to predicate locking, I think we should defer to Peter's
comment at Open DB Con:
http://www.treehou.se/~swm/peter_merge.jpg
Gavin
On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote:
So? That is what save points are for. You can even skip the select for
update if you don't mind dead tuples from the attempted insert.
SELECT ... FOR UPDATE;
IF not exists THEN
SAVEPOINT;
INSERT ;
IF UNIQUE VIOLATION THEN
/* Someone else inserted between the SELECT and our INSERT */
ROLLBACK TO SAVEPOINT;
UPDATE;
ELSE
RELEASE SAVEPOINT;
FI
ELSE
UPDATE;
FI
Isn't there still a race between INSERT and UPDATE?
Low probability, for sure, as it would have had to not exist, then
exist, then not exist, but still possible.
I'd like a REPLACE that could be safe, or at least cause a COMMIT to
fail, for this reason.
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
On Fri, 2005-11-11 at 18:36 -0500, mark@mark.mielke.cc wrote:
On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote:
So? That is what save points are for. You can even skip the select for
update if you don't mind dead tuples from the attempted insert.
SELECT ... FOR UPDATE;
IF not exists THEN
SAVEPOINT;
INSERT ;
IF UNIQUE VIOLATION THEN
/* Someone else inserted between the SELECT and our INSERT */
ROLLBACK TO SAVEPOINT;
UPDATE;
ELSE
RELEASE SAVEPOINT;
FI
ELSE
UPDATE;
FIIsn't there still a race between INSERT and UPDATE?
I suppose there is although I hadn't noticed before. I've never run into
it and always check to ensure the expected number of tuples were touched
by the update or delete.
Within the PostgreSQL backend you might get away with having your insert
hold a lock on the index page and follow it up with a FOR UPDATE lock on
the offending tuple thus ensuring that your update will succeed. If you
hack index mechanisms for the support you don't need the SAVEPOINT
either -- just don't throw an error when you run across the existing
entry.
For client side code one possibility is to repeat until successful.
WHILE
SELECT FOR UPDATE;
IF NOT EXISTS THEN
SAVEPOINT
INSERT;
IF UNIQUE VIOLATION THEN
ROLLBACK TO SAVEPOINT;
ELSE
RELEASE SAVEPOINT
EXIT;
FI
ELSE
UPDATE;
EXIT;
END
-- Check for infinite loop
END
--
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.Normally I'd plump for following the standard ... but AFAIR, we have had
bucketloads of requests for REPLACE functionality, and not one request
for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
whole lot harder and slower than REPLACE, it seems that we could do
worse than to concentrate on doing REPLACE for now. (We can always come
back to MERGE some other day.)
I would also like to add that MySQL's REPLACE is not exactly an INSERT
OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the
fields not specified in the query are set to their defaults:
i.e.
CREATE TABLE t (a int PRIMARY KEY, b int, c int);
INSERT INTO t (a, b, c) VALUES (1, 1, 2);
SELECT * FROM t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 2 |
+---+------+------+
REPLACE INTO t (a, b) VALUES (1, 1);
SELECT * FROM t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | NULL |
+---+------+------+
I wanted to point it out this because people are commonly mistaking this.
Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.Normally I'd plump for following the standard ... but AFAIR, we have had
bucketloads of requests for REPLACE functionality, and not one request
for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
whole lot harder and slower than REPLACE, it seems that we could do
worse than to concentrate on doing REPLACE for now. (We can always come
back to MERGE some other day.)I would also like to add that MySQL's REPLACE is not exactly an INSERT
OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the
fields not specified in the query are set to their defaults:i.e.
CREATE TABLE t (a int PRIMARY KEY, b int, c int);
INSERT INTO t (a, b, c) VALUES (1, 1, 2);
SELECT * FROM t;
+---+------+------+| a | b | c |
+---+------+------+
| 1 | 1 | 2 |
+---+------+------+
REPLACE INTO t (a, b) VALUES (1, 1);
SELECT * FROM t;
+---+------+------+| a | b | c |
+---+------+------+
| 1 | 1 | NULL |
+---+------+------+
I wanted to point it out this because people are commonly mistaking this.
Wow, that seems ugly.... maybe there's a reason for it, but I'm not sure we
could deviate from my$ql's behavior on this even if we wanted... they are the
"standard" here.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On 11/13/05, Robert Treat <xzilla@users.sourceforge.net> wrote:
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
| 1 | 1 | NULL |
Wow, that seems ugly.... maybe there's a reason for it, but I'm not sure we
could deviate from my$ql's behavior on this even if we wanted... they are the
"standard" here.
I don't think that's ugly, I think that's exactly working as
advertised. Replace behaves exactly like deleting the record with the
matching primary key and inserting the provided input. ... not merging
together old data with new.
On Sunday 13 November 2005 10:01, Gregory Maxwell wrote:
On 11/13/05, Robert Treat <xzilla@users.sourceforge.net> wrote:
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
| 1 | 1 | NULL |
Wow, that seems ugly.... maybe there's a reason for it, but I'm not sure
we could deviate from my$ql's behavior on this even if we wanted... they
are the "standard" here.I don't think that's ugly, I think that's exactly working as
advertised. Replace behaves exactly like deleting the record with the
matching primary key and inserting the provided input. ... not merging
together old data with new.
I disagree in that REPLACE is advertised as a solution for the INSERT else
UPDATE problem, but has a different behavior than a true INSERT else UPDATE
would produce. Maybe that's a problem with the implementation, or maybe
it's a problem in the advertisment, but there is certainly a discrepency
there.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
I disagree in that REPLACE is advertised as a solution for the INSERT else
UPDATE problem, but has a different behavior than a true INSERT else UPDATE
would produce. Maybe that's a problem with the implementation, or maybe
it's a problem in the advertisment, but there is certainly a discrepency
there.
Yeah. REPLACE fails to solve common examples like a web hit counter
("if key doesn't exist, insert row with count 1; if it does exist,
add 1 to the current count").
IIRC, SQL's MERGE deals with this by offering two quite separate
specifications of what to do when there is or isn't already a matching
row.
I don't necessarily feel that we have to slavishly duplicate what MySQL
offers. I do think that it's reasonable to restrict the functionality
to updating/replacing a row with matching primary key --- that gets us
out of the problem of needing a full predicate-locking mechanism, while
still covering most all of the practical use-cases that I can see.
It'd be useful to look at what comparable functionality is offered by
other DBs besides MySQL. Anyone know what DB2 or Oracle have in this
area?
regards, tom lane
