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  })