Re: ask for review of MERGE

On Thu, Sep 23, 2010 at 7:55 PM, Marko Tiikkaja <
marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:

> 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
> second
>
> 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
> unpredictable.
>
>
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
(1 row)

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

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

>
> Regards,
> Marko Tiikkaja
>