Implement isolation levels read uncommitted and repeatable read as acting

like the next higher one.

11 files changed, 108 insertions, 47 deletions

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 4cd309dd65e..667bb93ba8c 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -156,10 +156,10 @@ F051	Basic date and time	07	LOCALTIME	YES
 F051	Basic date and time	08	LOCALTIMESTAMP	YES	
 F052	Intervals and datetime arithmetic			YES	
 F081	UNION and EXCEPT in views			YES	
-F111	Isolation levels other than SERIALIZABLE			NO	
-F111	Isolation levels other than SERIALIZABLE	01	READ UNCOMMITTED isolation level	NO	
+F111	Isolation levels other than SERIALIZABLE			YES	
+F111	Isolation levels other than SERIALIZABLE	01	READ UNCOMMITTED isolation level	YES	behaves like READ COMMITTED
 F111	Isolation levels other than SERIALIZABLE	02	READ COMMITTED isolation level	YES	
-F111	Isolation levels other than SERIALIZABLE	03	REPEATABLE READ isolation level	NO	
+F111	Isolation levels other than SERIALIZABLE	03	REPEATABLE READ isolation level	YES	behaves like SERIALIZABLE
 F121	Basic diagnostics management			NO	
 F121	Basic diagnostics management	01	GET DIAGNOSTICS statement	NO	
 F121	Basic diagnostics management	02	SET TRANSACTION statement: DIAGNOSTICS SIZE clause	NO	
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index d2cc87e43d5..9a603a6f042 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/trigger.c,v 1.159 2003/10/02 06:34:03 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/trigger.c,v 1.160 2003/11/06 22:08:14 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1576,7 +1576,7 @@ ltrmark:;
 
 			case HeapTupleUpdated:
 				ReleaseBuffer(buffer);
-				if (XactIsoLevel == XACT_SERIALIZABLE)
+				if (IsXactIsoLevelSerializable)
 					ereport(ERROR,
 							(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 							 errmsg("could not serialize access due to concurrent update")));
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index 7c862a5b3ac..f763163957e 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.88 2003/09/25 06:57:59 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.89 2003/11/06 22:08:14 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -640,11 +640,21 @@ assign_XactIsoLevel(const char *value, bool doit, bool interactive)
 		if (doit)
 			XactIsoLevel = XACT_SERIALIZABLE;
 	}
+	else if (strcmp(value, "repeatable read") == 0)
+	{
+		if (doit)
+			XactIsoLevel = XACT_REPEATABLE_READ;
+	}
 	else if (strcmp(value, "read committed") == 0)
 	{
 		if (doit)
 			XactIsoLevel = XACT_READ_COMMITTED;
 	}
+	else if (strcmp(value, "read uncommitted") == 0)
+	{
+		if (doit)
+			XactIsoLevel = XACT_READ_UNCOMMITTED;
+	}
 	else if (strcmp(value, "default") == 0)
 	{
 		if (doit)
@@ -659,10 +669,19 @@ assign_XactIsoLevel(const char *value, bool doit, bool interactive)
 const char *
 show_XactIsoLevel(void)
 {
-	if (XactIsoLevel == XACT_SERIALIZABLE)
-		return "serializable";
-	else
-		return "read committed";
+	switch (XactIsoLevel)
+	{
+		case XACT_READ_UNCOMMITTED:
+			return "read uncommitted";
+		case XACT_READ_COMMITTED:
+			return "read committed";
+		case XACT_REPEATABLE_READ:
+			return "repeatable read";
+		case XACT_SERIALIZABLE:
+			return "serializable";
+		default:
+			return "bogus";
+	}
 }
 
 
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index bfdc94c6d51..1e6c3ffe30c 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -26,7 +26,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.220 2003/10/01 21:30:52 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.221 2003/11/06 22:08:14 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1139,7 +1139,7 @@ lnext:	;
 							break;
 
 						case HeapTupleUpdated:
-							if (XactIsoLevel == XACT_SERIALIZABLE)
+							if (IsXactIsoLevelSerializable)
 								ereport(ERROR,
 										(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 										 errmsg("could not serialize access due to concurrent update")));
@@ -1440,7 +1440,7 @@ ldelete:;
 			break;
 
 		case HeapTupleUpdated:
-			if (XactIsoLevel == XACT_SERIALIZABLE)
+			if (IsXactIsoLevelSerializable)
 				ereport(ERROR,
 						(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 						 errmsg("could not serialize access due to concurrent update")));
@@ -1576,7 +1576,7 @@ lreplace:;
 			break;
 
 		case HeapTupleUpdated:
-			if (XactIsoLevel == XACT_SERIALIZABLE)
+			if (IsXactIsoLevelSerializable)
 				ereport(ERROR,
 						(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 						 errmsg("could not serialize access due to concurrent update")));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b73aa6f9eef..5f0b0f4e5da 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.436 2003/10/02 06:34:04 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.437 2003/11/06 22:08:14 petere Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -342,7 +342,7 @@ static void doNegateFloat(Value *v);
 
 	DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
 	DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
-    DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
+	DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
 
 	EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING
 	EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
@@ -380,7 +380,7 @@ static void doNegateFloat(Value *v);
 	PRECISION PRESERVE PREPARE PRIMARY 
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE
 
-	READ REAL RECHECK REFERENCES REINDEX RELATIVE_P RENAME REPLACE
+	READ REAL RECHECK REFERENCES REINDEX RELATIVE_P RENAME REPEATABLE REPLACE
 	RESET RESTART RESTRICT RETURNS REVOKE RIGHT ROLLBACK ROW ROWS
 	RULE
 
@@ -393,7 +393,7 @@ static void doNegateFloat(Value *v);
 	TO TOAST TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
 	TRUNCATE TRUSTED TYPE_P
 
-	UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
+	UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNTIL
 	UPDATE USAGE USER USING
 
 	VACUUM VALID VALIDATOR VALUES VARCHAR VARYING
@@ -922,7 +922,9 @@ var_value:	opt_boolean
 				{ $$ = makeAConst($1); }
 		;
 
-iso_level:	READ COMMITTED							{ $$ = "read committed"; }
+iso_level:	READ UNCOMMITTED						{ $$ = "read uncommitted"; }
+			| READ COMMITTED						{ $$ = "read committed"; }
+			| REPEATABLE READ						{ $$ = "repeatable read"; }
 			| SERIALIZABLE							{ $$ = "serializable"; }
 		;
 
@@ -7407,6 +7409,7 @@ unreserved_keyword:
 			| REINDEX
 			| RELATIVE_P
 			| RENAME
+			| REPEATABLE
 			| REPLACE
 			| RESET
 			| RESTART
@@ -7445,6 +7448,7 @@ unreserved_keyword:
 			| TRUNCATE
 			| TRUSTED
 			| TYPE_P
+			| UNCOMMITTED
 			| UNENCRYPTED
 			| UNKNOWN
 			| UNLISTEN
diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c
index c4048b4c1d8..6d42a13ef87 100644
--- a/src/backend/parser/keywords.c
+++ b/src/backend/parser/keywords.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.141 2003/08/04 02:40:01 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.142 2003/11/06 22:08:15 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -251,6 +251,7 @@ static const ScanKeyword ScanKeywords[] = {
 	{"reindex", REINDEX},
 	{"relative", RELATIVE_P},
 	{"rename", RENAME},
+	{"repeatable", REPEATABLE},
 	{"replace", REPLACE},
 	{"reset", RESET},
 	{"restart", RESTART},
@@ -307,6 +308,7 @@ static const ScanKeyword ScanKeywords[] = {
 	{"truncate", TRUNCATE},
 	{"trusted", TRUSTED},
 	{"type", TYPE_P},
+	{"uncommitted", UNCOMMITTED},
 	{"unencrypted", UNENCRYPTED},
 	{"union", UNION},
 	{"unique", UNIQUE},
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index d0e3e9c0817..7caca437449 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -17,7 +17,7 @@
  *
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  *
- * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.63 2003/10/31 03:58:20 wieck Exp $
+ * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.64 2003/11/06 22:08:15 petere Exp $
  *
  * ----------
  */
@@ -3073,7 +3073,7 @@ ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
 	 * rows under current snapshot that wouldn't be visible per the
 	 * transaction snapshot).
 	 */
-	if (XactIsoLevel == XACT_SERIALIZABLE)
+	if (IsXactIsoLevelSerializable)
 	{
 		useCurrentSnapshot = detectNewRows;
 	}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index c3e9a0ec818..fd9def3e6cd 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.164 2003/10/18 22:59:09 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.165 2003/11/06 22:08:15 petere Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -1346,7 +1346,7 @@ static struct config_string ConfigureNamesString[] =
 		{"default_transaction_isolation", PGC_USERSET, CLIENT_CONN_STATEMENT,
 			gettext_noop("Sets the transaction isolation level of each new transaction."),
 			gettext_noop("Each SQL transaction has an isolation level, which "
-				 "can be either \"read committed\" or \"serializable\".")
+				 "can be either \"read uncommitted\", \"read committed\", \"repeatable read\", or \"serializable\".")
 		},
 		&default_iso_level_string,
 		"read committed", assign_defaultxactisolevel, NULL
@@ -4238,11 +4238,21 @@ assign_defaultxactisolevel(const char *newval, bool doit, bool interactive)
 		if (doit)
 			DefaultXactIsoLevel = XACT_SERIALIZABLE;
 	}
+	else if (strcasecmp(newval, "repeatable read") == 0)
+	{
+		if (doit)
+			DefaultXactIsoLevel = XACT_REPEATABLE_READ;
+	}
 	else if (strcasecmp(newval, "read committed") == 0)
 	{
 		if (doit)
 			DefaultXactIsoLevel = XACT_READ_COMMITTED;
 	}
+	else if (strcasecmp(newval, "read uncommitted") == 0)
+	{
+		if (doit)
+			DefaultXactIsoLevel = XACT_READ_UNCOMMITTED;
+	}
 	else
 		return NULL;
 	return newval;
diff --git a/src/backend/utils/time/tqual.c b/src/backend/utils/time/tqual.c
index d7d22b77866..fa59e0f1752 100644
--- a/src/backend/utils/time/tqual.c
+++ b/src/backend/utils/time/tqual.c
@@ -16,7 +16,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/time/tqual.c,v 1.70 2003/10/01 21:30:52 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/time/tqual.c,v 1.71 2003/11/06 22:08:15 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -984,7 +984,7 @@ SetQuerySnapshot(void)
 		return;
 	}
 
-	if (XactIsoLevel == XACT_SERIALIZABLE)
+	if (IsXactIsoLevelSerializable)
 		QuerySnapshot = SerializableSnapshot;
 	else
 		QuerySnapshot = GetSnapshotData(&QuerySnapshotData, false);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d4ceb9a3e9e..5ac058e8deb 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2003, PostgreSQL Global Development Group
  *
- * $Header: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v 1.91 2003/10/30 21:37:38 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v 1.92 2003/11/06 22:08:15 petere Exp $
  */
 
 /*----------------------------------------------------------------------
@@ -466,7 +466,7 @@ psql_completion(char *text, int start, int end)
 		"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT",
 		"COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", "EXECUTE",
 		"EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
-		"PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET", "SHOW",
+		"PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET", "SHOW", "START",
 		"TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", NULL
 	};
 
@@ -1101,30 +1101,36 @@ psql_completion(char *text, int start, int end)
 			 strcasecmp(prev_wd, "SHOW") == 0)
 		COMPLETE_WITH_LIST(pgsql_variables);
 	/* Complete "SET TRANSACTION" */
-	else if ((strcasecmp(prev2_wd, "SET") == 0 &&
-			  strcasecmp(prev_wd, "TRANSACTION") == 0) ||
-			 (strcasecmp(prev4_wd, "SESSION") == 0 &&
-			  strcasecmp(prev3_wd, "CHARACTERISTICS") == 0 &&
-			  strcasecmp(prev2_wd, "AS") == 0 &&
-			  strcasecmp(prev_wd, "TRANSACTION") == 0))
+	else if ((strcasecmp(prev2_wd, "SET") == 0
+			  && strcasecmp(prev_wd, "TRANSACTION") == 0)
+			 || (strcasecmp(prev2_wd, "START") == 0
+				 && strcasecmp(prev_wd, "TRANSACTION") == 0)
+			 || (strcasecmp(prev4_wd, "SESSION") == 0
+				 && strcasecmp(prev3_wd, "CHARACTERISTICS") == 0
+				 && strcasecmp(prev2_wd, "AS") == 0
+				 && strcasecmp(prev_wd, "TRANSACTION") == 0))
 	{
 		static const char * const my_list[] =
 		{"ISOLATION", "READ", NULL};
 
 		COMPLETE_WITH_LIST(my_list);
 	}
-	else if (strcasecmp(prev3_wd, "SET") == 0 &&
-			 strcasecmp(prev2_wd, "TRANSACTION") == 0 &&
-			 strcasecmp(prev_wd, "ISOLATION") == 0)
+	else if ((strcasecmp(prev3_wd, "SET") == 0
+			  || strcasecmp(prev3_wd, "START") == 0
+			  || (strcasecmp(prev4_wd, "CHARACTERISTICS") == 0
+				  && strcasecmp(prev3_wd, "AS") == 0))
+			 && strcasecmp(prev2_wd, "TRANSACTION") == 0
+			 && strcasecmp(prev_wd, "ISOLATION") == 0)
 		COMPLETE_WITH_CONST("LEVEL");
-	else if ((strcasecmp(prev4_wd, "SET") == 0 ||
-			  strcasecmp(prev4_wd, "AS") == 0) &&
-			 strcasecmp(prev3_wd, "TRANSACTION") == 0 &&
-			 strcasecmp(prev2_wd, "ISOLATION") == 0 &&
-			 strcasecmp(prev_wd, "LEVEL") == 0)
+	else if ((strcasecmp(prev4_wd, "SET") == 0
+			  || strcasecmp(prev4_wd, "START") == 0
+			  || strcasecmp(prev4_wd, "AS") == 0)
+			 && strcasecmp(prev3_wd, "TRANSACTION") == 0
+			 && strcasecmp(prev2_wd, "ISOLATION") == 0
+			 && strcasecmp(prev_wd, "LEVEL") == 0)
 	{
 		static const char * const my_list[] =
-		{"READ", "SERIALIZABLE", NULL};
+		{"READ", "REPEATABLE", "SERIALIZABLE", NULL};
 
 		COMPLETE_WITH_LIST(my_list);
 	}
@@ -1132,7 +1138,17 @@ psql_completion(char *text, int start, int end)
 			 strcasecmp(prev3_wd, "ISOLATION") == 0 &&
 			 strcasecmp(prev2_wd, "LEVEL") == 0 &&
 			 strcasecmp(prev_wd, "READ") == 0)
-		COMPLETE_WITH_CONST("COMMITTED");
+	{
+		static const char * const my_list[] =
+		{"UNCOMMITTED", "COMMITTED", NULL};
+
+		COMPLETE_WITH_LIST(my_list);
+	}
+	else if (strcasecmp(prev4_wd, "TRANSACTION") == 0 &&
+			 strcasecmp(prev3_wd, "ISOLATION") == 0 &&
+			 strcasecmp(prev2_wd, "LEVEL") == 0 &&
+			 strcasecmp(prev_wd, "REPEATABLE") == 0)
+		COMPLETE_WITH_CONST("READ");
 	else if ((strcasecmp(prev3_wd, "SET") == 0 ||
 			  strcasecmp(prev3_wd, "AS") == 0) &&
 			 strcasecmp(prev2_wd, "TRANSACTION") == 0 &&
@@ -1200,6 +1216,10 @@ psql_completion(char *text, int start, int end)
 		}
 	}
 
+/* START TRANSACTION */
+	else if (strcasecmp(prev_wd, "START") == 0)
+		COMPLETE_WITH_CONST("TRANSACTION");
+
 /* TRUNCATE */
 	else if (strcasecmp(prev_wd, "TRUNCATE") == 0)
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index d95c3df7945..33a95bad997 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: xact.h,v 1.57 2003/10/16 16:50:41 tgl Exp $
+ * $Id: xact.h,v 1.58 2003/11/06 22:08:15 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -22,14 +22,20 @@
 /*
  * Xact isolation levels
  */
-#define XACT_DIRTY_READ			0		/* not implemented */
+#define XACT_READ_UNCOMMITTED	0
 #define XACT_READ_COMMITTED		1
-#define XACT_REPEATABLE_READ	2		/* not implemented */
+#define XACT_REPEATABLE_READ	2
 #define XACT_SERIALIZABLE		3
 
 extern int	DefaultXactIsoLevel;
 extern int	XactIsoLevel;
 
+/*
+ * We only implement two distinct levels, so this is a convenience to
+ * check which level we're really using internally.
+ */
+#define IsXactIsoLevelSerializable ((XactIsoLevel == XACT_REPEATABLE_READ || XactIsoLevel == XACT_SERIALIZABLE))
+
 /* Xact read-only state */
 extern bool DefaultXactReadOnly;
 extern bool XactReadOnly;