github.com/tuhaihe/gpbackup@v1.0.3/integration/predata_table_defs_queries_test.go (about) 1 package integration 2 3 import ( 4 "database/sql" 5 "fmt" 6 7 "github.com/tuhaihe/gp-common-go-libs/structmatcher" 8 "github.com/tuhaihe/gp-common-go-libs/testhelper" 9 "github.com/tuhaihe/gpbackup/backup" 10 "github.com/tuhaihe/gpbackup/options" 11 "github.com/tuhaihe/gpbackup/testutils" 12 13 . "github.com/onsi/ginkgo/v2" 14 . "github.com/onsi/gomega" 15 ) 16 17 var _ = Describe("backup integration tests", func() { 18 Describe("GetPartitionTableMap", func() { 19 It("correctly maps oids to parent or leaf table types", func() { 20 createStmt := `CREATE TABLE public.summer_sales (id int, year int, month int) 21 DISTRIBUTED BY (id) 22 PARTITION BY RANGE (year) 23 SUBPARTITION BY RANGE (month) 24 SUBPARTITION TEMPLATE ( 25 START (6) END (8) EVERY (1), 26 DEFAULT SUBPARTITION other_months ) 27 ( START (2015) END (2017) EVERY (1), 28 DEFAULT PARTITION outlying_years ); 29 ` 30 testhelper.AssertQueryRuns(connectionPool, createStmt) 31 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.summer_sales") 32 33 parent := testutils.OidFromObjectName(connectionPool, "public", "summer_sales", backup.TYPE_RELATION) 34 intermediate1 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_outlying_years", backup.TYPE_RELATION) 35 leaf11 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_outlying_years_2_prt_2", backup.TYPE_RELATION) 36 leaf12 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_outlying_years_2_prt_3", backup.TYPE_RELATION) 37 leaf13 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_outlying_years_2_prt_other_months", backup.TYPE_RELATION) 38 intermediate2 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_2", backup.TYPE_RELATION) 39 leaf21 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_2_2_prt_2", backup.TYPE_RELATION) 40 leaf22 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_2_2_prt_3", backup.TYPE_RELATION) 41 leaf23 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_2_2_prt_other_months", backup.TYPE_RELATION) 42 intermediate3 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_3", backup.TYPE_RELATION) 43 leaf31 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_3_2_prt_2", backup.TYPE_RELATION) 44 leaf32 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_3_2_prt_3", backup.TYPE_RELATION) 45 leaf33 := testutils.OidFromObjectName(connectionPool, "public", "summer_sales_1_prt_3_2_prt_other_months", backup.TYPE_RELATION) 46 partTableMap := backup.GetPartitionTableMap(connectionPool) 47 48 Expect(partTableMap).To(HaveLen(13)) 49 structmatcher.ExpectStructsToMatch(partTableMap[parent], &backup.PartitionLevelInfo{Oid: parent, Level: "p", RootName: ""}) 50 structmatcher.ExpectStructsToMatch(partTableMap[intermediate1], &backup.PartitionLevelInfo{Oid: intermediate1, Level: "i", RootName: "summer_sales"}) 51 structmatcher.ExpectStructsToMatch(partTableMap[intermediate2], &backup.PartitionLevelInfo{Oid: intermediate2, Level: "i", RootName: "summer_sales"}) 52 structmatcher.ExpectStructsToMatch(partTableMap[intermediate3], &backup.PartitionLevelInfo{Oid: intermediate3, Level: "i", RootName: "summer_sales"}) 53 structmatcher.ExpectStructsToMatch(partTableMap[leaf11], &backup.PartitionLevelInfo{Oid: leaf11, Level: "l", RootName: "summer_sales"}) 54 structmatcher.ExpectStructsToMatch(partTableMap[leaf12], &backup.PartitionLevelInfo{Oid: leaf12, Level: "l", RootName: "summer_sales"}) 55 structmatcher.ExpectStructsToMatch(partTableMap[leaf13], &backup.PartitionLevelInfo{Oid: leaf13, Level: "l", RootName: "summer_sales"}) 56 structmatcher.ExpectStructsToMatch(partTableMap[leaf21], &backup.PartitionLevelInfo{Oid: leaf21, Level: "l", RootName: "summer_sales"}) 57 structmatcher.ExpectStructsToMatch(partTableMap[leaf22], &backup.PartitionLevelInfo{Oid: leaf22, Level: "l", RootName: "summer_sales"}) 58 structmatcher.ExpectStructsToMatch(partTableMap[leaf23], &backup.PartitionLevelInfo{Oid: leaf23, Level: "l", RootName: "summer_sales"}) 59 structmatcher.ExpectStructsToMatch(partTableMap[leaf31], &backup.PartitionLevelInfo{Oid: leaf31, Level: "l", RootName: "summer_sales"}) 60 structmatcher.ExpectStructsToMatch(partTableMap[leaf32], &backup.PartitionLevelInfo{Oid: leaf32, Level: "l", RootName: "summer_sales"}) 61 structmatcher.ExpectStructsToMatch(partTableMap[leaf33], &backup.PartitionLevelInfo{Oid: leaf33, Level: "l", RootName: "summer_sales"}) 62 }) 63 }) 64 Describe("GetColumnDefinitions", func() { 65 It("returns table attribute information for a heap table", func() { 66 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.atttable(a float, b text, c text NOT NULL, d int DEFAULT(5), e text)") 67 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.atttable") 68 testhelper.AssertQueryRuns(connectionPool, "COMMENT ON COLUMN public.atttable.a IS 'att comment'") 69 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.atttable DROP COLUMN b") 70 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.atttable ALTER COLUMN e SET STORAGE PLAIN") 71 oid := testutils.OidFromObjectName(connectionPool, "public", "atttable", backup.TYPE_RELATION) 72 privileges := sql.NullString{String: "", Valid: false} 73 if true { 74 testhelper.AssertQueryRuns(connectionPool, "GRANT SELECT (c, d) ON TABLE public.atttable TO testrole") 75 privileges = sql.NullString{String: "testrole=r/testrole", Valid: true} 76 } 77 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 78 79 columnA := backup.ColumnDefinition{Oid: 0, Num: 1, Name: "a", NotNull: false, HasDefault: false, Type: "double precision", Encoding: "", StatTarget: -1, StorageType: "", DefaultVal: "", Comment: "att comment"} 80 columnC := backup.ColumnDefinition{Oid: 0, Num: 3, Name: "c", NotNull: true, HasDefault: false, Type: "text", Encoding: "", StatTarget: -1, StorageType: "", DefaultVal: "", Comment: "", Privileges: privileges} 81 columnD := backup.ColumnDefinition{Oid: 0, Num: 4, Name: "d", NotNull: false, HasDefault: true, Type: "integer", Encoding: "", StatTarget: -1, StorageType: "", DefaultVal: "(5)", Comment: "", Privileges: privileges} 82 columnE := backup.ColumnDefinition{Oid: 0, Num: 5, Name: "e", NotNull: false, HasDefault: false, Type: "text", Encoding: "", StatTarget: -1, StorageType: "PLAIN", DefaultVal: "", Comment: ""} 83 84 Expect(tableAtts).To(HaveLen(4)) 85 86 structmatcher.ExpectStructsToMatchExcluding(&columnA, &tableAtts[0], "Oid") 87 structmatcher.ExpectStructsToMatchExcluding(&columnC, &tableAtts[1], "Oid") 88 structmatcher.ExpectStructsToMatchExcluding(&columnD, &tableAtts[2], "Oid") 89 structmatcher.ExpectStructsToMatchExcluding(&columnE, &tableAtts[3], "Oid") 90 }) 91 It("returns table attributes including encoding for a column oriented table", func() { 92 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.co_atttable(a float, b text ENCODING(blocksize=65536)) WITH (appendonly=true, orientation=column)") 93 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.co_atttable") 94 oid := testutils.OidFromObjectName(connectionPool, "public", "co_atttable", backup.TYPE_RELATION) 95 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 96 97 columnA := backup.ColumnDefinition{Oid: 0, Num: 1, Name: "a", NotNull: false, HasDefault: false, Type: "double precision", Encoding: "compresstype=none,blocksize=32768,compresslevel=0", StatTarget: -1, StorageType: "", DefaultVal: "", Comment: ""} 98 columnB := backup.ColumnDefinition{Oid: 0, Num: 2, Name: "b", NotNull: false, HasDefault: false, Type: "text", Encoding: "blocksize=65536,compresstype=none,compresslevel=0", StatTarget: -1, StorageType: "", DefaultVal: "", Comment: ""} 99 100 Expect(tableAtts).To(HaveLen(2)) 101 102 structmatcher.ExpectStructsToMatchExcluding(&columnA, &tableAtts[0], "Oid") 103 structmatcher.ExpectStructsToMatchExcluding(&columnB, &tableAtts[1], "Oid") 104 }) 105 It("returns an empty attribute array for a table with no columns", func() { 106 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.nocol_atttable()") 107 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.nocol_atttable") 108 oid := testutils.OidFromObjectName(connectionPool, "public", "nocol_atttable", backup.TYPE_RELATION) 109 110 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 111 112 Expect(tableAtts).To(BeEmpty()) 113 }) 114 It("returns table attributes with options only applicable to coordinator", func() { 115 testutils.SkipIfBefore6(connectionPool) 116 testhelper.AssertQueryRuns(connectionPool, "CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX')") 117 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 118 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.atttable(i character(8) COLLATE public.some_coll)") 119 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.atttable") 120 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE ONLY public.atttable ALTER COLUMN i SET (n_distinct=1);") 121 testhelper.AssertQueryRuns(connectionPool, "SECURITY LABEL FOR dummy ON COLUMN public.atttable.i IS 'unclassified';") 122 oid := testutils.OidFromObjectName(connectionPool, "public", "atttable", backup.TYPE_RELATION) 123 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 124 125 columnA := backup.ColumnDefinition{Oid: 0, Num: 1, Name: "i", NotNull: false, HasDefault: false, Type: "character(8)", Encoding: "", StatTarget: -1, StorageType: "", DefaultVal: "", Comment: "", Options: "n_distinct=1", Collation: "public.some_coll", SecurityLabelProvider: "dummy", SecurityLabel: "unclassified"} 126 127 Expect(tableAtts).To(HaveLen(1)) 128 129 structmatcher.ExpectStructsToMatchExcluding(&columnA, &tableAtts[0], "Oid") 130 }) 131 It("returns table attributes with foreign data options", func() { 132 testutils.SkipIfBefore6(connectionPool) 133 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER dummy;") 134 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER dummy") 135 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER sc FOREIGN DATA WRAPPER dummy;") 136 defer testhelper.AssertQueryRuns(connectionPool, "DROP SERVER sc") 137 testhelper.AssertQueryRuns(connectionPool, `CREATE FOREIGN TABLE public.ft1 ( 138 c1 integer OPTIONS (param1 'val1', param2 'val2') NOT NULL 139 ) SERVER sc ;`) 140 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN TABLE public.ft1") 141 142 oid := testutils.OidFromObjectName(connectionPool, "public", "ft1", backup.TYPE_RELATION) 143 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 144 145 Expect(tableAtts).To(HaveLen(1)) 146 column1 := backup.ColumnDefinition{Oid: 0, Num: 1, Name: "c1", NotNull: true, HasDefault: false, Type: "integer", StatTarget: -1, FdwOptions: "param1 'val1', param2 'val2'"} 147 structmatcher.ExpectStructsToMatchExcluding(column1, &tableAtts[0], "Oid") 148 }) 149 It("handles table with gin index, when index's attribute's collname and namespace are null", func() { 150 testutils.SkipIfBefore6(connectionPool) 151 152 testhelper.AssertQueryRuns(connectionPool, ` 153 CREATE TABLE public.test_tsvector ( 154 t text, 155 a tsvector, 156 distkey integer 157 ) DISTRIBUTED BY (distkey) 158 `) 159 testhelper.AssertQueryRuns(connectionPool, `CREATE INDEX wowidx ON public.test_tsvector USING gin (a)`) 160 defer testhelper.AssertQueryRuns(connectionPool, "DROP table public.test_tsvector cascade") 161 162 oid := testutils.OidFromObjectName(connectionPool, "public", "test_tsvector", backup.TYPE_RELATION) 163 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 164 165 Expect(tableAtts).To(HaveLen(3)) 166 }) 167 It("gets the correct collation schema", func() { 168 testutils.SkipIfBefore6(connectionPool) 169 170 testhelper.AssertQueryRuns(connectionPool, `CREATE SCHEMA myschema;`) 171 defer testhelper.AssertQueryRuns(connectionPool, `DROP SCHEMA myschema`) 172 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION myschema.mycoll (lc_collate = 'C', lc_ctype = 'C')`) 173 defer testhelper.AssertQueryRuns(connectionPool, `DROP COLLATION myschema.mycoll`) 174 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.foo(i text COLLATE myschema.mycoll)`) 175 defer testhelper.AssertQueryRuns(connectionPool, `DROP TABLE public.foo`) 176 177 oid := testutils.OidFromObjectName(connectionPool, "public", "foo", backup.TYPE_RELATION) 178 tableAtts := backup.GetColumnDefinitions(connectionPool)[oid] 179 180 Expect(tableAtts).To(HaveLen(1)) 181 Expect(tableAtts[0].Collation).To(Equal("myschema.mycoll")) 182 }) 183 }) 184 Describe("GetDistributionPolicies", func() { 185 It("returns distribution policy info for a table DISTRIBUTED RANDOMLY", func() { 186 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.dist_random(a int, b text) DISTRIBUTED RANDOMLY") 187 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_random") 188 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_random", backup.TYPE_RELATION) 189 190 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 191 192 Expect(distPolicies).To(Equal("DISTRIBUTED RANDOMLY")) 193 }) 194 It("returns distribution policy info for a table DISTRIBUTED BY one column", func() { 195 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.dist_one(a int, b text) DISTRIBUTED BY (a)") 196 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_one") 197 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_one", backup.TYPE_RELATION) 198 199 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 200 201 Expect(distPolicies).To(Equal("DISTRIBUTED BY (a)")) 202 }) 203 It("returns distribution policy info for a table DISTRIBUTED BY two columns", func() { 204 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.dist_two(a int, b text) DISTRIBUTED BY (a, b)") 205 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_two") 206 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_two", backup.TYPE_RELATION) 207 208 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 209 210 Expect(distPolicies).To(Equal("DISTRIBUTED BY (a, b)")) 211 }) 212 It("returns distribution policy info for a table DISTRIBUTED BY a custom operator", func() { 213 testutils.SkipIfBefore6(connectionPool) 214 testhelper.AssertQueryRuns(connectionPool, ` 215 CREATE OPERATOR FAMILY public.abs_int_hash_ops USING hash; 216 217 CREATE FUNCTION public.abseq(integer, integer) RETURNS boolean 218 LANGUAGE plpgsql IMMUTABLE STRICT NO SQL 219 AS $_$ 220 begin return abs($1) = abs($2); end; 221 $_$; 222 223 CREATE OPERATOR public.|=| ( 224 PROCEDURE = public.abseq, 225 LEFTARG = integer, 226 RIGHTARG = integer, 227 COMMUTATOR = OPERATOR(public.|=|), 228 MERGES, 229 HASHES 230 ); 231 CREATE FUNCTION public.abshashfunc(integer) RETURNS integer 232 LANGUAGE plpgsql IMMUTABLE STRICT NO SQL 233 AS $_$ 234 begin return abs($1); end; 235 $_$; 236 237 238 -- operator class dec 239 CREATE OPERATOR CLASS public.abs_int_hash_ops 240 FOR TYPE integer USING hash FAMILY public.abs_int_hash_ops AS 241 OPERATOR 1 public.|=|(integer,integer) , 242 FUNCTION 1 (integer, integer) public.abshashfunc(integer); 243 244 245 -- table ft object 246 CREATE TABLE public.abs_opclass_test ( 247 i integer, 248 t text, 249 j integer 250 ) DISTRIBUTED BY (i public.abs_int_hash_ops, t, j public.abs_int_hash_ops); 251 `) 252 253 defer testhelper.AssertQueryRuns(connectionPool, ` 254 DROP TABLE IF EXISTS public.abs_opclass_test; 255 DROP OPERATOR CLASS IF EXISTS public.abs_int_hash_ops USING hash; 256 DROP FUNCTION IF EXISTS public.abshashfunc(integer); 257 DROP OPERATOR IF EXISTS public.|=|(integer, integer); 258 DROP FUNCTION IF EXISTS public.abseq(integer, integer); 259 DROP OPERATOR FAMILY IF EXISTS public.abs_int_hash_ops USING hash; 260 `) 261 262 oid := testutils.OidFromObjectName(connectionPool, "public", "abs_opclass_test", backup.TYPE_RELATION) 263 264 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 265 266 Expect(distPolicies).To(Equal("DISTRIBUTED BY (i public.abs_int_hash_ops, t, j public.abs_int_hash_ops)")) 267 }) 268 It("returns distribution policy info for a table DISTRIBUTED BY column name as keyword", func() { 269 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.dist_one(a int, "group" text) DISTRIBUTED BY ("group")`) 270 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_one") 271 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_one", backup.TYPE_RELATION) 272 273 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 274 275 Expect(distPolicies).To(Equal(`DISTRIBUTED BY ("group")`)) 276 }) 277 It("returns distribution policy info for a table DISTRIBUTED BY multiple columns in correct order", func() { 278 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.dist_one (id int, memo varchar(20), dt date, col1 varchar(20)) DISTRIBUTED BY (memo, dt, id, col1); `) 279 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_one") 280 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_one", backup.TYPE_RELATION) 281 282 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 283 284 Expect(distPolicies).To(Equal(`DISTRIBUTED BY (memo, dt, id, col1)`)) 285 }) 286 It("returns distribution policy info for a table DISTRIBUTED REPLICATED", func() { 287 testutils.SkipIfBefore6(connectionPool) 288 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.dist_one(a int, "group" text) DISTRIBUTED REPLICATED`) 289 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.dist_one") 290 oid := testutils.OidFromObjectName(connectionPool, "public", "dist_one", backup.TYPE_RELATION) 291 292 distPolicies := backup.GetDistributionPolicies(connectionPool)[oid] 293 294 Expect(distPolicies).To(Equal(`DISTRIBUTED REPLICATED`)) 295 }) 296 }) 297 Describe("GetPartitionDefinitions", func() { 298 var partitionPartFalseExpectation = "false " 299 BeforeEach(func() { 300 // GPDB 7+ does not have pg_get_partition_def() 301 if true { 302 Skip("Test is not applicable to GPDB 7+") 303 } 304 305 if true { 306 partitionPartFalseExpectation = "'false'" 307 } 308 }) 309 It("returns empty string when no partition exists", func() { 310 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 311 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 312 oid := testutils.OidFromObjectName(connectionPool, "public", "simple_table", backup.TYPE_RELATION) 313 314 result, _ := backup.GetPartitionDetails(connectionPool) 315 316 Expect(result[oid]).To(Equal("")) 317 }) 318 It("returns a value for a partition definition", func() { 319 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (id int, rank int, year int, gender 320 char(1), count int ) 321 DISTRIBUTED BY (id) 322 PARTITION BY LIST (gender) 323 ( PARTITION girls VALUES ('F'), 324 PARTITION boys VALUES ('M'), 325 DEFAULT PARTITION other ); 326 `) 327 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 328 oid := testutils.OidFromObjectName(connectionPool, "public", "part_table", backup.TYPE_RELATION) 329 330 result, _ := backup.GetPartitionDetails(connectionPool) 331 332 // The spacing is very specific here and is output from the postgres function 333 expectedResult := fmt.Sprintf(`PARTITION BY LIST(gender) `+` 334 ( 335 PARTITION girls VALUES('F') WITH (tablename='part_table_1_prt_girls', appendonly=%[1]s), `+` 336 PARTITION boys VALUES('M') WITH (tablename='part_table_1_prt_boys', appendonly=%[1]s), `+` 337 DEFAULT PARTITION other WITH (tablename='part_table_1_prt_other', appendonly=%[1]s) 338 )`, partitionPartFalseExpectation) 339 Expect(result[oid]).To(Equal(expectedResult)) 340 }) 341 It("returns a value for a partition definition for a specific table", func() { 342 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (id int, rank int, year int, gender 343 char(1), count int ) 344 DISTRIBUTED BY (id) 345 PARTITION BY LIST (gender) 346 ( PARTITION girls VALUES ('F'), 347 PARTITION boys VALUES ('M'), 348 DEFAULT PARTITION other ); 349 `) 350 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 351 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table2 (id int, rank int, year int, gender 352 char(1), count int ) 353 DISTRIBUTED BY (id) 354 PARTITION BY LIST (gender) 355 ( PARTITION girls VALUES ('F'), 356 PARTITION boys VALUES ('M'), 357 DEFAULT PARTITION other ); 358 `) 359 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table2") 360 oid := testutils.OidFromObjectName(connectionPool, "public", "part_table", backup.TYPE_RELATION) 361 362 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.part_table") 363 364 results, _ := backup.GetPartitionDetails(connectionPool) 365 Expect(results).To(HaveLen(1)) 366 result := results[oid] 367 368 // The spacing is very specific here and is output from the postgres function 369 expectedResult := fmt.Sprintf(`PARTITION BY LIST(gender) `+` 370 ( 371 PARTITION girls VALUES('F') WITH (tablename='part_table_1_prt_girls', appendonly=%[1]s), `+` 372 PARTITION boys VALUES('M') WITH (tablename='part_table_1_prt_boys', appendonly=%[1]s), `+` 373 DEFAULT PARTITION other WITH (tablename='part_table_1_prt_other', appendonly=%[1]s) 374 )`, partitionPartFalseExpectation) 375 Expect(result).To(Equal(expectedResult)) 376 }) 377 It("returns a value for a partition definition in a specific schema", func() { 378 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (id int, rank int, year int, gender 379 char(1), count int ) 380 DISTRIBUTED BY (id) 381 PARTITION BY LIST (gender) 382 ( PARTITION girls VALUES ('F'), 383 PARTITION boys VALUES ('M'), 384 DEFAULT PARTITION other ); 385 `) 386 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 387 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 388 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE") 389 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE testschema.part_table (id int, rank int, year int, gender 390 char(1), count int ) 391 DISTRIBUTED BY (id) 392 PARTITION BY LIST (gender) 393 ( PARTITION girls VALUES ('F'), 394 PARTITION boys VALUES ('M'), 395 DEFAULT PARTITION other ); 396 `) 397 oid := testutils.OidFromObjectName(connectionPool, "testschema", "part_table", backup.TYPE_RELATION) 398 399 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 400 401 results, _ := backup.GetPartitionDetails(connectionPool) 402 Expect(results).To(HaveLen(1)) 403 result := results[oid] 404 405 // The spacing is very specific here and is output from the postgres function 406 expectedResult := fmt.Sprintf(`PARTITION BY LIST(gender) `+` 407 ( 408 PARTITION girls VALUES('F') WITH (tablename='part_table_1_prt_girls', appendonly=%[1]s), `+` 409 PARTITION boys VALUES('M') WITH (tablename='part_table_1_prt_boys', appendonly=%[1]s), `+` 410 DEFAULT PARTITION other WITH (tablename='part_table_1_prt_other', appendonly=%[1]s) 411 )`, partitionPartFalseExpectation) 412 Expect(result).To(Equal(expectedResult)) 413 }) 414 }) 415 Describe("GetPartitionTemplates", func() { 416 BeforeEach(func() { 417 // GPDB 7+ does not have pg_get_partition_template_def() 418 if true { 419 Skip("Test is not applicable to GPDB 7+") 420 } 421 }) 422 It("returns empty string when no partition definition template exists", func() { 423 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 424 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 425 oid := testutils.OidFromObjectName(connectionPool, "public", "simple_table", backup.TYPE_RELATION) 426 427 _, result := backup.GetPartitionDetails(connectionPool) 428 429 Expect(result[oid]).To(Equal("")) 430 }) 431 It("returns a value for a subpartition template", func() { 432 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (trans_id int, date date, amount decimal(9,2), region text) 433 DISTRIBUTED BY (trans_id) 434 PARTITION BY RANGE (date) 435 SUBPARTITION BY LIST (region) 436 SUBPARTITION TEMPLATE 437 ( SUBPARTITION usa VALUES ('usa'), 438 SUBPARTITION asia VALUES ('asia'), 439 SUBPARTITION europe VALUES ('europe'), 440 DEFAULT SUBPARTITION other_regions ) 441 ( START (date '2014-01-01') INCLUSIVE 442 END (date '2014-04-01') EXCLUSIVE 443 EVERY (INTERVAL '1 month') ) `) 444 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 445 oid := testutils.OidFromObjectName(connectionPool, "public", "part_table", backup.TYPE_RELATION) 446 447 _, result := backup.GetPartitionDetails(connectionPool) 448 449 /* 450 * The spacing is very specific here and is output from the postgres function 451 * The only difference between the below statements is spacing 452 */ 453 expectedResult := "" 454 if false { 455 expectedResult = `ALTER TABLE public.part_table 456 SET SUBPARTITION TEMPLATE 457 ( 458 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 459 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 460 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 461 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 462 ) 463 ` 464 } else { 465 expectedResult = `ALTER TABLE public.part_table 466 SET SUBPARTITION TEMPLATE 467 ( 468 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 469 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 470 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 471 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 472 ) 473 ` 474 } 475 476 Expect(result[oid]).To(Equal(expectedResult)) 477 }) 478 It("returns a value for a subpartition template for a specific table", func() { 479 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (trans_id int, date date, amount decimal(9,2), region text) 480 DISTRIBUTED BY (trans_id) 481 PARTITION BY RANGE (date) 482 SUBPARTITION BY LIST (region) 483 SUBPARTITION TEMPLATE 484 ( SUBPARTITION usa VALUES ('usa'), 485 SUBPARTITION asia VALUES ('asia'), 486 SUBPARTITION europe VALUES ('europe'), 487 DEFAULT SUBPARTITION other_regions ) 488 ( START (date '2014-01-01') INCLUSIVE 489 END (date '2014-04-01') EXCLUSIVE 490 EVERY (INTERVAL '1 month') ) `) 491 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 492 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table2 (trans_id int, date date, amount decimal(9,2), region text) 493 DISTRIBUTED BY (trans_id) 494 PARTITION BY RANGE (date) 495 SUBPARTITION BY LIST (region) 496 SUBPARTITION TEMPLATE 497 ( SUBPARTITION usa VALUES ('usa'), 498 SUBPARTITION asia VALUES ('asia'), 499 SUBPARTITION europe VALUES ('europe'), 500 DEFAULT SUBPARTITION other_regions ) 501 ( START (date '2014-01-01') INCLUSIVE 502 END (date '2014-04-01') EXCLUSIVE 503 EVERY (INTERVAL '1 month') ) `) 504 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table2") 505 oid := testutils.OidFromObjectName(connectionPool, "public", "part_table", backup.TYPE_RELATION) 506 507 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.part_table") 508 509 _, results := backup.GetPartitionDetails(connectionPool) 510 Expect(results).To(HaveLen(1)) 511 result := results[oid] 512 513 /* 514 * The spacing is very specific here and is output from the postgres function 515 * The only difference between the below statements is spacing 516 */ 517 expectedResult := "" 518 if false { 519 expectedResult = `ALTER TABLE public.part_table 520 SET SUBPARTITION TEMPLATE 521 ( 522 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 523 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 524 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 525 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 526 ) 527 ` 528 } else { 529 expectedResult = `ALTER TABLE public.part_table 530 SET SUBPARTITION TEMPLATE 531 ( 532 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 533 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 534 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 535 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 536 ) 537 ` 538 } 539 Expect(result).To(Equal(expectedResult)) 540 541 }) 542 It("returns a value for a subpartition template in a specific schema", func() { 543 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part_table (trans_id int, date date, amount decimal(9,2), region text) 544 DISTRIBUTED BY (trans_id) 545 PARTITION BY RANGE (date) 546 SUBPARTITION BY LIST (region) 547 SUBPARTITION TEMPLATE 548 ( SUBPARTITION usa VALUES ('usa'), 549 SUBPARTITION asia VALUES ('asia'), 550 SUBPARTITION europe VALUES ('europe'), 551 DEFAULT SUBPARTITION other_regions ) 552 ( START (date '2014-01-01') INCLUSIVE 553 END (date '2014-04-01') EXCLUSIVE 554 EVERY (INTERVAL '1 month') ) `) 555 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part_table") 556 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 557 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE") 558 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE testschema.part_table (trans_id int, date date, amount decimal(9,2), region text) 559 DISTRIBUTED BY (trans_id) 560 PARTITION BY RANGE (date) 561 SUBPARTITION BY LIST (region) 562 SUBPARTITION TEMPLATE 563 ( SUBPARTITION usa VALUES ('usa'), 564 SUBPARTITION asia VALUES ('asia'), 565 SUBPARTITION europe VALUES ('europe'), 566 DEFAULT SUBPARTITION other_regions ) 567 ( START (date '2014-01-01') INCLUSIVE 568 END (date '2014-04-01') EXCLUSIVE 569 EVERY (INTERVAL '1 month') ) `) 570 oid := testutils.OidFromObjectName(connectionPool, "testschema", "part_table", backup.TYPE_RELATION) 571 572 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 573 574 _, results := backup.GetPartitionDetails(connectionPool) 575 Expect(results).To(HaveLen(1)) 576 result := results[oid] 577 578 /* 579 * The spacing is very specific here and is output from the postgres function 580 * The only difference between the below statements is spacing 581 */ 582 expectedResult := "" 583 if false { 584 expectedResult = `ALTER TABLE testschema.part_table 585 SET SUBPARTITION TEMPLATE 586 ( 587 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 588 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 589 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 590 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 591 ) 592 ` 593 } else { 594 expectedResult = `ALTER TABLE testschema.part_table 595 SET SUBPARTITION TEMPLATE 596 ( 597 SUBPARTITION usa VALUES('usa') WITH (tablename='part_table'), 598 SUBPARTITION asia VALUES('asia') WITH (tablename='part_table'), 599 SUBPARTITION europe VALUES('europe') WITH (tablename='part_table'), 600 DEFAULT SUBPARTITION other_regions WITH (tablename='part_table') 601 ) 602 ` 603 } 604 605 Expect(result).To(Equal(expectedResult)) 606 }) 607 }) 608 Describe("GetTableType", func() { 609 It("Returns a map when a table OF type exists", func() { 610 testutils.SkipIfBefore6(connectionPool) 611 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.some_type AS (a text, b numeric)") 612 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.some_type") 613 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.some_table OF public.some_type (PRIMARY KEY (a), b WITH OPTIONS DEFAULT 1000)") 614 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 615 oid := testutils.OidFromObjectName(connectionPool, "public", "some_table", backup.TYPE_RELATION) 616 617 result := backup.GetTableType(connectionPool) 618 Expect(result).To(HaveLen(1)) 619 620 Expect(result[oid]).To(Equal("public.some_type")) 621 }) 622 It("Returns empty map when no tables OF type exist", func() { 623 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.some_table (i int, j int)") 624 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 625 626 result := backup.GetTableType(connectionPool) 627 Expect(result).To(BeEmpty()) 628 }) 629 }) 630 631 Describe("GetUnloggedTables", func() { 632 It("Returns a map when an UNLOGGED table exists", func() { 633 testutils.SkipIfBefore6(connectionPool) 634 testhelper.AssertQueryRuns(connectionPool, "CREATE UNLOGGED TABLE public.some_table(i int)") 635 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 636 oid := testutils.OidFromObjectName(connectionPool, "public", "some_table", backup.TYPE_RELATION) 637 638 result := backup.GetUnloggedTables(connectionPool) 639 Expect(result).To(HaveLen(1)) 640 641 Expect(result[oid]).To(BeTrue()) 642 }) 643 It("Returns empty map when no UNLOGGED tables exist", func() { 644 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.some_table (i int, j int)") 645 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 646 647 result := backup.GetUnloggedTables(connectionPool) 648 Expect(result).To(BeEmpty()) 649 }) 650 }) 651 652 Describe("GetForeignTableDefinitions", func() { 653 It("Returns a map when a FOREIGN table exists", func() { 654 testutils.SkipIfBefore6(connectionPool) 655 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER dummy;") 656 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER dummy") 657 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER sc FOREIGN DATA WRAPPER dummy;") 658 defer testhelper.AssertQueryRuns(connectionPool, "DROP SERVER sc") 659 testhelper.AssertQueryRuns(connectionPool, `CREATE FOREIGN TABLE public.ft1 ( 660 c1 integer OPTIONS (param1 'val1') NOT NULL, 661 c3 date 662 ) SERVER sc OPTIONS (delimiter ',', quote '"');`) 663 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN TABLE public.ft1") 664 oid := testutils.OidFromObjectName(connectionPool, "public", "ft1", backup.TYPE_RELATION) 665 result := backup.GetForeignTableDefinitions(connectionPool) 666 expectedResult := backup.ForeignTableDefinition{Oid: oid, Options: "delimiter ',', quote '\"'", Server: "sc"} 667 Expect(result).To(HaveLen(1)) 668 Expect(result[oid]).To(Equal(expectedResult)) 669 }) 670 It("Returns an empty map when no FOREIGN table exists", func() { 671 testutils.SkipIfBefore6(connectionPool) 672 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.some_table (i int, j int)") 673 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 674 675 result := backup.GetForeignTableDefinitions(connectionPool) 676 Expect(result).To(BeEmpty()) 677 }) 678 }) 679 Describe("GetForeignTableRelations", func() { 680 It("Returns a list with FOREIGN table", func() { 681 testutils.SkipIfBefore6(connectionPool) 682 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER dummy;") 683 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER dummy") 684 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER sc FOREIGN DATA WRAPPER dummy;") 685 defer testhelper.AssertQueryRuns(connectionPool, "DROP SERVER sc") 686 testhelper.AssertQueryRuns(connectionPool, `CREATE FOREIGN TABLE public.ft1 ( 687 c1 integer OPTIONS (param1 'val1') NOT NULL, 688 c3 date 689 ) SERVER sc OPTIONS (delimiter ',', quote '"');`) 690 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN TABLE public.ft1") 691 result := backup.GetForeignTableRelations(connectionPool) 692 Expect(result).To(HaveLen(1)) 693 Expect(result[0].Schema).To(Equal("public")) 694 Expect(result[0].Name).To(Equal("ft1")) 695 }) 696 It("Returns an empty list when no FOREIGN table exits", func() { 697 testutils.SkipIfBefore6(connectionPool) 698 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.some_table (i int, j int)") 699 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.some_table") 700 result := backup.GetForeignTableRelations(connectionPool) 701 Expect(result).To(HaveLen(0)) 702 }) 703 }) 704 Describe("GetTableStorageOptions", func() { 705 It("returns an empty string when no table storage options exist ", func() { 706 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 707 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 708 oid := testutils.OidFromObjectName(connectionPool, "public", "simple_table", backup.TYPE_RELATION) 709 710 _, result := backup.GetTableStorage(connectionPool) 711 712 Expect(result[oid]).To(Equal("")) 713 }) 714 It("returns a value for storage options of a table ", func() { 715 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.ao_table(i int) with (appendonly=true)") 716 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.ao_table") 717 oid := testutils.OidFromObjectName(connectionPool, "public", "ao_table", backup.TYPE_RELATION) 718 719 _, result := backup.GetTableStorage(connectionPool) 720 721 if false { 722 Expect(result[oid]).To(Equal("appendonly=true")) 723 } else { 724 // For GPDB 7+, storage options no longer contain appendonly and orientation 725 Expect(result[oid]).To(Equal("")) 726 } 727 }) 728 }) 729 Describe("GetTableInheritance", func() { 730 child := backup.Relation{Schema: "public", Name: "child"} 731 childOne := backup.Relation{Schema: "public", Name: "child_one"} 732 childTwo := backup.Relation{Schema: "public", Name: "child_two"} 733 parent := backup.Relation{Schema: "public", Name: "parent"} 734 parentOne := backup.Relation{Schema: "public", Name: "parent_one"} 735 parentTwo := backup.Relation{Schema: "public", Name: "parent_two"} 736 737 It("constructs dependencies correctly if there is one table dependent on one table", func() { 738 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent(i int)") 739 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent") 740 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child() INHERITS (public.parent)") 741 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child") 742 743 child.Oid = testutils.OidFromObjectName(connectionPool, "public", "child", backup.TYPE_RELATION) 744 tables := []backup.Relation{child, parent} 745 746 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 747 748 Expect(inheritanceMap).To(HaveLen(1)) 749 Expect(inheritanceMap[child.Oid]).To(ConsistOf("public.parent")) 750 }) 751 It("constructs dependencies correctly if there are two tables dependent on one table", func() { 752 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent(i int)") 753 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent") 754 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child_one() INHERITS (public.parent)") 755 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child_one") 756 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child_two() INHERITS (public.parent)") 757 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child_two") 758 759 childOne.Oid = testutils.OidFromObjectName(connectionPool, "public", "child_one", backup.TYPE_RELATION) 760 childTwo.Oid = testutils.OidFromObjectName(connectionPool, "public", "child_two", backup.TYPE_RELATION) 761 tables := []backup.Relation{parent, childOne, childTwo} 762 763 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 764 765 Expect(inheritanceMap).To(HaveLen(2)) 766 Expect(inheritanceMap[childOne.Oid]).To(ConsistOf("public.parent")) 767 Expect(inheritanceMap[childTwo.Oid]).To(ConsistOf("public.parent")) 768 }) 769 It("constructs dependencies correctly if there is one table dependent on two tables", func() { 770 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent_one(i int)") 771 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent_one") 772 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent_two(j int)") 773 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent_two") 774 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child() INHERITS (public.parent_one, public.parent_two)") 775 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child") 776 777 child.Oid = testutils.OidFromObjectName(connectionPool, "public", "child", backup.TYPE_RELATION) 778 tables := []backup.Relation{parentOne, parentTwo, child} 779 780 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 781 782 Expect(inheritanceMap).To(HaveLen(1)) 783 Expect(inheritanceMap[child.Oid]).To(Equal([]string{"public.parent_one", "public.parent_two"})) 784 }) 785 It("constructs dependencies correctly if there are no table dependencies", func() { 786 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent(i int)") 787 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent") 788 tables := make([]backup.Relation, 0) 789 790 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 791 792 Expect(inheritanceMap).To(BeEmpty()) 793 }) 794 It("constructs dependencies correctly if there are no table dependencies while filtering", func() { 795 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent(i int)") 796 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent") 797 798 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.parent") 799 tables := make([]backup.Relation, 0) 800 801 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 802 803 Expect(inheritanceMap).To(BeEmpty()) 804 }) 805 It("constructs dependencies correctly if there are two dependent tables but one is not in the backup set", func() { 806 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.parent(i int)") 807 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.parent") 808 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child_one() INHERITS (public.parent)") 809 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child_one") 810 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.child_two() INHERITS (public.parent)") 811 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.child_two") 812 813 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.child_one") 814 childOne.Oid = testutils.OidFromObjectName(connectionPool, "public", "child_one", backup.TYPE_RELATION) 815 tables := []backup.Relation{childOne} 816 817 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 818 819 Expect(inheritanceMap).To(HaveLen(1)) 820 Expect(inheritanceMap[childOne.Oid]).To(ConsistOf("public.parent")) 821 }) 822 It("does not record a dependency of an external leaf partition on a parent table", func() { 823 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.partition_table (id int, gender char(1)) 824 DISTRIBUTED BY (id) 825 PARTITION BY LIST (gender) 826 ( PARTITION girls VALUES ('F'), 827 PARTITION boys VALUES ('M'), 828 DEFAULT PARTITION other );`) 829 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.partition_table") 830 testhelper.AssertQueryRuns(connectionPool, `CREATE EXTERNAL WEB TABLE public.partition_table_ext_part_ (like public.partition_table_1_prt_girls) 831 EXECUTE 'echo -e "2\n1"' on host 832 FORMAT 'csv';`) 833 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.partition_table_ext_part_") 834 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.partition_table EXCHANGE PARTITION girls WITH TABLE public.partition_table_ext_part_ WITHOUT VALIDATION;`) 835 836 partition := backup.Relation{Schema: "public", Name: "partition_table_ext_part_"} 837 838 partition.Oid = testutils.OidFromObjectName(connectionPool, "public", "partition_table_ext_part_", backup.TYPE_RELATION) 839 tables := []backup.Relation{partition} 840 841 inheritanceMap := backup.GetTableInheritance(connectionPool, tables) 842 843 Expect(inheritanceMap).To(Not(HaveKey(partition.Oid))) 844 }) 845 }) 846 Describe("GetTableReplicaIdentity", func() { 847 It("Returns a map of oid to replica identity with default", func() { 848 testutils.SkipIfBefore6(connectionPool) 849 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.test_table()`) 850 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.test_table") 851 852 oid := testutils.OidFromObjectName(connectionPool, "public", "test_table", backup.TYPE_RELATION) 853 result := backup.GetTableReplicaIdentity(connectionPool) 854 Expect(result[oid]).To(Equal("d")) 855 }) 856 It("Returns a map of oid to replica identity with full", func() { 857 testutils.SkipIfBefore6(connectionPool) 858 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.test_table()`) 859 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.test_table") 860 861 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.test_table REPLICA IDENTITY FULL;`) 862 oid := testutils.OidFromObjectName(connectionPool, "public", "test_table", backup.TYPE_RELATION) 863 result := backup.GetTableReplicaIdentity(connectionPool) 864 Expect(result[oid]).To(Equal("f")) 865 }) 866 It("Returns a map of oid to replica identity with nothing", func() { 867 testutils.SkipIfBefore6(connectionPool) 868 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.test_table()`) 869 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.test_table") 870 871 testhelper.AssertQueryRuns(connectionPool, `ALTER TABLE public.test_table REPLICA IDENTITY NOTHING;`) 872 oid := testutils.OidFromObjectName(connectionPool, "public", "test_table", backup.TYPE_RELATION) 873 result := backup.GetTableReplicaIdentity(connectionPool) 874 Expect(result[oid]).To(Equal("n")) 875 }) 876 }) 877 Describe("GetPartitionAlteredSchema", func() { 878 BeforeEach(func() { 879 // For GPDB 7+, leaf partitions have their own DDL which will have the correct namespace 880 if true { 881 Skip("Test is not applicable to GPDB 7+") 882 } 883 }) 884 It("Returns a map of table oid to array of child partitions with different schemas", func() { 885 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 886 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 887 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foopart(a int, b int) PARTITION BY RANGE(a) (START(1) END (4) EVERY(1))") 888 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.foopart_1_prt_1 SET SCHEMA testschema") 889 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.foopart_1_prt_2 SET SCHEMA testschema") 890 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foopart") 891 892 oid := testutils.OidFromObjectName(connectionPool, "public", "foopart", backup.TYPE_RELATION) 893 result := backup.GetPartitionAlteredSchema(connectionPool) 894 895 expectedAlteredPartitions := []backup.AlteredPartitionRelation{ 896 {OldSchema: "public", NewSchema: "testschema", Name: "foopart_1_prt_1"}, 897 {OldSchema: "public", NewSchema: "testschema", Name: "foopart_1_prt_2"}, 898 } 899 900 Expect(result[oid]).To(ConsistOf(expectedAlteredPartitions)) 901 }) 902 }) 903 Describe("GetAttachedPartitionInfo", func() { 904 It("Returns a map of table oid to attach partition info", func() { 905 testutils.SkipIfBefore7(connectionPool) 906 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 907 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 908 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foopart(a integer, b integer) PARTITION BY RANGE (b) DISTRIBUTED BY (a)") 909 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foopart_1_prt_1 (a integer, b integer) DISTRIBUTED BY (a)") 910 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE ONLY testschema.foopart ATTACH PARTITION testschema.foopart_1_prt_1 FOR VALUES FROM (1) TO (2)") 911 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.foopart") 912 913 oid := testutils.OidFromObjectName(connectionPool, "testschema", "foopart_1_prt_1", backup.TYPE_RELATION) 914 result := backup.GetAttachPartitionInfo(connectionPool) 915 916 expectedAttachPartitionInfo := backup.AttachPartitionInfo{Oid: oid, Relname: "testschema.foopart_1_prt_1", Parent: "testschema.foopart", Expr: "FOR VALUES FROM (1) TO (2)"} 917 918 structmatcher.ExpectStructsToMatch(result[oid], &expectedAttachPartitionInfo) 919 }) 920 }) 921 })