Re: MERGE command for inheritance

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

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