On Thu, Sep 23, 2010 at 7:55 PM, Marko Tiikkaja <
> On 2010-09-23 1:31 PM +0300, Boxuan Zhai wrote:
>> I have just generate a new patch of MERGE command.
> I haven't followed the discussion very closely, but this part in the
> regression tests caught my attention:
> +-- we now have a duplicate key in Buy, so when we join to
> +-- Stock we will generate 2 matching rows, not one.
> +-- According to standard this command should fail.
> +-- But it suceeds in PostgreSQL implementation by simply ignoring the
> It doesn't seem like a very good idea to go against the standard here. The
> "second" row is not well defined in this case so the results are
Yes, the result is uncertain. It depends on which row is scanned first,
which is almost out of the control of users.
But, in postgres, this is what the system do for UPDATE.
For example, consider a simple update query like the following:
CREATE TABLE target (id int, val int);
INSERT INTO target VALUES (1, 10);
CREATE TABLE source (id int, add int);
INSERT INTO source VALUES (1, 100);
INSERT INTO source VALUES (1, 100000);
-- DO the update query with source table, which has multiple matched rows
UPDATE target SET val = val + add FROM source
WHERE source.id = target.id;
t=# SELECT * FROM target;
id | val
1 | 110
The target tuple has two matched source tuples, but it is only updated once.
And, yet, this query is not forbidden by postgres. The result is also
> The patch is also missing a (trivial) change to explain.c.
Sorry, I massed up the files. Here comes the new patch file, with EXPLAIN in
> Marko Tiikkaja
In response to
pgsql-hackers by date
|Next:||From: Bruce Momjian||Date: 2010-09-23 13:55:43|
|Subject: Re: Git cvsserver serious issue|
|Previous:||From: Robert Haas||Date: 2010-09-23 13:29:45|
|Subject: Re: Path question|