Fix inherited UPDATE for cases where child column numbering doesn't

[postgresql] / src / test / regress / sql / alter_table.sql

diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index b74df5a725681982ec94b532d9337ac52cb4c050..79ea952adeefb2f15e1e52181756abaa6a0ca9f9 100644 (file)
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -415,7 +415,11 @@ insert into atacc1 (test) values (2);
 insert into atacc1 (test) values (4);
 -- inserting NULL should fail
 insert into atacc1 (test) values(NULL);
--- try adding a primary key oid constraint
+-- try adding a second primary key (should fail)
+alter table atacc1 add constraint atacc_oid1 primary key(oid);
+-- drop first primary key constraint
+alter table atacc1 drop constraint atacc_test1 restrict;
+-- try adding a primary key on oid (should succeed)
 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 drop table atacc1;
 
@@ -718,6 +722,7 @@ copy test("........pg.dropped.1........") to stdout;
 copy test from stdin;
 10     11      12
 \.
+SET autocommit TO 'on';
 select * from test;
 copy test from stdin;
 21     22
@@ -844,3 +849,21 @@ where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
 order by relname, attnum;
 
 drop table p1, p2 cascade;
+
+-- test renumbering of child-table columns in inherited operations
+
+create table p1 (f1 int);
+create table c1 (f2 text, f3 int) inherits (p1);
+
+alter table p1 add column a1 int check (a1 > 0);
+alter table p1 add column f2 text;
+
+insert into p1 values (1,2,'abc');
+insert into c1 values(11,'xyz',33,0); -- should fail
+insert into c1 values(11,'xyz',33,22);
+
+select * from p1;
+update p1 set a1 = a1 + 1, f2 = upper(f2);
+select * from p1;
+
+drop table p1 cascade;