Fix INSERT OVERRIDING USER VALUE behavior

The original implementation disallowed using OVERRIDING USER VALUE on
identity columns defined as GENERATED ALWAYS, which is not per
standard.  So allow that now.

Expand documentation and tests around this.

Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Discussion: https://www.postgresql.org/message-id/flat/CAEZATCVrh2ufCwmzzM%3Dk_OfuLhTTPBJCdFkimst2kry4oHepuQ%40mail.gmail.com

3 files changed, 54 insertions, 16 deletions

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 3b4f28874aa..fe777c3103d 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -808,7 +808,9 @@ rewriteTargetListIU(List *targetList,
 		{
 			if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default)
 			{
-				if (override != OVERRIDING_SYSTEM_VALUE)
+				if (override == OVERRIDING_USER_VALUE)
+					apply_default = true;
+				else if (override != OVERRIDING_SYSTEM_VALUE)
 					ereport(ERROR,
 							(errcode(ERRCODE_GENERATED_ALWAYS),
 							 errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)),
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 7322b287650..7ac9df767f5 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -118,53 +118,72 @@ SELECT * FROM itest3;
 (5 rows)
 
 -- OVERRIDING tests
+-- GENERATED BY DEFAULT
+-- This inserts the row as presented:
 INSERT INTO itest1 VALUES (10, 'xyz');
-INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
+-- by the standard, but we allow it as a no-op, since it is of use if
+-- there are multiple identity columns in a table, which is also an
+-- extension.
+INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+-- This ignores the 30 and uses the sequence value instead:
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
 SELECT * FROM itest1;
  a  |  b  
 ----+-----
   1 | 
   2 | 
  10 | xyz
+ 20 | xyz
   3 | xyz
-(4 rows)
+(5 rows)
 
+-- GENERATED ALWAYS
+-- This is an error:
 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');
+-- This inserts the row as presented:
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+-- This ignores the 30 and uses the sequence value instead:
+INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
 SELECT * FROM itest2;
  a  |  b  
 ----+-----
   1 | 
   2 | 
- 10 | xyz
-(3 rows)
+ 20 | xyz
+  3 | xyz
+(4 rows)
 
 -- UPDATE tests
+-- GENERATED BY DEFAULT is not restricted.
 UPDATE itest1 SET a = 101 WHERE a = 1;
 UPDATE itest1 SET a = DEFAULT WHERE a = 2;
 SELECT * FROM itest1;
   a  |  b  
 -----+-----
   10 | xyz
+  20 | xyz
    3 | xyz
  101 | 
    4 | 
-(4 rows)
+(5 rows)
 
-UPDATE itest2 SET a = 101 WHERE a = 1;
+-- GENERATED ALWAYS allows only DEFAULT.
+UPDATE itest2 SET a = 101 WHERE a = 1;  -- error
 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;
+UPDATE itest2 SET a = DEFAULT WHERE a = 2;  -- ok
 SELECT * FROM itest2;
  a  |  b  
 ----+-----
   1 | 
- 10 | xyz
-  3 | 
-(3 rows)
+ 20 | xyz
+  3 | xyz
+  4 | 
+(4 rows)
 
 -- COPY tests
 CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint);
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index b4cdd21bdd4..1bf2a976eb0 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -64,25 +64,42 @@ SELECT * FROM itest3;
 
 -- OVERRIDING tests
 
+-- GENERATED BY DEFAULT
+
+-- This inserts the row as presented:
 INSERT INTO itest1 VALUES (10, 'xyz');
-INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
+-- by the standard, but we allow it as a no-op, since it is of use if
+-- there are multiple identity columns in a table, which is also an
+-- extension.
+INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+-- This ignores the 30 and uses the sequence value instead:
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
 
 SELECT * FROM itest1;
 
+-- GENERATED ALWAYS
+
+-- This is an error:
 INSERT INTO itest2 VALUES (10, 'xyz');
-INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
+-- This inserts the row as presented:
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+-- This ignores the 30 and uses the sequence value instead:
+INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
 
 SELECT * FROM itest2;
 
 
 -- UPDATE tests
 
+-- GENERATED BY DEFAULT is not restricted.
 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;
+-- GENERATED ALWAYS allows only DEFAULT.
+UPDATE itest2 SET a = 101 WHERE a = 1;  -- error
+UPDATE itest2 SET a = DEFAULT WHERE a = 2;  -- ok
 SELECT * FROM itest2;