Re: MERGE command for inheritance - Mailing list pgsql-hackers

Tom Lane wrote:

Do we really think this is anywhere near committable now?
 

There's a relatively objective standard for the first thing needed for commit--does it work?--in the form of the regression tests Simon put together before development.  I just tried the latest merge_v102.patch (regression diff attached) to see how that's going.  There are still a couple of errors in there.  It looks to me like the error handling and related DO NOTHING support are the next pair of things that patch needs work on.  I'd rather see that sorted out than to march onward to inheritance without the fundamentals even nailed down yet.

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


*** /home/postgres/pgwork/repo/git/postgresql/src/test/regress/expected/merge.out       2010-08-11 12:23:50.000000000 -0400
--- /home/postgres/pgwork/repo/git/postgresql/src/test/regress/results/merge.out        2010-08-11 12:33:27.000000000 -0400
***************
*** 44,57 ****
 WHEN MATCHED THEN
       UPDATE SET balance = t.balance + s.balance
 ;
! SELECT * FROM target;
!  id | balance
! ----+---------
!   1 |      10
!   2 |      25
!   3 |      50
! (3 rows)
!
 ROLLBACK;
 -- do a simple equivalent of an INSERT SELECT
 BEGIN;
--- 44,50 ----
 WHEN MATCHED THEN
       UPDATE SET balance = t.balance + s.balance
 ;
! NOTICE:  one tuple is ERROR
 ROLLBACK;
 -- do a simple equivalent of an INSERT SELECT
 BEGIN;
***************
*** 61,66 ****
--- 54,61 ----
 WHEN NOT MATCHED THEN
       INSERT VALUES (s.id, s.balance)
 ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
 SELECT * FROM target;
  id | balance
 ----+---------
***************
*** 102,107 ****
--- 97,103 ----
 WHEN MATCHED THEN
       DELETE
 ;
+ NOTICE:  one tuple is ERROR
 SELECT * FROM target;
  id | balance
 ----+---------
***************
*** 165,176 ****
 ERROR:  multiple actions on single target row

 ROLLBACK;
!
 -- This next SQL statement
 --  fails according to standard
 --  suceeds in PostgreSQL implementation by simply ignoring the second
 --  matching row since it activates no WHEN clause
 BEGIN;
 MERGE into target t
 USING (select * from source) AS s
 ON t.id = s.id
--- 161,175 ----
 ERROR:  multiple actions on single target row

 ROLLBACK;
! ERROR:  syntax error at or near "ERROR"
! LINE 1: ERROR:  multiple actions on single target row
!         ^
 -- This next SQL statement
 --  fails according to standard
 --  suceeds in PostgreSQL implementation by simply ignoring the second
 --  matching row since it activates no WHEN clause
 BEGIN;
+ ERROR:  current transaction is aborted, commands ignored until end of transaction block
 MERGE into target t
 USING (select * from source) AS s
 ON t.id = s.id
***************
*** 179,184 ****
--- 178,184 ----
 WHEN NOT MATCHED THEN
       INSERT VALUES (s.id, s.balance)
 ;
+ ERROR:  current transaction is aborted, commands ignored until end of transaction block
 ROLLBACK;
 -- Now lets prepare the test data to generate 2 non-matching rows
 DELETE FROM source WHERE id = 3 AND balance = 5;
***************
*** 188,195 ****
 ----+---------
   2 |       5
   3 |      20
-   4 |       5
   4 |      40
 (4 rows)

 -- This next SQL statement
--- 188,195 ----
 ----+---------
   2 |       5
   3 |      20
   4 |      40
+   4 |       5
 (4 rows)

 -- This next SQL statement
***************
*** 203,216 ****
 WHEN NOT MATCHED THEN
       INSERT VALUES (s.id, s.balance)
 ;
 SELECT * FROM target;
  id | balance
 ----+---------
   1 |      10
   2 |      20
   3 |      30
-   4 |       5
   4 |      40
 (5 rows)

 ROLLBACK;
--- 203,218 ----
 WHEN NOT MATCHED THEN
       INSERT VALUES (s.id, s.balance)
 ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
 SELECT * FROM target;
  id | balance
 ----+---------
   1 |      10
   2 |      20
   3 |      30
   4 |      40
+   4 |       5
 (5 rows)

 ROLLBACK;
***************
*** 225,239 ****
 WHEN NOT MATCHED AND s.balance > 100 THEN
       INSERT VALUES (s.id, s.balance)
 ;
 SELECT * FROM target;
  id | balance
 ----+---------
   1 |      10
   2 |      20
   3 |      30
!     |
!     |
! (5 rows)

 ROLLBACK;
 -- This next SQL statement suceeds, but does nothing since there are
--- 227,243 ----
 WHEN NOT MATCHED AND s.balance > 100 THEN
       INSERT VALUES (s.id, s.balance)
 ;
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
+ NOTICE:  one tuple is ERROR
 SELECT * FROM target;
  id | balance
 ----+---------
   1 |      10
   2 |      20
   3 |      30
! (3 rows)

 ROLLBACK;
 -- This next SQL statement suceeds, but does nothing since there are
***************
*** 249,262 ****
 WHEN NOT MATCHED
       DO NOTHING
 ;
 SELECT * FROM target;
!  id | balance
! ----+---------
!   1 |      10
!   2 |      20
!   3 |      30
! (3 rows)
!
 ROLLBACK;
 --
 -- Weirdness
--- 253,263 ----
 WHEN NOT MATCHED
       DO NOTHING
 ;
+ ERROR:  syntax error at or near "DO"
+ LINE 7:  DO NOTHING
+          ^
 SELECT * FROM target;
! ERROR:  current transaction is aborted, commands ignored until end of transaction block
 ROLLBACK;
 --
 -- Weirdness

======================================================================