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 1n8NoK-0004Bf-Mv for pgsql-hackers@arkaria.postgresql.org; Fri, 14 Jan 2022 14:44:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n8NoJ-0007gX-J9 for pgsql-hackers@arkaria.postgresql.org; Fri, 14 Jan 2022 14:44:11 +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 1n8NoJ-0007gN-3W for pgsql-hackers@lists.postgresql.org; Fri, 14 Jan 2022 14:44:11 +0000 Received: from mail-il1-x12e.google.com ([2607:f8b0:4864:20::12e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n8NoF-00080F-Vl for pgsql-hackers@lists.postgresql.org; Fri, 14 Jan 2022 14:44:10 +0000 Received: by mail-il1-x12e.google.com with SMTP id d3so8504297ilr.10 for ; Fri, 14 Jan 2022 06:44:07 -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=GBYUi/h5NF1YBpPipUwqeoi9ZuOtxE91/+e7f3g+nik=; b=V4ZnDtnebsNKY0BKTbHzMsRVl5WnnslPKLKDH7ls7Treqy6kQ+1Ga2PqH3xqYE5LdZ YbLYpjR/ieXnGAOVAdaermBVqc93yOyC46WJv2WytF70OV7DX6PUX/ZCTCwfVECoJO8j m9D66v+eS9LlDUr0di62aFP70AUV8s38LPEHdwD3Hf038ZuOKsJjcX+dLoP88lyj0vX7 5ecmWcKk6GkwPZ3xvkvYqQl4pTypcb9JKGzXeeBKAygNIG2ZXVkooj4iYHeMEbIZEy4a ixzQ0NWZy6UKkmk8U7lVuj7JXBcNoq9DZAUlmgNhwSaKBS521fi5FSEjOXJ+LD2fUCcg Dpsw== 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=GBYUi/h5NF1YBpPipUwqeoi9ZuOtxE91/+e7f3g+nik=; b=we70f+KGsBrnfStkNJ+1la8Fqx140ZCS6DztoBXxix993vrtjUzLHvDd+f9wF7Odla fvrKPcVCO79Iq+rmPh6nwEWpNiZurBddr/r9bATxLRKSpupVT6JSmGitKR2H8uglTdkE ngK52X5RQfTfOkoyFuqd3Ly86KUY0mDTtL8i18xxjI2f8qyAlrFWnB0+/ZbnPhzS0gab 0bOtF67DLOCZ1QNWp2BkBXyA2BC6On/3sK6sJZMZdHgBVYNCByDdR96ToLJyu8fWs0bj TnE6VhnIqI5+BzQFMd28Qmq7dhllw+gZME64+KzuvSHmnEUOGw5Q4YmWN28SenO6Y9/n 6FTQ== X-Gm-Message-State: AOAM533L7pO/KaQS2HuLYgvZIO67eiTNqUPoatGOE/aM9ZxbhlqEerPQ Zsib81FpTSqS91JiiZDwkXb+yA== X-Google-Smtp-Source: ABdhPJxDgca16GXabpL/Pwm3ura55EBhiMFNyHShUyBU17tIVb+U7NI2KjDfknGXesvM+bnsnd1f4g== X-Received: by 2002:a05:6e02:1948:: with SMTP id x8mr5231500ilu.107.1642171446086; Fri, 14 Jan 2022 06:44:06 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id z8sm4132878ill.35.2022.01.14.06.44.04 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 14 Jan 2022 06:44:05 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id C44FB800897; Fri, 14 Jan 2022 08:44:03 -0600 (CST) Date: Fri, 14 Jan 2022 08:44:03 -0600 From: Justin Pryzby To: Alvaro Herrera Cc: Andrew Dunstan , Simon Riggs , Tomas Vondra , Zhihong Yu , Daniel Westermann , Amit Langote , pgsql-hackers@lists.postgresql.org, Pavan Deolasee Subject: Re: support for MERGE Message-ID: <20220114144403.GO14051@telsasoft.com> References: <202201130951.sam572b7y5zn@alvherre.pgsql> <202201131243.oau7egmfr42s@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="kUmo4NyTJbuJZ752" Content-Disposition: inline In-Reply-To: <202201131243.oau7egmfr42s@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 --kUmo4NyTJbuJZ752 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Resending some language fixes to the public documentation. --kUmo4NyTJbuJZ752 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-f-typos.txt" From 28b8532976ddb3e8b617ca007fae6b4822b36527 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 | 48 ++++++++++++++--------------- doc/src/sgml/trigger.sgml | 7 +++-- src/test/regress/expected/merge.out | 4 +-- src/test/regress/sql/merge.sql | 4 +-- 7 files changed, 40 insertions(+), 38 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a1ae8423414..61a10c28120 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 executing an UPDATE. diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3db3908b429..db312681f78 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 477de2689b6..ad61d757af5 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 7700d9b9bb1..149df9f0ad9 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -73,11 +73,11 @@ 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 - executed for any candidate change row. + in the order specified. For each candidate change row, the first clause to + evaluate as true 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. @@ -178,7 +178,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 the actual name of the table or query. @@ -202,7 +202,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. @@ -226,7 +226,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,8 +240,8 @@ 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. + returns true, then the action for that clause + is executed for that row. A condition on a WHEN MATCHED clause can refer to columns @@ -277,8 +277,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. @@ -301,7 +301,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. @@ -312,8 +312,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. @@ -326,8 +326,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. @@ -434,14 +434,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, @@ -467,7 +467,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. @@ -513,8 +513,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 91f199dfe07..877925026a3 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 db568648645..a956904c798 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1460,7 +1460,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 @@ -1520,7 +1520,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 580c674e07f..923a79ba1de 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -966,7 +966,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); @@ -1020,7 +1020,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.1 --kUmo4NyTJbuJZ752--