pgsql: doc: Mention all options equivalent to pg_dump --filter patterns
doc: Mention all options equivalent to pg_dump --filter patterns. In the documentation for pg_dump's new --filter option, added by commit a5cf808be5, each object pattern should match some other existing pg_dump option, but some had been omitted, so add them. Noted by Daniel Gustafsson, reviewed by Ayush Vatsa. Discussion: https://postgr.es/m/CAEZATCWtVUt51B6BjTUQoS4dcNyOBj%2B04ngL7HSH3ehBXTUt%3Dw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c50d4f4028e5518511b9bfc3a17860a90dc88357 Modified Files -- doc/src/sgml/ref/pg_dump.sgml | 37 + 1 file changed, 21 insertions(+), 16 deletions(-)
pgsql: Fix PL/pgSQL's handling of integer ranges containing underscores
Fix PL/pgSQL's handling of integer ranges containing underscores. Commit faff8f8e47 allowed integer literals to contain underscores, but failed to update the lexer's "numericfail" rule. As a result, a decimal integer literal containing underscores would fail to parse, if used in an integer range with no whitespace after the first number, such as "1_001..1_003" in a PL/pgSQL FOR loop. Fix and backpatch to v16, where support for underscores in integer literals was added. Report and patch by Erik Wienhold. Discussion: https://postgr.es/m/808ce947-46ec-4628-85fa-3dd600b2c154%40ewie.name Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/b4e909082fa114d5934ca622b225d2352ec639fa Modified Files -- src/backend/parser/scan.l| 2 +- src/fe_utils/psqlscan.l | 2 +- src/interfaces/ecpg/preproc/pgc.l| 2 +- src/test/regress/expected/numerology.out | 11 +++ src/test/regress/sql/numerology.sql | 9 + 5 files changed, 23 insertions(+), 3 deletions(-)
pgsql: Fix PL/pgSQL's handling of integer ranges containing underscores
Fix PL/pgSQL's handling of integer ranges containing underscores. Commit faff8f8e47 allowed integer literals to contain underscores, but failed to update the lexer's "numericfail" rule. As a result, a decimal integer literal containing underscores would fail to parse, if used in an integer range with no whitespace after the first number, such as "1_001..1_003" in a PL/pgSQL FOR loop. Fix and backpatch to v16, where support for underscores in integer literals was added. Report and patch by Erik Wienhold. Discussion: https://postgr.es/m/808ce947-46ec-4628-85fa-3dd600b2c154%40ewie.name Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/cd2624fd97b0c36b68da278abc5362647f69b07d Modified Files -- src/backend/parser/scan.l| 2 +- src/fe_utils/psqlscan.l | 2 +- src/interfaces/ecpg/preproc/pgc.l| 2 +- src/test/regress/expected/numerology.out | 11 +++ src/test/regress/sql/numerology.sql | 9 + 5 files changed, 23 insertions(+), 3 deletions(-)
pgsql: Fix another couple of outdated comments for MERGE RETURNING.
Fix another couple of outdated comments for MERGE RETURNING. Oversights in c649fa24a4 which added RETURNING support to MERGE. Discussion: https://postgr.es/m/caaphdvpqp6vtuzg-_josueibgyqnrnvxj-vdf+hjlxjhdhz...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/5c5bccef211cfc98e0d6c4bc1af40a33c8ac2488 Modified Files -- src/backend/utils/adt/ruleutils.c | 2 +- src/include/nodes/parsenodes.h| 2 +- src/include/utils/portal.h| 6 +++--- 3 files changed, 5 insertions(+), 5 deletions(-)
pgsql: Use macro NUM_MERGE_MATCH_KINDS instead of '3' in MERGE code.
Use macro NUM_MERGE_MATCH_KINDS instead of '3' in MERGE code. Code quality improvement for 0294df2f1f84. Aleksander Alekseev, reviewed by Richard Guo. Discussion: https://postgr.es/m/CAJ7c6TMsiaV5urU_Pq6zJ2tXPDwk69-NKVh4AMN5XrRiM7N%2BGA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/2e068db56e31dfb510fe7416e52b7affe26f278f Modified Files -- src/backend/optimizer/prep/prepjointree.c | 2 +- src/backend/parser/parse_merge.c | 2 +- src/include/nodes/execnodes.h | 2 +- src/include/nodes/primnodes.h | 2 ++ 4 files changed, 5 insertions(+), 3 deletions(-)
pgsql: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
Add support for MERGE ... WHEN NOT MATCHED BY SOURCE. This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or DO NOTHING sub-commands. This is in contrast to already-supported WHEN NOT MATCHED actions, which operate on rows that exist in the data source, but not in the target relation. To make this distinction clearer, such actions may now be written as WHEN NOT MATCHED BY TARGET. Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is equivalent to writing WHEN NOT MATCHED BY TARGET. Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing. Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0r4nezuvwp+syrk...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0294df2f1f842dfb0eed79007b21016f486a3c6c Modified Files -- doc/src/sgml/mvcc.sgml| 12 +- doc/src/sgml/ref/merge.sgml | 100 +-- src/backend/executor/execMain.c | 6 +- src/backend/executor/execPartition.c | 21 +- src/backend/executor/nodeModifyTable.c| 387 -- src/backend/nodes/nodeFuncs.c | 3 + src/backend/optimizer/plan/createplan.c | 8 +- src/backend/optimizer/plan/planner.c | 22 ++ src/backend/optimizer/plan/setrefs.c | 20 +- src/backend/optimizer/prep/prepjointree.c | 51 +++- src/backend/optimizer/prep/preptlist.c| 26 +- src/backend/optimizer/util/pathnode.c | 5 +- src/backend/parser/gram.y | 62 +++-- src/backend/parser/parse_merge.c | 56 ++-- src/backend/utils/adt/ruleutils.c | 41 ++- src/bin/psql/tab-complete.c | 30 +- src/include/catalog/catversion.h | 2 +- src/include/nodes/execnodes.h | 15 +- src/include/nodes/parsenodes.h| 7 +- src/include/nodes/pathnodes.h | 2 + src/include/nodes/plannodes.h | 2 + src/include/nodes/primnodes.h | 10 +- src/include/optimizer/pathnode.h | 3 +- src/include/parser/kwlist.h | 2 + src/test/isolation/expected/merge-update.out | 88 -- src/test/isolation/specs/merge-update.spec| 10 +- src/test/regress/expected/merge.out | 299 +++- src/test/regress/expected/rules.out | 32 +++ src/test/regress/expected/updatable_views.out | 90 ++ src/test/regress/sql/merge.sql| 122 ++-- src/test/regress/sql/rules.sql| 19 ++ src/test/regress/sql/updatable_views.sql | 32 +++ src/tools/pgindent/typedefs.list | 1 + 33 files changed, 1226 insertions(+), 360 deletions(-)
pgsql: Add functions to generate random numbers in a specified range.
Add functions to generate random numbers in a specified range. This adds 3 new variants of the random() function: random(min integer, max integer) returns integer random(min bigint, max bigint) returns bigint random(min numeric, max numeric) returns numeric Each returns a random number x in the range min <= x <= max. For the numeric function, the number of digits after the decimal point is equal to the number of digits that "min" or "max" has after the decimal point, whichever has more. The main entry points for these functions are in a new C source file. The existing random(), random_normal(), and setseed() functions are moved there too, so that they can all share the same PRNG state, which is kept private to that file. Dean Rasheed, reviewed by Jian He, David Zhang, Aleksander Alekseev, and Tomas Vondra. Discussion: https://postgr.es/m/caezatcv89vxuq93xqdmc0t-0y2zeenqtdsjbmv7dyfbpykb...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/e6341323a8da64b18e9af3e75a4578230702d61c Modified Files -- doc/src/sgml/func.sgml| 43 +++- src/backend/utils/adt/Makefile| 1 + src/backend/utils/adt/float.c | 95 src/backend/utils/adt/meson.build | 1 + src/backend/utils/adt/numeric.c | 219 ++ src/backend/utils/adt/pseudorandomfuncs.c | 185 +++ src/common/pg_prng.c | 36 +++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 12 + src/include/common/pg_prng.h | 1 + src/include/utils/numeric.h | 4 + src/test/regress/expected/random.out | 360 ++ src/test/regress/sql/random.sql | 164 ++ 13 files changed, 1022 insertions(+), 101 deletions(-)
pgsql: Add "--exclude-extension" to pg_dump's options.
Add "--exclude-extension" to pg_dump's options. This option (or equivalently specifying "exclude extension pattern" in a filter file) allows extensions matching the specified pattern to be excluded from the dump. Ayush Vatsa, reviewed by Junwang Zhao, Dean Rasheed, and Daniel Gustafsson. Discussion: https://postgr.es/m/CACX+KaP=VgVy9h-EUh598DTu+-fNr1jyEmpghC8rRp9s=w3...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/522ed12f7c600243870b13d9ff59f8fd5af10978 Modified Files -- doc/src/sgml/ref/pg_dump.sgml | 34 +-- src/bin/pg_dump/pg_dump.c | 33 ++- src/test/modules/test_pg_dump/t/001_base.pl | 88 ++--- 3 files changed, 139 insertions(+), 16 deletions(-)
pgsql: Fix PDF doc generation.
Fix PDF doc generation. Commit c649fa24a4 broke PDF generation, due to a misplaced id attribute. Per buildfarm member crake. Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/7eb9a8201890f3b208fd4c109a5b08bf139b692a Modified Files -- doc/src/sgml/func.sgml | 4 ++-- doc/src/sgml/ref/merge.sgml | 17 + 2 files changed, 11 insertions(+), 10 deletions(-)
pgsql: Add RETURNING support to MERGE.
Add RETURNING support to MERGE. This allows a RETURNING clause to be appended to a MERGE query, to return values based on each row inserted, updated, or deleted. As with plain INSERT, UPDATE, and DELETE commands, the returned values are based on the new contents of the target table for INSERT and UPDATE actions, and on its old contents for DELETE actions. Values from the source relation may also be returned. As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be used as the source relation for other operations such as WITH queries and COPY commands. Additionally, a special function merge_action() is provided, which returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action executed for each row. The merge_action() function can be used anywhere in the RETURNING list, including in arbitrary expressions and subqueries, but it is an error to use it anywhere outside of a MERGE query's RETURNING list. Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera, Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut, and Wolfgang Walther. Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c649fa24a42ba89bf5460c7110e4fc8eeca65959 Modified Files -- doc/src/sgml/dml.sgml | 22 ++- doc/src/sgml/func.sgml| 79 doc/src/sgml/glossary.sgml| 6 +- doc/src/sgml/plpgsql.sgml | 16 +- doc/src/sgml/queries.sgml | 9 +- doc/src/sgml/ref/copy.sgml| 19 +- doc/src/sgml/ref/merge.sgml | 68 +-- doc/src/sgml/ref/select.sgml | 11 +- doc/src/sgml/rowtypes.sgml| 2 +- doc/src/sgml/spi.sgml | 14 +- doc/src/sgml/xfunc.sgml | 8 +- src/backend/commands/copy.c | 6 - src/backend/commands/copyto.c | 3 +- src/backend/executor/execExpr.c | 13 ++ src/backend/executor/execExprInterp.c | 48 + src/backend/executor/execPartition.c | 8 +- src/backend/executor/functions.c | 9 +- src/backend/executor/nodeModifyTable.c| 202 +-- src/backend/executor/spi.c| 7 +- src/backend/jit/llvm/llvmjit_expr.c | 6 + src/backend/jit/llvm/llvmjit_types.c | 1 + src/backend/nodes/nodeFuncs.c | 17 ++ src/backend/optimizer/plan/subselect.c| 9 +- src/backend/optimizer/util/paramassign.c | 51 + src/backend/parser/analyze.c | 19 +- src/backend/parser/gram.y | 14 +- src/backend/parser/parse_agg.c| 2 + src/backend/parser/parse_cte.c| 10 +- src/backend/parser/parse_expr.c | 34 src/backend/parser/parse_func.c | 1 + src/backend/parser/parse_merge.c | 7 +- src/backend/parser/parse_relation.c | 7 +- src/backend/parser/parse_target.c | 4 + src/backend/rewrite/rewriteHandler.c | 9 +- src/backend/rewrite/rowsecurity.c | 28 ++- src/backend/tcop/utility.c| 3 +- src/backend/utils/adt/ruleutils.c | 14 +- src/bin/psql/common.c | 8 +- src/include/catalog/catversion.h | 2 +- src/include/executor/execExpr.h | 3 + src/include/executor/spi.h| 1 + src/include/nodes/execnodes.h | 3 + src/include/nodes/parsenodes.h| 1 + src/include/nodes/primnodes.h | 21 ++ src/include/optimizer/paramassign.h | 2 + src/include/parser/analyze.h | 2 + src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 3 +- src/pl/plpgsql/src/pl_exec.c | 12 +- src/pl/tcl/pltcl.c| 1 + src/test/regress/expected/merge.out | 266 -- src/test/regress/expected/rowsecurity.out | 32 +++- src/test/regress/expected/rules.out | 16 +- src/test/regress/expected/updatable_views.out | 30 ++- src/test/regress/expected/with.out| 10 + src/test/regress/sql/merge.sql| 169 ++-- src/test/regress/sql/rowsecurity.sql | 21 ++ src/test/regress/sql/rules.sql| 6 +- src/test/regress/sql/updatable_views.sql | 9 +- src/test/regress/sql/with.sql | 8 + src/tools/pgindent/typedefs.list | 1 + 61 files changed, 1198 insertions(+), 216 deletions(-)
pgsql: Fix EXPLAIN output for subplans in MERGE.
Fix EXPLAIN output for subplans in MERGE. Given a subplan in a MERGE query, EXPLAIN would sometimes fail to properly display expressions involving Params referencing variables in other parts of the plan tree. This would affect subplans outside the topmost join plan node, for which expansion of Params would go via the top-level ModifyTable plan node. The problem was that "inner_tlist" for the ModifyTable node's deparse_namespace was set to the join node's targetlist, but "inner_plan" was set to the ModifyTable node itself, rather than the join node, leading to incorrect results when descending to the referenced variable. Fix and backpatch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWAv-sZuH%2BwG5xJ-%2BGt7qGNGX8wUQd3XYydMFDKgRB9nw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/33e729c5148c3a697abc552621b34bdc5fd497ed Modified Files -- src/backend/utils/adt/ruleutils.c | 21 +--- src/test/regress/expected/merge.out | 50 + src/test/regress/sql/merge.sql | 17 + 3 files changed, 79 insertions(+), 9 deletions(-)
pgsql: Fix EXPLAIN output for subplans in MERGE.
Fix EXPLAIN output for subplans in MERGE. Given a subplan in a MERGE query, EXPLAIN would sometimes fail to properly display expressions involving Params referencing variables in other parts of the plan tree. This would affect subplans outside the topmost join plan node, for which expansion of Params would go via the top-level ModifyTable plan node. The problem was that "inner_tlist" for the ModifyTable node's deparse_namespace was set to the join node's targetlist, but "inner_plan" was set to the ModifyTable node itself, rather than the join node, leading to incorrect results when descending to the referenced variable. Fix and backpatch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWAv-sZuH%2BwG5xJ-%2BGt7qGNGX8wUQd3XYydMFDKgRB9nw%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/34c854b93fbec6d7b6dce4ee48c5b7459364a124 Modified Files -- src/backend/utils/adt/ruleutils.c | 21 +--- src/test/regress/expected/merge.out | 50 + src/test/regress/sql/merge.sql | 17 + 3 files changed, 79 insertions(+), 9 deletions(-)
pgsql: Fix EXPLAIN output for subplans in MERGE.
Fix EXPLAIN output for subplans in MERGE. Given a subplan in a MERGE query, EXPLAIN would sometimes fail to properly display expressions involving Params referencing variables in other parts of the plan tree. This would affect subplans outside the topmost join plan node, for which expansion of Params would go via the top-level ModifyTable plan node. The problem was that "inner_tlist" for the ModifyTable node's deparse_namespace was set to the join node's targetlist, but "inner_plan" was set to the ModifyTable node itself, rather than the join node, leading to incorrect results when descending to the referenced variable. Fix and backpatch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWAv-sZuH%2BwG5xJ-%2BGt7qGNGX8wUQd3XYydMFDKgRB9nw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/89ee14a2f22b2353a6cf9d193c54504b4d3131f5 Modified Files -- src/backend/utils/adt/ruleutils.c | 21 +--- src/test/regress/expected/merge.out | 50 + src/test/regress/sql/merge.sql | 17 + 3 files changed, 79 insertions(+), 9 deletions(-)
pgsql: doc: Improve a couple of places in the MERGE docs.
doc: Improve a couple of places in the MERGE docs. In the synopsis, make the syntax for merge_update consistent with the syntax for a plain UPDATE command. It was missing the optional "ROW" keyword that can be used in a multi-column assignment, and the option to assign from a multi-column subquery, both of which have been supported by MERGE since it was introduced. In the parameters section for the with_query parameter, mention that WITH RECURSIVE isn't supported, since this is different from plain INSERT, UPDATE, and DELETE commands. While at it, move that entry to the top of the list, for consistency with the other pages. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWoQyWkMFfu7JXXQr8dA6%3DgxjhYzgpuBP2oz0QoJTxGWw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/a875743ff40226f4096140efc506bd909da2a21f Modified Files -- doc/src/sgml/ref/merge.sgml | 36 1 file changed, 28 insertions(+), 8 deletions(-)
pgsql: doc: Improve a couple of places in the MERGE docs.
doc: Improve a couple of places in the MERGE docs. In the synopsis, make the syntax for merge_update consistent with the syntax for a plain UPDATE command. It was missing the optional "ROW" keyword that can be used in a multi-column assignment, and the option to assign from a multi-column subquery, both of which have been supported by MERGE since it was introduced. In the parameters section for the with_query parameter, mention that WITH RECURSIVE isn't supported, since this is different from plain INSERT, UPDATE, and DELETE commands. While at it, move that entry to the top of the list, for consistency with the other pages. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWoQyWkMFfu7JXXQr8dA6%3DgxjhYzgpuBP2oz0QoJTxGWw%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/d4c573d8e81ed28ee335e9cb2115098b99d38e71 Modified Files -- doc/src/sgml/ref/merge.sgml | 36 1 file changed, 28 insertions(+), 8 deletions(-)
pgsql: doc: Improve a couple of places in the MERGE docs.
doc: Improve a couple of places in the MERGE docs. In the synopsis, make the syntax for merge_update consistent with the syntax for a plain UPDATE command. It was missing the optional "ROW" keyword that can be used in a multi-column assignment, and the option to assign from a multi-column subquery, both of which have been supported by MERGE since it was introduced. In the parameters section for the with_query parameter, mention that WITH RECURSIVE isn't supported, since this is different from plain INSERT, UPDATE, and DELETE commands. While at it, move that entry to the top of the list, for consistency with the other pages. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWoQyWkMFfu7JXXQr8dA6%3DgxjhYzgpuBP2oz0QoJTxGWw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/97d4262683acb586778af2b2a3721fa7cdc402f1 Modified Files -- doc/src/sgml/ref/merge.sgml | 36 1 file changed, 28 insertions(+), 8 deletions(-)
pgsql: Fix handling of self-modified tuples in MERGE.
Fix handling of self-modified tuples in MERGE. When an UPDATE or DELETE action in MERGE returns TM_SelfModified, there are 2 possible causes: 1). The target tuple was already updated or deleted by the current command. This can happen if the target row joins to more than one source row, and the SQL standard explicitly says that this must be an error. 2). The target tuple was already updated or deleted by a later command in the current transaction. This can happen if the tuple is modified by a BEFORE trigger or a volatile function used in the query, and should be an error for the same reason that it is in a plain UPDATE or DELETE command. In MERGE's primary error handling block, it failed to check for (2), causing it to return a misleading error message in such cases. In the secondary error handling block, following a concurrent update from another session, it failed to check for (1), causing it to silently ignore target rows joined to more than one source row, instead of reporting an error. Fix this, and add tests for both of these cases. Per report from Wenjiang Zhang. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/tencent_41DE0FF443FE14B94A5898D373792109E408%40qq.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/b5c645d2a2652070ac128a42cf0d7b34403a6cfe Modified Files -- src/backend/executor/nodeModifyTable.c | 46 +++- src/test/isolation/expected/merge-update.out | 43 ++ src/test/isolation/specs/merge-update.spec | 13 src/test/regress/expected/triggers.out | 8 + src/test/regress/sql/triggers.sql| 4 +++ 5 files changed, 106 insertions(+), 8 deletions(-)
pgsql: Fix handling of self-modified tuples in MERGE.
Fix handling of self-modified tuples in MERGE. When an UPDATE or DELETE action in MERGE returns TM_SelfModified, there are 2 possible causes: 1). The target tuple was already updated or deleted by the current command. This can happen if the target row joins to more than one source row, and the SQL standard explicitly says that this must be an error. 2). The target tuple was already updated or deleted by a later command in the current transaction. This can happen if the tuple is modified by a BEFORE trigger or a volatile function used in the query, and should be an error for the same reason that it is in a plain UPDATE or DELETE command. In MERGE's primary error handling block, it failed to check for (2), causing it to return a misleading error message in such cases. In the secondary error handling block, following a concurrent update from another session, it failed to check for (1), causing it to silently ignore target rows joined to more than one source row, instead of reporting an error. Fix this, and add tests for both of these cases. Per report from Wenjiang Zhang. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/tencent_41DE0FF443FE14B94A5898D373792109E408%40qq.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/dd73d10adf0048d2d8dc4e94cd64ce5e5ff45a4b Modified Files -- src/backend/executor/nodeModifyTable.c | 46 +++- src/test/isolation/expected/merge-update.out | 43 ++ src/test/isolation/specs/merge-update.spec | 13 src/test/regress/expected/triggers.out | 8 + src/test/regress/sql/triggers.sql| 4 +++ 5 files changed, 106 insertions(+), 8 deletions(-)
pgsql: Fix handling of self-modified tuples in MERGE.
Fix handling of self-modified tuples in MERGE. When an UPDATE or DELETE action in MERGE returns TM_SelfModified, there are 2 possible causes: 1). The target tuple was already updated or deleted by the current command. This can happen if the target row joins to more than one source row, and the SQL standard explicitly says that this must be an error. 2). The target tuple was already updated or deleted by a later command in the current transaction. This can happen if the tuple is modified by a BEFORE trigger or a volatile function used in the query, and should be an error for the same reason that it is in a plain UPDATE or DELETE command. In MERGE's primary error handling block, it failed to check for (2), causing it to return a misleading error message in such cases. In the secondary error handling block, following a concurrent update from another session, it failed to check for (1), causing it to silently ignore target rows joined to more than one source row, instead of reporting an error. Fix this, and add tests for both of these cases. Per report from Wenjiang Zhang. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/tencent_41DE0FF443FE14B94A5898D373792109E408%40qq.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/29ef1dd19b4f3eb54569b2eece4a8a65034a2216 Modified Files -- src/backend/executor/nodeModifyTable.c | 46 +++- src/test/isolation/expected/merge-update.out | 43 ++ src/test/isolation/specs/merge-update.spec | 13 src/test/regress/expected/triggers.out | 8 + src/test/regress/sql/triggers.sql| 4 +++ 5 files changed, 106 insertions(+), 8 deletions(-)
pgsql: Fix doc omission for MERGE into updatable views.
Fix doc omission for MERGE into updatable views. Commit 5f2e179bd3 missed one place in rules.sgml that should have mentioned MERGE. Also, be more specific when saying that MERGE doesn't support rules, since it does support SELECT rules. Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/8545b28679a2ec2aa66ad2ec81f17019dab5d780 Modified Files -- doc/src/sgml/rules.sgml | 6 -- 1 file changed, 4 insertions(+), 2 deletions(-)
pgsql: Support MERGE into updatable views.
Support MERGE into updatable views. This allows the target relation of MERGE to be an auto-updatable or trigger-updatable view, and includes support for WITH CHECK OPTION, security barrier views, and security invoker views. A trigger-updatable view must have INSTEAD OF triggers for every type of action (INSERT, UPDATE, and DELETE) mentioned in the MERGE command. An auto-updatable view must not have any INSTEAD OF triggers. Mixing auto-update and trigger-update actions (i.e., having a partial set of INSTEAD OF triggers) is not supported. Rule-updatable views are also not supported, since there is no rewriter support for non-SELECT rules with MERGE operations. Dean Rasheed, reviewed by Jian He and Alvaro Herrera. Discussion: https://postgr.es/m/CAEZATCVcB1g0nmxuEc-A+gGB0HnfcGQNGYH7gS=7rq0u0zo...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/5f2e179bd31e5f5803005101eb12a8d7bf8db8f3 Modified Files -- doc/src/sgml/ref/create_view.sgml | 42 +- doc/src/sgml/ref/merge.sgml | 22 +- doc/src/sgml/rules.sgml | 40 +- src/backend/commands/copyfrom.c | 2 +- src/backend/executor/execMain.c | 51 +-- src/backend/executor/execPartition.c | 4 +- src/backend/executor/nodeModifyTable.c| 138 +-- src/backend/optimizer/prep/prepjointree.c | 20 +- src/backend/optimizer/util/appendinfo.c | 3 +- src/backend/parser/parse_merge.c | 21 +- src/backend/rewrite/rewriteHandler.c | 403 +-- src/backend/rewrite/rewriteManip.c| 20 +- src/bin/psql/tab-complete.c | 1 + src/include/catalog/catversion.h | 2 +- src/include/executor/executor.h | 3 +- src/include/nodes/parsenodes.h| 8 + src/include/rewrite/rewriteHandler.h | 6 + src/test/regress/expected/merge.out | 10 - src/test/regress/expected/rules.out | 12 + src/test/regress/expected/updatable_views.out | 553 +- src/test/regress/sql/merge.sql| 9 - src/test/regress/sql/rules.sql| 13 + src/test/regress/sql/updatable_views.sql | 285 - 23 files changed, 1380 insertions(+), 288 deletions(-)
pgsql: Remove field UpdateContext->updated in nodeModifyTable.c
Remove field UpdateContext->updated in nodeModifyTable.c This field has been redundant ever since it was added by commit 25e777cf8e, which split up ExecUpdate() and ExecDelete() into reusable pieces. The only place that reads it is ExecMergeMatched(), if the result from ExecUpdateAct() is TM_Ok. However, all paths through ExecUpdateAct() that return TM_Ok also set this field to true, so the return status by itself is sufficient to tell if the update happened. Removing this field is a modest simplification, and it brings the UPDATE path in ExecMergeMatched() more into line with ExecUpdate(), ensuring that ExecUpdateEpilogue() is always called if ExecUpdateAct() returns TM_Ok, reducing the chance of bugs. Dean Rasheed, reviewed by Alvaro Herrera. Discussion: https://postgr.es/m/CAEZATCWGGmigGBzLHkJm5Ccv2mMxXmwi3%2Buq0yhwDHm-tsvSLg%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/362de947cd7e8c826d9b3c5dc2590348263ed3c1 Modified Files -- src/backend/executor/nodeModifyTable.c | 6 +- 1 file changed, 1 insertion(+), 5 deletions(-)
pgsql: Fix BEFORE ROW trigger handling in cross-partition MERGE update.
Fix BEFORE ROW trigger handling in cross-partition MERGE update. Fix a bug during MERGE if a cross-partition update is attempted on a partitioned table with a BEFORE DELETE ROW trigger that returns NULL, to prevent the update. This would cause an error to be thrown, or an assert failure in an assert-enabled build. This was an oversight in 9321c79c86, which failed to properly distinguish a DELETE prevented by a trigger from one prevented by a concurrent update. Fix by having ExecDelete() return the TM_Result status to ExecCrossPartitionUpdate(), so that it can distinguish the two cases, and make ExecCrossPartitionUpdate() return the TM_Result status to ExecUpdateAct(), so that it can return the correct status from a concurrent update. In addition, ensure that the command tag is correctly updated by having ExecMergeMatched() pass canSetTag to ExecUpdateAct(), rather than passing false, so that it updates the command tag if it does a cross-partition update, making this code path in ExecMergeMatched() consistent with ExecUpdate(). Per bug #18238 from Alexander Lakhin. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo and Jian He. Discussion: https://postgr.es/m/18238-2f2bdc7f720180b9%40postgresql.org Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/7f07384dc2697abb405e47a7447c6edf7e07d5f9 Modified Files -- src/backend/executor/nodeModifyTable.c | 20 +--- src/test/regress/expected/merge.out| 91 ++ src/test/regress/sql/merge.sql | 64 3 files changed, 167 insertions(+), 8 deletions(-)
pgsql: Fix BEFORE ROW trigger handling in cross-partition MERGE update.
Fix BEFORE ROW trigger handling in cross-partition MERGE update. Fix a bug during MERGE if a cross-partition update is attempted on a partitioned table with a BEFORE DELETE ROW trigger that returns NULL, to prevent the update. This would cause an error to be thrown, or an assert failure in an assert-enabled build. This was an oversight in 9321c79c86, which failed to properly distinguish a DELETE prevented by a trigger from one prevented by a concurrent update. Fix by having ExecDelete() return the TM_Result status to ExecCrossPartitionUpdate(), so that it can distinguish the two cases, and make ExecCrossPartitionUpdate() return the TM_Result status to ExecUpdateAct(), so that it can return the correct status from a concurrent update. In addition, ensure that the command tag is correctly updated by having ExecMergeMatched() pass canSetTag to ExecUpdateAct(), rather than passing false, so that it updates the command tag if it does a cross-partition update, making this code path in ExecMergeMatched() consistent with ExecUpdate(). Per bug #18238 from Alexander Lakhin. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo and Jian He. Discussion: https://postgr.es/m/18238-2f2bdc7f720180b9%40postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/7e8c6d7af6588b8a7249cc3e1eb195cc32d120a2 Modified Files -- src/backend/executor/nodeModifyTable.c | 20 +--- src/test/regress/expected/merge.out| 91 ++ src/test/regress/sql/merge.sql | 64 3 files changed, 167 insertions(+), 8 deletions(-)
pgsql: Fix BEFORE ROW trigger handling in cross-partition MERGE update.
Fix BEFORE ROW trigger handling in cross-partition MERGE update. Fix a bug during MERGE if a cross-partition update is attempted on a partitioned table with a BEFORE DELETE ROW trigger that returns NULL, to prevent the update. This would cause an error to be thrown, or an assert failure in an assert-enabled build. This was an oversight in 9321c79c86, which failed to properly distinguish a DELETE prevented by a trigger from one prevented by a concurrent update. Fix by having ExecDelete() return the TM_Result status to ExecCrossPartitionUpdate(), so that it can distinguish the two cases, and make ExecCrossPartitionUpdate() return the TM_Result status to ExecUpdateAct(), so that it can return the correct status from a concurrent update. In addition, ensure that the command tag is correctly updated by having ExecMergeMatched() pass canSetTag to ExecUpdateAct(), rather than passing false, so that it updates the command tag if it does a cross-partition update, making this code path in ExecMergeMatched() consistent with ExecUpdate(). Per bug #18238 from Alexander Lakhin. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo and Jian He. Discussion: https://postgr.es/m/18238-2f2bdc7f720180b9%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/a0ff37173d7d412d53bc299fc611068be3bc7060 Modified Files -- src/backend/executor/nodeModifyTable.c | 20 +--- src/test/regress/expected/merge.out| 91 ++ src/test/regress/sql/merge.sql | 64 3 files changed, 167 insertions(+), 8 deletions(-)
pgsql: psql: Add tab completion for view options.
psql: Add tab completion for view options. Add support for tab completion of WITH (...) options to CREATE VIEW, and for the corresponding SET/RESET (...) options in ALTER VIEW. Christoph Heiss, reviewed by Melih Mutlu, Vignesh C, Jim Jones, Mikhail Gribkov, David Zhang, Shubham Khanna, and me. Discussion: https://postgr.es/m/a2075c5a-66f9-a564-f038-9ac044b03...@c8h4.io Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/cd342474890f31a7364c0d1161334546822b639c Modified Files -- src/bin/psql/tab-complete.c | 50 + 1 file changed, 46 insertions(+), 4 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f499d2b20b42c34a3941ca284ed58b95c0ce330c Modified Files -- src/backend/utils/adt/timestamp.c | 70 -- src/test/regress/expected/interval.out | 13 +++ src/test/regress/sql/interval.sql | 8 3 files changed, 70 insertions(+), 21 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/2ffcebdba4cfa70fb1b6049ce85343e569304049 Modified Files -- src/backend/utils/adt/timestamp.c | 69 +++--- src/test/regress/expected/interval.out | 13 +++ src/test/regress/sql/interval.sql | 8 3 files changed, 69 insertions(+), 21 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/428770aadca974dd08d4dd002567edd79cd0e940 Modified Files -- src/backend/utils/adt/timestamp.c | 70 -- src/test/regress/expected/interval.out | 13 +++ src/test/regress/sql/interval.sql | 8 3 files changed, 70 insertions(+), 21 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/b218fbb7a35fcf31539bfad12732038fe082a2eb Modified Files -- src/backend/utils/adt/timestamp.c | 103 +++-- src/test/regress/expected/interval.out | 13 + src/test/regress/sql/interval.sql | 8 +++ 3 files changed, 80 insertions(+), 44 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/2851aa7d1fc3bd3dfac00ff2250a7e029ed6499f Modified Files -- src/backend/utils/adt/timestamp.c | 69 +++--- src/test/regress/expected/interval.out | 13 +++ src/test/regress/sql/interval.sql | 8 3 files changed, 69 insertions(+), 21 deletions(-)
pgsql: Guard against overflow in interval_mul() and interval_div().
Guard against overflow in interval_mul() and interval_div(). Commits 146604ec43 and a898b409f6 added overflow checks to interval_mul(), but not to interval_div(), which contains almost identical code, and so is susceptible to the same kinds of overflows. In addition, those checks did not catch all possible overflow conditions. Add additional checks to the "cascade down" code in interval_mul(), and copy all the overflow checks over to the corresponding code in interval_div(), so that they both generate "interval out of range" errors, rather than returning bogus results. Given that these errors are relatively easy to hit, back-patch to all supported branches. Per bug #18200 from Alexander Lakhin, and subsequent investigation. Discussion: https://postgr.es/m/18200-5ea288c7b2d504b1%40postgresql.org Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/72d0c135bd7a9ab4602ea1bece0054bb1e8d372d Modified Files -- src/backend/utils/adt/timestamp.c | 69 +++--- src/test/regress/expected/interval.out | 13 +++ src/test/regress/sql/interval.sql | 8 3 files changed, 69 insertions(+), 21 deletions(-)
pgsql: doc: improve description of privileges for MERGE and update glos
doc: improve description of privileges for MERGE and update glossary. On the MERGE page, the description of the privileges required could be taken to imply that the SELECT privilege is required on all columns of the data source, whereas actually it is only required on the columns referred to by conditions or expressions in the MERGE command. Re-word it to make that a little clearer, and mention expressions as well as conditions. Also, add a glossary entry for MERGE, and nearby on the glossary page, mention MERGE in the list of commands that cannot update a materialized view. Noted by Jian He. Patch by me, reviewed by Jian He. Discussion: https://postgr.es/m/CACJufxHuSoRXKwr0MtSFLXuT2nFVWcVfEWhxg7qdP9h%2Bs3a%2BUw%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/3b6728910ace14c64ffa43b538a84508f4fe6408 Modified Files -- doc/src/sgml/glossary.sgml | 21 +++-- doc/src/sgml/ref/merge.sgml | 10 +- 2 files changed, 24 insertions(+), 7 deletions(-)
pgsql: doc: improve description of privileges for MERGE and update glos
doc: improve description of privileges for MERGE and update glossary. On the MERGE page, the description of the privileges required could be taken to imply that the SELECT privilege is required on all columns of the data source, whereas actually it is only required on the columns referred to by conditions or expressions in the MERGE command. Re-word it to make that a little clearer, and mention expressions as well as conditions. Also, add a glossary entry for MERGE, and nearby on the glossary page, mention MERGE in the list of commands that cannot update a materialized view. Noted by Jian He. Patch by me, reviewed by Jian He. Discussion: https://postgr.es/m/CACJufxHuSoRXKwr0MtSFLXuT2nFVWcVfEWhxg7qdP9h%2Bs3a%2BUw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/4bc8f29088f8a2e9c89e6c0819337c4c8bf5f20a Modified Files -- doc/src/sgml/glossary.sgml | 21 +++-- doc/src/sgml/ref/merge.sgml | 10 +- 2 files changed, 24 insertions(+), 7 deletions(-)
pgsql: doc: improve description of privileges for MERGE and update glos
doc: improve description of privileges for MERGE and update glossary. On the MERGE page, the description of the privileges required could be taken to imply that the SELECT privilege is required on all columns of the data source, whereas actually it is only required on the columns referred to by conditions or expressions in the MERGE command. Re-word it to make that a little clearer, and mention expressions as well as conditions. Also, add a glossary entry for MERGE, and nearby on the glossary page, mention MERGE in the list of commands that cannot update a materialized view. Noted by Jian He. Patch by me, reviewed by Jian He. Discussion: https://postgr.es/m/CACJufxHuSoRXKwr0MtSFLXuT2nFVWcVfEWhxg7qdP9h%2Bs3a%2BUw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/ff772853d02e274684567620fba56dd1f10f2489 Modified Files -- doc/src/sgml/glossary.sgml | 21 +++-- doc/src/sgml/ref/merge.sgml | 10 +- 2 files changed, 24 insertions(+), 7 deletions(-)
pgsql: Support +/- infinity in the interval data type.
Support +/- infinity in the interval data type. This adds support for infinity to the interval data type, using the same input/output representation as the other date/time data types that support infinity. This allows various arithmetic operations on infinite dates, timestamps and intervals. The new values are represented by setting all fields of the interval to INT32/64_MIN for -infinity, and INT32/64_MAX for +infinity. This ensures that they compare as less/greater than all other interval values, without the need for any special-case comparison code. Note that, since those 2 values were formerly accepted as legal finite intervals, pg_upgrade and dump/restore from an old database will turn them from finite to infinite intervals. That seems OK, since those exact values should be extremely rare in practice, and they are outside the documented range supported by the interval type, which gives us a certain amount of leeway. Bump catalog version. Joseph Koshakow, Jian He, and Ashutosh Bapat, reviewed by me. Discussion: https://postgr.es/m/CAAvxfHea4%2BsPybKK7agDYOMo9N-Z3J6ZXf3BOM79pFsFNcRjwA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/519fc1bd9e9d7b408903e44f55f83f6db30742b7 Modified Files -- contrib/btree_gin/btree_gin.c |5 +- doc/src/sgml/datatype.sgml|4 +- doc/src/sgml/func.sgml|8 +- src/backend/utils/adt/date.c | 57 +- src/backend/utils/adt/datetime.c | 26 + src/backend/utils/adt/formatting.c|2 +- src/backend/utils/adt/selfuncs.c |4 + src/backend/utils/adt/timestamp.c | 1040 +++-- src/include/catalog/catversion.h |2 +- src/include/catalog/pg_aggregate.dat | 24 +- src/include/catalog/pg_proc.dat | 28 +- src/include/datatype/timestamp.h | 27 +- src/test/regress/expected/brin_multi.out | 28 + src/test/regress/expected/horology.out| 71 +- src/test/regress/expected/interval.out| 499 +- src/test/regress/expected/timestamp.out | 62 ++ src/test/regress/expected/timestamptz.out | 62 ++ src/test/regress/expected/window.out | 469 - src/test/regress/sql/brin_multi.sql | 19 + src/test/regress/sql/horology.sql |3 +- src/test/regress/sql/interval.sql | 193 +- src/test/regress/sql/timestamp.sql| 19 + src/test/regress/sql/timestamptz.sql | 18 + src/test/regress/sql/window.sql | 167 - src/tools/pgindent/typedefs.list |1 + 25 files changed, 2541 insertions(+), 297 deletions(-)
pgsql: Avoid integer overflow hazard in interval_time().
Avoid integer overflow hazard in interval_time(). When casting an interval to a time, the original code suffered from 64-bit integer overflow for inputs with a sufficiently large negative "time" field, leading to bogus results. Fix by rewriting the algorithm in a simpler form, that more obviously cannot overflow. While at it, improve the test coverage to include negative interval inputs. Discussion: https://postgr.es/m/CAEZATCXoUKHkcuq4q63hkiPsKZJd0kZWzgKtU%2BNT0aU4wbf_Pw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/3850d4dec1d91c4fdce274f42986840444d5593e Modified Files -- src/backend/utils/adt/date.c | 15 +++ src/test/regress/expected/horology.out | 12 src/test/regress/sql/horology.sql | 2 ++ 3 files changed, 17 insertions(+), 12 deletions(-)
pgsql: Fix AFTER ROW trigger execution in MERGE cross-partition update.
Fix AFTER ROW trigger execution in MERGE cross-partition update. When executing a MERGE UPDATE action, if the UPDATE is turned into a cross-partition DELETE then INSERT, do not attempt to invoke AFTER UPDATE ROW triggers, or any of the other post-update actions in ExecUpdateEpilogue(). For consistency with a plain UPDATE command, such triggers should not be fired (and typically fail anyway), and similarly, other post-update actions, such as WCO/RLS checks should not be executed, and might also lead to unexpected failures. Therefore, as with ExecUpdate(), make ExecMergeMatched() return immediately if ExecUpdateAct() reports that a cross-partition update was done, to be sure that no further processing is done for that tuple. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWjBgagyNZs02vgDF0DvASYj-iHTFtXG2-nP3orZhmtcw%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/06a546382aafe0cc9d895b2861c5b8a8e9f0b002 Modified Files -- src/backend/executor/nodeModifyTable.c | 16 src/test/regress/expected/triggers.out | 45 ++ src/test/regress/sql/triggers.sql | 36 +++ 3 files changed, 97 insertions(+)
pgsql: Fix AFTER ROW trigger execution in MERGE cross-partition update.
Fix AFTER ROW trigger execution in MERGE cross-partition update. When executing a MERGE UPDATE action, if the UPDATE is turned into a cross-partition DELETE then INSERT, do not attempt to invoke AFTER UPDATE ROW triggers, or any of the other post-update actions in ExecUpdateEpilogue(). For consistency with a plain UPDATE command, such triggers should not be fired (and typically fail anyway), and similarly, other post-update actions, such as WCO/RLS checks should not be executed, and might also lead to unexpected failures. Therefore, as with ExecUpdate(), make ExecMergeMatched() return immediately if ExecUpdateAct() reports that a cross-partition update was done, to be sure that no further processing is done for that tuple. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWjBgagyNZs02vgDF0DvASYj-iHTFtXG2-nP3orZhmtcw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/c0bfdaf2b773d9e0de343c768dfbb89c250ae439 Modified Files -- src/backend/executor/nodeModifyTable.c | 16 src/test/regress/expected/triggers.out | 45 ++ src/test/regress/sql/triggers.sql | 36 +++ 3 files changed, 97 insertions(+)
pgsql: Fix AFTER ROW trigger execution in MERGE cross-partition update.
Fix AFTER ROW trigger execution in MERGE cross-partition update. When executing a MERGE UPDATE action, if the UPDATE is turned into a cross-partition DELETE then INSERT, do not attempt to invoke AFTER UPDATE ROW triggers, or any of the other post-update actions in ExecUpdateEpilogue(). For consistency with a plain UPDATE command, such triggers should not be fired (and typically fail anyway), and similarly, other post-update actions, such as WCO/RLS checks should not be executed, and might also lead to unexpected failures. Therefore, as with ExecUpdate(), make ExecMergeMatched() return immediately if ExecUpdateAct() reports that a cross-partition update was done, to be sure that no further processing is done for that tuple. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWjBgagyNZs02vgDF0DvASYj-iHTFtXG2-nP3orZhmtcw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/a4f7d33a904fcd4da7a12d249416dd2c5c5f2c1c Modified Files -- src/backend/executor/nodeModifyTable.c | 16 src/test/regress/expected/triggers.out | 45 ++ src/test/regress/sql/triggers.sql | 36 +++ 3 files changed, 97 insertions(+)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0e3e8fbd3a8b1660b02a5bb032dc1f50e1d66b19 Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/308a69a9878212bc2f96279023ce16070dac9214 Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/b17a02be27e01d46cf9a74d173009547305d4cf8 Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/dea12b40d5210332453f4350e1653048b43fdb7a Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/4f4a0010a3a1e0404198130ac3cababeaa17d1e6 Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Fix corner-case 64-bit integer subtraction bug on some platforms
Fix corner-case 64-bit integer subtraction bug on some platforms. When computing "0 - INT64_MIN", most platforms would report an overflow error, which is correct. However, platforms without integer overflow builtins or 128-bit integers would fail to spot the overflow, and incorrectly return INT64_MIN. Back-patch to all supported branches. Patch be me. Thanks to Jian He for initial investigation, and Laurenz Albe and Tom Lane for review. Discussion: https://postgr.es/m/CAEZATCUNK-AZSD0jVdgkk0N%3DNcAXBWeAEX-QU9AnJPensikmdQ%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/c396aca2b712f7872dcc4641a2a0fa553f232f76 Modified Files -- src/include/common/int.h | 6 +- src/test/regress/expected/int8.out | 2 ++ src/test/regress/sql/int8.sql | 1 + 3 files changed, 8 insertions(+), 1 deletion(-)
pgsql: Guard against overflow in make_interval().
Guard against overflow in make_interval(). The original code did very little to guard against integer or floating point overflow when computing the interval's fields. Detect any such overflows and error out, rather than silently returning bogus results. Joseph Koshakow, reviewed by Ashutosh Bapat and me. Discussion: https://postgr.es/m/CAAvxfHcm1TPwH_zaGWuFoL8pZBestbRZTU6Z%3D-RvAdSXTPbKfg%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/b2d55447a563036579d6777f64a7483dceeab6ea Modified Files -- src/backend/utils/adt/timestamp.c | 39 -- src/include/datatype/timestamp.h | 1 + src/test/regress/expected/interval.out | 27 +++ src/test/regress/sql/interval.sql | 15 + 4 files changed, 71 insertions(+), 11 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/5f06918399f07d46c15756bea84d8d5665ebfa57 Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/162b38a0683960c115954694cd7be56dec5bfe33 Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/35e6a5c20d453921efa24342effb648cad3573ec Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f7cd7b265f6f96689a42d61f1e312554e1ff9fa8 Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/ab73a37e9e1e5308f4dbb81902125b484f9c5432 Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/849172ff4883d44168f96f39d3fde96d0aa34c99 Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: btree_gin: Fix calculation of leftmost interval value.
btree_gin: Fix calculation of leftmost interval value. Formerly, the value computed by leftmostvalue_interval() was a long way short of the minimum possible interval value. As a result, an index scan on a GIN index on an interval column with < or <= operators would miss large negative interval values. Fix by setting all fields of the leftmost interval to their minimum values, ensuring that the result is less than any other possible interval. Since this only affects index searches, no index rebuild is necessary. Back-patch to all supported branches. Dean Rasheed, reviewed by Heikki Linnakangas. Discussion: https://postgr.es/m/CAEZATCV80%2BgOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw%40mail.gmail.com Branch -- REL_11_STABLE Details --- https://git.postgresql.org/pg/commitdiff/c804ffb5622b5efe7bda78570aa1911073a7491d Modified Files -- contrib/btree_gin/btree_gin.c | 6 +++--- contrib/btree_gin/expected/interval.out | 16 +++- contrib/btree_gin/sql/interval.sql | 4 +++- 3 files changed, 17 insertions(+), 9 deletions(-)
pgsql: Fix EvalPlanQual rechecking during MERGE.
Fix EvalPlanQual rechecking during MERGE. Under some circumstances, concurrent MERGE operations could lead to inconsistent results, that varied according the plan chosen. This was caused by a lack of rowmarks on the source relation, which meant that EvalPlanQual rechecking was not guaranteed to return the same source tuples when re-running the join query. Fix by ensuring that preprocess_rowmarks() sets up PlanRowMarks for all non-target relations used in MERGE, in the same way that it does for UPDATE and DELETE. Per bug #18103. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo. Discussion: https://postgr.es/m/18103-c4386baab8e355e3%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/1d5caec221d85b8950a1ee3d8bb6fad262549ea6 Modified Files -- src/backend/executor/README| 12 +-- src/backend/executor/nodeModifyTable.c | 6 +- src/backend/optimizer/plan/planner.c | 5 +- src/include/nodes/execnodes.h | 4 +- src/include/nodes/plannodes.h | 4 +- src/test/isolation/expected/merge-join.out | 148 + src/test/isolation/isolation_schedule | 1 + src/test/isolation/specs/merge-join.spec | 45 + src/test/regress/expected/merge.out| 8 +- src/test/regress/expected/with.out | 42 10 files changed, 237 insertions(+), 38 deletions(-)
pgsql: Fix EvalPlanQual rechecking during MERGE.
Fix EvalPlanQual rechecking during MERGE. Under some circumstances, concurrent MERGE operations could lead to inconsistent results, that varied according the plan chosen. This was caused by a lack of rowmarks on the source relation, which meant that EvalPlanQual rechecking was not guaranteed to return the same source tuples when re-running the join query. Fix by ensuring that preprocess_rowmarks() sets up PlanRowMarks for all non-target relations used in MERGE, in the same way that it does for UPDATE and DELETE. Per bug #18103. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo. Discussion: https://postgr.es/m/18103-c4386baab8e355e3%40postgresql.org Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/6d2de076cbf078cd1d6d0df2136fe5b1da96d1fe Modified Files -- src/backend/executor/README| 12 +-- src/backend/executor/nodeModifyTable.c | 6 +- src/backend/optimizer/plan/planner.c | 5 +- src/include/nodes/execnodes.h | 4 +- src/include/nodes/plannodes.h | 4 +- src/test/isolation/expected/merge-join.out | 148 + src/test/isolation/isolation_schedule | 1 + src/test/isolation/specs/merge-join.spec | 45 + src/test/regress/expected/merge.out| 8 +- src/test/regress/expected/with.out | 42 10 files changed, 237 insertions(+), 38 deletions(-)
pgsql: Fix EvalPlanQual rechecking during MERGE.
Fix EvalPlanQual rechecking during MERGE. Under some circumstances, concurrent MERGE operations could lead to inconsistent results, that varied according the plan chosen. This was caused by a lack of rowmarks on the source relation, which meant that EvalPlanQual rechecking was not guaranteed to return the same source tuples when re-running the join query. Fix by ensuring that preprocess_rowmarks() sets up PlanRowMarks for all non-target relations used in MERGE, in the same way that it does for UPDATE and DELETE. Per bug #18103. Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Richard Guo. Discussion: https://postgr.es/m/18103-c4386baab8e355e3%40postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/3c1a1af91d9504e4ce0319f48e83f7c2d5765969 Modified Files -- src/backend/executor/README| 12 +-- src/backend/executor/nodeModifyTable.c | 6 +- src/backend/optimizer/plan/planner.c | 5 +- src/include/nodes/execnodes.h | 4 +- src/include/nodes/plannodes.h | 4 +- src/test/isolation/expected/merge-join.out | 148 + src/test/isolation/isolation_schedule | 1 + src/test/isolation/specs/merge-join.spec | 45 + src/test/regress/expected/merge.out| 8 +- src/test/regress/expected/with.out | 42 10 files changed, 237 insertions(+), 38 deletions(-)
pgsql: Fix RLS policy usage in MERGE.
Fix RLS policy usage in MERGE. If MERGE executes an UPDATE action on a table with row-level security, the code incorrectly applied the WITH CHECK clauses from the target table's INSERT policies to new rows, instead of the clauses from the table's UPDATE policies. In addition, it failed to check new rows against the target table's SELECT policies, if SELECT permissions were required (likely to always be the case). In addition, if MERGE executes a DO NOTHING action for matched rows, the code incorrectly applied the USING clauses from the target table's DELETE policies to existing target tuples. These policies were applied as checks that would throw an error, if they did not pass. Fix this, so that a MERGE UPDATE action applies the same RLS policies as a plain UPDATE query with a WHERE clause, and a DO NOTHING action does not apply any RLS checks (other than adding clauses from SELECT policies to the join). Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Stephen Frost. Security: CVE-2023-39418 Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/cb2ae5741f2458a474ed3c31458d242e678ff229 Modified Files -- src/backend/executor/nodeModifyTable.c| 7 +-- src/backend/rewrite/rowsecurity.c | 85 ++- src/test/regress/expected/rowsecurity.out | 58 - src/test/regress/sql/rowsecurity.sql | 52 +++ 4 files changed, 152 insertions(+), 50 deletions(-)
pgsql: Fix RLS policy usage in MERGE.
Fix RLS policy usage in MERGE. If MERGE executes an UPDATE action on a table with row-level security, the code incorrectly applied the WITH CHECK clauses from the target table's INSERT policies to new rows, instead of the clauses from the table's UPDATE policies. In addition, it failed to check new rows against the target table's SELECT policies, if SELECT permissions were required (likely to always be the case). In addition, if MERGE executes a DO NOTHING action for matched rows, the code incorrectly applied the USING clauses from the target table's DELETE policies to existing target tuples. These policies were applied as checks that would throw an error, if they did not pass. Fix this, so that a MERGE UPDATE action applies the same RLS policies as a plain UPDATE query with a WHERE clause, and a DO NOTHING action does not apply any RLS checks (other than adding clauses from SELECT policies to the join). Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Stephen Frost. Security: CVE-2023-39418 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c2e08b04c9e71ac6aabdc7d9b3f8e785e164d770 Modified Files -- src/backend/executor/nodeModifyTable.c| 7 +-- src/backend/rewrite/rowsecurity.c | 85 ++- src/test/regress/expected/rowsecurity.out | 58 - src/test/regress/sql/rowsecurity.sql | 52 +++ 4 files changed, 152 insertions(+), 50 deletions(-)
pgsql: Fix RLS policy usage in MERGE.
Fix RLS policy usage in MERGE. If MERGE executes an UPDATE action on a table with row-level security, the code incorrectly applied the WITH CHECK clauses from the target table's INSERT policies to new rows, instead of the clauses from the table's UPDATE policies. In addition, it failed to check new rows against the target table's SELECT policies, if SELECT permissions were required (likely to always be the case). In addition, if MERGE executes a DO NOTHING action for matched rows, the code incorrectly applied the USING clauses from the target table's DELETE policies to existing target tuples. These policies were applied as checks that would throw an error, if they did not pass. Fix this, so that a MERGE UPDATE action applies the same RLS policies as a plain UPDATE query with a WHERE clause, and a DO NOTHING action does not apply any RLS checks (other than adding clauses from SELECT policies to the join). Back-patch to v15, where MERGE was introduced. Dean Rasheed, reviewed by Stephen Frost. Security: CVE-2023-39418 Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/67a007dc0cdb8578726c2000a7abc513109cb4a2 Modified Files -- src/backend/executor/nodeModifyTable.c| 7 +-- src/backend/rewrite/rowsecurity.c | 85 ++- src/test/regress/expected/rowsecurity.out | 58 - src/test/regress/sql/rowsecurity.sql | 52 +++ 4 files changed, 152 insertions(+), 50 deletions(-)
pgsql: Add support for the error functions erf() and erfc().
Add support for the error functions erf() and erfc(). Expose the standard error functions as SQL-callable functions. These are expected to be useful to people working with normal distributions, and we use them here to test the distribution from random_normal(). Since these functions are defined in the POSIX and C99 standards, they should in theory be available on all supported platforms. If that turns out not to be the case, more work will be needed. On all platforms tested so far, using extra_float_digits = -1 in the regression tests is sufficient to allow for variations between implementations. However, past experience has shown that there are almost certainly going to be additional unexpected portability issues, so these tests may well need further adjustments, based on the buildfarm results. Dean Rasheed, reviewed by Nathan Bossart and Thomas Munro. Discussion: https://postgr.es/m/CAEZATCXv5fi7+Vu-POiyai+ucF95+YMcCMafxV+eZuN1B-=m...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/d5d574146d71c362852748c82ba8b9c873f5bf24 Modified Files -- doc/src/sgml/func.sgml | 35 +++ src/backend/utils/adt/float.c| 47 src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 7 ++ src/test/regress/expected/float8.out | 39 ++ src/test/regress/expected/random.out | 32 src/test/regress/sql/float8.sql | 14 +++ src/test/regress/sql/random.sql | 30 +++ 8 files changed, 205 insertions(+), 1 deletion(-)
pgsql: Fix MERGE command tag for actions blocked by BEFORE ROW triggers
Fix MERGE command tag for actions blocked by BEFORE ROW triggers. This ensures that the row count in the command tag for a MERGE is correctly computed in the case where UPDATEs or DELETEs are skipped due to a BEFORE ROW trigger returning NULL (the INSERT case was already handled correctly by ExecMergeNotMatched() calling ExecInsert()). Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCU8XEmR0JWKDtyb7iZ%3DqCffxS9uyJt0iOZ4TV4RT%2Bow1w%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/da6257eee35db5d281a115838abaf285b46b52f3 Modified Files -- src/backend/executor/nodeModifyTable.c | 10 ++ src/test/regress/expected/merge.out| 15 +++ src/test/regress/sql/merge.sql | 13 + 3 files changed, 34 insertions(+), 4 deletions(-)
pgsql: Fix MERGE command tag for actions blocked by BEFORE ROW triggers
Fix MERGE command tag for actions blocked by BEFORE ROW triggers. This ensures that the row count in the command tag for a MERGE is correctly computed in the case where UPDATEs or DELETEs are skipped due to a BEFORE ROW trigger returning NULL (the INSERT case was already handled correctly by ExecMergeNotMatched() calling ExecInsert()). Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCU8XEmR0JWKDtyb7iZ%3DqCffxS9uyJt0iOZ4TV4RT%2Bow1w%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/7b14e20b12cc8358cad9bdd05dd6b7de7f73c431 Modified Files -- src/backend/executor/nodeModifyTable.c | 10 ++ src/test/regress/expected/merge.out| 15 +++ src/test/regress/sql/merge.sql | 13 + 3 files changed, 34 insertions(+), 4 deletions(-)
pgsql: Fix concurrent update issues with MERGE.
Fix concurrent update issues with MERGE. If MERGE attempts an UPDATE or DELETE on a table with BEFORE ROW triggers, or a cross-partition UPDATE (with or without triggers), and a concurrent UPDATE or DELETE happens, the merge code would fail. In some cases this would lead to a crash, while in others it would cause the wrong merge action to be executed, or no action at all. The immediate cause of the crash was the trigger code calling ExecGetUpdateNewTuple() as part of the EPQ mechanism, which fails because during a merge ri_projectNew is NULL, since merge has its own per-action projection information, which ExecGetUpdateNewTuple() knows nothing about. Fix by arranging for the trigger code to exit early, returning the TM_Result and TM_FailureData information, if a concurrent modification is detected, allowing the merge code to do the necessary EPQ handling in its own way. Similarly, prevent the cross-partition update code from doing any EPQ processing for a merge, allowing the merge code to work out what it needs to do. This leads to a number of simplifications in nodeModifyTable.c. Most notably, the ModifyTableContext->GetUpdateNewTuple() callback is no longer needed, and mergeGetUpdateNewTuple() can be deleted, since there is no longer any requirement for get-update-new-tuple during a merge. Similarly, ModifyTableContext->cpUpdateRetrySlot is no longer needed. Thus ExecGetUpdateNewTuple() and the retry_slot handling of ExecCrossPartitionUpdate() can be restored to how they were in v14, before the merge code was added, and ExecMergeMatched() no longer needs any special-case handling for cross-partition updates. While at it, tidy up ExecUpdateEpilogue() a bit, making it handle recheckIndexes locally, rather than passing it in as a parameter, ensuring that it is freed properly. This dates back to when it was split off from ExecUpdate() to support merge. Per bug #17809 from Alexander Lakhin, and follow-up investigation of bug #17792, also from Alexander Lakhin. Back-patch to v15, where MERGE was introduced, taking care to preserve backwards-compatibility of the trigger API in v15 for any extensions that might use it. Discussion: https://postgr.es/m/17809-9e6650bef133f0fe%40postgresql.org https://postgr.es/m/17792-0f89452029662c36%40postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/7d9a75713ab91071a2110e25e7c86cbf2a6fdc4b Modified Files -- src/backend/commands/trigger.c | 82 ++-- src/backend/executor/nodeModifyTable.c | 181 +--- src/include/commands/trigger.h | 16 ++ src/test/isolation/expected/merge-delete.out | 195 + .../isolation/expected/merge-match-recheck.out | 233 + src/test/isolation/specs/merge-delete.spec | 66 +- src/test/isolation/specs/merge-match-recheck.spec | 107 ++ 7 files changed, 693 insertions(+), 187 deletions(-)
pgsql: Fix concurrent update issues with MERGE.
Fix concurrent update issues with MERGE. If MERGE attempts an UPDATE or DELETE on a table with BEFORE ROW triggers, or a cross-partition UPDATE (with or without triggers), and a concurrent UPDATE or DELETE happens, the merge code would fail. In some cases this would lead to a crash, while in others it would cause the wrong merge action to be executed, or no action at all. The immediate cause of the crash was the trigger code calling ExecGetUpdateNewTuple() as part of the EPQ mechanism, which fails because during a merge ri_projectNew is NULL, since merge has its own per-action projection information, which ExecGetUpdateNewTuple() knows nothing about. Fix by arranging for the trigger code to exit early, returning the TM_Result and TM_FailureData information, if a concurrent modification is detected, allowing the merge code to do the necessary EPQ handling in its own way. Similarly, prevent the cross-partition update code from doing any EPQ processing for a merge, allowing the merge code to work out what it needs to do. This leads to a number of simplifications in nodeModifyTable.c. Most notably, the ModifyTableContext->GetUpdateNewTuple() callback is no longer needed, and mergeGetUpdateNewTuple() can be deleted, since there is no longer any requirement for get-update-new-tuple during a merge. Similarly, ModifyTableContext->cpUpdateRetrySlot is no longer needed. Thus ExecGetUpdateNewTuple() and the retry_slot handling of ExecCrossPartitionUpdate() can be restored to how they were in v14, before the merge code was added, and ExecMergeMatched() no longer needs any special-case handling for cross-partition updates. While at it, tidy up ExecUpdateEpilogue() a bit, making it handle recheckIndexes locally, rather than passing it in as a parameter, ensuring that it is freed properly. This dates back to when it was split off from ExecUpdate() to support merge. Per bug #17809 from Alexander Lakhin, and follow-up investigation of bug #17792, also from Alexander Lakhin. Back-patch to v15, where MERGE was introduced, taking care to preserve backwards-compatibility of the trigger API in v15 for any extensions that might use it. Discussion: https://postgr.es/m/17809-9e6650bef133f0fe%40postgresql.org https://postgr.es/m/17792-0f89452029662c36%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/9321c79c86e6a6a4eac22e2235a21a8b68388723 Modified Files -- src/backend/commands/trigger.c | 27 ++- src/backend/executor/execReplication.c | 4 +- src/backend/executor/nodeModifyTable.c | 173 +-- src/include/commands/trigger.h | 5 +- src/test/isolation/expected/merge-delete.out | 195 + .../isolation/expected/merge-match-recheck.out | 233 + src/test/isolation/specs/merge-delete.spec | 66 +- src/test/isolation/specs/merge-match-recheck.spec | 107 ++ 8 files changed, 636 insertions(+), 174 deletions(-)
pgsql: Doc: Miscellaneous doc updates for MERGE.
Doc: Miscellaneous doc updates for MERGE. Update a few places in the documentation that should mention MERGE among the list of applicable commands. In a couple of places, a slightly more detailed description of what happens for MERGE seems appropriate. Reviewed by Alvaro Herrera. Discussion: http://postgr.es/m/CAEZATCWqHLcxab89ATMQZNGFG_mxDPM%2BjzkSbXKD3JYPfRGvtw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/ee7e8f3838733f09a67e1421f0d7c6498cd68ab0 Modified Files -- doc/src/sgml/arch-dev.sgml | 16 doc/src/sgml/ddl.sgml| 3 ++- doc/src/sgml/high-availability.sgml | 3 ++- doc/src/sgml/perform.sgml| 8 +--- doc/src/sgml/plpgsql.sgml| 3 ++- doc/src/sgml/protocol.sgml | 7 +++ doc/src/sgml/queries.sgml| 12 +++- doc/src/sgml/ref/create_publication.sgml | 6 ++ doc/src/sgml/ref/explain.sgml| 6 -- doc/src/sgml/ref/prepare.sgml| 3 ++- doc/src/sgml/ref/set_transaction.sgml| 11 +++ doc/src/sgml/xfunc.sgml | 3 ++- 12 files changed, 58 insertions(+), 23 deletions(-)
pgsql: Doc: Miscellaneous doc updates for MERGE.
Doc: Miscellaneous doc updates for MERGE. Update a few places in the documentation that should mention MERGE among the list of applicable commands. In a couple of places, a slightly more detailed description of what happens for MERGE seems appropriate. Reviewed by Alvaro Herrera. Discussion: http://postgr.es/m/CAEZATCWqHLcxab89ATMQZNGFG_mxDPM%2BjzkSbXKD3JYPfRGvtw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/a6864751cd11ae99c16da48d603fafa55ce8e57e Modified Files -- doc/src/sgml/arch-dev.sgml | 16 doc/src/sgml/ddl.sgml| 3 ++- doc/src/sgml/high-availability.sgml | 3 ++- doc/src/sgml/perform.sgml| 8 +--- doc/src/sgml/plpgsql.sgml| 3 ++- doc/src/sgml/protocol.sgml | 7 +++ doc/src/sgml/queries.sgml| 12 +++- doc/src/sgml/ref/create_publication.sgml | 6 ++ doc/src/sgml/ref/explain.sgml| 6 -- doc/src/sgml/ref/prepare.sgml| 3 ++- doc/src/sgml/ref/set_transaction.sgml| 11 +++ doc/src/sgml/xfunc.sgml | 3 ++- 12 files changed, 58 insertions(+), 23 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/39ad791e851022bc88feca4637118e7b589f5f3a Modified Files -- src/backend/rewrite/rewriteHandler.c | 22 ++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 60 insertions(+), 4 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/4fd093af7186e9e3bad805e580edd1991f8f3350 Modified Files -- src/backend/rewrite/rewriteHandler.c | 22 ++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 60 insertions(+), 4 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/a7d71c41dbd691ac86cc47114dab9db4b31f27ad Modified Files -- src/backend/rewrite/rewriteHandler.c | 22 ++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 60 insertions(+), 4 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/8e5b4e0013a8a24644243cdb9516ac52287a81c8 Modified Files -- src/backend/rewrite/rewriteHandler.c | 22 ++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 60 insertions(+), 4 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/27ff93d18c2ba921cfbb9e2e38f5fb66c130bc9f Modified Files -- src/backend/rewrite/rewriteHandler.c | 22 ++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 60 insertions(+), 4 deletions(-)
pgsql: Fix mishandling of OLD/NEW references in subqueries in rule acti
Fix mishandling of OLD/NEW references in subqueries in rule actions. If a rule action contains a subquery that refers to columns from OLD or NEW, then those are really lateral references, and the planner will complain if it sees such things in a subquery that isn't marked as lateral. However, at rule-definition time, the user isn't required to mark the subquery with LATERAL, and so it can fail when the rule is used. Fix this by marking such subqueries as lateral in the rewriter, at the point where they're used. Dean Rasheed and Tom Lane, per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/5e09da43-aaba-7ea7-0a51-a2eb981b058b%40gmail.com Branch -- REL_11_STABLE Details --- https://git.postgresql.org/pg/commitdiff/79f194cc0144fad07fa18c4b2a5f32bce9035ee0 Modified Files -- src/backend/rewrite/rewriteHandler.c | 23 +++ src/test/regress/expected/rules.out | 25 + src/test/regress/sql/rules.sql | 17 + 3 files changed, 61 insertions(+), 4 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- REL_11_STABLE Details --- https://git.postgresql.org/pg/commitdiff/e68b133c30e2146b51c15be702f8954bc8fdb63b Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f0423bea7f0afe6ccd830e2b729180e4355e04f6 Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/98b83b7349821b05134e6f50f516ecac878cb91d Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/940b5474365fa87ef4aad7abeee070e8e49cc9d5 Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/226da3d4767083b58b9d1acf67b1402865d359bf Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Fix multi-row DEFAULT handling for INSERT ... SELECT rules. Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, beef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/75c737636b8a1e9a110e580f1caee181023447e2 Modified Files -- src/backend/rewrite/rewriteHandler.c | 32 --- src/backend/rewrite/rewriteManip.c| 11 + src/test/regress/expected/updatable_views.out | 19 src/test/regress/sql/updatable_views.sql | 10 + 4 files changed, 65 insertions(+), 7 deletions(-)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/906356cf6128ac6e35bcb07f7fd5361eb76c4999 Modified Files -- src/backend/executor/spi.c | 2 ++ 1 file changed, 2 insertions(+)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/482ab3e4f9e0a2fcef96bdcbe7a719858fae8f79 Modified Files -- src/backend/executor/spi.c | 2 ++ 1 file changed, 2 insertions(+)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/52dbd9f845987ff3a6f97d30b3bebb13fdb4b2b4 Modified Files -- src/backend/executor/spi.c | 2 ++ 1 file changed, 2 insertions(+)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/d0460a31de6acc8bbb86e9e3d646f1113ebb0c20 Modified Files -- doc/src/sgml/spi.sgml | 9 + src/backend/executor/spi.c | 4 src/pl/tcl/pltcl.c | 1 + 3 files changed, 14 insertions(+)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/576b25bfd0e9a1d5bbc54931e888135bc6da8a2f Modified Files -- doc/src/sgml/spi.sgml | 9 + src/backend/executor/spi.c | 4 src/pl/tcl/pltcl.c | 1 + 3 files changed, 14 insertions(+)
pgsql: Add missing support for the latest SPI status codes.
Add missing support for the latest SPI status codes. SPI_result_code_string() was missing support for SPI_OK_TD_REGISTER, and in v15 and later, it was missing support for SPI_OK_MERGE, as was pltcl_process_SPI_result(). The last of those would trigger an error if a MERGE was executed from PL/Tcl. The others seem fairly innocuous, but worth fixing. Back-patch to all supported branches. Before v15, this is just adding SPI_OK_TD_REGISTER to SPI_result_code_string(), which is unlikely to be seen by anyone, but seems worth doing for completeness. Reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCUg8V%2BK%2BGcafOPqymxk84Y_prXgfe64PDoopjLFH6Z0Aw%40mail.gmail.com https://postgr.es/m/CAEZATCUMe%2B_KedPMM9AxKqm%3DSZogSxjUcrMe%2BsakusZh3BFcQw%40mail.gmail.com Branch -- REL_11_STABLE Details --- https://git.postgresql.org/pg/commitdiff/83a54d9661027cbb0a97e543ce7440d55812c87c Modified Files -- src/backend/executor/spi.c | 2 ++ 1 file changed, 2 insertions(+)
pgsql: Fix Assert failure for MERGE into a partitioned table with RLS.
Fix Assert failure for MERGE into a partitioned table with RLS. In ExecInitPartitionInfo(), the Assert when building the WITH CHECK OPTION list for the new partition assumed that the command would be an INSERT or UPDATE, but it can also be a MERGE. This can be triggered by a MERGE into a partitioned table with RLS checks to enforce. Fix, and back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWWFtQmW67F3XTyMU5Am10Oxa_b8oe0x%2BNu5Mo%2BCdRErg%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/d8c3b65db58db0a074dc9f7e27846e22e9dc579f Modified Files -- src/backend/executor/execPartition.c | 10 +++--- src/test/regress/expected/merge.out | 12 src/test/regress/sql/merge.sql | 12 3 files changed, 31 insertions(+), 3 deletions(-)
pgsql: Fix Assert failure for MERGE into a partitioned table with RLS.
Fix Assert failure for MERGE into a partitioned table with RLS. In ExecInitPartitionInfo(), the Assert when building the WITH CHECK OPTION list for the new partition assumed that the command would be an INSERT or UPDATE, but it can also be a MERGE. This can be triggered by a MERGE into a partitioned table with RLS checks to enforce. Fix, and back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWWFtQmW67F3XTyMU5Am10Oxa_b8oe0x%2BNu5Mo%2BCdRErg%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0d3b49d4af7509032c847bfc956aac98d30644bf Modified Files -- src/backend/executor/execPartition.c | 10 +++--- src/test/regress/expected/merge.out | 12 src/test/regress/sql/merge.sql | 12 3 files changed, 31 insertions(+), 3 deletions(-)
pgsql: Fix MERGE command tag for cross-partition updates.
Fix MERGE command tag for cross-partition updates. This ensures that the row count in the command tag for a MERGE is correctly computed. Previously, if MERGE updated a partitioned table, the row count would be incorrect if any row was moved to a different partition, since such updates were counted twice. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWRMG7XX2QEsVL1LswmNo2d_YG8tKTLkpD3=lp644s...@mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/018af1cc1c8075346e6a5fe3bb77b7e31399be70 Modified Files -- src/backend/executor/nodeModifyTable.c | 2 +- src/test/regress/expected/merge.out| 16 src/test/regress/sql/merge.sql | 11 +++ 3 files changed, 28 insertions(+), 1 deletion(-)
pgsql: Fix MERGE command tag for cross-partition updates.
Fix MERGE command tag for cross-partition updates. This ensures that the row count in the command tag for a MERGE is correctly computed. Previously, if MERGE updated a partitioned table, the row count would be incorrect if any row was moved to a different partition, since such updates were counted twice. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWRMG7XX2QEsVL1LswmNo2d_YG8tKTLkpD3=lp644s...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/80a48e0f21c51ed65fe647b18019ab6f8eab15cd Modified Files -- src/backend/executor/nodeModifyTable.c | 2 +- src/test/regress/expected/merge.out| 16 src/test/regress/sql/merge.sql | 11 +++ 3 files changed, 28 insertions(+), 1 deletion(-)
pgsql: Allow underscores in integer and numeric constants.
Allow underscores in integer and numeric constants. This allows underscores to be used in integer and numeric literals, and their corresponding type input functions, for visual grouping. For example: 1_500_000_000 3.14159_26535_89793 0x_ 0b_1001_0001 A single underscore is allowed between any 2 digits, or immediately after the base prefix indicator of non-decimal integers, per SQL:202x draft. Peter Eisentraut and Dean Rasheed Discussion: https://postgr.es/m/84aae844-dc55-a4be-86d9-4f0fa405cc97%40enterprisedb.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/faff8f8e47f18c7d589453e2e0d841d2bd96c1ac Modified Files -- doc/src/sgml/syntax.sgml | 36 ++-- src/backend/catalog/sql_features.txt | 1 + src/backend/parser/parse_node.c | 43 +--- src/backend/parser/scan.l | 27 +-- src/backend/utils/adt/numeric.c | 106 +++--- src/backend/utils/adt/numutils.c | 273 -- src/fe_utils/psqlscan.l | 16 +- src/interfaces/ecpg/preproc/pgc.l | 16 +- src/pl/plpgsql/src/expected/plpgsql_trap.out | 2 +- src/pl/plpgsql/src/sql/plpgsql_trap.sql | 2 +- src/test/regress/expected/int2.out| 44 + src/test/regress/expected/int4.out| 44 + src/test/regress/expected/int8.out| 44 + src/test/regress/expected/numeric.out | 62 +- src/test/regress/expected/numerology.out | 92 - src/test/regress/expected/partition_prune.out | 6 +- src/test/regress/sql/int2.sql | 14 ++ src/test/regress/sql/int4.sql | 14 ++ src/test/regress/sql/int8.sql | 14 ++ src/test/regress/sql/numeric.sql | 22 ++- src/test/regress/sql/numerology.sql | 24 ++- src/test/regress/sql/partition_prune.sql | 6 +- 22 files changed, 724 insertions(+), 184 deletions(-)
pgsql: Make int64_div_fast_to_numeric() more robust.
Make int64_div_fast_to_numeric() more robust. The prior coding of int64_div_fast_to_numeric() had a number of bugs that would cause it to fail under different circumstances, such as with log10val2 <= 0, or log10val2 a multiple of 4, or in the "slow" numeric path with log10val2 >= 10. None of those could be triggered by any of our current code, which only uses log10val2 = 3 or 6. However, they made it a hazard for any future code that might use it. Also, since this is exported by numeric.c, users writing their own C code might choose to use it. Therefore fix, and back-patch to v14, where it was introduced. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCW8gXgW0tgPxPgHDPhVX71%2BSWFRkhnXy%2BTfGDsKLepu2g%40mail.gmail.com Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/86bfbeab4f439ad527318d9edeb3c71ea46c1ab3 Modified Files -- src/backend/utils/adt/numeric.c | 80 +++-- 1 file changed, 54 insertions(+), 26 deletions(-)
pgsql: Make int64_div_fast_to_numeric() more robust.
Make int64_div_fast_to_numeric() more robust. The prior coding of int64_div_fast_to_numeric() had a number of bugs that would cause it to fail under different circumstances, such as with log10val2 <= 0, or log10val2 a multiple of 4, or in the "slow" numeric path with log10val2 >= 10. None of those could be triggered by any of our current code, which only uses log10val2 = 3 or 6. However, they made it a hazard for any future code that might use it. Also, since this is exported by numeric.c, users writing their own C code might choose to use it. Therefore fix, and back-patch to v14, where it was introduced. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCW8gXgW0tgPxPgHDPhVX71%2BSWFRkhnXy%2BTfGDsKLepu2g%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/4f74741a5cea38228fdb0fb2967fa2137ea02cbf Modified Files -- src/backend/utils/adt/numeric.c | 80 +++-- 1 file changed, 54 insertions(+), 26 deletions(-)
pgsql: Make int64_div_fast_to_numeric() more robust.
Make int64_div_fast_to_numeric() more robust. The prior coding of int64_div_fast_to_numeric() had a number of bugs that would cause it to fail under different circumstances, such as with log10val2 <= 0, or log10val2 a multiple of 4, or in the "slow" numeric path with log10val2 >= 10. None of those could be triggered by any of our current code, which only uses log10val2 = 3 or 6. However, they made it a hazard for any future code that might use it. Also, since this is exported by numeric.c, users writing their own C code might choose to use it. Therefore fix, and back-patch to v14, where it was introduced. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCW8gXgW0tgPxPgHDPhVX71%2BSWFRkhnXy%2BTfGDsKLepu2g%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/b2d47928908d7a99b8e39198d0e8e9e0cb2b024b Modified Files -- src/backend/utils/adt/numeric.c | 73 ++--- 1 file changed, 46 insertions(+), 27 deletions(-)
Re: pgsql: Ensure that numeric.c compiles with other NBASE values.
On Fri, 3 Feb 2023 at 01:18, Tom Lane wrote: > > Dean Rasheed writes: > > > 1). It doesn't work if log10val2 < 0, because then m < 0, and it > > doesn't multiply by the remainder. And it then throws an overflow > > error, because result.dscale comes out wrong when m < 0. > > I'm not quite sure that it's worth expending code space on the > log10val2 < 0 case (compared to just "Assert(log10val2 >= 0"). > On the other hand, it's not much extra code, and committing it now > might save somebody reinventing that logic in future. > Yeah, I thought about that, but it's hardly any code to support that case. Also, this function is out there now (I found an example on Stack Overflow of someone using it), so we have no control over how people will use it in their own C code, and so I think it's worth making it robust across the range of possible inputs. Regards, Dean
Re: pgsql: Ensure that numeric.c compiles with other NBASE values.
On Thu, 2 Feb 2023 at 15:15, Tom Lane wrote:
>
> Looking at this diff made me wonder why the static pow10[] array
> isn't marked "const"?
>
Good point.
However, looking more closely, I think this function is more or less
completely broken:
1). It doesn't work if log10val2 < 0, because then m < 0, and it
doesn't multiply by the remainder. And it then throws an overflow
error, because result.dscale comes out wrong when m < 0.
2). The result.dscale calculation is wrong if log10val2 is a multiple
of DEC_DIGITS, causing it to drop the last 4 digits.
3). If the scaled-up dividend doesn't fit in an int64, the numeric
computation breaks if log10val2 >= 10 due to integer overflow.
So for example:
int64_div_fast_to_numeric(123456, -1) -> ERROR: value overflows numeric format
int64_div_fast_to_numeric(123456, 0) -> ERROR: value overflows numeric format
int64_div_fast_to_numeric(123456, 4) -> 12
int64_div_fast_to_numeric(123456, 8) -> 0.0012
int64_div_fast_to_numeric(100, 10) -> 709186959.9285992800
As it happens, none of the above represents a live bug, because we
currently only call it with log10val2 = 3 or 6, but it's definitely a
bug waiting to happen.
This was added by a2da77cdb4 ("Change return type of EXTRACT to
numeric"), so it only goes back as far as v14.
After hacking on it for a while, I ended up with the attached.
Regards,
Dean
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index 08c8416..6bf6db6
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4235,7 +4235,7 @@ int64_to_numeric(int64 val)
}
/*
- * Convert val1/(10**val2) to numeric. This is much faster than normal
+ * Convert val1/(10**log10val2) to numeric. This is much faster than normal
* numeric division.
*/
Numeric
@@ -4243,59 +4243,78 @@ int64_div_fast_to_numeric(int64 val1, in
{
Numeric res;
NumericVar result;
- int64 saved_val1 = val1;
+ int rscale;
int w;
int m;
+ init_var();
+
+ /* result scale */
+ rscale = log10val2 < 0 ? 0 : log10val2;
+
/* how much to decrease the weight by */
w = log10val2 / DEC_DIGITS;
- /* how much is left */
+ /* how much is left to divide by */
m = log10val2 % DEC_DIGITS;
+ if (m < 0)
+ {
+ m += DEC_DIGITS;
+ w--;
+ }
/*
- * If there is anything left, multiply the dividend by what's left, then
- * shift the weight by one more.
+ * If there is anything left to divide by (10^m with 0 < m < DEC_DIGITS),
+ * multiply the dividend by 10^(DEC_DIGITS - m), and shift the weight by
+ * one more.
*/
if (m > 0)
{
#if DEC_DIGITS == 4
- static int pow10[] = {1, 10, 100, 1000};
+ static const int pow10[] = {1, 10, 100, 1000};
#elif DEC_DIGITS == 2
- static int pow10[] = {1, 10};
+ static const int pow10[] = {1, 10};
#elif DEC_DIGITS == 1
- static int pow10[] = {1};
+ static const int pow10[] = {1};
#else
#error unsupported NBASE
#endif
+ int64 factor = pow10[DEC_DIGITS - m];
+ int64 new_val1;
StaticAssertDecl(lengthof(pow10) == DEC_DIGITS, "mismatch with DEC_DIGITS");
- if (unlikely(pg_mul_s64_overflow(val1, pow10[DEC_DIGITS - m], )))
+ if (unlikely(pg_mul_s64_overflow(val1, factor, _val1)))
{
- /*
- * If it doesn't fit, do the whole computation in numeric the slow
- * way. Note that va1l may have been overwritten, so use
- * saved_val1 instead.
- */
- int val2 = 1;
+#ifdef HAVE_INT128
+ /* do the multiplication using 128-bit integers */
+ int128 tmp;
- for (int i = 0; i < log10val2; i++)
-val2 *= 10;
- res = numeric_div_opt_error(int64_to_numeric(saved_val1), int64_to_numeric(val2), NULL);
- res = DatumGetNumeric(DirectFunctionCall2(numeric_round,
- NumericGetDatum(res),
- Int32GetDatum(log10val2)));
- return res;
+ tmp = (int128) val1 * (int128) factor;
+
+ int128_to_numericvar(tmp, );
+#else
+ /* do the multiplication using numerics */
+ NumericVar tmp;
+
+ init_var();
+
+ int64_to_numericvar(val1, );
+ int64_to_numericvar(factor, );
+ mul_var(, , , 0);
+
+ free_var();
+#endif
}
+ else
+ int64_to_numericvar(new_val1, );
+
w++;
}
-
- init_var();
-
- int64_to_numericvar(val1, );
+ else
+ int64_to_numericvar(val1, );
result.weight -= w;
- result.dscale += w * DEC_DIGITS - (DEC_DIGITS - m);
+ result.dscale = rscale;
res = make_result();
pgsql: Ensure that numeric.c compiles with other NBASE values.
Ensure that numeric.c compiles with other NBASE values. As noted in the comments, support for different NBASE values is really only of historical interest, but as long as we're keeping it, we might as well make sure that it compiles. Joel Jacobson Discussion: https://postgr.es/m/06712c29-98e9-43b3-98da-f234d81c6e49%40app.fastmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/9a84f2947bf9345ad6b93ba37da63633649eaea8 Modified Files -- src/backend/utils/adt/numeric.c | 8 1 file changed, 8 insertions(+)
pgsql: Clarify the choice of rscale in numeric_sqrt().
Clarify the choice of rscale in numeric_sqrt(). Improve the comment explaining the choice of rscale in numeric_sqrt(), and ensure that the code works consistently when other values of NBASE/DEC_DIGITS are used. Note that, in practice, we always expect DEC_DIGITS == 4, and this does not change the computation in that case. Joel Jacobson and Dean Rasheed Discussion: https://postgr.es/m/06712c29-98e9-43b3-98da-f234d81c6e49%40app.fastmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0736fc1ceb0659a9f73699910ac56603336daeee Modified Files -- src/backend/utils/adt/numeric.c | 17 +++-- 1 file changed, 15 insertions(+), 2 deletions(-)
pgsql: Ensure that MERGE recomputes GENERATED expressions properly.
Ensure that MERGE recomputes GENERATED expressions properly. This fixes a bug that, under some circumstances, would cause MERGE to fail to properly recompute expressions for GENERATED STORED columns. Formerly, ExecInitModifyTable() did not call ExecInitStoredGenerated() for a MERGE command, which meant that the generated expressions information was not computed until later, when the first merge action was executed. However, if the first merge action to execute was an UPDATE, then ExecInitStoredGenerated() could decide to skip some some generated columns, if the columns on which they depended were not updated, which was a problem if the MERGE also contained an INSERT action, for which no generated columns should be skipped. So fix by having ExecInitModifyTable() call ExecInitStoredGenerated() for MERGE, and assume that it isn't safe to skip any generated columns in a MERGE. Possibly that could be relaxed, by allowing some generated columns to be skipped for a MERGE without an INSERT action, but it's not clear that it's worth the effort. Noticed while investigating bug #17759. Back-patch to v15, where MERGE was added. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17759-e76d9bece1b5421c%40postgresql.org https://postgr.es/m/CAEZATCXb_ezoMCcL0tzKwRGA1x0oeE%3DawTaysRfTPq%2B3wNJn8g%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/fe9e658f4d7fbc12d2b6a74c4ee90c73e53d68ef Modified Files -- src/backend/executor/nodeModifyTable.c | 4 ++-- src/test/regress/expected/generated.out | 20 src/test/regress/sql/generated.sql | 15 +++ 3 files changed, 37 insertions(+), 2 deletions(-)
pgsql: Ensure that MERGE recomputes GENERATED expressions properly.
Ensure that MERGE recomputes GENERATED expressions properly. This fixes a bug that, under some circumstances, would cause MERGE to fail to properly recompute expressions for GENERATED STORED columns. Formerly, ExecInitModifyTable() did not call ExecInitStoredGenerated() for a MERGE command, which meant that the generated expressions information was not computed until later, when the first merge action was executed. However, if the first merge action to execute was an UPDATE, then ExecInitStoredGenerated() could decide to skip some some generated columns, if the columns on which they depended were not updated, which was a problem if the MERGE also contained an INSERT action, for which no generated columns should be skipped. So fix by having ExecInitModifyTable() call ExecInitStoredGenerated() for MERGE, and assume that it isn't safe to skip any generated columns in a MERGE. Possibly that could be relaxed, by allowing some generated columns to be skipped for a MERGE without an INSERT action, but it's not clear that it's worth the effort. Noticed while investigating bug #17759. Back-patch to v15, where MERGE was added. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17759-e76d9bece1b5421c%40postgresql.org https://postgr.es/m/CAEZATCXb_ezoMCcL0tzKwRGA1x0oeE%3DawTaysRfTPq%2B3wNJn8g%40mail.gmail.com Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/4785af9e6318856d45e51fbc328d52f6c5340e13 Modified Files -- src/backend/executor/nodeModifyTable.c | 4 ++-- src/test/regress/expected/generated.out | 20 src/test/regress/sql/generated.sql | 15 +++ 3 files changed, 37 insertions(+), 2 deletions(-)
pgsql: Add non-decimal integer support to type numeric.
Add non-decimal integer support to type numeric. This enhances the numeric type input function, adding support for hexadecimal, octal, and binary integers of any size, up to the limits of the numeric type. Since 6fcda9aba8, such non-decimal integers have been accepted by the parser as integer literals and passed through to numeric_in(). This commit gives numeric_in() the ability to handle them. While at it, simplify the handling of NaN and infinities, reducing the number of calls to pg_strncasecmp(), and arrange for pg_strncasecmp() to not be called at all for regular numbers. This gives a significant performance improvement for decimal inputs, more than offsetting the small performance hit of checking for non-decimal input. Discussion: https://postgr.es/m/CAEZATCV8XShnmT9HZy25C%2Bo78CVOFmUN5EM9FRAZ5xvYTggPMg%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/6dfacbf72b53b775e8442a7fd2fca7c24b139773 Modified Files -- src/backend/utils/adt/numeric.c | 360 +-- src/test/regress/expected/numeric.out| 78 +-- src/test/regress/expected/numerology.out | 48 +++-- src/test/regress/sql/numeric.sql | 14 ++ 4 files changed, 405 insertions(+), 95 deletions(-)
