1-- 2-- ALTER_TABLE 3-- 4 5-- Clean up in case a prior regression run failed 6SET client_min_messages TO 'warning'; 7DROP ROLE IF EXISTS regress_alter_table_user1; 8RESET client_min_messages; 9 10CREATE USER regress_alter_table_user1; 11 12-- 13-- add attribute 14-- 15 16CREATE TABLE attmp (initial int4); 17 18COMMENT ON TABLE attmp_wrong IS 'table comment'; 19COMMENT ON TABLE attmp IS 'table comment'; 20COMMENT ON TABLE attmp IS NULL; 21 22ALTER TABLE attmp ADD COLUMN xmin integer; -- fails 23 24ALTER TABLE attmp ADD COLUMN a int4 default 3; 25 26ALTER TABLE attmp ADD COLUMN b name; 27 28ALTER TABLE attmp ADD COLUMN c text; 29 30ALTER TABLE attmp ADD COLUMN d float8; 31 32ALTER TABLE attmp ADD COLUMN e float4; 33 34ALTER TABLE attmp ADD COLUMN f int2; 35 36ALTER TABLE attmp ADD COLUMN g polygon; 37 38ALTER TABLE attmp ADD COLUMN i char; 39 40ALTER TABLE attmp ADD COLUMN k int4; 41 42ALTER TABLE attmp ADD COLUMN l tid; 43 44ALTER TABLE attmp ADD COLUMN m xid; 45 46ALTER TABLE attmp ADD COLUMN n oidvector; 47 48--ALTER TABLE attmp ADD COLUMN o lock; 49ALTER TABLE attmp ADD COLUMN p boolean; 50 51ALTER TABLE attmp ADD COLUMN q point; 52 53ALTER TABLE attmp ADD COLUMN r lseg; 54 55ALTER TABLE attmp ADD COLUMN s path; 56 57ALTER TABLE attmp ADD COLUMN t box; 58 59ALTER TABLE attmp ADD COLUMN v timestamp; 60 61ALTER TABLE attmp ADD COLUMN w interval; 62 63ALTER TABLE attmp ADD COLUMN x float8[]; 64 65ALTER TABLE attmp ADD COLUMN y float4[]; 66 67ALTER TABLE attmp ADD COLUMN z int2[]; 68 69INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t, 70 v, w, x, y, z) 71 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 72 'c', 73 314159, '(1,1)', '512', 74 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', 75 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', 76 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); 77 78SELECT * FROM attmp; 79 80DROP TABLE attmp; 81 82-- the wolf bug - schema mods caused inconsistent row descriptors 83CREATE TABLE attmp ( 84 initial int4 85); 86 87ALTER TABLE attmp ADD COLUMN a int4; 88 89ALTER TABLE attmp ADD COLUMN b name; 90 91ALTER TABLE attmp ADD COLUMN c text; 92 93ALTER TABLE attmp ADD COLUMN d float8; 94 95ALTER TABLE attmp ADD COLUMN e float4; 96 97ALTER TABLE attmp ADD COLUMN f int2; 98 99ALTER TABLE attmp ADD COLUMN g polygon; 100 101ALTER TABLE attmp ADD COLUMN i char; 102 103ALTER TABLE attmp ADD COLUMN k int4; 104 105ALTER TABLE attmp ADD COLUMN l tid; 106 107ALTER TABLE attmp ADD COLUMN m xid; 108 109ALTER TABLE attmp ADD COLUMN n oidvector; 110 111--ALTER TABLE attmp ADD COLUMN o lock; 112ALTER TABLE attmp ADD COLUMN p boolean; 113 114ALTER TABLE attmp ADD COLUMN q point; 115 116ALTER TABLE attmp ADD COLUMN r lseg; 117 118ALTER TABLE attmp ADD COLUMN s path; 119 120ALTER TABLE attmp ADD COLUMN t box; 121 122ALTER TABLE attmp ADD COLUMN v timestamp; 123 124ALTER TABLE attmp ADD COLUMN w interval; 125 126ALTER TABLE attmp ADD COLUMN x float8[]; 127 128ALTER TABLE attmp ADD COLUMN y float4[]; 129 130ALTER TABLE attmp ADD COLUMN z int2[]; 131 132INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t, 133 v, w, x, y, z) 134 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 135 'c', 136 314159, '(1,1)', '512', 137 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', 138 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', 139 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); 140 141SELECT * FROM attmp; 142 143CREATE INDEX attmp_idx ON attmp (a, (d + e), b); 144 145ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000; 146 147ALTER INDEX attmp_idx ALTER COLUMN 1 SET STATISTICS 1000; 148 149ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS 1000; 150 151\d+ attmp_idx 152 153ALTER INDEX attmp_idx ALTER COLUMN 3 SET STATISTICS 1000; 154 155ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000; 156 157ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1; 158 159DROP TABLE attmp; 160 161 162-- 163-- rename - check on both non-temp and temp tables 164-- 165CREATE TABLE attmp (regtable int); 166CREATE TEMP TABLE attmp (attmptable int); 167 168ALTER TABLE attmp RENAME TO attmp_new; 169 170SELECT * FROM attmp; 171SELECT * FROM attmp_new; 172 173ALTER TABLE attmp RENAME TO attmp_new2; 174 175SELECT * FROM attmp; -- should fail 176SELECT * FROM attmp_new; 177SELECT * FROM attmp_new2; 178 179DROP TABLE attmp_new; 180DROP TABLE attmp_new2; 181 182-- check rename of partitioned tables and indexes also 183CREATE TABLE part_attmp (a int primary key) partition by range (a); 184CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100); 185ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index; 186ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index; 187ALTER TABLE part_attmp RENAME TO part_at2tmp; 188ALTER TABLE part_attmp1 RENAME TO part_at2tmp1; 189SET ROLE regress_alter_table_user1; 190ALTER INDEX part_attmp_index RENAME TO fail; 191ALTER INDEX part_attmp1_index RENAME TO fail; 192ALTER TABLE part_at2tmp RENAME TO fail; 193ALTER TABLE part_at2tmp1 RENAME TO fail; 194RESET ROLE; 195DROP TABLE part_at2tmp; 196 197-- 198-- check renaming to a table's array type's autogenerated name 199-- (the array type's name should get out of the way) 200-- 201CREATE TABLE attmp_array (id int); 202CREATE TABLE attmp_array2 (id int); 203SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; 204SELECT typname FROM pg_type WHERE oid = 'attmp_array2[]'::regtype; 205ALTER TABLE attmp_array2 RENAME TO _attmp_array; 206SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; 207SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype; 208DROP TABLE _attmp_array; 209DROP TABLE attmp_array; 210 211-- renaming to table's own array type's name is an interesting corner case 212CREATE TABLE attmp_array (id int); 213SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype; 214ALTER TABLE attmp_array RENAME TO _attmp_array; 215SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype; 216DROP TABLE _attmp_array; 217 218-- ALTER TABLE ... RENAME on non-table relations 219-- renaming indexes (FIXME: this should probably test the index's functionality) 220ALTER INDEX IF EXISTS __onek_unique1 RENAME TO attmp_onek_unique1; 221ALTER INDEX IF EXISTS __attmp_onek_unique1 RENAME TO onek_unique1; 222 223ALTER INDEX onek_unique1 RENAME TO attmp_onek_unique1; 224ALTER INDEX attmp_onek_unique1 RENAME TO onek_unique1; 225 226SET ROLE regress_alter_table_user1; 227ALTER INDEX onek_unique1 RENAME TO fail; -- permission denied 228RESET ROLE; 229 230-- renaming views 231CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1; 232ALTER TABLE attmp_view RENAME TO attmp_view_new; 233 234SET ROLE regress_alter_table_user1; 235ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied 236RESET ROLE; 237 238-- hack to ensure we get an indexscan here 239set enable_seqscan to off; 240set enable_bitmapscan to off; 241-- 5 values, sorted 242SELECT unique1 FROM tenk1 WHERE unique1 < 5; 243reset enable_seqscan; 244reset enable_bitmapscan; 245 246DROP VIEW attmp_view_new; 247-- toast-like relation name 248alter table stud_emp rename to pg_toast_stud_emp; 249alter table pg_toast_stud_emp rename to stud_emp; 250 251-- renaming index should rename constraint as well 252ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); 253ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo; 254ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; 255 256-- renaming constraint 257ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0); 258ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo; 259ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo; 260 261-- renaming constraint should rename index as well 262ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); 263DROP INDEX onek_unique1_constraint; -- to see whether it's there 264ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo; 265DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there 266ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; 267 268-- renaming constraints vs. inheritance 269CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int); 270\d constraint_rename_test 271CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test); 272\d constraint_rename_test2 273ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail 274ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail 275ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok 276\d constraint_rename_test 277\d constraint_rename_test2 278ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT; 279ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok 280ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok 281\d constraint_rename_test 282\d constraint_rename_test2 283ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a); 284ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok 285\d constraint_rename_test 286\d constraint_rename_test2 287DROP TABLE constraint_rename_test2; 288DROP TABLE constraint_rename_test; 289ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok 290ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a); 291 292-- renaming constraints with cache reset of target relation 293CREATE TABLE constraint_rename_cache (a int, 294 CONSTRAINT chk_a CHECK (a > 0), 295 PRIMARY KEY (a)); 296ALTER TABLE constraint_rename_cache 297 RENAME CONSTRAINT chk_a TO chk_a_new; 298ALTER TABLE constraint_rename_cache 299 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new; 300CREATE TABLE like_constraint_rename_cache 301 (LIKE constraint_rename_cache INCLUDING ALL); 302\d like_constraint_rename_cache 303DROP TABLE constraint_rename_cache; 304DROP TABLE like_constraint_rename_cache; 305 306-- FOREIGN KEY CONSTRAINT adding TEST 307 308CREATE TABLE attmp2 (a int primary key); 309 310CREATE TABLE attmp3 (a int, b int); 311 312CREATE TABLE attmp4 (a int, b int, unique(a,b)); 313 314CREATE TABLE attmp5 (a int, b int); 315 316-- Insert rows into attmp2 (pktable) 317INSERT INTO attmp2 values (1); 318INSERT INTO attmp2 values (2); 319INSERT INTO attmp2 values (3); 320INSERT INTO attmp2 values (4); 321 322-- Insert rows into attmp3 323INSERT INTO attmp3 values (1,10); 324INSERT INTO attmp3 values (1,20); 325INSERT INTO attmp3 values (5,50); 326 327-- Try (and fail) to add constraint due to invalid source columns 328ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full; 329 330-- Try (and fail) to add constraint due to invalid destination columns explicitly given 331ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full; 332 333-- Try (and fail) to add constraint due to invalid data 334ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full; 335 336-- Delete failing row 337DELETE FROM attmp3 where a=5; 338 339-- Try (and succeed) 340ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full; 341ALTER TABLE attmp3 drop constraint attmpconstr; 342 343INSERT INTO attmp3 values (5,50); 344 345-- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate 346ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID; 347ALTER TABLE attmp3 validate constraint attmpconstr; 348 349-- Delete failing row 350DELETE FROM attmp3 where a=5; 351 352-- Try (and succeed) and repeat to show it works on already valid constraint 353ALTER TABLE attmp3 validate constraint attmpconstr; 354ALTER TABLE attmp3 validate constraint attmpconstr; 355 356-- Try a non-verified CHECK constraint 357ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail 358ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds 359ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails 360DELETE FROM attmp3 WHERE NOT b > 10; 361ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds 362ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds 363 364-- Test inherited NOT VALID CHECK constraints 365select * from attmp3; 366CREATE TABLE attmp6 () INHERITS (attmp3); 367CREATE TABLE attmp7 () INHERITS (attmp3); 368 369INSERT INTO attmp6 VALUES (6, 30), (7, 16); 370ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID; 371ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails 372DELETE FROM attmp6 WHERE b > 20; 373ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds 374 375-- An already validated constraint must not be revalidated 376CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$; 377INSERT INTO attmp7 VALUES (8, 18); 378ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b)); 379ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID; 380ALTER TABLE attmp3 VALIDATE CONSTRAINT identity; 381 382-- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT 383create table parent_noinh_convalid (a int); 384create table child_noinh_convalid () inherits (parent_noinh_convalid); 385insert into parent_noinh_convalid values (1); 386insert into child_noinh_convalid values (1); 387alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid; 388-- fail, because of the row in parent 389alter table parent_noinh_convalid validate constraint check_a_is_2; 390delete from only parent_noinh_convalid; 391-- ok (parent itself contains no violating rows) 392alter table parent_noinh_convalid validate constraint check_a_is_2; 393select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2'; 394-- cleanup 395drop table parent_noinh_convalid, child_noinh_convalid; 396 397-- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on 398-- attmp4 is a,b 399 400ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full; 401 402DROP TABLE attmp7; 403 404DROP TABLE attmp6; 405 406DROP TABLE attmp5; 407 408DROP TABLE attmp4; 409 410DROP TABLE attmp3; 411 412DROP TABLE attmp2; 413 414-- NOT VALID with plan invalidation -- ensure we don't use a constraint for 415-- exclusion until validated 416set constraint_exclusion TO 'partition'; 417create table nv_parent (d date, check (false) no inherit not valid); 418-- not valid constraint added at creation time should automatically become valid 419\d nv_parent 420 421create table nv_child_2010 () inherits (nv_parent); 422create table nv_child_2011 () inherits (nv_parent); 423alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; 424alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid; 425explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31'; 426create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent); 427explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date; 428explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; 429-- after validation, the constraint should be used 430alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check; 431explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; 432 433-- add an inherited NOT VALID constraint 434alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid; 435\d nv_child_2009 436-- we leave nv_parent and children around to help test pg_dump logic 437 438-- Foreign key adding test with mixed types 439 440-- Note: these tables are TEMP to avoid name conflicts when this test 441-- is run in parallel with foreign_key.sql. 442 443CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); 444INSERT INTO PKTABLE VALUES(42); 445CREATE TEMP TABLE FKTABLE (ftest1 inet); 446-- This next should fail, because int=inet does not exist 447ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 448-- This should also fail for the same reason, but here we 449-- give the column name 450ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); 451DROP TABLE FKTABLE; 452-- This should succeed, even though they are different types, 453-- because int=int8 exists and is a member of the integer opfamily 454CREATE TEMP TABLE FKTABLE (ftest1 int8); 455ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 456-- Check it actually works 457INSERT INTO FKTABLE VALUES(42); -- should succeed 458INSERT INTO FKTABLE VALUES(43); -- should fail 459DROP TABLE FKTABLE; 460-- This should fail, because we'd have to cast numeric to int which is 461-- not an implicit coercion (or use numeric=numeric, but that's not part 462-- of the integer opfamily) 463CREATE TEMP TABLE FKTABLE (ftest1 numeric); 464ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 465DROP TABLE FKTABLE; 466DROP TABLE PKTABLE; 467-- On the other hand, this should work because int implicitly promotes to 468-- numeric, and we allow promotion on the FK side 469CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); 470INSERT INTO PKTABLE VALUES(42); 471CREATE TEMP TABLE FKTABLE (ftest1 int); 472ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 473-- Check it actually works 474INSERT INTO FKTABLE VALUES(42); -- should succeed 475INSERT INTO FKTABLE VALUES(43); -- should fail 476DROP TABLE FKTABLE; 477DROP TABLE PKTABLE; 478 479CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, 480 PRIMARY KEY(ptest1, ptest2)); 481-- This should fail, because we just chose really odd types 482CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); 483ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; 484DROP TABLE FKTABLE; 485-- Again, so should this... 486CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); 487ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) 488 references pktable(ptest1, ptest2); 489DROP TABLE FKTABLE; 490-- This fails because we mixed up the column ordering 491CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); 492ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) 493 references pktable(ptest2, ptest1); 494-- As does this... 495ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) 496 references pktable(ptest1, ptest2); 497DROP TABLE FKTABLE; 498DROP TABLE PKTABLE; 499 500-- Test that ALTER CONSTRAINT updates trigger deferrability properly 501 502CREATE TEMP TABLE PKTABLE (ptest1 int primary key); 503CREATE TEMP TABLE FKTABLE (ftest1 int); 504 505ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable 506 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 507ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable 508 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; 509ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable 510 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; 511 512ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable 513 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; 514ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE; 515ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable 516 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 517ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED; 518ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable 519 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 520ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE; 521 522SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred 523FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint 524WHERE tgrelid = 'pktable'::regclass 525ORDER BY 1,2,3; 526SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred 527FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint 528WHERE tgrelid = 'fktable'::regclass 529ORDER BY 1,2,3; 530 531-- temp tables should go away by themselves, need not drop them. 532 533-- test check constraint adding 534 535create table atacc1 ( test int ); 536-- add a check constraint 537alter table atacc1 add constraint atacc_test1 check (test>3); 538-- should fail 539insert into atacc1 (test) values (2); 540-- should succeed 541insert into atacc1 (test) values (4); 542drop table atacc1; 543 544-- let's do one where the check fails when added 545create table atacc1 ( test int ); 546-- insert a soon to be failing row 547insert into atacc1 (test) values (2); 548-- add a check constraint (fails) 549alter table atacc1 add constraint atacc_test1 check (test>3); 550insert into atacc1 (test) values (4); 551drop table atacc1; 552 553-- let's do one where the check fails because the column doesn't exist 554create table atacc1 ( test int ); 555-- add a check constraint (fails) 556alter table atacc1 add constraint atacc_test1 check (test1>3); 557drop table atacc1; 558 559-- something a little more complicated 560create table atacc1 ( test int, test2 int, test3 int); 561-- add a check constraint (fails) 562alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4); 563-- should fail 564insert into atacc1 (test,test2,test3) values (4,4,2); 565-- should succeed 566insert into atacc1 (test,test2,test3) values (4,4,5); 567drop table atacc1; 568 569-- lets do some naming tests 570create table atacc1 (test int check (test>3), test2 int); 571alter table atacc1 add check (test2>test); 572-- should fail for $2 573insert into atacc1 (test2, test) values (3, 4); 574drop table atacc1; 575 576-- inheritance related tests 577create table atacc1 (test int); 578create table atacc2 (test2 int); 579create table atacc3 (test3 int) inherits (atacc1, atacc2); 580alter table atacc2 add constraint foo check (test2>0); 581-- fail and then succeed on atacc2 582insert into atacc2 (test2) values (-3); 583insert into atacc2 (test2) values (3); 584-- fail and then succeed on atacc3 585insert into atacc3 (test2) values (-3); 586insert into atacc3 (test2) values (3); 587drop table atacc3; 588drop table atacc2; 589drop table atacc1; 590 591-- same things with one created with INHERIT 592create table atacc1 (test int); 593create table atacc2 (test2 int); 594create table atacc3 (test3 int) inherits (atacc1, atacc2); 595alter table atacc3 no inherit atacc2; 596-- fail 597alter table atacc3 no inherit atacc2; 598-- make sure it really isn't a child 599insert into atacc3 (test2) values (3); 600select test2 from atacc2; 601-- fail due to missing constraint 602alter table atacc2 add constraint foo check (test2>0); 603alter table atacc3 inherit atacc2; 604-- fail due to missing column 605alter table atacc3 rename test2 to testx; 606alter table atacc3 inherit atacc2; 607-- fail due to mismatched data type 608alter table atacc3 add test2 bool; 609alter table atacc3 inherit atacc2; 610alter table atacc3 drop test2; 611-- succeed 612alter table atacc3 add test2 int; 613update atacc3 set test2 = 4 where test2 is null; 614alter table atacc3 add constraint foo check (test2>0); 615alter table atacc3 inherit atacc2; 616-- fail due to duplicates and circular inheritance 617alter table atacc3 inherit atacc2; 618alter table atacc2 inherit atacc3; 619alter table atacc2 inherit atacc2; 620-- test that we really are a child now (should see 4 not 3 and cascade should go through) 621select test2 from atacc2; 622drop table atacc2 cascade; 623drop table atacc1; 624 625-- adding only to a parent is allowed as of 9.2 626 627create table atacc1 (test int); 628create table atacc2 (test2 int) inherits (atacc1); 629-- ok: 630alter table atacc1 add constraint foo check (test>0) no inherit; 631-- check constraint is not there on child 632insert into atacc2 (test) values (-3); 633-- check constraint is there on parent 634insert into atacc1 (test) values (-3); 635insert into atacc1 (test) values (3); 636-- fail, violating row: 637alter table atacc2 add constraint foo check (test>0) no inherit; 638drop table atacc2; 639drop table atacc1; 640 641-- test unique constraint adding 642 643create table atacc1 ( test int ) ; 644-- add a unique constraint 645alter table atacc1 add constraint atacc_test1 unique (test); 646-- insert first value 647insert into atacc1 (test) values (2); 648-- should fail 649insert into atacc1 (test) values (2); 650-- should succeed 651insert into atacc1 (test) values (4); 652-- try to create duplicates via alter table using - should fail 653alter table atacc1 alter column test type integer using 0; 654drop table atacc1; 655 656-- let's do one where the unique constraint fails when added 657create table atacc1 ( test int ); 658-- insert soon to be failing rows 659insert into atacc1 (test) values (2); 660insert into atacc1 (test) values (2); 661-- add a unique constraint (fails) 662alter table atacc1 add constraint atacc_test1 unique (test); 663insert into atacc1 (test) values (3); 664drop table atacc1; 665 666-- let's do one where the unique constraint fails 667-- because the column doesn't exist 668create table atacc1 ( test int ); 669-- add a unique constraint (fails) 670alter table atacc1 add constraint atacc_test1 unique (test1); 671drop table atacc1; 672 673-- something a little more complicated 674create table atacc1 ( test int, test2 int); 675-- add a unique constraint 676alter table atacc1 add constraint atacc_test1 unique (test, test2); 677-- insert initial value 678insert into atacc1 (test,test2) values (4,4); 679-- should fail 680insert into atacc1 (test,test2) values (4,4); 681-- should all succeed 682insert into atacc1 (test,test2) values (4,5); 683insert into atacc1 (test,test2) values (5,4); 684insert into atacc1 (test,test2) values (5,5); 685drop table atacc1; 686 687-- lets do some naming tests 688create table atacc1 (test int, test2 int, unique(test)); 689alter table atacc1 add unique (test2); 690-- should fail for @@ second one @@ 691insert into atacc1 (test2, test) values (3, 3); 692insert into atacc1 (test2, test) values (2, 3); 693drop table atacc1; 694 695-- test primary key constraint adding 696 697create table atacc1 ( id serial, test int) ; 698-- add a primary key constraint 699alter table atacc1 add constraint atacc_test1 primary key (test); 700-- insert first value 701insert into atacc1 (test) values (2); 702-- should fail 703insert into atacc1 (test) values (2); 704-- should succeed 705insert into atacc1 (test) values (4); 706-- inserting NULL should fail 707insert into atacc1 (test) values(NULL); 708-- try adding a second primary key (should fail) 709alter table atacc1 add constraint atacc_oid1 primary key(id); 710-- drop first primary key constraint 711alter table atacc1 drop constraint atacc_test1 restrict; 712-- try adding a primary key on oid (should succeed) 713alter table atacc1 add constraint atacc_oid1 primary key(id); 714drop table atacc1; 715 716-- let's do one where the primary key constraint fails when added 717create table atacc1 ( test int ); 718-- insert soon to be failing rows 719insert into atacc1 (test) values (2); 720insert into atacc1 (test) values (2); 721-- add a primary key (fails) 722alter table atacc1 add constraint atacc_test1 primary key (test); 723insert into atacc1 (test) values (3); 724drop table atacc1; 725 726-- let's do another one where the primary key constraint fails when added 727create table atacc1 ( test int ); 728-- insert soon to be failing row 729insert into atacc1 (test) values (NULL); 730-- add a primary key (fails) 731alter table atacc1 add constraint atacc_test1 primary key (test); 732insert into atacc1 (test) values (3); 733drop table atacc1; 734 735-- let's do one where the primary key constraint fails 736-- because the column doesn't exist 737create table atacc1 ( test int ); 738-- add a primary key constraint (fails) 739alter table atacc1 add constraint atacc_test1 primary key (test1); 740drop table atacc1; 741 742-- adding a new column as primary key to a non-empty table. 743-- should fail unless the column has a non-null default value. 744create table atacc1 ( test int ); 745insert into atacc1 (test) values (0); 746-- add a primary key column without a default (fails). 747alter table atacc1 add column test2 int primary key; 748-- now add a primary key column with a default (succeeds). 749alter table atacc1 add column test2 int default 0 primary key; 750drop table atacc1; 751 752-- this combination used to have order-of-execution problems (bug #15580) 753create table atacc1 (a int); 754insert into atacc1 values(1); 755alter table atacc1 756 add column b float8 not null default random(), 757 add primary key(a); 758drop table atacc1; 759 760-- something a little more complicated 761create table atacc1 ( test int, test2 int); 762-- add a primary key constraint 763alter table atacc1 add constraint atacc_test1 primary key (test, test2); 764-- try adding a second primary key - should fail 765alter table atacc1 add constraint atacc_test2 primary key (test); 766-- insert initial value 767insert into atacc1 (test,test2) values (4,4); 768-- should fail 769insert into atacc1 (test,test2) values (4,4); 770insert into atacc1 (test,test2) values (NULL,3); 771insert into atacc1 (test,test2) values (3, NULL); 772insert into atacc1 (test,test2) values (NULL,NULL); 773-- should all succeed 774insert into atacc1 (test,test2) values (4,5); 775insert into atacc1 (test,test2) values (5,4); 776insert into atacc1 (test,test2) values (5,5); 777drop table atacc1; 778 779-- lets do some naming tests 780create table atacc1 (test int, test2 int, primary key(test)); 781-- only first should succeed 782insert into atacc1 (test2, test) values (3, 3); 783insert into atacc1 (test2, test) values (2, 3); 784insert into atacc1 (test2, test) values (1, NULL); 785drop table atacc1; 786 787-- alter table / alter column [set/drop] not null tests 788-- try altering system catalogs, should fail 789alter table pg_class alter column relname drop not null; 790alter table pg_class alter relname set not null; 791 792-- try altering non-existent table, should fail 793alter table non_existent alter column bar set not null; 794alter table non_existent alter column bar drop not null; 795 796-- test setting columns to null and not null and vice versa 797-- test checking for null values and primary key 798create table atacc1 (test int not null); 799alter table atacc1 add constraint "atacc1_pkey" primary key (test); 800alter table atacc1 alter column test drop not null; 801alter table atacc1 drop constraint "atacc1_pkey"; 802alter table atacc1 alter column test drop not null; 803insert into atacc1 values (null); 804alter table atacc1 alter test set not null; 805delete from atacc1; 806alter table atacc1 alter test set not null; 807 808-- try altering a non-existent column, should fail 809alter table atacc1 alter bar set not null; 810alter table atacc1 alter bar drop not null; 811 812-- try creating a view and altering that, should fail 813create view myview as select * from atacc1; 814alter table myview alter column test drop not null; 815alter table myview alter column test set not null; 816drop view myview; 817 818drop table atacc1; 819 820-- set not null verified by constraints 821create table atacc1 (test_a int, test_b int); 822insert into atacc1 values (null, 1); 823-- constraint not cover all values, should fail 824alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10); 825alter table atacc1 alter test_a set not null; 826alter table atacc1 drop constraint atacc1_constr_or; 827-- not valid constraint, should fail 828alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid; 829alter table atacc1 alter test_a set not null; 830alter table atacc1 drop constraint atacc1_constr_invalid; 831-- with valid constraint 832update atacc1 set test_a = 1; 833alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null); 834alter table atacc1 alter test_a set not null; 835delete from atacc1; 836 837insert into atacc1 values (2, null); 838alter table atacc1 alter test_a drop not null; 839-- test multiple set not null at same time 840-- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan 841alter table atacc1 alter test_a set not null, alter test_b set not null; 842-- commands order has no importance 843alter table atacc1 alter test_b set not null, alter test_a set not null; 844 845-- valid one by table scan, one by check constraints 846update atacc1 set test_b = 1; 847alter table atacc1 alter test_b set not null, alter test_a set not null; 848 849alter table atacc1 alter test_a drop not null, alter test_b drop not null; 850-- both column has check constraints 851alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null); 852alter table atacc1 alter test_b set not null, alter test_a set not null; 853drop table atacc1; 854 855-- test inheritance 856create table parent (a int); 857create table child (b varchar(255)) inherits (parent); 858 859alter table parent alter a set not null; 860insert into parent values (NULL); 861insert into child (a, b) values (NULL, 'foo'); 862alter table parent alter a drop not null; 863insert into parent values (NULL); 864insert into child (a, b) values (NULL, 'foo'); 865alter table only parent alter a set not null; 866alter table child alter a set not null; 867delete from parent; 868alter table only parent alter a set not null; 869insert into parent values (NULL); 870alter table child alter a set not null; 871insert into child (a, b) values (NULL, 'foo'); 872delete from child; 873alter table child alter a set not null; 874insert into child (a, b) values (NULL, 'foo'); 875drop table child; 876drop table parent; 877 878-- test setting and removing default values 879create table def_test ( 880 c1 int4 default 5, 881 c2 text default 'initial_default' 882); 883insert into def_test default values; 884alter table def_test alter column c1 drop default; 885insert into def_test default values; 886alter table def_test alter column c2 drop default; 887insert into def_test default values; 888alter table def_test alter column c1 set default 10; 889alter table def_test alter column c2 set default 'new_default'; 890insert into def_test default values; 891select * from def_test; 892 893-- set defaults to an incorrect type: this should fail 894alter table def_test alter column c1 set default 'wrong_datatype'; 895alter table def_test alter column c2 set default 20; 896 897-- set defaults on a non-existent column: this should fail 898alter table def_test alter column c3 set default 30; 899 900-- set defaults on views: we need to create a view, add a rule 901-- to allow insertions into it, and then alter the view to add 902-- a default 903create view def_view_test as select * from def_test; 904create rule def_view_test_ins as 905 on insert to def_view_test 906 do instead insert into def_test select new.*; 907insert into def_view_test default values; 908alter table def_view_test alter column c1 set default 45; 909insert into def_view_test default values; 910alter table def_view_test alter column c2 set default 'view_default'; 911insert into def_view_test default values; 912select * from def_view_test; 913 914drop rule def_view_test_ins on def_view_test; 915drop view def_view_test; 916drop table def_test; 917 918-- alter table / drop column tests 919-- try altering system catalogs, should fail 920alter table pg_class drop column relname; 921 922-- try altering non-existent table, should fail 923alter table nosuchtable drop column bar; 924 925-- test dropping columns 926create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); 927insert into atacc1 values (1, 2, 3, 4); 928alter table atacc1 drop a; 929alter table atacc1 drop a; 930 931-- SELECTs 932select * from atacc1; 933select * from atacc1 order by a; 934select * from atacc1 order by "........pg.dropped.1........"; 935select * from atacc1 group by a; 936select * from atacc1 group by "........pg.dropped.1........"; 937select atacc1.* from atacc1; 938select a from atacc1; 939select atacc1.a from atacc1; 940select b,c,d from atacc1; 941select a,b,c,d from atacc1; 942select * from atacc1 where a = 1; 943select "........pg.dropped.1........" from atacc1; 944select atacc1."........pg.dropped.1........" from atacc1; 945select "........pg.dropped.1........",b,c,d from atacc1; 946select * from atacc1 where "........pg.dropped.1........" = 1; 947 948-- UPDATEs 949update atacc1 set a = 3; 950update atacc1 set b = 2 where a = 3; 951update atacc1 set "........pg.dropped.1........" = 3; 952update atacc1 set b = 2 where "........pg.dropped.1........" = 3; 953 954-- INSERTs 955insert into atacc1 values (10, 11, 12, 13); 956insert into atacc1 values (default, 11, 12, 13); 957insert into atacc1 values (11, 12, 13); 958insert into atacc1 (a) values (10); 959insert into atacc1 (a) values (default); 960insert into atacc1 (a,b,c,d) values (10,11,12,13); 961insert into atacc1 (a,b,c,d) values (default,11,12,13); 962insert into atacc1 (b,c,d) values (11,12,13); 963insert into atacc1 ("........pg.dropped.1........") values (10); 964insert into atacc1 ("........pg.dropped.1........") values (default); 965insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13); 966insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13); 967 968-- DELETEs 969delete from atacc1 where a = 3; 970delete from atacc1 where "........pg.dropped.1........" = 3; 971delete from atacc1; 972 973-- try dropping a non-existent column, should fail 974alter table atacc1 drop bar; 975 976-- try removing an oid column, should succeed (as it's nonexistant) 977alter table atacc1 SET WITHOUT OIDS; 978 979-- try adding an oid column, should fail (not supported) 980alter table atacc1 SET WITH OIDS; 981 982-- try dropping the xmin column, should fail 983alter table atacc1 drop xmin; 984 985-- try creating a view and altering that, should fail 986create view myview as select * from atacc1; 987select * from myview; 988alter table myview drop d; 989drop view myview; 990 991-- test some commands to make sure they fail on the dropped column 992analyze atacc1(a); 993analyze atacc1("........pg.dropped.1........"); 994vacuum analyze atacc1(a); 995vacuum analyze atacc1("........pg.dropped.1........"); 996comment on column atacc1.a is 'testing'; 997comment on column atacc1."........pg.dropped.1........" is 'testing'; 998alter table atacc1 alter a set storage plain; 999alter table atacc1 alter "........pg.dropped.1........" set storage plain; 1000alter table atacc1 alter a set statistics 0; 1001alter table atacc1 alter "........pg.dropped.1........" set statistics 0; 1002alter table atacc1 alter a set default 3; 1003alter table atacc1 alter "........pg.dropped.1........" set default 3; 1004alter table atacc1 alter a drop default; 1005alter table atacc1 alter "........pg.dropped.1........" drop default; 1006alter table atacc1 alter a set not null; 1007alter table atacc1 alter "........pg.dropped.1........" set not null; 1008alter table atacc1 alter a drop not null; 1009alter table atacc1 alter "........pg.dropped.1........" drop not null; 1010alter table atacc1 rename a to x; 1011alter table atacc1 rename "........pg.dropped.1........" to x; 1012alter table atacc1 add primary key(a); 1013alter table atacc1 add primary key("........pg.dropped.1........"); 1014alter table atacc1 add unique(a); 1015alter table atacc1 add unique("........pg.dropped.1........"); 1016alter table atacc1 add check (a > 3); 1017alter table atacc1 add check ("........pg.dropped.1........" > 3); 1018create table atacc2 (id int4 unique); 1019alter table atacc1 add foreign key (a) references atacc2(id); 1020alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); 1021alter table atacc2 add foreign key (id) references atacc1(a); 1022alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........"); 1023drop table atacc2; 1024create index "testing_idx" on atacc1(a); 1025create index "testing_idx" on atacc1("........pg.dropped.1........"); 1026 1027-- test create as and select into 1028insert into atacc1 values (21, 22, 23); 1029create table attest1 as select * from atacc1; 1030select * from attest1; 1031drop table attest1; 1032select * into attest2 from atacc1; 1033select * from attest2; 1034drop table attest2; 1035 1036-- try dropping all columns 1037alter table atacc1 drop c; 1038alter table atacc1 drop d; 1039alter table atacc1 drop b; 1040select * from atacc1; 1041 1042drop table atacc1; 1043 1044-- test constraint error reporting in presence of dropped columns 1045create table atacc1 (id serial primary key, value int check (value < 10)); 1046insert into atacc1(value) values (100); 1047alter table atacc1 drop column value; 1048alter table atacc1 add column value int check (value < 10); 1049insert into atacc1(value) values (100); 1050insert into atacc1(id, value) values (null, 0); 1051drop table atacc1; 1052 1053-- test inheritance 1054create table parent (a int, b int, c int); 1055insert into parent values (1, 2, 3); 1056alter table parent drop a; 1057create table child (d varchar(255)) inherits (parent); 1058insert into child values (12, 13, 'testing'); 1059 1060select * from parent; 1061select * from child; 1062alter table parent drop c; 1063select * from parent; 1064select * from child; 1065 1066drop table child; 1067drop table parent; 1068 1069-- check error cases for inheritance column merging 1070create table parent (a float8, b numeric(10,4), c text collate "C"); 1071 1072create table child (a float4) inherits (parent); -- fail 1073create table child (b decimal(10,7)) inherits (parent); -- fail 1074create table child (c text collate "POSIX") inherits (parent); -- fail 1075create table child (a double precision, b decimal(10,4)) inherits (parent); 1076 1077drop table child; 1078drop table parent; 1079 1080-- test copy in/out 1081create table attest (a int4, b int4, c int4); 1082insert into attest values (1,2,3); 1083alter table attest drop a; 1084select * from attest; 1085select * from attest; 1086select * from attest; 1087drop table attest; 1088 1089-- test inheritance 1090 1091create table dropColumn (a int, b int, e int); 1092create table dropColumnChild (c int) inherits (dropColumn); 1093create table dropColumnAnother (d int) inherits (dropColumnChild); 1094 1095-- these two should fail 1096alter table dropColumnchild drop column a; 1097alter table only dropColumnChild drop column b; 1098 1099 1100 1101-- these three should work 1102alter table only dropColumn drop column e; 1103alter table dropColumnChild drop column c; 1104alter table dropColumn drop column a; 1105 1106create table renameColumn (a int); 1107create table renameColumnChild (b int) inherits (renameColumn); 1108create table renameColumnAnother (c int) inherits (renameColumnChild); 1109 1110-- these three should fail 1111alter table renameColumnChild rename column a to d; 1112alter table only renameColumnChild rename column a to d; 1113alter table only renameColumn rename column a to d; 1114 1115-- these should work 1116alter table renameColumn rename column a to d; 1117alter table renameColumnChild rename column b to a; 1118 1119-- these should work 1120alter table if exists doesnt_exist_tab rename column a to d; 1121alter table if exists doesnt_exist_tab rename column b to a; 1122 1123-- this should work 1124alter table renameColumn add column w int; 1125 1126-- this should fail 1127alter table only renameColumn add column x int; 1128 1129 1130-- Test corner cases in dropping of inherited columns 1131 1132create table p1 (f1 int, f2 int); 1133create table c1 (f1 int not null) inherits(p1); 1134 1135-- should be rejected since c1.f1 is inherited 1136alter table c1 drop column f1; 1137-- should work 1138alter table p1 drop column f1; 1139-- c1.f1 is still there, but no longer inherited 1140select f1 from c1; 1141alter table c1 drop column f1; 1142select f1 from c1; 1143 1144drop table p1 cascade; 1145 1146create table p1 (f1 int, f2 int); 1147create table c1 () inherits(p1); 1148 1149-- should be rejected since c1.f1 is inherited 1150alter table c1 drop column f1; 1151alter table p1 drop column f1; 1152-- c1.f1 is dropped now, since there is no local definition for it 1153select f1 from c1; 1154 1155drop table p1 cascade; 1156 1157create table p1 (f1 int, f2 int); 1158create table c1 () inherits(p1); 1159 1160-- should be rejected since c1.f1 is inherited 1161alter table c1 drop column f1; 1162alter table only p1 drop column f1; 1163-- c1.f1 is NOT dropped, but must now be considered non-inherited 1164alter table c1 drop column f1; 1165 1166drop table p1 cascade; 1167 1168create table p1 (f1 int, f2 int); 1169create table c1 (f1 int not null) inherits(p1); 1170 1171-- should be rejected since c1.f1 is inherited 1172alter table c1 drop column f1; 1173alter table only p1 drop column f1; 1174-- c1.f1 is still there, but no longer inherited 1175alter table c1 drop column f1; 1176 1177drop table p1 cascade; 1178 1179create table p1(id int, name text); 1180create table p2(id2 int, name text, height int); 1181create table c1(age int) inherits(p1,p2); 1182create table gc1() inherits (c1); 1183 1184select relname, attname, attinhcount, attislocal 1185from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) 1186where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped 1187order by relname, attnum; 1188 1189-- should work 1190alter table only p1 drop column name; 1191-- should work. Now c1.name is local and inhcount is 0. 1192alter table p2 drop column name; 1193-- should be rejected since its inherited 1194alter table gc1 drop column name; 1195-- should work, and drop gc1.name along 1196alter table c1 drop column name; 1197-- should fail: column does not exist 1198alter table gc1 drop column name; 1199-- should work and drop the attribute in all tables 1200alter table p2 drop column height; 1201 1202-- IF EXISTS test 1203create table dropColumnExists (); 1204alter table dropColumnExists drop column non_existing; --fail 1205alter table dropColumnExists drop column if exists non_existing; --succeed 1206 1207select relname, attname, attinhcount, attislocal 1208from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) 1209where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped 1210order by relname, attnum; 1211 1212drop table p1, p2 cascade; 1213 1214-- test attinhcount tracking with merged columns 1215 1216create table depth0(); 1217create table depth1(c text) inherits (depth0); 1218create table depth2() inherits (depth1); 1219alter table depth0 add c text; 1220 1221select attrelid::regclass, attname, attinhcount, attislocal 1222from pg_attribute 1223where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2') 1224order by attrelid::regclass::text, attnum; 1225 1226-- test renumbering of child-table columns in inherited operations 1227 1228create table p1 (f1 int); 1229create table c1 (f2 text, f3 int) inherits (p1); 1230 1231alter table p1 add column a1 int check (a1 > 0); 1232alter table p1 add column f2 text; 1233 1234insert into p1 values (1,2,'abc'); 1235insert into c1 values(11,'xyz',33,0); -- should fail 1236insert into c1 values(11,'xyz',33,22); 1237 1238select * from p1; 1239update p1 set a1 = a1 + 1, f2 = upper(f2); 1240select * from p1; 1241 1242drop table p1 cascade; 1243 1244-- test that operations with a dropped column do not try to reference 1245-- its datatype 1246 1247create domain mytype as text; 1248create temp table foo (f1 text, f2 mytype, f3 text); 1249 1250insert into foo values('bb','cc','dd'); 1251select * from foo; 1252 1253drop domain mytype cascade; 1254 1255select * from foo; 1256insert into foo values('qq','rr'); 1257select * from foo; 1258update foo set f3 = 'zz'; 1259select * from foo; 1260select f3,max(f1) from foo group by f3; 1261 1262-- Simple tests for alter table column type 1263alter table foo alter f1 TYPE integer; -- fails 1264alter table foo alter f1 TYPE varchar(10); 1265 1266create table anothertab (atcol1 serial8, atcol2 boolean, 1267 constraint anothertab_chk check (atcol1 <= 3)); 1268 1269insert into anothertab (atcol1, atcol2) values (default, true); 1270insert into anothertab (atcol1, atcol2) values (default, false); 1271select * from anothertab; 1272 1273alter table anothertab alter column atcol1 type boolean; -- fails 1274alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails 1275alter table anothertab alter column atcol1 type integer; 1276 1277select * from anothertab; 1278 1279insert into anothertab (atcol1, atcol2) values (45, null); -- fails 1280insert into anothertab (atcol1, atcol2) values (default, null); 1281 1282select * from anothertab; 1283 1284alter table anothertab alter column atcol2 type text 1285 using case when atcol2 is true then 'IT WAS TRUE' 1286 when atcol2 is false then 'IT WAS FALSE' 1287 else 'IT WAS NULL!' end; 1288 1289select * from anothertab; 1290alter table anothertab alter column atcol1 type boolean 1291 using case when atcol1 % 2 = 0 then true else false end; -- fails 1292alter table anothertab alter column atcol1 drop default; 1293alter table anothertab alter column atcol1 type boolean 1294 using case when atcol1 % 2 = 0 then true else false end; -- fails 1295alter table anothertab drop constraint anothertab_chk; 1296alter table anothertab drop constraint anothertab_chk; -- fails 1297alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds 1298 1299alter table anothertab alter column atcol1 type boolean 1300 using case when atcol1 % 2 = 0 then true else false end; 1301 1302select * from anothertab; 1303 1304drop table anothertab; 1305 1306create table another (f1 int, f2 text); 1307 1308insert into another values(1, 'one'); 1309insert into another values(2, 'two'); 1310insert into another values(3, 'three'); 1311 1312select * from another; 1313 1314alter table another 1315 alter f1 type text using f2 || ' more', 1316 alter f2 type bigint using f1 * 10; 1317 1318select * from another; 1319 1320drop table another; 1321 1322-- table's row type 1323create table tab1 (a int, b text); 1324create table tab2 (x int, y tab1); 1325alter table tab1 alter column b type varchar; -- fails 1326 1327-- Alter column type that's part of a partitioned index 1328create table at_partitioned (a int, b text) partition by range (a); 1329create table at_part_1 partition of at_partitioned for values from (0) to (1000); 1330insert into at_partitioned values (512, '0.123'); 1331create table at_part_2 (b text, a int); 1332insert into at_part_2 values ('1.234', 1024); 1333create index on at_partitioned (b); 1334create index on at_partitioned (a); 1335\d at_part_1 1336\d at_part_2 1337alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000); 1338\d at_part_2 1339alter table at_partitioned alter column b type numeric using b::numeric; 1340\d at_part_1 1341\d at_part_2 1342drop table at_partitioned; 1343 1344-- Alter column type when no table rewrite is required 1345-- Also check that comments are preserved 1346create table at_partitioned(id int, name varchar(64), unique (id, name)) 1347 partition by hash(id); 1348comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint'; 1349comment on index at_partitioned_id_name_key is 'parent index'; 1350create table at_partitioned_0 partition of at_partitioned 1351 for values with (modulus 2, remainder 0); 1352comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint'; 1353comment on index at_partitioned_0_id_name_key is 'child 0 index'; 1354create table at_partitioned_1 partition of at_partitioned 1355 for values with (modulus 2, remainder 1); 1356comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint'; 1357comment on index at_partitioned_1_id_name_key is 'child 1 index'; 1358insert into at_partitioned values(1, 'foo'); 1359insert into at_partitioned values(3, 'bar'); 1360 1361create temp table old_oids as 1362 select relname, oid as oldoid, relfilenode as oldfilenode 1363 from pg_class where relname like 'at_partitioned%'; 1364 1365select relname, 1366 c.oid = oldoid as orig_oid, 1367 case relfilenode 1368 when 0 then 'none' 1369 when c.oid then 'own' 1370 when oldfilenode then 'orig' 1371 else 'OTHER' 1372 end as storage, 1373 obj_description(c.oid, 'pg_class') as desc 1374 from pg_class c left join old_oids using (relname) 1375 where relname like 'at_partitioned%' 1376 order by relname; 1377 1378select conname, obj_description(oid, 'pg_constraint') as desc 1379 from pg_constraint where conname like 'at_partitioned%' 1380 order by conname; 1381 1382alter table at_partitioned alter column name type varchar(127); 1383 1384-- Note: these tests currently show the wrong behavior for comments :-( 1385 1386select relname, 1387 c.oid = oldoid as orig_oid, 1388 case relfilenode 1389 when 0 then 'none' 1390 when c.oid then 'own' 1391 when oldfilenode then 'orig' 1392 else 'OTHER' 1393 end as storage, 1394 obj_description(c.oid, 'pg_class') as desc 1395 from pg_class c left join old_oids using (relname) 1396 where relname like 'at_partitioned%' 1397 order by relname; 1398 1399select conname, obj_description(oid, 'pg_constraint') as desc 1400 from pg_constraint where conname like 'at_partitioned%' 1401 order by conname; 1402 1403-- Don't remove this DROP, it exposes bug #15672 1404drop table at_partitioned; 1405 1406-- disallow recursive containment of row types 1407create temp table recur1 (f1 int); 1408alter table recur1 add column f2 recur1; -- fails 1409alter table recur1 add column f2 recur1[]; -- fails 1410create domain array_of_recur1 as recur1[]; 1411alter table recur1 add column f2 array_of_recur1; -- fails 1412create temp table recur2 (f1 int, f2 recur1); 1413alter table recur1 add column f2 recur2; -- fails 1414alter table recur1 add column f2 int; 1415alter table recur1 alter column f2 type recur2; -- fails 1416 1417-- SET STORAGE may need to add a TOAST table 1418create table test_storage (a text); 1419alter table test_storage alter a set storage plain; 1420alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table 1421alter table test_storage alter a set storage extended; -- re-add TOAST table 1422 1423select reltoastrelid <> 0 as has_toast_table 1424from pg_class 1425where oid = 'test_storage'::regclass; 1426 1427-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779) 1428CREATE TABLE test_inh_check (a float check (a > 10.2), b float); 1429CREATE TABLE test_inh_check_child() INHERITS(test_inh_check); 1430\d test_inh_check 1431\d test_inh_check_child 1432select relname, conname, coninhcount, conislocal, connoinherit 1433 from pg_constraint c, pg_class r 1434 where relname like 'test_inh_check%' and c.conrelid = r.oid 1435 order by 1, 2; 1436ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric; 1437\d test_inh_check 1438\d test_inh_check_child 1439select relname, conname, coninhcount, conislocal, connoinherit 1440 from pg_constraint c, pg_class r 1441 where relname like 'test_inh_check%' and c.conrelid = r.oid 1442 order by 1, 2; 1443-- also try noinherit, local, and local+inherited cases 1444ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT; 1445ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000); 1446ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1); 1447ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1); 1448\d test_inh_check 1449\d test_inh_check_child 1450select relname, conname, coninhcount, conislocal, connoinherit 1451 from pg_constraint c, pg_class r 1452 where relname like 'test_inh_check%' and c.conrelid = r.oid 1453 order by 1, 2; 1454ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric; 1455\d test_inh_check 1456\d test_inh_check_child 1457select relname, conname, coninhcount, conislocal, connoinherit 1458 from pg_constraint c, pg_class r 1459 where relname like 'test_inh_check%' and c.conrelid = r.oid 1460 order by 1, 2; 1461 1462-- ALTER COLUMN TYPE with different schema in children 1463-- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com 1464CREATE TABLE test_type_diff (f1 int); 1465CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff); 1466ALTER TABLE test_type_diff ADD COLUMN f2 int; 1467INSERT INTO test_type_diff_c VALUES (1, 2, 3); 1468ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint; 1469 1470CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8); 1471CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2); 1472CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4); 1473CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8); 1474ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2; 1475ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2; 1476ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2; 1477INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3); 1478INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6); 1479INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9); 1480ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8; 1481-- whole-row references are disallowed 1482ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2)); 1483 1484-- check for rollback of ANALYZE corrupting table property flags (bug #11638) 1485CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text); 1486CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text); 1487BEGIN; 1488ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey; 1489ANALYZE check_fk_presence_2; 1490ROLLBACK; 1491\d check_fk_presence_2 1492DROP TABLE check_fk_presence_1, check_fk_presence_2; 1493 1494-- check column addition within a view (bug #14876) 1495create table at_base_table(id int, stuff text); 1496insert into at_base_table values (23, 'skidoo'); 1497create view at_view_1 as select * from at_base_table bt; 1498create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1; 1499\d+ at_view_1 1500\d+ at_view_2 1501explain (verbose, costs off) select * from at_view_2; 1502select * from at_view_2; 1503 1504create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt; 1505\d+ at_view_1 1506\d+ at_view_2 1507explain (verbose, costs off) select * from at_view_2; 1508select * from at_view_2; 1509 1510drop view at_view_2; 1511drop view at_view_1; 1512drop table at_base_table; 1513 1514-- 1515-- lock levels 1516-- 1517drop type lockmodes; 1518create type lockmodes as enum ( 1519 'SIReadLock' 1520,'AccessShareLock' 1521,'RowShareLock' 1522,'RowExclusiveLock' 1523,'ShareUpdateExclusiveLock' 1524,'ShareLock' 1525,'ShareRowExclusiveLock' 1526,'ExclusiveLock' 1527,'AccessExclusiveLock' 1528); 1529 1530drop view my_locks; 1531create or replace view my_locks as 1532select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode 1533from pg_locks l join pg_class c on l.relation = c.oid 1534where virtualtransaction = ( 1535 select virtualtransaction 1536 from pg_locks 1537 where transactionid = txid_current()::integer) 1538and locktype = 'relation' 1539and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') 1540and c.relname != 'my_locks' 1541group by c.relname; 1542 1543create table alterlock (f1 int primary key, f2 text); 1544insert into alterlock values (1, 'foo'); 1545create table alterlock2 (f3 int primary key, f1 int); 1546insert into alterlock2 values (1, 1); 1547 1548begin; alter table alterlock alter column f2 set statistics 150; 1549select * from my_locks order by 1; 1550rollback; 1551 1552begin; alter table alterlock cluster on alterlock_pkey; 1553select * from my_locks order by 1; 1554commit; 1555 1556begin; alter table alterlock set without cluster; 1557select * from my_locks order by 1; 1558commit; 1559 1560begin; alter table alterlock set (fillfactor = 100); 1561select * from my_locks order by 1; 1562commit; 1563 1564begin; alter table alterlock reset (fillfactor); 1565select * from my_locks order by 1; 1566commit; 1567 1568begin; alter table alterlock set (toast.autovacuum_enabled = off); 1569select * from my_locks order by 1; 1570commit; 1571 1572begin; alter table alterlock set (autovacuum_enabled = off); 1573select * from my_locks order by 1; 1574commit; 1575 1576begin; alter table alterlock alter column f2 set (n_distinct = 1); 1577select * from my_locks order by 1; 1578rollback; 1579 1580-- test that mixing options with different lock levels works as expected 1581begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80); 1582select * from my_locks order by 1; 1583commit; 1584 1585begin; alter table alterlock alter column f2 set storage extended; 1586select * from my_locks order by 1; 1587rollback; 1588 1589begin; alter table alterlock alter column f2 set default 'x'; 1590select * from my_locks order by 1; 1591rollback; 1592 1593begin; 1594create trigger ttdummy 1595 before delete or update on alterlock 1596 for each row 1597 execute procedure 1598 ttdummy (1, 1); 1599select * from my_locks order by 1; 1600rollback; 1601 1602begin; 1603select * from my_locks order by 1; 1604alter table alterlock2 add foreign key (f1) references alterlock (f1); 1605select * from my_locks order by 1; 1606rollback; 1607 1608begin; 1609alter table alterlock2 1610add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID; 1611select * from my_locks order by 1; 1612commit; 1613begin; 1614alter table alterlock2 validate constraint alterlock2nv; 1615select * from my_locks order by 1; 1616rollback; 1617 1618create or replace view my_locks as 1619select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode 1620from pg_locks l join pg_class c on l.relation = c.oid 1621where virtualtransaction = ( 1622 select virtualtransaction 1623 from pg_locks 1624 where transactionid = txid_current()::integer) 1625and locktype = 'relation' 1626and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') 1627and c.relname = 'my_locks' 1628group by c.relname; 1629 1630-- raise exception 1631alter table my_locks set (autovacuum_enabled = false); 1632alter view my_locks set (autovacuum_enabled = false); 1633alter table my_locks reset (autovacuum_enabled); 1634alter view my_locks reset (autovacuum_enabled); 1635 1636begin; 1637alter view my_locks set (security_barrier=off); 1638select * from my_locks order by 1; 1639alter view my_locks reset (security_barrier); 1640rollback; 1641 1642-- this test intentionally applies the ALTER TABLE command against a view, but 1643-- uses a view option so we expect this to succeed. This form of SQL is 1644-- accepted for historical reasons, as shown in the docs for ALTER VIEW 1645begin; 1646alter table my_locks set (security_barrier=off); 1647select * from my_locks order by 1; 1648alter table my_locks reset (security_barrier); 1649rollback; 1650 1651-- cleanup 1652drop table alterlock2; 1653drop table alterlock; 1654drop view my_locks; 1655drop type lockmodes; 1656 1657-- 1658-- alter function 1659-- 1660create function test_strict(text) returns text as 1661 'select coalesce($1, ''got passed a null'');' 1662 language sql returns null on null input; 1663select test_strict(NULL); 1664alter function test_strict(text) called on null input; 1665select test_strict(NULL); 1666 1667create function non_strict(text) returns text as 1668 'select coalesce($1, ''got passed a null'');' 1669 language sql called on null input; 1670select non_strict(NULL); 1671alter function non_strict(text) returns null on null input; 1672select non_strict(NULL); 1673 1674-- 1675-- alter object set schema 1676-- 1677 1678create schema alter1; 1679create schema alter2; 1680 1681create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); 1682 1683create view alter1.v1 as select * from alter1.t1; 1684 1685create function alter1.plus1(int) returns int as 'select $1+1' language sql; 1686 1687create domain alter1.posint integer check (value > 0); 1688 1689create type alter1.ctype as (f1 int, f2 text); 1690 1691create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql 1692as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2'; 1693 1694create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype); 1695 1696create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as 1697 operator 1 alter1.=(alter1.ctype, alter1.ctype); 1698 1699create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8; 1700 1701create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 1702create text search configuration alter1.cfg(parser = alter1.prs); 1703create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize); 1704create text search dictionary alter1.dict(template = alter1.tmpl); 1705 1706insert into alter1.t1(f2) values(11); 1707insert into alter1.t1(f2) values(12); 1708 1709alter table alter1.t1 set schema alter1; -- no-op, same schema 1710alter table alter1.t1 set schema alter2; 1711alter table alter1.v1 set schema alter2; 1712alter function alter1.plus1(int) set schema alter2; 1713alter domain alter1.posint set schema alter2; 1714alter operator class alter1.ctype_hash_ops using hash set schema alter2; 1715alter operator family alter1.ctype_hash_ops using hash set schema alter2; 1716alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2; 1717alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2; 1718alter type alter1.ctype set schema alter1; -- no-op, same schema 1719alter type alter1.ctype set schema alter2; 1720alter conversion alter1.ascii_to_utf8 set schema alter2; 1721alter text search parser alter1.prs set schema alter2; 1722alter text search configuration alter1.cfg set schema alter2; 1723alter text search template alter1.tmpl set schema alter2; 1724alter text search dictionary alter1.dict set schema alter2; 1725 1726-- this should succeed because nothing is left in alter1 1727drop schema alter1; 1728 1729insert into alter2.t1(f2) values(13); 1730insert into alter2.t1(f2) values(14); 1731 1732select * from alter2.t1; 1733 1734select * from alter2.v1; 1735 1736select alter2.plus1(41); 1737 1738-- clean up 1739drop schema alter2 cascade; 1740 1741-- 1742-- composite types 1743-- 1744 1745CREATE TYPE test_type AS (a int); 1746\d test_type 1747 1748ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails 1749 1750ALTER TYPE test_type ADD ATTRIBUTE b text; 1751\d test_type 1752 1753ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails 1754 1755ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar; 1756\d test_type 1757 1758ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer; 1759\d test_type 1760 1761ALTER TYPE test_type DROP ATTRIBUTE b; 1762\d test_type 1763 1764ALTER TYPE test_type DROP ATTRIBUTE c; -- fails 1765 1766ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c; 1767 1768ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean; 1769\d test_type 1770 1771ALTER TYPE test_type RENAME ATTRIBUTE a TO aa; 1772ALTER TYPE test_type RENAME ATTRIBUTE d TO dd; 1773\d test_type 1774 1775DROP TYPE test_type; 1776 1777CREATE TYPE test_type1 AS (a int, b text); 1778CREATE TABLE test_tbl1 (x int, y test_type1); 1779ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails 1780 1781CREATE TYPE test_type2 AS (a int, b text); 1782CREATE TABLE test_tbl2 OF test_type2; 1783CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); 1784\d test_type2 1785\d test_tbl2 1786 1787ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails 1788ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE; 1789\d test_type2 1790\d test_tbl2 1791 1792ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails 1793ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE; 1794\d test_type2 1795\d test_tbl2 1796 1797ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails 1798ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE; 1799\d test_type2 1800\d test_tbl2 1801 1802ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails 1803ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE; 1804\d test_type2 1805\d test_tbl2 1806\d test_tbl2_subclass 1807 1808DROP TABLE test_tbl2_subclass; 1809 1810CREATE TYPE test_typex AS (a int, b text); 1811CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0)); 1812ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails 1813ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE; 1814\d test_tblx 1815DROP TABLE test_tblx; 1816DROP TYPE test_typex; 1817 1818-- This test isn't that interesting on its own, but the purpose is to leave 1819-- behind a table to test pg_upgrade with. The table has a composite type 1820-- column in it, and the composite type has a dropped attribute. 1821CREATE TYPE test_type3 AS (a int); 1822CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3; 1823ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int; 1824 1825CREATE TYPE test_type_empty AS (); 1826DROP TYPE test_type_empty; 1827 1828-- 1829-- typed tables: OF / NOT OF 1830-- 1831 1832CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2)); 1833ALTER TYPE tt_t0 DROP ATTRIBUTE z; 1834CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK 1835CREATE TABLE tt1 (x int, y bigint); -- wrong base type 1836CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod 1837CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order 1838CREATE TABLE tt4 (x int); -- too few columns 1839CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns 1840CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent 1841CREATE TABLE tt7 (x int, q text, y numeric(8,2)); 1842ALTER TABLE tt7 DROP q; -- OK 1843 1844ALTER TABLE tt0 OF tt_t0; 1845ALTER TABLE tt1 OF tt_t0; 1846ALTER TABLE tt2 OF tt_t0; 1847ALTER TABLE tt3 OF tt_t0; 1848ALTER TABLE tt4 OF tt_t0; 1849ALTER TABLE tt5 OF tt_t0; 1850ALTER TABLE tt6 OF tt_t0; 1851ALTER TABLE tt7 OF tt_t0; 1852 1853CREATE TYPE tt_t1 AS (x int, y numeric(8,2)); 1854ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table 1855ALTER TABLE tt7 NOT OF; 1856\d tt7 1857 1858-- make sure we can drop a constraint on the parent but it remains on the child 1859CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL)); 1860CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent); 1861ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check"; 1862-- should fail 1863INSERT INTO test_drop_constr_child (c) VALUES (NULL); 1864DROP TABLE test_drop_constr_parent CASCADE; 1865 1866-- 1867-- IF EXISTS test 1868-- 1869ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; 1870ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); 1871ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); 1872ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; 1873ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; 1874ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; 1875 1876CREATE TABLE tt8(a int); 1877CREATE SCHEMA alter2; 1878 1879ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; 1880ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); 1881ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); 1882ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; 1883ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; 1884ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; 1885 1886\d alter2.tt8 1887 1888DROP TABLE alter2.tt8; 1889DROP SCHEMA alter2; 1890 1891-- 1892-- Check conflicts between index and CHECK constraint names 1893-- 1894CREATE TABLE tt9(c integer); 1895ALTER TABLE tt9 ADD CHECK(c > 1); 1896ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name 1897ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3); 1898ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name 1899ALTER TABLE tt9 ADD UNIQUE(c); 1900ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name 1901ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name 1902ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name 1903ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name 1904ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6); 1905ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name 1906\d tt9 1907DROP TABLE tt9; 1908 1909 1910-- Check that comments on constraints and indexes are not lost at ALTER TABLE. 1911CREATE TABLE comment_test ( 1912 id int, 1913 positive_col int CHECK (positive_col > 0), 1914 indexed_col int, 1915 CONSTRAINT comment_test_pk PRIMARY KEY (id)); 1916CREATE INDEX comment_test_index ON comment_test(indexed_col); 1917 1918COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test'; 1919COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test'; 1920COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col'; 1921COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test'; 1922COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test'; 1923 1924SELECT col_description('comment_test'::regclass, 1) as comment; 1925SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; 1926SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; 1927 1928-- Change the datatype of all the columns. ALTER TABLE is optimized to not 1929-- rebuild an index if the new data type is binary compatible with the old 1930-- one. Check do a dummy ALTER TABLE that doesn't change the datatype 1931-- first, to test that no-op codepath, and another one that does. 1932ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int; 1933ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text; 1934ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int; 1935ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; 1936ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; 1937ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; 1938 1939-- Check that the comments are intact. 1940SELECT col_description('comment_test'::regclass, 1) as comment; 1941SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; 1942SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; 1943 1944-- Check compatibility for foreign keys and comments. This is done 1945-- separately as rebuilding the column type of the parent leads 1946-- to an error and would reduce the test scope. 1947CREATE TABLE comment_test_child ( 1948 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test); 1949CREATE INDEX comment_test_child_fk ON comment_test_child(id); 1950COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child'; 1951COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child'; 1952COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child'; 1953 1954-- Change column type of parent 1955ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; 1956ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer; 1957 1958-- Comments should be intact 1959SELECT col_description('comment_test_child'::regclass, 1) as comment; 1960SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test_child'::regclass ORDER BY 1, 2; 1961SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2; 1962 1963-- Check that we map relation oids to filenodes and back correctly. Only 1964-- display bad mappings so the test output doesn't change all the time. A 1965-- filenode function call can return NULL for a relation dropped concurrently 1966-- with the call's surrounding query, so ignore a NULL mapped_oid for 1967-- relations that no longer exist after all calls finish. 1968CREATE TEMP TABLE filenode_mapping AS 1969SELECT 1970 oid, mapped_oid, reltablespace, relfilenode, relname 1971FROM pg_class, 1972 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid 1973WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid; 1974 1975SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid 1976WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL; 1977 1978-- Checks on creating and manipulation of user defined relations in 1979-- pg_catalog. 1980-- 1981-- XXX: It would be useful to add checks around trying to manipulate 1982-- catalog tables, but that might have ugly consequences when run 1983-- against an existing server with allow_system_table_mods = on. 1984 1985SHOW allow_system_table_mods; 1986-- disallowed because of search_path issues with pg_dump 1987CREATE TABLE pg_catalog.new_system_table(); 1988-- instead create in public first, move to catalog 1989CREATE TABLE new_system_table(id serial primary key, othercol text); 1990ALTER TABLE new_system_table SET SCHEMA pg_catalog; 1991ALTER TABLE new_system_table SET SCHEMA public; 1992ALTER TABLE new_system_table SET SCHEMA pg_catalog; 1993-- will be ignored -- already there: 1994ALTER TABLE new_system_table SET SCHEMA pg_catalog; 1995ALTER TABLE new_system_table RENAME TO old_system_table; 1996CREATE INDEX old_system_table__othercol ON old_system_table (othercol); 1997INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata'); 1998UPDATE old_system_table SET id = -id; 1999DELETE FROM old_system_table WHERE othercol = 'somedata'; 2000TRUNCATE old_system_table; 2001ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey; 2002ALTER TABLE old_system_table DROP COLUMN othercol; 2003DROP TABLE old_system_table; 2004 2005-- set logged 2006CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT); 2007-- check relpersistence of an unlogged table 2008SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' 2009UNION ALL 2010SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' 2011UNION ALL 2012SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' 2013ORDER BY relname; 2014CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key 2015CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key 2016ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key 2017ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists 2018ALTER TABLE unlogged1 SET LOGGED; 2019-- check relpersistence of an unlogged table after changing to permanent 2020SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' 2021UNION ALL 2022SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' 2023UNION ALL 2024SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' 2025ORDER BY relname; 2026ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing 2027DROP TABLE unlogged3; 2028DROP TABLE unlogged2; 2029DROP TABLE unlogged1; 2030-- set unlogged 2031CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); 2032-- check relpersistence of a permanent table 2033SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' 2034UNION ALL 2035SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' 2036UNION ALL 2037SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' 2038ORDER BY relname; 2039CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key 2040CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key 2041ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists 2042ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key 2043ALTER TABLE logged2 SET UNLOGGED; 2044ALTER TABLE logged1 SET UNLOGGED; 2045-- check relpersistence of a permanent table after changing to unlogged 2046SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' 2047UNION ALL 2048SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' 2049UNION ALL 2050SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' 2051ORDER BY relname; 2052ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing 2053DROP TABLE logged3; 2054DROP TABLE logged2; 2055DROP TABLE logged1; 2056 2057-- test ADD COLUMN IF NOT EXISTS 2058CREATE TABLE test_add_column(c1 integer); 2059\d test_add_column 2060ALTER TABLE test_add_column 2061 ADD COLUMN c2 integer; 2062\d test_add_column 2063ALTER TABLE test_add_column 2064 ADD COLUMN c2 integer; -- fail because c2 already exists 2065ALTER TABLE ONLY test_add_column 2066 ADD COLUMN c2 integer; -- fail because c2 already exists 2067\d test_add_column 2068ALTER TABLE test_add_column 2069 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists 2070ALTER TABLE ONLY test_add_column 2071 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists 2072\d test_add_column 2073ALTER TABLE test_add_column 2074 ADD COLUMN c2 integer, -- fail because c2 already exists 2075 ADD COLUMN c3 integer; 2076\d test_add_column 2077ALTER TABLE test_add_column 2078 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2079 ADD COLUMN c3 integer; -- fail because c3 already exists 2080\d test_add_column 2081ALTER TABLE test_add_column 2082 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2083 ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists 2084\d test_add_column 2085ALTER TABLE test_add_column 2086 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2087 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists 2088 ADD COLUMN c4 integer; 2089\d test_add_column 2090DROP TABLE test_add_column; 2091 2092-- unsupported constraint types for partitioned tables 2093CREATE TABLE partitioned ( 2094 a int, 2095 b int 2096) PARTITION BY RANGE (a, (a+b+1)); 2097ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&); 2098 2099-- cannot drop column that is part of the partition key 2100ALTER TABLE partitioned DROP COLUMN a; 2101ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); 2102ALTER TABLE partitioned DROP COLUMN b; 2103ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); 2104 2105-- partitioned table cannot participate in regular inheritance 2106CREATE TABLE nonpartitioned ( 2107 a int, 2108 b int 2109); 2110ALTER TABLE partitioned INHERIT nonpartitioned; 2111ALTER TABLE nonpartitioned INHERIT partitioned; 2112 2113-- cannot add NO INHERIT constraint to partitioned tables 2114ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT; 2115 2116DROP TABLE partitioned, nonpartitioned; 2117 2118-- 2119-- ATTACH PARTITION 2120-- 2121 2122-- check that target table is partitioned 2123CREATE TABLE unparted ( 2124 a int 2125); 2126CREATE TABLE fail_part (like unparted); 2127ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a'); 2128DROP TABLE unparted, fail_part; 2129 2130-- check that partition bound is compatible 2131CREATE TABLE list_parted ( 2132 a int NOT NULL, 2133 b char(2) COLLATE "C", 2134 CONSTRAINT check_a CHECK (a > 0) 2135) PARTITION BY LIST (a); 2136CREATE TABLE fail_part (LIKE list_parted); 2137ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10); 2138DROP TABLE fail_part; 2139 2140-- check that the table being attached exists 2141ALTER TABLE list_parted ATTACH PARTITION nonexistant FOR VALUES IN (1); 2142 2143-- check ownership of the source table 2144CREATE ROLE regress_test_me; 2145CREATE ROLE regress_test_not_me; 2146CREATE TABLE not_owned_by_me (LIKE list_parted); 2147ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me; 2148SET SESSION AUTHORIZATION regress_test_me; 2149CREATE TABLE owned_by_me ( 2150 a int 2151) PARTITION BY LIST (a); 2152ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1); 2153RESET SESSION AUTHORIZATION; 2154DROP TABLE owned_by_me, not_owned_by_me; 2155DROP ROLE regress_test_not_me; 2156DROP ROLE regress_test_me; 2157 2158-- check that the table being attached is not part of regular inheritance 2159CREATE TABLE parent (LIKE list_parted); 2160CREATE TABLE child () INHERITS (parent); 2161ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1); 2162ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); 2163DROP TABLE parent CASCADE; 2164 2165-- check any TEMP-ness 2166CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); 2167CREATE TABLE perm_part (a int); 2168ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1); 2169DROP TABLE temp_parted, perm_part; 2170 2171-- check that the table being attached is not a typed table 2172CREATE TYPE mytype AS (a int); 2173CREATE TABLE fail_part OF mytype; 2174ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2175DROP TYPE mytype CASCADE; 2176 2177-- check that the table being attached has only columns present in the parent 2178CREATE TABLE fail_part (like list_parted, c int); 2179ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2180DROP TABLE fail_part; 2181 2182-- check that the table being attached has every column of the parent 2183CREATE TABLE fail_part (a int NOT NULL); 2184ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2185DROP TABLE fail_part; 2186 2187-- check that columns match in type, collation and NOT NULL status 2188CREATE TABLE fail_part ( 2189 b char(3), 2190 a int NOT NULL 2191); 2192ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2193ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX"; 2194ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2195DROP TABLE fail_part; 2196 2197-- check that the table being attached has all constraints of the parent 2198CREATE TABLE fail_part ( 2199 b char(2) COLLATE "C", 2200 a int NOT NULL 2201); 2202ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2203 2204-- check that the constraint matches in definition with parent's constraint 2205ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0); 2206ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2207DROP TABLE fail_part; 2208 2209-- check the attributes and constraints after partition is attached 2210CREATE TABLE part_1 ( 2211 a int NOT NULL, 2212 b char(2) COLLATE "C", 2213 CONSTRAINT check_a CHECK (a > 0) 2214); 2215ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); 2216-- attislocal and conislocal are always false for merged attributes and constraints respectively. 2217SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0; 2218SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a'; 2219 2220-- check that the new partition won't overlap with an existing partition 2221CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS); 2222ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2223DROP TABLE fail_part; 2224-- check that an existing table can be attached as a default partition 2225CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS); 2226ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT; 2227-- check attaching default partition fails if a default partition already 2228-- exists 2229CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS); 2230ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT; 2231 2232-- check validation when attaching list partitions 2233CREATE TABLE list_parted2 ( 2234 a int, 2235 b char 2236) PARTITION BY LIST (a); 2237 2238-- check that violating rows are correctly reported 2239CREATE TABLE part_2 (LIKE list_parted2); 2240INSERT INTO part_2 VALUES (3, 'a'); 2241ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2242 2243-- should be ok after deleting the bad row 2244DELETE FROM part_2; 2245ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2246 2247-- check partition cannot be attached if default has some row for its values 2248CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT; 2249INSERT INTO list_parted2_def VALUES (11, 'z'); 2250CREATE TABLE part_3 (LIKE list_parted2); 2251ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11); 2252-- should be ok after deleting the bad row 2253DELETE FROM list_parted2_def WHERE a = 11; 2254ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11); 2255 2256-- adding constraints that describe the desired partition constraint 2257-- (or more restrictive) will help skip the validation scan 2258CREATE TABLE part_3_4 ( 2259 LIKE list_parted2, 2260 CONSTRAINT check_a CHECK (a IN (3)) 2261); 2262 2263-- however, if a list partition does not accept nulls, there should be 2264-- an explicit NOT NULL constraint on the partition key column for the 2265-- validation scan to be skipped; 2266ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); 2267 2268-- adding a NOT NULL constraint will cause the scan to be skipped 2269ALTER TABLE list_parted2 DETACH PARTITION part_3_4; 2270ALTER TABLE part_3_4 ALTER a SET NOT NULL; 2271ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); 2272 2273-- check if default partition scan skipped 2274ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6)); 2275CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66); 2276 2277-- check validation when attaching range partitions 2278CREATE TABLE range_parted ( 2279 a int, 2280 b int 2281) PARTITION BY RANGE (a, b); 2282 2283-- check that violating rows are correctly reported 2284CREATE TABLE part1 ( 2285 a int NOT NULL CHECK (a = 1), 2286 b int NOT NULL CHECK (b >= 1 AND b <= 10) 2287); 2288INSERT INTO part1 VALUES (1, 10); 2289-- Remember the TO bound is exclusive 2290ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); 2291 2292-- should be ok after deleting the bad row 2293DELETE FROM part1; 2294ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); 2295 2296-- adding constraints that describe the desired partition constraint 2297-- (or more restrictive) will help skip the validation scan 2298CREATE TABLE part2 ( 2299 a int NOT NULL CHECK (a = 1), 2300 b int NOT NULL CHECK (b >= 10 AND b < 18) 2301); 2302ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20); 2303 2304-- Create default partition 2305CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT; 2306 2307-- Only one default partition is allowed, hence, following should give error 2308CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS); 2309ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT; 2310 2311-- Overlapping partitions cannot be attached, hence, following should give error 2312INSERT INTO partr_def1 VALUES (2, 10); 2313CREATE TABLE part3 (LIKE range_parted); 2314ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20); 2315 2316-- Attaching partitions should be successful when there are no overlapping rows 2317ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20); 2318 2319-- check that leaf partitions are scanned when attaching a partitioned 2320-- table 2321CREATE TABLE part_5 ( 2322 LIKE list_parted2 2323) PARTITION BY LIST (b); 2324 2325-- check that violating rows are correctly reported 2326CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a'); 2327INSERT INTO part_5_a (a, b) VALUES (6, 'a'); 2328ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2329 2330-- delete the faulting row and also add a constraint to skip the scan 2331DELETE FROM part_5_a WHERE a NOT IN (3); 2332ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5); 2333ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2334ALTER TABLE list_parted2 DETACH PARTITION part_5; 2335ALTER TABLE part_5 DROP CONSTRAINT check_a; 2336 2337-- scan should again be skipped, even though NOT NULL is now a column property 2338ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL; 2339ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2340 2341-- Check the case where attnos of the partitioning columns in the table being 2342-- attached differs from the parent. It should not affect the constraint- 2343-- checking logic that allows to skip the scan. 2344CREATE TABLE part_6 ( 2345 c int, 2346 LIKE list_parted2, 2347 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6) 2348); 2349ALTER TABLE part_6 DROP c; 2350ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6); 2351 2352-- Similar to above, but the table being attached is a partitioned table 2353-- whose partition has still different attnos for the root partitioning 2354-- columns. 2355CREATE TABLE part_7 ( 2356 LIKE list_parted2, 2357 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) 2358) PARTITION BY LIST (b); 2359CREATE TABLE part_7_a_null ( 2360 c int, 2361 d int, 2362 e int, 2363 LIKE list_parted2, -- 'a' will have attnum = 4 2364 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'), 2365 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) 2366); 2367ALTER TABLE part_7_a_null DROP c, DROP d, DROP e; 2368ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null); 2369ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); 2370 2371-- Same example, but check this time that the constraint correctly detects 2372-- violating rows 2373ALTER TABLE list_parted2 DETACH PARTITION part_7; 2374ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped 2375INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a'); 2376SELECT tableoid::regclass, a, b FROM part_7 order by a; 2377ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); 2378 2379-- check that leaf partitions of default partition are scanned when 2380-- attaching a partitioned table. 2381ALTER TABLE part_5 DROP CONSTRAINT check_a; 2382CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a); 2383CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5); 2384INSERT INTO part5_def_p1 VALUES (5, 'y'); 2385CREATE TABLE part5_p1 (LIKE part_5); 2386ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y'); 2387-- should be ok after deleting the bad row 2388DELETE FROM part5_def_p1 WHERE b = 'y'; 2389ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y'); 2390 2391-- check that the table being attached is not already a partition 2392ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2393 2394-- check that circular inheritance is not allowed 2395ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); 2396ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); 2397 2398-- If a partitioned table being created or an existing table being attached 2399-- as a partition does not have a constraint that would allow validation scan 2400-- to be skipped, but an individual partition does, then the partition's 2401-- validation scan is skipped. 2402CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); 2403CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b); 2404CREATE TABLE quuux_default1 PARTITION OF quuux_default ( 2405 CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1) 2406) FOR VALUES IN ('b'); 2407CREATE TABLE quuux1 (a int, b text); 2408ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! 2409CREATE TABLE quuux2 (a int, b text); 2410ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation 2411DROP TABLE quuux1, quuux2; 2412-- should validate for quuux1, but not for quuux2 2413CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1); 2414CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2); 2415DROP TABLE quuux; 2416 2417-- check validation when attaching hash partitions 2418 2419-- Use hand-rolled hash functions and operator class to get predictable result 2420-- on different matchines. part_test_int4_ops is defined in insert.sql. 2421 2422-- check that the new partition won't overlap with an existing partition 2423CREATE TABLE hash_parted ( 2424 a int, 2425 b int 2426) PARTITION BY HASH (a part_test_int4_ops); 2427CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0); 2428CREATE TABLE fail_part (LIKE hpart_1); 2429ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4); 2430ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0); 2431DROP TABLE fail_part; 2432 2433-- check validation when attaching hash partitions 2434 2435-- check that violating rows are correctly reported 2436CREATE TABLE hpart_2 (LIKE hash_parted); 2437INSERT INTO hpart_2 VALUES (3, 0); 2438ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1); 2439 2440-- should be ok after deleting the bad row 2441DELETE FROM hpart_2; 2442ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1); 2443 2444-- check that leaf partitions are scanned when attaching a partitioned 2445-- table 2446CREATE TABLE hpart_5 ( 2447 LIKE hash_parted 2448) PARTITION BY LIST (b); 2449 2450-- check that violating rows are correctly reported 2451CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3'); 2452INSERT INTO hpart_5_a (a, b) VALUES (7, 1); 2453ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2); 2454 2455-- should be ok after deleting the bad row 2456DELETE FROM hpart_5_a; 2457ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2); 2458 2459-- check that the table being attach is with valid modulus and remainder value 2460CREATE TABLE fail_part(LIKE hash_parted); 2461ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1); 2462ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8); 2463ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2); 2464DROP TABLE fail_part; 2465 2466-- 2467-- DETACH PARTITION 2468-- 2469 2470-- check that the table is partitioned at all 2471CREATE TABLE regular_table (a int); 2472ALTER TABLE regular_table DETACH PARTITION any_name; 2473DROP TABLE regular_table; 2474 2475-- check that the partition being detached exists at all 2476ALTER TABLE list_parted2 DETACH PARTITION part_4; 2477ALTER TABLE hash_parted DETACH PARTITION hpart_4; 2478 2479-- check that the partition being detached is actually a partition of the parent 2480CREATE TABLE not_a_part (a int); 2481ALTER TABLE list_parted2 DETACH PARTITION not_a_part; 2482ALTER TABLE list_parted2 DETACH PARTITION part_1; 2483 2484ALTER TABLE hash_parted DETACH PARTITION not_a_part; 2485DROP TABLE not_a_part; 2486 2487-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and 2488-- attislocal/conislocal is set to true 2489ALTER TABLE list_parted2 DETACH PARTITION part_3_4; 2490SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0; 2491SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a'; 2492DROP TABLE part_3_4; 2493 2494-- check that a detached partition is not dropped on dropping a partitioned table 2495CREATE TABLE range_parted2 ( 2496 a int 2497) PARTITION BY RANGE(a); 2498CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100); 2499ALTER TABLE range_parted2 DETACH PARTITION part_rp; 2500DROP TABLE range_parted2; 2501SELECT * from part_rp; 2502DROP TABLE part_rp; 2503 2504-- Check ALTER TABLE commands for partitioned tables and partitions 2505 2506-- cannot add/drop column to/from *only* the parent 2507ALTER TABLE ONLY list_parted2 ADD COLUMN c int; 2508ALTER TABLE ONLY list_parted2 DROP COLUMN b; 2509 2510-- cannot add a column to partition or drop an inherited one 2511ALTER TABLE part_2 ADD COLUMN c text; 2512ALTER TABLE part_2 DROP COLUMN b; 2513 2514-- Nor rename, alter type 2515ALTER TABLE part_2 RENAME COLUMN b to c; 2516ALTER TABLE part_2 ALTER COLUMN b TYPE text; 2517 2518-- cannot add/drop NOT NULL or check constraints to *only* the parent, when 2519-- partitions exist 2520ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; 2521ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); 2522 2523ALTER TABLE list_parted2 ALTER b SET NOT NULL; 2524ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL; 2525ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); 2526ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b; 2527 2528-- It's alright though, if no partitions are yet created 2529CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a); 2530ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL; 2531ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0); 2532ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL; 2533ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a; 2534DROP TABLE parted_no_parts; 2535 2536-- cannot drop inherited NOT NULL or check constraints from partition 2537ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0); 2538ALTER TABLE part_2 ALTER b DROP NOT NULL; 2539ALTER TABLE part_2 DROP CONSTRAINT check_a2; 2540 2541-- Doesn't make sense to add NO INHERIT constraints on partitioned tables 2542ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT; 2543 2544-- check that a partition cannot participate in regular inheritance 2545CREATE TABLE inh_test () INHERITS (part_2); 2546CREATE TABLE inh_test (LIKE part_2); 2547ALTER TABLE inh_test INHERIT part_2; 2548ALTER TABLE part_2 INHERIT inh_test; 2549 2550-- cannot drop or alter type of partition key columns of lower level 2551-- partitioned tables; for example, part_5, which is list_parted2's 2552-- partition, is partitioned on b; 2553ALTER TABLE list_parted2 DROP COLUMN b; 2554ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; 2555 2556-- dropping non-partition key columns should be allowed on the parent table. 2557ALTER TABLE list_parted DROP COLUMN b; 2558SELECT * FROM list_parted; 2559 2560-- cleanup 2561DROP TABLE list_parted, list_parted2, range_parted; 2562DROP TABLE fail_def_part; 2563DROP TABLE hash_parted; 2564 2565-- more tests for certain multi-level partitioning scenarios 2566create table p (a int, b int) partition by range (a, b); 2567create table p1 (b int, a int not null) partition by range (b); 2568create table p11 (like p1); 2569alter table p11 drop a; 2570alter table p11 add a int; 2571alter table p11 drop a; 2572alter table p11 add a int not null; 2573-- attnum for key attribute 'a' is different in p, p1, and p11 2574select attrelid::regclass, attname, attnum 2575from pg_attribute 2576where attname = 'a' 2577 and (attrelid = 'p'::regclass 2578 or attrelid = 'p1'::regclass 2579 or attrelid = 'p11'::regclass) 2580order by attrelid::regclass::text; 2581 2582alter table p1 attach partition p11 for values from (2) to (5); 2583 2584insert into p1 (a, b) values (2, 3); 2585-- check that partition validation scan correctly detects violating rows 2586alter table p attach partition p1 for values from (1, 2) to (1, 10); 2587 2588-- cleanup 2589drop table p; 2590drop table p1; 2591 2592-- validate constraint on partitioned tables should only scan leaf partitions 2593create table parted_validate_test (a int) partition by list (a); 2594create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1); 2595alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid; 2596alter table parted_validate_test validate constraint parted_validate_test_chka; 2597drop table parted_validate_test; 2598-- test alter column options 2599CREATE TABLE attmp(i integer); 2600INSERT INTO attmp VALUES (1); 2601ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2); 2602ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited); 2603ANALYZE attmp; 2604DROP TABLE attmp; 2605 2606DROP USER regress_alter_table_user1; 2607 2608-- check that violating rows are correctly reported when attaching as the 2609-- default partition 2610create table defpart_attach_test (a int) partition by list (a); 2611create table defpart_attach_test1 partition of defpart_attach_test for values in (1); 2612create table defpart_attach_test_d (like defpart_attach_test); 2613insert into defpart_attach_test_d values (1), (2); 2614 2615-- error because its constraint as the default partition would be violated 2616-- by the row containing 1 2617alter table defpart_attach_test attach partition defpart_attach_test_d default; 2618delete from defpart_attach_test_d where a = 1; 2619alter table defpart_attach_test_d add check (a > 1); 2620 2621-- should be attached successfully and without needing to be scanned 2622alter table defpart_attach_test attach partition defpart_attach_test_d default; 2623 2624drop table defpart_attach_test; 2625 2626-- check combinations of temporary and permanent relations when attaching 2627-- partitions. 2628create table perm_part_parent (a int) partition by list (a); 2629create temp table temp_part_parent (a int) partition by list (a); 2630create table perm_part_child (a int); 2631create temp table temp_part_child (a int); 2632alter table temp_part_parent attach partition perm_part_child default; -- error 2633alter table perm_part_parent attach partition temp_part_child default; -- error 2634alter table temp_part_parent attach partition temp_part_child default; -- ok 2635drop table perm_part_parent cascade; 2636drop table temp_part_parent cascade; 2637 2638-- check that attaching partitions to a table while it is being used is 2639-- prevented 2640create table tab_part_attach (a int) partition by list (a); 2641create or replace function func_part_attach() returns trigger 2642 language plpgsql as $$ 2643 begin 2644 execute 'create table tab_part_attach_1 (a int)'; 2645 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)'; 2646 return null; 2647 end $$; 2648create trigger trig_part_attach before insert on tab_part_attach 2649 for each statement execute procedure func_part_attach(); 2650insert into tab_part_attach values (1); 2651drop table tab_part_attach; 2652drop function func_part_attach(); 2653 2654-- test case where the partitioning operator is a SQL function whose 2655-- evaluation results in the table's relcache being rebuilt partway through 2656-- the execution of an ATTACH PARTITION command 2657create function at_test_sql_partop (int4, int4) returns int language sql 2658as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$; 2659create operator class at_test_sql_partop for type int4 using btree as 2660 operator 1 < (int4, int4), operator 2 <= (int4, int4), 2661 operator 3 = (int4, int4), operator 4 >= (int4, int4), 2662 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4); 2663create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop); 2664create table at_test_sql_partop_1 (a int); 2665alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10); 2666drop table at_test_sql_partop; 2667drop operator class at_test_sql_partop using btree; 2668drop function at_test_sql_partop; 2669