github.com/tuhaihe/gpbackup@v1.0.3/integration/predata_relations_queries_test.go (about)

     1  package integration
     2  
     3  import (
     4  	"database/sql"
     5  	"math"
     6  	"sort"
     7  
     8  	"github.com/tuhaihe/gp-common-go-libs/structmatcher"
     9  	"github.com/tuhaihe/gp-common-go-libs/testhelper"
    10  	"github.com/tuhaihe/gpbackup/backup"
    11  	"github.com/tuhaihe/gpbackup/options"
    12  	"github.com/tuhaihe/gpbackup/testutils"
    13  
    14  	. "github.com/onsi/ginkgo/v2"
    15  	. "github.com/onsi/gomega"
    16  )
    17  
    18  var _ = Describe("backup integration tests", func() {
    19  	Describe("GetAllUserTables", func() {
    20  		It("returns user table information for basic heap tables", func() {
    21  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
    22  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
    23  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
    24  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE")
    25  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.testtable(t text)")
    26  			includeRelationsQuoted, err := options.QuoteTableNames(connectionPool, backup.MustGetFlagStringArray(options.INCLUDE_RELATION))
    27  			Expect(err).NotTo(HaveOccurred())
    28  
    29  			tables := backup.GetIncludedUserTableRelations(connectionPool, includeRelationsQuoted)
    30  
    31  			tableFoo := backup.Relation{Schema: "public", Name: "foo"}
    32  
    33  			tableTestTable := backup.Relation{Schema: "testschema", Name: "testtable"}
    34  
    35  			Expect(tables).To(HaveLen(2))
    36  			structmatcher.ExpectStructsToMatchExcluding(&tableFoo, &tables[0], "SchemaOid", "Oid")
    37  			structmatcher.ExpectStructsToMatchExcluding(&tableTestTable, &tables[1], "SchemaOid", "Oid")
    38  		})
    39  		Context("leaf-partition-data flag", func() {
    40  			It("returns only parent partition tables if the leaf-partition-data flag is not set and there are no include tables", func() {
    41  				createStmt := `CREATE TABLE public.rank (id int, rank int, year int, gender
    42  char(1), count int )
    43  DISTRIBUTED BY (id)
    44  PARTITION BY LIST (gender)
    45  ( PARTITION girls VALUES ('F'),
    46    PARTITION boys VALUES ('M'),
    47    DEFAULT PARTITION other );`
    48  				testhelper.AssertQueryRuns(connectionPool, createStmt)
    49  				defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rank")
    50  
    51  				tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
    52  
    53  				tableRank := backup.Relation{Schema: "public", Name: "rank"}
    54  
    55  				if false {
    56  					Expect(tables).To(HaveLen(1))
    57  				} else {
    58  					// For GPDB 7+, the leaf partitions have their own DDL so they need to be obtained as well
    59  					Expect(tables).To(HaveLen(4))
    60  				}
    61  
    62  				structmatcher.ExpectStructsToMatchExcluding(&tableRank, &tables[0], "SchemaOid", "Oid")
    63  			})
    64  			It("returns both parent and leaf partition tables if the leaf-partition-data flag is set and there are no include tables", func() {
    65  				_ = backupCmdFlags.Set(options.LEAF_PARTITION_DATA, "true")
    66  				createStmt := `CREATE TABLE public.rank (id int, rank int, year int, gender
    67  char(1), count int )
    68  DISTRIBUTED BY (id)
    69  PARTITION BY LIST (gender)
    70  ( PARTITION girls VALUES ('F'),
    71    PARTITION boys VALUES ('M'),
    72    DEFAULT PARTITION other );`
    73  				testhelper.AssertQueryRuns(connectionPool, createStmt)
    74  				defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rank")
    75  
    76  				tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
    77  
    78  				expectedTableNames := []string{"public.rank", "public.rank_1_prt_boys", "public.rank_1_prt_girls", "public.rank_1_prt_other"}
    79  				tableNames := make([]string, 0)
    80  				for _, table := range tables {
    81  					tableNames = append(tableNames, table.FQN())
    82  				}
    83  				sort.Strings(tableNames)
    84  
    85  				Expect(tables).To(HaveLen(4))
    86  				Expect(tableNames).To(Equal(expectedTableNames))
    87  			})
    88  		})
    89  		It("returns user table information for table in specific schema", func() {
    90  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
    91  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
    92  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
    93  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema")
    94  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foo(i int)")
    95  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.foo")
    96  
    97  			_ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema")
    98  			tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
    99  
   100  			tableFoo := backup.Relation{Schema: "testschema", Name: "foo"}
   101  
   102  			Expect(tables).To(HaveLen(1))
   103  			structmatcher.ExpectStructsToMatchIncluding(&tableFoo, &tables[0], "Name", "Schema")
   104  		})
   105  		It("returns user table information for tables in includeTables", func() {
   106  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
   107  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
   108  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   109  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema")
   110  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foo(i int)")
   111  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.foo")
   112  
   113  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "testschema.foo")
   114  			includeRelationsQuoted, err := options.QuoteTableNames(connectionPool, backup.MustGetFlagStringArray(options.INCLUDE_RELATION))
   115  			Expect(err).NotTo(HaveOccurred())
   116  			tables := backup.GetIncludedUserTableRelations(connectionPool, includeRelationsQuoted)
   117  
   118  			tableFoo := backup.Relation{Schema: "testschema", Name: "foo"}
   119  
   120  			Expect(tables).To(HaveLen(1))
   121  			structmatcher.ExpectStructsToMatchIncluding(&tableFoo, &tables[0], "Name", "Schema")
   122  		})
   123  		It("returns user table information for tables not in excludeTables", func() {
   124  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
   125  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
   126  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   127  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema")
   128  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foo(i int)")
   129  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.foo")
   130  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.\"user\"(i int)")
   131  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.\"user\"")
   132  
   133  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "testschema.foo")
   134  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "testschema.user")
   135  			tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
   136  
   137  			tableFoo := backup.Relation{Schema: "public", Name: "foo"}
   138  
   139  			Expect(tables).To(HaveLen(1))
   140  			structmatcher.ExpectStructsToMatchIncluding(&tableFoo, &tables[0], "Name", "Schema")
   141  		})
   142  		It("returns user table information for tables in includeSchema but not in excludeTables", func() {
   143  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
   144  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
   145  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   146  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema")
   147  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.foo(i int)")
   148  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.foo")
   149  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.bar(i int)")
   150  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.bar")
   151  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.\"user\"(i int)")
   152  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.\"user\"")
   153  
   154  			_ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema")
   155  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "testschema.foo")
   156  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "testschema.user")
   157  			tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
   158  
   159  			tableFoo := backup.Relation{Schema: "testschema", Name: "bar"}
   160  			Expect(tables).To(HaveLen(1))
   161  			structmatcher.ExpectStructsToMatchIncluding(&tableFoo, &tables[0], "Name", "Schema")
   162  		})
   163  		It("returns user table information for tables even with an non existent excludeTable", func() {
   164  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foo(i int)")
   165  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foo")
   166  
   167  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "testschema.nonexistant")
   168  			tables := backup.GetIncludedUserTableRelations(connectionPool, []string{})
   169  
   170  			tableFoo := backup.Relation{Schema: "public", Name: "foo"}
   171  
   172  			Expect(tables).To(HaveLen(1))
   173  			structmatcher.ExpectStructsToMatchIncluding(&tableFoo, &tables[0], "Name", "Schema")
   174  		})
   175  	})
   176  	Describe("GetAllSequenceRelations", func() {
   177  		It("returns a slice of all sequences", func() {
   178  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence START 10")
   179  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   180  			testhelper.AssertQueryRuns(connectionPool, "COMMENT ON SEQUENCE public.my_sequence IS 'this is a sequence comment'")
   181  
   182  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   183  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE")
   184  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE testschema.my_sequence2")
   185  
   186  			sequences := backup.GetAllSequences(connectionPool)
   187  
   188  			mySequence := backup.Relation{Schema: "public", Name: "my_sequence"}
   189  			mySequence2 := backup.Relation{Schema: "testschema", Name: "my_sequence2"}
   190  
   191  			Expect(sequences).To(HaveLen(2))
   192  			structmatcher.ExpectStructsToMatchIncluding(&mySequence, &sequences[0], "Name", "Schema")
   193  			structmatcher.ExpectStructsToMatchIncluding(&mySequence2, &sequences[1], "Name", "Schema")
   194  		})
   195  		It("returns a slice of all sequences in a specific schema", func() {
   196  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence START 10")
   197  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   198  
   199  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   200  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE")
   201  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE testschema.my_sequence")
   202  			mySequence := backup.Relation{Schema: "testschema", Name: "my_sequence"}
   203  
   204  			_ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema")
   205  			sequences := backup.GetAllSequences(connectionPool)
   206  
   207  			Expect(sequences).To(HaveLen(1))
   208  			structmatcher.ExpectStructsToMatchIncluding(&mySequence, &sequences[0], "Name", "Schema")
   209  		})
   210  		It("does not return sequences owned by included tables", func() {
   211  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence START 10")
   212  
   213  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.seq_table(i int)")
   214  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.seq_table")
   215  			testhelper.AssertQueryRuns(connectionPool, "ALTER SEQUENCE public.my_sequence OWNED BY public.seq_table.i")
   216  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.seq_table")
   217  
   218  			sequences := backup.GetAllSequences(connectionPool)
   219  
   220  			Expect(sequences).To(BeEmpty())
   221  		})
   222  		It("returns sequences owned by excluded tables if the sequence is not excluded", func() {
   223  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence START 10")
   224  
   225  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.seq_table(i int)")
   226  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.seq_table")
   227  			testhelper.AssertQueryRuns(connectionPool, "ALTER SEQUENCE public.my_sequence OWNED BY public.seq_table.i")
   228  			mySequence := backup.Relation{Schema: "public", Name: "my_sequence"}
   229  
   230  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "public.seq_table")
   231  			sequences := backup.GetAllSequences(connectionPool)
   232  
   233  			Expect(sequences).To(HaveLen(1))
   234  			structmatcher.ExpectStructsToMatchIncluding(&mySequence, &sequences[0], "Name", "Schema")
   235  		})
   236  		It("does not return an excluded sequence", func() {
   237  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.sequence1 START 10")
   238  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.sequence1")
   239  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.sequence2 START 10")
   240  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.sequence2")
   241  
   242  			sequence2 := backup.Relation{Schema: "public", Name: "sequence2"}
   243  
   244  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "public.sequence1")
   245  			sequences := backup.GetAllSequences(connectionPool)
   246  
   247  			Expect(sequences).To(HaveLen(1))
   248  			structmatcher.ExpectStructsToMatchIncluding(&sequence2, &sequences[0], "Name", "Schema")
   249  		})
   250  		It("returns only the included sequence", func() {
   251  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.sequence1 START 10")
   252  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.sequence1")
   253  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.sequence2 START 10")
   254  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.sequence2")
   255  
   256  			sequence1 := backup.Relation{Schema: "public", Name: "sequence1"}
   257  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.sequence1")
   258  
   259  			sequences := backup.GetAllSequences(connectionPool)
   260  
   261  			Expect(sequences).To(HaveLen(1))
   262  			structmatcher.ExpectStructsToMatchIncluding(&sequence1, &sequences[0], "Name", "Schema")
   263  		})
   264  	})
   265  	Describe("GetSequenceDefinition", func() {
   266  		var dataType string
   267  
   268  		BeforeEach(func() {
   269  			dataType = ""
   270  			if true {
   271  				dataType = "bigint"
   272  			}
   273  		})
   274  
   275  		It("returns sequence information for sequence with default values", func() {
   276  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence")
   277  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   278  
   279  			resultSequenceDef := backup.GetSequenceDefinition(connectionPool, "public.my_sequence")
   280  
   281  			expectedSequence := backup.SequenceDefinition{LastVal: 1, Type: dataType, Increment: 1, MaxVal: math.MaxInt64, MinVal: 1, CacheVal: 1}
   282  			if true {
   283  				expectedSequence.StartVal = 1
   284  			}
   285  			if true {
   286  				// In GPDB 7+, default cache value is 20
   287  				expectedSequence.CacheVal = 20
   288  			}
   289  
   290  			structmatcher.ExpectStructsToMatch(&expectedSequence, &resultSequenceDef)
   291  		})
   292  		It("returns sequence information for a complex sequence", func() {
   293  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.with_sequence(a int, b char(20))")
   294  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.with_sequence")
   295  			testhelper.AssertQueryRuns(connectionPool,
   296  				"CREATE SEQUENCE public.my_sequence INCREMENT BY 5 MINVALUE 20 MAXVALUE 1000 START 100 OWNED BY public.with_sequence.a CACHE 1")
   297  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   298  			testhelper.AssertQueryRuns(connectionPool, "INSERT INTO public.with_sequence VALUES (nextval('public.my_sequence'), 'acme')")
   299  			testhelper.AssertQueryRuns(connectionPool, "INSERT INTO public.with_sequence VALUES (nextval('public.my_sequence'), 'beta')")
   300  
   301  			resultSequenceDef := backup.GetSequenceDefinition(connectionPool, "public.my_sequence")
   302  
   303  			expectedSequence := backup.SequenceDefinition{LastVal: 105, Type: dataType, Increment: 5, MaxVal: 1000, MinVal: 20, CacheVal: 1, IsCycled: false, IsCalled: true}
   304  			if true {
   305  				expectedSequence.StartVal = 100
   306  			}
   307  
   308  			structmatcher.ExpectStructsToMatch(&expectedSequence, &resultSequenceDef)
   309  		})
   310  	})
   311  	Describe("Get sequence owner information", func() {
   312  		It("returns sequence information for sequences owned by columns", func() {
   313  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.without_sequence(a int, b char(20));")
   314  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.without_sequence")
   315  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.with_sequence(a int, b char(20));")
   316  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.with_sequence")
   317  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence OWNED BY public.with_sequence.a;")
   318  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   319  
   320  			sequences := backup.GetAllSequences(connectionPool)
   321  
   322  			Expect(sequences).To(HaveLen(1))
   323  			Expect(sequences[0].OwningTable).To(Equal("public.with_sequence"))
   324  			Expect(sequences[0].OwningColumn).To(Equal("public.with_sequence.a"))
   325  		})
   326  		It("does not return sequence owner columns if the owning table is not backed up", func() {
   327  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.my_table(a int, b char(20));")
   328  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.my_table")
   329  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence OWNED BY public.my_table.a;")
   330  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   331  
   332  			_ = backupCmdFlags.Set(options.EXCLUDE_RELATION, "public.my_table")
   333  			sequences := backup.GetAllSequences(connectionPool)
   334  
   335  			Expect(sequences).To(HaveLen(1))
   336  			Expect(sequences[0].OwningTable).To(Equal(""))
   337  			Expect(sequences[0].OwningColumn).To(Equal(""))
   338  		})
   339  		It("returns sequence owner if both table and sequence are backed up", func() {
   340  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.my_table(a int, b char(20));")
   341  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.my_table")
   342  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence OWNED BY public.my_table.a;")
   343  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   344  
   345  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.my_sequence")
   346  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.my_table")
   347  			sequences := backup.GetAllSequences(connectionPool)
   348  
   349  			Expect(sequences).To(HaveLen(1))
   350  			Expect(sequences[0].OwningTable).To(Equal("public.my_table"))
   351  			Expect(sequences[0].OwningColumn).To(Equal("public.my_table.a"))
   352  		})
   353  		It("returns sequence owner if only the table is backed up", func() {
   354  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.my_table(a int, b char(20));")
   355  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.my_table")
   356  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence OWNED BY public.my_table.a;")
   357  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence")
   358  
   359  			_ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.my_table")
   360  			sequences := backup.GetAllSequences(connectionPool)
   361  			Expect(sequences).To(HaveLen(0))
   362  		})
   363  	})
   364  	Describe("GetAllSequences", func() {
   365  		// TODO: add test for IsIdentity field for GPDB7
   366  		It("returns a slice of definitions for all sequences", func() {
   367  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.seq_one START 3")
   368  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.seq_one")
   369  			testhelper.AssertQueryRuns(connectionPool, "COMMENT ON SEQUENCE public.seq_one IS 'this is a sequence comment'")
   370  			startValOne := int64(0)
   371  			startValTwo := int64(0)
   372  			if true {
   373  				startValOne = 3
   374  				startValTwo = 7
   375  			}
   376  
   377  			testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.seq_two START 7")
   378  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.seq_two")
   379  
   380  			seqOneRelation := backup.Relation{Schema: "public", Name: "seq_one"}
   381  
   382  			seqOneDef := backup.SequenceDefinition{LastVal: 3, Increment: 1, MaxVal: math.MaxInt64, MinVal: 1, CacheVal: 1, StartVal: startValOne}
   383  			seqTwoRelation := backup.Relation{Schema: "public", Name: "seq_two"}
   384  			seqTwoDef := backup.SequenceDefinition{LastVal: 7, Increment: 1, MaxVal: math.MaxInt64, MinVal: 1, CacheVal: 1, StartVal: startValTwo}
   385  			if true {
   386  				// In GPDB 7+, default cache value is 20
   387  				seqOneDef.CacheVal = 20
   388  				seqOneDef.Type = "bigint"
   389  				seqTwoDef.CacheVal = 20
   390  				seqTwoDef.Type = "bigint"
   391  			}
   392  
   393  			results := backup.GetAllSequences(connectionPool)
   394  
   395  			structmatcher.ExpectStructsToMatchExcluding(&seqOneRelation, &results[0].Relation, "SchemaOid", "Oid")
   396  			structmatcher.ExpectStructsToMatchExcluding(&seqOneDef, &results[0].Definition)
   397  			structmatcher.ExpectStructsToMatchExcluding(&seqTwoRelation, &results[1].Relation, "SchemaOid", "Oid")
   398  			structmatcher.ExpectStructsToMatchExcluding(&seqTwoDef, &results[1].Definition)
   399  		})
   400  	})
   401  	Describe("GetAllViews", func() {
   402  		var viewDef sql.NullString
   403  		BeforeEach(func() {
   404  			if false {
   405  				viewDef = sql.NullString{String: "SELECT 1;", Valid: true}
   406  			} else {
   407  				viewDef = sql.NullString{String: " SELECT 1;", Valid: true}
   408  			}
   409  		})
   410  		It("returns a slice for a basic view", func() {
   411  			testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW public.simpleview AS SELECT 1")
   412  			defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW public.simpleview")
   413  
   414  			results := backup.GetAllViews(connectionPool)
   415  			view := backup.View{Oid: 1, Schema: "public", Name: "simpleview", Definition: viewDef}
   416  
   417  			Expect(results).To(HaveLen(1))
   418  			structmatcher.ExpectStructsToMatchExcluding(&view, &results[0], "Oid")
   419  		})
   420  		It("returns a slice for view in a specific schema", func() {
   421  			testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW public.simpleview AS SELECT 1")
   422  			defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW public.simpleview")
   423  			testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema")
   424  			defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema")
   425  			testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW testschema.simpleview AS SELECT 1")
   426  			defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW testschema.simpleview")
   427  			_ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema")
   428  
   429  			results := backup.GetAllViews(connectionPool)
   430  			view := backup.View{Oid: 1, Schema: "testschema", Name: "simpleview", Definition: viewDef}
   431  
   432  			Expect(results).To(HaveLen(1))
   433  			structmatcher.ExpectStructsToMatchExcluding(&view, &results[0], "Oid")
   434  		})
   435  		It("PANIC on views with anyarray typecasts in its view definition", func() {
   436  			// The view definition gets incorrectly converted and stored as
   437  			// `SELECT '{1}'::anyarray = NULL::anyarray`. This issue is fixed
   438  			// in later versions of GPDB.
   439  			if true {
   440  				Skip("test only applicable to GPDB 4.3.X, GPDB 5.0.0 - 5.28.5, and GPDB 6.0.0 - 6.14.0")
   441  			}
   442  			testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW public.opexpr_array_typecasting AS SELECT '{1}'::int[] = NULL::int[]")
   443  			defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW public.opexpr_array_typecasting")
   444  
   445  			defer testhelper.ShouldPanicWithMessage(`[CRITICAL]:-Detected anyarray type cast in view definition for View 'public.opexpr_array_typecasting': Drop the view or recreate the view without explicit array type casts.`)
   446  			_ = backup.GetAllViews(connectionPool)
   447  		})
   448  		It("returns a slice for a view with options", func() {
   449  			testutils.SkipIfBefore6(connectionPool)
   450  			testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW public.simpleview WITH (security_barrier=true) AS SELECT 1")
   451  			defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW public.simpleview")
   452  
   453  			results := backup.GetAllViews(connectionPool)
   454  			view := backup.View{Oid: 1, Schema: "public", Name: "simpleview", Definition: viewDef, Options: " WITH (security_barrier=true)"}
   455  
   456  			Expect(results).To(HaveLen(1))
   457  			structmatcher.ExpectStructsToMatchExcluding(&view, &results[0], "Oid")
   458  		})
   459  		It("returns a slice for materialized views", func() {
   460  			if true {
   461  				Skip("test only applicable to GPDB 6.2 and above")
   462  			}
   463  			testhelper.AssertQueryRuns(connectionPool, "CREATE MATERIALIZED VIEW public.simplematerialview AS SELECT 1 AS a DISTRIBUTED BY (a)")
   464  			defer testhelper.AssertQueryRuns(connectionPool, "DROP MATERIALIZED VIEW public.simplematerialview")
   465  
   466  			results := backup.GetAllViews(connectionPool)
   467  			materialView := backup.View{Oid: 1, Schema: "public", Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS a;", Valid: true}, IsMaterialized: true, DistPolicy: "DISTRIBUTED BY (a)"}
   468  
   469  			Expect(results).To(HaveLen(1))
   470  			structmatcher.ExpectStructsToMatchExcluding(&materialView, &results[0], "Oid")
   471  		})
   472  		It("returns a slice for materialized views with storage parameters", func() {
   473  			if true {
   474  				Skip("test only applicable to GPDB 6.2 and above")
   475  			}
   476  			testhelper.AssertQueryRuns(connectionPool, "CREATE MATERIALIZED VIEW public.simplematerialview WITH (fillfactor=50, autovacuum_enabled=false) AS SELECT 1 AS a DISTRIBUTED BY (a)")
   477  			defer testhelper.AssertQueryRuns(connectionPool, "DROP MATERIALIZED VIEW public.simplematerialview")
   478  
   479  			results := backup.GetAllViews(connectionPool)
   480  			materialView := backup.View{Oid: 1, Schema: "public", Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS a;", Valid: true}, Options: " WITH (fillfactor=50, autovacuum_enabled=false)", IsMaterialized: true, DistPolicy: "DISTRIBUTED BY (a)"}
   481  
   482  			Expect(results).To(HaveLen(1))
   483  			structmatcher.ExpectStructsToMatchExcluding(&materialView, &results[0], "Oid")
   484  		})
   485  		It("returns a slice for materialized views with tablespaces", func() {
   486  			if true {
   487  				Skip("test only applicable to GPDB 6.2 and above")
   488  			}
   489  			testhelper.AssertQueryRuns(connectionPool, "CREATE TABLESPACE test_tablespace LOCATION '/tmp/test_dir'")
   490  			defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLESPACE test_tablespace")
   491  			testhelper.AssertQueryRuns(connectionPool, "CREATE MATERIALIZED VIEW public.simplematerialview TABLESPACE test_tablespace AS SELECT 1 AS a DISTRIBUTED BY (a)")
   492  			defer testhelper.AssertQueryRuns(connectionPool, "DROP MATERIALIZED VIEW public.simplematerialview")
   493  
   494  			results := backup.GetAllViews(connectionPool)
   495  			materialView := backup.View{Oid: 1, Schema: "public", Name: "simplematerialview", Definition: sql.NullString{String: " SELECT 1 AS a;", Valid: true}, Tablespace: "test_tablespace", IsMaterialized: true, DistPolicy: "DISTRIBUTED BY (a)"}
   496  
   497  			Expect(results).To(HaveLen(1))
   498  			structmatcher.ExpectStructsToMatchExcluding(&materialView, &results[0], "Oid")
   499  		})
   500  	})
   501  })