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