Re: new patch of MERGE (merge_204) & a question about duplicated ctid

I did some basic testing of the latest update here, but quickly hit a
problem that wasn't in the previous version.  Attached is the standalone
test script that used to work, but now fails like this:

psql:simple.sql:12: ERROR:  the vars in merge action tlist of qual
should only belongs to the source table or target table

This test case is intended to implement the common UPSERT situation that
is one of the main requests that MERGE is intended to satisfy, using
this syntax:

MERGE INTO Stock t
 USING (VALUES(10,100)) AS s(item_id,balance)
 ON s.item_id=t.item_id
 WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
 WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
 ;

If you can suggest an alternate way to express this that works with the
new patch, I might switch to that and retry.  I was never 100% sure this
was the right way to write this, and I don't have another database with
MERGE support here to try against.  (Aside:  if someone else does, I'd
be really curious to see if the attached test case works or not on
another database system.  I think we need to include compatibility
testing with other MERGE implementations into the test mix here soon.)

Regardless, this failure suggests that you need to add this sort of test
to the regression test set.  We need to have an example of an UPSERT
using constant data in there to make sure this continues to work in the
future.

This is a good week for me in terms of having time for PostgreSQL
hacking, so if you can suggest something here or update the patch I'll
try it soon afterwards.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us


DROP TABLE Stock;
CREATE TABLE Stock(item_id int UNIQUE, balance int);
INSERT INTO Stock VALUES (10, 2200);
INSERT INTO Stock VALUES (20, 1900);
SELECT * FROM Stock ORDER BY item_id;

MERGE INTO Stock t
 USING (VALUES(10,100)) AS s(item_id,balance)
 ON s.item_id=t.item_id
 WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
 WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
 ;

SELECT * FROM Stock ORDER BY item_id;

MERGE INTO Stock t
 USING (VALUES(30,2000)) AS s(item_id,balance)
 ON s.item_id=t.item_id
 WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
 WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
 ;

SELECT * FROM Stock ORDER BY item_id;