This is an automated email from the ASF dual-hosted git repository. yjhjstz pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 2f4bafda0ddccf2c8de2ec3777c0575a3110c209 Author: Kalen Krempely <[email protected]> AuthorDate: Mon Oct 24 19:05:23 2022 -0700 fix upgrading external tables with dropped cols External tables with dropped columns failed to upgrade with: ``` pg_restore: creating EXTERNAL TABLE ext_upgrade_failure_test pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 237; 1259 18142 EXTERNAL TABLE ext_upgrade_failure_test gpadmin pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "," LINE 14: "........pg.dropped.1........" , ^ ``` Fix pg_dump to output the ALTER TABLE DROP COLUMN DDL for external tables with dropped columns. --- src/bin/pg_dump/pg_dump.c | 15 ++++++++++++++- src/test/regress/expected/pg_dump_binary_upgrade.out | 18 ++++++++++++++++++ src/test/regress/greenplum_schedule | 2 ++ src/test/regress/sql/pg_dump_binary_upgrade.sql | 20 ++++++++++++++++++++ 4 files changed, 54 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index bfdf20beab..1b5d41796a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -17720,7 +17720,20 @@ dumpExternal(Archive *fout, const TableInfo *tbinfo, PQExpBuffer q, PQExpBuffer appendPQExpBuffer(q, "%s ", fmtId(tbinfo->attnames[j])); /* Attribute type */ - appendPQExpBufferStr(q, tbinfo->atttypnames[j]); + if (tbinfo->attisdropped[j]) + { + /* + * ALTER TABLE DROP COLUMN clears + * pg_attribute.atttypid, so we will not have gotten a + * valid type name; insert INTEGER as a stopgap. We'll + * clean things up later. + */ + appendPQExpBufferStr(q, " INTEGER /* dummy */"); + } + else + { + appendPQExpBufferStr(q, tbinfo->atttypnames[j]); + } actual_atts++; } diff --git a/src/test/regress/expected/pg_dump_binary_upgrade.out b/src/test/regress/expected/pg_dump_binary_upgrade.out new file mode 100644 index 0000000000..992a9784ea --- /dev/null +++ b/src/test/regress/expected/pg_dump_binary_upgrade.out @@ -0,0 +1,18 @@ +-- Ensure that pg_dump --binary-upgrade correctly outputs +-- ALTER TABLE DROP COLUMN DDL for external tables. +CREATE SCHEMA dump_this_schema; +-- External tables with dropped columns is dumped correctly. +CREATE EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns ( + a int, + b int, + c int +) LOCATION ('gpfdist://1.1.1.1:8082/xxxx.csv') FORMAT 'csv'; +ALTER EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns DROP COLUMN a; +ALTER EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns DROP COLUMN b; +-- Run pg_dump and expect to see an ALTER TABLE DROP COLUMN output +-- for the tables with dropped column references. +\! pg_dump --binary-upgrade --schema dump_this_schema regression | grep " DROP COLUMN " +ALTER FOREIGN TABLE ONLY dump_this_schema.external_table_with_dropped_columns DROP COLUMN "........pg.dropped.1........"; +ALTER FOREIGN TABLE ONLY dump_this_schema.external_table_with_dropped_columns DROP COLUMN "........pg.dropped.2........"; +DROP SCHEMA dump_this_schema CASCADE; +NOTICE: drop cascades to foreign table dump_this_schema.external_table_with_dropped_columns diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule index 8f83c38787..58d65b4cb1 100755 --- a/src/test/regress/greenplum_schedule +++ b/src/test/regress/greenplum_schedule @@ -18,6 +18,8 @@ # test for builtin namespace pg_ext_aux test: pg_ext_aux +test: pg_dump_binary_upgrade + test: gp_dispatch_keepalives # copy command diff --git a/src/test/regress/sql/pg_dump_binary_upgrade.sql b/src/test/regress/sql/pg_dump_binary_upgrade.sql new file mode 100644 index 0000000000..70291e049c --- /dev/null +++ b/src/test/regress/sql/pg_dump_binary_upgrade.sql @@ -0,0 +1,20 @@ +-- Ensure that pg_dump --binary-upgrade correctly outputs +-- ALTER TABLE DROP COLUMN DDL for external tables. + +CREATE SCHEMA dump_this_schema; + +-- External tables with dropped columns is dumped correctly. +CREATE EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns ( + a int, + b int, + c int +) LOCATION ('gpfdist://1.1.1.1:8082/xxxx.csv') FORMAT 'csv'; + +ALTER EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns DROP COLUMN a; +ALTER EXTERNAL TABLE dump_this_schema.external_table_with_dropped_columns DROP COLUMN b; + +-- Run pg_dump and expect to see an ALTER TABLE DROP COLUMN output +-- for the tables with dropped column references. +\! pg_dump --binary-upgrade --schema dump_this_schema regression | grep " DROP COLUMN " + +DROP SCHEMA dump_this_schema CASCADE; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
