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
g...@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
======================================================================
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
