Allow read only connections during recovery, known as Hot Standby.

Enabled by recovery_connections = on (default) and forcing archive recovery using a recovery.conf. Recovery processing now emulates the original transactions as they are replayed, providing full locking and MVCC behaviour for read only queries. Recovery must enter consistent state before connections are allowed, so there is a delay, typically short, before connections succeed. Replay of recovering transactions can conflict and in some cases deadlock with queries during recovery; these result in query cancellation after max_standby_delay seconds have expired. Infrastructure changes have minor effects on normal running, though introduce four new types of WAL record.

New test mode "make standbycheck" allows regression tests of static command behaviour on a standby server while in recovery. Typical and extreme dynamic behaviours have been checked via code inspection and manual testing. Few port specific behaviours have been utilised, though primary testing has been on Linux only so far.

This commit is the basic patch. Additional changes will follow in this release to enhance some aspects of behaviour, notably improved handling of conflicts, deadlock detection and query cancellation. Changes to VACUUM FULL are also required.

Simon Riggs, with significant and lengthy review by Heikki Linnakangas, including streamlined redesign of snapshot creation and two-phase commit.

Important contributions from Florian Pflug, Mark Kirkwood, Merlin Moncure, Greg Stark, Gianni Ciolli, Gabriele Bartolini, Hannu Krosing, Robert Haas, Tatsuo Ishii, Hiroyuki Yamada plus support and feedback from many other community members.

13 files changed, 823 insertions, 12 deletions

diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile
index 4a47bdfe4f..3a23918a1c 100644
--- a/src/test/regress/GNUmakefile
+++ b/src/test/regress/GNUmakefile
@@ -6,7 +6,7 @@
 # Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
 # Portions Copyright (c) 1994, Regents of the University of California
 #
-# $PostgreSQL: pgsql/src/test/regress/GNUmakefile,v 1.80 2009/12/18 21:28:42 momjian Exp $
+# $PostgreSQL: pgsql/src/test/regress/GNUmakefile,v 1.81 2009/12/19 01:32:45 sriggs Exp $
 #
 #-------------------------------------------------------------------------
 
@@ -149,6 +149,8 @@ installcheck: all
 installcheck-parallel: all
 	$(pg_regress_call) --psqldir=$(PSQLDIR) --schedule=$(srcdir)/parallel_schedule $(MAXCONNOPT)
 
+standbycheck: all
+	$(pg_regress_call) --psqldir=$(PSQLDIR) --schedule=$(srcdir)/standby_schedule --use-existing
 
 # old interfaces follow...
 
diff --git a/src/test/regress/expected/hs_standby_allowed.out b/src/test/regress/expected/hs_standby_allowed.out
new file mode 100644
index 0000000000..1abe5f6fe9
--- /dev/null
+++ b/src/test/regress/expected/hs_standby_allowed.out
@@ -0,0 +1,215 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_allowed.sql
+--
+-- SELECT
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+select count(*) as should_be_2 from hs2;
+ should_be_2 
+-------------
+           2
+(1 row)
+
+select count(*) as should_be_3 from hs3;
+ should_be_3 
+-------------
+           3
+(1 row)
+
+COPY hs1 TO '/tmp/copy_test';
+\! cat /tmp/copy_test
+1
+-- Access sequence directly
+select min_value as sequence_min_value from hsseq;
+ sequence_min_value 
+--------------------
+                  1
+(1 row)
+
+-- Transactions
+begin;
+select count(*)  as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+end;
+begin transaction read only;
+select count(*)  as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+end;
+begin transaction isolation level serializable;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+commit;
+begin;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+commit;
+begin;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+abort;
+start transaction;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+commit;
+begin;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+rollback;
+begin;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+savepoint s;
+select count(*) as should_be_2 from hs2;
+ should_be_2 
+-------------
+           2
+(1 row)
+
+commit;
+begin;
+select count(*) as should_be_1 from hs1;
+ should_be_1 
+-------------
+           1
+(1 row)
+
+savepoint s;
+select count(*) as should_be_2 from hs2;
+ should_be_2 
+-------------
+           2
+(1 row)
+
+release savepoint s;
+select count(*) as should_be_2 from hs2;
+ should_be_2 
+-------------
+           2
+(1 row)
+
+savepoint s;
+select count(*) as should_be_3 from hs3;
+ should_be_3 
+-------------
+           3
+(1 row)
+
+rollback to savepoint s;
+select count(*) as should_be_2 from hs2;
+ should_be_2 
+-------------
+           2
+(1 row)
+
+commit;
+-- SET parameters
+-- has no effect on read only transactions, but we can still set it
+set synchronous_commit = on;
+show synchronous_commit;
+ synchronous_commit 
+--------------------
+ on
+(1 row)
+
+reset synchronous_commit;
+discard temp;
+discard all;
+-- CURSOR commands
+BEGIN;
+DECLARE hsc CURSOR FOR select * from hs3;
+FETCH next from hsc;
+ col1 
+------
+  113
+(1 row)
+
+fetch first from hsc;
+ col1 
+------
+  113
+(1 row)
+
+fetch last from hsc;
+ col1 
+------
+  115
+(1 row)
+
+fetch 1 from hsc;
+ col1 
+------
+(0 rows)
+
+CLOSE hsc;
+COMMIT;
+-- Prepared plans
+PREPARE hsp AS select count(*) from hs1;
+PREPARE hsp_noexec (integer) AS insert into hs1 values ($1);
+EXECUTE hsp;
+ count 
+-------
+     1
+(1 row)
+
+DEALLOCATE hsp;
+-- LOCK
+BEGIN;
+LOCK hs1 IN ACCESS SHARE MODE;
+LOCK hs1 IN ROW SHARE MODE;
+LOCK hs1 IN ROW EXCLUSIVE MODE;
+COMMIT;
+-- LOAD
+-- should work, easier if there is no test for that...
+-- ALLOWED COMMANDS
+CHECKPOINT;
+discard all;
diff --git a/src/test/regress/expected/hs_standby_check.out b/src/test/regress/expected/hs_standby_check.out
new file mode 100644
index 0000000000..df885ea9e0
--- /dev/null
+++ b/src/test/regress/expected/hs_standby_check.out
@@ -0,0 +1,20 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_check.sql
+--
+--
+-- If the query below returns false then all other tests will fail after it.
+--
+select case pg_is_in_recovery() when false then
+	'These tests are intended only for execution on a standby server that is reading ' ||
+	'WAL from a server upon which the regression database is already created and into ' ||
+	'which src/test/regress/sql/hs_primary_setup.sql has been run'
+else
+	'Tests are running on a standby server during recovery'
+end;
+                         case                          
+-------------------------------------------------------
+ Tests are running on a standby server during recovery
+(1 row)
+
diff --git a/src/test/regress/expected/hs_standby_disallowed.out b/src/test/regress/expected/hs_standby_disallowed.out
new file mode 100644
index 0000000000..030201d30d
--- /dev/null
+++ b/src/test/regress/expected/hs_standby_disallowed.out
@@ -0,0 +1,137 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_disallowed.sql
+--
+SET transaction_read_only = off;
+ERROR:  cannot set transaction read-write mode during recovery
+begin transaction read write;
+ERROR:  cannot set transaction read-write mode during recovery
+commit;
+WARNING:  there is no transaction in progress
+-- SELECT
+select * from hs1 FOR SHARE;
+ERROR:  transaction is read-only
+select * from hs1 FOR UPDATE;
+ERROR:  transaction is read-only
+-- DML
+BEGIN;
+insert into hs1 values (37);
+ERROR:  transaction is read-only
+ROLLBACK;
+BEGIN;
+delete from hs1 where col1 = 1;
+ERROR:  transaction is read-only
+ROLLBACK;
+BEGIN;
+update hs1 set col1 = NULL where col1 > 0;
+ERROR:  transaction is read-only
+ROLLBACK;
+BEGIN;
+truncate hs3;
+ERROR:  transaction is read-only
+ROLLBACK;
+-- DDL
+create temporary table hstemp1 (col1 integer);
+ERROR:  transaction is read-only
+BEGIN;
+drop table hs2;
+ERROR:  transaction is read-only
+ROLLBACK;
+BEGIN;
+create table hs4 (col1 integer);
+ERROR:  transaction is read-only
+ROLLBACK;
+-- Sequences
+SELECT nextval('hsseq');
+ERROR:  cannot be executed during recovery
+-- Two-phase commit transaction stuff
+BEGIN;
+SELECT count(*) FROM hs1;
+ count 
+-------
+     1
+(1 row)
+
+PREPARE TRANSACTION 'foobar';
+ERROR:  cannot be executed during recovery
+ROLLBACK;
+BEGIN;
+SELECT count(*) FROM hs1;
+ count 
+-------
+     1
+(1 row)
+
+COMMIT PREPARED 'foobar';
+ERROR:  cannot be executed during recovery
+ROLLBACK;
+BEGIN;
+SELECT count(*) FROM hs1;
+ count 
+-------
+     1
+(1 row)
+
+PREPARE TRANSACTION 'foobar';
+ERROR:  cannot be executed during recovery
+ROLLBACK PREPARED 'foobar';
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+BEGIN;
+SELECT count(*) FROM hs1;
+ count 
+-------
+     1
+(1 row)
+
+ROLLBACK PREPARED 'foobar';
+ERROR:  cannot be executed during recovery
+ROLLBACK;
+-- Locks
+BEGIN;
+LOCK hs1;
+ERROR:  cannot be executed during recovery
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE UPDATE EXCLUSIVE MODE;
+ERROR:  cannot be executed during recovery
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE MODE;
+ERROR:  cannot be executed during recovery
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE ROW EXCLUSIVE MODE;
+ERROR:  cannot be executed during recovery
+COMMIT;
+BEGIN;
+LOCK hs1 IN EXCLUSIVE MODE;
+ERROR:  cannot be executed during recovery
+COMMIT;
+BEGIN;
+LOCK hs1 IN ACCESS EXCLUSIVE MODE;
+ERROR:  cannot be executed during recovery
+COMMIT;
+-- Listen
+listen a;
+ERROR:  cannot be executed during recovery
+notify a;
+ERROR:  cannot be executed during recovery
+unlisten a;
+ERROR:  cannot be executed during recovery
+unlisten *;
+ERROR:  cannot be executed during recovery
+-- disallowed commands
+ANALYZE hs1;
+ERROR:  cannot be executed during recovery
+VACUUM hs2;
+ERROR:  cannot be executed during recovery
+CLUSTER hs2 using hs1_pkey;
+ERROR:  cannot be executed during recovery
+REINDEX TABLE hs2;
+ERROR:  cannot be executed during recovery
+REVOKE SELECT ON hs1 FROM PUBLIC;
+ERROR:  transaction is read-only
+GRANT SELECT ON hs1 TO PUBLIC;
+ERROR:  transaction is read-only
diff --git a/src/test/regress/expected/hs_standby_functions.out b/src/test/regress/expected/hs_standby_functions.out
new file mode 100644
index 0000000000..edcf1c72ad
--- /dev/null
+++ b/src/test/regress/expected/hs_standby_functions.out
@@ -0,0 +1,40 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_functions.sql
+--
+-- should fail
+select txid_current();
+ERROR:  cannot be executed during recovery
+select length(txid_current_snapshot()::text) >= 4;
+ ?column? 
+----------
+ t
+(1 row)
+
+select pg_start_backup('should fail');
+ERROR:  recovery is in progress
+HINT:  WAL control functions cannot be executed during recovery.
+select pg_switch_xlog();
+ERROR:  recovery is in progress
+HINT:  WAL control functions cannot be executed during recovery.
+select pg_stop_backup();
+ERROR:  recovery is in progress
+HINT:  WAL control functions cannot be executed during recovery.
+-- should return no rows
+select * from pg_prepared_xacts;
+ transaction | gid | prepared | owner | database 
+-------------+-----+----------+-------+----------
+(0 rows)
+
+-- just the startup process
+select locktype, virtualxid, virtualtransaction, mode, granted
+from pg_locks where virtualxid = '1/1';
+  locktype  | virtualxid | virtualtransaction |     mode      | granted 
+------------+------------+--------------------+---------------+---------
+ virtualxid | 1/1        | 1/0                | ExclusiveLock | t
+(1 row)
+
+-- suicide is painless
+select pg_cancel_backend(pg_backend_pid());
+ERROR:  canceling statement due to user request
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 7fe472b503..78c30bdb2f 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -11,7 +11,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/test/regress/pg_regress.c,v 1.67 2009/11/23 16:02:24 tgl Exp $
+ * $PostgreSQL: pgsql/src/test/regress/pg_regress.c,v 1.68 2009/12/19 01:32:45 sriggs Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -93,6 +93,7 @@ static char *temp_install = NULL;
 static char *temp_config = NULL;
 static char *top_builddir = NULL;
 static bool nolocale = false;
+static bool use_existing = false;
 static char *hostname = NULL;
 static int	port = -1;
 static bool port_specified_by_user = false;
@@ -1545,7 +1546,7 @@ run_schedule(const char *schedule, test_function tfunc)
 
 		if (num_tests == 1)
 		{
-			status(_("test %-20s ... "), tests[0]);
+			status(_("test %-24s ... "), tests[0]);
 			pids[0] = (tfunc) (tests[0], &resultfiles[0], &expectfiles[0], &tags[0]);
 			wait_for_tests(pids, statuses, NULL, 1);
 			/* status line is finished below */
@@ -1590,7 +1591,7 @@ run_schedule(const char *schedule, test_function tfunc)
 			bool		differ = false;
 
 			if (num_tests > 1)
-				status(_("     %-20s ... "), tests[i]);
+				status(_("     %-24s ... "), tests[i]);
 
 			/*
 			 * Advance over all three lists simultaneously.
@@ -1918,6 +1919,7 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 		{"dlpath", required_argument, NULL, 17},
 		{"create-role", required_argument, NULL, 18},
 		{"temp-config", required_argument, NULL, 19},
+		{"use-existing", no_argument, NULL, 20},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -2008,6 +2010,9 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 			case 19:
 				temp_config = strdup(optarg);
 				break;
+			case 20:
+				use_existing = true;
+				break;
 			default:
 				/* getopt_long already emitted a complaint */
 				fprintf(stderr, _("\nTry \"%s -h\" for more information.\n"),
@@ -2254,19 +2259,25 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 		 * Using an existing installation, so may need to get rid of
 		 * pre-existing database(s) and role(s)
 		 */
-		for (sl = dblist; sl; sl = sl->next)
-			drop_database_if_exists(sl->str);
-		for (sl = extraroles; sl; sl = sl->next)
-			drop_role_if_exists(sl->str);
+		if (!use_existing)
+		{
+			for (sl = dblist; sl; sl = sl->next)
+				drop_database_if_exists(sl->str);
+			for (sl = extraroles; sl; sl = sl->next)
+				drop_role_if_exists(sl->str);
+		}
 	}
 
 	/*
 	 * Create the test database(s) and role(s)
 	 */
-	for (sl = dblist; sl; sl = sl->next)
-		create_database(sl->str);
-	for (sl = extraroles; sl; sl = sl->next)
-		create_role(sl->str, dblist);
+	if (!use_existing)
+	{
+		for (sl = dblist; sl; sl = sl->next)
+			create_database(sl->str);
+		for (sl = extraroles; sl; sl = sl->next)
+			create_role(sl->str, dblist);
+	}
 
 	/*
 	 * Ready to run the tests
diff --git a/src/test/regress/sql/hs_primary_extremes.sql b/src/test/regress/sql/hs_primary_extremes.sql
new file mode 100644
index 0000000000..900bd1924e
--- /dev/null
+++ b/src/test/regress/sql/hs_primary_extremes.sql
@@ -0,0 +1,74 @@
+--
+-- Hot Standby tests
+--
+-- hs_primary_extremes.sql
+--
+
+drop table if exists hs_extreme;
+create table hs_extreme (col1 integer);
+
+CREATE OR REPLACE FUNCTION hs_subxids (n integer)
+RETURNS void 
+LANGUAGE plpgsql 
+AS $$
+    BEGIN
+      IF n <= 0 THEN RETURN; END IF;
+      INSERT INTO hs_extreme VALUES (n);
+      PERFORM hs_subxids(n - 1);
+      RETURN;
+    EXCEPTION WHEN raise_exception THEN NULL; END;
+$$;
+
+BEGIN;
+SELECT hs_subxids(257);
+ROLLBACK;
+BEGIN;
+SELECT hs_subxids(257);
+COMMIT;
+
+set client_min_messages = 'warning';
+
+CREATE OR REPLACE FUNCTION hs_locks_create (n integer)
+RETURNS void 
+LANGUAGE plpgsql 
+AS $$
+    BEGIN
+      IF n <= 0 THEN
+		CHECKPOINT;
+		RETURN; 
+	  END IF;
+      EXECUTE 'CREATE TABLE hs_locks_' || n::text || ' ()';
+      PERFORM hs_locks_create(n - 1);
+      RETURN;
+    EXCEPTION WHEN raise_exception THEN NULL; END;
+$$;
+
+CREATE OR REPLACE FUNCTION hs_locks_drop (n integer)
+RETURNS void 
+LANGUAGE plpgsql 
+AS $$
+    BEGIN
+      IF n <= 0 THEN
+		CHECKPOINT;
+		RETURN; 
+	  END IF;
+	  EXECUTE 'DROP TABLE IF EXISTS hs_locks_' || n::text;
+      PERFORM hs_locks_drop(n - 1);
+      RETURN;
+    EXCEPTION WHEN raise_exception THEN NULL; END;
+$$;
+
+BEGIN;
+SELECT hs_locks_drop(257);
+SELECT hs_locks_create(257);
+SELECT count(*) > 257 FROM pg_locks;
+ROLLBACK;
+BEGIN;
+SELECT hs_locks_drop(257);
+SELECT hs_locks_create(257);
+SELECT count(*) > 257 FROM pg_locks;
+COMMIT;
+SELECT hs_locks_drop(257);
+
+SELECT pg_switch_xlog();
+
diff --git a/src/test/regress/sql/hs_primary_setup.sql b/src/test/regress/sql/hs_primary_setup.sql
new file mode 100644
index 0000000000..a00b367cbc
--- /dev/null
+++ b/src/test/regress/sql/hs_primary_setup.sql
@@ -0,0 +1,25 @@
+--
+-- Hot Standby tests
+--
+-- hs_primary_setup.sql
+--
+
+drop table if exists hs1;
+create table hs1 (col1 integer primary key);
+insert into hs1 values (1);
+
+drop table if exists hs2;
+create table hs2 (col1 integer primary key);
+insert into hs2 values (12);
+insert into hs2 values (13);
+
+drop table if exists hs3;
+create table hs3 (col1 integer primary key);
+insert into hs3 values (113);
+insert into hs3 values (114);
+insert into hs3 values (115);
+
+DROP sequence if exists hsseq;
+create sequence hsseq;
+
+SELECT pg_switch_xlog();
diff --git a/src/test/regress/sql/hs_standby_allowed.sql b/src/test/regress/sql/hs_standby_allowed.sql
new file mode 100644
index 0000000000..58e2c010d3
--- /dev/null
+++ b/src/test/regress/sql/hs_standby_allowed.sql
@@ -0,0 +1,121 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_allowed.sql
+--
+
+-- SELECT
+
+select count(*) as should_be_1 from hs1;
+
+select count(*) as should_be_2 from hs2;
+
+select count(*) as should_be_3 from hs3;
+
+COPY hs1 TO '/tmp/copy_test';
+\! cat /tmp/copy_test
+
+-- Access sequence directly
+select min_value as sequence_min_value from hsseq;
+
+-- Transactions
+
+begin;
+select count(*)  as should_be_1 from hs1;
+end;
+
+begin transaction read only;
+select count(*)  as should_be_1 from hs1;
+end;
+
+begin transaction isolation level serializable;
+select count(*) as should_be_1 from hs1;
+select count(*) as should_be_1 from hs1;
+select count(*) as should_be_1 from hs1;
+commit;
+
+begin;
+select count(*) as should_be_1 from hs1;
+commit;
+
+begin;
+select count(*) as should_be_1 from hs1;
+abort;
+
+start transaction;
+select count(*) as should_be_1 from hs1;
+commit;
+
+begin;
+select count(*) as should_be_1 from hs1;
+rollback;
+
+begin;
+select count(*) as should_be_1 from hs1;
+savepoint s;
+select count(*) as should_be_2 from hs2;
+commit;
+
+begin;
+select count(*) as should_be_1 from hs1;
+savepoint s;
+select count(*) as should_be_2 from hs2;
+release savepoint s;
+select count(*) as should_be_2 from hs2;
+savepoint s;
+select count(*) as should_be_3 from hs3;
+rollback to savepoint s;
+select count(*) as should_be_2 from hs2;
+commit;
+
+-- SET parameters
+
+-- has no effect on read only transactions, but we can still set it
+set synchronous_commit = on;
+show synchronous_commit;
+reset synchronous_commit;
+
+discard temp;
+discard all;
+
+-- CURSOR commands
+
+BEGIN;
+
+DECLARE hsc CURSOR FOR select * from hs3;
+
+FETCH next from hsc;
+fetch first from hsc;
+fetch last from hsc;
+fetch 1 from hsc;
+
+CLOSE hsc;
+
+COMMIT;
+
+-- Prepared plans
+
+PREPARE hsp AS select count(*) from hs1;
+PREPARE hsp_noexec (integer) AS insert into hs1 values ($1);
+
+EXECUTE hsp;
+
+DEALLOCATE hsp;
+
+-- LOCK
+
+BEGIN;
+LOCK hs1 IN ACCESS SHARE MODE;
+LOCK hs1 IN ROW SHARE MODE;
+LOCK hs1 IN ROW EXCLUSIVE MODE;
+COMMIT;
+
+-- LOAD
+-- should work, easier if there is no test for that...
+
+
+-- ALLOWED COMMANDS
+
+CHECKPOINT;
+
+discard all;
diff --git a/src/test/regress/sql/hs_standby_check.sql b/src/test/regress/sql/hs_standby_check.sql
new file mode 100644
index 0000000000..3fe8a02720
--- /dev/null
+++ b/src/test/regress/sql/hs_standby_check.sql
@@ -0,0 +1,16 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_check.sql
+--
+
+--
+-- If the query below returns false then all other tests will fail after it.
+--
+select case pg_is_in_recovery() when false then
+	'These tests are intended only for execution on a standby server that is reading ' ||
+	'WAL from a server upon which the regression database is already created and into ' ||
+	'which src/test/regress/sql/hs_primary_setup.sql has been run'
+else
+	'Tests are running on a standby server during recovery'
+end;
diff --git a/src/test/regress/sql/hs_standby_disallowed.sql b/src/test/regress/sql/hs_standby_disallowed.sql
new file mode 100644
index 0000000000..21bbf526b7
--- /dev/null
+++ b/src/test/regress/sql/hs_standby_disallowed.sql
@@ -0,0 +1,105 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_disallowed.sql
+--
+
+SET transaction_read_only = off;
+
+begin transaction read write;
+commit;
+
+-- SELECT
+
+select * from hs1 FOR SHARE;
+select * from hs1 FOR UPDATE;
+
+-- DML
+BEGIN;
+insert into hs1 values (37);
+ROLLBACK;
+BEGIN;
+delete from hs1 where col1 = 1;
+ROLLBACK;
+BEGIN;
+update hs1 set col1 = NULL where col1 > 0;
+ROLLBACK;
+BEGIN;
+truncate hs3;
+ROLLBACK;
+
+-- DDL
+
+create temporary table hstemp1 (col1 integer);
+BEGIN;
+drop table hs2;
+ROLLBACK;
+BEGIN;
+create table hs4 (col1 integer);
+ROLLBACK;
+
+-- Sequences
+
+SELECT nextval('hsseq');
+
+-- Two-phase commit transaction stuff
+
+BEGIN;
+SELECT count(*) FROM hs1;
+PREPARE TRANSACTION 'foobar';
+ROLLBACK;
+BEGIN;
+SELECT count(*) FROM hs1;
+COMMIT PREPARED 'foobar';
+ROLLBACK;
+
+BEGIN;
+SELECT count(*) FROM hs1;
+PREPARE TRANSACTION 'foobar';
+ROLLBACK PREPARED 'foobar';
+ROLLBACK;
+
+BEGIN;
+SELECT count(*) FROM hs1;
+ROLLBACK PREPARED 'foobar';
+ROLLBACK;
+
+
+-- Locks
+BEGIN;
+LOCK hs1;
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE UPDATE EXCLUSIVE MODE;
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE MODE;
+COMMIT;
+BEGIN;
+LOCK hs1 IN SHARE ROW EXCLUSIVE MODE;
+COMMIT;
+BEGIN;
+LOCK hs1 IN EXCLUSIVE MODE;
+COMMIT;
+BEGIN;
+LOCK hs1 IN ACCESS EXCLUSIVE MODE;
+COMMIT;
+
+-- Listen
+listen a;
+notify a;
+unlisten a;
+unlisten *;
+
+-- disallowed commands
+
+ANALYZE hs1;
+
+VACUUM hs2;
+
+CLUSTER hs2 using hs1_pkey;
+
+REINDEX TABLE hs2;
+
+REVOKE SELECT ON hs1 FROM PUBLIC;
+GRANT SELECT ON hs1 TO PUBLIC;
diff --git a/src/test/regress/sql/hs_standby_functions.sql b/src/test/regress/sql/hs_standby_functions.sql
new file mode 100644
index 0000000000..7577045f11
--- /dev/null
+++ b/src/test/regress/sql/hs_standby_functions.sql
@@ -0,0 +1,24 @@
+--
+-- Hot Standby tests
+--
+-- hs_standby_functions.sql
+--
+
+-- should fail
+select txid_current();
+
+select length(txid_current_snapshot()::text) >= 4;
+
+select pg_start_backup('should fail');
+select pg_switch_xlog();
+select pg_stop_backup();
+
+-- should return no rows
+select * from pg_prepared_xacts;
+
+-- just the startup process
+select locktype, virtualxid, virtualtransaction, mode, granted
+from pg_locks where virtualxid = '1/1';
+
+-- suicide is painless
+select pg_cancel_backend(pg_backend_pid());
diff --git a/src/test/regress/standby_schedule b/src/test/regress/standby_schedule
new file mode 100644
index 0000000000..7e239d4b28
--- /dev/null
+++ b/src/test/regress/standby_schedule
@@ -0,0 +1,21 @@
+# $PostgreSQL: pgsql/src/test/regress/standby_schedule,v 1.1 2009/12/19 01:32:45 sriggs Exp $
+#
+# Test schedule for Hot Standby
+#
+# First test checks we are on a standby server.
+# Subsequent tests rely upon a setup script having already
+# been executed in the appropriate database on the primary server
+# which is feeding WAL files to target standby.
+#
+# psql -f src/test/regress/sql/hs_primary_setup.sql regression
+#
+test: hs_standby_check
+#
+# These tests will pass on both primary and standby servers
+#
+test: hs_standby_allowed
+#
+# These tests will fail on a non-standby server
+#
+test: hs_standby_disallowed
+test: hs_standby_functions