Re: MERGE command for inheritance

On Thu, Aug 12, 2010 at 12:49 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

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

Sorry for the mismatch problem of regress. In fact, I am still unable to
make the regression test run on my machine. When I try the command
pg_regreess in /src/test/regress/, it always gives a FATAL error:

FATAL: parameter "port" cannot be changed without restarting the server
psql: FATAL: parameter "port" cannot be changed without restarting the
server
command failed: ""C:/msys/1.0/local/pgsql/bin//psql" -X -c "DROP DATABASE IF
EXISTS \"regression\"" "postgres""

However, I can run this command directly in the MinGW command line
interface. I guess this is because the psql_command() function has some
check before accept commands. And the MinGW environment cannot pass these
checks.

All the SQL commands in the .sql file have been tested by hand. And they are
all correct. However, the .out file is not automatic generated by pgsql.

I may need to find a linux system to try to generate the correct .out file
some other time. Or, would someone help me to generate an .out file through
pg_regress?

> --
> Greg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us <http://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
>
> ======================================================================
>
>
>