On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
Btw about that keys, oracle gives error on many-to-one or many-to-many
relationship between the source and target tables.
The standard has something called a cardinality violation if the
to-be-merged table doesn't match 1-1 with the
Martijn,
Here is a quick test (Oracle 10.1.0.3/Linux):
SQL select banner from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE10.1.0.3.0
On Tue, Nov 22, 2005 at 04:20:12AM +0100, Petr Jelinek wrote:
It was already said here that oracle and db2 both use MERGE, dunno about
mssql.
And yes merge CAN be used to do REPLACE (oracle uses their dummy table
for this, we can use the fact that FROM clause isn't required in postgres).
Jaime Casanova wrote:
And yes merge CAN be used to do REPLACE (oracle uses their dummy table
for this, we can use the fact that FROM clause isn't required in postgres).
the FROM clause is required by default (starting with 8.1) unless you
change a postgresql.conf parameter.
and i
On 11/22/05, Bruce Momjian pgman@candle.pha.pa.us wrote:
Jaime Casanova wrote:
And yes merge CAN be used to do REPLACE (oracle uses their dummy table
for this, we can use the fact that FROM clause isn't required in
postgres).
the FROM clause is required by default (starting
On Tue, Nov 22, 2005 at 11:57:48AM +0100, Martijn van Oosterhout wrote:
excellent research snipped
Rather than trying to make MERGE do something it wasn't designed for,
we should probably be spending our efforts on triggers for error
conditions. Maybe something like:
CREATE TRIGGER foo
Jaime Casanova wrote:
the FROM clause is required by default (starting with 8.1) unless you
change a postgresql.conf parameter.
and i don't think that idea will have any fan...
Bruce already replied to your first statement so, what idea won't have
any fan ? It's not that we would change
On Thu, Nov 17, 2005 at 09:30:43PM -0500, Bruce Momjian wrote:
Is the requirement for predicate locking, over and above a unique
constraint on an index that involves the record key, to deal with
the scenario of two inserts executing at the same time, both before
commit?
No. If you have
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
I don't think MERGE can really be made to be both though, in which case
it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
DUPLICATE UPDATE something else. Perhaps a special form of MERGE where
you know it's
Jim C. Nasby wrote:
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
I don't think MERGE can really be made to be both though, in which case
it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
DUPLICATE UPDATE something else. Perhaps a special form of MERGE
And yes merge CAN be used to do REPLACE (oracle uses their dummy table
for this, we can use the fact that FROM clause isn't required in postgres).
the FROM clause is required by default (starting with 8.1) unless you
change a postgresql.conf parameter.
and i don't think that idea will have
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Oh, good point. I was thinking just about concurrent MERGEs. However,
it is more complicated than that. By definitaion you can not see
changes from other transactions while your statement is being run (even
if you increment
Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.
update
if no rows updated
insert
if duplicate key
update
if no rows updated goto insert
That is why you have the loop. This is not a problem with above code,
Oh, good point. I was thinking just about concurrent MERGEs. However,
it is more complicated than that. By definitaion you can not see
changes from other transactions while your statement is being run (even
if you increment CommandCounter), so to be atomic, you would still see
the row even
Bruce Momjian pgman@candle.pha.pa.us writes:
Oh, good point. I was thinking just about concurrent MERGEs. However,
it is more complicated than that. By definitaion you can not see
changes from other transactions while your statement is being run (even
if you increment CommandCounter), so to
Well, from my point of view it is more than delete and insert. That I
can do right now with existing infrastructure. The problem I try to
solve is something along: a bunch of clients try to update a count, and
ONE of them must initialize the count if it does not exist... this can't
be done with
The problem I try to solve is something along: a bunch of clients try
to update a count, and ONE of them must initialize the count if it
does not exist... this can't be done with current infrastructure without
race conditions.
The solution without merge but a unique key in other db's is:
Yes, these algorithms are clear to me, but they don't work for batch
updates in postgres without savepoints before each row insert/update,
which is not good for performance (not to mention on older postgres
versions without savepoint support it won't work at all). If there is a
way of no race
On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote:
Yes, these algorithms are clear to me, but they don't work for batch
updates in postgres without savepoints before each row insert/update,
which is not good for performance (not to mention on older postgres
versions without savepoint
OK, in this case I don't care about either MERGE or REPLACE, but for an
UPSERT which does the locking :-)
Cheers,
Csaba.
On Thu, 2005-11-17 at 13:32, Martijn van Oosterhout wrote:
On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote:
Yes, these algorithms are clear to me, but they don't
Csaba Nagy [EMAIL PROTECTED] writes:
OK, in this case I don't care about either MERGE or REPLACE, but for an
UPSERT which does the locking :-)
This is exactly the point --- pretty much nobody has come to us and
asked for a feature that does what Peter and Martijn say MERGE does.
(I haven't
* Tom Lane ([EMAIL PROTECTED]) wrote:
This is exactly the point --- pretty much nobody has come to us and
asked for a feature that does what Peter and Martijn say MERGE does.
(I haven't bothered to look at the 2003 spec, I'm assuming they read it
correctly.) What we *have* been asked for,
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
transaction which is supposed to not fail but instead do locking to
ensure that it doesn't fail. This requires predicate locking to be
efficient because you want to
Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.
---
Zeugswetter Andreas DCP SD wrote:
The problem I try to solve is something along: a bunch of clients try
[EMAIL PROTECTED] wrote:
On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
transaction which is supposed to not fail but instead do locking to
ensure that it doesn't fail. This requires predicate locking to be
Tom Lane wrote:
Csaba Nagy [EMAIL PROTECTED] writes:
OK, in this case I don't care about either MERGE or REPLACE, but for an
UPSERT which does the locking :-)
This is exactly the point --- pretty much nobody has come to us and
asked for a feature that does what Peter and Martijn say MERGE
On Thu, 17 Nov 2005, Bruce Momjian wrote:
Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.
No matter what operation you start with you need a loop that try
insert/update until one of them succeed like in this example:
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
We should probably throw a notice or warning if we go to a table lock,
too.
That's not very useful, because you can only do somethign about it AFTER
the 1 hour exclusive lock merge has already run :)
We shouldn't do anything remotely like
I agree. I would never ever ever want it to silently start doing
table locks. I would simply avoid using merge at all if that was a
possibility.
However it seems like the idea is to eventually flesh out full
fledged merge. And to do that it sounds like you would need to do
one of the
You could also just add something to the merge syntax like ALLOW
TABLE LOCK or something. The idea is just that the user can
explicitly allow the table lock and thus the more complicated merge.
The problem here is that many people will see that option and think
it's safe to do it... i mean,
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table lock.
---
Rick Gigger wrote:
I agree. I would never ever ever
On 11/16/05, Bruce Momjian pgman@candle.pha.pa.us wrote:
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table lock.
If the lock will be required, what's the problem in doing it
Jaime Casanova [EMAIL PROTECTED] writes:
If the lock will be required, what's the problem in doing it
internally?
I already explained that: lock upgrading is deadlock-prone.
regards, tom lane
---(end of broadcast)---
TIP
Jaime Casanova wrote:
On 11/16/05, Bruce Momjian pgman@candle.pha.pa.us wrote:
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table lock.
If the lock will be required, what's
On Wed, Nov 16, 2005 at 11:37:46AM -0500, Bruce Momjian wrote:
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table lock.
The bit I'm still missing is why there needs to be a lock at
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
On Wed, Nov 16, 2005 at 11:37:46AM -0500, Bruce Momjian wrote:
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table
On Wed, 2005-11-16 at 18:34 +0100, Martijn van Oosterhout wrote:
On Wed, Nov 16, 2005 at 11:37:46AM -0500, Bruce Momjian wrote:
Interesting approach. Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table
On Wed, Nov 16, 2005 at 12:59:36PM -0500, Bruce Momjian wrote:
Martijn van Oosterhout wrote:
The bit I'm still missing is why there needs to be a lock at all. The
SQL standard doesn't say anywhere that concurrent MERGE operations
can't conflict. It seems to me that standard visibility rules
; pgsql-hackers@postgresql.org; Jaime
Casanova;
Peter Eisentraut
Subject: Re: [HACKERS] MERGE vs REPLACE
On Wed, 2005-11-16 at 18:34 +0100, Martijn van Oosterhout wrote:
On Wed, Nov 16, 2005 at 11:37:46AM -0500, Bruce Momjian wrote:
Interesting approach. Actually, we could tell the user
On Wed, Nov 16, 2005 at 11:06:15AM -0800, Dann Corbit wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Simon Riggs
Conceptually, a MERGE statement is just a long string of INSERTs and
UPDATEs in the same transaction and I
Conceptually, a MERGE statement is just a long string of INSERTs and
UPDATEs in the same transaction and I think we should treat it as
such.
I've just got one question about this. Everyone seems to be saying
that try to insert and if that fails update is the same as try to
insert and if
On Wed, Nov 16, 2005 at 09:49:28AM -0500, Tom Lane wrote:
I think we should do REPLACE-like functionality that simply fails if the
match condition isn't equality on a primary key. If we can use SQL-spec
MERGE syntax for this, that's fine, but let's not think in terms of
silently changing to a
daveg [EMAIL PROTECTED] writes:
I agree, but would like to relax the primary key requirement to simply
a unique index. I can see use cases for unique so long as not null keys,
so it would be nice if the MERGE operation would work for these. As nulls
are not equal anyway this doesn't seem to do
On Wed, Nov 16, 2005 at 04:51:07PM -0500, Tom Lane wrote:
daveg [EMAIL PROTECTED] writes:
I agree, but would like to relax the primary key requirement to simply
a unique index. I can see use cases for unique so long as not null keys,
so it would be nice if the MERGE operation would work for
On Tue, Nov 15, 2005 at 10:27:10AM -0800, Josh Berkus wrote:
Yes, I guess my hesitation on the full-table-lock strategy is that it
doesn't really fulfill the mandate for why people want REPLACE-like
statements ... to give them an INSERT-or-UPDATE with *higher* efficiency
and concurrency
* Tom Lane ([EMAIL PROTECTED]) wrote:
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
On 11/15/05, Josh Berkus josh@agliodbs.com wrote:
Simon,
The UPSERT concept is also supported by Teradata, who simply append an
ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems
to me to be a fairly small subset of MERGE functionality and we ought to
be able to
On Tue, 2005-11-15 at 10:27 -0800, Josh Berkus wrote:
The UPSERT concept is also supported by Teradata, who simply append an
ELSE INSERT clause onto the standard UPDATE syntax. MySQL REPLACE seems
to me to be a fairly small subset of MERGE functionality and we ought to
be able to offer
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
Tom Lane wrote:
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
Simon Riggs wrote:
I've been looking at ways of doing INSERT then UPDATE, but it doesn't
seem very easy to avoid unique index violations in that case. So doing
the UPDATE first then INSERTs later seems like the way to go.
INSERT has to be first to avoid a race condition (see my previous
Josh Berkus wrote:
So we thus have two seperate use cases. The first, for bulk loading/ETL is
what MERGE fulfills rather neatly and for that full table locking is
perfectly OK, even desirable. You really don't want to MERGE-load the
same table on two threads at once.
The second case
Just to summarize, MySQL REPLACE is INSERT or DELETE/INSERT, while they
have a SET clauses that allows UPDATE, and INSERT has a ON DUPLICATE KEY
UPDATE clause too.
I think the INSERT ... ON DUPLICATE KEY is undesirable because this
functionality should have a new keyword in the first position,
On Tue, Nov 15, 2005 at 07:16:21PM -0500, Bruce Momjian wrote:
Tom Lane wrote:
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
We should probably throw a notice or warning if we go to a table lock,
too.
That's not very useful, because you can only do somethign about it AFTER
the 1 hour exclusive lock merge has already run :)
Chris
---(end of broadcast)---
TIP 2:
On Wed, Nov 16, 2005 at 01:27:29PM +0800, Christopher Kings-Lynne wrote:
We should probably throw a notice or warning if we go to a table lock,
too.
That's not very useful, because you can only do somethign about it AFTER
the 1 hour exclusive lock merge has already run :)
Not true; it
On 11/13/05, Petr Jelinek wrote:
I am really not db expert and I don't have copy of sql standard but you
don't need to use 2 tables I think - USING part can also be subquery
(some SELECT) and if I am right then you could simulate what REPLACE
does because in PostgreSQL you are not forced to
I think you translated it correctly, MySQL has another way of specifying this
which is INSERT ... ON DUPLICATE KEY UPDATE ...
(http://dev.mysql.com/doc/refman/5.0/en/insert.html)
Regards
Paolo
Jochem van Dieten [EMAIL PROTECTED] ha scritto
On 11/13/05, Petr Jelinek wrote:
I am really not
See 'merge_db' in http://lnk.nu/postgresql.org/5sl.html
On Fri, Nov 11, 2005 at 10:07:07PM -0500, Rod Taylor wrote:
On Fri, 2005-11-11 at 18:36 -0500, [EMAIL PROTECTED] 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
On Fri, Nov 11, 2005 at 03:42:38PM -0500, Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] 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
On Sun, 2005-11-13 at 23:56 +0100, Martijn van Oosterhout wrote:
On Sun, Nov 13, 2005 at 11:32:47PM +0100, Petr Jelinek wrote:
I am really not db expert and I don't have copy of sql standard but you
don't need to use 2 tables I think - USING part can also be subquery
(some SELECT) and if
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] 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
On 11/13/05, Robert Treat [EMAIL PROTECTED] 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
On Sunday 13 November 2005 10:01, Gregory Maxwell wrote:
On 11/13/05, Robert Treat [EMAIL PROTECTED] 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
Robert Treat [EMAIL PROTECTED] 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
Tom Lane wrote:
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?
IIRC they both have MERGE.
--
Regards
Petr Jelinek (PJMODOS)
---(end of
Petr Jelinek wrote:
Tom Lane wrote:
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?
Oracle:
http://www.psoug.org/reference/merge.html
http://www.psoug.org/reference/translate_replace.html
Tom Lane wrote:
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.
In that regard, MERGE is quite flexible, but MERGE doesn't address the
point of REPLACE, because MERGE requires *two* tables as input,
Peter Eisentraut wrote:
In that regard, MERGE is quite flexible, but MERGE doesn't address the
point of REPLACE, because MERGE requires *two* tables as input, whereas
REPLACE only takes *one*. Unless someone can show that you can trick
MERGE into doing the REPLACE job anyway, we're not
On Sun, Nov 13, 2005 at 11:32:47PM +0100, Petr Jelinek wrote:
I am really not db expert and I don't have copy of sql standard but you
don't need to use 2 tables I think - USING part can also be subquery
(some SELECT) and if I am right then you could simulate what REPLACE
does because in
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] 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
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.
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
On 11/11/05, Peter Eisentraut [EMAIL PROTECTED] 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
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 [EMAIL PROTECTED] 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
On 11/11/05, Peter Eisentraut [EMAIL PROTECTED] 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
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.
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
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:
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
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
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
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
On Fri, 2005-11-11 at 18:36 -0500, [EMAIL PROTECTED] 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
85 matches
Mail list logo
