pgsql: Minor corrections for partition pruning
Minor corrections for partition pruning When the partition pruning code finds an OpExpr with an operator that does not belong to the partition key's opfamily, the code checks to see if the negator of the operator is the opfamily's BTEqualStrategyNumber operator so that partition pruning can support that operator and invert the matching partitions. Doing this only works for LIST partitioned tables. Here we fix a minor correctness issue where when we discover we're not pruning for a LIST partitioned table, we return PARTCLAUSE_NOMATCH. PARTCLAUSE_NOMATCH is only meant to be used when the clause may match another partitioned key column. For this case, the clause is not going to be any more useful to another partitioned key as the partition strategy is not going to change from one key to the next. Noticed while working 4c2369ac5. No backpatch because returning PARTCLAUSE_NOMATCH instead of PARTCLAUSE_UNSUPPORTED mostly just causes wasted effort checking subsequent partition keys against a clause that will never be used for pruning. In passing, correct a comment for get_matching_range_bounds() which mentions that an 'opstrategy' of 0 is supported. It's not, so fix the comment. This was pointed out by Alexander Lakhin. Discussion: https://postgr.es/m/caaphdvqriy8mpofj_bd66ygxj4+xulpv-4ydb+epdcqfzty...@mail.gmail.com Discussion: https://postgr.es/m/312fb507-9b5e-cf83-d8ed-cd0da72a9...@gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/d2ca9a50b5b99ef29aa65b68b5e6ddb253fbb04a Modified Files -- src/backend/partitioning/partprune.c | 13 - 1 file changed, 8 insertions(+), 5 deletions(-)
pgsql: Fix race leading to incorrect conflict cause in InvalidatePossib
Fix race leading to incorrect conflict cause in InvalidatePossiblyObsoleteSlot() The invalidation of an active slot is done in two steps: - Termination of the backend holding it, if any. - Report that the slot is obsolete, with a conflict cause depending on the slot's data. This can be racy because between these two steps the slot mutex would be released while doing system calls, which means that the effective_xmin and effective_catalog_xmin could advance during that time, detecting a conflict cause different than the one originally wanted before the process owning a slot is terminated. Holding the mutex longer is not an option, so this commit changes the code to record the LSNs stored in the slot during the termination of the process owning the slot. Bonus thanks to Alexander Lakhin for the various tests and the analysis. Author: Bertrand Drouvot Reviewed-by: Michael Paquier, Bharath Rupireddy Discussion: https://postgr.es/m/zatjw2xh+tquc...@ip-10-97-1-34.eu-west-3.compute.internal Backpatch-through: 16 Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/59cea09f03a56a40bce70a7461226c4d45740d02 Modified Files -- src/backend/replication/slot.c | 39 +-- 1 file changed, 33 insertions(+), 6 deletions(-)
pgsql: Fix race leading to incorrect conflict cause in InvalidatePossib
Fix race leading to incorrect conflict cause in InvalidatePossiblyObsoleteSlot() The invalidation of an active slot is done in two steps: - Termination of the backend holding it, if any. - Report that the slot is obsolete, with a conflict cause depending on the slot's data. This can be racy because between these two steps the slot mutex would be released while doing system calls, which means that the effective_xmin and effective_catalog_xmin could advance during that time, detecting a conflict cause different than the one originally wanted before the process owning a slot is terminated. Holding the mutex longer is not an option, so this commit changes the code to record the LSNs stored in the slot during the termination of the process owning the slot. Bonus thanks to Alexander Lakhin for the various tests and the analysis. Author: Bertrand Drouvot Reviewed-by: Michael Paquier, Bharath Rupireddy Discussion: https://postgr.es/m/zatjw2xh+tquc...@ip-10-97-1-34.eu-west-3.compute.internal Backpatch-through: 16 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/818fefd8fd4412d45eb542155cb2833a2b864acc Modified Files -- src/backend/replication/slot.c | 39 +-- 1 file changed, 33 insertions(+), 6 deletions(-)
pgsql: doc: Use system-username instead of system-user
doc: Use system-username instead of system-user This inconsistency has been introduced in efb6f4a4f9b6. Reported-by: Julien Rouhaud Author: Bertrand Drouvot Discussion: https://postgr.es/m/zdmwux1hpiebk...@ip-10-97-1-34.eu-west-3.compute.internal Backpatch-through: 16 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/01ec4d89b91ed4c0cad57b188b530b9e7980ccb5 Modified Files -- doc/src/sgml/client-auth.sgml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-)
pgsql: doc: Use system-username instead of system-user
doc: Use system-username instead of system-user This inconsistency has been introduced in efb6f4a4f9b6. Reported-by: Julien Rouhaud Author: Bertrand Drouvot Discussion: https://postgr.es/m/zdmwux1hpiebk...@ip-10-97-1-34.eu-west-3.compute.internal Backpatch-through: 16 Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/ec4fcf4a9292378a6a586c7533ef970edf083831 Modified Files -- doc/src/sgml/client-auth.sgml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/3ffcd24c29c60e913c9f5f4c0911180c9f3b9897 Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/3850fcca69b5db0694ceb5d1134699dc247f201e Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f9c8f7ccd6e34bc9222a09af3205600611bcda27 Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/1b3495e29db6c7ca32a88d5546e7813efa71cbbb Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/fb95cc72bfe7a31efa8a711b8094a0c41ffad291 Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix incorrect pruning of NULL partition for boolean IS NOT claus
Fix incorrect pruning of NULL partition for boolean IS NOT clauses Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d0...@postgresql.org Backpatch-through: 12 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/4c2369ac5d0a108df4d65a2886657efa010d67ca Modified Files -- src/backend/partitioning/partprune.c | 56 +- src/test/regress/expected/partition_prune.out | 103 ++ src/test/regress/sql/partition_prune.sql | 30 3 files changed, 187 insertions(+), 2 deletions(-)
pgsql: Fix test race between primary XLOG_RUNNING_XACTS and standby log
Fix test race between primary XLOG_RUNNING_XACTS and standby logical slot. Before the previous commit, the test could hang until LOG_SNAPSHOT_INTERVAL_MS (15s), until checkpoint_timeout (300s), or indefinitely. An indefinite hang was awfully improbable. It entailed the test reaching checkpoint_timeout before the DecodingContextFindStartpoint() of a CREATE SUBSCRIPTION, yet after the preceding WAL record. Back-patch to v16, which introduced the test. Bertrand Drouvot, reported by Noah Misch. Discussion: https://postgr.es/m/20240211010227.a2.nmi...@google.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/c59a97313ba34aeb4b1670307c0228fb94694adb Modified Files -- src/test/perl/PostgreSQL/Test/Cluster.pm | 46 +++--- .../recovery/t/035_standby_logical_decoding.pl | 4 +- 2 files changed, 34 insertions(+), 16 deletions(-)
pgsql: Bound waits in 035_standby_logical_decoding.pl.
Bound waits in 035_standby_logical_decoding.pl. One IPC::Run::start() used an IPC::Run::timer() without checking for expiration. The other used no timeout or timer. Back-patch to v16, which introduced the test. Reviewed by Bertrand Drouvot. Discussion: https://postgr.es/m/20240211010227.a2.nmi...@google.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/4791f87f34bd3a055db34519d6f878afeedd2548 Modified Files -- src/test/recovery/t/035_standby_logical_decoding.pl | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-)
pgsql: Fix test race between primary XLOG_RUNNING_XACTS and standby log
Fix test race between primary XLOG_RUNNING_XACTS and standby logical slot. Before the previous commit, the test could hang until LOG_SNAPSHOT_INTERVAL_MS (15s), until checkpoint_timeout (300s), or indefinitely. An indefinite hang was awfully improbable. It entailed the test reaching checkpoint_timeout before the DecodingContextFindStartpoint() of a CREATE SUBSCRIPTION, yet after the preceding WAL record. Back-patch to v16, which introduced the test. Bertrand Drouvot, reported by Noah Misch. Discussion: https://postgr.es/m/20240211010227.a2.nmi...@google.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0e162810df7657bac24ba4657460a87104523fc6 Modified Files -- src/test/perl/PostgreSQL/Test/Cluster.pm | 46 +++--- .../recovery/t/035_standby_logical_decoding.pl | 4 +- 2 files changed, 34 insertions(+), 16 deletions(-)
pgsql: Bound waits in 035_standby_logical_decoding.pl.
Bound waits in 035_standby_logical_decoding.pl. One IPC::Run::start() used an IPC::Run::timer() without checking for expiration. The other used no timeout or timer. Back-patch to v16, which introduced the test. Reviewed by Bertrand Drouvot. Discussion: https://postgr.es/m/20240211010227.a2.nmi...@google.com Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f024746484b966f916e6d8e1d0bd39de51aad2e1 Modified Files -- src/test/recovery/t/035_standby_logical_decoding.pl | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f2c7a6ea8bbcada6dfecb22612092ce9ea352444 Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/eac4aff0b8a5d4cd003012ba4464bd0b39a9f875 Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/94d7250ac42c0185662130e4bfbe05f656b54278 Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/517010bd49931f75d0058811c7fa9d4f1a1493d3 Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/7f2718532401cfcf5ad2d5d6e7feb15a91b6aa03 Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Doc: fix typo in SECURITY LABEL synopsis.
Doc: fix typo in SECURITY LABEL synopsis. One case missed its trailing "|". Reported by Tim Needham. Discussion: https://postgr.es/m/170833547220.3279712.700702770281879...@wrigleys.postgresql.org Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/6257ebf1e335b0ca73a460aa9774e3c782506cee Modified Files -- doc/src/sgml/ref/security_label.sgml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Get rid of pg_class usage in SJE regression tests
Get rid of pg_class usage in SJE regression tests Usage of pg_class led to instability, see the buildfarm failure. https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2024-02-15%2021%3A58%3A04 Reported-by: Andrei Lepikhov Discussion: https://postgr.es/m/250c4cec-e459-4311-8fd6-da2558b3f...@postgrespro.ru Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/e1b7fde418f2c0ba4ab0d9fbfa801ef62d96397b Modified Files -- src/test/regress/expected/aggregates.out | 32 +++- src/test/regress/sql/aggregates.sql | 9 +++-- 2 files changed, 18 insertions(+), 23 deletions(-)
pgsql: ci: Add test coverage of different pg_upgrade modes
ci: Add test coverage of different pg_upgrade modes Run freebsd with --link and macos with --clone, to get some coverage of the non-default modes. Author: Justin Pryzby Discussion: https://www.postgresql.org/message-id/flat/20220528153741.gk19...@telsasoft.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/21a71648d39fe386acf8928ae59f41c6cccb47cf Modified Files -- .cirrus.tasks.yml | 4 1 file changed, 4 insertions(+)