On Wed, 27 May 2026 at 14:04, vignesh C <[email protected]> wrote: > > > I have fixed the rest of the comments. The attached v41 version patch > has the changes for the same. Additionally the comments from [1] have > also been fixed.
I was evaluating whether the existing pg_upgrade changes for conflict log tables can handle the addition of new columns in a future release. To validate this, I performed the following: Added two new columns to the conflict log table: v20_new_col1 TEXT v20_new_col2 TEXT These changes are present in patch '0001'. For adding new columns during binary upgrade, the following version-specific logic is required in 'pg_dump': ALTER TABLE pg_conflict.pg_conflict_log_for_subid_oid ADD COLUMN v20_new_col1 TEXT; ALTER TABLE pg_conflict.pg_conflict_log_for_subid_oid ADD COLUMN v20_new_col2 TEXT; These changes are included in patch '0001'. One important point here is that when 'ALTER TABLE ... ADD COLUMN' is run, the server does not rewrite existing rows on disk. Instead, it only updates the system catalog with the new column metadata. While selecting data from the table, the server handles this as follows: 1. Deform what is physically present - 'slot_deform_heap_tuple()' reads the raw tuple bytes from disk, but only up to 't_natts', which is the number of columns recorded in the tuple header at the time that row was inserted. It stops there because the tuple has no physical data for columns added later. 2. Fill in what is missing - After deforming the tuple, if the number of populated columns is still less than the number of columns requested by the query, it calls 'slot_getmissingattrs()' to cover the gap. Since the new columns were added with no default value, 'slot_getmissingattrs()' sets: tts_isnull[attnum] = true; This is how NULL is returned for the newly added columns in existing rows. These changes were tested on a new server with the v40 version patch + '0001' patch. 1. Pre-upgrade state using v40 version patches Simulated conflicts using a setup where the schema does not include the new columns: postgres=# select * from pg_conflict.pg_conflict_log_for_subid_16396 ; .... (4 rows) 2. Upgrade using 'pg_upgrade' The upgrade was performed on a cluster initialized with patches v40 + '0001', and it completed successfully. Post-upgrade verification: postgres=# select conflict_type, v20_new_col1, v20_new_col2 from pg_conflict.pg_conflict_log_for_subid_16396 ; conflict_type | v20_new_col1 | v20_new_col2 ---------------+--------------+-------------- insert_exists | | insert_exists | | insert_exists | | insert_exists | | (4 rows) Existing rows were preserved, and the newly added columns are visible and populated with NULLs, as expected. 3. Post-upgrade conflict insertion After starting the old publisher again to continue generating conflicts: postgres=# select conflict_type, v20_new_col1, v20_new_col2 from pg_conflict.pg_conflict_log_for_subid_16396 ; conflict_type | v20_new_col1 | v20_new_col2 ---------------+--------------+-------------- insert_exists | | insert_exists | | insert_exists | | insert_exists | | insert_exists | v20_new_col1 | v20_new_col2 insert_exists | v20_new_col1 | v20_new_col2 insert_exists | v20_new_col1 | v20_new_col2 (7 rows) New conflicts are inserted successfully, and the newly added columns are correctly populated for new entries. Based on this testing, the current 'pg_upgrade' framework, along with the additional dump-time adjustments, appears sufficient to support schema evolution of conflict log tables, specifically for adding new columns in future releases. Thoughts? Regards, Vignesh
0001-Add-new-columns-to-CLT-and-add-upgrade-changes.patch
Description: Binary data
