pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object 7+ messages / 1 participants [nested] [flat]
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/c0ae03384fa3ffc79423f07ce369455ea9086da9 Modified Files -------------- src/bin/pg_dump/common.c | 19 +++ src/bin/pg_dump/pg_dump.c | 59 ++++++-- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 238 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 21 +++ src/test/regress/sql/publication.sql | 22 +++ 6 files changed, 335 insertions(+), 30 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/22f126da6ccebdafea77908a4a80d73d7735dcf1 Modified Files -------------- src/bin/pg_dump/common.c | 19 +++ src/bin/pg_dump/pg_dump.c | 62 ++++++-- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 238 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 21 +++ src/test/regress/sql/publication.sql | 22 +++ 6 files changed, 335 insertions(+), 33 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7ee7c1cd389e32599c56ad88a4e74c625ab65d52 Modified Files -------------- src/bin/pg_dump/common.c | 19 ++- src/bin/pg_dump/pg_dump.c | 79 ++++++++--- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 227 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 8 ++ src/test/regress/sql/publication.sql | 9 ++ 6 files changed, 309 insertions(+), 39 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1ca1889ea6a31e4b4f128e849c614959090f2c1c Modified Files -------------- src/bin/pg_dump/common.c | 19 +++ src/bin/pg_dump/pg_dump.c | 62 ++++++-- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 238 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 21 +++ src/test/regress/sql/publication.sql | 22 +++ 6 files changed, 335 insertions(+), 33 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0decd5e89db9f5edb9b27351082f0d74aae7a9b6 Modified Files -------------- src/bin/pg_dump/common.c | 19 +++ src/bin/pg_dump/pg_dump.c | 59 ++++++-- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 238 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 21 +++ src/test/regress/sql/publication.sql | 22 +++ 6 files changed, 335 insertions(+), 30 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/04bc2c42f765f16e1df4d1504b871d638d6b47e8 Modified Files -------------- src/bin/pg_dump/common.c | 19 ++- src/bin/pg_dump/pg_dump.c | 79 ++++++++--- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 227 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 8 ++ src/test/regress/sql/publication.sql | 9 ++ 6 files changed, 309 insertions(+), 39 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Sort dump objects independent of OIDs, for the 7 holdout object @ 2025-07-31 13:43 Noah Misch <noah@leadboat.com> 0 siblings, 0 replies; 7+ messages in thread From: Noah Misch @ 2025-07-31 13:43 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Sort dump objects independent of OIDs, for the 7 holdout object types. pg_dump sorts objects by their logical names, e.g. (nspname, relname, tgname), before dependency-driven reordering. That removes one source of logically-identical databases differing in their schema-only dumps. In other words, it helps with schema diffing. The logical name sort ignored essential sort keys for constraints, operators, PUBLICATION ... FOR TABLE, PUBLICATION ... FOR TABLES IN SCHEMA, operator classes, and operator families. pg_dump's sort then depended on object OID, yielding spurious schema diffs. After this change, OIDs affect dump order only in the event of catalog corruption. While pg_dump also wrongly ignored pg_collation.collencoding, CREATE COLLATION restrictions have been keeping that imperceptible in practical use. Use techniques like we use for object types already having full sort key coverage. Where the pertinent queries weren't fetching the ignored sort keys, this adds columns to those queries and stores those keys in memory for the long term. The ignorance of sort keys became more problematic when commit 172259afb563d35001410dc6daad78b250924038 added a schema diff test sensitive to it. Buildfarm member hippopotamus witnessed that. However, dump order stability isn't a new goal, and this might avoid other dump comparison failures. Hence, back-patch to v13 (all supported versions). Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/20250707192654.9e.nmisch@google.com Backpatch-through: 13 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0ac1581c3f2dd02009f0bfa453854946d56e1fc4 Modified Files -------------- src/bin/pg_dump/common.c | 19 +++ src/bin/pg_dump/pg_dump.c | 62 ++++++-- src/bin/pg_dump/pg_dump.h | 6 + src/bin/pg_dump/pg_dump_sort.c | 238 +++++++++++++++++++++++++++--- src/test/regress/expected/publication.out | 21 +++ src/test/regress/sql/publication.sql | 22 +++ 6 files changed, 335 insertions(+), 33 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-07-31 13:43 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com> 2025-07-31 13:43 pgsql: Sort dump objects independent of OIDs, for the 7 holdout object Noah Misch <noah@leadboat.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox