github.com/cloudberrydb/gpbackup@v1.0.3-0.20240118031043-5410fd45eed6/integration/predata_externals_queries_test.go (about) 1 package integration 2 3 import ( 4 "database/sql" 5 6 "github.com/cloudberrydb/gp-common-go-libs/structmatcher" 7 "github.com/cloudberrydb/gp-common-go-libs/testhelper" 8 "github.com/cloudberrydb/gpbackup/backup" 9 "github.com/cloudberrydb/gpbackup/testutils" 10 11 . "github.com/onsi/ginkgo/v2" 12 . "github.com/onsi/gomega" 13 ) 14 15 var _ = Describe("backup integration tests", func() { 16 Describe("GetExternalTableDefinitions", func() { 17 It("returns a slice for a basic external table definition", func() { 18 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 19 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 20 testhelper.AssertQueryRuns(connectionPool, `CREATE READABLE EXTERNAL TABLE public.ext_table(i int) 21 LOCATION ('file://tmp/myfile.txt') 22 FORMAT 'TEXT'`) 23 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL TABLE public.ext_table") 24 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 25 26 results := backup.GetExternalTableDefinitions(connectionPool) 27 result := results[oid] 28 29 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "file://tmp/myfile.txt", Valid: true}, 30 ExecLocation: "ALL_SEGMENTS", FormatType: "t", FormatOpts: "delimiter ' ' null '\\N' escape '\\'", 31 Command: "", RejectLimit: 0, RejectLimitType: "", ErrTableName: "", ErrTableSchema: "", Encoding: "UTF8", 32 Writable: false, URIs: []string{"file://tmp/myfile.txt"}} 33 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 34 }) 35 It("returns a slice for a basic external web table definition", func() { 36 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 37 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 38 testhelper.AssertQueryRuns(connectionPool, `CREATE READABLE EXTERNAL WEB TABLE public.ext_table(i int) 39 EXECUTE 'hostname' 40 FORMAT 'TEXT'`) 41 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL WEB TABLE public.ext_table") 42 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 43 44 results := backup.GetExternalTableDefinitions(connectionPool) 45 result := results[oid] 46 47 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "", Valid: true}, 48 ExecLocation: "ALL_SEGMENTS", FormatType: "t", FormatOpts: "delimiter ' ' null '\\N' escape '\\'", 49 Command: "hostname", RejectLimit: 0, RejectLimitType: "", ErrTableName: "", ErrTableSchema: "", Encoding: "UTF8", 50 Writable: false, URIs: nil} 51 if true { 52 // The query for GPDB 7+ will have a NULL value instead of "" 53 extTable.Location.Valid = false 54 } 55 56 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 57 }) 58 It("returns a slice for a complex external table definition", func() { 59 testhelper.AssertQueryRuns(connectionPool, `CREATE READABLE EXTERNAL TABLE public.ext_table(i int) 60 LOCATION ('file://tmp/myfile.txt') 61 FORMAT 'TEXT' 62 LOG ERRORS 63 SEGMENT REJECT LIMIT 10 PERCENT 64 `) 65 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL TABLE public.ext_table") 66 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 67 68 results := backup.GetExternalTableDefinitions(connectionPool) 69 result := results[oid] 70 71 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "file://tmp/myfile.txt", Valid: true}, 72 ExecLocation: "ALL_SEGMENTS", FormatType: "t", FormatOpts: "delimiter ' ' null '\\N' escape '\\'", 73 Command: "", RejectLimit: 10, RejectLimitType: "p", LogErrors: true, Encoding: "UTF8", 74 Writable: false, URIs: []string{"file://tmp/myfile.txt"}} 75 76 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 77 }) 78 It("returns a slice for an external table using CSV format", func() { 79 testhelper.AssertQueryRuns(connectionPool, `CREATE READABLE EXTERNAL TABLE public.ext_table(i int) 80 LOCATION ('file://tmp/myfile.txt') 81 FORMAT 'CSV' (delimiter E'|' null E'' escape E'\'' quote E'\'' force not null i) 82 LOG ERRORS 83 SEGMENT REJECT LIMIT 10 PERCENT 84 `) 85 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL TABLE public.ext_table") 86 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 87 88 results := backup.GetExternalTableDefinitions(connectionPool) 89 result := results[oid] 90 91 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "file://tmp/myfile.txt", Valid: true}, 92 ExecLocation: "ALL_SEGMENTS", FormatType: "c", FormatOpts: `delimiter '|' null '' escape ''' quote ''' force not null i`, 93 Command: "", RejectLimit: 10, RejectLimitType: "p", LogErrors: true, Encoding: "UTF8", 94 Writable: false, URIs: []string{"file://tmp/myfile.txt"}} 95 96 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 97 }) 98 It("returns a slice for an external table using CUSTOM format", func() { 99 testhelper.AssertQueryRuns(connectionPool, `CREATE READABLE EXTERNAL TABLE public.ext_table(i int) 100 LOCATION ('file://tmp/myfile.txt') 101 FORMAT 'CUSTOM' (formatter = E'fixedwidth_out', i = E'20') 102 LOG ERRORS 103 SEGMENT REJECT LIMIT 10 PERCENT 104 `) 105 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL TABLE public.ext_table") 106 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 107 108 results := backup.GetExternalTableDefinitions(connectionPool) 109 result := results[oid] 110 111 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "file://tmp/myfile.txt", Valid: true}, 112 ExecLocation: "ALL_SEGMENTS", FormatType: "b", FormatOpts: "formatter 'fixedwidth_out' i '20' ", 113 Command: "", RejectLimit: 10, RejectLimitType: "p", LogErrors: true, Encoding: "UTF8", 114 Writable: false, URIs: []string{"file://tmp/myfile.txt"}} 115 if true { 116 extTable.FormatOpts = "formatter 'fixedwidth_out'i '20'" 117 } 118 119 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 120 }) 121 It("returns a slice for a complex external table definition TEXT format delimiter", func() { 122 testutils.SkipIfBefore5(connectionPool) 123 testhelper.AssertQueryRuns(connectionPool, `CREATE EXTERNAL TABLE public.ext_table ( 124 i int 125 ) LOCATION ( 126 'file://tmp/myfile.txt' 127 ) ON ALL 128 FORMAT 'text' (delimiter E'%' null E'' escape E'OFF') 129 ENCODING 'UTF8' 130 LOG ERRORS PERSISTENTLY SEGMENT REJECT LIMIT 10 PERCENT`) 131 defer testhelper.AssertQueryRuns(connectionPool, "DROP EXTERNAL TABLE public.ext_table") 132 oid := testutils.OidFromObjectName(connectionPool, "public", "ext_table", backup.TYPE_RELATION) 133 134 results := backup.GetExternalTableDefinitions(connectionPool) 135 result := results[oid] 136 137 extTable := backup.ExternalTableDefinition{Oid: 0, Type: 0, Protocol: 0, Location: sql.NullString{String: "file://tmp/myfile.txt", Valid: true}, 138 ExecLocation: "ALL_SEGMENTS", FormatType: "t", FormatOpts: "delimiter '%' null '' escape 'OFF'", 139 Command: "", RejectLimit: 10, RejectLimitType: "p", LogErrors: true, LogErrPersist: true, Encoding: "UTF8", 140 Writable: false, URIs: []string{"file://tmp/myfile.txt"}} 141 142 structmatcher.ExpectStructsToMatchExcluding(&extTable, &result, "Oid") 143 }) 144 }) 145 Describe("GetExternalProtocols", func() { 146 It("returns a slice for a protocol", func() { 147 testhelper.AssertQueryRuns(connectionPool, "CREATE OR REPLACE FUNCTION public.write_to_s3() RETURNS integer AS '$libdir/gps3ext.so', 's3_export' LANGUAGE C STABLE;") 148 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.write_to_s3()") 149 testhelper.AssertQueryRuns(connectionPool, "CREATE OR REPLACE FUNCTION public.read_from_s3() RETURNS integer AS '$libdir/gps3ext.so', 's3_import' LANGUAGE C STABLE;") 150 defer testhelper.AssertQueryRuns(connectionPool, "DROP FUNCTION public.read_from_s3()") 151 testhelper.AssertQueryRuns(connectionPool, "CREATE PROTOCOL s3 (writefunc = public.write_to_s3, readfunc = public.read_from_s3);") 152 defer testhelper.AssertQueryRuns(connectionPool, "DROP PROTOCOL s3") 153 154 readFunctionOid := testutils.OidFromObjectName(connectionPool, "public", "read_from_s3", backup.TYPE_FUNCTION) 155 writeFunctionOid := testutils.OidFromObjectName(connectionPool, "public", "write_to_s3", backup.TYPE_FUNCTION) 156 157 results := backup.GetExternalProtocols(connectionPool) 158 159 protocolDef := backup.ExternalProtocol{Oid: 1, Name: "s3", Owner: "testrole", Trusted: false, ReadFunction: readFunctionOid, WriteFunction: writeFunctionOid, Validator: 0} 160 161 Expect(results).To(HaveLen(1)) 162 structmatcher.ExpectStructsToMatchExcluding(&protocolDef, &results[0], "Oid") 163 }) 164 }) 165 Describe("GetExternalPartitionInfo", func() { 166 BeforeEach(func() { 167 // For GPDB 7+, external partitions will have their own ATTACH PARTITION DDL commands. 168 if true { 169 Skip("Test is not applicable to GPDB 7+") 170 } 171 }) 172 AfterEach(func() { 173 testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_tbl") 174 testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_tbl_ext_part_") 175 }) 176 It("returns a slice of external partition info for a named list partition", func() { 177 testhelper.AssertQueryRuns(connectionPool, ` 178 CREATE TABLE public.part_tbl (id int, gender char(1)) 179 DISTRIBUTED BY (id) 180 PARTITION BY LIST (gender) 181 ( PARTITION girls VALUES ('F'), 182 PARTITION boys VALUES ('M'), 183 DEFAULT PARTITION other );`) 184 testhelper.AssertQueryRuns(connectionPool, ` 185 CREATE EXTERNAL WEB TABLE public.part_tbl_ext_part_ (like public.part_tbl_1_prt_girls) 186 EXECUTE 'echo -e "2\n1"' on host 187 FORMAT 'csv';`) 188 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.part_tbl EXCHANGE PARTITION girls WITH TABLE public.part_tbl_ext_part_ WITHOUT VALIDATION;`) 189 190 resultExtPartitions, resultPartInfoMap := backup.GetExternalPartitionInfo(connectionPool) 191 192 Expect(resultExtPartitions).To(HaveLen(1)) 193 Expect(resultPartInfoMap).To(HaveLen(3)) 194 expectedExternalPartition := backup.PartitionInfo{ 195 PartitionRuleOid: 1, 196 PartitionParentRuleOid: 0, 197 ParentRelationOid: 2, 198 ParentSchema: "public", 199 ParentRelationName: "part_tbl", 200 RelationOid: 1, 201 PartitionName: "girls", 202 PartitionRank: 0, 203 IsExternal: true, 204 } 205 structmatcher.ExpectStructsToMatchExcluding(&expectedExternalPartition, &resultExtPartitions[0], "PartitionRuleOid", "RelationOid", "ParentRelationOid") 206 }) 207 It("returns a slice of external partition info for an unnamed range partition", func() { 208 testhelper.AssertQueryRuns(connectionPool, ` 209 CREATE TABLE public.part_tbl (a int) 210 DISTRIBUTED BY (a) 211 PARTITION BY RANGE (a) 212 (start(1) end(3) every(1));`) 213 testhelper.AssertQueryRuns(connectionPool, ` 214 CREATE EXTERNAL WEB TABLE public.part_tbl_ext_part_ (like public.part_tbl_1_prt_1) 215 EXECUTE 'echo -e "2\n1"' on host 216 FORMAT 'csv';`) 217 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.part_tbl EXCHANGE PARTITION FOR (RANK(1)) WITH TABLE public.part_tbl_ext_part_ WITHOUT VALIDATION;`) 218 219 resultExtPartitions, resultPartInfoMap := backup.GetExternalPartitionInfo(connectionPool) 220 221 Expect(resultExtPartitions).To(HaveLen(1)) 222 Expect(resultPartInfoMap).To(HaveLen(2)) 223 expectedExternalPartition := backup.PartitionInfo{ 224 PartitionRuleOid: 1, 225 PartitionParentRuleOid: 0, 226 ParentRelationOid: 2, 227 ParentSchema: "public", 228 ParentRelationName: "part_tbl", 229 RelationOid: 1, 230 PartitionName: "", 231 PartitionRank: 1, 232 IsExternal: true, 233 } 234 structmatcher.ExpectStructsToMatchExcluding(&expectedExternalPartition, &resultExtPartitions[0], "PartitionRuleOid", "RelationOid", "ParentRelationOid") 235 }) 236 It("returns a slice of info for a two level partition", func() { 237 testutils.SkipIfBefore5(connectionPool) 238 testhelper.AssertQueryRuns(connectionPool, ` 239 CREATE TABLE public.part_tbl (a int,b date,c text,d int) 240 DISTRIBUTED BY (a) 241 PARTITION BY RANGE (b) 242 SUBPARTITION BY LIST (c) 243 SUBPARTITION TEMPLATE 244 (SUBPARTITION usa values ('usa'), 245 SUBPARTITION apj values ('apj'), 246 SUBPARTITION eur values ('eur')) 247 (PARTITION Sep16 START (date '2016-09-01') INCLUSIVE , 248 PARTITION Oct16 START (date '2016-10-01') INCLUSIVE , 249 PARTITION Nov16 START (date '2016-11-01') INCLUSIVE , 250 PARTITION Dec16 START (date '2016-12-01') INCLUSIVE 251 END (date '2017-01-01') EXCLUSIVE); 252 `) 253 254 testhelper.AssertQueryRuns(connectionPool, `CREATE EXTERNAL TABLE public.part_tbl_ext_part_ (a int,b date,c text,d int) LOCATION ('gpfdist://127.0.0.1/apj') FORMAT 'text';`) 255 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.part_tbl ALTER PARTITION Dec16 EXCHANGE PARTITION apj WITH TABLE public.part_tbl_ext_part_ WITHOUT VALIDATION;`) 256 257 resultExtPartitions, _ := backup.GetExternalPartitionInfo(connectionPool) 258 259 Expect(resultExtPartitions).To(HaveLen(1)) 260 expectedExternalPartition := backup.PartitionInfo{ 261 PartitionRuleOid: 1, 262 PartitionParentRuleOid: 0, 263 ParentRelationOid: 2, 264 ParentSchema: "public", 265 ParentRelationName: "part_tbl", 266 RelationOid: testutils.OidFromObjectName(connectionPool, "public", "part_tbl_1_prt_dec16_2_prt_apj", backup.TYPE_RELATION), 267 PartitionName: "apj", 268 PartitionRank: 0, 269 IsExternal: true, 270 } 271 structmatcher.ExpectStructsToMatchExcluding(&expectedExternalPartition, &resultExtPartitions[0], "PartitionRuleOid", "PartitionParentRuleOid", "ParentRelationOid") 272 }) 273 It("returns a slice of info for a three level partition", func() { 274 testutils.SkipIfBefore5(connectionPool) 275 testhelper.AssertQueryRuns(connectionPool, ` 276 CREATE TABLE public.part_tbl (id int, year int, month int, day int, region text) 277 DISTRIBUTED BY (id) 278 PARTITION BY RANGE (year) 279 SUBPARTITION BY RANGE (month) 280 SUBPARTITION TEMPLATE ( 281 START (1) END (4) EVERY (1) ) 282 SUBPARTITION BY LIST (region) 283 SUBPARTITION TEMPLATE ( 284 SUBPARTITION usa VALUES ('usa'), 285 SUBPARTITION europe VALUES ('europe'), 286 SUBPARTITION asia VALUES ('asia') 287 ) 288 ( START (2002) END (2005) EVERY (1)); 289 `) 290 291 testhelper.AssertQueryRuns(connectionPool, `CREATE EXTERNAL TABLE public.part_tbl_ext_part_ (like public.part_tbl_1_prt_3_2_prt_1_3_prt_europe) LOCATION ('gpfdist://127.0.0.1/apj') FORMAT 'text';`) 292 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.part_tbl ALTER PARTITION FOR (RANK(3)) ALTER PARTITION FOR (RANK(1)) EXCHANGE PARTITION europe WITH TABLE public.part_tbl_ext_part_ WITHOUT VALIDATION;`) 293 294 resultExtPartitions, _ := backup.GetExternalPartitionInfo(connectionPool) 295 296 Expect(resultExtPartitions).To(HaveLen(1)) 297 expectedExternalPartition := backup.PartitionInfo{ 298 PartitionRuleOid: 10, 299 PartitionParentRuleOid: 11, 300 ParentRelationOid: 2, 301 ParentSchema: "public", 302 ParentRelationName: "part_tbl", 303 RelationOid: 1, 304 PartitionName: "europe", 305 PartitionRank: 0, 306 IsExternal: true, 307 } 308 expectedExternalPartition.RelationOid = testutils.OidFromObjectName(connectionPool, "public", "part_tbl_1_prt_3_2_prt_1_3_prt_europe", backup.TYPE_RELATION) 309 structmatcher.ExpectStructsToMatchExcluding(&expectedExternalPartition, &resultExtPartitions[0], "PartitionRuleOid", "PartitionParentRuleOid", "ParentRelationOid") 310 }) 311 }) 312 })