Identity columns

This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:

- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

Reviewed-by: Vitaly Burovoy <vitaly.burovoy@gmail.com>

10 files changed, 632 insertions, 3 deletions

diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index a25b221703..3f405c94ce 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -66,6 +66,53 @@ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y
 (2 rows)
 
 DROP TABLE inhg;
+CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text);
+\d test_like_id_1
+                     Table "public.test_like_id_1"
+ Column |  Type   | Collation | Nullable |           Default            
+--------+---------+-----------+----------+------------------------------
+ a      | integer |           | not null | generated always as identity
+ b      | text    |           |          | 
+
+INSERT INTO test_like_id_1 (b) VALUES ('b1');
+SELECT * FROM test_like_id_1;
+ a | b  
+---+----
+ 1 | b1
+(1 row)
+
+CREATE TABLE test_like_id_2 (LIKE test_like_id_1);
+\d test_like_id_2
+           Table "public.test_like_id_2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | text    |           |          | 
+
+INSERT INTO test_like_id_2 (b) VALUES ('b2');
+ERROR:  null value in column "a" violates not-null constraint
+DETAIL:  Failing row contains (null, b2).
+SELECT * FROM test_like_id_2;  -- identity was not copied
+ a | b 
+---+---
+(0 rows)
+
+CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
+\d test_like_id_3
+                     Table "public.test_like_id_3"
+ Column |  Type   | Collation | Nullable |           Default            
+--------+---------+-----------+----------+------------------------------
+ a      | integer |           | not null | generated always as identity
+ b      | text    |           |          | 
+
+INSERT INTO test_like_id_3 (b) VALUES ('b3');
+SELECT * FROM test_like_id_3;  -- identity was copied and applied
+ a | b  
+---+----
+ 1 | b3
+(1 row)
+
+DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
 INSERT INTO inhg VALUES (5, 10);
 INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
new file mode 100644
index 0000000000..88b56dad93
--- /dev/null
+++ b/src/test/regress/expected/identity.out
@@ -0,0 +1,322 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd');
+ attrelid | attname | attidentity 
+----------+---------+-------------
+(0 rows)
+
+CREATE TABLE itest1 (a int generated by default as identity, b text);
+CREATE TABLE itest2 (a bigint generated always as identity, b text);
+CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);
+ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error
+ERROR:  column "a" of relation "itest3" is already an identity column
+SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2;
+ table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment |  identity_maximum   | identity_minimum | identity_cycle 
+------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+----------------
+ itest1     | a           |                | NO          | YES         | BY DEFAULT          | 1              | 1                  | 2147483647          | 1                | NO
+ itest1     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
+ itest2     | a           |                | NO          | YES         | ALWAYS              | 1              | 1                  | 9223372036854775807 | 1                | NO
+ itest2     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
+ itest3     | a           |                | NO          | YES         | BY DEFAULT          | 7              | 5                  | 32767               | 1                | NO
+ itest3     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
+(6 rows)
+
+-- internal sequences should not be shown here
+SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
+ sequence_name 
+---------------
+(0 rows)
+
+CREATE TABLE itest4 (a int, b text);
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, requires NOT NULL
+ERROR:  column "a" of relation "itest4" must be declared NOT NULL before identity can be added
+ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- ok
+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;  -- error, disallowed
+ERROR:  column "a" of relation "itest4" is an identity column
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, already set
+ERROR:  column "a" of relation "itest4" is already an identity column
+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY;  -- error, wrong data type
+ERROR:  identity column type must be smallint, integer, or bigint
+-- for later
+ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';
+-- invalid column type
+CREATE TABLE itest_err_1 (a text generated by default as identity);
+ERROR:  identity column type must be smallint, integer, or bigint
+-- duplicate identity
+CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);
+ERROR:  multiple identity specifications for column "a" of table "itest_err_2"
+LINE 1: ...E itest_err_2 (a int generated always as identity generated ...
+                                                             ^
+-- cannot have default and identity
+CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);
+ERROR:  both default and identity specified for column "a" of table "itest_err_3"
+LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau...
+                                                  ^
+-- cannot combine serial and identity
+CREATE TABLE itest_err_4 (a serial generated by default as identity);
+ERROR:  both default and identity specified for column "a" of table "itest_err_4"
+INSERT INTO itest1 DEFAULT VALUES;
+INSERT INTO itest1 DEFAULT VALUES;
+INSERT INTO itest2 DEFAULT VALUES;
+INSERT INTO itest2 DEFAULT VALUES;
+INSERT INTO itest3 DEFAULT VALUES;
+INSERT INTO itest3 DEFAULT VALUES;
+INSERT INTO itest4 DEFAULT VALUES;
+INSERT INTO itest4 DEFAULT VALUES;
+SELECT * FROM itest1;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+(2 rows)
+
+SELECT * FROM itest2;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+(2 rows)
+
+SELECT * FROM itest3;
+ a  | b 
+----+---
+  7 | 
+ 12 | 
+(2 rows)
+
+SELECT * FROM itest4;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+(2 rows)
+
+-- OVERRIDING tests
+INSERT INTO itest1 VALUES (10, 'xyz');
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+SELECT * FROM itest1;
+ a  |  b  
+----+-----
+  1 | 
+  2 | 
+ 10 | xyz
+  3 | xyz
+(4 rows)
+
+INSERT INTO itest2 VALUES (10, 'xyz');
+ERROR:  cannot insert into column "a"
+DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
+HINT:  Use OVERRIDING SYSTEM VALUE to override.
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
+SELECT * FROM itest2;
+ a  |  b  
+----+-----
+  1 | 
+  2 | 
+ 10 | xyz
+(3 rows)
+
+-- UPDATE tests
+UPDATE itest1 SET a = 101 WHERE a = 1;
+UPDATE itest1 SET a = DEFAULT WHERE a = 2;
+SELECT * FROM itest1;
+  a  |  b  
+-----+-----
+  10 | xyz
+   3 | xyz
+ 101 | 
+   4 | 
+(4 rows)
+
+UPDATE itest2 SET a = 101 WHERE a = 1;
+ERROR:  column "a" can only be updated to DEFAULT
+DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
+UPDATE itest2 SET a = DEFAULT WHERE a = 2;
+SELECT * FROM itest2;
+ a  |  b  
+----+-----
+  1 | 
+ 10 | xyz
+  3 | 
+(3 rows)
+
+-- DROP IDENTITY tests
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;  -- error
+ERROR:  column "a" of relation "itest4" is not an identity column
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS;  -- noop
+NOTICE:  column "a" of relation "itest4" is not an identity column, skipping
+INSERT INTO itest4 DEFAULT VALUES;  -- fails because NOT NULL is not dropped
+ERROR:  null value in column "a" violates not-null constraint
+DETAIL:  Failing row contains (null, ).
+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
+INSERT INTO itest4 DEFAULT VALUES;
+SELECT * FROM itest4;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+   | 
+(3 rows)
+
+-- check that sequence is removed
+SELECT sequence_name FROM itest4_a_seq;
+ERROR:  relation "itest4_a_seq" does not exist
+LINE 1: SELECT sequence_name FROM itest4_a_seq;
+                                  ^
+-- test views
+CREATE TABLE itest10 (a int generated by default as identity, b text);
+CREATE TABLE itest11 (a int generated always as identity, b text);
+CREATE VIEW itestv10 AS SELECT * FROM itest10;
+CREATE VIEW itestv11 AS SELECT * FROM itest11;
+INSERT INTO itestv10 DEFAULT VALUES;
+INSERT INTO itestv10 DEFAULT VALUES;
+INSERT INTO itestv11 DEFAULT VALUES;
+INSERT INTO itestv11 DEFAULT VALUES;
+SELECT * FROM itestv10;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+(2 rows)
+
+SELECT * FROM itestv11;
+ a | b 
+---+---
+ 1 | 
+ 2 | 
+(2 rows)
+
+INSERT INTO itestv10 VALUES (10, 'xyz');
+INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');
+SELECT * FROM itestv10;
+ a  |  b  
+----+-----
+  1 | 
+  2 | 
+ 10 | xyz
+  3 | xyz
+(4 rows)
+
+INSERT INTO itestv11 VALUES (10, 'xyz');
+ERROR:  cannot insert into column "a"
+DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
+HINT:  Use OVERRIDING SYSTEM VALUE to override.
+INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
+SELECT * FROM itestv11;
+ a  |  b  
+----+-----
+  1 | 
+  2 | 
+ 11 | xyz
+(3 rows)
+
+-- various ALTER COLUMN tests
+-- fail, not allowed for identity columns
+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
+ERROR:  column "a" of relation "itest1" is an identity column
+-- fail, not allowed, already has a default
+CREATE TABLE itest5 (a serial, b text);
+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ERROR:  column "a" of relation "itest5" already has a default value
+ALTER TABLE itest3 ALTER COLUMN a TYPE int;
+SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;
+ seqtypid 
+----------
+ integer
+(1 row)
+
+\d itest3
+                           Table "public.itest3"
+ Column |  Type   | Collation | Nullable |             Default              
+--------+---------+-----------+----------+----------------------------------
+ a      | integer |           | not null | generated by default as identity
+ b      | text    |           |          | 
+
+ALTER TABLE itest3 ALTER COLUMN a TYPE text;  -- error
+ERROR:  identity column type must be smallint, integer, or bigint
+-- ALTER COLUMN ... SET
+CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
+INSERT INTO itest6 DEFAULT VALUES;
+ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
+INSERT INTO itest6 DEFAULT VALUES;
+INSERT INTO itest6 DEFAULT VALUES;
+SELECT * FROM itest6;
+  a  | b 
+-----+---
+   1 | 
+ 100 | 
+ 102 | 
+(3 rows)
+
+SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';
+ table_name | column_name | is_identity | identity_generation 
+------------+-------------+-------------+---------------------
+ itest6     | a           | YES         | BY DEFAULT
+ itest6     | b           | NO          | 
+(2 rows)
+
+ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2;  -- fail, not identity
+ERROR:  column "b" of relation "itest6" is not an identity column
+-- prohibited direct modification of sequence
+ALTER SEQUENCE itest6_a_seq OWNED BY NONE;
+ERROR:  cannot change ownership of identity sequence
+DETAIL:  Sequence "itest6_a_seq" is linked to table "itest6".
+-- inheritance
+CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
+INSERT INTO itest7 DEFAULT VALUES;
+SELECT * FROM itest7;
+ a 
+---
+ 1
+(1 row)
+
+-- identity property is not inherited
+CREATE TABLE itest7a (b text) INHERITS (itest7);
+-- make column identity in child table
+CREATE TABLE itest7b (a int);
+CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);
+NOTICE:  merging column "a" with inherited definition
+INSERT INTO itest7c DEFAULT VALUES;
+SELECT * FROM itest7c;
+ a 
+---
+ 1
+(1 row)
+
+CREATE TABLE itest7d (a int not null);
+CREATE TABLE itest7e () INHERITS (itest7d);
+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY;  -- error
+ERROR:  cannot recursively add identity column to table that has child tables
+SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;
+ table_name | column_name | is_nullable | is_identity | identity_generation 
+------------+-------------+-------------+-------------+---------------------
+ itest7     | a           | NO          | YES         | ALWAYS
+ itest7a    | a           | NO          | NO          | 
+ itest7a    | b           | YES         | NO          | 
+ itest7b    | a           | YES         | NO          | 
+ itest7c    | a           | NO          | YES         | ALWAYS
+ itest7d    | a           | NO          | YES         | ALWAYS
+ itest7e    | a           | NO          | NO          | 
+(7 rows)
+
+-- These ALTER TABLE variants will not recurse.
+ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;
+ALTER TABLE itest7 ALTER COLUMN a RESTART;
+ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;
+-- privileges
+CREATE USER regress_user1;
+CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);
+GRANT SELECT, INSERT ON itest8 TO regress_user1;
+SET ROLE regress_user1;
+INSERT INTO itest8 DEFAULT VALUES;
+SELECT * FROM itest8;
+ a | b 
+---+---
+ 1 | 
+(1 row)
+
+RESET ROLE;
+DROP TABLE itest8;
+DROP USER regress_user1;
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 1d8d02b800..16c12f3434 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -20,8 +20,8 @@ ERROR:  CACHE (0) must be greater than zero
 CREATE SEQUENCE sequence_testx OWNED BY nobody;  -- nonsense word
 ERROR:  invalid OWNED BY option
 HINT:  Specify OWNED BY table.column or OWNED BY NONE.
-CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename;  -- not a table
-ERROR:  referenced relation "pg_tables" is not a table or foreign table
+CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid;  -- not a table
+ERROR:  referenced relation "pg_class_oid_index" is not a table or foreign table
 CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname;  -- not same schema
 ERROR:  sequence must be in same schema as table it is linked to
 CREATE TABLE sequence_test_table (a int);
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 81612d8c88..b652562f5b 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -393,6 +393,36 @@ SELECT * FROM truncate_a;
   2 |  34
 (2 rows)
 
+CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id 
+----
+ 44
+ 45
+(2 rows)
+
+TRUNCATE truncate_b;
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id 
+----
+ 46
+ 47
+(2 rows)
+
+TRUNCATE truncate_b RESTART IDENTITY;
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id 
+----
+ 44
+ 45
+(2 rows)
+
 -- check rollback of a RESTART IDENTITY operation
 BEGIN;
 TRUNCATE truncate_a RESTART IDENTITY;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 9f95b016fd..1f8f0987e3 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -111,6 +111,11 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
 # ----------
 test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
 
+# ----------
+# Another group of parallel tests
+# ----------
+test: identity
+
 # event triggers cannot run concurrently with any test that runs DDL
 test: event_trigger
 
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index e026b7cc90..04206c3162 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -170,6 +170,7 @@ test: conversion
 test: truncate
 test: alter_table
 test: sequence
+test: identity
 test: polymorphism
 test: rowtypes
 test: returning
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 900ca804cb..557040bbe7 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -37,6 +37,20 @@ INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
 SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
 DROP TABLE inhg;
 
+CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text);
+\d test_like_id_1
+INSERT INTO test_like_id_1 (b) VALUES ('b1');
+SELECT * FROM test_like_id_1;
+CREATE TABLE test_like_id_2 (LIKE test_like_id_1);
+\d test_like_id_2
+INSERT INTO test_like_id_2 (b) VALUES ('b2');
+SELECT * FROM test_like_id_2;  -- identity was not copied
+CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
+\d test_like_id_3
+INSERT INTO test_like_id_3 (b) VALUES ('b3');
+SELECT * FROM test_like_id_3;  -- identity was copied and applied
+DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+
 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
 INSERT INTO inhg VALUES (5, 10);
 INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
new file mode 100644
index 0000000000..a7e7b15737
--- /dev/null
+++ b/src/test/regress/sql/identity.sql
@@ -0,0 +1,192 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd');
+
+
+CREATE TABLE itest1 (a int generated by default as identity, b text);
+CREATE TABLE itest2 (a bigint generated always as identity, b text);
+CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);
+ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error
+
+SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2;
+
+-- internal sequences should not be shown here
+SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
+
+CREATE TABLE itest4 (a int, b text);
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, requires NOT NULL
+ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- ok
+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;  -- error, disallowed
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, already set
+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY;  -- error, wrong data type
+
+-- for later
+ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';
+
+-- invalid column type
+CREATE TABLE itest_err_1 (a text generated by default as identity);
+
+-- duplicate identity
+CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);
+
+-- cannot have default and identity
+CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);
+
+-- cannot combine serial and identity
+CREATE TABLE itest_err_4 (a serial generated by default as identity);
+
+INSERT INTO itest1 DEFAULT VALUES;
+INSERT INTO itest1 DEFAULT VALUES;
+INSERT INTO itest2 DEFAULT VALUES;
+INSERT INTO itest2 DEFAULT VALUES;
+INSERT INTO itest3 DEFAULT VALUES;
+INSERT INTO itest3 DEFAULT VALUES;
+INSERT INTO itest4 DEFAULT VALUES;
+INSERT INTO itest4 DEFAULT VALUES;
+
+SELECT * FROM itest1;
+SELECT * FROM itest2;
+SELECT * FROM itest3;
+SELECT * FROM itest4;
+
+
+-- OVERRIDING tests
+
+INSERT INTO itest1 VALUES (10, 'xyz');
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+
+SELECT * FROM itest1;
+
+INSERT INTO itest2 VALUES (10, 'xyz');
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
+
+SELECT * FROM itest2;
+
+
+-- UPDATE tests
+
+UPDATE itest1 SET a = 101 WHERE a = 1;
+UPDATE itest1 SET a = DEFAULT WHERE a = 2;
+SELECT * FROM itest1;
+
+UPDATE itest2 SET a = 101 WHERE a = 1;
+UPDATE itest2 SET a = DEFAULT WHERE a = 2;
+SELECT * FROM itest2;
+
+
+-- DROP IDENTITY tests
+
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;  -- error
+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS;  -- noop
+
+INSERT INTO itest4 DEFAULT VALUES;  -- fails because NOT NULL is not dropped
+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
+INSERT INTO itest4 DEFAULT VALUES;
+SELECT * FROM itest4;
+
+-- check that sequence is removed
+SELECT sequence_name FROM itest4_a_seq;
+
+
+-- test views
+
+CREATE TABLE itest10 (a int generated by default as identity, b text);
+CREATE TABLE itest11 (a int generated always as identity, b text);
+
+CREATE VIEW itestv10 AS SELECT * FROM itest10;
+CREATE VIEW itestv11 AS SELECT * FROM itest11;
+
+INSERT INTO itestv10 DEFAULT VALUES;
+INSERT INTO itestv10 DEFAULT VALUES;
+
+INSERT INTO itestv11 DEFAULT VALUES;
+INSERT INTO itestv11 DEFAULT VALUES;
+
+SELECT * FROM itestv10;
+SELECT * FROM itestv11;
+
+INSERT INTO itestv10 VALUES (10, 'xyz');
+INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');
+
+SELECT * FROM itestv10;
+
+INSERT INTO itestv11 VALUES (10, 'xyz');
+INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
+
+SELECT * FROM itestv11;
+
+
+-- various ALTER COLUMN tests
+
+-- fail, not allowed for identity columns
+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
+
+-- fail, not allowed, already has a default
+CREATE TABLE itest5 (a serial, b text);
+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+
+ALTER TABLE itest3 ALTER COLUMN a TYPE int;
+SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;
+\d itest3
+
+ALTER TABLE itest3 ALTER COLUMN a TYPE text;  -- error
+
+
+-- ALTER COLUMN ... SET
+
+CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
+INSERT INTO itest6 DEFAULT VALUES;
+
+ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
+INSERT INTO itest6 DEFAULT VALUES;
+INSERT INTO itest6 DEFAULT VALUES;
+SELECT * FROM itest6;
+
+SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';
+
+ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2;  -- fail, not identity
+
+
+-- prohibited direct modification of sequence
+
+ALTER SEQUENCE itest6_a_seq OWNED BY NONE;
+
+
+-- inheritance
+
+CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
+INSERT INTO itest7 DEFAULT VALUES;
+SELECT * FROM itest7;
+
+-- identity property is not inherited
+CREATE TABLE itest7a (b text) INHERITS (itest7);
+
+-- make column identity in child table
+CREATE TABLE itest7b (a int);
+CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);
+INSERT INTO itest7c DEFAULT VALUES;
+SELECT * FROM itest7c;
+
+CREATE TABLE itest7d (a int not null);
+CREATE TABLE itest7e () INHERITS (itest7d);
+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY;  -- error
+
+SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;
+
+-- These ALTER TABLE variants will not recurse.
+ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;
+ALTER TABLE itest7 ALTER COLUMN a RESTART;
+ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;
+
+-- privileges
+CREATE USER regress_user1;
+CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);
+GRANT SELECT, INSERT ON itest8 TO regress_user1;
+SET ROLE regress_user1;
+INSERT INTO itest8 DEFAULT VALUES;
+SELECT * FROM itest8;
+RESET ROLE;
+DROP TABLE itest8;
+DROP USER regress_user1;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 74663d7351..d53e33d779 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -13,7 +13,7 @@ CREATE SEQUENCE sequence_testx CACHE 0;
 
 -- OWNED BY errors
 CREATE SEQUENCE sequence_testx OWNED BY nobody;  -- nonsense word
-CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename;  -- not a table
+CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid;  -- not a table
 CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname;  -- not same schema
 CREATE TABLE sequence_test_table (a int);
 CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b;  -- wrong column
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index d61eea1a42..9d3d8de54a 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -202,6 +202,24 @@ INSERT INTO truncate_a DEFAULT VALUES;
 INSERT INTO truncate_a DEFAULT VALUES;
 SELECT * FROM truncate_a;
 
+CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));
+
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+
+TRUNCATE truncate_b;
+
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+
+TRUNCATE truncate_b RESTART IDENTITY;
+
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+
 -- check rollback of a RESTART IDENTITY operation
 BEGIN;
 TRUNCATE truncate_a RESTART IDENTITY;