Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mlxqh-0006dZ-8z for pgsql-hackers@arkaria.postgresql.org; Sat, 13 Nov 2021 18:33:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mlxqf-0004aD-Sb for pgsql-hackers@arkaria.postgresql.org; Sat, 13 Nov 2021 18:33:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mlxqf-0004a4-DI for pgsql-hackers@lists.postgresql.org; Sat, 13 Nov 2021 18:33:57 +0000 Received: from mail-io1-xd2b.google.com ([2607:f8b0:4864:20::d2b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mlxqc-000165-9s for pgsql-hackers@lists.postgresql.org; Sat, 13 Nov 2021 18:33:56 +0000 Received: by mail-io1-xd2b.google.com with SMTP id m9so15635044iop.0 for ; Sat, 13 Nov 2021 10:33:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=RHkcqTgoINwzqhNdXhPBrMcMS5Ag0QrQYLqfmlX2Jhg=; b=Ez6TwHB1R8Ncv6GHFpVnfCwqNtWWGbmg1ACZgCTAbXQjOLhVsOe5RCkHv9y/04iItd KNf2E5znOJmhzCd+vCcHWrv4LV8x31GMCr/OabOwyFQZRSHOPOHQkSq4J8uaXES6OgZq 7F5sfhWcOtY1Lf+Gy8e7ccIZrgZ43VSlhbo6Y8bIwemYno4aVw1kU6c9h6FX6dsBfh4y T6V4XtNJp0wRApAFhcE02Y1ZXiMEx/3HFXr8gGkKWrKDMZTZlHLsJmnZ9C8iidkV2sBF xmgfdmvbaSSxoG0OA/eHjtf1Z6Qy8Oje264y501E9AWhQMxtCUd+6L2WMLdNQnvhU+Up TKCQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=RHkcqTgoINwzqhNdXhPBrMcMS5Ag0QrQYLqfmlX2Jhg=; b=kBtKmCdtfnsI5BBX5RahvftDY+46N8bT/ESu9XjhTzlfziKcq+CbGDsr2d2pL6mgo6 Khzuxcot5g8l0V51NSkJrBizfoTWmK8uzRNQsDV22DQpwLSnvaLeACzbkY2AvJLhyvYm ImfzSFN8QJBMDXHgEbxNVzg8Yq0uDvG6Avws9HVgqMoOwMKflkk3A+SMCjoU+d0qYS76 CaEuQwS97Vr9tjl43rj+GFPPguscSqQ4oN9FNi8haoPhodqXzXyi5L4f3qT/xzhHNo1y kJPuGw3D3IzeoaGRFphIHeb+ZbhSoUif8X4A7jEPTxGf+eJNm/VuciGUgnUIcyYXGtcC eGeg== X-Gm-Message-State: AOAM532FGicuVjLDjM+YsOlsIWXgPPVx6AaoHZBnJ75K5DJLZNicsvW4 tE2MqFkK75mSe3CoMgHmcsprRg== X-Google-Smtp-Source: ABdhPJxFCiOXETacwOVnf2vSBJIAvL90UoeQ5gGpHSH0hmQW81XB7CiKcD/3vB+Lnzr7e2htlQdtIQ== X-Received: by 2002:a5d:9d92:: with SMTP id ay18mr17261153iob.130.1636828432097; Sat, 13 Nov 2021 10:33:52 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id o14sm5257576ioo.36.2021.11.13.10.33.51 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 13 Nov 2021 10:33:51 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 0A9A7800870; Sat, 13 Nov 2021 12:33:49 -0600 (CST) Date: Sat, 13 Nov 2021 12:33:49 -0600 From: Justin Pryzby To: Alvaro Herrera Cc: pgsql-hackers@lists.postgresql.org Subject: Re: support for MERGE Message-ID: <20211113183349.GL17618@telsasoft.com> References: <20201231134736.GA25392@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="8N5nZmKALFZnI1Hj" Content-Disposition: inline In-Reply-To: <20201231134736.GA25392@alvherre.pgsql> User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --8N5nZmKALFZnI1Hj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Thu, Dec 31, 2020 at 10:47:36AM -0300, Alvaro Herrera wrote: > Here's a rebase of Simon/Pavan's MERGE patch to current sources. I > cleaned up some minor things in it, but aside from rebasing, it's pretty > much their work (even the commit message is Simon's). > > Adding to commitfest. I reviewed the documentation to learn about the feature, and fixed some typos. +notpatch. -- Justin --8N5nZmKALFZnI1Hj Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-f-typos.notpatch" From 2f3d2465a93ee6207b2afdab8787cf2eaa2c0bb4 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 13 Nov 2021 12:11:46 -0600 Subject: [PATCH] f!typos --- doc/src/sgml/mvcc.sgml | 8 ++--- doc/src/sgml/ref/create_policy.sgml | 5 +-- doc/src/sgml/ref/insert.sgml | 2 +- doc/src/sgml/ref/merge.sgml | 50 ++++++++++++++--------------- doc/src/sgml/trigger.sgml | 7 ++-- src/test/regress/expected/merge.out | 4 +-- src/test/regress/sql/merge.sql | 4 +-- 7 files changed, 41 insertions(+), 39 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a1ae842341..7dd0a3811f 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -441,16 +441,16 @@ COMMIT; can specify several actions and they can be conditional, the conditions for each action are re-evaluated on the updated version of the row, starting from the first action, even if the action that had - originally matched was later in the list of actions. + originally matched appears later in the list of actions. On the other hand, if the row is concurrently updated or deleted so that the join condition fails, then MERGE will - evaluate the conditions NOT MATCHED actions next, + evaluate the condition's NOT MATCHED actions next, and execute the first one that succeeds. If MERGE attempts an INSERT and a unique index is present and a duplicate row is concurrently - inserted then a uniqueness violation is raised. + inserted, then a uniqueness violation is raised. MERGE does not attempt to avoid the - ERROR by attempting an UPDATE. + ERROR by executign an UPDATE. diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3db3908b42..db312681f7 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -96,10 +96,11 @@ CREATE POLICY name ON - No separate policy exists for MERGE. Instead policies + No separate policy exists for MERGE. Instead, the policies defined for SELECT, INSERT, UPDATE and DELETE are applied - while executing MERGE, depending on the actions that are activated. + while executing MERGE, depending on the actions that are + performed. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 477de2689b..ad61d757af 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -592,7 +592,7 @@ INSERT oid count You may also wish to consider using MERGE, since that - allows mixed INSERT, UPDATE and + allows mixing INSERT, UPDATE and DELETE within a single statement. See . diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index 7e1de11114..f7d29da9d9 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -73,10 +73,10 @@ DELETE from data_source to target_table_name producing zero or more candidate change rows. For each candidate change - row the status of MATCHED or NOT MATCHED + row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated - in the order specified. The first clause to match each candidate change - row is executed. No more than one WHEN clause is + in the order specified. For each candidate change row, the first clause to + the row is executed. No more than one WHEN clause is executed for any candidate change row. @@ -85,14 +85,14 @@ DELETE regular UPDATE, INSERT, or DELETE commands of the same names. The syntax of those commands is different, notably that there is no WHERE - clause and no tablename is specified. All actions refer to the + clause and no table name is specified. All actions refer to the target_table_name, though modifications to other tables may be made using triggers. - When DO NOTHING action is specified, the source row is - skipped. Since actions are evaluated in the given order, DO + When DO NOTHING is specified, the source row is + skipped. Since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling. @@ -179,7 +179,7 @@ DELETE A substitute name for the data source. When an alias is - provided, it completely hides whether table or query was specified. + provided, it completely hides whatever table or query was specified. @@ -203,7 +203,7 @@ DELETE rows should appear in join_condition. join_condition subexpressions that only reference target_table_name - columns can only affect which action is taken, often in surprising ways. + columns can affect which action is taken, often in surprising ways. @@ -227,7 +227,7 @@ DELETE Conversely, if the WHEN clause specifies WHEN NOT MATCHED and the candidate change row does not match a row in the - target_table_name + target_table_name, the WHEN clause is executed if the condition is absent or it evaluates to true. @@ -240,12 +240,12 @@ DELETE An expression that returns a value of type boolean. - If this expression for a WHEN clause - returns true then the action for that clause - clause is executed for that row. + If the expression for a WHEN clause + returns true, then the action for that clause + is executed for that row. The expression may not contain functions that possibly perform writes to the database. - @@ -282,8 +282,8 @@ DELETE is a partitioned table, each row is routed to the appropriate partition and inserted into it. If target_table_name - is a partition, an error will occur if one of the input rows violates - the partition constraint. + is a partition, an error will occur if any input row violates the + partition constraint. Column names may not be specified more than once. @@ -306,7 +306,7 @@ DELETE Column names may not be specified more than once. - A table name and WHERE clause are not allowed. + Neither a table name nor a WHERE clause are allowed. @@ -317,8 +317,8 @@ DELETE Specifies a DELETE action that deletes the current row of the target_table_name. - Do not include the tablename or any other clauses, as you would normally - do with an command. + Do not include the table name or any other clauses, as you would normally + do with a command. @@ -331,8 +331,8 @@ DELETE class="parameter">target_table_name. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite - column leaves the other fields null.) When referencing a - column, do not include the table's name in the specification + column leaves the other fields null.) + Do not include the table's name in the specification of a target column. @@ -439,14 +439,14 @@ MERGE total-count Perform any BEFORE STATEMENT triggers for all actions specified, whether or not their WHEN - clauses are executed. + clauses match. Perform a join from source to target table. The resulting query will be optimized normally and will produce - a set of candidate change row. For each candidate change row, + a set of candidate change rows. For each candidate change row, @@ -472,7 +472,7 @@ MERGE total-count - Apply the action specified, invoking any check constraints on the + Perform the specified action, invoking any check constraints on the target table. @@ -518,8 +518,8 @@ MERGE total-count This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by MERGE. - If the repeated action is an INSERT this will - cause a uniqueness violation while a repeated UPDATE + If the repeated action is an INSERT, this will + cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard. This differs from historical PostgreSQL diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 91f199dfe0..877925026a 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -196,15 +196,16 @@ No separate triggers are defined for MERGE. Instead, statement-level or row-level UPDATE, DELETE and INSERT triggers are fired - depending on what actions are specified in the MERGE query - and what actions are activated. + depending on (for statement-level triggers) what actions are specified in + the MERGE query and (for row-level triggers) what + actions are performed. While running a MERGE command, statement-level BEFORE and AFTER triggers are fired for events specified in the actions of the MERGE command, - irrespective of whether the action is finally activated or not. This is same as + irrespective of whether or not the action is ultimately performed. This is same as an UPDATE statement that updates no rows, yet statement-level triggers are fired. The row-level triggers are fired only when a row is actually updated, inserted or deleted. So it's perfectly legal diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 840091ef75..e17ae19a1c 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1469,7 +1469,7 @@ SELECT * FROM pa_target ORDER BY tid; ROLLBACK; DROP TABLE pa_source; DROP TABLE pa_target CASCADE; --- Sub-partitionin +-- Sub-partitioning CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text) PARTITION BY RANGE (logts); CREATE TABLE part_m01 PARTITION OF pa_target @@ -1529,7 +1529,7 @@ INSERT INTO cj_source1 VALUES (3, 10, 400); INSERT INTO cj_source2 VALUES (1, 'initial source2'); INSERT INTO cj_source2 VALUES (2, 'initial source2'); INSERT INTO cj_source2 VALUES (3, 'initial source2'); --- source relation is an unalised join +-- source relation is an unaliased join MERGE INTO cj_target t USING cj_source1 s1 INNER JOIN cj_source2 s2 ON sid1 = sid2 diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index ead408664d..666e8d939f 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -981,7 +981,7 @@ ROLLBACK; DROP TABLE pa_source; DROP TABLE pa_target CASCADE; --- Sub-partitionin +-- Sub-partitioning CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text) PARTITION BY RANGE (logts); @@ -1035,7 +1035,7 @@ INSERT INTO cj_source2 VALUES (1, 'initial source2'); INSERT INTO cj_source2 VALUES (2, 'initial source2'); INSERT INTO cj_source2 VALUES (3, 'initial source2'); --- source relation is an unalised join +-- source relation is an unaliased join MERGE INTO cj_target t USING cj_source1 s1 INNER JOIN cj_source2 s2 ON sid1 = sid2 -- 2.17.0 --8N5nZmKALFZnI1Hj--