pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix pg_dump for hash partitioning on enum columns. 6+ messages / 1 participants [nested] [flat]
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/bc8cd50fefd369b217f80078585c486505aafb62 Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 18 ++-- src/bin/pg_dump/meson.build | 1 + src/bin/pg_dump/pg_backup_archiver.c | 73 +++++++++++++-- src/bin/pg_dump/pg_dump.c | 151 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 8 files changed, 288 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2b216da1e55dc125ada193328e87e471d49b0938 Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 18 ++-- src/bin/pg_dump/pg_backup_archiver.c | 73 +++++++++++++-- src/bin/pg_dump/pg_dump.c | 151 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 7 files changed, 287 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7e7c5b683985c85fb990c4d49ab960cbc83434b4 Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 18 ++-- src/bin/pg_dump/pg_backup_archiver.c | 73 +++++++++++++-- src/bin/pg_dump/pg_dump.c | 151 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 7 files changed, 287 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/8f83ce8c5244ce40514e8643a648704d8c85baa9 Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 18 ++-- src/bin/pg_dump/pg_backup_archiver.c | 73 +++++++++++++-- src/bin/pg_dump/pg_dump.c | 151 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 7 files changed, 287 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/5fc1ac151d85a4da8724e1d886fbb6fe3ff519c0 Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 18 ++-- src/bin/pg_dump/pg_backup_archiver.c | 73 +++++++++++++-- src/bin/pg_dump/pg_dump.c | 151 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 7 files changed, 287 insertions(+), 52 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix pg_dump for hash partitioning on enum columns. @ 2023-03-17 17:31 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2023-03-17 17:31 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix pg_dump for hash partitioning on enum columns. Hash partitioning on an enum is problematic because the hash codes are derived from the OIDs assigned to the enum values, which will almost certainly be different after a dump-and-reload than they were before. This means that some rows probably end up in different partitions than before, causing restore to fail because of partition constraint violations. (pg_upgrade dodges this problem by using hacks to force the enum values to keep the same OIDs, but that's not possible nor desirable for pg_dump.) Users can work around that by specifying --load-via-partition-root, but since that's a dump-time not restore-time decision, one might find out the need for it far too late. Instead, teach pg_dump to apply that option automatically when dealing with a partitioned table that has hash-on-enum partitioning. Also deal with a pre-existing issue for --load-via-partition-root mode: in a parallel restore, we try to TRUNCATE target tables just before loading them, in order to enable some backend optimizations. This is bad when using --load-via-partition-root because (a) we're likely to suffer deadlocks from restore jobs trying to restore rows into other partitions than they came from, and (b) if we miss getting a deadlock we might still lose data due to a TRUNCATE removing rows from some already-completed restore job. The fix for this is conceptually simple: just don't TRUNCATE if we're dealing with a --load-via-partition-root case. The tricky bit is for pg_restore to identify those cases. In dumps using COPY commands we can inspect each COPY command to see if it targets the nominal target table or some ancestor. However, in dumps using INSERT commands it's pretty impractical to examine the INSERTs in advance. To provide a solution for that going forward, modify pg_dump to mark TABLE DATA items that are using --load-via-partition-root with a comment. (This change also responds to a complaint from Robert Haas that the dump output for --load-via-partition-root is pretty confusing.) pg_restore checks for the special comment as well as checking the COPY command if present. This will fail to identify the combination of --load-via-partition-root and --inserts in pre-existing dump files, but that should be a pretty rare case in the field. If it does happen you will probably get a deadlock failure that you can work around by not using parallel restore, which is the same as before this bug fix. Having done this, there seems no remaining reason for the alarmism in the pg_dump man page about combining --load-via-partition-root with parallel restore, so remove that warning. Patch by me; thanks to Julien Rouhaud for review. Back-patch to v11 where hash partitioning was introduced. Discussion: https://postgr.es/m/1376149.1675268279@sss.pgh.pa.us Branch ------ REL_11_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/012ffb365a05694e392bf96c8363d119bd19534c Modified Files -------------- doc/src/sgml/ref/pg_dump.sgml | 10 -- doc/src/sgml/ref/pg_dumpall.sgml | 4 - src/bin/pg_dump/common.c | 19 ++-- src/bin/pg_dump/pg_backup_archiver.c | 73 ++++++++++++-- src/bin/pg_dump/pg_dump.c | 153 +++++++++++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/004_pg_dump_parallel.pl | 81 ++++++++++++++++ 7 files changed, 289 insertions(+), 53 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-03-17 17:31 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us> 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us> 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us> 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us> 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us> 2023-03-17 17:31 pgsql: Fix pg_dump for hash partitioning on enum columns. Tom Lane <tgl@sss.pgh.pa.us>
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