github.com/tuhaihe/gpbackup@v1.0.3/integration/postdata_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("ConstructImplicitIndexOidList", func() { 19 It("returns an empty string if there are no implicit indexes", func() { 20 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 21 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 22 23 testhelper.AssertQueryRuns(connectionPool, "CREATE UNIQUE INDEX simple_table_unique_index ON public.simple_table USING btree(i)") 24 25 indexNameSet := backup.ConstructImplicitIndexOidList(connectionPool) 26 27 Expect(indexNameSet).To(Equal("")) 28 }) 29 It("returns a string of all implicit indexes", func() { 30 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int UNIQUE)") 31 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 32 33 actualIndexOids := backup.ConstructImplicitIndexOidList(connectionPool) 34 35 expectedIndexOids := testutils.OidFromObjectName(connectionPool, "public", "simple_table_i_key", backup.TYPE_RELATION) 36 Expect(actualIndexOids).To(Equal(fmt.Sprintf("'%d'", expectedIndexOids))) 37 }) 38 It("returns a string of all implicit indexes for long table names", func() { 39 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.long_table_name_63_chars_abcdefghigklmnopqrstuvwxyz123456789abc(mycol int UNIQUE)") 40 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.long_table_name_63_chars_abcdefghigklmnopqrstuvwxyz123456789abc") 41 42 actualIndexOids := backup.ConstructImplicitIndexOidList(connectionPool) 43 44 expectedIndexOids := testutils.OidFromObjectName(connectionPool, "public", "long_table_name_63_chars_abcdefghigklmnopqrstuvwxyz12_mycol_key", backup.TYPE_RELATION) 45 Expect(actualIndexOids).To(Equal(fmt.Sprintf("'%d'", expectedIndexOids))) 46 }) 47 48 }) 49 Describe("GetIndex", func() { 50 It("returns no slice when no index exists", func() { 51 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int)") 52 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 53 54 results := backup.GetIndexes(connectionPool) 55 56 Expect(results).To(BeEmpty()) 57 }) 58 It("returns a slice of multiple indexes", func() { 59 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 60 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 61 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON public.simple_table(i)") 62 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx1") 63 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx2 ON public.simple_table(j)") 64 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx2") 65 66 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx1 ON public.simple_table USING btree (i)", Valid: true}} 67 index2 := backup.IndexDefinition{Oid: 1, Name: "simple_table_idx2", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx2 ON public.simple_table USING btree (j)", Valid: true}} 68 69 results := backup.GetIndexes(connectionPool) 70 71 Expect(results).To(HaveLen(2)) 72 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx1", backup.TYPE_INDEX) 73 results[1].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx2", backup.TYPE_INDEX) 74 75 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 76 structmatcher.ExpectStructsToMatchExcluding(&index2, &results[1], "Oid") 77 }) 78 It("returns a slice of multiple indexes, including implicit indexes created by constraints", func() { 79 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 80 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table CASCADE") 81 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.simple_table ADD CONSTRAINT test_constraint UNIQUE (i, k)") 82 testhelper.AssertQueryRuns(connectionPool, "CREATE UNIQUE INDEX simple_table_idx1 ON public.simple_table(i)") 83 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx2 ON public.simple_table(j)") 84 85 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE UNIQUE INDEX simple_table_idx1 ON public.simple_table USING btree (i)", Valid: true}} 86 index2 := backup.IndexDefinition{Oid: 1, Name: "simple_table_idx2", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx2 ON public.simple_table USING btree (j)", Valid: true}} 87 88 results := backup.GetIndexes(connectionPool) 89 supportsConstraint := make([]backup.IndexDefinition, 0) 90 userIndex := make([]backup.IndexDefinition, 0) 91 for _, indexDef := range results { 92 if indexDef.SupportsConstraint { 93 supportsConstraint = append(supportsConstraint, indexDef) 94 } else { 95 userIndex = append(userIndex, indexDef) 96 } 97 } 98 99 Expect(userIndex).To(HaveLen(2)) 100 Expect(supportsConstraint).To(HaveLen(1)) 101 structmatcher.ExpectStructsToMatchExcluding(&index1, &userIndex[0], "Oid") 102 structmatcher.ExpectStructsToMatchExcluding(&index2, &userIndex[1], "Oid") 103 }) 104 It("returns a slice of indexes for only partition parent tables", func() { 105 // In GPDB 7+, all partitions will have their own CREATE INDEX statement 106 // followed by an ALTER INDEX ATTACH PARTITION statement 107 if true { 108 Skip("Test is not applicable to GPDB 7+") 109 } 110 111 testhelper.AssertQueryRuns(connectionPool, `CREATE TABLE public.part (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) 112 PARTITION BY RANGE (date) 113 (PARTITION Jan08 START (date '2008-01-01') INCLUSIVE , 114 PARTITION Feb08 START (date '2008-02-01') INCLUSIVE , 115 PARTITION Mar08 START (date '2008-03-01') INCLUSIVE 116 END (date '2008-04-01') EXCLUSIVE); 117 `) 118 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.part") 119 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX part_idx ON public.part(id)") 120 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.part_idx") 121 122 index1 := backup.IndexDefinition{Oid: 0, Name: "part_idx", OwningSchema: "public", OwningTable: "part", Def: sql.NullString{String: "CREATE INDEX part_idx ON public.part USING btree (id)", Valid: true}} 123 124 results := backup.GetIndexes(connectionPool) 125 126 Expect(results).To(HaveLen(1)) 127 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 128 }) 129 It("returns a slice containing an index in a non-default tablespace", func() { 130 if false { 131 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLESPACE test_tablespace FILESPACE test_dir") 132 } else { 133 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLESPACE test_tablespace LOCATION '/tmp/test_dir'") 134 } 135 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLESPACE test_tablespace") 136 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 137 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 138 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx ON public.simple_table(i) TABLESPACE test_tablespace") 139 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx") 140 141 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx", OwningSchema: "public", OwningTable: "simple_table", Tablespace: "test_tablespace", Def: sql.NullString{String: "CREATE INDEX simple_table_idx ON public.simple_table USING btree (i)", Valid: true}} 142 143 results := backup.GetIndexes(connectionPool) 144 145 Expect(results).To(HaveLen(1)) 146 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx", backup.TYPE_INDEX) 147 148 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 149 }) 150 It("returns a slice for an index in specific schema", func() { 151 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 152 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 153 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON public.simple_table(i)") 154 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx1") 155 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 156 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 157 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.simple_table(i int, j int, k int)") 158 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.simple_table") 159 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON testschema.simple_table(i)") 160 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX testschema.simple_table_idx1") 161 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 162 163 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "testschema", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx1 ON testschema.simple_table USING btree (i)", Valid: true}} 164 165 results := backup.GetIndexes(connectionPool) 166 167 Expect(results).To(HaveLen(1)) 168 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx1", backup.TYPE_INDEX) 169 170 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 171 }) 172 It("returns a slice of indexes belonging to filtered tables", func() { 173 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 174 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 175 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON public.simple_table(i)") 176 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx1") 177 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 178 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 179 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.simple_table(i int, j int, k int)") 180 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.simple_table") 181 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON testschema.simple_table(i)") 182 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX testschema.simple_table_idx1") 183 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "testschema.simple_table") 184 185 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "testschema", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx1 ON testschema.simple_table USING btree (i)", Valid: true}} 186 187 results := backup.GetIndexes(connectionPool) 188 189 Expect(results).To(HaveLen(1)) 190 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx1", backup.TYPE_INDEX) 191 192 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 193 }) 194 It("returns a slice for an index used for clustering", func() { 195 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 196 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 197 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON public.simple_table(i)") 198 defer testhelper.AssertQueryRuns(connectionPool, "DROP INDEX public.simple_table_idx1") 199 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.simple_table CLUSTER ON simple_table_idx1") 200 201 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx1 ON public.simple_table USING btree (i)", Valid: true}, IsClustered: true} 202 203 results := backup.GetIndexes(connectionPool) 204 205 Expect(results).To(HaveLen(1)) 206 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx1", backup.TYPE_INDEX) 207 208 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 209 }) 210 It("returns a slice of an index with statistics on expression columns", func() { 211 testutils.SkipIfBefore7(connectionPool) 212 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.simple_table(i int, j int, k int)") 213 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.simple_table") 214 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX simple_table_idx1 ON public.simple_table(i, (i+100), (j * 8))") 215 testhelper.AssertQueryRuns(connectionPool, "ALTER INDEX public.simple_table_idx1 ALTER COLUMN 2 SET STATISTICS 400") 216 testhelper.AssertQueryRuns(connectionPool, "ALTER INDEX public.simple_table_idx1 ALTER COLUMN 3 SET STATISTICS 500") 217 218 index1 := backup.IndexDefinition{Oid: 0, Name: "simple_table_idx1", OwningSchema: "public", OwningTable: "simple_table", Def: sql.NullString{String: "CREATE INDEX simple_table_idx1 ON public.simple_table USING btree (i, ((i + 100)), ((j * 8)))", Valid: true}, StatisticsColumns: "2,3", StatisticsValues: "400,500"} 219 220 results := backup.GetIndexes(connectionPool) 221 222 Expect(results).To(HaveLen(1)) 223 results[0].Oid = testutils.OidFromObjectName(connectionPool, "", "simple_table_idx1", backup.TYPE_INDEX) 224 225 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[0], "Oid") 226 }) 227 It("returns a sorted slice of partition indexes ", func() { 228 testutils.SkipIfBefore7(connectionPool) 229 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foopart_new (a integer, b integer) PARTITION BY RANGE (b) DISTRIBUTED BY (a)") 230 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.foopart_new") 231 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.foopart_new_p1 (a integer, b integer) DISTRIBUTED BY (a); ALTER TABLE ONLY public.foopart_new ATTACH PARTITION public.foopart_new_p1 FOR VALUES FROM (0) TO (1);") 232 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX fooidx ON ONLY public.foopart_new USING btree (b)") 233 testhelper.AssertQueryRuns(connectionPool, "CREATE INDEX foopart_new_p1_b_idx ON public.foopart_new_p1 USING btree (b)") 234 testhelper.AssertQueryRuns(connectionPool, "ALTER INDEX public.fooidx ATTACH PARTITION public.foopart_new_p1_b_idx;") 235 236 index0 := backup.IndexDefinition{Oid: 0, Name: "fooidx", OwningSchema: "public", OwningTable: "foopart_new", Def: sql.NullString{String: "CREATE INDEX fooidx ON ONLY public.foopart_new USING btree (b)", Valid: true}} 237 index1 := backup.IndexDefinition{Oid: 0, Name: "foopart_new_p1_b_idx", OwningSchema: "public", OwningTable: "foopart_new_p1", Def: sql.NullString{String: "CREATE INDEX foopart_new_p1_b_idx ON public.foopart_new_p1 USING btree (b)", Valid: true}, ParentIndexFQN: "public.fooidx"} 238 index0.Oid = testutils.OidFromObjectName(connectionPool, "", "fooidx", backup.TYPE_INDEX) 239 index1.Oid = testutils.OidFromObjectName(connectionPool, "", "foopart_new_p1_b_idx", backup.TYPE_INDEX) 240 index1.ParentIndex = index0.Oid 241 242 results := backup.GetIndexes(connectionPool) 243 244 Expect(results).To(HaveLen(2)) 245 246 structmatcher.ExpectStructsToMatchExcluding(&index0, &results[0]) 247 structmatcher.ExpectStructsToMatchExcluding(&index1, &results[1]) 248 }) 249 250 It("returns a slice for an index with non-key columns included", func() { 251 testutils.SkipIfBefore7(connectionPool) 252 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.table_with_index (a int, b int, c int, d int) DISTRIBUTED BY (a);") 253 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.table_with_index") 254 testhelper.AssertQueryRuns(connectionPool, "CREATE UNIQUE INDEX table_with_index_idx ON public.table_with_index USING btree (a, b) INCLUDE (c, d);") 255 256 expectedIndex := backup.IndexDefinition{Oid: 0, Name: "table_with_index_idx", OwningSchema: "public", OwningTable: "table_with_index", Def: sql.NullString{String: "CREATE UNIQUE INDEX table_with_index_idx ON public.table_with_index USING btree (a, b) INCLUDE (c, d)", Valid: true}, IsClustered: false} 257 258 results := backup.GetIndexes(connectionPool) 259 260 Expect(results).To(HaveLen(1)) 261 structmatcher.ExpectStructsToMatchExcluding(&expectedIndex, &results[0], "Oid") 262 }) 263 }) 264 Describe("GetRules", func() { 265 var ( 266 ruleDef1 string 267 ruleDef2 string 268 rule1 backup.RuleDefinition 269 ) 270 BeforeEach(func() { 271 if false { 272 ruleDef1 = "CREATE RULE double_insert AS ON INSERT TO public.rule_table1 DO INSERT INTO public.rule_table1 (i) VALUES (1);" 273 ruleDef2 = "CREATE RULE update_notify AS ON UPDATE TO public.rule_table1 DO NOTIFY rule_table1;" 274 } else { 275 ruleDef1 = "CREATE RULE double_insert AS\n ON INSERT TO public.rule_table1 DO INSERT INTO public.rule_table1 (i)\n VALUES (1);" 276 ruleDef2 = "CREATE RULE update_notify AS\n ON UPDATE TO public.rule_table1 DO\n NOTIFY rule_table1;" 277 } 278 rule1 = backup.RuleDefinition{Oid: 0, Name: "double_insert", OwningSchema: "public", OwningTable: "rule_table1", Def: sql.NullString{String: ruleDef1, Valid: true}} 279 }) 280 It("returns no slice when no rule exists", func() { 281 results := backup.GetRules(connectionPool) 282 283 Expect(results).To(BeEmpty()) 284 }) 285 It("returns a slice of multiple rules", func() { 286 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.rule_table1(i int)") 287 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rule_table1") 288 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.rule_table2(i int)") 289 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rule_table2") 290 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE double_insert AS ON INSERT TO public.rule_table1 DO INSERT INTO public.rule_table1 (i) VALUES (1)") 291 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE double_insert ON public.rule_table1") 292 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE update_notify AS ON UPDATE TO public.rule_table1 DO NOTIFY rule_table1") 293 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE update_notify ON public.rule_table1") 294 295 rule2 := backup.RuleDefinition{Oid: 1, Name: "update_notify", OwningSchema: "public", OwningTable: "rule_table1", Def: sql.NullString{String: ruleDef2, Valid: true}} 296 297 results := backup.GetRules(connectionPool) 298 299 Expect(results).To(HaveLen(2)) 300 structmatcher.ExpectStructsToMatchExcluding(&rule1, &results[0], "Oid") 301 structmatcher.ExpectStructsToMatchExcluding(&rule2, &results[1], "Oid") 302 }) 303 It("returns a slice of rules for a specific schema", func() { 304 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.rule_table1(i int)") 305 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rule_table1") 306 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE double_insert AS ON INSERT TO public.rule_table1 DO INSERT INTO public.rule_table1 (i) VALUES (1)") 307 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE double_insert ON public.rule_table1") 308 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 309 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 310 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.rule_table1(i int)") 311 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.rule_table1") 312 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE double_insert AS ON INSERT TO testschema.rule_table1 DO INSERT INTO testschema.rule_table1 (i) VALUES (1)") 313 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE double_insert ON testschema.rule_table1") 314 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "public") 315 316 results := backup.GetRules(connectionPool) 317 318 Expect(results).To(HaveLen(1)) 319 structmatcher.ExpectStructsToMatchExcluding(&rule1, &results[0], "Oid") 320 }) 321 It("returns a slice of rules belonging to filtered tables", func() { 322 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.rule_table1(i int)") 323 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.rule_table1") 324 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE double_insert AS ON INSERT TO public.rule_table1 DO INSERT INTO public.rule_table1 (i) VALUES (1)") 325 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE double_insert ON public.rule_table1") 326 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 327 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 328 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.rule_table1(i int)") 329 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.rule_table1") 330 testhelper.AssertQueryRuns(connectionPool, "CREATE RULE double_insert AS ON INSERT TO testschema.rule_table1 DO INSERT INTO testschema.rule_table1 (i) VALUES (1)") 331 defer testhelper.AssertQueryRuns(connectionPool, "DROP RULE double_insert ON testschema.rule_table1") 332 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "public.rule_table1") 333 334 results := backup.GetRules(connectionPool) 335 336 Expect(results).To(HaveLen(1)) 337 structmatcher.ExpectStructsToMatchExcluding(&rule1, &results[0], "Oid") 338 }) 339 }) 340 Describe("GetTriggers", func() { 341 It("returns no slice when no trigger exists", func() { 342 results := backup.GetTriggers(connectionPool) 343 344 Expect(results).To(BeEmpty()) 345 }) 346 It("returns a slice of multiple triggers", func() { 347 triggerString1 := `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 348 triggerString2 := `CREATE TRIGGER sync_trigger_table2 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table2 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 349 if true { 350 triggerString1 = `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 351 triggerString2 = `CREATE TRIGGER sync_trigger_table2 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table2 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 352 353 } 354 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table1(i int)") 355 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table1") 356 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table2(j int)") 357 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table2") 358 testhelper.AssertQueryRuns(connectionPool, triggerString1) 359 testhelper.AssertQueryRuns(connectionPool, triggerString2) 360 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table1 ON public.trigger_table1") 361 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table2 ON public.trigger_table2") 362 363 trigger1 := backup.TriggerDefinition{Oid: 0, Name: "sync_trigger_table1", OwningSchema: "public", OwningTable: "trigger_table1", Def: sql.NullString{String: triggerString1, Valid: true}} 364 trigger2 := backup.TriggerDefinition{Oid: 1, Name: "sync_trigger_table2", OwningSchema: "public", OwningTable: "trigger_table2", Def: sql.NullString{String: triggerString2, Valid: true}} 365 366 results := backup.GetTriggers(connectionPool) 367 368 Expect(results).To(HaveLen(2)) 369 structmatcher.ExpectStructsToMatchExcluding(&trigger1, &results[0], "Oid") 370 structmatcher.ExpectStructsToMatchExcluding(&trigger2, &results[1], "Oid") 371 }) 372 It("does not include constraint triggers", func() { 373 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table1(i int PRIMARY KEY)") 374 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table1") 375 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table2(j int)") 376 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table2") 377 testhelper.AssertQueryRuns(connectionPool, "ALTER TABLE public.trigger_table2 ADD CONSTRAINT fkc FOREIGN KEY (j) REFERENCES public.trigger_table1 (i) ON UPDATE RESTRICT ON DELETE RESTRICT") 378 379 results := backup.GetTriggers(connectionPool) 380 381 Expect(results).To(BeEmpty()) 382 }) 383 It("returns a slice of triggers for a specific schema", func() { 384 triggerString1 := `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 385 triggerString2 := `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON testschema.trigger_table1 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 386 if true { 387 triggerString1 = `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 388 triggerString2 = `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON testschema.trigger_table1 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 389 } 390 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table1(i int)") 391 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table1") 392 testhelper.AssertQueryRuns(connectionPool, triggerString1) 393 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table1 ON public.trigger_table1") 394 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 395 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 396 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.trigger_table1(i int)") 397 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.trigger_table1") 398 testhelper.AssertQueryRuns(connectionPool, triggerString2) 399 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table1 ON testschema.trigger_table1") 400 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 401 402 trigger1 := backup.TriggerDefinition{Oid: 0, Name: "sync_trigger_table1", OwningSchema: "testschema", OwningTable: "trigger_table1", Def: sql.NullString{String: triggerString2, Valid: true}} 403 404 results := backup.GetTriggers(connectionPool) 405 406 Expect(results).To(HaveLen(1)) 407 structmatcher.ExpectStructsToMatchExcluding(&trigger1, &results[0], "Oid") 408 }) 409 It("returns a slice of triggers belonging to filtered tables", func() { 410 triggerString1 := `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 411 triggerString2 := `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON testschema.trigger_table1 FOR EACH STATEMENT EXECUTE PROCEDURE "RI_FKey_check_ins"()` 412 if true { 413 triggerString1 = `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON public.trigger_table1 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 414 triggerString2 = `CREATE TRIGGER sync_trigger_table1 AFTER INSERT OR DELETE OR UPDATE ON testschema.trigger_table1 FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()` 415 } 416 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.trigger_table1(i int)") 417 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.trigger_table1") 418 testhelper.AssertQueryRuns(connectionPool, triggerString1) 419 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table1 ON public.trigger_table1") 420 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 421 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 422 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE testschema.trigger_table1(i int)") 423 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE testschema.trigger_table1") 424 testhelper.AssertQueryRuns(connectionPool, triggerString2) 425 defer testhelper.AssertQueryRuns(connectionPool, "DROP TRIGGER sync_trigger_table1 ON testschema.trigger_table1") 426 _ = backupCmdFlags.Set(options.INCLUDE_RELATION, "testschema.trigger_table1") 427 428 trigger1 := backup.TriggerDefinition{Oid: 0, Name: "sync_trigger_table1", OwningSchema: "testschema", OwningTable: "trigger_table1", Def: sql.NullString{String: triggerString2, Valid: true}} 429 430 results := backup.GetTriggers(connectionPool) 431 432 Expect(results).To(HaveLen(1)) 433 structmatcher.ExpectStructsToMatchExcluding(&trigger1, &results[0], "Oid") 434 }) 435 }) 436 Describe("GetEventTriggers", func() { 437 BeforeEach(func() { 438 testutils.SkipIfBefore6(connectionPool) 439 testhelper.AssertQueryRuns(connectionPool, `CREATE FUNCTION abort_any_command() 440 RETURNS event_trigger LANGUAGE plpgsql 441 AS $$ BEGIN RAISE EXCEPTION 'exception'; END; $$;`) 442 }) 443 AfterEach(func() { 444 testhelper.AssertQueryRuns(connectionPool, `DROP FUNCTION abort_any_command()`) 445 }) 446 It("returns no slice when no event trigger exists", func() { 447 results := backup.GetEventTriggers(connectionPool) 448 449 Expect(results).To(BeEmpty()) 450 }) 451 It("returns a slice of multiple event triggers ", func() { 452 453 testhelper.AssertQueryRuns(connectionPool, "CREATE EVENT TRIGGER testeventtrigger1 ON ddl_command_start EXECUTE PROCEDURE abort_any_command();") 454 defer testhelper.AssertQueryRuns(connectionPool, "DROP EVENT TRIGGER testeventtrigger1") 455 testhelper.AssertQueryRuns(connectionPool, "CREATE EVENT TRIGGER testeventtrigger2 ON ddl_command_start EXECUTE PROCEDURE abort_any_command();") 456 defer testhelper.AssertQueryRuns(connectionPool, "DROP EVENT TRIGGER testeventtrigger2") 457 458 results := backup.GetEventTriggers(connectionPool) 459 460 eventTrigger1 := backup.EventTrigger{Oid: 1, Name: "testeventtrigger1", Event: "ddl_command_start", FunctionName: "abort_any_command", Enabled: "O"} 461 eventTrigger2 := backup.EventTrigger{Oid: 1, Name: "testeventtrigger2", Event: "ddl_command_start", FunctionName: "abort_any_command", Enabled: "O"} 462 463 Expect(results).To(HaveLen(2)) 464 structmatcher.ExpectStructsToMatchExcluding(&eventTrigger1, &results[0], "Oid") 465 structmatcher.ExpectStructsToMatchExcluding(&eventTrigger2, &results[1], "Oid") 466 467 }) 468 It("returns a slice of event trigger with a filter tag", func() { 469 testhelper.AssertQueryRuns(connectionPool, "CREATE EVENT TRIGGER testeventtrigger1 ON ddl_command_start WHEN TAG IN ('DROP FUNCTION') EXECUTE PROCEDURE abort_any_command();") 470 defer testhelper.AssertQueryRuns(connectionPool, "DROP EVENT TRIGGER testeventtrigger1") 471 472 results := backup.GetEventTriggers(connectionPool) 473 474 eventTrigger1 := backup.EventTrigger{Oid: 1, Name: "testeventtrigger1", Event: "ddl_command_start", FunctionName: "abort_any_command", Enabled: "O", EventTags: `'DROP FUNCTION'`} 475 476 Expect(results).To(HaveLen(1)) 477 structmatcher.ExpectStructsToMatchExcluding(&eventTrigger1, &results[0], "Oid") 478 479 }) 480 It("returns a slice of event trigger with multiple filter tags", func() { 481 testhelper.AssertQueryRuns(connectionPool, "CREATE EVENT TRIGGER testeventtrigger1 ON ddl_command_start WHEN TAG IN ('DROP FUNCTION', 'DROP TABLE') EXECUTE PROCEDURE abort_any_command();") 482 defer testhelper.AssertQueryRuns(connectionPool, "DROP EVENT TRIGGER testeventtrigger1") 483 484 results := backup.GetEventTriggers(connectionPool) 485 486 eventTrigger1 := backup.EventTrigger{Oid: 1, Name: "testeventtrigger1", Event: "ddl_command_start", FunctionName: "abort_any_command", Enabled: "O", EventTags: `'DROP FUNCTION', 'DROP TABLE'`} 487 488 Expect(results).To(HaveLen(1)) 489 structmatcher.ExpectStructsToMatchExcluding(&eventTrigger1, &results[0], "Oid") 490 491 }) 492 }) 493 Describe("GetPolicies", func() { 494 BeforeEach(func() { 495 testutils.SkipIfBefore7(connectionPool) 496 }) 497 It("returns no results when no policies exists", func() { 498 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.policy_table(user_name text)") 499 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.policy_table") 500 results := backup.GetPolicies(connectionPool) 501 Expect(results).To(BeEmpty()) 502 }) 503 It("returns a slice of multiple policies", func() { 504 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.users(user_name text)") 505 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.users") 506 testhelper.AssertQueryRuns(connectionPool, "CREATE POLICY policy1_user_sel ON public.users FOR SELECT USING (true)") 507 defer testhelper.AssertQueryRuns(connectionPool, "DROP POLICY policy1_user_sel on public.users") 508 testhelper.AssertQueryRuns(connectionPool, "CREATE POLICY policy2_user_mod ON public.users USING (user_name = current_user)") 509 defer testhelper.AssertQueryRuns(connectionPool, "DROP POLICY policy2_user_mod on public.users") 510 511 results := backup.GetPolicies(connectionPool) 512 513 Expect(results).To(HaveLen(2)) 514 policy1 := backup.RLSPolicy{Oid: 1, Name: "policy1_user_sel", Cmd: "r", Permissive: "true", Schema: "public", Table: "users", Qual: "true"} 515 policy2 := backup.RLSPolicy{Oid: 1, Name: "policy2_user_mod", Cmd: "*", Permissive: "true", Schema: "public", Table: "users", Qual: "(user_name = CURRENT_USER)"} 516 structmatcher.ExpectStructsToMatchExcluding(&policy1, &results[0], "Oid") 517 structmatcher.ExpectStructsToMatchExcluding(&policy2, &results[1], "Oid") 518 }) 519 It("returns a slice of multiple policies with checks", func() { 520 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.passwd(user_name text, shell text not null)") 521 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.passwd") 522 testhelper.AssertQueryRuns(connectionPool, "CREATE ROLE BOB") 523 defer testhelper.AssertQueryRuns(connectionPool, "DROP ROLE BOB") 524 testhelper.AssertQueryRuns(connectionPool, "CREATE POLICY policy1_bob_all ON public.passwd TO bob USING (true) WITH CHECK (true)") 525 defer testhelper.AssertQueryRuns(connectionPool, "DROP POLICY policy1_bob_all on public.passwd") 526 testhelper.AssertQueryRuns(connectionPool, "CREATE POLICY policy2_all_view ON public.passwd FOR SELECT USING (true)") 527 defer testhelper.AssertQueryRuns(connectionPool, "DROP POLICY policy2_all_view on public.passwd") 528 testhelper.AssertQueryRuns(connectionPool, "CREATE POLICY policy3_user_mod ON public.passwd FOR UPDATE USING (user_name = current_user) WITH CHECK (current_user = user_name AND shell IN ('/bin/bash', '/bin/sh'))") 529 defer testhelper.AssertQueryRuns(connectionPool, "DROP POLICY policy3_user_mod on public.passwd") 530 531 results := backup.GetPolicies(connectionPool) 532 533 Expect(results).To(HaveLen(3)) 534 policy1 := backup.RLSPolicy{Oid: 1, Name: "policy1_bob_all", Cmd: "*", Permissive: "true", Schema: "public", Table: "passwd", Roles: "bob", Qual: "true",WithCheck:"true"} 535 policy2 := backup.RLSPolicy{Oid: 1, Name: "policy2_all_view", Cmd: "r", Permissive: "true", Schema: "public", Table: "passwd", Roles: "", Qual: "true", WithCheck:""} 536 policy3 := backup.RLSPolicy{Oid: 1, Name: "policy3_user_mod", Cmd: "w", Permissive: "true", Schema: "public", Table: "passwd", Roles: "", Qual: "(user_name = CURRENT_USER)", WithCheck: "((CURRENT_USER = user_name) AND (shell = ANY (ARRAY['/bin/bash'::text, '/bin/sh'::text])))"} 537 structmatcher.ExpectStructsToMatchExcluding(&policy1, &results[0], "Oid") 538 structmatcher.ExpectStructsToMatchExcluding(&policy2, &results[1], "Oid") 539 structmatcher.ExpectStructsToMatchExcluding(&policy3, &results[2], "Oid") 540 }) 541 }) 542 // TODO: test GetExtendedStatistics() 543 })