The pgsql-hackers
Weekly Digest

The proposer introduced pg_get_policy_ddl() to reconstruct CREATE POLICY statements, initially as a function with regclass, name, and an optional pretty boolean. Over several iterations, the function's signature was updated to align with a new VARIADIC options text[] style, similar to pg_get_database_ddl(). Early feedback included minor documentation and style suggestions. A significant issue was identified by Reviewer 1 in v10: the generated DDL for simple boolean USING/WITH CHECK expressions (e.g., USING true) lacked necessary parentheses, causing syntax errors upon re-execution. The proposer addressed this in v11 by ensuring expressions are always parenthesized, mimicking pg_dump's behavior, and added a round-trip regression test to verify correctness. Reviewer 1 confirmed that v11 fixed the issue and passed all tests. Commenter 2 also acknowledged their previous oversight regarding the parentheses.

The proposer reported a NULL pointer dereference in the syslogger during startup when `shared_preload_libraries` are used with `logging_collector` and `-DEXEC_BACKEND`. Through bisecting, the issue was traced to a commit that assigned `MyBackendType` earlier in the process, causing the syslogger to attempt writing to a log file before it was open. A reviewer provided a patch introducing a new boolean flag, `syslogger_setup_done`, to explicitly track when the logging file descriptors are ready. This solution was discussed, refined to include other logging paths, and ultimately committed, resolving the bug.

This long-running thread proposes a feature to log the execution plan of a running query, without requiring EXPLAIN ANALYZE. The initial patches aimed to allow logging on demand via pg_log_query_plan() and introduce a log_query_plan_pending GUC. Early discussions focused on the mechanism for logging, especially in cases where a query might finish before LogQueryPlan() is invoked. a core developer provided detailed suggestions for a race-free test case involving advisory locks and injection points, which the proposer implemented. Further testing and robustness checks were performed, including looping the test and inserting sleeps, which did not reveal failures. The thread was rebased multiple times. A new reviewer recently rebased the patch (v54), fixed a missing hook call, updated TAP test numbers, fixed Meson build references, adjusted auto_explain.c, and applied pgindent. The new reviewer expressed the importance of this infrastructure for progressive explain features.

This thread addresses a regression in PostgreSQL 18 concerning physical replication slots. Previously, physical slots invalidated due to `max_slot_wal_keep_size` could be reacquired if WAL was available via archive recovery. However, commit f41d8468 made this impossible, preventing standbys from resuming streaming and necessitating manual slot recreation. The proposer initially suggested a direct fix to allow reacquisition, but a reviewer highlighted that invalidated slots offer no WAL protection, posing a risk. The proposer revised the approach, introducing an opt-in `auto_revalidate` mechanism for physical slots. This feature enables self-healing for specific invalidation reasons (WAL_REMOVED, IDLE_TIMEOUT) by allowing acquisition with a warning and clearing the invalidation upon successful WAL streaming. A v2 patchset, including core infrastructure, SQL functions, view exposure, comprehensive TAP tests, and documentation, was submitted for review.

This thread discusses a patch that adds logging for parallel worker usage in PostgreSQL, particularly focusing on cases where fewer workers are launched than planned (a "shortage"). The proposer initially included logging for both parallel queries and maintenance commands (VACUUM, CREATE INDEX). A reviewer suggested limiting logging to parallel queries only for simplicity and consistency with existing verbose options for maintenance tasks, proposing a simpler log line format and renaming the GUC setting from "failure" to "shortage". The proposer argued for including maintenance operations due to observability needs in audited systems and centralization of logs/configuration. Subsequent patch versions incorporated simplifying the log message and renaming the setting to "shortage". A committer joined the review, questioning the purpose of one patch, pointing out existing similar logging for VACUUM, suggesting adding logging for parallel GIN index builds, and raising concerns about duplicate log messages with `VERBOSE` mode. The proposer rebased the patch, merged one component, and added GIN index logging, reiterating the distinct use case for auditing. The latest discussion revolves around the format of the error message and the implications of consistency.

The proposer suggested adding glob-style pattern matching to `LISTEN` channel names for PostgreSQL 20, providing a proof-of-concept patch and initial benchmarks. A reviewer directed the proposer to previous threads on similar topics. Another reviewer expressed skepticism, questioning the necessity of server-side support given existing alternatives and noting that the current implementation negates the benefits of targeted wakeups from v19. The proposer acknowledged these concerns and is now exploring alternative approaches, such as pattern matching against message payloads rather than channel names.

This thread focuses on fixing a bug in the new feature that allows altering `CHECK` constraint enforceability. The proposer identified that when a parent table's constraint was set to `ENFORCED`, it failed to properly update corresponding constraints in child tables (both regular inheritance and partitioned) that had previously been set to `NOT ENFORCED`. Initial patches aimed to correct this recursion, but complex multiple inheritance scenarios presented by a commenter required further refinement of the logic to ensure that enforced parent constraints were correctly considered up the inheritance hierarchy. Later, a reviewer suggested adding `check_stack_depth()` for deep inheritance chains and optimizing `table_open` calls for performance. An additional discussion involved best practices for `errmsg()` formatting, leading to further refinements in the error messages. The thread is ongoing with the proposer having submitted a v10 patch.

The thread addresses a buildfarm failure in `035_standby_logical_decoding.pl` where `pg_recvlogical` fails to read WAL segments that have been removed due to a timeline switch during standby promotion. The proposer identified a race condition in `logical_read_xlog_page()` where `walsender` attempts to read WAL from an old timeline (timeline 1) while the server has promoted to a new one (timeline 2). A reviewer confirmed the diagnosis and suggested a similar fix for `read_local_xlog_page_guts()`. The proposer submitted a four-patch series: 0001 fixes the race by checking `GetWALInsertionTimeLineIfSet()` when `RecoveryInProgress()` is true; 0002 adds a test for this fix; 0003 applies the fix to `read_local_xlog_page_guts()`; and 0004 adds a test for 0003. The discussion also covers backpatching considerations for older versions. The latest exchange involves a reviewer's intent to review the patch set and a brief discussion about the optimal injection points for testing.

This thread proposes a documentation patch to clarify usage information for `refint`'s trigger arguments, specifically addressing potential SQL injection concerns that were reported to the security team. While `refint` is slated for complete removal in v20, the proposer believes the documentation update is still valuable and should be back-patched to v14. The security team concluded that forcibly quoting arguments would cause more breakage than prevent exploits, as trigger arguments are typically controlled by the trigger author. A reviewer suggested mentioning the deprecation, but the proposer preferred to keep that for the actual removal effort. Two other reviewers agreed with the patch. The proposer confirmed it was committed.

This thread discusses an issue in GitHub Actions CI where Ccache rapidly exhausts available cache space due to inefficient management across branches. The proposer identified two key problems: caches are uploaded too frequently, even when the hit ratio is high, and a flawed cache key design causes branches named similarly to the default branch to incorrectly use the main branch's cache. A patch was proposed to mitigate these issues by introducing logic to upload new caches only when the existing cache entry's hit ratio is low and by refining the cache key mechanism. The proposer also sought feedback on restructuring the CI configuration using 'composite actions' for better organization, but a reviewer deemed it unnecessary. The reviewer confirmed the patch's effectiveness, noting a minor typo in the commit message.

The proposer introduced a patch to add `pg_get_table_ddl()` function, similar to existing `_ddl` functions, to reconstruct `CREATE TABLE` statements along with associated `ALTER TABLE`, `CREATE INDEX`, `CREATE RULE`, and `CREATE STATISTICS` statements. It supports various column-level and table-level features, with options for fine-tuning output. Triggers and policies are noted as TODOs. The proposer opted to reuse existing C helpers. Initial testing was done using `pg_regress`. A reviewer suggested an option for schema-qualified output. Another reviewer reported several bugs related to check constraints on partitions, inherited generated columns, and named not-null constraints not being replayed correctly.

This extensive thread discusses the implementation of a conflict log history table for logical replication. Early in the thread (inferred from message context), a reviewer suggested that the `conflict_log_destination` option should be a string to allow for combined values like 'log, table' and future extensibility, rather than an enum. Subsequent messages delve into detailed patch reviews. Commenter 1 provided extensive feedback on patch `v45-0004`, focusing on `describeSubscriptions` and suggesting improvements for combining `appendPQExpBuffer` calls, maintaining column order, and using a common function for conflict log table names. Commenter 2 raised a concern about explicit `LOCK TABLE` operations on Conflict Log Tables (CLTs) potentially blocking apply workers. Commenter 3 initially favored disallowing such locks but later agreed that allowing them, with users accepting responsibility for consequences, was a cleaner approach if implementing timeouts was complex. Commenter 3 then submitted an updated patch addressing most issues, excluding those from commenter 1 on patch `0004`. Commenter 2 then reported further issues in the `v46` patches, including `NULL` values for `remote_commit_ts`, stale `remote_xid` in two-phase transactions, and an `Assert()` that should be an `ERROR`. Commenter 2 also identified an internal `ERROR: errstart was not called` when `InsertConflictLogTuple()` fails, suggesting `TRY-CATCH` block improvements. Commenter 4 addressed some of commenter 1's earlier comments on a different patch (`v45-0003`) regarding assert placement and field order, confirming some issues were handled in `v47`.

This thread proposes enhancing log_checkpoints output to accurately reflect WAL segment creations from all processes, not just those from PreallocXlogFiles(). The proposer's initial patch introduced a shared-memory atomic counter (walSegmentsCreated) and a baseline (walSegsCreatedLastCheckpoint) to track and report new segments between checkpoints. After initial feedback, regression tests were added, and minor documentation tweaks and integer overflow comments were addressed. Subsequently, commenter 1 (reviewer 3) suggested exposing this counter in pg_stat_checkpointer for monitoring and raised questions about atomicity, int overflow ranges, and testing under wal_level=minimal. Most recently, reviewer 4 proposed a significant design simplification: using a single, atomically resettable counter with pg_atomic_exchange_u64 instead of the current two-field cumulative and baseline approach, asking for the proposer's rationale behind the original design. This suggests a re-evaluation of the counter's implementation.

This thread discusses a limitation in the `pg_createsubscriber` utility where it prevented specifying the same publication name multiple times, even if these publications resided in different databases. The proposer argued that this restriction became problematic with a new PG19 feature that allows `pg_createsubscriber` to reuse existing publications, as publication names are database-local. A reviewer confirmed the issue and suggested considering subscription names as well. However, another commenter raised concerns about potential replication slot name conflicts if duplicate subscription names were permitted without explicit `--replication-slot` options. As a result, the proposer decided to prioritize the fix for publication names for PG19, deferring the more complex handling of subscription names to a future release. The patch addressing the publication name duplication issue was subsequently accepted and committed.

This thread explores the problem of subquery pull-up transformations potentially degrading query planning performance by expanding the `jointree` search space, especially when exceeding the `join_collapse_limit`. The proposer observed performance regressions with correlated subplans and initplan-to-join transformations, suggesting mechanisms for users or extensions to manage this or an in-core solution to stop pull-ups when the collapse limit is reached. Initial discussion by core developers considered raising `join_collapse_limit` and `from_collapse_limit` defaults given modern hardware, but acknowledged exponential planning time growth and the need to identify and mitigate pathological cases. The proposer later identified the main issue as SubLinks being moved high in the `jointree`, losing their filtering effect. A patch was provided to address this by ensuring pulled-up SubLinks are inserted into the minimal safe `join tree`. Subsequently, another developer conducted stress tests, highlighting significant memory consumption during planning for larger joins. A committer then provided a trivial patch to fix specific memory leaks in `find_mergeclauses_for_outer_pathkeys` and `generate_join_implied_equalities`, reducing memory usage by about 30%.

The proposer identified missing SGML markup in ALTER PUBLICATION examples within the PostgreSQL documentation and provided a trivial patch to correct it. A reviewer approved the patch, noting that it should be committed to HEAD (PG19) once the beta1 tag was released. After the release, the patch was successfully pushed into the codebase, resolving the documentation inconsistency.

The proposer identified a type mismatch in the `old_catalog_xmin` variable, noting it was incorrectly defined as `XLogRecPtr` instead of `TransactionId` in a recent commit. A patch was provided to correct this. Reviewers agreed that although the mismatch did not cause functional issues due to its usage in an equality check, the type was conceptually incorrect and should be fixed. It was decided to backpatch the fix through v17. The committer confirmed the patch has been successfully pushed and backpatched to the specified versions.

The thread discusses a bug introduced by a recent commit (a0b6ef29a) where ALTER TABLE ADD COLUMN ... DEFAULT for domain types with non-volatile constraints fails with a hard error (e.g., division by zero) even on empty tables. This behavior is a change from previous versions and is inconsistent with CREATE TABLE behavior. The proposer offered two solutions: one using PG_TRY/PG_CATCH and another bypassing the fast path for empty tables. Reviewer 1 expressed strong dislike for both proposed solutions, citing concerns about error suppression and the need for new table access method routines. Reviewer 1 also suggested reverting the original commit (a0b6ef29a) and redesigning the optimization for a future version (v20), arguing that the original commit introduced inconsistent default expression error handling. Commenters also debated the pg_dump implications and noted similar inconsistencies for non-domain types. The general consensus appears to be against the current patch approaches, with a recommendation to revert the original feature.

The thread initially proposed a patch to add RISC-V Zbb popcount hardware optimization. Early discussion questioned the safety of the implementation without runtime checks and the practical benefit of such an optimization for PostgreSQL workloads, despite demonstrations of significant speedup for popcount operations. The conversation then shifted to address a critical auto-vectorization bug in Clang (versions < 22) on RISC-V, which caused des_init() and potentially other code (like zic.c) to miscompile and lead to failures in the build farm. After extensive investigation, it was confirmed that the bug is a Clang LoopVectorize wrong-code issue. The proposer subsequently submitted a patch to disable auto-vectorization for RISC-V systems when compiled with Clang versions older than 22. The latest activity indicates that the proposer re-attached the popcount optimization patches alongside this crucial bug fix, hoping to get both accepted for the upcoming release.

The proposer initiated a review of the PostgreSQL 19 Beta 1 release announcement draft. Several participants provided extensive feedback, suggesting improvements such as highlighting key features (e.g., `pg_plan_advice`, 64-bit MultiXactOffset, `GROUP BY ALL`, `LISTEN/NOTIFY` scalability), refining descriptions for JSONPATH functions and the `REPACK` command, and adding a note about "greasing the protocol." A separate discussion also arose regarding the visibility and accessibility of beta packages on the download pages, with suggestions for clearer guidance for users.

The proposer identified a rare but critical `pg_rewind` scenario where two diverging timelines could have the same TLI and LSN after multiple crashes during standby promotion, causing `pg_rewind` to incorrectly identify them as the same timeline and potentially leading to data loss. The proposed solution involves adding a UUID to the `XLOG_END_OF_RECOVERY` record and history file to distinguish such timelines. Reviewers provided feedback on the UUID implementation, suggesting using `gettimeofday()` for timestamps, and reported a Windows-specific test failure. A commenter questioned the "heavyweight" nature of UUIDs, suggesting strengthening existing history-based matching, but the proposer clarified why TLI and LSN alone are insufficient in this specific edge case. Another reviewer suggested considering an alternative approach to TLI allocation, by introducing randomness to prevent collisions, to maintain TLI as the sole branch identifier. The latest message also identified several issues in the patch, including a missing `MemoryContextSwitchTo`, ignored `rewind_parse_uuid` return value, a potential change in `pg_rewind`'s requirement for target history files, and potential ABI break.

The proposer submitted a patch to optimize SQL queries that include both `DISTINCT` and `GROUP BY` clauses. The optimization aims to prevent the planner from creating a redundant `Unique` plan node when the `GROUP BY` clause already guarantees the uniqueness of the output rows. This applies under specific conditions: when using plain `GROUP BY` (not `GROUPING SETS`), plain `DISTINCT` (not `DISTINCT ON`), and when all `GROUP BY` keys are included in the `DISTINCT` clause. An example demonstrated a reduction in the query plan complexity. A reviewer suggested checking for `hasTargetSRFs` and potentially refactoring to reuse existing similar logic in `query_is_distinct_for`.

The proposer observed that the `pg_stat_get_lock()` function's `prorows` estimate in `pg_proc.dat` (10 rows) did not match its actual return count (12 rows). A patch was submitted to update `prorows` to 12 and make minor comment tweaks, with the argument that a more accurate count would aid the planner. However, multiple reviewers countered that `prorows` serves as a rough estimate for the planner, not an exact count. They cited other system functions where estimates widely differ from actual returns and argued that striving for exact synchronization would introduce unnecessary maintenance overhead without demonstrating any tangible planning problems. A suggestion was also made to explicitly clarify in the documentation that `prorows` is an estimate. The overall consensus pointed to the rejection of the proposed `prorows` adjustment.

This thread addressed a bug where `DROP PROPERTY GRAPH` operations resulted in an "unsupported object class" error when event triggers were active. The issue was traced to `getObjectTypeDescription()` and `getObjectIdentityParts()` lacking `switch` cases for `PropgraphElementLabelRelationId` and `PropgraphLabelPropertyRelationId`. The proposer submitted a patch to add these missing cases, ensuring `DROP PROPERTY GRAPH`, `DROP PROPERTY GRAPH IF EXISTS`, and `DROP SCHEMA CASCADE` function correctly with property graphs. Discussions centered on the clarity and wording of object descriptions, particularly for label properties, and the implications of exposing these internal catalog details to event triggers. After several revisions and clarifications, including concerns about confusing object identity outputs and the need for more descriptive labels, the v7 patch was committed. The commit included additional fixes related to translation markers and `ObjectTypeMap` entries.

The proposer suggests optimizing PostgreSQL CI artifact uploads by only retaining logs from failed tests, thereby reducing upload times (e.g., from 300s to 70s on macOS) and storage costs, and improving the visibility of failures. Initially, a Python script was proposed for Meson builds, but it faced issues on NetBSD and Windows due to `python3` availability. A revised approach (v2) integrated the script via `meson compile`, placing it in `src/tools/ci/`. Version 3 adapted the solution for GitHub Actions, running the clearing command only if a job fails, which significantly reduced log size from ~8MB to ~1MB. A reviewer suggested copying logs to a new location instead of deleting, invoking the script directly via Ninja instead of `meson compile` for performance, and making the solution work for Autoconf builds eventually. The proposer agreed to some changes and clarified challenges with Autoconf and Python3 dependencies.

This thread continues a long-standing effort to prevent orphaned object dependencies. An initial patch (0001), based on a developer's suggestion to use heavyweight locks during DDL to conflict with DROP operations, was committed to address orphaned objects, with a reviewer noting its alignment with existing shared dependency handling. Subsequently, the focus shifted to a related Time-Of-Check To-Time-Of-Use (TOCTOU) vulnerability where a REVOKE operation could occur between an initial permission check and dependency recording. The proposer submitted a new patch series aiming to solve this by tracking ACL checks and re-checking permissions if invalidations occur before locking. A commenter raised concerns that this approach, unlike `RangeVarGetRelidExtended()`, doesn't coordinate name lookup, potentially leaving a TOCTOU window for schema drops. The commenter also suggested simplifying a loop in the proposed code, which the proposer agreed to. The latest discussion revolves around how the tracking mechanism handles multiple ACL checks with different modes for the same object.

The proposer suggested removing the `refint` contrib module for PostgreSQL v20, citing its long-standing documentation as superseded by built-in foreign keys and the increasing maintenance cost relative to its value as sample code. The patch set includes removing the module's files and adjusting `pg_upgrade` tests for cross-version compatibility. Reviewers unanimously agreed with the removal, and one suggested adding an `appendix-obsolete` entry to the documentation and a back-patched notice about its upcoming removal. Another reviewer supported the removal, noting that `refint` no longer meets current code standards and would require significant work to improve. The proposer submitted a v2 patch incorporating the `appendix-obsolete` entry.

The proposer reported a bug where `ALTER PROPERTY GRAPH ... DROP LABEL` could cause a `cache lookup failed for label` error when querying a view that depends on the dropped label. This issue arises from missing dependency information. A patch was provided to address this. Several reviewers positively reviewed the patch, and it was marked as "Ready for Committer". Subsequently, the proposer identified two additional, related problems: one concerning the expansion of empty labels in views (to ensure proper `pg_dump` and dependencies) and another involving labels shared by vertex and edge tables. The proposer decided to commit the initial fix first, and later provided patches for these new issues in the same series. Another participant reported a related issue, which was then identified as a duplicate of a patch already being discussed in a different thread, and confirmed that the patch from that other thread resolved their issue. A separate new thread was then initiated by a commenter to discuss a specific cleanup issue related to orphaned `pg_propgraph_property` entries.

This thread proposes adding support for ScalarArrayOpExpr (SAOP), such as `ANY()` and `IN()`, in the optimizer for partial index paths. Previously, only `BitmapOr` paths were considered for partial indexes. The proposer developed a new function, `generate_bitmap_saop_paths`, based on existing code. A reviewer provided detailed feedback, suggesting a precheck for suitable indexes, using a Bitmapset to track suitable indexes, consolidating tests, shrinking row counts, and benchmarking performance overhead in worst-case scenarios. The proposer responded with a significant refactor (v2, v3) to address these points, including a "best choice index" logic for `IN()` clauses and moving tests. After further rebasing for a commitfest entry (v4), the proposer conducted performance tests with a "SAOP-saturation-test.sql" script. This testing exposed a shortcoming where only the synthesized equality clause was matched, leading to two new optimization patches. The proposer confirmed that, with these additions, planner and execution timings are consistent with established code and improved.

The proposer highlighted an inconsistency in synchronized_standby_slots behavior: it requires ALL-of-N physical replication slots to catch up before logical decoding can proceed, which conflicts with quorum-based (ANY M of N) synchronous replication. In a quorum setup, if one standby fails, logical decoding blocks even if the transaction is safely committed on a quorum of standbys. The proposal is to make synchronized_standby_slots quorum-aware by allowing ANY M (slot1, slot2, ...) syntax, similar to synchronous_standby_names, to return true when M of N slots have caught up. The default behavior remains unchanged, and a separate GUC is preferred. Detailed discussions ensued regarding how to handle duplicate slot names specified in the GUC: whether to automatically resolve them, or raise an error. Initial thoughts favored skipping duplicates internally. Later, the consensus shifted towards normalizing duplicates internally without erroring, but raising an error if N > M (where N is required slots and M is unique available slots after deduplication). This aligns with synchronous_standby_names for duplicates but enforces configurability for N > M immediately.

This thread addresses a bug in UPDATE/DELETE FOR PORTION OF operations when used with inheritance tables. The original issue caused leftover rows, resulting from a portion update or delete, to be incorrectly inserted into the parent table instead of the correct child table where the original data resided. Additionally, there was a problem with attnum mapping for range columns in multiple-inheritance scenarios. The proposer initially submitted a patch, which was then re-worked by commenter 1 as part of a larger series of fixes related to FOR PORTION OF. After a request from the proposer, the patch was separated into a standalone fix for the inheritance/leftover bug, incorporating a refactoring. The final version (v3) was confirmed to resolve the reported bug and was subsequently committed.

The proposer identified an issue where hot standby feedback, without a physical replication slot, can incorrectly hold back VACUUM on user data tables on the primary due to the standby's catalog_xmin (potentially from a logical replication slot) being used as the xmin for all data. This can lead to significant table bloat. The proposed fix involves adding a catalog_xmin field to PGPROC to track it separately from xmin, mirroring the behavior of replication slots. This new proc_catalog_xmin would only affect catalog_oldest_nonremovable and shared_oldest_nonremovable. The proposer provided a v2 patch with test stability improvements and performance benchmarks showing negligible impact.

This thread discusses a documentation-only patch to mention "OFF" as an undocumented alias for "NONE" in the `EXPLAIN SERIALIZE` option. The proposer identified inconsistencies across the codebase: `ParseExplainOptionList()` accepts "OFF" as an alias, but it's not documented, not in the `ExplainSerializeOption` enum, not in `psql` tab completion, and not covered in regression tests. To maintain backward compatibility, the proposer chose to only document the alias for now, leaving other inconsistencies for future patches. A reviewer questioned the necessity of documenting this convenience alias, drawing parallels to undocumented boolean-style representations for GUC parameters. However, the reviewer conceded that for `SERIALIZE`, documenting "OFF" might prevent confusion due to its enum-like values, suggesting a minimal wording change. The proposer agreed to the minimal wording.

The thread discusses a bug in fe_utils where psql's expanded aligned output format (\x, \pset format aligned) was broken when displaying tables with data shorter than the header. The proposer identified the issue and provided a patch to fix it, which also included a regression test and addressed an unintended forcing to wrapped mode. Reviewer 1 suggested minor improvements to a comment and to ensure the expanded mode was explicitly turned off after the test. After a rebase by the proposer, reviewer 2 took over the review and subsequently committed the fix. The fix ensures that the calculation for available data width in expanded aligned mode correctly handles cases where data is shorter than the header, preventing layout issues.

This thread proposes adding the reason for the oldest xmin to VACUUM logs to help diagnose and prevent table bloat. The proposer argues that current logs only indicate a blockage, requiring manual querying of volatile views which is difficult for post-hoc analysis. The initial patch outputs the blocker's reason and PID. Commenters suggested that a SQL-visible interface for current xid/xmin holders would be more useful for proactive monitoring. The proposer agrees on the value of a SQL view but emphasizes the importance of logs for historical analysis, especially when a blocker has disappeared. Another participant is already working on such a view, and the discussion shifted towards having both a logging mechanism and a SQL view, potentially sharing a common internal function.

This thread discusses a patch to pg_upgrade that addresses a problem with replication origin IDs (roidents) not being preserved during upgrades of subscriber clusters. The existing pg_upgrade process can cause roidents to be reassigned or swapped, leading to update_origin_differs conflicts due to incorrect commit-timestamp records. The proposed solution involves modifying pg_dumpall to dump and restore all replication origins with their original roidents and LSN positions, and adjusting CREATE SUBSCRIPTION to use these preserved roidents. Discussions covered backward compatibility (supporting PG17+), handling of long replication origin names, and adding a consistency check to pg_upgrade to prevent errors if the new cluster already has conflicting replication origins. The proposer has submitted multiple patch versions, addressing various review comments and bug reports.

This thread focuses on the review and refinement of the first draft of the PostgreSQL 19 release notes. The proposer initially shared the draft, which contained 212 features, and outlined plans to document the creation methodology. Commenter 1 identified an inaccurate description of `pg_read_all_data()` and `pg_write_all_data()` as functions rather than roles, prompting the proposer to correct and re-categorize the item. Commenter 2 noted duplicate names in an entry for psql tab completion, which the proposer agreed to fix. Another commenter suggested including a psql tab completion fix for `REPACK` boolean options, but the proposer decided against it, explaining that such fixes are generally only added for existing commands. Commenter 3 provided comprehensive feedback on the 'Incompatibilities' section, suggesting wording changes and reordering of explanations, and offered improvements for 'Optimizer', 'General Performance', 'System Views', and 'Monitoring' sections. The proposer accepted several suggestions while explaining the rationale behind the current internal ordering of items within sections, which prioritizes grouping similar items for readability.

This extensive thread focuses on developing index prefetching functionality to enhance index scan performance through significant architectural changes to the table access method (AM) interface, including slot-based index scan interfaces and batch processing (`amgetbatch`). Multiple patch versions (v20-v26) were submitted, addressing bitrot, refining logic, and incorporating feedback on details like `int8` arithmetic for ring buffers and the optimal use of `TupleTableSlot` for index-only scans. Initially targeting Postgres 19, the developers decided to defer the patchset to Postgres 20 due to the need for more polish. Work has since resumed, with the latest patches showing robust performance gains and significant improvements, such as enabling GiST indexes to utilize `gistgetbatch` and eliminating the need for index-only scans to use a buffered `TupleTableSlot`.

The proposer identified an incorrect comment in the report_sequence_errors() function in sequencesync.c. The comment incorrectly stated that case (c) referred to "missing sequences on the subscriber," whereas the actual code logic and warning message refer to sequences missing on the publisher side. The proposed fix is a simple change to correct the comment from "subscriber" to "publisher" to accurately reflect the function's behavior. Reviewer 1 confirmed the fix and mentioned adding clarity about inconsistent definitions on the subscriber side.

The proposer identifies an issue where `ALTER TABLE ALTER COLUMN TYPE` operations on partitioned tables unexpectedly trigger a reindex, even for type changes that typically do not require it. This behavior is attributed to the current `ATRewriteCatalogs` process, which drops the parent table's partitioned index (cascading to child indexes) before child tables are processed, preventing child indexes from being properly remembered for rebuilding without reindexing. The proposed solution includes three patches. The first patch adds a regression test to demonstrate the bug. The second patch modifies `ATRewriteCatalogs` to batch deletions, ensuring child indexes are tracked and recreated first, potentially avoiding unnecessary reindexing. It also introduces a new `AT_PASS_OLD_PARTITIONED_INDEX` step for parent index recreation. The third patch extends similar changes to index-backed constraints.

The proposer identified a bug where virtual generated NOT NULL columns were not correctly handled during tuple deformation, leading to incorrect values (e.g., 0 instead of 20) being returned for subsequent columns. The issue stemmed from an incorrect calculation of firstNonGuaranteedAttr in TupleDescFinalize(), which failed to consider virtual generated attributes as non-guaranteed. The proposer submitted a patch to include ATTRIBUTE_GENERATED_VIRTUAL in the check. Commenter 1 confirmed the bug and suggested a related concern about attcacheoff, which the proposer addressed in v2. A reviewer then committed a slightly adjusted version of the patch, simplifying a condition and adding an Assert() for future checks. The proposer agreed with the adjustments. Commenter 2 later suggested adding more tests for the fix.

This thread discusses a long-standing bug where DELETE/UPDATE operations on foreign tables referencing remote partitioned tables can corrupt data if multiple rows are affected, due to reliance on ctid for identification. The proposer resurrected the issue and re-submitted an old patch that simply disallows multi-row DELETE/UPDATE operations. Commenter 1 demonstrated that this patch was still unsafe and proposed a new remotely_inherited table option for postgres_fdw to explicitly prevent such operations. The proposer expressed concern about this option's reliance on user configuration. Commenter 1 also dismissed the WHERE CURRENT OF approach due to performance concerns. Subsequently, Commenter 2 presented a new, more invasive patch set focusing on passing remote OID attributes through the executor to the FDW for accurate UPDATE/DELETE queries, which Commenter 1 then pointed out would not be back-patchable. Commenter 1 re-proposed their remotely_inherited patch, emphasizing its back-patchability and providing an updated version with postgresImportForeignSchema() support, which Commenter 2 agreed to review.

The proposer submitted a patch to reword two error messages related to publication membership: "relation \"%s\" is already member of publication \"%s\"" and "schema \"%s\" is already member of publication \"%s\". The proposed change updates "member of" to "a member of" to align with correct grammar and other similar error messages in PostgreSQL. Commenter 1 reviewed the patch, confirmed it applies cleanly, and noted that the wording matches existing messages, giving it an LGTM. Commenter 2 also approved the fix but suggested that, as a minor inconsistency not newly introduced, it should only be applied to the HEAD development branch and could wait until the next major release branch opens.

The proposer identified a bug where ALTER SUBSCRIPTION SET (retain_dead_tuples = true, origin = 'none') incorrectly bypassed the publisher version/recovery check. This occurred because the origin handling logic unconditionally overwrote the check_pub_rdt flag to false, even if retain_dead_tuples had set it to true within the same command. The proposed fix involves changing the assignment to use a bitwise OR assignment (|=) instead of a direct assignment (=), ensuring that the flag set by retain_dead_tuples cannot be inadvertently cleared. A test case was also provided to reproduce the problem. Commenter 1 and Commenter 2 reviewed and approved the fix, which has now been committed.

The proposer noticed an inconsistency in the LSN reported for pg_logical_emit_message() compared to other operations (INSERT, UPDATE, DELETE) during logical decoding. While pg_waldump shows the logical message record at a specific LSN (e.g., 0/017E97B8), pg_logical_slot_get_changes() reports an LSN that matches the following RUNNING_XACTS record (e.g., 0/017E97F8). For other operations like INSERT, UPDATE, and DELETE, the LSNs from both tools match the start LSN of the corresponding WAL records. The proposer has started investigating the internal queuing mechanisms (ReorderBufferQueueChange()) to understand this discrepancy.

The proposer identified an inconsistency in ECPG's GET/SET DESCRIPTOR functionality: while the precompiler accepts syntax with multiple COUNT assignments (e.g., :desc_count1 = count, :desc_count2 = count), it generates invalid C code, leading to compilation errors. The proposer's initial patch fixed this by making the precompiler reject such invalid syntax. Discussion explored whether to allow multiple COUNT clauses (by generating multiple ECPGget_desc_header calls) or to strictly disallow it. Following a review of SQL standards and Oracle's Pro*C (which disallows multiple COUNT), the consensus was to treat it as a syntax error to minimize special-purpose code and align with other systems. A reviewer proposed adding TAP tests, but it was decided against, as many other unsupported syntax cases are not tested. The fix was then confirmed and backported to all supported PostgreSQL versions.

The proposer identified a pg_upgrade failure scenario when upgrading PostgreSQL from version 17 to 18. The issue occurs when a foreign key (FK) constraint with the same name exists on both a partitioned parent table and one of its partitions. Specifically, if ALTER TABLE ONLY ... ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID is used on a partition, and then ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... (without ONLY) is used on the parent, pg_dump outputs both FK definitions. During pg_restore, the FK on the parent is implicitly inherited by the partition. The subsequent explicit ALTER TABLE ONLY ... ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID command for the partition then fails with "constraint 'fk_trn_acc' for relation 'trn_part1' already exists". The proposer asks if this is a known issue or a bug in pg_dump.

The proposer identified an issue with psql's \dRs+ command: when querying pg_foreign_server to display subscription server names, the query uses an unqualified reference (pg_foreign_server). This allows a malicious or careless user to create a temporary table named pg_foreign_server earlier in the search_path, which can then "pollute" or spoof the output of \dRs+ by displaying incorrect server names. This problem was introduced by a prior commit related to CREATE SUBSCRIPTION ... SERVER. The proposed patch fixes this by adding the pg_catalog schema qualification to the pg_foreign_server reference within the psql describe query, ensuring it always refers to the canonical catalog table and preventing spoofing.

This thread details critical security-related bugs found in the new FK existence-check fast path introduced in PostgreSQL 19 beta. The proposer identified three defects, including an out-of-bounds write and integrity bypasses, which are reachable by unprivileged table owners. These issues occur because user-defined code (cast functions and equality operators) can be executed during a deferred batch flush, leading to re-entrancy problems. Detailed reproduction steps for these defects were provided, confirming their presence in master and REL_19_BETA1. A reviewer acknowledged the report and has started investigating, adding the issues to the PostgreSQL 19 Open Items list.

This thread addresses a bug where LIKE expressions with literal backslashes fail to produce correct results when used with non-deterministic collations. The proposer identified that the issue stemmed from incorrect backslash escaping in like_match.c introduced in an earlier commit. An initial patch was submitted, which was then refined based on feedback from reviewer 1 regarding code readability and multibyte encoding handling. Reviewer 2 performed a thorough review of the updated patch (v2), confirmed it fixed both false-negative and false-positive cases of the bug, and suggested improvements to the commit message and a minor typo. The proposer incorporated these suggestions into a v3 patch, which also included an additional regression test case. Reviewer 2 then confirmed the v3 patch passed all tests and changed its CommitFest status to "Ready for Committer."