pgsql: pg_dump: provide a stable sort order for rules.
pg_dump: provide a stable sort order for rules. Previously, we sorted rules by schema name and then rule name; if that wasn't unique, we sorted by rule OID. This can be problematic for comparing dumps from databases with different histories, especially since certain rule names like "_RETURN" are very common. Let's make the sort key schema name, rule name, table name, which should be unique. (This is the same behavior we've long used for triggers and RLS policies.) Andreas Karlsson This back-patches v18 commit 350e6b8ea86c22c0b95c2e32a4e8d109255b5596 to all supported branches. The next commit will assert that pg_dump provides a stable sort order for all object types. That assertion would fail without stabilizing DO_RULE order as this commit did. Discussion: https://postgr.es/m/b4e468d8-0cd6-42e6-ac8a-1d6afa6e0...@proxel.se Discussion: https://postgr.es/m/20250707192654.9e.nmi...@google.com Backpatch-through: 13-17 Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/25388fb2cb842bb62d9bf2a884924bd7cb35b5ba Author: Tom Lane Modified Files -- src/bin/pg_dump/pg_dump_sort.c | 11 +++ 1 file changed, 11 insertions(+)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: pg_dump: provide a stable sort order for rules.
pg_dump: provide a stable sort order for rules. Previously, we sorted rules by schema name and then rule name; if that wasn't unique, we sorted by rule OID. This can be problematic for comparing dumps from databases with different histories, especially since certain rule names like "_RETURN" are very common. Let's make the sort key schema name, rule name, table name, which should be unique. (This is the same behavior we've long used for triggers and RLS policies.) Andreas Karlsson This back-patches v18 commit 350e6b8ea86c22c0b95c2e32a4e8d109255b5596 to all supported branches. The next commit will assert that pg_dump provides a stable sort order for all object types. That assertion would fail without stabilizing DO_RULE order as this commit did. Discussion: https://postgr.es/m/b4e468d8-0cd6-42e6-ac8a-1d6afa6e0...@proxel.se Discussion: https://postgr.es/m/20250707192654.9e.nmi...@google.com Backpatch-through: 13-17 Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/cc9a62c51a1894a9c838becf558abb056c904fb1 Author: Tom Lane Modified Files -- src/bin/pg_dump/pg_dump_sort.c | 11 +++ 1 file changed, 11 insertions(+)
pgsql: pg_dump: provide a stable sort order for rules.
pg_dump: provide a stable sort order for rules. Previously, we sorted rules by schema name and then rule name; if that wasn't unique, we sorted by rule OID. This can be problematic for comparing dumps from databases with different histories, especially since certain rule names like "_RETURN" are very common. Let's make the sort key schema name, rule name, table name, which should be unique. (This is the same behavior we've long used for triggers and RLS policies.) Andreas Karlsson This back-patches v18 commit 350e6b8ea86c22c0b95c2e32a4e8d109255b5596 to all supported branches. The next commit will assert that pg_dump provides a stable sort order for all object types. That assertion would fail without stabilizing DO_RULE order as this commit did. Discussion: https://postgr.es/m/b4e468d8-0cd6-42e6-ac8a-1d6afa6e0...@proxel.se Discussion: https://postgr.es/m/20250707192654.9e.nmi...@google.com Backpatch-through: 13-17 Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/9affed26349acafb42471de34f37dc06b1a3879e Author: Tom Lane Modified Files -- src/bin/pg_dump/pg_dump_sort.c | 11 +++ 1 file changed, 11 insertions(+)
pgsql: pg_dump: provide a stable sort order for rules.
pg_dump: provide a stable sort order for rules. Previously, we sorted rules by schema name and then rule name; if that wasn't unique, we sorted by rule OID. This can be problematic for comparing dumps from databases with different histories, especially since certain rule names like "_RETURN" are very common. Let's make the sort key schema name, rule name, table name, which should be unique. (This is the same behavior we've long used for triggers and RLS policies.) Andreas Karlsson This back-patches v18 commit 350e6b8ea86c22c0b95c2e32a4e8d109255b5596 to all supported branches. The next commit will assert that pg_dump provides a stable sort order for all object types. That assertion would fail without stabilizing DO_RULE order as this commit did. Discussion: https://postgr.es/m/b4e468d8-0cd6-42e6-ac8a-1d6afa6e0...@proxel.se Discussion: https://postgr.es/m/20250707192654.9e.nmi...@google.com Backpatch-through: 13-17 Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/e99010cbd8e27d4074646f508e9a273fd0e9322e Author: Tom Lane Modified Files -- src/bin/pg_dump/pg_dump_sort.c | 11 +++ 1 file changed, 11 insertions(+)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: pg_dump: provide a stable sort order for rules.
pg_dump: provide a stable sort order for rules. Previously, we sorted rules by schema name and then rule name; if that wasn't unique, we sorted by rule OID. This can be problematic for comparing dumps from databases with different histories, especially since certain rule names like "_RETURN" are very common. Let's make the sort key schema name, rule name, table name, which should be unique. (This is the same behavior we've long used for triggers and RLS policies.) Andreas Karlsson This back-patches v18 commit 350e6b8ea86c22c0b95c2e32a4e8d109255b5596 to all supported branches. The next commit will assert that pg_dump provides a stable sort order for all object types. That assertion would fail without stabilizing DO_RULE order as this commit did. Discussion: https://postgr.es/m/b4e468d8-0cd6-42e6-ac8a-1d6afa6e0...@proxel.se Discussion: https://postgr.es/m/20250707192654.9e.nmi...@google.com Backpatch-through: 13-17 Branch -- REL_17_STABLE Details --- https://git.postgresql.org/pg/commitdiff/5dd4957b28df92d25508e4d0c950099c52347a75 Author: Tom Lane Modified Files -- src/bin/pg_dump/pg_dump_sort.c | 11 +++ 1 file changed, 11 insertions(+)
pgsql: Sort dump objects independent of OIDs, for the 7 holdout object
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 Discussion: https://postgr.es/m/20250707192654.9e.nmi...@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(-)
pgsql: Schema-qualify unnest() in ALTER DATABASE ... RESET
Schema-qualify unnest() in ALTER DATABASE ... RESET Commit 9df8727c5067 failed to schema-quality the unnest() call in the query used to list the variables in ALTER DATABASE ... RESET. If there's another unnest() function in the search_path, this could cause either failures, or even security issues (when the tab-completion gets used by privileged accounts). Report and fix by Dagfinn Ilmari Mannsåker. Backpatch to 18, same as 9df8727c5067. Author: Dagfinn Ilmari Mannsåker Reviewed-by: jian he Discussion: https://postgr.es/m/87qzyghw2x.fsf%40wibble.ilmari.org Discussion: https://postgr.es/m/87tt4lumqz.fsf%40wibble.ilmari.org Backpatch-through: 18 Branch -- REL_18_STABLE Details --- https://git.postgresql.org/pg/commitdiff/72c437f6e464e6fd35a84d3fde7795cb13a7b453 Modified Files -- src/bin/psql/tab-complete.in.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Fix tab completion for ALTER ROLE|USER ... RESET
Fix tab completion for ALTER ROLE|USER ... RESET Commit c407d5426b87 added tab completion for ALTER ROLE|USER ... RESET, with the intent to offer only the variables actually set on the role. But as soon as the user started typing something, it would start to offer all possible matching variables. Fix this the same way ALTER DATABASE ... RESET does it, i.e. by properly considering the prefix. A second issue causing similar symptoms (offering variables that are not actually set for a role) was caused by a match to another pattern. The ALTER DATABASE ... RESET was already excluded, so do the same thing for ROLE/USER. Report and fix by Dagfinn Ilmari Mannsåker. Backpatch to 18, same as c407d5426b87. Author: Dagfinn Ilmari Mannsåker Reviewed-by: jian he Discussion: https://postgr.es/m/87qzyghw2x.fsf%40wibble.ilmari.org Discussion: https://postgr.es/m/87tt4lumqz.fsf%40wibble.ilmari.org Backpatch-through: 18 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/ca09ef3a6aa69a1250bc83e6d9517f28a2ff181c Modified Files -- src/bin/psql/tab-complete.in.c | 14 ++ 1 file changed, 10 insertions(+), 4 deletions(-)
pgsql: Schema-qualify unnest() in ALTER DATABASE ... RESET
Schema-qualify unnest() in ALTER DATABASE ... RESET Commit 9df8727c5067 failed to schema-quality the unnest() call in the query used to list the variables in ALTER DATABASE ... RESET. If there's another unnest() function in the search_path, this could cause either failures, or even security issues (when the tab-completion gets used by privileged accounts). Report and fix by Dagfinn Ilmari Mannsåker. Backpatch to 18, same as 9df8727c5067. Author: Dagfinn Ilmari Mannsåker Reviewed-by: jian he Discussion: https://postgr.es/m/87qzyghw2x.fsf%40wibble.ilmari.org Discussion: https://postgr.es/m/87tt4lumqz.fsf%40wibble.ilmari.org Backpatch-through: 18 Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/dbf5a83d4650fc893838a2f92306b3d6439f55ba Modified Files -- src/bin/psql/tab-complete.in.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-)
pgsql: Fix tab completion for ALTER ROLE|USER ... RESET
Fix tab completion for ALTER ROLE|USER ... RESET Commit c407d5426b87 added tab completion for ALTER ROLE|USER ... RESET, with the intent to offer only the variables actually set on the role. But as soon as the user started typing something, it would start to offer all possible matching variables. Fix this the same way ALTER DATABASE ... RESET does it, i.e. by properly considering the prefix. A second issue causing similar symptoms (offering variables that are not actually set for a role) was caused by a match to another pattern. The ALTER DATABASE ... RESET was already excluded, so do the same thing for ROLE/USER. Report and fix by Dagfinn Ilmari Mannsåker. Backpatch to 18, same as c407d5426b87. Author: Dagfinn Ilmari Mannsåker Reviewed-by: jian he Discussion: https://postgr.es/m/87qzyghw2x.fsf%40wibble.ilmari.org Discussion: https://postgr.es/m/87tt4lumqz.fsf%40wibble.ilmari.org Backpatch-through: 18 Branch -- REL_18_STABLE Details --- https://git.postgresql.org/pg/commitdiff/88914332eaed702afb2cdbb0b776822738d58ece Modified Files -- src/bin/psql/tab-complete.in.c | 14 ++ 1 file changed, 10 insertions(+), 4 deletions(-)
