pgsql: Add some opfamily support functions to lsyscache.c
Add some opfamily support functions to lsyscache.c Add get_opfamily_method() and get_opfamily_member_for_cmptype() in lsyscache.c. No callers yet, but we'll add some soon. This is part of generalizing some parts of the code away from having btree hardcoded and use CompareType instead. Author: Mark Dilger Discussion: https://www.postgresql.org/message-id/flat/e72eaa49-354d-4c2e-8eb9-255197f55...@enterprisedb.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/7317e641268fb9b08d32519920adf1f16c8591ea Modified Files -- src/backend/utils/cache/lsyscache.c | 44 + src/include/utils/lsyscache.h | 4 2 files changed, 48 insertions(+)
pgsql: Allow non-btree unique indexes for partition keys
Allow non-btree unique indexes for partition keys We were rejecting non-btree indexes in some cases owing to the inability to determine the equality operators for other index AMs; that problem no longer exists, because we can look up the equality operator using COMPARE_EQ. The problem of not knowing the strategy number for equality in other index AMs is already resolved. Stop rejecting the indexes upfront, and instead reject any for which the equality operator lookup fails. Author: Mark Dilger Discussion: https://www.postgresql.org/message-id/flat/e72eaa49-354d-4c2e-8eb9-255197f55...@enterprisedb.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/f278e1fe300ab1b7d43c3efb55a29aa17e5f5dda Modified Files -- src/backend/commands/indexcmds.c | 30 +++--- 1 file changed, 11 insertions(+), 19 deletions(-)
pgsql: Fix typo.
Fix typo. Author: vignesh C Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/caldanm1kqj0vffdjrpbfyi9shz6lhfee-ckn+eqsepfkheb...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/122a9af5def2db78f2c2131958eab8873bfee93b Modified Files -- src/backend/access/heap/vacuumlazy.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Fix indentation again.
Fix indentation again. Because somehow I manage to keep forgetting this. Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/796bdda484c838313959f65e2b700f14ac7c0e66 Modified Files -- src/include/commands/explain.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: aio: Add io_method=worker
aio: Add io_method=worker The previous commit introduced the infrastructure to start io_workers. This commit actually makes the workers execute IOs. IO workers consume IOs from a shared memory submission queue, run traditional synchronous system calls, and perform the shared completion handling immediately. Client code submits most requests by pushing IOs into the submission queue, and waits (if necessary) using condition variables. Some IOs cannot be performed in another process due to lack of infrastructure for reopening the file, and must processed synchronously by the client code when submitted. For now the default io_method is changed to "worker". We should re-evaluate that around beta1, we might want to be careful and set the default to "sync" for 18. Reviewed-by: Noah Misch Co-authored-by: Thomas Munro Co-authored-by: Andres Freund Discussion: https://postgr.es/m/uvrtrknj4kdytuboidbhwclo4gxhswwcpgadptsjvjqcluzmah%40brqs62irg4dt Discussion: https://postgr.es/m/20210223100344.llw5an2akleng...@alap3.anarazel.de Discussion: https://postgr.es/m/stj36ea6yyhoxtqkhpieia2z4krnam7qyetc57rfezgk4zgapf@gcnactj4z56m Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/247ce06b883d7b3a40d08312dc03dfb37fbff212 Modified Files -- doc/src/sgml/config.sgml| 5 + src/backend/storage/aio/aio.c | 2 + src/backend/storage/aio/aio_init.c | 9 + src/backend/storage/aio/method_worker.c | 445 +++- src/backend/utils/activity/wait_event_names.txt | 1 + src/backend/utils/misc/postgresql.conf.sample | 2 +- src/include/storage/aio.h | 5 +- src/include/storage/aio_internal.h | 1 + src/include/storage/lwlocklist.h| 1 + src/tools/pgindent/typedefs.list| 3 + 10 files changed, 466 insertions(+), 8 deletions(-)
pgsql: aio: Infrastructure for io_method=worker
aio: Infrastructure for io_method=worker This commit contains the basic, system-wide, infrastructure for io_method=worker. It does not yet actually execute IO, this commit just provides the infrastructure for running IO workers, kept separate for easier review. The number of IO workers can be adjusted with a PGC_SIGHUP GUC. Eventually we'd like to make the number of workers dynamically scale up/down based on the current "IO load". To allow the number of IO workers to be increased without a restart, we need to reserve PGPROC entries for the workers unconditionally. This has been judged to be worth the cost. If it turns out to be problematic, we can introduce a PGC_POSTMASTER GUC to control the maximum number. As io workers might be needed during shutdown, e.g. for AIO during the shutdown checkpoint, a new PMState phase is added. IO workers are shut down after the shutdown checkpoint has been performed and walsender/archiver have shut down, but before the checkpointer itself shuts down. See also 87a6690cc69. Updates PGSTAT_FILE_FORMAT_ID due to the addition of a new BackendType. Reviewed-by: Noah Misch Co-authored-by: Thomas Munro Co-authored-by: Andres Freund Discussion: https://postgr.es/m/uvrtrknj4kdytuboidbhwclo4gxhswwcpgadptsjvjqcluzmah%40brqs62irg4dt Discussion: https://postgr.es/m/20210223100344.llw5an2akleng...@alap3.anarazel.de Discussion: https://postgr.es/m/stj36ea6yyhoxtqkhpieia2z4krnam7qyetc57rfezgk4zgapf@gcnactj4z56m Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/55b454d0e14084c841a034073abbf1a0ea937a45 Modified Files -- doc/src/sgml/config.sgml| 19 +++ src/backend/postmaster/launch_backend.c | 2 + src/backend/postmaster/pmchild.c| 1 + src/backend/postmaster/postmaster.c | 174 ++-- src/backend/storage/aio/Makefile| 1 + src/backend/storage/aio/meson.build | 1 + src/backend/storage/aio/method_worker.c | 88 src/backend/tcop/postgres.c | 7 + src/backend/utils/activity/pgstat_backend.c | 1 + src/backend/utils/activity/pgstat_io.c | 1 + src/backend/utils/activity/wait_event_names.txt | 1 + src/backend/utils/init/miscinit.c | 3 + src/backend/utils/misc/guc_tables.c | 13 ++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/miscadmin.h | 2 + src/include/pgstat.h| 2 +- src/include/storage/aio_subsys.h| 4 + src/include/storage/io_worker.h | 22 +++ src/include/storage/proc.h | 4 +- src/test/regress/expected/stats.out | 10 +- 20 files changed, 342 insertions(+), 15 deletions(-)
pgsql: Simplify reindexdb coding
Simplify reindexdb coding get_parallel_object_list() was trying to serve two masters, and it was doing a bad job at both. In particular, it treated the given user_list as an output argument, but only sometimes. This was confusing, and the two paths through it didn't really have all that much in common, so the complexity wasn't buying us much. Split it in two: get_parallel_tables_list() handles the straightforward cases for schemas, databases and tables, takes one list as argument and returns another list. A new function get_parallel_tabidx_list() handles the case for indexes. This takes a list as argument and outputs two lists, just like get_parallel_object_list used to do, but now the API is clearer (IMO anyway). Another difference is that accompanying the list of indexes now we have a list of tables as an OID list rather than a fully-qualified table name list. This makes some comparisons easier, and we don't really need the names of the tables, just their OIDs. (This requires atooid, which requires ). Author: Ranier Vilela Author: Álvaro Herrera Discussion: https://postgr.es/m/CAEudQArfqr0-s0VVPSEh=0kgOgBJvFNdGW=xsl5rbcr0wdm...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/f76892c9ff7e5f2dcb4073310d1a5273f47d1d9d Modified Files -- src/bin/scripts/reindexdb.c| 275 +++-- src/bin/scripts/t/090_reindexdb.pl | 2 + 2 files changed, 141 insertions(+), 136 deletions(-)
pgsql: Increase default maintenance_io_concurrency to 16
Increase default maintenance_io_concurrency to 16 Since its introduction in fc34b0d9de27a, the default maintenance_io_concurrency has been larger than the default effective_io_concurrency. maintenance_io_concurrency primarily controlled prefetching done on behalf of the whole system, for operations like recovery. Therefore it makes sense for it to have a value equal to or greater than effective_io_concurrency, which controls I/O concurrency for reading a relation in a bitmap heap scan. ff79b5b2ab increased effective_io_concurrency to 16, so we'll increase maintenance_io_concurrency as well. For now, though, we'll keep the defaults of effective_io_concurrency and maintenance_io_concurrency equal to one another (16). On fast, high IOPs systems, significantly higher values of maintenance_io_concurrency are observably beneficial [1]. However, such values would flood low IOPs systems and increase overall system I/O latency. It is worth mentioning that since 9256822608f and c3e775e608f, maintenance_io_concurrency also controls the I/O concurrency of each vacuum worker. Since many autovacuum workers may be simultaneously issuing I/Os, we want to keep maintenance_io_concurrency appropriately conservative. [1] https://postgr.es/m/c5d52837-6256-0556-ac8c-d6d3d558820a%40enterprisedb.com Suggested-by: Jakub Wartak Discussion: https://postgr.es/m/CAKZiRmxdHQaU%2B2Zpe6d%3Dx%3D0vigJ1sfWwwVYLJAf%3Dud_wQ_VcUw%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/cc6be07ebde2aef7cc0507f997f563ce77c00a00 Modified Files -- doc/src/sgml/config.sgml | 8 src/backend/utils/misc/postgresql.conf.sample | 2 +- src/include/storage/bufmgr.h | 2 +- 3 files changed, 6 insertions(+), 6 deletions(-)
pgsql: Allow non-btree unique indexes for matviews
Allow non-btree unique indexes for matviews We were rejecting non-btree indexes in some cases owing to the inability to determine the equality operators for other index AMs; that problem no longer exists, because we can look up the equality operator using COMPARE_EQ. Stop rejecting these indexes, but instead rely on all unique indexes having equality operators. Unique indexes must have equality operators. Author: Mark Dilger Discussion: https://www.postgresql.org/message-id/flat/e72eaa49-354d-4c2e-8eb9-255197f55...@enterprisedb.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/9d6db8bec19413cd0167f1e59d1af005a997bd3e Modified Files -- src/backend/commands/matview.c | 15 --- 1 file changed, 4 insertions(+), 11 deletions(-)
pgsql: Make it possible for loadable modules to add EXPLAIN options.
Make it possible for loadable modules to add EXPLAIN options. Modules can use RegisterExtensionExplainOption to register new EXPLAIN options, and GetExplainExtensionId, GetExplainExtensionState, and SetExplainExtensionState to store related state inside the ExplainState object. Since this substantially increases the amount of code that needs to handle ExplainState-related tasks, move a few bits of existing code to a new file explain_state.c and add the rest of this infrastructure there. See the comments at the top of explain_state.c for further explanation of how this mechanism works. This does not yet provide a way for such such options to do anything useful. The intention is that we'll add hooks for that purpose in a separate commit. Discussion: http://postgr.es/m/ca+tgmoyszg58hpubmei46o8d3skx+szoo4k_agqgwirzvra...@mail.gmail.com Reviewed-by: Srinath Reddy Reviewed-by: Andrei Lepikhov Reviewed-by: Tom Lane Reviewed-by: Sami Imseih Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c65bc2e1d14a2d4daed7c1921ac518f2c5ac3d17 Modified Files -- contrib/auto_explain/auto_explain.c | 1 + contrib/file_fdw/file_fdw.c | 3 +- contrib/postgres_fdw/postgres_fdw.c | 2 +- src/backend/commands/Makefile | 1 + src/backend/commands/createas.c | 2 + src/backend/commands/explain.c| 143 + src/backend/commands/explain_dr.c | 1 + src/backend/commands/explain_format.c | 1 + src/backend/commands/explain_state.c | 371 ++ src/backend/commands/meson.build | 1 + src/backend/commands/prepare.c| 2 + src/backend/executor/execAmi.c| 1 + src/backend/tcop/pquery.c | 1 + src/include/commands/explain.h| 80 ++-- src/include/commands/explain_state.h | 95 + src/include/commands/prepare.h| 3 +- src/include/nodes/extensible.h| 2 +- src/tools/pgindent/typedefs.list | 2 + 18 files changed, 503 insertions(+), 209 deletions(-)
pgsql: Fix headerscheck warning.
Fix headerscheck warning. Reported-by: Tom Lane Discussion: https://postgr.es/m/93731.1742310...@sss.pgh.pa.us Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/549ea06e4217aca10d3a73dc09cf5018c51bc23a Modified Files -- src/common/unicode/generate-unicode_case_table.pl | 14 -- src/common/unicode_case.c | 11 +++ src/include/common/unicode_case_table.h | 11 --- 3 files changed, 11 insertions(+), 25 deletions(-)
pgsql: Use correct variable name in publicationcmds.c.
Use correct variable name in publicationcmds.c. subid was used at few places for publicationid in publicationcmds.c/.h. Author: vignesh C Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/caldanm1kqj0vffdjrpbfyi9shz6lhfee-ckn+eqsepfkheb...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/01e27aab05f5f83ab4b79688795848d787dab738 Modified Files -- src/backend/commands/publicationcmds.c | 12 ++-- src/include/commands/publicationcmds.h | 2 +- 2 files changed, 7 insertions(+), 7 deletions(-)
pgsql: Add X25519 to the default set of curves
Add X25519 to the default set of curves Since many clients default to the X25519 curve in the TLS handshake, the fact that the server by defualt doesn't support it cause an extra roundtrip for each TLS connection. By adding multiple curves, which is supported since 3d1ef3a15c3eb68da, we can reduce the risk of extra roundtrips. Author: Daniel Gustafsson Co-authored-by: Jacob Champion Reported-by: Andres Freund Reviewed-by: Jacob Champion Discussion: https://postgr.es/m/20240616234612.6cslu7nqexquv...@awork3.anarazel.de Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/daa02c6bd9262adeb44f4a9ed9d94fa6259afd94 Modified Files -- doc/src/sgml/config.sgml | 2 +- src/backend/utils/misc/guc_tables.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- src/test/ssl/t/SSL/Server.pm | 2 +- 4 files changed, 4 insertions(+), 4 deletions(-)
pgsql: Silence compiler warning.
Silence compiler warning. Assorted buildfarm members are complaining about "'process_list' may be used uninitialized in this function" since f76892c9f, presumably because they don't trust that the switch case labels are exhaustive. We can silence that by initializing the variable to NULL. Should a switch fall-through actually happen, we'll get SIGSEGV at the first use, which is as good as an Assert. Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/4078da6c478039bbafdd58138957dc47db545935 Modified Files -- src/bin/scripts/reindexdb.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
Re: pgsql: aio: Add core asynchronous I/O infrastructure
Andres Freund writes: > aio: Add core asynchronous I/O infrastructure Some of the buildfarm is mildly unhappy with this. So far I see ayu | 2025-03-18 13:08:04 | aio_callback.c:83:12: warning: comparison of constant 1 with expression of type 'PgAioHandleCallbackID' (aka 'enum PgAioHandleCallbackID') is always false [-Wtautological-constant-out-of-range-compare] ayu | 2025-03-18 13:08:04 | aio_callback.c:190:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] ayu | 2025-03-18 13:08:04 | aio_callback.c:220:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] ayu | 2025-03-18 13:08:04 | aio_callback.c:274:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] ayu | 2025-03-18 13:08:04 | aio_target.c:50:41: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] ayu | 2025-03-18 13:08:04 | aio_target.c:110:41: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_callback.c:83:12: warning: comparison of constant 1 with expression of type 'PgAioHandleCallbackID' (aka 'enum PgAioHandleCallbackID') is always false [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_callback.c:190:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_callback.c:220:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_callback.c:274:56: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_target.c:50:41: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] demoiselle| 2025-03-18 03:43:46 | aio_target.c:110:41: warning: comparison of constant 1 with expression of type 'PgAioTargetID' (aka 'enum PgAioTargetID') is always true [-Wtautological-constant-out-of-range-compare] I don't have that compiler handy to check, but maybe changes like - if (cb_id >= lengthof(aio_handle_cbs)) + if ((size_t) cb_id >= lengthof(aio_handle_cbs)) would silence these? Or you could just drop all those tests and have faith in the type-safety of the enums. regards, tom lane
pgsql: Increase io_combine_limit range to 1MB.
Increase io_combine_limit range to 1MB. The default of 128kB is unchanged, but the upper limit is changed from 32 blocks to 128 blocks, unless the operating system's IOV_MAX is too low. Some other RDBMSes seem to cap their multi-block buffer pool I/O around this number, and it seems useful to allow experimentation. The concrete change is to our definition of PG_IOV_MAX, which provides the maximum for io_combine_limit and io_max_combine_limit. It also affects a couple of other places that work with arrays of struct iovec or smaller objects on the stack, so we still don't want to use the system IOV_MAX directly without a clamp: it is not under our control and likely to be 1024. 128 seems acceptable for our current usage. For Windows, we can't use real scatter/gather yet, so we continue to define our own IOV_MAX value of 16 and emulate preadv()/pwritev() with loops. Someone would need to research the trade-offs of raising that number. NB if trying to see this working: you might temporarily need to hack BAS_BULKREAD to be bigger, since otherwise the obvious way of "a very big SELECT" is limited by that for now. Suggested-by: Tomas Vondra Discussion: https://postgr.es/m/CA%2BhUKG%2B2T9p-%2BzM6Eeou-RAJjTML6eit1qn26f9twznX59qtCA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/06fb5612c970b3af95aca3db5a955669b07537ca Modified Files -- doc/src/sgml/config.sgml | 4 src/backend/storage/aio/read_stream.c | 7 --- src/backend/utils/misc/postgresql.conf.sample | 4 ++-- src/include/port/pg_iovec.h | 8 ++-- 4 files changed, 16 insertions(+), 7 deletions(-)
pgsql: Introduce io_max_combine_limit.
Introduce io_max_combine_limit. The existing io_combine_limit can be changed by users. The new io_max_combine_limit is fixed at server startup time, and functions as a silent clamp on the user setting. That in itself is probably quite useful, but the primary motivation is: aio_init.c allocates shared memory for all asynchronous IOs including some per-block data, and we didn't want to waste memory you'd never used by assuming they could be up to PG_IOV_MAX. This commit already halves the size of 'AioHandleIov' and 'AioHandleData'. A follow-up commit can now expand PG_IOV_MAX without affecting that. Since our GUC system doesn't support dependencies or cross-checks between GUCs, the user-settable one now assigns a "raw" value to io_combine_limit_guc, and the lower of io_combine_limit_guc and io_max_combine_limit is maintained in io_combine_limit. Reviewed-by: Andres Freund (earlier version) Discussion: https://postgr.es/m/CA%2BhUKG%2B2T9p-%2BzM6Eeou-RAJjTML6eit1qn26f9twznX59qtCA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/10f6646847515b1ab02735c24b04abaf1996f65f Modified Files -- doc/src/sgml/config.sgml | 23 ++- src/backend/commands/variable.c | 18 ++ src/backend/storage/aio/aio_init.c| 17 ++--- src/backend/storage/buffer/bufmgr.c | 5 - src/backend/utils/misc/guc_tables.c | 16 +++- src/backend/utils/misc/postgresql.conf.sample | 2 ++ src/include/storage/bufmgr.h | 4 +++- src/include/utils/guc_hooks.h | 2 ++ 8 files changed, 72 insertions(+), 15 deletions(-)
pgsql: Ensure first ModifyTable rel initialized if all are pruned
Ensure first ModifyTable rel initialized if all are pruned Commit cbc127917e introduced tracking of unpruned relids to avoid processing pruned relations, and changed ExecInitModifyTable() to initialize only unpruned result relations. As a result, MERGE statements that prune all target partitions can now lead to crashes or incorrect behavior during execution. The crash occurs because some executor code paths rely on ModifyTableState.resultRelInfo[0] being present and initialized, even when no result relations remain after pruning. For example, ExecMerge() and ExecMergeNotMatched() use the first resultRelInfo to determine the appropriate action. Similarly, ExecInitPartitionInfo() assumes that at least one result relation exists. To preserve these assumptions, ExecInitModifyTable() now includes the first result relation in the initialized result relation list if all result relations for that ModifyTable were pruned. To enable that, ExecDoInitialPruning() ensures the first relation is locked if it was pruned and locking is necessary. To support this exception to the pruning logic, PlannedStmt now includes a list of RT indexes identifying the first result relation of each ModifyTable node in the plan. This allows ExecDoInitialPruning() to check whether each such relation was pruned and, if so, lock it if necessary. Bug: #18830 Reported-by: Robins Tharakan Diagnozed-by: Tender Wang Diagnozed-by: Dean Rasheed Co-authored-by: Dean Rasheed Reviewed-by: Tender Wang Reviewed-by: Dean Rasheed Discussion: https://postgr.es/m/18830-1f31ea1dc930d444%40postgresql.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/28317de723b60b61c40e7de4341a3029f698ddaf Modified Files -- src/backend/commands/explain.c| 14 - src/backend/executor/execMain.c | 2 +- src/backend/executor/execPartition.c | 31 +- src/backend/executor/execUtils.c | 18 +++--- src/backend/executor/nodeModifyTable.c| 32 +-- src/backend/optimizer/plan/planner.c | 1 + src/backend/optimizer/plan/setrefs.c | 3 + src/include/executor/execPartition.h | 4 +- src/include/executor/executor.h | 3 +- src/include/nodes/pathnodes.h | 3 + src/include/nodes/plannodes.h | 7 +++ src/test/regress/expected/partition_prune.out | 82 +++ src/test/regress/sql/partition_prune.sql | 32 +++ 13 files changed, 214 insertions(+), 18 deletions(-)
pgsql: Add commit 796bdda484 to .git-blame-ignore-revs.
Add commit 796bdda484 to .git-blame-ignore-revs. Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/7fb418f020815a146c5c9f33e3b344ea9d6df3c7 Modified Files -- .git-blame-ignore-revs | 3 +++ 1 file changed, 3 insertions(+)
Re: pgsql: Increase default maintenance_io_concurrency to 16
This commit makes our default random_page_cost = 4 out of line with these new settings (assumes modern SSD/NAS/SAN hardware) and more out of line with reality. --- On Tue, Mar 18, 2025 at 01:08:47PM +, Melanie Plageman wrote: > Increase default maintenance_io_concurrency to 16 > > Since its introduction in fc34b0d9de27a, the default > maintenance_io_concurrency has been larger than the default > effective_io_concurrency. maintenance_io_concurrency primarily > controlled prefetching done on behalf of the whole system, for > operations like recovery. Therefore it makes sense for it to have a > value equal to or greater than effective_io_concurrency, which controls > I/O concurrency for reading a relation in a bitmap heap scan. > > ff79b5b2ab increased effective_io_concurrency to 16, so we'll increase > maintenance_io_concurrency as well. For now, though, we'll keep the > defaults of effective_io_concurrency and maintenance_io_concurrency > equal to one another (16). > > On fast, high IOPs systems, significantly higher values of > maintenance_io_concurrency are observably beneficial [1]. However, such > values would flood low IOPs systems and increase overall system I/O > latency. > > It is worth mentioning that since 9256822608f and c3e775e608f, > maintenance_io_concurrency also controls the I/O concurrency of each > vacuum worker. Since many autovacuum workers may be simultaneously > issuing I/Os, we want to keep maintenance_io_concurrency appropriately > conservative. > > [1] > https://postgr.es/m/c5d52837-6256-0556-ac8c-d6d3d558820a%40enterprisedb.com > > Suggested-by: Jakub Wartak > Discussion: > https://postgr.es/m/CAKZiRmxdHQaU%2B2Zpe6d%3Dx%3D0vigJ1sfWwwVYLJAf%3Dud_wQ_VcUw%40mail.gmail.com > > Branch > -- > master > > Details > --- > https://git.postgresql.org/pg/commitdiff/cc6be07ebde2aef7cc0507f997f563ce77c00a00 > > Modified Files > -- > doc/src/sgml/config.sgml | 8 > src/backend/utils/misc/postgresql.conf.sample | 2 +- > src/include/storage/bufmgr.h | 2 +- > 3 files changed, 6 insertions(+), 6 deletions(-) > -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
pgsql: Optimize check for pending backend IO stats
Optimize check for pending backend IO stats This commit changes the backend stats code so as we rely on a single boolean rather than a repeated check based on pg_memory_is_all_zeros() in the code, making it cheaper should PgStat_PendingIO get bigger in size. The frequency of backend stats reports is not a bottleneck, but there is no reason to not make that cheaper, and the logic is simple as the only entry points updating backend IO stats are pgstat_count_backend_io_op() and pgstat_count_backend_io_op_time(). Author: Bertrand Drouvot Reviewed-by: Xuneng Zhou Discussion: https://postgr.es/m/z8wyf1jyy4mwo...@ip-10-97-1-34.eu-west-3.compute.internal Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/6d3ea48ff1aea5fb1ccfed69424bf93a8643b4a4 Modified Files -- src/backend/utils/activity/pgstat_backend.c | 19 +++ 1 file changed, 11 insertions(+), 8 deletions(-)
Re: pgsql: aio: Add core asynchronous I/O infrastructure
Andres Freund writes: > I wonder if we should instead either ask those buildfarm animals to be > disabled or have the warning manually disabled. I don't think it's a good > investment on our part to work towards warning cleanliness on clang 4 and 5, > on distros from 2017 and and 2018 respectively. Well, if we don't want to fix it I can just adjust my warning-scraping script to ignore these. Let's wait a bit and see if any newer compilers warn similarly before deciding. regards, tom lane
pgsql: Fix assertion failure in parallel vacuum with minimal maintenanc
Fix assertion failure in parallel vacuum with minimal maintenance_work_mem setting. bbf668d66fbf lowered the minimum value of maintenance_work_mem to 64kB. However, in parallel vacuum cases, since the initial underlying DSA size is 256kB, it attempts to perform a cycle of index vacuuming and table vacuuming with an empty TID store, resulting in an assertion failure. This commit ensures that at least one page is processed before index vacuuming and table vacuuming begins. Backpatch to 17, where the minimum maintenance_work_mem value was lowered. Reviewed-by: David Rowley Discussion: https://postgr.es/m/cad21aoceambkkxskbj4db+5pjdrl4zhxrcilbges_g_g8mv...@mail.gmail.com Backpatch-through: 17 Branch -- REL_17_STABLE Details --- https://git.postgresql.org/pg/commitdiff/a38dce3c4a90aa0facef350bc6a502f31b0cfa31 Modified Files -- src/backend/access/heap/vacuumlazy.c | 7 +-- src/test/regress/expected/vacuum.out | 12 src/test/regress/sql/vacuum.sql | 13 + 3 files changed, 30 insertions(+), 2 deletions(-)
pgsql: Fix assertion failure in parallel vacuum with minimal maintenanc
Fix assertion failure in parallel vacuum with minimal maintenance_work_mem setting. bbf668d66fbf lowered the minimum value of maintenance_work_mem to 64kB. However, in parallel vacuum cases, since the initial underlying DSA size is 256kB, it attempts to perform a cycle of index vacuuming and table vacuuming with an empty TID store, resulting in an assertion failure. This commit ensures that at least one page is processed before index vacuuming and table vacuuming begins. Backpatch to 17, where the minimum maintenance_work_mem value was lowered. Reviewed-by: David Rowley Discussion: https://postgr.es/m/cad21aoceambkkxskbj4db+5pjdrl4zhxrcilbges_g_g8mv...@mail.gmail.com Backpatch-through: 17 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/f4290f20dd4d6f75e01fbb87304c3b8f31d1cfea Modified Files -- src/backend/access/heap/vacuumlazy.c | 7 +-- src/test/regress/expected/vacuum.out | 12 src/test/regress/sql/vacuum.sql | 13 + 3 files changed, 30 insertions(+), 2 deletions(-)
Re: pgsql: Increase default maintenance_io_concurrency to 16
Hi, On 2025-03-18 16:08:22 -0400, Bruce Momjian wrote: > This commit makes our default random_page_cost = 4 out of line with > these new settings (assumes modern SSD/NAS/SAN hardware) and more out of > line with reality. How so? That seems like an independent consideration to me. Greetings, Andres Freund
pgsql: Introduce squashing of constant lists in query jumbling
Introduce squashing of constant lists in query jumbling pg_stat_statements produces multiple entries for queries like SELECT something FROM table WHERE col IN (1, 2, 3, ...) depending on the number of parameters, because every element of ArrayExpr is individually jumbled. Most of the time that's undesirable, especially if the list becomes too large. Fix this by introducing a new GUC query_id_squash_values which modifies the node jumbling code to only consider the first and last element of a list of constants, rather than each list element individually. This affects both the query_id generated by query jumbling, as well as pg_stat_statements query normalization so that it suppresses printing of the individual elements of such a list. The default value is off, meaning the previous behavior is maintained. Author: Dmitry Dolgov <9erthali...@gmail.com> Reviewed-by: Sergey Dudoladov (mysterious, off-list) Reviewed-by: David Geier Reviewed-by: Robert Haas Reviewed-by: Álvaro Herrera Reviewed-by: Sami Imseih Reviewed-by: Sutou Kouhei Reviewed-by: Tom Lane Reviewed-by: Michael Paquier Reviewed-by: Marcos Pegoraro Reviewed-by: Julien Rouhaud Reviewed-by: Zhihong Yu Tested-by: Yasuo Honda Tested-by: Sergei Kornilov Tested-by: Maciek Sakrejda Tested-by: Chengxi Sun Tested-by: Jakub Wartak Discussion: https://postgr.es/m/ca+q6zcwtubt_sxj0v6hy6ez89uv5wug5aefpe_9n0jr3vwn...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/62d712ecfd940f60e68bde5b6972b6859937c412 Modified Files -- contrib/pg_stat_statements/Makefile | 2 +- contrib/pg_stat_statements/expected/squashing.out | 464 ++ contrib/pg_stat_statements/meson.build| 1 + contrib/pg_stat_statements/pg_stat_statements.c | 76 +++- contrib/pg_stat_statements/sql/squashing.sql | 180 + doc/src/sgml/config.sgml | 30 ++ doc/src/sgml/pgstatstatements.sgml| 24 +- src/backend/nodes/gen_node_support.pl | 19 +- src/backend/nodes/queryjumblefuncs.c | 146 ++- src/backend/postmaster/launch_backend.c | 3 + src/backend/utils/misc/guc_tables.c | 10 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/nodes/nodes.h | 2 + src/include/nodes/primnodes.h | 2 +- src/include/nodes/queryjumble.h | 7 + 15 files changed, 945 insertions(+), 22 deletions(-)
pgsql: smgr: Make SMgrRelation initialization safer against errors
smgr: Make SMgrRelation initialization safer against errors In case the smgr_open callback failed, the ->pincount field would not be initialized and the relation would not be put onto the unpinned_relns list. This buglet was introduced in 21d9c3ee4ef7, in 17. Discussion: https://postgr.es/m/3vae7l5ozvqtxmd7rr7zaeq3qkuipz365u3rtim5t5wdkr6f4g@vkgf2fogjirl Backpatch-through: 17 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/499faf9063a4c5a9985d7ac657b86e8e34e02199 Modified Files -- src/backend/storage/smgr/smgr.c | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-)
pgsql: smgr: Make SMgrRelation initialization safer against errors
smgr: Make SMgrRelation initialization safer against errors In case the smgr_open callback failed, the ->pincount field would not be initialized and the relation would not be put onto the unpinned_relns list. This buglet was introduced in 21d9c3ee4ef7, in 17. Discussion: https://postgr.es/m/3vae7l5ozvqtxmd7rr7zaeq3qkuipz365u3rtim5t5wdkr6f4g@vkgf2fogjirl Backpatch-through: 17 Branch -- REL_17_STABLE Details --- https://git.postgresql.org/pg/commitdiff/ee578921b60ef9a14eaea54b608549e4f8b14f26 Modified Files -- src/backend/storage/smgr/smgr.c | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-)
pgsql: Doc: manually break lines in wide UUID examples.
Doc: manually break lines in wide UUID examples. Buildfarm member crake has been complaining "WARNING: The contents of fo:inline line 1 exceed the available area in the inline-progression direction by 20500 millipoints. (See position 23808:106)" since ba57dcfdc went in. The other doc-building animals are not showing this warning, and I don't see it on my RHEL8 workstation either, but I was able to reproduce it on a Fedora 41 box. So apparently this is due to a recent-ish change in DocBook's line-breaking heuristics, which caused it to cope less well with the UUIDs in these examples. Put in some zero-width spaces to encourage the PDF toolchain to break these lines in a better place. (Only one of these examples actually needs this today, but I marked up all three to ensure that they get wrapped in a consistent way.) Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/a6524105d20b190fb4f5b2116e044e29be88f215 Modified Files -- doc/src/sgml/func.sgml | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-)
pgsql: Update guidance for running vacuumdb after pg_upgrade.
Update guidance for running vacuumdb after pg_upgrade. Now that pg_upgrade can carry over most optimizer statistics, we should recommend using vacuumdb's new --missing-stats-only option to only analyze relations that are missing statistics. Reviewed-by: John Naylor Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c9d502eb68094d817fe94c2e954f6fdbb62f4d48 Modified Files -- doc/src/sgml/ref/pgupgrade.sgml | 9 + src/bin/pg_upgrade/check.c | 2 +- 2 files changed, 6 insertions(+), 5 deletions(-)
pgsql: vacuumdb: Add option for analyzing only relations missing stats.
vacuumdb: Add option for analyzing only relations missing stats. This commit adds a new --missing-stats-only option that can be used with --analyze-only or --analyze-in-stages. When this option is specified, vacuumdb will analyze a relation if it lacks any statistics for a column, expression index, or extended statistics object. This new option is primarily intended for use after pg_upgrade (since it can now retain most optimizer statistics), but it might be useful in other situations, too. Author: Corey Huinker Co-authored-by: Nathan Bossart Reviewed-by: John Naylor Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/edba754f052ea0c02287080004aa6051eaa4f597 Modified Files -- doc/src/sgml/ref/vacuumdb.sgml | 16 + src/bin/scripts/t/100_vacuumdb.pl| 60 src/bin/scripts/vacuumdb.c | 114 ++- src/test/perl/PostgreSQL/Test/Cluster.pm | 27 4 files changed, 215 insertions(+), 2 deletions(-)
pgsql: oauth: Fix postcondition for set_timer on macOS
oauth: Fix postcondition for set_timer on macOS On macOS, readding an EVFILT_TIMER to a kqueue does not appear to clear out previously queued timer events, so checks for timer expiration do not work correctly during token retrieval. Switching to IPv4-only communication exposes the problem, because libcurl is no longer clearing out other timeouts related to Happy Eyeballs dual-stack handling. Fully remove and re-register the kqueue timer events during each call to set_timer(), to clear out any stale expirations. Author: Jacob Champion Discussion: https://postgr.es/m/CAOYmi%2Bn4EDOOUL27_OqYT2-F2rS6S%2B3mK-ppWb2Ec92UEoUbYA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/434dbf6907ec8fafa6862a0f00385f293e63ac0e Modified Files -- src/interfaces/libpq/fe-auth-oauth-curl.c | 48 ++- 1 file changed, 35 insertions(+), 13 deletions(-)
pgsql: oauth: Use IPv4-only issuer in oauth_validator tests
oauth: Use IPv4-only issuer in oauth_validator tests The test authorization server implemented in oauth_server.py does not listen on IPv6. Most of the time, libcurl happily falls back to IPv4 after failing its initial connection, but on NetBSD, something is consistently showing up on the unreserved IPv6 port and causing a test failure. Rather than deal with dual-stack details across all test platforms, change the issuer to enforce the use of IPv4 only. (This elicits more punishing timeout behavior from libcurl, so it's a useful change from the testing perspective as well.) Author: Jacob Champion Reported-by: Thomas Munro Discussion: https://postgr.es/m/CAOYmi%2Bn4EDOOUL27_OqYT2-F2rS6S%2B3mK-ppWb2Ec92UEoUbYA%40mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/8d9d5843b55f47d24031165f99b07d41715b93e9 Modified Files -- src/test/modules/oauth_validator/t/001_server.pl | 2 +- src/test/modules/oauth_validator/t/OAuth/Server.pm | 4 ++-- src/test/modules/oauth_validator/t/oauth_server.py | 2 +- 3 files changed, 4 insertions(+), 4 deletions(-)
pgsql: oauth: Disallow synchronous DNS in libcurl
oauth: Disallow synchronous DNS in libcurl There is concern that a blocking DNS lookup in libpq could stall a backend process (say, via FDW). Since there's currently no strong evidence that synchronous DNS is a popular option, disallow it entirely rather than warning at configure time. We can revisit if anyone complains. Per query from Andres Freund. Author: Jacob Champion Discussion: https://postgr.es/m/p4bd7mn6dxr2zdak74abocyltpfdxif4pxqzixqpxpetjwt34h%40qc6jgfmoddvq Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/d7e40845f923ed5091b334b1018a547e39846415 Modified Files -- config/programs.m4 | 10 +- configure | 14 +- meson.build| 18 ++ 3 files changed, 16 insertions(+), 26 deletions(-)
pgsql: oauth: Improve validator docs on interruptibility
oauth: Improve validator docs on interruptibility Andres pointed out that EINTR handling is inadequate for real-world use cases. Direct module writers to our wait APIs instead. Author: Jacob Champion Discussion: https://postgr.es/m/p4bd7mn6dxr2zdak74abocyltpfdxif4pxqzixqpxpetjwt34h%40qc6jgfmoddvq Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/873c0fd67872426e88ac07cbb7ae07457416aafa Modified Files -- doc/src/sgml/oauth-validators.sgml | 12 +++- 1 file changed, 7 insertions(+), 5 deletions(-)
pgsql: oauth: Simplify copy of PGoauthBearerRequest
oauth: Simplify copy of PGoauthBearerRequest Follow-up to 03366b61d. Since there are no more const members in the PGoauthBearerRequest struct, the previous memcpy() can be replaced with simple assignment. Author: Jacob Champion Discussion: https://postgr.es/m/p4bd7mn6dxr2zdak74abocyltpfdxif4pxqzixqpxpetjwt34h%40qc6jgfmoddvq Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/1cf4c56480f883bec50753e092da51ceaf3cea67 Modified Files -- src/interfaces/libpq/fe-auth-oauth.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Fix compiler warning for commit 434dbf69.
Fix compiler warning for commit 434dbf69. Reported-by: Tom Lane Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/0b53c08677a6515786bde9d4471b42ef7289759e Modified Files -- src/interfaces/libpq/fe-auth-oauth-curl.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: vacuumdb: Teach vacuum_one_database() to reuse query results.
vacuumdb: Teach vacuum_one_database() to reuse query results. Presently, each call to vacuum_one_database() queries the catalogs to retrieve the list of tables to process. A follow-up commit will add a "missing stats only" feature to --analyze-in-stages, which requires saving the catalog query results (since tables without statistics will have them after the first stage). This commit adds a new parameter to vacuum_one_database() that specifies either a previously-retrieved list or a place to return the catalog query results. Note that nothing uses this new parameter yet. Author: Corey Huinker Co-authored-by: Nathan Bossart Reviewed-by: John Naylor Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/9c03c8d187210f112371aab881b379bd4800562c Modified Files -- src/bin/scripts/vacuumdb.c | 330 ++--- 1 file changed, 194 insertions(+), 136 deletions(-)
pgsql: psql: Allow queries terminated by semicolons while in pipeline m
psql: Allow queries terminated by semicolons while in pipeline mode Currently, the only way to pipe queries in an ongoing pipeline (in a \startpipeline block) is to leverage the meta-commands able to create extended queries such as \bind, \parse or \bind_named. While this is good enough for testing the backend with pipelines, it has been mentioned that it can also be very useful to allow queries terminated by semicolons to be appended to a pipeline. For example, it would be possible to migrate existing psql scripts to use pipelines by just adding a set of \startpipeline and \endpipeline meta-commands, making such scripts more efficient. Doing such a change is proving to be simple in psql: queries terminated by semicolons can be executed through PQsendQueryParams() without any parameters set when the pipeline mode is active, instead of PQsendQuery(), the default, like pgbench. \watch is still forbidden while in a pipeline, as it expects its results to be processed synchronously. The large portion of this commit consists in providing more test coverage, with mixes of extended queries appended in a pipeline by \bind and friends, and queries terminated by semicolons. This improvement has been suggested by Daniel Vérité. Author: Anthonin Bonnefoy Discussion: https://postgr.es/m/d67b9c19-d009-4a50-8020-1a0ea9236...@manitou-mail.org Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/2cce0fe440fb3f252a7be70a89298168009a2c15 Modified Files -- doc/src/sgml/ref/psql-ref.sgml | 27 ++- src/bin/psql/command.c | 7 + src/bin/psql/common.c | 10 +- src/test/regress/expected/psql_pipeline.out | 252 +--- src/test/regress/sql/psql_pipeline.sql | 110 ++-- 5 files changed, 359 insertions(+), 47 deletions(-)
pgsql: Fix copy-paste error related to the autovacuum launcher in pgsta
Fix copy-paste error related to the autovacuum launcher in pgstat_io.c Autovacuum launchers perform no WAL IO reads, but pgstat_tracks_io_op() was tracking them as an allowed combination for the "init" and "normal" contexts. This caused the "read", "read_bytes" and "read_time" attributes of pg_stat_io to show zeros for the autovacuum launcher rather than NULL. NULL means that a combination of IO object, IO context and IO operation has no meaning for a backend type. Zero is the same as telling that a combination is relevant, and that WAL reads are possible in an autovacuum launcher, but it is not relevant. Copy-pasto introduced in a051e71e28a1. Author: Ranier Vilela Reviewed-by: Nazir Bilal Yavuz Discussion: https://postgr.es/m/caeudqaopemapiuqe7bvdv+x2fupmkmb9rrsaodr+hhqzlkg...@mail.gmail.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/17d8bba6dad12e14a7cafca9ef5eef21e577e9c3 Modified Files -- src/backend/utils/activity/pgstat_io.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: extension_control_path
extension_control_path The new GUC extension_control_path specifies a path to look for extension control files. The default value is $system, which looks in the compiled-in location, as before. The path search uses the same code and works in the same way as dynamic_library_path. Some use cases of this are: (1) testing extensions during package builds, (2) installing extensions outside security-restricted containers like Python.app (on macOS), (3) adding extensions to PostgreSQL running in a Kubernetes environment using operators such as CloudNativePG without having to rebuild the base image for each new extension. There is also a tweak in Makefile.global so that it is possible to install extensions using PGXS into an different directory than the default, using 'make install prefix=/else/where'. This previously only worked when specifying the subdirectories, like 'make install datadir=/else/where/share pkglibdir=/else/where/lib', for purely implementation reasons. (Of course, without the path feature, installing elsewhere was rarely useful.) Author: Peter Eisentraut Co-authored-by: Matheus Alcantara Reviewed-by: David E. Wheeler Reviewed-by: Gabriele Bartolini Reviewed-by: Marco Nenciarini Reviewed-by: Niccolò Fei Discussion: https://www.postgresql.org/message-id/flat/e7c7bffb-8857-48d4-a71f-88b359fad...@justatheory.com Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/4f7f7b0375854e2f89876473405a8f21c95012af Modified Files -- doc/src/sgml/config.sgml | 68 doc/src/sgml/extend.sgml | 19 +- doc/src/sgml/ref/create_extension.sgml | 6 +- src/Makefile.global.in | 19 +- src/backend/commands/extension.c | 403 + src/backend/utils/fmgr/dfmgr.c | 77 ++-- src/backend/utils/misc/guc_tables.c| 13 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/commands/extension.h | 2 + src/include/fmgr.h | 3 + src/test/modules/test_extensions/Makefile | 1 + src/test/modules/test_extensions/meson.build | 5 + .../t/001_extension_control_path.pl| 80 13 files changed, 512 insertions(+), 185 deletions(-)
