01 index prefetching Patch Review 99 msgs Nov 19, 23:29
This extensive thread details the development and refinement of index prefetching, including architectural changes and bug fixes. Early discussions focused on testing methodologies for varying storage latencies and measuring 'unconsumed IO.' The proposer later raised a concern about an incomplete GiST cleanup-lock fix on standbys, comparing it to a known nbtree issue. Subsequent patch versions (v36-v38) introduced a significant redesign of hashgetbatch for simpler sibling traversal, and an experimental patch for read stream request deduplication to resolve performance regressions caused by redundant block requests. The proposer also addressed performance inconsistencies under `io_method=sync` by suppressing `posix_fadvise` hints in that mode. Reviewer 1 provided detailed feedback on `tableam_index_pos_advance` logic, documentation, and a potential off-by-one error. Commenter 1 reported a segfault bug in SP-GiST index-only scans on released branches and proposed a back-portable fix. Commenter 2 offered substantial architectural feedback regarding the table AM interface, highlighting potential blocking issues for out-of-tree AMs due to new batch callbacks and per-item recheck requirements, and offered to contribute generic implementations.
02 Snapshot related assert failure on skink Proposed 16 msgs Nov 20, 08:29
The thread begins with a report of a snapshot-related assert failure on the buildfarm. The initial investigation by the proposer and a reviewer identified a bug where xactCompletionCount was not correctly updated during hot standby, leading to snapshot reuse with an older xmin. An experimental fix successfully prevented the initial failures, and a committer stated they would prepare and commit a patch. However, the latest message in the thread reports that the assertion can still be triggered via a different path, even after the initial fix was committed. This new reproduction mechanism, involving ProcArrayApplyRecoveryInfo() and `latestObservedXid`, is described, along with a proposed fix to address it.
03 Use WALReadFromBuffers in more places Patch Review 26 msgs Nov 20, 02:29
This long-running thread aims to enhance WAL reading efficiency by enabling `WALReadFromBuffers` in more contexts, particularly for walsenders and the WAL summarizer, allowing them to opportunistically read WAL directly from memory buffers instead of files. The initial patches focused on implementing this optimization. Benchmarking showed significant reductions in disk I/O for walsenders, especially those closely following the publisher. A key challenge arose when tests expecting disk reads started failing due to all WAL being served from buffers; this led to the introduction of `IOOP_HIT` tracking in `pg_stat_io` to distinguish buffer hits from file reads. Discussions also refined how these "hits" are counted (per page rather than per function call) and addressed potential issues with `WAL` segment tracking (`ws_tli`) when file reads are bypassed. One patch (0001) was committed, and subsequent patches incorporate fixes and refinements for the remaining components.
04 Disable startup progress timeout during standby WAL replay Patch Review 12 msgs Nov 20, 07:28
This thread addresses an inefficiency in PostgreSQL's startup process where the startup progress timeout remains active during standby WAL replay, leading to unnecessary periodic wakeups. The original intent, as described in code comments, was to suppress recovery progress logging in standby mode to avoid log bloat. The proposer's patch aims to explicitly disable this timeout before entering WAL replay in standby mode, ensuring that early startup operations still report progress while preventing superfluous activity during extended replay. Discussions focused on the optimal placement for the `disable_startup_progress_timeout()` call, considering various recovery paths and how other functions like `ResetUnloggedRelations()` might re-enable the timeout. An unrelated issue regarding `disable_all_timeouts(false)` was identified and subsequently split into a separate thread for independent review. The latest patch version includes clarified comments, a note about standby progress during initial crash recovery, and a fix for a minor bug where changing `log_startup_progress_interval` to zero during initial crash recovery might not properly stop the timer.
05 [RFC] Lock-free XLog Reservation from WAL Patch Review 48 msgs Nov 19, 15:29
This thread discusses the "Lock-free XLog Reservation from WAL" to reduce lock contention during WAL insertions on systems with many virtual CPUs. The initial proposal outlined a multi-faceted approach, including removing `PrevBytePos` for atomic `CurrBytePos` updates, updating the prev-link of the next XLog, and breaking sequential write convention, along with increasing `NUM_XLOGINSERT_LOCKS`. Early patch versions and performance tests, particularly when `NUM_XLOGINSERT_LOCKS` remained at the default 8, sometimes showed performance decreases or mixed results, indicating that the new complexity might not always outperform the existing spinlock. The patch was iterated on to fix issues like a null pointer dereference on Windows. Much later, the proposer submitted v8 of a CAS128 patch, which aims to update `CurrBytePos` and `PrevBytePos` atomically, eliminating the need for a complex predecessor hash table. This v8 patch, which includes fallback to spinlocks if native CAS128 is unavailable, showed significant performance gains (up to +40.4% with 64 locks) on high-core count Intel Xeon systems for HammerDB TPROC-C workloads. The proposer noted that increasing locks might still regress a specific `pg_logical_emit_message` workload.
06 A bug in recovery prefetcher, triggering asserts / FATAL: checkpoint request failed Committed 11 msgs Nov 20, 10:28
The proposer identified and fixed a pre-existing bug in the recovery prefetcher, present since PG 15. The bug caused XLogPrefetcherBeginRead to not reset prefetcher->record, leading to XLogPrefetcherReadRecord being confused and triggering assert failures in development builds or "FATAL: checkpoint request failed" errors in regular builds, particularly with reduced maintenance_io_concurrency. The fix involved simply resetting prefetcher->record to NULL. The proposer provided a patch with the fix and a new TAP test to reproduce the issue. Reviewers confirmed the bug and the effectiveness of the fix, with one reviewer suggesting an additional check in FinishWalRecovery() as a separate improvement. The proposer committed and backported the fix, including the TAP test. However, buildfarm members later reported failures with the new test on platforms lacking posix_fadvise(), as these platforms reject non-zero maintenance_io_concurrency. The discussion then shifted to how to correctly skip the test on such unsupported platforms, particularly considering differences in prefetching support across PostgreSQL versions and operating systems like macOS. The proposer plans to add conditional skipping for older versions.
07 Assert "target_wal_endrec == divergerec" in pg_rewind Committed 6 msgs Nov 20, 08:29
The thread discusses an Assert("target_wal_endrec == divergerec") failure in pg_rewind that occurs when the target server is a direct ancestor of the source and stopped earlier on the common timeline. This assertion was previously identified as bogus. A patch was proposed to remove this assertion and include a TAP test for the scenario. Reviewers confirmed the assertion is indeed spurious and agreed that removing it is correct. The test case, initially integrated in a non-standard way, was refined and moved to a new dedicated file to cover important cases of direct ancestors based on backups. The removal of the assertion and the addition of the test are now slated for commitment.
08 [BUG] Take a long time to reach consistent after pg_rewind Patch Review 16 msgs Nov 20, 08:29
The thread discusses a bug where a server, after pg_rewind, can take an unboundedly long time to reach a consistent state if the source server is idle. This occurs because pg_rewind sets minRecoveryPoint using pg_current_wal_insert_lsn(), which can point past a WAL page header where no actual WAL record ends, making the LSN unreachable without further WAL activity. Several solutions were discussed: adjusting minRecoveryPoint to the beginning of the page, or using GetXLogInsertEndRecPtr() or the flush LSN. The consensus shifted towards using the flush LSN due to its crash-safety properties. The latest patch (v5) implements this, updates documentation, and passed review for correctness and backpatching suitability, though a regression test is still considered.
09 Don't use the deprecated and insecure PQcancel in our frontend tools anymore Committed 33 msgs Nov 19, 14:30
This thread addresses a security concern by replacing the insecure PQcancel function with newer, encryption-aware cancellation APIs in various PostgreSQL frontend tools like psql. The core change involves refactoring the cancellation logic to use a dedicated thread for sending cancel requests, achieving consistency across platforms. Initial discussions included the feasibility of backporting this complex change to recent PostgreSQL versions. During review, a subtle behavioral change was noted regarding psql's Ctrl+C handling in script mode, particularly during \copy operations. The proposer clarified that the new behavior, where Ctrl+C terminates the entire script, was an intentional fix for what was considered a bug. After addressing minor issues, including pager display, the first two patches in the series, which focused on this refactoring and behavior correction, were committed.
10 Hash aggregate collisions cause excessive spilling Patch Review 17 msgs Nov 20, 04:28
The thread addresses a critical performance bug in HashAggregate nodes where hash collisions cause excessive spilling, leading to queries getting stuck indefinitely. The initial problem was traced to aggstate->hash_metacxt exceeding work_mem, forcing frequent spills with an n² overhead. The proposer initially suggested a fix using parent->plan->plan_node_id as a hash initialization vector (hash_iv) to improve hash distribution. This solution, iterated upon with suggestions from reviewers, now uses hash_combine with murmurhash32 on the parallel worker number and plan node ID, and includes comments for ExecInitSubPlan. Reviewers confirmed the fix's effectiveness but also highlighted underlying issues like poor n_distinct statistics and peculiar hash function behavior. The discussion has evolved to also include improvements to hash_agg_check_limits() to prevent inefficient hash table resizing before spilling and better memory management for grouping sets. The issue is considered urgent, with suggestions for backpatching.
11 Report buffer access strategies in EXPLAIN Patch Review 23 msgs Nov 20, 09:28
The proposer initiated a patch series to enhance `EXPLAIN (ANALYZE, BUFFERS)` by reporting buffer access strategies (BAS), including whether normal buffer replacement or a ring buffer strategy is used. This builds on previous discussions and `pg_stat_io` already exposing similar data. Initial feedback from reviewer 1 included suggestions for documentation wording, identified an issue with `SampleScan` in parallel plans not reporting strategies for worker-executed scans, and raised concerns about assumptions in index scan instrumentation. A commenter questioned the consistency of terminology like "strategy ring". The proposer submitted v5 and v6 patches, incorporating some feedback and performing mechanical rebases. The discussion is ongoing regarding the precise wording for L2 cache descriptions in the documentation and whether specific strategy names should be explicitly listed or if a glossary link is sufficient, with reviewer 2 expressing reservations about some additions in patch 0003.
12 let ALTER TABLE DROP COLUMN drop whole-row referenced object Discussing 18 msgs Nov 19, 17:28
This thread discusses modifying `ALTER TABLE DROP COLUMN` to correctly handle objects (constraints, indexes, policies, triggers) that contain whole-row references. The proposer initially identified that such objects are not dropped automatically, leading to inconsistencies, contrary to documentation. An early patch aimed to rectify this by automatically dropping these whole-row dependent objects when a column is dropped and making `ALTER COLUMN SET DATA TYPE` error out if such dependencies exist. Reviews led to patch refinements, addressing implementation details and memory leaks. A reviewer confirmed the patch's correct behavior for various scenarios, including `CASCADE` and `ALTER COLUMN TYPE` errors. However, a commenter later raised a fundamental design concern, suggesting that the proposed scanning of system catalogs for whole-row references is inefficient. This commenter proposed an alternative approach involving registering whole-row `Vars` in `pg_depends` for proper invalidation and argued that whole-row indexes should be rebuilt rather than dropped when a column is modified. The latest reply further highlighted issues with silently dropping unique indexes and `CHECK` constraints, providing examples of data integrity problems and advocating for rebuilding or rechecking these objects.
13 Table AM callback for logical decoding support (REPACK CONCURRENTLY) Patch Review 4 msgs Nov 20, 09:28
The proposer identified a limitation where `REPACK (CONCURRENTLY)` is restricted to heap tables because the `TableAmRoutine` lacks a clear way to indicate if a Table Access Method (AM) supports logical decoding, potentially leading to silent data loss. The proposer proposed adding an optional `relation_supports_logical_decoding` callback to `TableAmRoutine` to address this. This would be the second such addition for out-of-tree AMs. The patch includes a test module to demonstrate its functionality. Commenter 1 supported the AM method approach but questioned the necessity of a `Relation` argument for the callback, as logical decoding support seems AM-level. Commenter 2 suggested an alternative of providing the AM's `RmgrId` to check for an `rm_decode()` callback. Commenter 3 raised a testing concern about `wal_level=minimal` and suggested setting `wal_level=replica` for tests.
14 Parallel GIN, BRIN builds fail to maintain ii_BrokenHotChain Patch Review 4 msgs Nov 20, 10:28
The proposer identified a bug in parallel GIN and BRIN index builds, where they fail to correctly maintain the ii_BrokenHotChain flag. This oversight means that if an individual parallel worker sets ii_BrokenHotChain, the information is not propagated to the leader process, leading to pg_index.indcheckxmin not being set correctly. Consequently, older snapshots might incorrectly use the index, risking inconsistent results. The proposer provided an isolation test, generated by an LLM, that demonstrates this incorrect behavior. Reviewer 1 acknowledged the issue and offered to fix it. A commenter then indicated they had encountered the exact same problem, had already developed a fix as part of a larger refactoring to prevent future similar issues, and confirmed that the proposer's test passed with their patch. Reviewer 1 confirmed they would use the provided fix.
15 [PATCH] Add planner support function to two-argument regexp_like() Patch Review 6 msgs Nov 20, 01:28
The proposer identified that regexp_like() (two-argument form) lacks a planner support function, unlike the ~ operator, leading to inefficient sequential scans instead of index scans for regular expressions with fixed prefixes. The initial patch aimed to link regexp_like() to the existing textregexeq_support() function, which can extract prefixes for B-tree index conditions. An early reviewer confirmed installcheck-world passed. The proposer explained the mechanism to another reviewer. A reviewer suggested unnecessary regression tests could be removed, which the proposer agreed to. However, a later reviewer found a regression in row-count estimation when expression index statistics are available for regexp_like(), causing the support function to return a default selectivity instead of using the statistics, leading to underestimates. The reviewer suggested addressing this problem.
16 psql: avoid over-reading unterminated prompt escapes Committed 7 msgs Nov 20, 09:28
The proposer reported a `psql` crash, specifically a heap-buffer-overflow, observed with AddressSanitizer when `PROMPT1` was set to an unterminated variable like `%:aa`. This issue stemmed from `psql` attempting to over-read characters beyond the allocated buffer for prompt escapes. After an initial patch submission, reviewer 1 suggested adding a TAP test for custom prompts, and commenter 1 provided a detailed review, confirming the fix's correctness across different versions and with ASan, and also supplied an optional TAP test. The committer decided against including the optional TAP test due to its unreliable bug detection without ASan and the associated maintenance overhead. Subsequently, the committer announced that the patch had been pushed, resolving the issue. The proposer expressed thanks for the commit.
17 EXPLAIN ANALYZE CREATE TABLE AS EXECUTE runs non-SELECT prepared statements Committed 4 msgs Nov 20, 05:29
The proposer identified a bug where EXPLAIN ANALYZE CREATE TABLE AS EXECUTE would incorrectly execute non-SELECT prepared statements, unlike CREATE TABLE AS EXECUTE which correctly rejects them. This could lead to unexpected data modifications and table creation with rows from non-SELECT statements (like INSERT ... RETURNING). The proposed patch adds the necessary check to ExplainExecuteQuery, aligning its behavior with CREATE TABLE AS EXECUTE. Reviewers confirmed the issue and the effectiveness of the patch, noting it's a straightforward fix that should be backpatched. Discussions also touched upon related but out-of-scope issues regarding EXPLAIN behavior with existing tables and volatile functions. The general consensus is that the patch correctly closes a critical EXPLAIN loophole.
18 Checkpoints stuck after a failed bulk write inside a savepoint Discussing 5 msgs Nov 20, 08:29
The thread addresses a bug where checkpoints could get stuck indefinitely after a bulk write operation fails within a savepoint. This happens because the DELAY_CHKPT_START flag is set outside a critical section and not cleared upon error, causing subsequent checkpoints to wait. The proposer's patch aims to eliminate the need for this flag by reordering operations: first registering the sync request, then checking the redo pointer, and performing an immediate fsync only if needed. A reviewer confirmed the fix and its underlying logic. The discussion then focused on a minor performance implication of the proposed fix, involving a potentially redundant fsync. An alternative approach was suggested to further minimize this overhead. The necessity of backpatching was also questioned.
19 [PATCH] Reject duplicate SQL/JSON PASSING argument names Patch Review 6 msgs Nov 20, 09:28
The proposer submitted a patch to enforce uniqueness for variable names in SQL/JSON `PASSING` clauses. Previously, duplicate names were silently handled by using the first matching value, which could lead to confusion. The patch introduces an error (`ERRCODE_DUPLICATE_ALIAS`) when duplicate `PASSING` argument names are detected in `JSON_EXISTS`, `JSON_QUERY`, `JSON_VALUE`, and `JSON_TABLE`, aligning behavior with Oracle and promoting clearer error reporting. Reviewer 1 provided comprehensive feedback, suggesting improvements to code style, documentation, test coverage, and the commit message. The proposer incorporated all the suggested changes into a v2 patch, which Reviewer 1 subsequently reviewed and marked as "ready for committer."
20 pgstat: Flush some statistics within running transactions, take 2 Patch Review 53 msgs Nov 20, 08:29
The thread proposes a mechanism to flush some PostgreSQL statistics within running transactions, rather than only at transaction boundaries. This aims to provide more up-to-date metrics, especially for long-running transactions. The proposer introduced SQL and C APIs to trigger on-demand flushes. The discussion involved refining which counters are flushed mid-transaction versus deferred until commit/rollback, particularly for transactional write counters like tuple inserts/updates/deletes. Subsequent patch versions addressed issues found during review, including correct handling of TRUNCATE within procedures and interactions with ANALYZE, and the behavior of CHECK_FOR_INTERRUPTS in the flush loop. A recently identified n_ins_since_vacuum issue was determined to be a pre-existing bug not introduced by the patch.
21 [PATCH v1] Preserve implicit composite casts in ROW deparsing Patch Review 4 msgs Nov 20, 08:29
The proposer identified a bug where pg_get_viewdef() loses implicit composite type casts when deparsing ROW expressions. This can lead to views failing to restore correctly or silently calling different function overloads after schema changes. The proposer provided a patch that ensures RowExpr printing honors the showimplicit flag for named composites. A reviewer confirmed the bug, tested the patch across multiple PostgreSQL versions, and agreed it merits backpatching. The patch was updated to include more comprehensive test cases, specifically for VARIADIC functions and EXPLAIN output.
22 Per-table resync for logical replication subscriptions Patch Review 37 msgs Nov 19, 05:29
The proposer introduced a new ALTER SUBSCRIPTION ... REFRESH TABLE command to allow per-table resynchronization for logical replication subscribers, addressing scenarios like data inconsistency, row filter changes, or repairing a single table without affecting others. The command aims to be subscriber-local and reuse existing tablesync machinery. Throughout numerous patch versions (v1 to v15), the feature was refined through extensive review. Key challenges included preventing deadlocks during concurrent REFRESH operations, ensuring correct handling of replication origins when the publisher is down, managing trigger interactions during TRUNCATE operations, and resolving test race conditions. The proposer implemented solutions such as reordering lock acquisition, adjusting replication origin lifecycle, and refining how triggers are handled. The latest feedback from a new reviewer highlighted that the command currently doesn't verify if the target table is still published, potentially leading to data loss if an unsubscribed table is truncated and cannot be re-copied.
23 pg_restore fails on extracted tar archives with large objects Patch Review 3 msgs Nov 20, 08:29
The proposer identified a bug in pg_restore where it fails to restore large objects from extracted tar archives. This issue arises because the tar format stores NNN.dat instead of blobs_NNN.toc in the BLOBS TOC entry, a problem exacerbated by a change in v17. A patch was proposed to fix this by constructing the correct filename instead of relying on the potentially incorrect one in the TOC entry. A reviewer confirmed the bug and the effectiveness of the patch, and also suggested adding a small test. The latest message points out a related problem in _PrepParallelRestore() concerning size estimation for parallel large object restores, suggesting the same filename construction fix might be needed there too.
24 pg_upgrade doesn't copy ICU rules on template0 Committed 9 msgs Nov 20, 10:28
The thread began with the proposer reporting that pg_upgrade failed to copy ICU rules for template0, an oversight from a previous commit that added ICU rules. A patch author quickly provided a fix, adapting the existing UPDATE mechanism to handle potentially long ICU rules and removing unnecessary version checks in set_locale_and_encoding(). This fix was confirmed by the proposer. A reviewer then raised a broader question about the utility of locale settings (including ICU rules) on template0, as they act as inflexible defaults that cannot be changed after initdb. The reviewer also identified a related bug where CREATE DATABASE with template template0 and LOCALE_PROVIDER='builtin' would fail if template0 had ICU rules, preventing the creation of non-ICU databases. The proposer provided a patch for this CREATE DATABASE issue. Ultimately, both the CREATE DATABASE fix and the original pg_upgrade fix for daticurules were committed, with the reviewer acknowledging that daticurules are now handled consistently with other locale settings despite lingering questions about the overall design.
25 [PATCH] Handle SQL NULL results from casts in JSON generation Discussing 2 msgs Nov 20, 06:29
The proposer identified an error in PostgreSQL's JSON generation functions where they crash with "function returned NULL" when a custom cast function to `json` or `jsonb` returns a SQL NULL for a non-NULL input. This issue was observed with `json_build_array` and `jsonb_build_array`. The problem stems from `OidFunctionCall1()` treating a NULL result as an error. The initial patch (v1) proposed checking `fcinfo->isnull` and writing a JSON null. In v2, the proposer opted for `to_json()` and `to_jsonb()` to return SQL NULL for such top-level casts, aligning with the behavior of explicit `::json` casts, and is currently seeking feedback on this design choice.
26 Add a hook for handling logical decoding messages on subscribers. Discussing 49 msgs Nov 20, 05:29
The thread proposes adding a hook for logical decoding messages on subscribers to enable extensions to handle DDL, function replication, or trigger custom routines. Early discussions refined the callback signature and transaction ID handling. Later, a reviewer raised a concern about compatibility with older, unpatched publishers when streaming is enabled: messages from aborted subtransactions might not be correctly discarded on the subscriber, as the unpatched publisher sends only the top-level XID. The proposer initially suggested requiring v20+ publishers for the message option. This led to further discussion about nuanced version checks, with one reviewer suggesting different requirements based on whether streaming is enabled or disabled. Concurrently, a new critical issue was identified: non-transactional messages could be repeatedly re-applied on the subscriber due to incorrect LSN advancement. A fix and a test case for this redelivery problem were provided. The proposer has responded to the versioning discussion, opting for a simpler v20+ requirement regardless of streaming, but the redelivery issue remains unaddressed in the latest exchange.
27 Increase NUM_XLOGINSERT_LOCKS Patch Review 9 msgs Nov 19, 15:29
This thread discusses increasing the `NUM_XLOGINSERT_LOCKS` value to improve WAL insertion performance. The proposer initially showed measurable gains on a notebook with a synthetic insert/delete workload, suggesting increases up to 256 locks or 64 locks with conditional locking attempts. However, reviewer 1 presented past and current benchmarks on larger servers showing that significantly increasing these locks (e.g., beyond 8) could lead to performance regressions due to increased spinlock contention and cacheline pingpong, especially for certain workloads like logical replication. Despite this, there was some consensus that a smaller increase, like up to 64, might be a 'sweet spot' for smaller installations. Later in the thread, a new proposer (who was also involved in the separate lock-free xlog reservation discussion) submitted a patch (v1) to make `NUM_XLOGINSERT_LOCKS` configurable via a GUC, defaulting to 8 but allowing powers of two from 4 to 128. This patch, submitted independently of a CAS128 reservation patch, showed significant performance gains (up to +40.4%) on high-core count systems when used with 64 locks. The new proposer is seeking review on the GUC setting and its validation.
28 Use local buffer for packed numerics Discussing 5 msgs Nov 20, 08:29
The proposer introduced a patchset to optimize numeric operations by using local buffers for unpacking MinimalTuple numerics, preventing repeated palloc/pfree calls. Initial benchmarks indicated 15-35% speedup, particularly for aggregates and sorting. A reviewer pointed out a more aggressive optimization, working directly with packed numeric representations, and shared their own patch for comparison. The discussion then expanded to cover the proposer's broader, three-part numeric optimization project, including direct comparison for numeric_eq()/numeric_ne() which showed even greater speedups. The latest messages involve sharing benchmark results and alternative patch approaches from the reviewer, highlighting mixed performance outcomes depending on architecture and implementation details, with a focus on centralizing case distinctions.
29 CI slowdown due to PG_TEST_INITDB_EXTRA_OPTS Committed 18 msgs Nov 20, 08:29
The thread identifies that the use of PG_TEST_INITDB_EXTRA_OPTS in CI jobs significantly slows down test execution by disabling the initdb template mechanism, leading to a 3x increase in CPU time. Initial proposals included recording and matching template options or introducing a new environment variable PG_TEST_SERVER_POSTGRES_OPTS to directly set GUCs in postgresql.conf. The latter approach was chosen and a patch was committed, leading to substantial performance improvements (20-30% reduction in CI runtimes). Discussions also occurred regarding further optimizations, such as splitting long-running ASAN and MinGW tests across multiple runners. Following the commitment of the primary patch, there's an ongoing discussion about backpatching this improvement to older supported PostgreSQL versions, with concerns raised about introducing new user-visible options to stable branches.
30 [PATCH] psql: Complete GRANTED BY in GRANT and REVOKE Proposed 1 msgs Nov 20, 09:28
The proposer identified two completion issues within psql for `GRANT` and `REVOKE` commands. Firstly, the `GRANTED BY` clause and grantor names were not being completed. Secondly, when typing `GRANT SELECT ON t TO r WITH GRANT OPTION` and pressing Tab after a space, psql incorrectly inserted `TO`, invalidating the statement. The proposed patch aims to resolve these by adjusting the completion logic to prioritize matching `WITH GRANT OPTION` and by adding proper completion for the `GRANTED BY` clause and grantor names in both `GRANT` and `REVOKE` commands.
31 pg_dump: remove restore.sql from tar format Patch Review 1 msgs Nov 20, 09:28
The proposer suggested removing the undocumented `restore.sql` script from `pg_dump`'s tar format. This script has historically caused maintenance burden, requiring numerous fixes and necessitating a costly second pass over the Table of Contents (TOC) with tar-specific hacks that re-run attribute statistics queries. The proposer argued that `pg_restore -f` offers a supported and more efficient alternative for generating a restore script. Commenter 1 strongly supported the removal, agreeing that the overhead associated with an undocumented script, particularly the second TOC pass and tar-specific machinery, is not justified given the existence of a superior, supported alternative.
32 Initial table sync copies the whole table when it is no longer published Discussing 19 msgs Nov 20, 05:29
The proposer identified a bug where, during initial logical replication table synchronization, if a publication is dropped or a table is removed from it before sync begins, the subscriber incorrectly copies the entire table, ignoring column filters and row specifications. The proposed fix introduces an error in such cases, preventing erroneous data copy and ensuring the table is not silently marked as synchronized. Extensive discussion ensued regarding whether to raise an ERROR or a WARNING, and how users could recover from such errors, especially with disable_on_error enabled. A workaround for recovery was detailed and subsequently documented, with refinement suggestions for the documentation itself. Test cases were also fine-tuned. The core patch appears to be accepted, but the latest message from the proposer questions the necessity of backpatching, citing precedents where similar behavioral changes were not backpatched without field reports, suggesting further discussion or waiting for feedback.
33 Avoid copying MinimalTuple twice in ExecHashSkewTableInsert() Patch Review 2 msgs Nov 20, 08:29
The proposer identified an inefficiency in ExecHashSkewTableInsert() where MinimalTuple was copied twice when the input slot was not a MinimalTupleTableSlot. A patch was proposed to use ExecCopySlotMinimalTupleExtra() to avoid the redundant copy, aiming for minor performance improvements and code simplification.
34 Proposal: Conflict log history table for Logical Replication Patch Review 48 msgs Nov 20, 04:28
This lengthy thread discusses the proposal and implementation of a conflict log history table for Logical Replication. Key issues include handling cases where replica identity values render to more than 1GB during conflict recording, which has been addressed with documentation. A significant part of the discussion revolves around how the apply worker interacts with prepared transactions, particularly concerning locks on the Conflict Log Table (CLT). Commenters debate whether to error out the apply worker due to potential false positives, document the hazards, or implement more precise lock-acquisition checks, with no definitive consensus yet. Another area of focus is pg_dump and pg_upgrade behavior for the CLT, including dumping definitions, privileges, comments, and security labels, and ensuring correct testing. The patches are undergoing extensive review, with multiple versions (up to v96 mentioned) addressing various code, documentation, and testing comments.
35 Injection point: fix race condition between attach and cleanup Rejected 11 msgs Nov 20, 02:29
This thread discusses a race condition in PostgreSQL's injection point mechanism, specifically during the cleanup of local injection points in multi-backend test scenarios. The proposer observed that an exiting backend's cleanup routine might inadvertently detach a newly attached injection point if another backend reused the same name, leading to incorrect test behavior. A patch was proposed to uniquely identify injection points using a slot ID and generation to prevent this. However, core developers strongly pushed back against complicating the server-side injection point functionality, arguing that test facilities should remain simple. They suggested that such issues should either be handled within the test code itself, by using distinct names for injection points, or by improving the test infrastructure (e.g., ensuring backends fully exit before reuse). The consensus was to reject the proposed core changes due to added complexity and to address the problem at the testing layer, either by modifying test scripts or enhancing test utility functions.
36 [PATCH]Fix planner's strictness check for JsonExpr Patch Review 17 msgs Nov 20, 02:29
The thread addresses a bug in PostgreSQL's planner where `JsonExpr` nodes were incorrectly treated as strict, leading to incorrect `NULL` handling, particularly in `LEFT JOIN` scenarios with `PASSING` arguments. The initial proposal aimed to make `JsonExpr` non-strict by default. Through several iterations and detailed reviews, the strictness rule for `JsonExpr` was refined. Key discussions revolved around correctly handling `RETURNING` clauses with null-rejecting domains, and the interaction with other planner optimizations like expression inlining. The solution evolved to define `JsonExpr` as non-strict if it returns any domain, or if a variable, placeholder, or parameter appears in its `PASSING` values or `ON EMPTY / ON ERROR` expressions. This allows more precise strictness checks, enabling inlining for more `JSON` expressions while preserving correctness. The patch also considers its integration with related ongoing work, such as a separate fix for `FULL JOIN` strictness.
37 Use an index scan in RemoveSubscriptionRel() Committed 6 msgs Nov 20, 00:29
The thread discusses a performance improvement for RemoveSubscriptionRel() on a subscriber, proposing to replace an inefficient heap scan with an index scan when looking up by relid. The proposer demonstrated significant performance gains, reducing operation time from 2.8s to 0.23s in a synthetic test with 100,000 rows. Initial review questioned the necessity of reordering scan keys in the patch. However, a commenter proved this reordering is essential by demonstrating a failure without it and explaining the underlying _bt_preprocess_keys() check. The proposer confirmed that existing test suites also caught this issue. The patch was updated and subsequently committed.
38 [PATCH v1] Fix initial sync of zero-column published tables Patch Review 13 msgs Nov 20, 06:29
The proposer identified a bug where initial synchronization of a zero-column published table mishandles subscriber-only columns, either populating them with empty strings instead of defaults (text mode) or failing with a field-count mismatch (binary mode). This occurs because `COPY` incorrectly interprets an empty column list as an unspecified list. The proposed solution involves introducing `BeginCopyFromExtended()`, which accepts a flag to explicitly indicate an empty column list, ensuring subscriber defaults are correctly applied. The patch progressed through several versions. Initial reviews by commenter 1 and commenter 2 provided feedback on test simplification and code comments, leading to v2 and v3. Reviewer 3 confirmed the fix on master and verified it corrected various edge cases. Further discussions with commenter 2 led to an improved commit message (v4) and requests for backporting patches to affected PostgreSQL branches. The proposer then provided v5, with separate patches for master and REL_15_STABLE, addressing all previous comments and confirming extensive testing across multiple branches.
39 Switching XLog source from archive to streaming when primary available Discussing 9 msgs Nov 19, 11:29
This thread discusses a proposed feature for PostgreSQL standbys to automatically switch from archive recovery to streaming replication when the primary becomes available. Initially, the discussion focused on the performance differences between archive and streaming replication. However, the proposer later shifted the focus to a correctness/availability problem: a standby stuck in archive recovery can prevent its physical replication slot on the primary from advancing, leading to unbounded WAL retention on the primary and potential disk exhaustion, as well as stalling logical replication if slot synchronization is enabled. A patch (v25) was introduced that adds a timeout-based parameter, 'streaming_replication_retry_interval', allowing the standby to attempt switching to streaming after consuming local WAL, falling back to archive if streaming fails. A reproducer for the issue was provided. The latest discussion explores an alternative approach: having the walreceiver on the standby send feedback to the primary even while processing WAL from the archive, to advance the replication slot without necessarily switching to streaming.
40 [PATCH] Clamp auto-tuned transaction_buffers to at least 8 SLRU banks Proposed 1 msgs Nov 20, 06:29
The proposer identified a performance bottleneck in PostgreSQL related to the auto-tuning of `transaction_buffers`. When `transaction_buffers` is set to 0, the `CLOGShmemBuffers()` function can allocate a very small buffer pool, leading to only two SLRU banks for common `shared_buffers` configurations. This small pool causes frequent eviction and re-reading of CLOG pages, leading to high contention on the `LWLock:XactSLRU` lock and reduced transaction throughput under high concurrency. Benchmarking on a 128-core machine with `pgbench` showed significant performance degradation (up to 8.1% lower TPS) and millions of CLOG page re-reads. The proposed patch clamps the auto-tuned `transaction_buffers` to a minimum of 8 SLRU banks (128 slots, 1MB), which was found to eliminate pathological re-read behavior and restore full TPS with minimal memory overhead.
41 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Committed 28 msgs Nov 20, 00:29
The thread, which has been ongoing for a long time, addresses a bug in CREATE DATABASE's WAL logging strategy that mishandles non-standard page layouts, leading to issues with Visibility Map (VM) pages. The proposed solution is a hybrid approach, using page_std for heap tables and btree indexes, and zeroing out unused space. Recent discussions focused on refining the TAP tests to ensure original coverage is maintained and to make them less fragile by increasing shared_buffers. A related issue was discovered where wal_consistency_checking did not properly validate VM pages, which is being considered for a separate fix. The main fix has been committed and backpatched. Further discussion is on an additional optimization for RelationCopyStorage() for the master branch.
42 Parallel vacuum: check XID wraparound failsafe between indexes Patch Review 12 msgs Nov 20, 03:28
The thread discusses a proposed improvement for parallel vacuum in PostgreSQL 20 to check the XID wraparound failsafe more frequently. Currently, this check occurs only after all parallel workers have vacuumed their assigned indexes, potentially delaying the advancement of `relfrozenxid` and `relminmxid` and increasing the risk of XID wraparound. The proposer suggests a mechanism where both the leader and parallel workers check the failsafe after each index and set a shared flag if triggered. This flag would then cause cost-based delays to be ignored and remaining indexes to be skipped, ensuring faster completion and XID advancement. Early discussion validated the approach and suggested minor code improvements. A reviewer later identified a potential issue where dynamically changed `vacuum_failsafe_age` GUC values might not be honored by workers. The proposer addressed this in v4 by ensuring the failsafe age is passed from the leader to workers so they can react to such changes.
43 Index drops during phase 1 of REINDEX CONCURRENTLY Committed 6 msgs Nov 20, 05:29
The proposer identified a problem in REINDEX CONCURRENTLY where indexes could be dropped during the first phase of re-indexing, leading to errors in the second phase. The initial patch aimed to address this, but a reviewer found a scenario where the fix was insufficient due to RelationGetIndexList() returning indexes in OID order and DROP INDEX being possible within the same transaction. This could cause an index to be dropped by a function call while another index was being processed, leading to subsequent failures. After discussion and a proposed alternative solution by a commenter, the proposer decided to keep the Relation references open from the initial index gathering loop, simplifying the approach. The proposer then committed this revised fix, including backpatching to v15.
44 Autovacuum freeze scores wrap around for just-vacuumed tables Committed 5 msgs Nov 19, 15:29
This thread addresses a bug in `relation_needs_vacanalyze()` where autovacuum freeze scores could incorrectly wrap around to a very large value for recently vacuumed tables. The proposer found that if `relfrozenxid` or `relminmxid` advanced due to a concurrent `VACUUM` or table rewrite between reading `recentXid` and `pg_class` scan, the subtraction would overflow. This could cause just-vacuumed tables to be assigned an unduly high priority in the autovacuum list. The proposer provided a patch to treat the age as zero in such overflow cases and included a TAP test for demonstration. Commenter 1 confirmed the issue and generally approved the patch, suggesting back-patching. Reviewer 1 agreed the fix was valuable, though not a release blocker, and committed the patch.
45 Fix resource leaks in pg_receivewal on LZ4 close failures Discussing 10 msgs Nov 19, 13:28
The proposer initiated a discussion regarding file descriptor and memory leaks in `pg_receivewal` when LZ4 compression finalization or the final write operations fail. A patch was proposed to address these resource leaks. A commenter raised a question about the patch's behavior in `dir_close()`, where it appeared to bypass cleanup operations for a successful `close()`. The proposer clarified this was intentional, aiming to preserve existing error handling behavior while still releasing resources. Another commenter supported the patch, highlighting the importance of fixing resource leaks in a service that might run in a loop. Further discussion ensued about a potential temporary file leak (unlink) that the current patch might not fully address, leading to a suggestion for a separate follow-up patch to handle this broader cleanup scenario. The proposer agreed, indicating that a separate patch would be more appropriate for that specific issue.
46 A latent pointer increment bug in pgtypes_defmt_scan() Committed 4 msgs Nov 20, 01:28
The proposer discovered a latent bug in pgtypes_defmt_scan() where a while loop intended to skip leading spaces was incorrectly advancing pstr (a char**) instead of *pstr (the char* pointing to the string). This bug could potentially cause a crash but was effectively dead code because the caller already skipped leading spaces. The proposer provided a one-line fix. Another commenter confirmed the fix was correct but suggested just removing the loop and adding an assertion. The committer agreed the code was wrong and committed the one-line fix, noting that the function could use more cleanup due to redundant space-skipping logic. The proposer acknowledged the redundancy but preferred keeping the function self-contained for now.
47 Block various aggregate component functions from being called directly Rejected 4 msgs Nov 19, 22:28
This thread discusses a proposal to block various aggregate component functions from being called directly by adding a `prointernal` column to `pg_proc`. The proposer observed an increase in bug reports from LLMs and fuzz testers for these internal functions, which are not designed for direct user interaction and can exhibit unexpected behavior in edge cases. The proposed solution aimed to prevent such misuse while still allowing their use within `CREATE AGGREGATE` or `CREATE OPERATOR`. Commenter 1 supported the trade-off. Reviewer 1 suggested using a `prokind` value instead of a new column and advocated for allowing extensions to use this feature. However, commenter 2 demonstrated that the proposed method would not prevent the core issue, as these functions could still be misused by creating a custom aggregate that wraps them, rendering the `prointernal` flag ineffective for stopping such bug reports. This fundamental flaw led to the rejection of the proposed approach.
48 auto_explain crashes on EXPLAIN (BUFFERS) of an index scan (19, master) Committed 3 msgs Nov 20, 03:28
A crash in `auto_explain` was reported when using `EXPLAIN (BUFFERS)` without `ANALYZE` for index scans, while `log_analyze` is enabled. This issue affected PostgreSQL versions 19 and master, tracing back to a change where index scan nodes stopped allocating `IndexScanInstrumentation` under `EXEC_FLAG_EXPLAIN_ONLY`. The `auto_explain` extension, however, still attempted to print these plans as if they had instrumentation, leading to a NULL pointer dereference. The proposer provided a patch that treats missing instrumentation as zero counts within `explain.c`, logging `(never executed)` and `Index Searches: 0` as expected, and included new TAP test cases. The patch was reviewed, confirmed, and subsequently committed to resolve the crash.
49 [PATCH v2] Fix SP-GiST index-only scans that return only INCLUDE columns Committed 2 msgs Nov 19, 21:28
The proposer identified a critical bug in SP-GiST index-only scans when querying only `INCLUDE` columns, especially when the opclass cannot reconstruct the key (`canReturnData = false`). In such scenarios, `leaf_consistent` would still attempt to return the key, which `heap_form_tuple` would misinterpret, leading to data corruption or crashes. The proposer provided a patch (v2) that ensures the key is only requested from `leaf_consistent` when `canReturnData` is true, otherwise returning NULL, and prevents the key from being copied into the ordered-scan queue. A similar fix had been incorporated into the new `spggetbatch` path, but this patch was specifically adapted for the existing `spggettuple` path for backpatching. The bug has existed since `INCLUDE` support was added to SP-GiST in v14, and the proposer recommended backpatching through v15. The patch was tested and confirmed to resolve the issue.
50 Keep TIDBitmap pagetable memory across bitmap scans Rejected 7 msgs Nov 20, 02:29
The thread discusses performance bottlenecks in `TIDBitmap` due to repeated memory allocations and deallocations, leading to significant page faults and kernel contention, especially under high concurrency. The proposer identified that the `TIDBitmap` pagetable, a simple hash table, frequently grows from a small size, causing numerous memory allocations and subsequent trims, leading to repeated page faults. Initial measurements showed substantial system time and spinlock contention. The proposer explored various solutions, including presizing the table and tuning `malloc` settings, but found these inadequate. A prototype patch was proposed to reuse allocated memory for up to two large bitmaps per backend. Further investigation ruled out cgroups as the primary cause of contention, instead pointing to per-NUMA-node page queue locks on page faults and releases. Testing with huge pages confirmed they could eliminate page faults but did not address other scaling issues related to the `TIDBitmap`'s doubling growth strategy. The proposer concluded that the ultimate solution likely involves a more fundamental data structure change (radix tree) combined with process-level fault fixes, and offered to contribute the test module to the radix tree effort. The discussion shifted towards foundational changes, de-prioritizing the current patch.